<?php
/* ============================================================================
   WBMCZ — TMO MIGRATION  (Hero slider · Leads · Field level KYC · Areas)
   Run this ONCE on the live server:   https://wbmcz.online/tmomigration.php

   What it does
     1. hero_slides → creates the table if it is missing
     2. hero_slides → adds every column the new admin slider manager needs:
          badge_text / badge_bn / badge_hi          the small gold pill
          title_bn / title_hi                       Bengali & Hindi title
          subtitle_bn / subtitle_hi                 Bengali & Hindi subtitle
          button_bn / button_hi                     button 1 label
          button2_text / button2_link
          button2_bn / button2_hi                   button 2 label
          text_align                                left | center
          overlay                                   0-100, darkness of the photo
          show_trust                                1 = show the 100% Secure row
     3. hero_slides → fills a sensible button on the slides that have none,
                      and an index on (status, slide_order)
     4. leads + lead_activities        → Lead Management
     5. kyc_custom_fields + kyc_custom_values → the admin's own KYC questions
     6. product_areas.state            → a product can be limited by state
     7. roles                          → the new "manage_leads" module is added
                                         to every role that already has 'all'
     8. hero_slides.panel              → the home page banner and the
                                         after-login banner become two
                                         separate sets that never mix

   Everything the hero shows now comes from this table only — nothing is read
   from the translations file any more.

   Safe to run more than once — every step checks before it changes anything.
   DELETE THIS FILE after a successful run.
   ============================================================================ */

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

$conn = $db->getConnection();
$log  = [];

function tmo_table_exists($conn, $table) {
    try { $r = $conn->query("SHOW TABLES LIKE '$table'"); return $r && $r->num_rows > 0; }
    catch (\Throwable $e) { return false; }
}
function tmo_col_exists($conn, $table, $col) {
    try { $r = $conn->query("SHOW COLUMNS FROM `$table` LIKE '$col'"); return $r && $r->num_rows > 0; }
    catch (\Throwable $e) { return false; }
}
function tmo_index_exists($conn, $table, $index) {
    try { $r = $conn->query("SHOW INDEX FROM `$table` WHERE Key_name = '$index'"); return $r && $r->num_rows > 0; }
    catch (\Throwable $e) { return false; }
}
function tmo_run(&$log, $conn, $label, $sql) {
    try { $conn->query($sql); $log[] = ["ok", $label, "executed"]; }
    catch (\Throwable $e) { $log[] = ["fail", $label, $e->getMessage()]; }
}
function tmo_add_col(&$log, $conn, $table, $col, $def) {
    if (!tmo_table_exists($conn, $table))    { $log[] = ["skip", "$table.$col", "table does not exist"]; return; }
    if (tmo_col_exists($conn, $table, $col)) { $log[] = ["ok",   "$table.$col", "already present"];      return; }
    try {
        $conn->query("ALTER TABLE `$table` ADD COLUMN `$col` $def");
        $log[] = ["add", "$table.$col", $def];
    } catch (\Throwable $e) { $log[] = ["fail", "$table.$col", $e->getMessage()]; }
}
function tmo_add_index(&$log, $conn, $table, $index, $sql) {
    if (!tmo_table_exists($conn, $table))        { $log[] = ["skip", "$table.$index", "table does not exist"]; return; }
    if (tmo_index_exists($conn, $table, $index)) { $log[] = ["ok",   "$table.$index", "already present"];      return; }
    try {
        $conn->query("ALTER TABLE `$table` ADD $sql");
        $log[] = ["add", "$table.$index", $sql];
    } catch (\Throwable $e) { $log[] = ["fail", "$table.$index", $e->getMessage()]; }
}

/* ---------------------------------------------------------------------------
   1. hero_slides — created only if the live database does not have it
   --------------------------------------------------------------------------- */
