-- Famlynk Loan Module Schema (MySQL / InnoDB / utf8mb4)
-- Run against ggn_famlynk

ALTER TABLE loans
  ADD COLUMN IF NOT EXISTS loan_name VARCHAR(160) NULL AFTER member_id,
  ADD COLUMN IF NOT EXISTS description TEXT NULL AFTER bank_name,
  ADD COLUMN IF NOT EXISTS total_payable_amount DECIMAL(14,2) NULL AFTER principal_amount,
  ADD COLUMN IF NOT EXISTS interest_payable_amount DECIMAL(14,2) NULL AFTER total_payable_amount,
  ADD COLUMN IF NOT EXISTS emi_day INT NULL AFTER start_date,
  ADD COLUMN IF NOT EXISTS closing_date DATE NULL AFTER emi_day,
  ADD COLUMN IF NOT EXISTS status VARCHAR(30) NOT NULL DEFAULT 'active' AFTER closing_date;

CREATE INDEX IF NOT EXISTS idx_loans_family_status ON loans (family_id, status);

CREATE TABLE IF NOT EXISTS loan_payments (
  payment_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  loan_id BIGINT NOT NULL,
  family_id BIGINT NOT NULL,
  payment_date DATE NOT NULL,
  payment_amount DECIMAL(14,2) NOT NULL,
  payment_type VARCHAR(30) NOT NULL,
  remaining_balance DECIMAL(14,2) NOT NULL,
  created_by BIGINT NULL,
  updated_by BIGINT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_loan_payments_loan_date (loan_id, payment_date),
  INDEX idx_loan_payments_family (family_id),
  CONSTRAINT chk_loan_payment_type CHECK (payment_type IN ('EMI', 'EXTRA_PAYMENT'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS loan_interest_history (
  interest_change_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  loan_id BIGINT NOT NULL,
  family_id BIGINT NOT NULL,
  old_interest_rate DECIMAL(7,3) NOT NULL,
  new_interest_rate DECIMAL(7,3) NOT NULL,
  effective_date DATE NOT NULL,
  created_by BIGINT NULL,
  updated_by BIGINT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_loan_interest_history_loan_effective (loan_id, effective_date),
  INDEX idx_loan_interest_history_family (family_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS loan_schedule (
  schedule_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  loan_id BIGINT NOT NULL,
  family_id BIGINT NOT NULL,
  month_number INT NOT NULL,
  emi_date DATE NOT NULL,
  principal_component DECIMAL(14,2) NOT NULL,
  interest_component DECIMAL(14,2) NOT NULL,
  emi_amount DECIMAL(14,2) NOT NULL,
  remaining_balance DECIMAL(14,2) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  created_by BIGINT NULL,
  updated_by BIGINT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_loan_schedule_loan_month (loan_id, month_number),
  INDEX idx_loan_schedule_loan_status (loan_id, status),
  INDEX idx_loan_schedule_family (family_id),
  CONSTRAINT chk_loan_schedule_status CHECK (status IN ('pending', 'paid'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;