<?php
// Run this file once to apply new database migrations
// Access: http://localhost/AAbank_mob/backend/migrate.php

include_once __DIR__ . '/config/database.php';

$database = new Database();
$db = $database->getConnection();

$queries = [
    "CREATE TABLE IF NOT EXISTS loans (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        amount DECIMAL(15,2) NOT NULL,
        interest_rate DECIMAL(5,2) DEFAULT 12.00,
        term_months INT NOT NULL,
        monthly_payment DECIMAL(15,2) NOT NULL,
        total_repayment DECIMAL(15,2) NOT NULL,
        amount_paid DECIMAL(15,2) DEFAULT 0.00,
        status ENUM('pending','active','paid','rejected') DEFAULT 'pending',
        purpose VARCHAR(255) DEFAULT '',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    )",

    "CREATE TABLE IF NOT EXISTS deposits (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        amount DECIMAL(15,2) NOT NULL,
        method ENUM('card','crypto') NOT NULL,
        crypto_currency VARCHAR(10) DEFAULT NULL,
        crypto_address VARCHAR(255) DEFAULT NULL,
        card_last4 VARCHAR(4) DEFAULT NULL,
        status ENUM('pending','completed','failed') DEFAULT 'completed',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    )",

    "CREATE TABLE IF NOT EXISTS admin_notes (
        id INT AUTO_INCREMENT PRIMARY KEY,
        admin_id INT NOT NULL,
        title VARCHAR(255) NOT NULL,
        content TEXT NOT NULL,
        color VARCHAR(20) DEFAULT 'blue',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (admin_id) REFERENCES users(id) ON DELETE CASCADE
    )",

    "CREATE TABLE IF NOT EXISTS admin_settings (
        id INT AUTO_INCREMENT PRIMARY KEY,
        setting_key VARCHAR(100) NOT NULL UNIQUE,
        setting_value TEXT NOT NULL,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )",

    "INSERT IGNORE INTO admin_settings (setting_key, setting_value) VALUES
        ('bank_name', 'IOSCO Bank'),
        ('max_transfer_limit', '50000'),
        ('loan_interest_rate', '12'),
        ('crypto_enabled', '1'),
        ('registration_enabled', '1'),
        ('maintenance_mode', '0')"
];

$results = [];
foreach ($queries as $i => $q) {
    try {
        $db->exec($q);
        $results[] = "Query " . ($i + 1) . ": OK";
    } catch (Exception $e) {
        $results[] = "Query " . ($i + 1) . " ERROR: " . $e->getMessage();
    }
}

header('Content-Type: text/plain');
echo "=== IOSCO Bank Migration ===\n\n";
foreach ($results as $r) {
    echo $r . "\n";
}
echo "\nDone! You can now delete this file.";
?>
