-- Loan module migration for existing Gold Loan databases.
-- Run this once after branch_module.sql and customer_module.sql.

CREATE TABLE IF NOT EXISTS `loans` (
  `loan_id` int NOT NULL AUTO_INCREMENT,
  `loan_branch_id` int DEFAULT NULL,
  `loan_customer_id` int NOT NULL,
  `loan_tenure_months` int NOT NULL,
  `loan_start_date` date DEFAULT NULL,
  `loan_gold_description` text NOT NULL,
  `loan_gold_value` decimal(12,2) NOT NULL DEFAULT '0.00',
  `loan_gold_rate` decimal(12,2) NOT NULL DEFAULT '0.00',
  `loan_file_charges` decimal(12,2) NOT NULL DEFAULT '0.00',
  `loan_amount` decimal(12,2) NOT NULL DEFAULT '0.00',
  `loan_roi` decimal(6,2) NOT NULL DEFAULT '0.00',
  `loan_emi_amount` decimal(12,2) NOT NULL DEFAULT '0.00',
  `loan_total_interest` decimal(12,2) NOT NULL DEFAULT '0.00',
  `loan_total_payable` decimal(12,2) NOT NULL DEFAULT '0.00',
  `loan_status` enum('active','closed','cancelled','captured') NOT NULL DEFAULT 'active',
  `loan_created_by` int DEFAULT NULL,
  `loan_dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`loan_id`),
  KEY `loan_branch_id` (`loan_branch_id`),
  KEY `loan_customer_id` (`loan_customer_id`),
  KEY `loan_created_by` (`loan_created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `loan_gold_captures` (
  `cap_id` int NOT NULL AUTO_INCREMENT,
  `cap_loan_id` int NOT NULL,
  `cap_date` date NOT NULL,
  `cap_gold_value` decimal(12,2) NOT NULL DEFAULT '0.00',
  `cap_remarks` text,
  `cap_by` int DEFAULT NULL,
  `cap_dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`cap_id`),
  KEY `cap_loan_id` (`cap_loan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `loan_gold_images` (
  `img_id` int NOT NULL AUTO_INCREMENT,
  `img_loan_id` int NOT NULL,
  `img_file` varchar(255) NOT NULL,
  `img_original_name` varchar(255) NOT NULL,
  `img_uploaded_by` int DEFAULT NULL,
  `img_dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`img_id`),
  KEY `img_loan_id` (`img_loan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `loan_emis` (
  `emi_id` int NOT NULL AUTO_INCREMENT,
  `emi_loan_id` int NOT NULL,
  `emi_no` int NOT NULL,
  `emi_due_date` date NOT NULL,
  `emi_amount` decimal(12,2) NOT NULL DEFAULT '0.00',
  `emi_principal` decimal(12,2) NOT NULL DEFAULT '0.00',
  `emi_interest` decimal(12,2) NOT NULL DEFAULT '0.00',
  `emi_overdue_interest` decimal(12,2) NOT NULL DEFAULT '0.00',
  `emi_waveoff_amount` decimal(12,2) NOT NULL DEFAULT '0.00',
  `emi_last_interest_date` date DEFAULT NULL,
  `emi_balance` decimal(12,2) NOT NULL DEFAULT '0.00',
  `emi_status` enum('unpaid','paid') NOT NULL DEFAULT 'unpaid',
  `emi_paid_date` date DEFAULT NULL,
  `emi_dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`emi_id`),
  KEY `emi_loan_id` (`emi_loan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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;

CREATE TABLE IF NOT EXISTS `loan_emi_payments` (
  `pay_id` int NOT NULL AUTO_INCREMENT,
  `pay_emi_id` int NOT NULL,
  `pay_loan_id` int NOT NULL,
  `pay_amount_type` enum('Online','UPI','Cash','Other') NOT NULL DEFAULT 'Cash',
  `pay_amount` decimal(12,2) NOT NULL DEFAULT '0.00',
  `pay_date` date NOT NULL,
  `pay_transaction_id` varchar(120) DEFAULT NULL,
  `pay_remarks` text,
  `pay_received_by` int DEFAULT NULL,
  `pay_dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`pay_id`),
  KEY `pay_emi_id` (`pay_emi_id`),
  KEY `pay_loan_id` (`pay_loan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;