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

    $pdo->exec("CREATE TABLE IF NOT EXISTS loan_types (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        description TEXT DEFAULT NULL,
        interest_rate DECIMAL(5,2) NOT NULL DEFAULT 0.00,
        min_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
        max_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
        min_duration_months INT NOT NULL DEFAULT 1,
        max_duration_months INT NOT NULL DEFAULT 12,
        is_active TINYINT(1) DEFAULT 1,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    echo "Created loan_types table\n";

    $pdo->exec("CREATE TABLE IF NOT EXISTS loans (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        loan_type_id INT NOT NULL,
        amount DECIMAL(12,2) NOT NULL,
        interest_rate DECIMAL(5,2) NOT NULL DEFAULT 0.00,
        duration_months INT NOT NULL DEFAULT 1,
        total_payable DECIMAL(12,2) NOT NULL DEFAULT 0.00,
        monthly_payment DECIMAL(12,2) NOT NULL DEFAULT 0.00,
        status ENUM('pending','approved','rejected','disbursed','completed','defaulted') NOT NULL DEFAULT 'pending',
        purpose TEXT DEFAULT NULL,
        application_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        approval_date DATETIME DEFAULT NULL,
        approved_by INT DEFAULT NULL,
        disbursement_date DATETIME DEFAULT NULL,
        notes TEXT DEFAULT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (loan_type_id) REFERENCES loan_types(id) ON DELETE CASCADE,
        FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    echo "Created loans table\n";

    $pdo->exec("CREATE TABLE IF NOT EXISTS loan_repayments (
        id INT AUTO_INCREMENT PRIMARY KEY,
        loan_id INT NOT NULL,
        installment_number INT NOT NULL,
        due_date DATE NOT NULL,
        amount DECIMAL(12,2) NOT NULL,
        paid_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
        paid_date DATETIME DEFAULT NULL,
        status ENUM('pending','paid','partial','overdue') NOT NULL DEFAULT 'pending',
        payment_method VARCHAR(100) DEFAULT NULL,
        transaction_ref VARCHAR(255) DEFAULT NULL,
        notes TEXT DEFAULT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (loan_id) REFERENCES loans(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    echo "Created loan_repayments table\n";

    echo "Migration 019 completed successfully\n";
} catch (Exception $e) {
    echo 'Error: ' . $e->getMessage() . "\n";
    exit(1);
}
