call raiseError('needs to be run in console, one by one');

-- -----------------------------------------------------
-- Table actionStringT
-- -----------------------------------------------------
-- DROP TABLE IF EXISTS actionStringT ;

CREATE TABLE IF NOT EXISTS actionStringT (
  id VARCHAR(60) BINARY NOT NULL,
  added TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  changed TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id) )
ENGINE = InnoDB
COMMENT = 'used to denote some hard coded actions performed in system';


-- DROP TABLE IF EXISTS actionStringTL ;

CREATE TABLE IF NOT EXISTS actionStringTL (
  id VARCHAR(60) BINARY NOT NULL,
  languageId CHAR(3) NOT NULL,
  value TEXT NOT NULL,
  PRIMARY KEY (id, languageId),
  INDEX fk_actionStringTL_languageT1_idx (languageId ASC),
  CONSTRAINT fk_actionStringTL_actionStringT1
    FOREIGN KEY (id)
    REFERENCES actionStringT (id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT fk_actionStringTL_languageT1
    FOREIGN KEY (languageId)
    REFERENCES languageT (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table objectDateT
-- -----------------------------------------------------
-- DROP TABLE IF EXISTS objectDateT ;

CREATE TABLE IF NOT EXISTS objectDateT (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  objectId INT UNSIGNED NOT NULL,
  isActive ENUM('y') NULL,
  datePublished DATETIME NOT NULL,
  dateExpiry DATETIME NOT NULL,
  added TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX isactive_dateexpiry_idx (isActive ASC, dateExpiry ASC),
  UNIQUE INDEX objectid_isactive_uq (objectId ASC, isActive ASC),
  CONSTRAINT fk_objectDateT_objectT1
    FOREIGN KEY (objectId)
    REFERENCES objectT (id)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- objectDateT data
start transaction;

insert objectDateT(objectId, isActive, datePublished, dateExpiry)
select o.id, 'y', o.datePublished, o.dateExpiry
from objectT o
where o.datePublished is not null and o.dateExpiry is not null;

commit;

alter table objectT drop datePublished, drop dateExpiry;



-- -----------------------------------------------------
-- Table constT
-- -----------------------------------------------------

/*
alter table constT rename to constT_prev;

*/



-- DROP TABLE IF EXISTS constT ;

CREATE TABLE IF NOT EXISTS constT (
  id VARCHAR(60) BINARY NOT NULL,
  comment VARCHAR(255) NOT NULL DEFAULT '',
  isDisplayed ENUM('n','y') NOT NULL DEFAULT 'n',
  dataType ENUM('string', 'int', 'float') NOT NULL,
  sorter INT NOT NULL DEFAULT 0,
  valueString VARCHAR(255) NULL,
  valueInt INT NULL,
  valueFloat DOUBLE NULL,
  PRIMARY KEY (id),
  INDEX isdisplayed_idx (isDisplayed ASC),
  CONSTRAINT fk_constT_actionStringT1
    FOREIGN KEY (id)
    REFERENCES actionStringT (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- constT and actionStringT data
insert actionStringT(id)
select id from constT_prev;

insert actionStringTL(id, languageId, value)
select acs.id, l.id, acs.id
from actionStringT acs
join languageT l on 1=1;

insert constT(id, comment, isDisplayed, dataType, valueString, valueInt, valueFloat)
select 
  cp.id, 
  cp.comment, 
  'n', 
  if(cp.valueInt is not null, 'int', if(cp.valueFloat is not null, 'float', 'string')),
  cp.valueString,
  cp.valueInt,
  cp.valueFloat
from constT_prev cp;

-- drop table constT_prev;

-- don't forget to run 02_code

-- setting for map clusterization

insert actionStringT(id)
values ('ui.map.clusterization');

insert actionStringTL(id, languageId, value)
values
    ('ui.map.clusterization', 'ukr', 'Критерій кластеризації карти'),
    ('ui.map.clusterization', 'rus', 'Критерий кластеризации карты'),
    ('ui.map.clusterization', 'eng', 'Map clusterization criterion');

insert constT(id, comment, isDisplayed, dataType, valueInt)
values ('ui.map.clusterization', '', 'y', 'int', 18);


/*

insert actionStringT(id)
values
    ('test.setting.string'),
    ('test.setting.int'),
    ('test.setting.float');

insert actionStringTL(id, languageId, value)
values
    ('test.setting.string', 'ukr', 'test string setting'),
    ('test.setting.string', 'rus', 'test string setting'),
    ('test.setting.string', 'eng', 'test string setting'),
    ('test.setting.int', 'ukr', 'test int setting'),
    ('test.setting.int', 'rus', 'test int setting'),
    ('test.setting.int', 'eng', 'test int setting'),
    ('test.setting.float', 'ukr', 'test float setting'),
    ('test.setting.float', 'rus', 'test float setting'),
    ('test.setting.float', 'eng', 'test float setting');

insert constT(id, comment, isDisplayed, sorter, dataType, valueString, valueInt, valueFloat)
values 
    ('test.setting.string', '', 'y', -30, 'string', 'test', null, null),
    ('test.setting.int', '', 'y', -20, 'int', null, 1, null),
    ('test.setting.float', '', 'y', -10, 'float', null, null, 1.5);


delete from constT where id like 'test.setting.%';

*/