-- Quick fix for production database - Add missing penalty columns
-- This script is safe to run multiple times

-- Add penalty_mode to loan_products
ALTER TABLE loan_products 
ADD COLUMN IF NOT EXISTS penalty_mode VARCHAR(20) NOT NULL DEFAULT 'automatic';

-- Add penalty_frequency to loan_products  
ALTER TABLE loan_products 
ADD COLUMN IF NOT EXISTS penalty_frequency VARCHAR(20) NOT NULL DEFAULT 'daily';

-- Ensure late_payment_penalty has correct type
ALTER TABLE loan_products 
MODIFY COLUMN late_payment_penalty DECIMAL(5,2) NOT NULL DEFAULT 0.00;

-- Add applied_by_id to penalty_charges
ALTER TABLE penalty_charges 
ADD COLUMN IF NOT EXISTS applied_by_id INT NULL;

-- Add is_automatic to penalty_charges
ALTER TABLE penalty_charges 
ADD COLUMN IF NOT EXISTS is_automatic TINYINT(1) NOT NULL DEFAULT 1;

-- Add reason to penalty_charges
ALTER TABLE penalty_charges 
ADD COLUMN IF NOT EXISTS reason TEXT NULL;

-- Add foreign key if it doesn't exist (this may fail if FK already exists, that's OK)
ALTER TABLE penalty_charges 
ADD CONSTRAINT penalty_charges_applied_by_id_fk 
FOREIGN KEY (applied_by_id) REFERENCES users(id) ON DELETE SET NULL;

SELECT 'Production database updated successfully!' AS status;