-- SQL to add installment-related columns to the loans table
-- Run these queries to update your existing loans table

-- 1. Add new installment-related columns
ALTER TABLE loans 
ADD COLUMN IF NOT EXISTS payment_method VARCHAR(50) DEFAULT 'installment',
ADD COLUMN IF NOT EXISTS installment_amount DECIMAL(12,2),
ADD COLUMN IF NOT EXISTS total_installments INTEGER,
ADD COLUMN IF NOT EXISTS paid_installments INTEGER DEFAULT 0,
ADD COLUMN IF NOT EXISTS last_payment_date TIMESTAMP WITH TIME ZONE,
ADD COLUMN IF NOT EXISTS late_fees DECIMAL(12,2) DEFAULT 0,
ADD COLUMN IF NOT EXISTS grace_period INTEGER DEFAULT 5;

-- 2. Add constraint for payment_method enum
ALTER TABLE loans 
DROP CONSTRAINT IF EXISTS loans_payment_method_check;

ALTER TABLE loans 
ADD CONSTRAINT loans_payment_method_check 
CHECK (payment_method IN ('full', 'installment', 'custom'));

-- 3. Update existing loans to have default values
UPDATE loans 
SET 
    payment_method = 'installment',
    total_installments = 12,
    paid_installments = 0
WHERE payment_method IS NULL;

-- 4. Verify the changes
SELECT 
    id,
    loan_amount,
    total_paid,
    remaining_balance,
    payment_method,
    installment_amount,
    total_installments,
    paid_installments,
    status
FROM loans
ORDER BY created_at DESC; 