-- -----------------------------------------------------
-- Table externalDataT
-- -----------------------------------------------------

alter table externalDataT add externalParentKey varchar(255) null after externalDataSourceId;
alter table externalDataT add index externalparentkey_idx (externalParentKey asc);

alter table externalDataT modify parentNodeId int unsigned null;

alter table externalDataT drop foreign key fk_externalDataT_tagT2;
alter table externalDataT add constraint fk_externalDataT_tagT2
    foreign key (parentNodeId) references tagT (id) on delete set null on update no action;




drop table if exists tmpAdmterDataT;

create table tmpAdmterDataT (
  id varchar(10) not null,
  parentId varchar(10) null,
  koatuu varchar(10) null,
  ukrName varchar(255) null,
  rusName varchar(255) null,
  engName varchar(255) null,
  oblast_koatuu varchar(10) null,
  district_koatuu varchar(10) null,
  adminCenterId varchar(10) null,
  type char(2) null,
  primary key (id))
ENGINE = InnoDB;



-- -----------------------------------------------------
-- Table externalAdmterTypeT
-- -----------------------------------------------------
DROP TABLE IF EXISTS externalAdmterTypeT ;

CREATE TABLE IF NOT EXISTS externalAdmterTypeT (
  id INT UNSIGNED NOT NULL,
  typeCode VARCHAR(30) BINARY NOT NULL,
  added TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table externalAdmterTypeTL
-- -----------------------------------------------------
DROP TABLE IF EXISTS externalAdmterTypeTL ;

CREATE TABLE IF NOT EXISTS externalAdmterTypeTL (
  id INT UNSIGNED NOT NULL,
  languageId CHAR(3) NOT NULL,
  prefix VARCHAR(100) NOT NULL,
  suffix VARCHAR(100) NOT NULL,
  PRIMARY KEY (id, languageId),
  INDEX fk_externalAdmterTypeTL_languageT1_idx (languageId ASC),
  CONSTRAINT fk_externalAdmterTypeTL_externalAdmterTypeT1
    FOREIGN KEY (id)
    REFERENCES externalAdmterTypeT (id)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT fk_externalAdmterTypeTL_languageT1
    FOREIGN KEY (languageId)
    REFERENCES languageT (id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Data for table externalAdmterTypeT
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO externalAdmterTypeT (id, typeCode, added) VALUES (2, 'oblast', NULL);
INSERT INTO externalAdmterTypeT (id, typeCode, added) VALUES (3, 'raion', NULL);
INSERT INTO externalAdmterTypeT (id, typeCode, added) VALUES (4, 'neighborhood', NULL);
INSERT INTO externalAdmterTypeT (id, typeCode, added) VALUES (5, 'villagecouncil', NULL);
INSERT INTO externalAdmterTypeT (id, typeCode, added) VALUES (6, 'locality', NULL);
INSERT INTO externalAdmterTypeT (id, typeCode, added) VALUES (7, 'locality', NULL);
INSERT INTO externalAdmterTypeT (id, typeCode, added) VALUES (8, 'locality', NULL);
INSERT INTO externalAdmterTypeT (id, typeCode, added) VALUES (9, 'locality', NULL);

COMMIT;


-- -----------------------------------------------------
-- Data for table externalAdmterTypeTL
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO externalAdmterTypeTL (id, languageId, prefix, suffix) VALUES (2, 'ukr', '', 'область');
INSERT INTO externalAdmterTypeTL (id, languageId, prefix, suffix) VALUES (2, 'rus', '', 'область');
INSERT INTO externalAdmterTypeTL (id, languageId, prefix, suffix) VALUES (2, 'eng', '', 'oblast');
INSERT INTO externalAdmterTypeTL (id, languageId, prefix, suffix) VALUES (3, 'ukr', '', 'район');
INSERT INTO externalAdmterTypeTL (id, languageId, prefix, suffix) VALUES (3, 'rus', '', 'район');
INSERT INTO externalAdmterTypeTL (id, languageId, prefix, suffix) VALUES (3, 'eng', '', 'raion');
INSERT INTO externalAdmterTypeTL (id, languageId, prefix, suffix) VALUES (4, 'ukr', '', 'район');
INSERT INTO externalAdmterTypeTL (id, languageId, prefix, suffix) VALUES (4, 'rus', '', 'район');
INSERT INTO externalAdmterTypeTL (id, languageId, prefix, suffix) VALUES (4, 'eng', '', 'district');
INSERT INTO externalAdmterTypeTL (id, languageId, prefix, suffix) VALUES (5, 'ukr', '', 'сільська рада');
INSERT INTO externalAdmterTypeTL (id, languageId, prefix, suffix) VALUES (5, 'rus', '', 'сельский совет');
INSERT INTO externalAdmterTypeTL (id, languageId, prefix, suffix) VALUES (5, 'eng', '', 'village council');
INSERT INTO externalAdmterTypeTL (id, languageId, prefix, suffix) VALUES (6, 'ukr', 'м.', '');
INSERT INTO externalAdmterTypeTL (id, languageId, prefix, suffix) VALUES (6, 'rus', 'г.', '');
INSERT INTO externalAdmterTypeTL (id, languageId, prefix, suffix) VALUES (6, 'eng', '', 'city');
INSERT INTO externalAdmterTypeTL (id, languageId, prefix, suffix) VALUES (7, 'ukr', 'смт.', '');
INSERT INTO externalAdmterTypeTL (id, languageId, prefix, suffix) VALUES (7, 'rus', 'пгт.', '');
INSERT INTO externalAdmterTypeTL (id, languageId, prefix, suffix) VALUES (7, 'eng', '', 's.m.t.');
INSERT INTO externalAdmterTypeTL (id, languageId, prefix, suffix) VALUES (8, 'ukr', 'с.', '');
INSERT INTO externalAdmterTypeTL (id, languageId, prefix, suffix) VALUES (8, 'rus', 'с.', '');
INSERT INTO externalAdmterTypeTL (id, languageId, prefix, suffix) VALUES (8, 'eng', '', 'village');
INSERT INTO externalAdmterTypeTL (id, languageId, prefix, suffix) VALUES (9, 'ukr', 'с-ще', '');
INSERT INTO externalAdmterTypeTL (id, languageId, prefix, suffix) VALUES (9, 'rus', 'пос.', '');
INSERT INTO externalAdmterTypeTL (id, languageId, prefix, suffix) VALUES (9, 'eng', '', 'hamlet');

COMMIT;


-- -----------------------------------------------------
-- Data for table `externalDataSourceT`
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO `externalDataSourceT` (`id`, `name`, `added`, `changed`) VALUES ('admter', 'Admter Database', NULL, NULL);

COMMIT;