-- -----------------------------------------------------
-- Table InsuranceCategoryMaster
-- -----------------------------------------------------
DROP TABLE IF EXISTS InsuranceCategoryMaster;

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

--
-- Master table values for InsuranceCategoryMaster
--
LOCK TABLES InsuranceCategoryMaster WRITE;
DELETE FROM InsuranceCategoryMaster;
INSERT INTO InsuranceCategoryMaster(id, name) VALUES
(1, 'Health Insurance');
UNLOCK TABLES;


-- -----------------------------------------------------
-- Table InsuranceProduct
-- -----------------------------------------------------
DROP TABLE IF EXISTS InsuranceProduct;

CREATE TABLE InsuranceProduct (
  product_id SMALLINT UNSIGNED NOT NULL,
  cycle TINYINT UNSIGNED NOT NULL,
  class_id TINYINT UNSIGNED NOT NULL,
  category_id TINYINT UNSIGNED NOT NULL,
  company_id TINYINT UNSIGNED NOT NULL,
  min_sum_insured MEDIUMINT UNSIGNED NOT NULL,
  max_sum_insured MEDIUMINT UNSIGNED NOT NULL,
  tenure_id TINYINT UNSIGNED NOT NULL,
  min_tenure SMALLINT UNSIGNED NOT NULL,
  max_tenure SMALLINT UNSIGNED NOT NULL,
  
  PRIMARY KEY (product_id),
  FOREIGN KEY (product_id)
    REFERENCES Product (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  FOREIGN KEY (category_id)
    REFERENCES InsuranceCategoryMaster (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  FOREIGN KEY (company_id)
    REFERENCES InsuranceCompanyMaster (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  FOREIGN KEY (tenure_id)
    REFERENCES TenureMaster (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8;


-- -----------------------------------------------------
-- Table InsuranceProductInfo
-- -----------------------------------------------------
DROP TABLE IF EXISTS InsuranceProductInfo;

CREATE TABLE InsuranceProductInfo (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  product_id SMALLINT UNSIGNED NOT NULL,
  payment_freq_id TINYINT UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (product_id)
    REFERENCES InsuranceProduct (product_id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  FOREIGN KEY (payment_freq_id)
    REFERENCES FrequencyMaster (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE UNIQUE INDEX insuranceproductinfo_prod_comp_idx ON InsuranceProductInfo(product_id, payment_freq_id);


-- -----------------------------------------------------
-- Table InsuranceProductPremium
-- -----------------------------------------------------
DROP TABLE IF EXISTS InsuranceProductPremium;

CREATE TABLE InsuranceProductPremium (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  product_id SMALLINT UNSIGNED NOT NULL,
  min_age TINYINT UNSIGNED NOT NULL,
  max_age TINYINT UNSIGNED NOT NULL,
  premium MEDIUMINT UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (product_id)
    REFERENCES InsuranceProduct (product_id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8;


INSERT INTO ProductCategoryMaster (id, name) VALUES (4, 'Insurance');


