ALTER TABLE Account
	 ADD COLUMN migration_date DATE DEFAULT NULL,
	 MODIFY account_no VARCHAR(15) NOT NULL;

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

ALTER TABLE LoanAccountApprove DROP foreign key LoanAccountApprove_ibfk_1;
ALTER TABLE LoanAccountAssociate DROP foreign key LoanAccountAssociate_ibfk_1;
ALTER TABLE LoanAccountCenter DROP foreign key LoanAccountCenter_ibfk_1;
ALTER TABLE LoanAccountCheque DROP foreign key LoanAccountCheque_ibfk_1;
ALTER TABLE LoanAccountCollateral DROP foreign key LoanAccountCollateral_ibfk_1;
ALTER TABLE LoanAccountDecline DROP foreign key LoanAccountDecline_ibfk_1;
ALTER TABLE LoanAccountFee DROP foreign key LoanAccountFee_ibfk_1;
ALTER TABLE LoanAccountGroup DROP foreign key LoanAccountGroup_ibfk_1;
ALTER TABLE LoanAccountInfo DROP foreign key LoanAccountInfo_ibfk_1;
ALTER TABLE LoanAccountOfficer DROP foreign key LoanAccountOfficer_ibfk_1;
ALTER TABLE LoanAccountSchedule DROP foreign key LoanAccountSchedule_ibfk_1;
ALTER TABLE LoanAccountStatHistory DROP foreign key LoanAccountStatHistory_ibfk_1;
ALTER TABLE LoanAccountStat DROP foreign key LoanAccountStat_ibfk_1;
ALTER TABLE LoanAttendance DROP foreign key LoanAttendance_ibfk_1;
ALTER TABLE LoanCreditNote DROP foreign key LoanCreditNote_ibfk_1;
ALTER TABLE LoanCreditRemark DROP foreign key LoanCreditRemark_ibfk_1;
ALTER TABLE LoanMoratorium DROP foreign key LoanMoratorium_ibfk_2;
ALTER TABLE LoanScheduleAmountRef DROP foreign key LoanScheduleAmountRef_ibfk_3;
ALTER TABLE LoanScheduleDateRef DROP foreign key LoanScheduleDateRef_ibfk_3;

ALTER TABLE LoanDemand DROP foreign key LoanDemand_ibfk_1;
ALTER TABLE LoanDemandUnpaid DROP foreign key LoanDemandUnpaid_ibfk_1;
ALTER TABLE LoanPayment DROP foreign key LoanPayment_ibfk_1;
ALTER TABLE LoanSystemTransaction DROP foreign key LoanSystemTransaction_ibfk_1;
ALTER TABLE LoanTransaction DROP foreign key LoanTransaction_ibfk_1;

ALTER TABLE LoanAccount 
	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 UPDATE CASCADE ON DELETE NO ACTION,
	DROP FOREIGN KEY LoanAccount_ibfk_1,
 	DROP FOREIGN KEY LoanAccount_ibfk_2,
	DROP COLUMN account_no,
	DROP COLUMN customer_id,
	DROP COLUMN product_id,
	DROP COLUMN opening_date,
	DROP COLUMN closing_date,
	DROP COLUMN migration_date;

