<?php
/* ============================================================================
   WBMCZ — Lead management

   Two kinds of lead live side by side:

     POTENTIAL  someone who registered on the site but has never subscribed
                and has never placed an order. These are read live from the
                `users` table, so the list is always current — nothing has to
                be imported or kept in sync.

     ACTIVE     a row in `leads`: either an enquiry typed in by hand, or a
                potential member that somebody picked up to work on. Every
                call, status change and hand-over is written to
                `lead_activities`, so the whole history is visible.

   A potential member becomes a lead row the moment anyone assigns them,
   changes their status or writes a note — see lead_from_user().
   ============================================================================ */

if (!function_exists('leads_ensure_schema')) {

function leads_ensure_schema($db) {
    static $done = false;
    if ($done) return;
    $done = true;

    $conn = $db->getConnection();
    try {
        $conn->query("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");
    } catch (\Throwable $e) {}

    /* the potential list reads these two columns — add them if an older
       database has not had testsmigration.php run against it yet */
    foreach (['created_by' => 'INT NULL', 'created_by_name' => 'VARCHAR(120) NULL'] as $c => $def) {
        try {
            $r = $conn->query("SHOW COLUMNS FROM users LIKE '$c'");
            if ($r && $r->num_rows === 0) { $conn->query("ALTER TABLE users ADD COLUMN `$c` $def"); }
        } catch (\Throwable $e) {}
    }

    try {
        $conn->query("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");
    } catch (\Throwable $e) {}
}

/* ---------------------------------------------------------------------------
   Vocabulary
   --------------------------------------------------------------------------- */
function lead_statuses() {
    return [
        'new'        => ['New',            'bg-slate-100 text-slate-700',   'fa-sparkles'],
        'contacted'  => ['Contacted',      'bg-sky-100 text-sky-700',       'fa-phone'],
        'interested' => ['Interested',     'bg-violet-100 text-violet-700', 'fa-thumbs-up'],
        'follow_up'  => ['Follow-up',      'bg-amber-100 text-amber-700',   'fa-clock'],
        'converted'  => ['Converted',      'bg-emerald-100 text-emerald-700','fa-circle-check'],
        'lost'       => ['Not interested', 'bg-rose-100 text-rose-700',     'fa-circle-xmark'],
    ];
}
function lead_status_label($k) { $m = lead_statuses(); return $m[$k][0] ?? ucfirst((string)$k); }
function lead_status_class($k) { $m = lead_statuses(); return $m[$k][1] ?? 'bg-slate-100 text-slate-600'; }
function lead_status_icon($k)  { $m = lead_statuses(); return $m[$k][2] ?? 'fa-circle'; }

function lead_sources() {
    return [
        'manual'   => 'Walk-in / Office',
        'call'     => 'Phone call',
        'whatsapp' => 'WhatsApp',
        'facebook' => 'Facebook / Social',
        'referral' => 'Referral',
        'website'  => 'Website registration',
        'camp'     => 'Camp / Event',
        'other'    => 'Other',
    ];
}
function lead_source_label($k) { $m = lead_sources(); return $m[$k] ?? ucfirst((string)$k); }

function lead_priorities() {
    return ['high' => ['High', 'bg-rose-100 text-rose-700'],
            'medium' => ['Medium', 'bg-amber-100 text-amber-700'],
            'low' => ['Low', 'bg-slate-100 text-slate-600']];
}

/* open = still worth working on */
function lead_open_statuses() { return ['new', 'contacted', 'interested', 'follow_up']; }

/* ---------------------------------------------------------------------------
   Activity trail
   --------------------------------------------------------------------------- */
function lead_log($db, $lead_id, $action, $remark = '', $old = '', $new = '', $me = null, $follow = null) {
    leads_ensure_schema($db);
    try {
        $db->insert("INSERT INTO lead_activities (lead_id, action, old_value, new_value, remark, next_follow_up, done_by, done_by_name)
                     VALUES (?,?,?,?,?,?,?,?)",
            [(int)$lead_id, $action, (string)$old, (string)$new, (string)$remark,
             ($follow !== '' && $follow !== null) ? $follow : null,
             $me['id'] ?? null, $me['name'] ?? null]);
    } catch (\Throwable $e) {}
}

function lead_activities($db, $lead_id) {
    leads_ensure_schema($db);
    try {
        return $db->select("SELECT * FROM lead_activities WHERE lead_id = ? ORDER BY id DESC LIMIT 100", [(int)$lead_id]);
    } catch (\Throwable $e) { return []; }
}

/* ---------------------------------------------------------------------------
   A registered member becomes a lead row the first time somebody works on it
   --------------------------------------------------------------------------- */
function lead_from_user($db, $user_id, $me = null) {
    leads_ensure_schema($db);
    $user_id = (int)$user_id;
    if ($user_id <= 0) return 0;

    try {
        $x = $db->select("SELECT id FROM leads WHERE user_id = ? ORDER BY id DESC LIMIT 1", [$user_id]);
        if (!empty($x)) return (int)$x[0]['id'];
    } catch (\Throwable $e) { return 0; }

    $u = [];
    try { $r = $db->select("SELECT * FROM users WHERE id = ?", [$user_id]); $u = !empty($r) ? $r[0] : []; }
    catch (\Throwable $e) {}
    if (empty($u)) return 0;

    $id = $db->insert(
        "INSERT INTO leads (user_id, name, mobile, email, source, lead_type, state, district, pincode,
                            status, priority, created_by, created_by_name)
         VALUES (?,?,?,?,'website','potential',?,?,?,'new','medium',?,?)",
        [$user_id, $u['name'] ?? '', $u['mobile'] ?? '', $u['email'] ?? '',
         $u['state'] ?? '', $u['district'] ?? '', $u['pincode'] ?? '',
         $me['id'] ?? null, $me['name'] ?? null]
    );
    if ($id) { lead_log($db, $id, 'created', 'Picked up from Potential Leads', '', '', $me); }
    return (int)$id;
}

