-- -----------------------------------------------------
-- Table zzzNodeQueueT
-- -----------------------------------------------------
DROP TABLE IF EXISTS zzzNodeQueueT ;

CREATE TABLE IF NOT EXISTS zzzNodeQueueT (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  nodeId INT UNSIGNED NOT NULL,
  flag CHAR(10) BINARY NOT NULL DEFAULT ' ',
  PRIMARY KEY (id) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table currencyT
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS currencyT (
  id VARCHAR(4) BINARY NOT NULL,
  isDefault ENUM('y') NULL,
  sorter INT NOT NULL DEFAULT 0,
  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;


-- -----------------------------------------------------
-- Table currencyTL
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS currencyTL (
  id VARCHAR(4) BINARY NOT NULL,
  languageId CHAR(3) NOT NULL,
  name VARCHAR(50) NOT NULL,
  textAA VARCHAR(50) NOT NULL DEFAULT '' COMMENT 'put before uofmPre',
  textAZ VARCHAR(50) NOT NULL DEFAULT '' COMMENT 'put after uofmPre',
  textZA VARCHAR(50) NOT NULL DEFAULT '' COMMENT 'put before uofmPost',
  textZZ VARCHAR(50) NOT NULL DEFAULT '' COMMENT 'put after uofmPost',
  PRIMARY KEY (id, languageId) ,
  INDEX fk_currencyTL_languageT1_idx (languageId ASC) ,
  CONSTRAINT fk_currencyTL_currencyT1
    FOREIGN KEY (id)
    REFERENCES currencyT (id)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT fk_currencyTL_languageT1
    FOREIGN KEY (languageId)
    REFERENCES languageT (id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table attributePriceT
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS attributePriceT (
  id INT UNSIGNED NOT NULL,
  priceRound INT NOT NULL,
  PRIMARY KEY (id) ,
  CONSTRAINT fk_attributePriceT_attributeT1
    FOREIGN KEY (id)
    REFERENCES attributeT (id)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table currencyExchangeRateT
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS currencyExchangeRateT (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  currencyId VARCHAR(4) BINARY NOT NULL,
  effectiveDate DATETIME NOT NULL,
  rate DECIMAL(22,7) NOT NULL,
  added TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id) ,
  INDEX fk_currencyExchangeRateT_currencyT1_idx (currencyId ASC) ,
  CONSTRAINT fk_currencyExchangeRateT_currencyT1
    FOREIGN KEY (currencyId)
    REFERENCES currencyT (id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table attributeValuePriceT
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS attributeValuePriceT (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  attributeId INT UNSIGNED NOT NULL,
  objectId INT UNSIGNED NOT NULL,
  currencyId VARCHAR(4) BINARY NOT NULL,
  value DECIMAL(22,6) NOT NULL,
  valueRaw DECIMAL(22,6) NULL COMMENT 'is null for calculated price',
  currencyExchangeRateId BIGINT UNSIGNED NULL,
  added TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  changed TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX fk_attributeValuePriceT_objectT1_idx (objectId ASC) ,
  INDEX fk_attributeValuePriceT_currencyT1_idx (currencyId ASC) ,
  PRIMARY KEY (id) ,
  INDEX fk_attributeValuePriceT_attributePriceT1_idx (attributeId ASC) ,
  UNIQUE INDEX attributeid_objectid_currencyid_uq (attributeId ASC, objectId ASC, currencyId ASC) ,
  INDEX fk_attributeValuePriceT_currencyExchangeRateT1_idx (currencyExchangeRateId ASC) ,
  CONSTRAINT fk_attributeValuePriceT_objectT1
    FOREIGN KEY (objectId)
    REFERENCES objectT (id)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT fk_attributeValuePriceT_currencyT1
    FOREIGN KEY (currencyId)
    REFERENCES currencyT (id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_attributeValuePriceT_attributePriceT1
    FOREIGN KEY (attributeId)
    REFERENCES attributePriceT (id)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT fk_attributeValuePriceT_currencyExchangeRateT1
    FOREIGN KEY (currencyExchangeRateId)
    REFERENCES currencyExchangeRateT (id)
    ON DELETE SET NULL
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table searchCriterionPriceT
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS searchCriterionPriceT (
  searchCriterionId BIGINT UNSIGNED NOT NULL,
  currencyId VARCHAR(4) BINARY NOT NULL,
  valueLow DECIMAL(22,6) NULL,
  valueHigh DECIMAL(22,6) NULL,
  PRIMARY KEY (searchCriterionId) ,
  INDEX fk_searchCriterionPriceT_currencyT1_idx (currencyId ASC) ,
  CONSTRAINT fk_searchCriterionPriceT_currencyT1
    FOREIGN KEY (currencyId)
    REFERENCES currencyT (id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_searchCriterionPriceT_searchCriterionT1
    FOREIGN KEY (searchCriterionId)
    REFERENCES searchCriterionT (id)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table cacheAttributeValuePriceT
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS cacheAttributeValuePriceT (
  attributeId INT UNSIGNED NOT NULL,
  objectId INT UNSIGNED NOT NULL,
  value TEXT NOT NULL,
  PRIMARY KEY (attributeId, objectId) ,
  INDEX fk_cacheAttributeValuePriceT_objectT1_idx (objectId ASC) ,
  CONSTRAINT fk_cacheAttributeValuePriceT_attributePriceT1
    FOREIGN KEY (attributeId)
    REFERENCES attributePriceT (id)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT fk_cacheAttributeValuePriceT_objectT1
    FOREIGN KEY (objectId)
    REFERENCES objectT (id)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- data
-- -----------------------------------------------------

-- new attribute data type

start transaction;

insert ignore internalDataTypeT(id) values ('price');

commit;



-- fill in currencies

start transaction;

select sorter_Set(10, 10);

insert currencyT(id, isDefault, sorter, added, changed)
values 
    ('UAH', 'y', sorter_Get(), now(), now()),
    ('USD', null, sorter_Get(), now(), now()),
    ('EUR', null, sorter_Get(), now(), now());

insert currencyTL(id, languageId, name, textAA, textAZ, textZA, textZZ)
values
    ('UAH', 'ukr', 'Гривня',  default, default, 'грн',   default),
    ('UAH', 'rus', 'Гривна',  default, default, 'грн',   default),
    ('UAH', 'eng', 'Hryvnia', default, 'UAH',   default, default),

    ('USD', 'ukr', 'Долар США',  default, default, '$',     default),
    ('USD', 'rus', 'Доллар США', default, default, '$',     default),
    ('USD', 'eng', 'US Dollar',  default, '$',     default, default),

    ('EUR', 'ukr', 'Євро', default, default, 'євро',  default),
    ('EUR', 'rus', 'Евро', default, default, 'евро',  default),
    ('EUR', 'eng', 'Euro', default, 'EUR',   default, default);

commit;


-- fill in exchange rates

start transaction;

insert currencyExchangeRateT(currencyId, effectiveDate, rate)
values
    ('USD', '2016-07-20', 24.814308),
    ('EUR', '2016-07-20', 27.382589);

commit;


-- convert numeric price attributes into currency powered attributes

start transaction;

create temporary table att(id int unsigned not null primary key)
select a.id from attributeT a where a.code in ('price', 'pricerent', 'pricerentdaily');


update attributeT a join att on a.id = att.id set
    dataTypeId = 'price';


insert attributePriceT(id, priceRound)
select att.id, 0 from att;


insert attributeValuePriceT(attributeId, objectId, currencyId, value, valueRaw)
select
    avn.attributeId,
    avn.objectId,
    'UAH',
    avn.value,
    avn.value
from attributeValueNumericT avn
join att on avn.attributeId = att.id;


delete from attributeNumericT 
using attributeNumericT
join att on attributeNumericT.id = att.id;


drop temporary table att;

commit;


-- RUN AFTER 02_code applied
/*
start transaction;

select __object_UpdateSortCacheAll();
select __currency_CalculateAll();

commit;
*/