-- Add penalty columns to loan_products table in production
-- Run this on production database

-- Check if columns exist and add them if they don't
SET @dbname = DATABASE();
SET @tablename = 'loan_products';

-- Add penalty_mode column
SET @col_exists = 0;
SELECT COUNT(*) INTO @col_exists 
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = @dbname 
AND TABLE_NAME = @tablename 
AND COLUMN_NAME = 'penalty_mode';

SET @query = IF(@col_exists = 0,
    'ALTER TABLE loan_products ADD COLUMN penalty_mode VARCHAR(20) NOT NULL DEFAULT ''automatic''',
    'SELECT ''Column penalty_mode already exists'' AS message');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add penalty_frequency column
SET @col_exists = 0;
SELECT COUNT(*) INTO @col_exists 
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = @dbname 
AND TABLE_NAME = @tablename 
AND COLUMN_NAME = 'penalty_frequency';

SET @query = IF(@col_exists = 0,
    'ALTER TABLE loan_products ADD COLUMN penalty_frequency VARCHAR(20) NOT NULL DEFAULT ''daily''',
    'SELECT ''Column penalty_frequency already exists'' AS message');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Modify late_payment_penalty to allow decimals if needed
ALTER TABLE loan_products MODIFY COLUMN late_payment_penalty DECIMAL(5,2) NOT NULL DEFAULT 0.00;

-- Add columns to penalty_charges table
SET @tablename = 'penalty_charges';

-- Add applied_by column
SET @col_exists = 0;
SELECT COUNT(*) INTO @col_exists 
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = @dbname 
AND TABLE_NAME = @tablename 
AND COLUMN_NAME = 'applied_by_id';

SET @query = IF(@col_exists = 0,
    'ALTER TABLE penalty_charges ADD COLUMN applied_by_id INT NULL',
    'SELECT ''Column applied_by_id already exists'' AS message');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add is_automatic column
SET @col_exists = 0;
SELECT COUNT(*) INTO @col_exists 
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = @dbname 
AND TABLE_NAME = @tablename 
AND COLUMN_NAME = 'is_automatic';

SET @query = IF(@col_exists = 0,
    'ALTER TABLE penalty_charges ADD COLUMN is_automatic TINYINT(1) NOT NULL DEFAULT 1',
    'SELECT ''Column is_automatic already exists'' AS message');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add reason column
SET @col_exists = 0;
SELECT COUNT(*) INTO @col_exists 
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = @dbname 
AND TABLE_NAME = @tablename 
AND COLUMN_NAME = 'reason';

SET @query = IF(@col_exists = 0,
    'ALTER TABLE penalty_charges ADD COLUMN reason TEXT NULL',
    'SELECT ''Column reason already exists'' AS message');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add foreign key constraint for applied_by if it doesn't exist
SET @fk_exists = 0;
SELECT COUNT(*) INTO @fk_exists 
FROM information_schema.TABLE_CONSTRAINTS 
WHERE TABLE_SCHEMA = @dbname 
AND TABLE_NAME = @tablename 
AND CONSTRAINT_NAME = 'penalty_charges_applied_by_id_fk';

SET @query = IF(@fk_exists = 0,
    '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 ''Foreign key penalty_charges_applied_by_id_fk already exists'' AS message');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SELECT 'Penalty columns added successfully!' AS result;