-- =============================================================
-- Phase 9 — EMI Collection & Schedule
--
-- Table added:
--   emi_installments  (monthly installments generated from a
--                      disbursed finance application)
--
-- Each disbursed finance application can have a generated EMI
-- schedule. One row per installment stores the due date, the
-- amortized principal / interest split and, once collected, the
-- payment details. Collections are tracked here directly rather
-- than in `payments` so the sale balance is not double-counted
-- against the disbursed loan amount.
--
-- Run through install.php (idempotent) — do NOT apply raw via
-- phpMyAdmin, the installer matches the existing used-car-pro tables.
-- =============================================================

CREATE TABLE IF NOT EXISTS `emi_installments` (
    `id`               INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `finance_id`       INT UNSIGNED NOT NULL,
    `installment_no`   INT UNSIGNED NOT NULL,
    `due_date`         DATE NOT NULL,
    `emi_amount`       DECIMAL(12,2) NOT NULL DEFAULT 0,
    `principal_amount` DECIMAL(12,2) NOT NULL DEFAULT 0,
    `interest_amount`  DECIMAL(12,2) NOT NULL DEFAULT 0,
    `status`           ENUM('Pending','Paid','Waived') NOT NULL DEFAULT 'Pending',
    `paid_date`        DATE DEFAULT NULL,
    `paid_amount`      DECIMAL(12,2) DEFAULT NULL,
    `payment_method`   VARCHAR(30) DEFAULT NULL,
    `transaction_no`   VARCHAR(60) DEFAULT NULL,
    `remarks`          TEXT,
    `created_at`       DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at`       DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_emi_installments_finance_id` (`finance_id`),
    KEY `idx_emi_installments_status` (`status`),
    KEY `idx_emi_installments_due_date` (`due_date`),
    CONSTRAINT `fk_emi_installments_finance_id` FOREIGN KEY (`finance_id`) REFERENCES `finance_applications` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;