-- ============================================
-- LOAN APP - Schema Update for Weekly Loans
-- Run this ONCE on your existing database
-- ============================================

-- Add loan_type column to Loans table
ALTER TABLE `Loans`
  ADD COLUMN `loan_type` ENUM('daily','weekly') NOT NULL DEFAULT 'daily' AFTER `premium`,
  ADD COLUMN `issue_first_payment` DECIMAL(10,2) NULL AFTER `loan_type`;

-- Weekly Payment Schedule table
-- Stores each installment due date for weekly loans
CREATE TABLE IF NOT EXISTS `WeeklySchedule` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `loan_id` INT NOT NULL,
  `installment_no` INT NOT NULL COMMENT '1=issue day, 2=day5, ..., 13=day60',
  `payment_date` DATE NOT NULL,
  `amount_due` DECIMAL(10,2) NOT NULL,
  `is_paid` TINYINT(1) NOT NULL DEFAULT 0,
  `paid_date` DATE NULL,
  `payment_id` INT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_loan_date` (`loan_id`, `payment_date`),
  KEY `idx_payment_date` (`payment_date`),
  CONSTRAINT `ws_loan_fk` FOREIGN KEY (`loan_id`) REFERENCES `Loans` (`loan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- SMS Log table (optional - tracks sent reminders)
CREATE TABLE IF NOT EXISTS `SmsLog` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `loan_id` INT NOT NULL,
  `phone` VARCHAR(20) NOT NULL,
  `message` TEXT NOT NULL,
  `sent_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `status` ENUM('sent','failed','pending') DEFAULT 'pending',
  PRIMARY KEY (`id`),
  KEY `idx_loan` (`loan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- VERIFY (optional - check columns were added)
-- ============================================
-- DESCRIBE Loans;
-- SHOW TABLES;
