-- Adds overdue interest and wave-off support for existing loan EMI tables.

DROP PROCEDURE IF EXISTS add_emi_column;

DELIMITER //
CREATE PROCEDURE add_emi_column(IN column_name VARCHAR(64), IN column_definition TEXT)
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'loan_emis'
      AND COLUMN_NAME = column_name
  ) THEN
    SET @sql = CONCAT('ALTER TABLE `loan_emis` ADD COLUMN ', column_definition);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
END//
DELIMITER ;

CALL add_emi_column('emi_overdue_interest', "`emi_overdue_interest` decimal(12,2) NOT NULL DEFAULT '0.00' AFTER `emi_interest`");
CALL add_emi_column('emi_waveoff_amount', "`emi_waveoff_amount` decimal(12,2) NOT NULL DEFAULT '0.00' AFTER `emi_overdue_interest`");
CALL add_emi_column('emi_last_interest_date', '`emi_last_interest_date` date DEFAULT NULL AFTER `emi_waveoff_amount`');

DROP PROCEDURE IF EXISTS add_emi_column;

CREATE TABLE IF NOT EXISTS `loan_emi_waveoffs` (
  `wave_id` int NOT NULL AUTO_INCREMENT,
  `wave_emi_id` int NOT NULL,
  `wave_loan_id` int NOT NULL,
  `wave_amount` decimal(12,2) NOT NULL DEFAULT '0.00',
  `wave_remarks` text,
  `wave_by` int DEFAULT NULL,
  `wave_dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`wave_id`),
  KEY `wave_emi_id` (`wave_emi_id`),
  KEY `wave_loan_id` (`wave_loan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;