CREATE TABLE AccountWaiverTypeMaster (
  id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  product_category_id TINYINT UNSIGNED NOT NULL,
  tx_type_id TINYINT UNSIGNED NOT NULL,
  name VARCHAR(100) NOT NULL,
  ui_name VARCHAR(100) DEFAULT NULL,
  system_defined TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  FOREIGN KEY (tx_type_id)
    REFERENCES TransactionTypeMaster (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  FOREIGN KEY (product_category_id)
    REFERENCES ProductCategoryMaster (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE UNIQUE INDEX accountwaivertype_prod_name_idx ON AccountWaiverTypeMaster(product_category_id, name);
CREATE UNIQUE INDEX accountwaivertype_prod_tx_type_idx ON AccountWaiverTypeMaster(product_category_id, tx_type_id);


CREATE TABLE DemandStatusMaster (
  id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE UNIQUE INDEX demandstatus_name_idx ON DemandStatusMaster(name);


INSERT INTO DemandStatusMaster(id, name) VALUES
(1, 'System Generated'),
(2, 'Added Directly'),
(3, 'System Generated And Waived'),
(4, 'Added Directly And Waived'),
(5, 'Cancelled');

UPDATE AccountDemand SET `is_valid`=5 WHERE `is_valid`=0;

ALTER TABLE AccountDemand CHANGE COLUMN `is_valid` `status` TINYINT UNSIGNED NOT NULL;
ALTER TABLE AccountDemand ADD CONSTRAINT `AccountDemand_ibfk_4` FOREIGN KEY (`status`) REFERENCES `DemandStatusMaster` (`id` ) ON DELETE NO ACTION ON UPDATE CASCADE;

UPDATE AccountDemandUnpaid SET `is_valid`=5 WHERE `is_valid`=0;

ALTER TABLE AccountDemandUnpaid CHANGE COLUMN `is_valid` `status` TINYINT UNSIGNED NOT NULL;
ALTER TABLE AccountDemandUnpaid ADD CONSTRAINT `AccountDemandUnpaid_ibfk_4` FOREIGN KEY (`status`) REFERENCES `DemandStatusMaster` (`id` ) ON DELETE NO ACTION ON UPDATE CASCADE;

CREATE TABLE AccountDemandWaiver (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  demand_id INTEGER UNSIGNED NULL,
  date DATE NOT NULL,
  amount DECIMAL(15,6) NOT NULL,
  posting_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  user_id MEDIUMINT UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (demand_id)
    REFERENCES AccountDemand (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  FOREIGN KEY (user_id)
    REFERENCES User (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE UNIQUE INDEX accountdemandwaiver_demand_date_idx ON AccountDemandWaiver (demand_id, date);
