<?php
try {
    $pdo = new PDO('mysql:host=127.0.0.1;dbname=projects_membershipdb;charset=utf8mb4', 'root', '', [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

    // Check if paid_month exists
    $stmt = $pdo->query("SHOW COLUMNS FROM contributions LIKE 'paid_month'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE contributions ADD COLUMN paid_month VARCHAR(7) DEFAULT NULL AFTER payment_date");
        echo "Added paid_month column\n";
    } else {
        echo "paid_month column exists\n";
    }

    // Check if updated_by exists
    $stmt = $pdo->query("SHOW COLUMNS FROM contributions LIKE 'updated_by'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE contributions ADD COLUMN updated_by INT DEFAULT NULL AFTER processed_by");
        echo "Added updated_by column\n";
    } else {
        echo "updated_by column exists\n";
    }

    // Insert payment reminder settings
    $settings = [
        ['payment_reminder_enabled', 'true', 'boolean', 'payment'],
        ['payment_reminder_day', '1', 'number', 'payment'],
        ['payment_reminder_frequency', 'weekly', 'text', 'payment'],
        ['payment_reminder_grace_days', '5', 'number', 'payment'],
        ['payment_reminder_subject', 'Payment Reminder - Membership Contribution', 'text', 'payment'],
        ['payment_reminder_message', "Dear {name},\n\nThis is a friendly reminder that your membership contribution for {month} has not been recorded yet.\n\nPlease make your payment at your earliest convenience.\n\nThank you.\n\nGhana Anaesthesiologists Society", 'textarea', 'payment'],
        ['receipt_prefix', 'GAS/RECEIPT', 'text', 'payment'],
        ['receipt_company_name', 'Ghana Anaesthesiologists Society', 'text', 'payment'],
        ['receipt_company_details', 'P.O. Box XX, Accra, Ghana | Email: info@ghanaanaesthesia.com | Tel: +233-XXX-XXXX', 'text', 'payment'],
    ];

    $insert = $pdo->prepare("INSERT IGNORE INTO settings (`key`, `value`, `type`, `group`) VALUES (?, ?, ?, ?)");
    foreach ($settings as $s) {
        $insert->execute($s);
    }
    echo 'Inserted ' . count($settings) . ' payment settings' . "\n";
    echo "Migration 009 completed successfully\n";
} catch (Exception $e) {
    echo 'Error: ' . $e->getMessage() . "\n";
    exit(1);
}