ALTER TABLE LoanAccountApprove ADD CONSTRAINT `LoanAccountApprove_ibfk_1` FOREIGN KEY (account_id) REFERENCES LoanAccount (account_id) ON DELETE NO ACTION ON UPDATE CASCADE;
ALTER TABLE LoanAccountAssociate ADD CONSTRAINT `LoanAccountAssociate_ibfk_1` FOREIGN KEY (account_id) REFERENCES LoanAccount (account_id) ON DELETE NO ACTION ON UPDATE CASCADE;
ALTER TABLE LoanAccountCenter ADD CONSTRAINT `LoanAccountCenter_ibfk_1` FOREIGN KEY (account_id) REFERENCES LoanAccount (account_id) ON DELETE NO ACTION ON UPDATE CASCADE;
ALTER TABLE LoanAccountCheque ADD CONSTRAINT `LoanAccountCheque_ibfk_1` FOREIGN KEY (account_id) REFERENCES LoanAccount (account_id) ON DELETE NO ACTION ON UPDATE CASCADE;
ALTER TABLE LoanAccountCollateral ADD CONSTRAINT `LoanAccountCollateral_ibfk_1` FOREIGN KEY (account_id) REFERENCES LoanAccount (account_id) ON DELETE NO ACTION ON UPDATE CASCADE;
ALTER TABLE LoanAccountDecline ADD CONSTRAINT `LoanAccountDecline_ibfk_1` FOREIGN KEY (account_id) REFERENCES LoanAccount (account_id) ON DELETE NO ACTION ON UPDATE CASCADE;
ALTER TABLE LoanAccountFee ADD CONSTRAINT `LoanAccountFee_ibfk_1` FOREIGN KEY (account_id) REFERENCES LoanAccount (account_id) ON DELETE NO ACTION ON UPDATE CASCADE;
ALTER TABLE LoanAccountGroup ADD CONSTRAINT `LoanAccountGroup_ibfk_1` FOREIGN KEY (account_id) REFERENCES LoanAccount (account_id) ON DELETE NO ACTION ON UPDATE CASCADE;
ALTER TABLE LoanAccountInfo ADD CONSTRAINT `LoanAccountInfo_ibfk_1` FOREIGN KEY (account_id) REFERENCES LoanAccount (account_id) ON DELETE NO ACTION ON UPDATE CASCADE;
ALTER TABLE LoanAccountOfficer ADD CONSTRAINT `LoanAccountOfficer_ibfk_1` FOREIGN KEY (account_id) REFERENCES LoanAccount (account_id) ON DELETE NO ACTION ON UPDATE CASCADE;
ALTER TABLE LoanAccountSchedule ADD CONSTRAINT `LoanAccountSchedule_ibfk_1` FOREIGN KEY (account_id) REFERENCES LoanAccount (account_id) ON DELETE NO ACTION ON UPDATE CASCADE;
ALTER TABLE LoanAccountStatHistory ADD CONSTRAINT `LoanAccountStatHistory_ibfk_1` FOREIGN KEY (account_id) REFERENCES LoanAccount (account_id) ON DELETE NO ACTION ON UPDATE CASCADE;
ALTER TABLE LoanAccountStat ADD CONSTRAINT `LoanAccountStat_ibfk_1` FOREIGN KEY (account_id) REFERENCES LoanAccount (account_id) ON DELETE NO ACTION ON UPDATE CASCADE;
ALTER TABLE LoanAttendance ADD CONSTRAINT `LoanAttendance_ibfk_1` FOREIGN KEY (account_id) REFERENCES LoanAccount (account_id) ON DELETE NO ACTION ON UPDATE CASCADE;
ALTER TABLE LoanCreditNote ADD CONSTRAINT `LoanCreditNote_ibfk_1` FOREIGN KEY (account_id) REFERENCES LoanAccount (account_id) ON DELETE NO ACTION ON UPDATE CASCADE;
ALTER TABLE LoanCreditRemark ADD CONSTRAINT `LoanCreditRemark_ibfk_1` FOREIGN KEY (account_id) REFERENCES LoanAccount (account_id) ON DELETE NO ACTION ON UPDATE CASCADE;
ALTER TABLE LoanMoratorium ADD CONSTRAINT `LoanMoratorium_ibfk_2` FOREIGN KEY (account_id) REFERENCES LoanAccount (account_id) ON DELETE NO ACTION ON UPDATE CASCADE;
ALTER TABLE LoanScheduleAmountRef ADD CONSTRAINT `LoanScheduleAmountRef_ibfk_3` FOREIGN KEY (account_id) REFERENCES LoanAccount (account_id) ON DELETE NO ACTION ON UPDATE CASCADE;
ALTER TABLE LoanScheduleDateRef ADD CONSTRAINT `LoanScheduleDateRef_ibfk_3` FOREIGN KEY (account_id) REFERENCES LoanAccount (account_id) ON DELETE NO ACTION ON UPDATE CASCADE;

ALTER TABLE LoanDemand ADD CONSTRAINT `LoanDemand_ibfk_1` FOREIGN KEY (account_id) REFERENCES LoanAccount (account_id) ON DELETE NO ACTION ON UPDATE CASCADE;
ALTER TABLE LoanDemandUnpaid ADD CONSTRAINT `LoanDemandUnpaid_ibfk_1` FOREIGN KEY (account_id) REFERENCES LoanAccount (account_id) ON DELETE NO ACTION ON UPDATE CASCADE;
ALTER TABLE LoanPayment ADD CONSTRAINT `LoanPayment_ibfk_1` FOREIGN KEY (account_id) REFERENCES LoanAccount (account_id) ON DELETE NO ACTION ON UPDATE CASCADE;
ALTER TABLE LoanSystemTransaction ADD CONSTRAINT `LoanSystemTransaction_ibfk_1` FOREIGN KEY (account_id) REFERENCES LoanAccount (account_id) ON DELETE NO ACTION ON UPDATE CASCADE;
ALTER TABLE LoanTransaction ADD CONSTRAINT `LoanTransaction_ibfk_1` FOREIGN KEY (account_id) REFERENCES LoanAccount (account_id) ON DELETE NO ACTION ON UPDATE CASCADE;



