<?php
/* ============================================================================
   Standalone migration for: User List + Notes/Update + KYC
   Run once:  http://localhost/neo/admin_userlist_migration  (or open this file)
   Adds ONLY:
     - users address columns: village, post_office, block_city, landmark
     - table user_notes
     - table user_update_history
     - table kyc_details
   Nothing else is touched.  Delete this file after running (security).
   ============================================================================ */

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

echo "<h2>Migration — User List, Notes/Update & KYC</h2>";

$queries = [

    // ---- users: address detail columns ----
    "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",

    // ---- Notes 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",

    // ---- 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 + documents) ----
    "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",
];

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

foreach ($queries as $query) {
    // Older MariaDB/MySQL may not support "ADD COLUMN IF NOT EXISTS" — strip and ignore duplicates.
    $q = str_replace(" IF NOT EXISTS", "", $query);
    try {
        if ($conn->query($q)) {
            $ok++;
            echo "<p style='color:green;'>Success: " . htmlspecialchars(substr($q, 0, 70)) . "...</p>";
        }
    } catch (mysqli_sql_exception $e) {
        $err = $e->getMessage();
        if (strpos($err, 'Duplicate column') !== false || strpos($err, 'already exists') !== false) {
            echo "<p style='color:orange;'>Info (already exists): " . htmlspecialchars(substr($q, 0, 70)) . "...</p>";
        } else {
            echo "<p style='color:red;'>Error: " . htmlspecialchars($err) . "</p>";
        }
    }
}

// Make sure the KYC upload folder exists
$kyc_dir = __DIR__ . '/uploads/kyc';
if (!is_dir($kyc_dir)) { @mkdir($kyc_dir, 0777, true); }

echo "<br><p><b>Done ($ok statements ran).</b> Please delete this file (migration_userlist.php) for security.</p>";
?>