if (!tmo_table_exists($conn, 'hero_slides')) {
    tmo_run($log, $conn, 'CREATE hero_slides', "
        CREATE TABLE IF NOT EXISTS hero_slides (
            id INT AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(200) NULL,
            subtitle TEXT NULL,
            image_path VARCHAR(255) NULL,
            button_text VARCHAR(60) NULL,
            button_link VARCHAR(255) NULL,
            slide_order INT NOT NULL DEFAULT 0,
            status TINYINT(1) NOT NULL DEFAULT 1,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");
} else {
    $log[] = ["ok", "hero_slides", "table already exists"];
}

/* ---------------------------------------------------------------------------
   2. hero_slides — everything the slider manager writes
   --------------------------------------------------------------------------- */
tmo_add_col($log, $conn, 'hero_slides', 'badge_text',   "VARCHAR(160) NULL");
tmo_add_col($log, $conn, 'hero_slides', 'badge_bn',     "VARCHAR(160) NULL");
tmo_add_col($log, $conn, 'hero_slides', 'badge_hi',     "VARCHAR(160) NULL");

tmo_add_col($log, $conn, 'hero_slides', 'title_bn',     "VARCHAR(200) NULL");
tmo_add_col($log, $conn, 'hero_slides', 'title_hi',     "VARCHAR(200) NULL");

tmo_add_col($log, $conn, 'hero_slides', 'subtitle_bn',  "TEXT NULL");
tmo_add_col($log, $conn, 'hero_slides', 'subtitle_hi',  "TEXT NULL");

tmo_add_col($log, $conn, 'hero_slides', 'button_bn',    "VARCHAR(60) NULL");
tmo_add_col($log, $conn, 'hero_slides', 'button_hi',    "VARCHAR(60) NULL");

tmo_add_col($log, $conn, 'hero_slides', 'button2_text', "VARCHAR(60) NULL");
tmo_add_col($log, $conn, 'hero_slides', 'button2_link', "VARCHAR(255) NULL");
tmo_add_col($log, $conn, 'hero_slides', 'button2_bn',   "VARCHAR(60) NULL");
tmo_add_col($log, $conn, 'hero_slides', 'button2_hi',   "VARCHAR(60) NULL");

tmo_add_col($log, $conn, 'hero_slides', 'text_align',   "VARCHAR(10) NOT NULL DEFAULT 'left'");
tmo_add_col($log, $conn, 'hero_slides', 'overlay',      "TINYINT NOT NULL DEFAULT 70");
tmo_add_col($log, $conn, 'hero_slides', 'show_trust',   "TINYINT(1) NOT NULL DEFAULT 1");

tmo_add_index($log, $conn, 'hero_slides', 'idx_hero_live', "INDEX `idx_hero_live` (`status`, `slide_order`)");

/* ---------------------------------------------------------------------------
   3. a slide with no button would show no call to action at all, so give the
      existing slides the default Register button
   --------------------------------------------------------------------------- */
if (tmo_col_exists($conn, 'hero_slides', 'button_text')) {
    try {
        $conn->query("UPDATE hero_slides
                      SET button_text = 'Get Started Free', button_link = 'register.php'
                      WHERE button_text IS NULL OR button_text = ''");
        $n = $conn->affected_rows;
        $log[] = ["add", "hero_slides.button_text", "$n slide(s) given the default Register button"];
    } catch (\Throwable $e) { $log[] = ["fail", "hero_slides.button_text", $e->getMessage()]; }

    try {
        $conn->query("UPDATE hero_slides SET button_link = 'register.php' WHERE button_link IS NULL OR button_link = ''");
    } catch (\Throwable $e) {}
}

/* order + overlay sanity */
try { $conn->query("UPDATE hero_slides SET overlay = 70 WHERE overlay IS NULL OR overlay < 0 OR overlay > 100"); } catch (\Throwable $e) {}
try { $conn->query("UPDATE hero_slides SET text_align = 'left' WHERE text_align IS NULL OR text_align = ''"); } catch (\Throwable $e) {}

$slide_count = 0;
try {
    $r = $conn->query("SELECT COUNT(*) c FROM hero_slides");
    if ($r && ($x = $r->fetch_assoc())) $slide_count = (int)$x['c'];
} catch (\Throwable $e) {}
$log[] = ["ok", "hero_slides", "$slide_count slide(s) in the table"];

/* ---------------------------------------------------------------------------
   4. LEADS — enquiries and follow-up
   --------------------------------------------------------------------------- */
tmo_run($log, $conn, 'CREATE leads', "
    CREATE TABLE IF NOT EXISTS leads (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NULL,
        name VARCHAR(150) NOT NULL,
        mobile VARCHAR(20) NOT NULL,
        alt_mobile VARCHAR(20) NULL,
        email VARCHAR(150) NULL,
        source VARCHAR(30) NOT NULL DEFAULT 'manual',
        lead_type VARCHAR(20) NOT NULL DEFAULT 'active',
        interest VARCHAR(255) NULL,
        state VARCHAR(100) NULL,
        district VARCHAR(100) NULL,
        pincode VARCHAR(20) NULL,
        address VARCHAR(255) NULL,
        status VARCHAR(20) NOT NULL DEFAULT 'new',
        priority VARCHAR(10) NOT NULL DEFAULT 'medium',
        assigned_to INT NULL,
        assigned_name VARCHAR(120) NULL,
        assigned_at DATETIME NULL,
        next_follow_up DATE NULL,
        last_contacted_at DATETIME NULL,
        notes TEXT NULL,
        converted_at DATETIME NULL,
        created_by INT NULL,
        created_by_name VARCHAR(120) NULL,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP NULL DEFAULT NULL,
        KEY idx_leads_status (status),
        KEY idx_leads_assigned (assigned_to),
        KEY idx_leads_user (user_id),
        KEY idx_leads_follow (next_follow_up)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
");

tmo_run($log, $conn, 'CREATE lead_activities', "
    CREATE TABLE IF NOT EXISTS lead_activities (
        id INT AUTO_INCREMENT PRIMARY KEY,
        lead_id INT NOT NULL,
        action VARCHAR(30) NOT NULL DEFAULT 'note',
        old_value VARCHAR(160) NULL,
        new_value VARCHAR(160) NULL,
        remark TEXT NULL,
        next_follow_up DATE NULL,
        done_by INT NULL,
        done_by_name VARCHAR(120) NULL,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        KEY idx_la_lead (lead_id, created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
");

/* ---------------------------------------------------------------------------
   5. The admin's own KYC questions, and the answers members give
   --------------------------------------------------------------------------- */
tmo_run($log, $conn, 'CREATE kyc_custom_fields', "
    CREATE TABLE IF NOT EXISTS kyc_custom_fields (
        id INT AUTO_INCREMENT PRIMARY KEY,
        field_label VARCHAR(200) NOT NULL,
        field_label_bn VARCHAR(200) NULL,
        field_type VARCHAR(20) NOT NULL DEFAULT 'text',
        options TEXT NULL,
        placeholder VARCHAR(200) NULL,
        help_text VARCHAR(255) NULL,
        group_name VARCHAR(60) NOT NULL DEFAULT 'Additional Information',
        display_order INT NOT NULL DEFAULT 0,
        status TINYINT(1) NOT NULL DEFAULT 1,
        created_by INT NULL,
        created_by_name VARCHAR(120) NULL,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        KEY idx_kcf_live (status, display_order)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
");

tmo_run($log, $conn, 'CREATE kyc_custom_values', "
    CREATE TABLE IF NOT EXISTS kyc_custom_values (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        field_id INT NOT NULL,
        value TEXT NULL,
        file_path VARCHAR(255) NULL,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP NULL DEFAULT NULL,
        UNIQUE KEY uniq_kcv (user_id, field_id),
        KEY idx_kcv_user (user_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
");

/* ---------------------------------------------------------------------------
   6. product_areas — a product can now be limited by STATE as well as pincode.
      A row holds either a pincode or a state, so pincode has to allow NULL.
   --------------------------------------------------------------------------- */
tmo_add_col($log, $conn, 'product_areas', 'state', "VARCHAR(100) NULL");
tmo_add_index($log, $conn, 'product_areas', 'idx_pa_state', "INDEX `idx_pa_state` (`service_id`, `service_type`, `state`)");
if (tmo_col_exists($conn, 'product_areas', 'pincode')) {
    try {
        $conn->query("ALTER TABLE product_areas MODIFY COLUMN `pincode` VARCHAR(20) NULL");
        $log[] = ["ok", "product_areas.pincode", "now optional (a row may carry a state instead)"];
    } catch (\Throwable $e) { $log[] = ["fail", "product_areas.pincode", $e->getMessage()]; }
}

/* seller_services.required_kyc now stores field level keys, so give it room */
if (tmo_col_exists($conn, 'seller_services', 'required_kyc')) {
    try { $conn->query("ALTER TABLE seller_services MODIFY COLUMN `required_kyc` VARCHAR(255) NULL"); }
    catch (\Throwable $e) {}
}

/* ---------------------------------------------------------------------------
   7. roles — the new Lead module. Any role that already holds 'all' keeps
      full access automatically; a role with a specific list gets nothing new
      unless you tick it yourself in Admin → Custom Roles.
   --------------------------------------------------------------------------- */
if (tmo_table_exists($conn, 'roles')) {
    $n = 0;
    try {
        $r = $conn->query("SELECT id, permissions FROM roles");
        if ($r) {
            while ($row = $r->fetch_assoc()) {
                $p = json_decode((string)$row['permissions'], true);
                if (!is_array($p)) continue;
                if (in_array('all', $p, true)) continue;              // already everything
                if (in_array('manage_leads', $p, true)) continue;      // already ticked
                /* a role that manages customers gets the lead module too — it is
                   the same audience, and it can be unticked any time */
                if (in_array('manage_users', $p, true)) {
                    $p[] = 'manage_leads';
                    $st = $conn->prepare("UPDATE roles SET permissions = ? WHERE id = ?");
                    if ($st) {
                        $j = json_encode(array_values($p));
                        $st->bind_param('si', $j, $row['id']);
                        $st->execute();
                        $st->close();
                        $n++;
                    }
                }
            }
        }
        $log[] = ["add", "roles.manage_leads", "$n role(s) that manage customers can now open Lead Management"];
    } catch (\Throwable $e) { $log[] = ["fail", "roles.manage_leads", $e->getMessage()]; }
}

/* ---------------------------------------------------------------------------
   8. hero_slides.panel — SPLIT THE TWO BANNERS
      Until now index.php (the public home page) and dashboard.php (what a
      member sees after logging in) both read the same rows, so editing the
      home page banner changed the after-login one as well. Each slide now
      belongs to exactly one of them.

        landing    the big hero on the public home page
        dashboard  the banner inside the member dashboard

      Every slide that exists today was built for the home page, so it stays
      there. To keep the after-login banner from going blank on the day you
      run this, a COPY of each live slide is made for the dashboard set —
      only once, and only if that set is still empty. Delete or rewrite those
      copies whenever you like; the home page will not notice.
   --------------------------------------------------------------------------- */
tmo_add_col($log, $conn, 'hero_slides', 'panel', "VARCHAR(20) NOT NULL DEFAULT 'landing'");
tmo_add_index($log, $conn, 'hero_slides', 'idx_hero_panel', "INDEX `idx_hero_panel` (`panel`, `status`, `slide_order`)");

if (tmo_col_exists($conn, 'hero_slides', 'panel')) {
    /* anything without a panel belongs to the home page */
    try {
        $conn->query("UPDATE hero_slides SET panel = 'landing' WHERE panel IS NULL OR panel = ''");
        $log[] = ["ok", "hero_slides.panel", "existing slides kept on the home page"];
    } catch (\Throwable $e) { $log[] = ["fail", "hero_slides.panel", $e->getMessage()]; }

    /* does the dashboard set already have something? */
    $have_dash = 0;
    try {
        $r = $conn->query("SELECT COUNT(*) c FROM hero_slides WHERE panel = 'dashboard'");
        if ($r && ($x = $r->fetch_assoc())) $have_dash = (int)$x['c'];
    } catch (\Throwable $e) {}

    if ($have_dash > 0) {
        $log[] = ["ok", "hero_slides dashboard set", "$have_dash slide(s) already there — nothing copied"];
    } else {
        /* build the copy from whichever columns this database actually has */
        $cols = [];
        try {
            $r = $conn->query("SHOW COLUMNS FROM hero_slides");
            if ($r) { while ($row = $r->fetch_assoc()) { $cols[] = $row['Field']; } }
        } catch (\Throwable $e) {}

        $copy = array_values(array_filter($cols, function ($c) { return $c !== 'id' && $c !== 'panel' && $c !== 'created_at'; }));
        if (!empty($copy)) {
            $list = '`' . implode('`, `', $copy) . '`';
            try {
                $conn->query("INSERT INTO hero_slides (`panel`, $list)
                              SELECT 'dashboard', $list FROM hero_slides
                              WHERE COALESCE(NULLIF(panel, ''), 'landing') = 'landing' AND status = 1");
                $n = $conn->affected_rows;
                $log[] = ["add", "hero_slides dashboard set", "$n copy(ies) made so the after-login banner is not blank"];
            } catch (\Throwable $e) { $log[] = ["fail", "hero_slides dashboard set", $e->getMessage()]; }
        }
    }
}

$fails = 0;
foreach ($log as $l) { if ($l[0] === 'fail') $fails++; }
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>TMO Migration — WBMCZ Hero Slider</title>
    <script src="https://cdn.tailwindcss.com"></script>
</head>
<body class="bg-slate-100 p-6 font-sans">
    <div class="max-w-3xl mx-auto bg-white rounded-2xl shadow p-6 border border-slate-200">
        <h1 class="text-2xl font-extrabold text-slate-800 mb-1">TMO Migration — Hero Slider</h1>
        <p class="text-sm text-slate-500 mb-5">Database: <b><?php echo DB_NAME; ?></b> &middot; hero banners (split), leads, KYC questions, state areas</p>

        <div class="mb-5 rounded-xl px-4 py-3 <?php echo $fails ? 'bg-rose-50 text-rose-700 border border-rose-200' : 'bg-emerald-50 text-emerald-700 border border-emerald-200'; ?>">
            <?php echo $fails ? "Finished with $fails error(s) — see the rows marked FAIL." : 'Migration completed successfully.'; ?>
        </div>

        <table class="w-full text-sm border-collapse">
            <thead>
                <tr class="bg-slate-50 text-slate-500 text-xs uppercase tracking-wider">
                    <th class="text-left py-2 px-3">Result</th>
                    <th class="text-left py-2 px-3">Target</th>
                    <th class="text-left py-2 px-3">Detail</th>
                </tr>
            </thead>
            <tbody class="divide-y divide-slate-100">
            <?php foreach ($log as $l):
                $cls = ['ok' => 'text-slate-500', 'add' => 'text-emerald-600', 'skip' => 'text-amber-600', 'fail' => 'text-rose-600'][$l[0]] ?? '';
            ?>
                <tr>
                    <td class="py-2 px-3 font-bold uppercase <?php echo $cls; ?>"><?php echo $l[0]; ?></td>
                    <td class="py-2 px-3 font-mono text-slate-700"><?php echo htmlspecialchars($l[1]); ?></td>
                    <td class="py-2 px-3 text-slate-500"><?php echo htmlspecialchars($l[2]); ?></td>
                </tr>
            <?php endforeach; ?>
            </tbody>
        </table>

        <p class="mt-6 text-xs text-slate-500">
            Next: <b>Admin &rarr; Website Settings &rarr; Hero Slider</b> — it now has two tabs,
            <b>Home Page Banner</b> and <b>After-Login Banner</b>, and they are completely separate;
            <b>Lead Management</b> for the new leads screen, and
            <b>Service Management &rarr; KYC Questions</b> to write your own KYC boxes.
        </p>
        <p class="mt-2 text-xs text-rose-600 font-bold">Delete tmomigration.php from the server once this page shows &ldquo;completed successfully&rdquo;.</p>
    </div>
</body>
</html>