/* ---------------------------------------------------------------------------
   POTENTIAL LEADS — registered, never subscribed, never ordered.
   $where / $params are appended to the query so the page can filter.
   --------------------------------------------------------------------------- */
function lead_potential_sql() {
    return "FROM users u
            LEFT JOIN leads l ON l.user_id = u.id
            WHERE u.user_type = 'customer'
              AND u.deleted_at IS NULL
              AND COALESCE(u.is_subscribed, 0) = 0
              AND NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)";
}

function lead_potential_count($db, $extra = '', $params = []) {
    try {
        $r = $db->select("SELECT COUNT(DISTINCT u.id) c " . lead_potential_sql() . " $extra", $params);
        return !empty($r) ? (int)$r[0]['c'] : 0;
    } catch (\Throwable $e) { return 0; }
}

function lead_potential_rows($db, $extra = '', $params = [], $limit = 100, $offset = 0) {
    try {
        return $db->select(
            "SELECT u.id, u.name, u.mobile, u.email, u.state, u.district, u.pincode, u.created_at,
                    u.referred_by, u.created_by_name,
                    l.id AS lead_id, l.status AS lead_status, l.assigned_name, l.assigned_to,
                    l.next_follow_up, l.priority
             " . lead_potential_sql() . " $extra
             ORDER BY u.id DESC LIMIT " . (int)$limit . " OFFSET " . (int)$offset, $params);
    } catch (\Throwable $e) { return []; }
}

/* Staff accounts a lead can be handed to */
function lead_staff_list($db) {
    try {
        return $db->select("SELECT u.id, u.name, r.name AS role_name
                            FROM users u LEFT JOIN roles r ON u.role_id = r.id
                            WHERE u.user_type = 'admin' AND u.deleted_at IS NULL
                            ORDER BY u.name ASC");
    } catch (\Throwable $e) { return []; }
}

/* Headline numbers for the stat cards */
function lead_stats($db, $only_mine = 0) {
    leads_ensure_schema($db);
    $out = ['total' => 0, 'open' => 0, 'today' => 0, 'due' => 0, 'converted' => 0, 'lost' => 0, 'unassigned' => 0];
    $w = $only_mine > 0 ? " WHERE assigned_to = " . (int)$only_mine : "";
    try {
        $r = $db->select("SELECT COUNT(*) t,
             SUM(status IN ('new','contacted','interested','follow_up')) o,
             SUM(DATE(created_at) = CURDATE()) td,
             SUM(next_follow_up IS NOT NULL AND next_follow_up <= CURDATE()
                 AND status IN ('new','contacted','interested','follow_up')) du,
             SUM(status = 'converted') cv,
             SUM(status = 'lost') ls,
             SUM(assigned_to IS NULL) ua
             FROM leads $w");
        if (!empty($r)) {
            $x = $r[0];
            $out = ['total' => (int)$x['t'], 'open' => (int)$x['o'], 'today' => (int)$x['td'],
                    'due' => (int)$x['du'], 'converted' => (int)$x['cv'], 'lost' => (int)$x['ls'],
                    'unassigned' => (int)$x['ua']];
        }
    } catch (\Throwable $e) {}
    return $out;
}

} // end function_exists guard
