<?php
require_once __DIR__ . '/config.php';
require_once __DIR__ . '/db.php';

echo "<h2>Database Migration for Subscription & Referrals</h2>";

$queries = [
    // Add columns to users table
    "ALTER TABLE users ADD COLUMN IF NOT EXISTS state VARCHAR(100) NULL AFTER wallet_balance",
    "ALTER TABLE users ADD COLUMN IF NOT EXISTS district VARCHAR(100) NULL AFTER state",
    "ALTER TABLE users ADD COLUMN IF NOT EXISTS pincode VARCHAR(20) NULL AFTER district",
    "ALTER TABLE users ADD COLUMN IF NOT EXISTS is_subscribed TINYINT(1) DEFAULT 0 AFTER pincode",
    "ALTER TABLE users ADD COLUMN IF NOT EXISTS bonus_wallet DECIMAL(10,2) DEFAULT 0.00 AFTER is_subscribed",
    "ALTER TABLE users ADD COLUMN IF NOT EXISTS referred_by INT NULL DEFAULT NULL AFTER user_type",

    // Address detail columns (used by User List management > Update form)
    "ALTER TABLE users ADD COLUMN IF NOT EXISTS village VARCHAR(150) NULL AFTER pincode",
    "ALTER TABLE users ADD COLUMN IF NOT EXISTS post_office VARCHAR(150) NULL AFTER village",
    "ALTER TABLE users ADD COLUMN IF NOT EXISTS block_city VARCHAR(150) NULL AFTER post_office",
    "ALTER TABLE users ADD COLUMN IF NOT EXISTS landmark VARCHAR(255) NULL AFTER block_city",

    // User notes (created by admin/staff against a user)
    "CREATE TABLE IF NOT EXISTS user_notes (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        title VARCHAR(255) NOT NULL,
        description TEXT NULL,
        created_by INT NULL,
        created_by_name VARCHAR(120) NULL,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

    // User update history (who changed which field, when)
    "CREATE TABLE IF NOT EXISTS user_update_history (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        field_label VARCHAR(120) NOT NULL,
        old_value TEXT NULL,
        new_value TEXT NULL,
        updated_by INT NULL,
        updated_by_name VARCHAR(120) NULL,
        updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

    // KYC details (personal, address, bank, business + document uploads)
    "CREATE TABLE IF NOT EXISTS kyc_details (
        id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL UNIQUE,
        full_name VARCHAR(150) NULL, gender VARCHAR(20) NULL, dob VARCHAR(20) NULL,
        father_name VARCHAR(150) NULL, email VARCHAR(150) NULL,
        aadhar_number VARCHAR(30) NULL, pancard_number VARCHAR(30) NULL,
        aadhar_front VARCHAR(255) NULL, aadhar_back VARCHAR(255) NULL, pan_card VARCHAR(255) NULL, photo VARCHAR(255) NULL,
        village VARCHAR(150) NULL, post_office VARCHAR(150) NULL, block_city VARCHAR(150) NULL,
        landmark VARCHAR(255) NULL, district VARCHAR(150) NULL, state VARCHAR(150) NULL, pincode VARCHAR(20) NULL,
        address_proof VARCHAR(255) NULL,
        bank_name VARCHAR(150) NULL, branch_name VARCHAR(150) NULL, account_number VARCHAR(50) NULL, ifsc_code VARCHAR(30) NULL, bank_passbook VARCHAR(255) NULL,
        business_name VARCHAR(200) NULL, business_phone VARCHAR(30) NULL, business_email VARCHAR(150) NULL,
        biz_village VARCHAR(150) NULL, biz_post_office VARCHAR(150) NULL, biz_block_city VARCHAR(150) NULL,
        biz_landmark VARCHAR(255) NULL, biz_district VARCHAR(150) NULL, biz_state VARCHAR(150) NULL, biz_pincode VARCHAR(20) NULL,
        business_proof VARCHAR(255) NULL,
        status ENUM('pending','approved','rejected') DEFAULT 'pending', admin_remark TEXT NULL,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

    // Add commission columns to agency_services
    "ALTER TABLE agency_services ADD COLUMN IF NOT EXISTS referral_commission DECIMAL(10,2) DEFAULT 0.00 AFTER discount_price",
    "ALTER TABLE agency_services ADD COLUMN IF NOT EXISTS self_discount DECIMAL(10,2) DEFAULT 0.00 AFTER referral_commission",
    "ALTER TABLE agency_services ADD COLUMN IF NOT EXISTS company_profit DECIMAL(10,2) DEFAULT 0.00 AFTER self_discount",

    // Add commission columns to seller_services
    "ALTER TABLE seller_services ADD COLUMN IF NOT EXISTS referral_commission DECIMAL(10,2) DEFAULT 0.00 AFTER discount_price",
    "ALTER TABLE seller_services ADD COLUMN IF NOT EXISTS self_discount DECIMAL(10,2) DEFAULT 0.00 AFTER referral_commission",
    "ALTER TABLE seller_services ADD COLUMN IF NOT EXISTS company_profit DECIMAL(10,2) DEFAULT 0.00 AFTER self_discount",
    
    // Add subscription price to settings
    "INSERT IGNORE INTO settings (setting_key, setting_value, setting_type) VALUES ('subscription_price', '999', 'text')"
];

$conn = $db->getConnection();
$success_count = 0;

foreach ($queries as $query) {
    // MariaDB/MySQL doesn't support 'ADD COLUMN IF NOT EXISTS' natively in all versions before 10.3/8.0.
    // So we will just run it and ignore Duplicate column errors.
    $query_to_run = str_replace(" IF NOT EXISTS", "", $query);
    try {
        $result = $conn->query($query_to_run);
        
        if ($result) {
            $success_count++;
            echo "<p style='color:green;'>Success: " . htmlspecialchars($query_to_run) . "</p>";
        }
    } catch (mysqli_sql_exception $e) {
        $error = $e->getMessage();
        if (strpos($error, 'Duplicate column') !== false || strpos($error, 'Duplicate entry') !== false || strpos($error, 'already exists') !== false) {
             echo "<p style='color:orange;'>Info: Already exists for query: " . htmlspecialchars($query_to_run) . "</p>";
        } else {
             echo "<p style='color:red;'>Error executing query: " . htmlspecialchars($query_to_run) . " - " . htmlspecialchars($error) . "</p>";
        }
    }
}

echo "<br><p>Migration complete. <b>Please delete this file (migration.php) for security reasons.</b></p>";
?>
