-- -----------------------------------------------------
-- Table Account
-- -----------------------------------------------------

CREATE TABLE Account (
  id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
  account_no VARCHAR(10) NOT NULL,
  customer_id MEDIUMINT UNSIGNED NOT NULL,
  product_id SMALLINT UNSIGNED NOT NULL,
  opening_date DATE NOT NULL,
  closing_date DATE DEFAULT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (customer_id)
    REFERENCES Customer (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  FOREIGN KEY (product_id)
    REFERENCES Product (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE
 
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE UNIQUE INDEX account_account_no_idx ON Account(account_no);
CREATE INDEX account_cust_id_idx ON Account(customer_id);
CREATE INDEX account_product_id_idx ON Account(product_id);

-- -----------------------------------------------------
-- Table SavingsAccount
-- -----------------------------------------------------

CREATE TABLE SavingsAccount (
  account_id MEDIUMINT UNSIGNED NOT NULL,
  recommended_amount MEDIUMINT UNSIGNED NOT NULL,
  PRIMARY KEY (account_id),
  FOREIGN KEY (account_id)
    REFERENCES Account (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8;

-- -----------------------------------------------------
-- Table SavingsAccountInfo
-- -----------------------------------------------------

CREATE TABLE SavingsAccountInfo (
  account_id MEDIUMINT UNSIGNED NOT NULL,
  effective_date DATE NOT NULL,
  end_date DATE NULL,
  officer_id SMALLINT UNSIGNED NOT NULL,
  status TINYINT UNSIGNED NOT NULL,
  PRIMARY KEY (account_id, effective_date),
  FOREIGN KEY (account_id)
    REFERENCES SavingsAccount (account_id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  FOREIGN KEY (officer_id)
    REFERENCES Employee (id) 
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  FOREIGN KEY (status)
    REFERENCES AccountStatusMaster (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE INDEX savingsaccountinfo_officer_id_idx ON SavingsAccountInfo(officer_id);
CREATE INDEX savingsaccountinfo_status_idx ON SavingsAccountInfo(status);


-- -----------------------------------------------------
-- Table SavingsAccountStat
-- -----------------------------------------------------

CREATE TABLE SavingsAccountStat (
  account_id MEDIUMINT UNSIGNED NOT NULL,
  total_saved_amount MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
  last_saved_on DATE DEFAULT NULL,
  interest_accrued DECIMAL(12,6) UNSIGNED NOT NULL,
  accrual_updated_on DATE NOT NULL,
  PRIMARY KEY (account_id),
  FOREIGN KEY (account_id)
    REFERENCES SavingsAccount (account_id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8;

SET foreign_key_checks = 0;

ALTER TABLE DepositAccountInfo
DROP FOREIGN KEY DepositAccountInfo_ibfk_1;
ALTER TABLE DepositAccountStat
DROP FOREIGN KEY DepositAccountStat_ibfk_1;

INSERT INTO Account SELECT id, account_no, customer_id, product_id, opening_date, closing_date FROM DepositAccount;

ALTER TABLE DepositAccount 
DROP FOREIGN KEY DepositAccount_ibfk_1,
DROP FOREIGN KEY DepositAccount_ibfk_2,
DROP INDEX depositaccount_account_no_idx,
DROP INDEX depositaccount_cust_id_idx,
DROP INDEX depositaccount_product_id_idx,
DROP COLUMN account_no,
DROP COLUMN customer_id,
DROP COLUMN product_id,
Drop COLUMN opening_date,
Drop COLUMN closing_date,
CHANGE COLUMN id account_id MEDIUMINT UNSIGNED NOT NULL,
DROP PRIMARY KEY,
ADD PRIMARY KEY (account_id),
ADD FOREIGN KEY (account_id)
    REFERENCES Account (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE;

ALTER TABLE DepositAccountInfo
ADD FOREIGN KEY (account_id)
    REFERENCES DepositAccount (account_id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE;

ALTER TABLE DepositAccountStat
ADD FOREIGN KEY (account_id)
    REFERENCES DepositAccount (account_id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE;

ALTER TABLE SavingsProduct 
CHANGE COLUMN minimum_balance minimum_balance mediumint unsigned NOT NULL DEFAULT 0;
UPDATE SavingsProduct SET minimum_balance = 0 WHERE minimum_balance IS NULL;

SET foreign_key_checks = 1;
