<?php
/* ============================================================================
   WBMCZ — ACTIVITY PIPELINE
   ----------------------------------------------------------------------------
   A LADDER and some DESKS. That is the whole thing.

   THE LADDER — where a person has got to. Everybody sits on exactly one rung,
   the highest one they have reached, and the tabs are simply those rungs:

       Lead  ->  Free user  ->  Subscription  ->  KYC  ->  Refer  ->  Order

   Nobody drags anybody up. Five of the six rungs are facts about the account —
   subscribed, KYC approved, has referred somebody, has ordered — so the moment
   a fact becomes true the person moves up on their own and appears under the
   next tab. The sixth, Free user, is the one thing staff decide: mark somebody
   a free user and they sit there until they subscribe.

   One person, one row. It is the same row all the way up the ladder, so the
   remark, the call-back date and the whole history travel with them instead of
   being left behind on a task nobody looks at again.

   THE DESKS — queues somebody has to decide on: seller requests, KYC
   submissions, product listings, support, withdrawals, payouts, website
   requests. Each reads and writes the real table, so a verdict here is the same
   verdict as on that table's own admin page.

   Access is per person: a tick opens one rung or one desk for one member of
   staff, and within a rung they still only see the people they hold.
   ============================================================================ */

require_once __DIR__ . '/tasks.php';
/* names the per-category review pages, for the Product Requests desk */
if (!function_exists('pmp_service_file')) { @require_once __DIR__ . '/product_pages.php'; }

if (!function_exists('esc')) {
    function esc($v) { return htmlspecialchars((string)$v, ENT_QUOTES, 'UTF-8'); }
}
if (!function_exists('pipe_cut')) {
    function pipe_cut($s, $n) {
        $s = trim(strip_tags((string)$s));
        if (function_exists('mb_substr')) { return mb_strlen($s) > $n ? mb_substr($s, 0, $n) . '…' : $s; }
        return strlen($s) > $n ? substr($s, 0, $n) . '…' : $s;
    }
}

if (!function_exists('pipe_roles')) {

/* ---------------------------------------------------------------------------
   THE TOP TABS
   --------------------------------------------------------------------------- */
function pipe_roles() {
    return [
        'user'       => ['label' => 'User',        'icon' => 'fa-user',            'kind' => 'pipeline'],
        'seller'     => ['label' => 'Seller',      'icon' => 'fa-store',           'kind' => 'pipeline'],
        'employee'   => ['label' => 'Employee',    'icon' => 'fa-user-tie',        'kind' => 'team'],
        'admin'      => ['label' => 'Admin',       'icon' => 'fa-shield-halved',   'kind' => 'team'],
        'superadmin' => ['label' => 'Super Admin', 'icon' => 'fa-crown',           'kind' => 'team'],
        'office'     => ['label' => 'Office',      'icon' => 'fa-building-columns','kind' => 'desks'],
    ];
}

/* the facts a rung is built out of */
function pipe_kyc_ok()   { return "EXISTS (SELECT 1 FROM kyc_details k WHERE k.user_id = u.id AND k.status = 'approved')"; }
/* Anything at all on file — used to tell "nothing has come in" from the rest. */
function pipe_kyc_any()  { return "EXISTS (SELECT 1 FROM kyc_details k WHERE k.user_id = u.id)"; }
/* Submitted and sitting with us — the middle of the three KYC states. */
function pipe_kyc_sent() { return "EXISTS (SELECT 1 FROM kyc_details k WHERE k.user_id = u.id AND LOWER(COALESCE(k.status,'')) IN ('pending','submitted'))"; }
function pipe_refer_ok() { return "EXISTS (SELECT 1 FROM users r WHERE r.referred_by = u.id)"; }
function pipe_order_ok() { return "EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)"; }
function pipe_subs_ok()  { return "COALESCE(u.is_subscribed,0) = 1"; }

/* ---------------------------------------------------------------------------
   THE RUNGS, in the order they are shown.
   `at` is what makes somebody belong on this rung. They are tested from the
   TOP of the ladder down, so the highest true one wins and nobody can be in
   two places at once.
   --------------------------------------------------------------------------- */
function pipe_ladder($role = 'user') {
    /* only the two people-facing tabs have a ladder; Office and the team tabs
       are desks and summaries */
    if ($role !== 'user' && $role !== 'seller') return [];
    $p = ($role === 'seller') ? 's_' : '';
    $who = ($role === 'seller') ? "u.user_type = 'seller'" : "u.user_type = 'customer'";

    return [
        $p . 'lead' => [
            'label' => 'Lead', 'icon' => 'fa-bullseye', 'who' => $who,
            'blurb' => 'Nothing has happened yet',
            'goal'  => 'Get them on a subscription — or mark them a free user',
            'at'    => "1",                      /* the floor: everybody who is nothing else */
            'outcomes' => ['interested', 'follow_up', 'free_user', 'not_interested'],
        ],
        $p . 'free' => [
            'label' => 'Free User', 'icon' => 'fa-user', 'who' => $who,
            'blurb' => 'Marked as staying on the free plan',
            'goal'  => 'Bring them round to a subscription when they are ready',
            'at'    => "t.outcome = 'free_user'",
            'outcomes' => ['interested', 'follow_up', 'not_interested'],
        ],
        $p . 'subs' => [
            'label' => 'Subscription', 'icon' => 'fa-crown', 'who' => $who,
            'blurb' => 'Subscription taken',
            'goal'  => 'Ask them for their KYC',
            'at'    => pipe_subs_ok(),
            'outcomes' => ['interested', 'follow_up', 'kyc_asked', 'not_interested'],
        ],

        /* ------------------------------------------------------------------
           KYC IS THREE STEPS, NOT ONE

           One "KYC" chip hid the difference between three completely different
           jobs: chasing somebody who has not sent anything, checking what has
           arrived, and being finished. They need different people and different
           words, so they are three rungs.

             Pending KYC   we asked, nothing has come     — chase them
             KYC Request   it has come, we have not read it — read it
             KYC Complete  approved                        — move them on

           Pending KYC hangs on the outcome a staff member records, exactly the
           way Free User does. Without that it would have to mean "everybody
           with no KYC row", which is nearly the whole database — Lead included
           — and it would swallow the ladder from the top.
           ------------------------------------------------------------------ */
        $p . 'kyc_pend' => [
            'label' => 'Pending KYC', 'icon' => 'fa-hourglass-half', 'who' => $who,
            'blurb' => 'On a plan, free or paid — and no KYC has come in yet',
            'goal'  => 'Chase the documents',
            /* Everybody who has settled on a plan but sent nothing in: the ones
               marked free, the ones who subscribed, and anybody a staff member
               has asked outright. Which of the three they are is shown as a
               badge on the row, because the work is the same — chase them — but
               who you are talking to is not. */
            'at'    => "(" . pipe_subs_ok() . " OR t.outcome IN ('free_user','kyc_asked'))"
                     . " AND NOT " . pipe_kyc_any(),
            'outcomes' => ['follow_up', 'not_interested'],
        ],
        $p . 'kyc_req' => [
            'label' => 'KYC Request', 'icon' => 'fa-file-circle-check', 'who' => $who,
            'blurb' => 'Documents submitted — waiting on us',
            'goal'  => 'Read the documents and approve or send them back',
            'at'    => pipe_kyc_sent(),
            'outcomes' => ['follow_up', 'not_interested'],
        ],
        $p . 'kyc_done' => [
            'label' => 'KYC Complete', 'icon' => 'fa-id-card', 'who' => $who,
            'blurb' => 'KYC approved',
            'goal'  => 'Ask them to refer somebody',
            'at'    => pipe_kyc_ok(),
            'outcomes' => ['wants_refer', 'follow_up', 'not_interested'],
        ],
        $p . 'refer' => [
            'label' => 'Refer', 'icon' => 'fa-share-nodes', 'who' => $who,
            'blurb' => 'Has referred somebody',
            'goal'  => 'Ask them to place an order',
            'at'    => pipe_refer_ok(),
            'outcomes' => ['wants_order', 'follow_up', 'not_interested'],
        ],
        $p . 'order' => [
            'label' => 'Order', 'icon' => 'fa-cart-shopping', 'who' => $who,
            'blurb' => 'Has placed an order — all the way up',
            'goal'  => 'Keep them ordering',
            'at'    => pipe_order_ok(),
            'outcomes' => ['interested', 'follow_up', 'not_interested'],
        ],
    ];
}

/* The rung somebody is on, as one SQL expression. Highest true wins. */
function pipe_rung_case($role = 'user', $withTask = true) {
    $rungs = pipe_ladder($role);
    if (empty($rungs)) return "''";
    $keys  = array_keys($rungs);
    $sql   = "CASE";
    foreach (array_reverse($keys) as $k) {       /* top of the ladder first */
        $at = $rungs[$k]['at'];
        if ($at === '1') continue;               /* the floor is the ELSE */
        if (!$withTask && strpos($at, 't.') !== false) continue;
        $sql .= " WHEN ($at) THEN '$k'";
    }
    return $sql . " ELSE '" . $keys[0] . "' END";
}

function pipe_ladder_all() {
    $out = [];
    foreach (['user', 'seller'] as $r) {
        foreach (pipe_ladder($r) as $k => $v) { $v['role'] = $r; $out[$k] = $v; }
    }
    return $out;
}

/* ---------------------------------------------------------------------------
   WHAT HAPPENED WHEN WE SPOKE TO THEM
   --------------------------------------------------------------------------- */
function pipe_outcomes() {
    return [
        'interested'     => ['Interested',     'fa-thumbs-up',     'bg-emerald-100 text-emerald-700', 'in_progress', 'They want it — keep working on them'],
        'follow_up'      => ['Follow up',      'fa-phone-volume',  'bg-blue-100 text-blue-700',       'followup',    'Call back on a set date'],
        'free_user'      => ['Free user',      'fa-user',          'bg-slate-200 text-slate-700',     'in_progress', 'Moves them to the Free User tab'],
        'kyc_asked'      => ['KYC asked for',  'fa-hourglass-half','bg-amber-100 text-amber-700',     'in_progress', 'Moves them to Pending KYC'],
        'wants_refer'    => ['Wants to refer', 'fa-share-nodes',   'bg-violet-100 text-violet-700',   'in_progress', 'Ready to bring somebody in'],
        'wants_order'    => ['Wants to order', 'fa-cart-shopping', 'bg-emerald-100 text-emerald-700', 'in_progress', 'Ready to place an order'],
        'not_interested' => ['Not interested', 'fa-circle-xmark',  'bg-rose-100 text-rose-700',       'rejected',    'Leaves the list, still counted'],
    ];
}
function pipe_outcome_meta($k) {
    $o = pipe_outcomes();
    return isset($o[$k]) ? $o[$k] : ['Not touched yet', 'fa-circle', 'bg-slate-100 text-slate-500', 'pending', ''];
}

function pipe_decisions() {
    return [
        'accept' => ['Accept', 'fa-circle-check', 'emerald'],
        'hold'   => ['Hold',   'fa-circle-pause', 'amber'],
        'reject' => ['Reject', 'fa-circle-xmark', 'rose'],
    ];
}

/* ---------------------------------------------------------------------------
   SCHEMA
   --------------------------------------------------------------------------- */
function pipe_ensure_schema($db) {
    static $done = false;
    if ($done) return true;
    $done = true;

    tasks_ensure_schema($db);
    $conn = $db->getConnection();

    foreach ([['tasks', 'outcome', 'VARCHAR(30) NULL'],
              ['tasks', 'outcome_at', 'DATETIME NULL'],
              ['tasks', 'advanced', 'INT NOT NULL DEFAULT 0'],
              ['users', 'task_access', 'TEXT NULL']] as $c) {
        try {
            $r = $conn->query("SHOW COLUMNS FROM `{$c[0]}` LIKE '{$c[1]}'");
            if ($r && $r->num_rows === 0) { $conn->query("ALTER TABLE `{$c[0]}` ADD COLUMN `{$c[1]}` {$c[2]}"); }
        } catch (\Throwable $e) {}
    }

    try {
        $conn->query("CREATE TABLE IF NOT EXISTS pipe_reviews (
            id INT AUTO_INCREMENT PRIMARY KEY,
            kind VARCHAR(30) NOT NULL DEFAULT 'kyc',
            ref_id INT NOT NULL DEFAULT 0,
            user_id INT NOT NULL DEFAULT 0,
            decision VARCHAR(20) NOT NULL,
            remark TEXT NULL,
            done_by INT NULL,
            done_by_name VARCHAR(150) NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            KEY idx_pr_kind (kind, ref_id, id),
            KEY idx_pr_user (kind, user_id, id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    } catch (\Throwable $e) {}
    try {
        $r = $conn->query("SHOW COLUMNS FROM pipe_reviews LIKE 'ref_id'");
        if ($r && $r->num_rows === 0) { $conn->query("ALTER TABLE pipe_reviews ADD COLUMN ref_id INT NOT NULL DEFAULT 0"); }
    } catch (\Throwable $e) {}

    return true;
}

function pipe_note_decision($db, $kind, $refId, $userId, $decision, $remark) {
    $me = tasks_actor($db);
    try {
        $db->insert("INSERT INTO pipe_reviews (kind, ref_id, user_id, decision, remark, done_by, done_by_name)
                     VALUES (?,?,?,?,?,?,?)",
            [$kind, (int)$refId, (int)$userId, $decision, $remark, $me['id'] ?: null, $me['name']]);
    } catch (\Throwable $e) {}
}

/* small query helpers */
function pipe_qa($db, $sql, $p = []) { try { return $db->select($sql, $p); } catch (\Throwable $e) { return []; } }
function pipe_q1($db, $sql, $p = []) { $r = pipe_qa($db, $sql, $p); return (int)(isset($r[0]['c']) ? $r[0]['c'] : 0); }
function pipe_likes($q, $n) { return array_fill(0, $n, '%' . $q . '%'); }
function pipe_last_look($r) {
    if (empty($r['last_decision'])) return '<span class="text-slate-300 text-[12px]">not looked at yet</span>';
    return '<span class="pill bg-slate-100 text-slate-600">' . esc(ucfirst((string)$r['last_decision'])) . '</span>'
         . '<p class="text-[11.5px] text-slate-400 mt-1 max-w-[200px]">' . esc((string)$r['last_remark']) . '</p>';
}

/* ---------------------------------------------------------------------------
   THE DESKS
   --------------------------------------------------------------------------- */
function pipe_desks() {
    return [

    'seller_request' => [
        'role' => 'seller', 'label' => 'Seller Requests', 'icon' => 'fa-store', 'first' => true,
        'blurb' => 'People asking to sell on the platform',
        'decisions' => ['accept', 'hold', 'reject'],
        'cols' => ['Who', 'Business', 'Asked on', 'Last look'],
        'search' => 'name, mobile or business',
        'count' => function ($db, $q) {
            return pipe_q1($db, "SELECT COUNT(*) c FROM seller_upgrade_requests r JOIN users u ON u.id = r.user_id
                                 WHERE r.status = 'Pending'" . ($q !== '' ? " AND (u.name LIKE ? OR u.mobile LIKE ? OR r.business_name LIKE ?)" : ''),
                                 $q !== '' ? pipe_likes($q, 3) : []);
        },
        'rows' => function ($db, $q, $lim, $off) {
            return pipe_qa($db, "SELECT r.*, u.name, u.mobile,
                        (SELECT p.decision FROM pipe_reviews p WHERE p.kind='seller_request' AND p.ref_id=r.id ORDER BY p.id DESC LIMIT 1) last_decision,
                        (SELECT p.remark   FROM pipe_reviews p WHERE p.kind='seller_request' AND p.ref_id=r.id ORDER BY p.id DESC LIMIT 1) last_remark
                     FROM seller_upgrade_requests r JOIN users u ON u.id = r.user_id
                    WHERE r.status = 'Pending'" . ($q !== '' ? " AND (u.name LIKE ? OR u.mobile LIKE ? OR r.business_name LIKE ?)" : '') . "
                    ORDER BY r.id DESC LIMIT $lim OFFSET $off", $q !== '' ? pipe_likes($q, 3) : []);
        },
        'key'   => function ($r) { return (int)$r['id']; },
        'title' => function ($r) { return $r['name'] . ' · ' . $r['business_name']; },
        'row'   => function ($r) {
            return [
                '<p class="font-bold text-slate-800">' . esc($r['name']) . '</p><p class="text-[12px] text-slate-500">' . esc($r['mobile']) . '</p>
                 <a href="upgrade_requests.php" class="text-[11px] font-bold text-indigo-600 hover:underline">Full request screen &rarr;</a>',
                '<p class="font-semibold text-slate-700">' . esc($r['business_name']) . '</p>
                 <p class="text-[12px] text-slate-500">' . esc($r['business_phone']) . '</p>
                 <p class="text-[11.5px] text-slate-400 max-w-[240px]">' . esc((string)$r['business_address']) . '</p>',
                '<span class="text-[12.5px] text-slate-600">' . date('d M Y', strtotime((string)$r['created_at'])) . '</span>',
                pipe_last_look($r),
            ];
        },
        'decide' => function ($db, $id, $dec, $remark) {
            $r = pipe_qa($db, "SELECT * FROM seller_upgrade_requests WHERE id = ?", [(int)$id]);
            if (empty($r)) return ['ok' => false, 'error' => 'That request is gone.'];
            $r = $r[0]; $uid = (int)$r['user_id'];
            if ($dec === 'accept') {
                try {
                    $db->insert("UPDATE seller_upgrade_requests SET status = 'Approved' WHERE id = ?", [(int)$id]);
                    $db->insert("UPDATE users SET user_type = 'seller' WHERE id = ?", [$uid]);
                    $has = pipe_qa($db, "SELECT id FROM sellers WHERE user_id = ?", [$uid]);
                    if (empty($has)) {
                        $u = pipe_qa($db, "SELECT name, mobile, email FROM users WHERE id = ?", [$uid]);
                        $u = !empty($u) ? $u[0] : ['name' => $r['business_name'], 'mobile' => $r['business_phone'], 'email' => null];
                        $db->insert("INSERT INTO sellers (user_id, name, phone, email, business_name, full_address, status)
                                     VALUES (?,?,?,?,?,?,1)",
                            [$uid, $u['name'], $u['mobile'], $u['email'], $r['business_name'], $r['business_address']]);
                    } else { $db->insert("UPDATE sellers SET status = 1 WHERE user_id = ?", [$uid]); }
                    /* they change side of the house, so their old row goes with them */
                    $db->insert("UPDATE tasks SET scope_role = 'seller', module_key = 's_lead' WHERE target_user_id = ? AND scope_role = 'user'", [$uid]);
                } catch (\Throwable $e) { return ['ok' => false, 'error' => 'It could not be saved.']; }
            } elseif ($dec === 'reject') {
                try { $db->insert("UPDATE seller_upgrade_requests SET status = 'Rejected' WHERE id = ?", [(int)$id]); }
                catch (\Throwable $e) { return ['ok' => false, 'error' => 'It could not be saved.']; }
            }
            pipe_note_decision($db, 'seller_request', $id, $uid, $dec, $remark);
            return ['ok' => true];
        },
    ],

    /* The ladder's "KYC Request" rung and this desk hold the same people —
       everyone who has sent their documents in. They are not a duplicate: the
       rung says where somebody has got to, and this is where you read the
       documents and answer. The names say which is which. */
    'kyc_pending'        => pipe_kyc_desk('user',   'customer', 'KYC · approve or reject', 'Read the documents and answer — the same people as the KYC Request rung'),
    'kyc_pending_seller' => pipe_kyc_desk('seller', 'seller',   'KYC · approve or reject', 'Read the documents and answer — the same people as the KYC Request rung'),

    'product_request' => [
        'role' => 'seller', 'label' => 'Product Requests', 'icon' => 'fa-box-open',
        'blurb' => 'Listings a seller has submitted, not live yet',
        'decisions' => ['accept', 'hold', 'reject'],
        'cols' => ['Product', 'Seller', 'Price', 'Last look'],
        'search' => 'product or seller',
        'count' => function ($db, $q) {
            return pipe_q1($db, "SELECT COUNT(*) c FROM seller_services s LEFT JOIN users u ON u.id = s.seller_id
                                 WHERE COALESCE(s.status,0) = 0" . ($q !== '' ? " AND (s.title LIKE ? OR u.name LIKE ?)" : ''),
                                 $q !== '' ? pipe_likes($q, 2) : []);
        },
        'rows' => function ($db, $q, $lim, $off) {
            return pipe_qa($db, "SELECT s.*, u.name AS seller_name, u.mobile AS seller_mobile,
                        c.title AS cat_title, c.category_key AS cat_key,
                        (SELECT p.decision FROM pipe_reviews p WHERE p.kind='product_request' AND p.ref_id=s.id ORDER BY p.id DESC LIMIT 1) last_decision,
                        (SELECT p.remark   FROM pipe_reviews p WHERE p.kind='product_request' AND p.ref_id=s.id ORDER BY p.id DESC LIMIT 1) last_remark
                     FROM seller_services s
                     LEFT JOIN users u ON u.id = s.seller_id
                     LEFT JOIN service_categories c ON c.id = s.category_id
                    WHERE COALESCE(s.status,0) = 0" . ($q !== '' ? " AND (s.title LIKE ? OR u.name LIKE ?)" : '') . "
                    ORDER BY s.id DESC LIMIT $lim OFFSET $off", $q !== '' ? pipe_likes($q, 2) : []);
        },
        'key'   => function ($r) { return (int)$r['id']; },
        'title' => function ($r) { return (string)$r['title']; },
        'row'   => function ($r) {
            /* Accept / hold / reject is the quick answer. When the listing needs
               a proper read — the price, the KYC it asks for, where it can be
               sold — this hands over to that category's own review screen,
               which is why those screens are no longer a second copy of this
               list in the sidebar. */
            $full = 'pending_services.php';
            if (function_exists('pmp_service_file') && !empty($r['cat_key'])) {
                $__f = pmp_service_file(['id' => (int)$r['category_id'], 'category_key' => $r['cat_key']]);
                if (is_file(dirname(__DIR__) . '/admin/' . $__f)) { $full = $__f; }
            }
            return [
                '<p class="font-bold text-slate-800">' . esc($r['title']) . '</p>
                 <p class="text-[12px] text-slate-500">' . esc((string)$r['cat_title']) . '</p>
                 <p class="text-[11.5px] text-slate-400 max-w-[260px]">' . pipe_cut($r['description'], 110) . '</p>
                 <a href="' . esc($full) . '" class="inline-block mt-1 text-[11px] font-bold text-indigo-600 hover:underline">Open the full listing &rarr;</a>',
                '<p class="font-semibold text-slate-700">' . esc((string)$r['seller_name']) . '</p>
                 <p class="text-[12px] text-slate-500">' . esc((string)$r['seller_mobile']) . '</p>',
                '<span class="font-extrabold text-slate-800">₹' . number_format((float)($r['discount_price'] > 0 ? $r['discount_price'] : $r['price']), 2) . '</span>',
                pipe_last_look($r),
            ];
        },
        'decide' => function ($db, $id, $dec, $remark) {
            $s = pipe_qa($db, "SELECT id, seller_id FROM seller_services WHERE id = ?", [(int)$id]);
            if (empty($s)) return ['ok' => false, 'error' => 'That listing is gone.'];
            if ($dec === 'accept') {
                try { $db->insert("UPDATE seller_services SET status = 1 WHERE id = ?", [(int)$id]); }
                catch (\Throwable $e) { return ['ok' => false, 'error' => 'It could not be saved.']; }
            } elseif ($dec === 'reject') {
                try { $db->insert("UPDATE seller_services SET status = 2 WHERE id = ?", [(int)$id]); }
                catch (\Throwable $e) { return ['ok' => false, 'error' => 'It could not be saved.']; }
            }
            pipe_note_decision($db, 'product_request', $id, (int)$s[0]['seller_id'], $dec, $remark);
            return ['ok' => true];
        },
    ],

    'support_user'   => pipe_support_desk('user',   'customer', 'Messages from members'),
    'support_seller' => pipe_support_desk('seller', 'seller',   'Messages from sellers'),

    'wallet' => pipe_money_desk('wallet', 'Wallet', 'fa-wallet',
        'Withdrawal requests waiting to be looked at', 'pending'),
    'payout' => pipe_money_desk('payout', 'Payout', 'fa-money-bill-transfer',
        'Approved withdrawals waiting for the money to actually go', 'processing'),

    'website' => [
        'role' => 'office', 'label' => 'Website Requests', 'icon' => 'fa-globe',
        'blurb' => 'Members asking for a website of their own',
        'decisions' => ['accept', 'hold', 'reject'],
        'cols' => ['Who', 'What they want', 'Asked on', 'Last look'],
        'search' => 'name or website',
        'count' => function ($db, $q) {
            return pipe_q1($db, "SELECT COUNT(*) c FROM website_requests w JOIN users u ON u.id = w.user_id
                                 WHERE LOWER(w.status) = 'pending'" . ($q !== '' ? " AND (u.name LIKE ? OR w.website_name LIKE ?)" : ''),
                                 $q !== '' ? pipe_likes($q, 2) : []);
        },
        'rows' => function ($db, $q, $lim, $off) {
            return pipe_qa($db, "SELECT w.*, u.name, u.mobile,
                        (SELECT p.decision FROM pipe_reviews p WHERE p.kind='website' AND p.ref_id=w.id ORDER BY p.id DESC LIMIT 1) last_decision,
                        (SELECT p.remark   FROM pipe_reviews p WHERE p.kind='website' AND p.ref_id=w.id ORDER BY p.id DESC LIMIT 1) last_remark
                     FROM website_requests w JOIN users u ON u.id = w.user_id
                    WHERE LOWER(w.status) = 'pending'" . ($q !== '' ? " AND (u.name LIKE ? OR w.website_name LIKE ?)" : '') . "
                    ORDER BY w.id DESC LIMIT $lim OFFSET $off", $q !== '' ? pipe_likes($q, 2) : []);
        },
        'key'   => function ($r) { return (int)$r['id']; },
        'title' => function ($r) { return (string)$r['website_name']; },
        'row'   => function ($r) {
            return [
                '<p class="font-bold text-slate-800">' . esc($r['name']) . '</p>
                 <p class="text-[12px] text-slate-500">' . esc((string)$r['contact_number'] ?: (string)$r['mobile']) . '</p>',
                '<p class="font-semibold text-slate-700">' . esc((string)$r['website_name']) . '</p>
                 <p class="text-[11.5px] text-slate-400 max-w-[260px]">' . pipe_cut($r['reason'], 120) . '</p>',
                '<span class="text-[12.5px] text-slate-600">' . date('d M Y', strtotime((string)$r['created_at'])) . '</span>',
                pipe_last_look($r),
            ];
        },
        'decide' => function ($db, $id, $dec, $remark) {
            $w = pipe_qa($db, "SELECT id, user_id FROM website_requests WHERE id = ?", [(int)$id]);
            if (empty($w)) return ['ok' => false, 'error' => 'That request is gone.'];
            $st = $dec === 'accept' ? 'approved' : ($dec === 'reject' ? 'rejected' : null);
            if ($st !== null) {
                try { $db->insert("UPDATE website_requests SET status = ?, updated_at = NOW() WHERE id = ?", [$st, (int)$id]); }
                catch (\Throwable $e) { return ['ok' => false, 'error' => 'It could not be saved.']; }
            }
            pipe_note_decision($db, 'website', $id, (int)$w[0]['user_id'], $dec, $remark);
            return ['ok' => true];
        },
    ],

    /* ---------------------------------------------------------------------
       OUT ON THE ROAD

       Three desks about money that has not arrived yet, in the order it moves:

         Visit Collection   somebody has to go and do the job, and collect
         Verify Collection  they say they collected — is it true?
         Seller Dues        what a seller is holding that has not come in

       Each is assignable like any other desk, so an admin can hand Visit
       Collection to one person and Seller Dues to another and neither sees the
       other's work.
       --------------------------------------------------------------------- */

    'visit_collect' => [
        'role' => 'office', 'label' => 'Visit Collection', 'icon' => 'fa-person-walking',
        'blurb' => 'Orders that need somebody to go out — and the money to come back with them',
        'decisions' => ['accept', 'hold', 'reject'],
        'cols' => ['Order', 'Customer', 'Still owed', 'Last look'],
        'search' => 'order code, customer or mobile',
        'remark_label' => 'What happened on the visit',
        'count' => function ($db, $q) {
            return pipe_q1($db, "SELECT COUNT(*) c FROM orders o
                                 WHERE o.fulfilment <> 'online'
                                   AND LOWER(COALESCE(o.visit_status,'none')) NOT IN ('done','completed','cancelled')
                                   AND LOWER(COALESCE(o.status,'')) NOT IN ('cancelled','refunded')"
                                 . ($q !== '' ? " AND (o.order_code LIKE ? OR o.customer_name LIKE ? OR o.customer_mobile LIKE ?)" : ''),
                                 $q !== '' ? pipe_likes($q, 3) : []);
        },
        'rows' => function ($db, $q, $lim, $off) {
            return pipe_qa($db, "SELECT o.*, u.name AS member_name, u.mobile AS member_mobile,
                        (SELECT p.decision FROM pipe_reviews p WHERE p.kind='visit_collect' AND p.ref_id=o.id ORDER BY p.id DESC LIMIT 1) last_decision,
                        (SELECT p.remark   FROM pipe_reviews p WHERE p.kind='visit_collect' AND p.ref_id=o.id ORDER BY p.id DESC LIMIT 1) last_remark
                     FROM orders o LEFT JOIN users u ON u.id = o.user_id
                    WHERE o.fulfilment <> 'online'
                      AND LOWER(COALESCE(o.visit_status,'none')) NOT IN ('done','completed','cancelled')
                      AND LOWER(COALESCE(o.status,'')) NOT IN ('cancelled','refunded')"
                    . ($q !== '' ? " AND (o.order_code LIKE ? OR o.customer_name LIKE ? OR o.customer_mobile LIKE ?)" : '') . "
                    ORDER BY (o.due_amount > 0) DESC, o.id DESC LIMIT $lim OFFSET $off",
                    $q !== '' ? pipe_likes($q, 3) : []);
        },
        'key'   => function ($r) { return (int)$r['id']; },
        'title' => function ($r) { return ($r['order_code'] ?: ('Order #' . $r['id'])) . ' · ' . $r['service_name']; },
        'row'   => function ($r) {
            $when = !empty($r['visit_at']) ? date('d M Y', strtotime((string)$r['visit_at'])) : 'not fixed yet';
            return [
                '<p class="font-bold text-slate-800">' . esc((string)($r['order_code'] ?: ('#' . $r['id']))) . '</p>
                 <p class="text-[12px] text-slate-500">' . esc((string)$r['service_name']) . '</p>
                 <p class="text-[11px] text-slate-400">Visit: ' . esc($when) . '</p>',
                '<p class="font-semibold text-slate-700">' . esc((string)($r['customer_name'] ?: $r['member_name'])) . '</p>
                 <p class="text-[12px] text-slate-500">' . esc((string)($r['customer_mobile'] ?: $r['member_mobile'])) . '</p>
                 <p class="text-[11.5px] text-slate-400 max-w-[240px]">' . pipe_cut((string)$r['address'], 90) . '</p>',
                '<p class="font-extrabold text-slate-800">₹' . number_format((float)$r['due_amount'], 2) . '</p>
                 <p class="text-[11.5px] text-slate-500">of ₹' . number_format((float)$r['amount'], 2) . ' · paid ₹' . number_format((float)$r['paid_amount'], 2) . '</p>',
                pipe_last_look($r),
            ];
        },
        'decide' => function ($db, $id, $dec, $remark) {
            $o = pipe_qa($db, "SELECT id, user_id, due_amount FROM orders WHERE id = ?", [(int)$id]);
            if (empty($o)) return ['ok' => false, 'error' => 'That order is gone.'];
            /* accept = the visit happened. Money actually handed over is recorded
               by whoever took it, on the order screen — this desk only closes the
               visit, so a tick here can never invent a payment. */
            if ($dec === 'accept') {
                try { $db->insert("UPDATE orders SET visit_status = 'done', visit_at = COALESCE(visit_at, NOW()), updated_at = NOW() WHERE id = ?", [(int)$id]); }
                catch (\Throwable $e) { return ['ok' => false, 'error' => 'It could not be saved.']; }
            } elseif ($dec === 'reject') {
                try { $db->insert("UPDATE orders SET visit_status = 'failed', updated_at = NOW() WHERE id = ?", [(int)$id]); }
                catch (\Throwable $e) { return ['ok' => false, 'error' => 'It could not be saved.']; }
            }
            pipe_note_decision($db, 'visit_collect', $id, (int)$o[0]['user_id'], $dec, $remark);
            return ['ok' => true];
        },
    ],

    'verify_collect' => [
        'role' => 'office', 'label' => 'Verify Collection', 'icon' => 'fa-receipt',
        'blurb' => 'Cash a seller says they took — waiting to be checked',
        'decisions' => ['accept', 'hold', 'reject'],
        'cols' => ['Taken by', 'From', 'Amount', 'Last look'],
        'search' => 'seller, customer or reference',
        'remark_label' => 'Why it is right, or why it is not',
        'count' => function ($db, $q) {
            return pipe_q1($db, "SELECT COUNT(*) c FROM seller_collections sc LEFT JOIN users u ON u.id = sc.seller_id
                                 WHERE LOWER(sc.status) = 'pending'"
                                 . ($q !== '' ? " AND (u.name LIKE ? OR sc.customer_name LIKE ? OR sc.reference LIKE ?)" : ''),
                                 $q !== '' ? pipe_likes($q, 3) : []);
        },
        'rows' => function ($db, $q, $lim, $off) {
            return pipe_qa($db, "SELECT sc.*, u.name AS seller_name, u.mobile AS seller_mobile,
                        (SELECT p.decision FROM pipe_reviews p WHERE p.kind='verify_collect' AND p.ref_id=sc.id ORDER BY p.id DESC LIMIT 1) last_decision,
                        (SELECT p.remark   FROM pipe_reviews p WHERE p.kind='verify_collect' AND p.ref_id=sc.id ORDER BY p.id DESC LIMIT 1) last_remark
                     FROM seller_collections sc LEFT JOIN users u ON u.id = sc.seller_id
                    WHERE LOWER(sc.status) = 'pending'"
                    . ($q !== '' ? " AND (u.name LIKE ? OR sc.customer_name LIKE ? OR sc.reference LIKE ?)" : '') . "
                    ORDER BY sc.id DESC LIMIT $lim OFFSET $off", $q !== '' ? pipe_likes($q, 3) : []);
        },
        'key'   => function ($r) { return (int)$r['id']; },
        'title' => function ($r) { return $r['seller_name'] . ' · ₹' . number_format((float)$r['amount'], 2); },
        'row'   => function ($r) {
            return [
                '<p class="font-bold text-slate-800">' . esc((string)$r['seller_name']) . '</p>
                 <p class="text-[12px] text-slate-500">' . esc((string)$r['seller_mobile']) . '</p>
                 <p class="text-[11px] text-slate-400">' . esc((string)$r['recorded_by_name']) . ' entered it</p>',
                '<p class="font-semibold text-slate-700">' . esc((string)$r['customer_name']) . '</p>
                 <p class="text-[12px] text-slate-500">' . esc((string)($r['order_code'] ?: ('Order #' . $r['order_id']))) . '</p>',
                '<p class="font-extrabold text-slate-800">₹' . number_format((float)$r['amount'], 2) . '</p>
                 <p class="text-[11.5px] text-slate-500">' . esc(ucfirst((string)$r['mode'])) . ($r['reference'] ? ' · ' . esc((string)$r['reference']) : '') . '</p>
                 <p class="text-[11px] text-slate-400">' . date('d M Y', strtotime((string)$r['created_at'])) . '</p>',
                pipe_last_look($r),
            ];
        },
        'decide' => function ($db, $id, $dec, $remark) {
            $me = tasks_actor($db);
            $c  = pipe_qa($db, "SELECT id, seller_id, customer_id FROM seller_collections WHERE id = ?", [(int)$id]);
            if (empty($c)) return ['ok' => false, 'error' => 'That entry is gone.'];
            $st = $dec === 'accept' ? 'approved' : ($dec === 'reject' ? 'rejected' : null);
            if ($st !== null) {
                try {
                    $db->insert("UPDATE seller_collections SET status = ?, approved_by = ?, approved_by_name = ?, approved_at = NOW() WHERE id = ?",
                        [$st, $me['id'] ?: null, $me['name'], (int)$id]);
                } catch (\Throwable $e) { return ['ok' => false, 'error' => 'It could not be saved.']; }
            }
            pipe_note_decision($db, 'verify_collect', $id, (int)$c[0]['seller_id'], $dec, $remark);
            return ['ok' => true];
        },
    ],

    'seller_dues' => [
        'role' => 'office', 'label' => 'Seller Dues', 'icon' => 'fa-hand-holding-dollar',
        'blurb' => 'Money sellers have collected and not yet handed over',
        'decisions' => ['accept', 'hold', 'reject'],
        'cols' => ['Seller', 'Collected', 'Handed over', 'Last look'],
        'search' => 'seller name or mobile',
        'remark_label' => 'How the money came in (cash, UPI, reference)',
        /* One row per seller who is still holding something. The sums are
           collections that have been verified, less what has already come in —
           so an unverified collection never shows up as a debt. */
        'count' => function ($db, $q) {
            return pipe_q1($db, "SELECT COUNT(*) c FROM (
                    SELECT sc.seller_id,
                           SUM(sc.amount) - COALESCE((SELECT SUM(st.amount) FROM seller_settlements st
                                                      WHERE st.seller_id = sc.seller_id AND st.direction = 'in'), 0) AS owed
                      FROM seller_collections sc LEFT JOIN users u ON u.id = sc.seller_id
                     WHERE LOWER(sc.status) = 'approved'"
                     . ($q !== '' ? " AND (u.name LIKE ? OR u.mobile LIKE ?)" : '') . "
                     GROUP BY sc.seller_id HAVING owed > 0.009) d",
                     $q !== '' ? pipe_likes($q, 2) : []);
        },
        'rows' => function ($db, $q, $lim, $off) {
            return pipe_qa($db, "SELECT sc.seller_id AS id, u.name AS seller_name, u.mobile AS seller_mobile,
                        SUM(sc.amount) AS collected, COUNT(*) AS entries, MAX(sc.created_at) AS last_at,
                        COALESCE((SELECT SUM(st.amount) FROM seller_settlements st
                                  WHERE st.seller_id = sc.seller_id AND st.direction = 'in'), 0) AS handed,
                        SUM(sc.amount) - COALESCE((SELECT SUM(st.amount) FROM seller_settlements st
                                  WHERE st.seller_id = sc.seller_id AND st.direction = 'in'), 0) AS owed,
                        (SELECT p.decision FROM pipe_reviews p WHERE p.kind='seller_dues' AND p.ref_id=sc.seller_id ORDER BY p.id DESC LIMIT 1) last_decision,
                        (SELECT p.remark   FROM pipe_reviews p WHERE p.kind='seller_dues' AND p.ref_id=sc.seller_id ORDER BY p.id DESC LIMIT 1) last_remark
                     FROM seller_collections sc LEFT JOIN users u ON u.id = sc.seller_id
                    WHERE LOWER(sc.status) = 'approved'"
                    . ($q !== '' ? " AND (u.name LIKE ? OR u.mobile LIKE ?)" : '') . "
                    GROUP BY sc.seller_id, u.name, u.mobile
                    HAVING owed > 0.009
                    ORDER BY owed DESC LIMIT $lim OFFSET $off", $q !== '' ? pipe_likes($q, 2) : []);
        },
        'key'   => function ($r) { return (int)$r['id']; },
        'title' => function ($r) { return $r['seller_name'] . ' · ₹' . number_format((float)$r['owed'], 2) . ' owed'; },
        'row'   => function ($r) {
            return [
                '<p class="font-bold text-slate-800">' . esc((string)$r['seller_name']) . '</p>
                 <p class="text-[12px] text-slate-500">' . esc((string)$r['seller_mobile']) . '</p>',
                '<p class="font-semibold text-slate-700">₹' . number_format((float)$r['collected'], 2) . '</p>
                 <p class="text-[11.5px] text-slate-500">' . (int)$r['entries'] . ' collection(s)</p>
                 <p class="text-[11px] text-slate-400">last ' . date('d M Y', strtotime((string)$r['last_at'])) . '</p>',
                '<p class="text-[12.5px] text-slate-600">₹' . number_format((float)$r['handed'], 2) . ' in</p>
                 <p class="font-extrabold text-rose-600">₹' . number_format((float)$r['owed'], 2) . ' still out</p>',
                pipe_last_look($r),
            ];
        },
        'decide' => function ($db, $id, $dec, $remark) {
            $me  = tasks_actor($db);
            $sid = (int)$id;
            if ($dec === 'accept') {
                /* The seller has handed the money over. The amount is worked out
                   here rather than typed in, so the ledger cannot drift: it is
                   exactly what is outstanding at this moment. */
                $r = pipe_qa($db, "SELECT SUM(sc.amount) - COALESCE((SELECT SUM(st.amount) FROM seller_settlements st
                                          WHERE st.seller_id = ? AND st.direction = 'in'), 0) AS owed
                                     FROM seller_collections sc
                                    WHERE sc.seller_id = ? AND LOWER(sc.status) = 'approved'", [$sid, $sid]);
                $owed = !empty($r) ? round((float)$r[0]['owed'], 2) : 0.0;
                if ($owed <= 0) { return ['ok' => false, 'error' => 'Nothing is outstanding for this seller any more.']; }
                try {
                    $db->insert("INSERT INTO seller_settlements (seller_id, direction, amount, mode, reference, note, created_by, created_by_name)
                                 VALUES (?, 'in', ?, 'cash', ?, ?, ?, ?)",
                        [$sid, $owed, pipe_cut($remark, 110), 'Cleared from the Seller Dues desk', $me['id'] ?: null, $me['name']]);
                } catch (\Throwable $e) { return ['ok' => false, 'error' => 'It could not be saved.']; }
            }
            pipe_note_decision($db, 'seller_dues', $id, $sid, $dec, $remark);
            return ['ok' => true];
        },
    ],

    'general' => [
        'role' => 'office', 'label' => 'General', 'icon' => 'fa-sliders',
        'blurb' => 'The parts of the site that are edited, not decided on',
        'decisions' => [],
        'links' => [
            ['Carousel &amp; hero slides', 'fa-images',      'hero_slides.php',        'The pictures at the top of the home page'],
            ['Website settings',           'fa-gear',        'settings.php',           'Name, logo, contact details, the lot'],
            ['Subscription plans',         'fa-crown',       'subscription_plans.php', 'What a subscription costs and gives'],
            ['Notifications',              'fa-bell',        'notifications.php',      'What members are told, and when'],
            ['Payment settings',           'fa-credit-card', 'payment_settings.php',   'The gateway keys and charges'],
            ['SMS &amp; OTP',              'fa-comment-sms', 'sms_settings.php',       'The gateway, the codes, the wording'],
        ],
    ],

    ];
}

function pipe_money_desk($kind, $label, $icon, $blurb, $status) {
    return [
        'role' => 'office', 'label' => $label, 'icon' => $icon, 'blurb' => $blurb,
        'decisions' => ['accept', 'hold', 'reject'],
        'cols' => ['Who', 'Amount', 'Where it goes', 'Last look'],
        'search' => 'name, mobile or reference',
        'remark_label' => $kind === 'payout' ? 'UTR number, or why it is held' : 'Remark',
        'count' => function ($db, $q) use ($status) {
            return pipe_q1($db, "SELECT COUNT(*) c FROM withdrawal_requests w JOIN users u ON u.id = w.user_id
                                 WHERE LOWER(w.status) = ?" . ($q !== '' ? " AND (u.name LIKE ? OR u.mobile LIKE ? OR w.reference_no LIKE ?)" : ''),
                                 array_merge([$status], $q !== '' ? pipe_likes($q, 3) : []));
        },
        'rows' => function ($db, $q, $lim, $off) use ($status, $kind) {
            return pipe_qa($db, "SELECT w.*, u.name, u.mobile,
                        (SELECT p.decision FROM pipe_reviews p WHERE p.kind='$kind' AND p.ref_id=w.id ORDER BY p.id DESC LIMIT 1) last_decision,
                        (SELECT p.remark   FROM pipe_reviews p WHERE p.kind='$kind' AND p.ref_id=w.id ORDER BY p.id DESC LIMIT 1) last_remark
                     FROM withdrawal_requests w JOIN users u ON u.id = w.user_id
                    WHERE LOWER(w.status) = ?" . ($q !== '' ? " AND (u.name LIKE ? OR u.mobile LIKE ? OR w.reference_no LIKE ?)" : '') . "
                    ORDER BY w.id DESC LIMIT $lim OFFSET $off",
                    array_merge([$status], $q !== '' ? pipe_likes($q, 3) : []));
        },
        'key'   => function ($r) { return (int)$r['id']; },
        'title' => function ($r) { return $r['name'] . ' · ₹' . number_format((float)$r['amount'], 2); },
        'row'   => function ($r) {
            return [
                '<p class="font-bold text-slate-800">' . esc($r['name']) . '</p>
                 <p class="text-[12px] text-slate-500">' . esc($r['mobile']) . '</p>
                 <p class="text-[11px] font-mono text-slate-400">' . esc((string)$r['reference_no']) . '</p>',
                '<p class="font-extrabold text-slate-800">₹' . number_format((float)$r['amount'], 2) . '</p>
                 <p class="text-[11.5px] text-slate-500">₹' . number_format((float)$r['net_amount'], 2) . ' after ₹' . number_format((float)$r['charge'], 2) . '</p>',
                '<p class="text-[12.5px] text-slate-700">' . esc((string)$r['bank_name']) . '</p>
                 <p class="text-[12px] text-slate-500">' . esc((string)$r['account_number']) . ' · ' . esc((string)$r['ifsc_code']) . '</p>',
                pipe_last_look($r),
            ];
        },
        'decide' => function ($db, $id, $dec, $remark) use ($kind) {
            $me = tasks_actor($db);
            $w = pipe_qa($db, "SELECT id, user_id FROM withdrawal_requests WHERE id = ?", [(int)$id]);
            if (empty($w)) return ['ok' => false, 'error' => 'That request is gone.'];

            if ($kind === 'wallet') {
                /* Accepting only moves it on to the payout desk — the money is
                   marked sent there, once a UTR exists. */
                $st = $dec === 'accept' ? 'processing' : ($dec === 'reject' ? 'rejected' : null);
                if ($st !== null) {
                    try { $db->insert("UPDATE withdrawal_requests SET status = ?, admin_remark = ?, reviewed_by = ?, reviewed_by_name = ?, reviewed_at = NOW() WHERE id = ?",
                                      [$st, $remark, $me['id'] ?: null, $me['name'], (int)$id]); }
                    catch (\Throwable $e) { return ['ok' => false, 'error' => 'It could not be saved.']; }
                }
            } else {
                if ($dec === 'accept') {
                    if ($remark === '') return ['ok' => false, 'error' => 'Put the UTR number in the remark before marking it paid.'];
                    try { $db->insert("UPDATE withdrawal_requests SET status = 'approved', utr_number = ?, admin_remark = ?, reviewed_by = ?, reviewed_by_name = ?, reviewed_at = NOW() WHERE id = ?",
                                      [substr($remark, 0, 60), $remark, $me['id'] ?: null, $me['name'], (int)$id]); }
                    catch (\Throwable $e) { return ['ok' => false, 'error' => 'It could not be saved.']; }
                } elseif ($dec === 'reject') {
                    try { $db->insert("UPDATE withdrawal_requests SET status = 'rejected', admin_remark = ?, reviewed_at = NOW() WHERE id = ?", [$remark, (int)$id]); }
                    catch (\Throwable $e) { return ['ok' => false, 'error' => 'It could not be saved.']; }
                }
            }
            pipe_note_decision($db, $kind, $id, (int)$w[0]['user_id'], $dec, $remark);
            return ['ok' => true];
        },
    ];
}

function pipe_kyc_desk($role, $userType, $label, $blurb) {
    return [
        'role' => $role, 'label' => $label, 'icon' => 'fa-file-circle-check', 'blurb' => $blurb,
        'decisions' => ['accept', 'hold', 'reject'],
        'cols' => ['Member', 'On the form', 'Documents', 'Last look'],
        'search' => 'name, mobile or Aadhaar',
        'count' => function ($db, $q) use ($userType) {
            return pipe_q1($db, "SELECT COUNT(*) c FROM kyc_details k JOIN users u ON u.id = k.user_id
                                 WHERE k.status = 'pending' AND u.deleted_at IS NULL AND u.user_type = ?"
                                 . ($q !== '' ? " AND (u.name LIKE ? OR u.mobile LIKE ? OR k.aadhar_number LIKE ?)" : ''),
                                 array_merge([$userType], $q !== '' ? pipe_likes($q, 3) : []));
        },
        'rows' => function ($db, $q, $lim, $off) use ($userType) {
            return pipe_qa($db, "SELECT k.*, u.name AS acc_name, u.mobile AS acc_mobile,
                        (SELECT p.decision FROM pipe_reviews p WHERE p.kind='kyc' AND p.user_id=u.id ORDER BY p.id DESC LIMIT 1) last_decision,
                        (SELECT p.remark   FROM pipe_reviews p WHERE p.kind='kyc' AND p.user_id=u.id ORDER BY p.id DESC LIMIT 1) last_remark
                     FROM kyc_details k JOIN users u ON u.id = k.user_id
                    WHERE k.status = 'pending' AND u.deleted_at IS NULL AND u.user_type = ?"
                    . ($q !== '' ? " AND (u.name LIKE ? OR u.mobile LIKE ? OR k.aadhar_number LIKE ?)" : '') . "
                    ORDER BY k.updated_at IS NULL, k.updated_at DESC, k.id DESC LIMIT $lim OFFSET $off",
                    array_merge([$userType], $q !== '' ? pipe_likes($q, 3) : []));
        },
        'key'   => function ($r) { return (int)$r['user_id']; },
        'title' => function ($r) { return (string)$r['acc_name']; },
        'row'   => function ($r) {
            $docs = 0;
            foreach (['aadhar_front', 'aadhar_back', 'pan_card', 'photo', 'address_proof', 'bank_passbook'] as $c) {
                if (!empty($r[$c])) $docs++;
            }
            return [
                '<p class="font-bold text-slate-800">' . esc((string)$r['acc_name']) . '</p>
                 <p class="text-[12px] text-slate-500">' . esc((string)$r['acc_mobile']) . '</p>',
                '<p class="text-[12.5px] text-slate-700">' . esc((string)$r['full_name']) . '</p>
                 <p class="text-[12px] text-slate-400">'
                    . ($r['aadhar_number'] !== '' ? 'Aadhaar ' . esc(substr((string)$r['aadhar_number'], -4)) : 'no Aadhaar')
                    . ' · ' . ($r['pincode'] !== '' ? esc((string)$r['pincode']) : 'no pin') . '</p>',
                '<span class="pill ' . ($docs >= 3 ? 'bg-emerald-100 text-emerald-700' : 'bg-amber-100 text-amber-700') . '">
                    <i class="fas fa-paperclip"></i> ' . $docs . ' file' . ($docs === 1 ? '' : 's') . '</span>
                 <a href="user_list.php?kyc=' . (int)$r['user_id'] . '" target="_blank" class="ml-1 text-[11.5px] font-bold text-indigo-600 hover:underline">Open</a>
                 <a href="kyc_pending.php?status=pending" class="block mt-1 text-[11px] font-bold text-indigo-600 hover:underline">Full review screen &rarr;</a>',
                pipe_last_look($r),
            ];
        },
        'decide' => function ($db, $userId, $dec, $remark) { return pipe_kyc_decide($db, $userId, $dec, $remark); },
    ];
}

function pipe_support_desk($role, $userType, $blurb) {
    return [
        'role' => $role, 'label' => 'Support', 'icon' => 'fa-headset', 'blurb' => $blurb,
        'decisions' => ['accept', 'hold', 'reject'],
        'decision_labels' => ['accept' => 'Reply &amp; close', 'hold' => 'Keep open', 'reject' => 'Close without action'],
        'remark_label' => 'Your reply',
        'cols' => ['Who', 'Subject', 'What they wrote', 'State'],
        'search' => 'name, mobile or subject',
        'count' => function ($db, $q) use ($userType) {
            return pipe_q1($db, "SELECT COUNT(*) c FROM support_tickets t JOIN users u ON u.id = t.user_id
                                 WHERE t.status NOT IN ('Closed','Resolved') AND u.user_type = ?"
                                 . ($q !== '' ? " AND (u.name LIKE ? OR u.mobile LIKE ? OR t.subject LIKE ?)" : ''),
                                 array_merge([$userType], $q !== '' ? pipe_likes($q, 3) : []));
        },
        'rows' => function ($db, $q, $lim, $off) use ($userType) {
            return pipe_qa($db, "SELECT t.*, u.name, u.mobile FROM support_tickets t JOIN users u ON u.id = t.user_id
                    WHERE t.status NOT IN ('Closed','Resolved') AND u.user_type = ?"
                    . ($q !== '' ? " AND (u.name LIKE ? OR u.mobile LIKE ? OR t.subject LIKE ?)" : '') . "
                    ORDER BY t.id DESC LIMIT $lim OFFSET $off",
                    array_merge([$userType], $q !== '' ? pipe_likes($q, 3) : []));
        },
        'key'   => function ($r) { return (int)$r['id']; },
        'title' => function ($r) { return (string)$r['subject']; },
        'row'   => function ($r) {
            return [
                '<p class="font-bold text-slate-800">' . esc($r['name']) . '</p><p class="text-[12px] text-slate-500">' . esc($r['mobile']) . '</p>',
                '<p class="font-semibold text-slate-700">' . esc((string)$r['subject']) . '</p>
                 <p class="text-[11.5px] text-slate-400">' . date('d M Y', strtotime((string)$r['created_at'])) . '</p>',
                '<p class="text-[12.5px] text-slate-600 max-w-[320px]">' . pipe_cut($r['message'], 180) . '</p>'
                    . (!empty($r['admin_reply']) ? '<p class="text-[11.5px] text-emerald-700 mt-1"><i class="fas fa-reply"></i> ' . pipe_cut($r['admin_reply'], 110) . '</p>' : ''),
                '<span class="pill bg-slate-100 text-slate-600">' . esc((string)$r['status']) . '</span>',
            ];
        },
        'decide' => function ($db, $id, $dec, $remark) {
            $t = pipe_qa($db, "SELECT id, user_id FROM support_tickets WHERE id = ?", [(int)$id]);
            if (empty($t)) return ['ok' => false, 'error' => 'That ticket is gone.'];
            $st = $dec === 'accept' ? 'Resolved' : ($dec === 'reject' ? 'Closed' : 'Pending');
            try { $db->insert("UPDATE support_tickets SET status = ?, admin_reply = ? WHERE id = ?",
                              [$st, ($remark !== '' ? $remark : null), (int)$id]); }
            catch (\Throwable $e) { return ['ok' => false, 'error' => 'It could not be saved.']; }
            pipe_note_decision($db, 'support', $id, (int)$t[0]['user_id'], $dec, $remark);
            return ['ok' => true];
        },
    ];
}

/* ---------------------------------------------------------------------------
   TABS
   --------------------------------------------------------------------------- */
function pipe_role_tabs($role) {
    $lead = []; $out = [];
    foreach (pipe_desks() as $k => $v) {
        if ($v['role'] !== $role || empty($v['first'])) continue;
        $v['type'] = 'desk'; $lead[$k] = $v;
    }
    foreach (pipe_ladder($role) as $k => $v) { $v['type'] = 'rung'; $out[$k] = $v; }
    foreach (pipe_desks() as $k => $v) {
        if ($v['role'] !== $role || !empty($v['first'])) continue;
        $v['type'] = 'desk'; $out[$k] = $v;
    }
    return $lead + $out;
}

function pipe_all_tabs() {
    $out = [];
    foreach (pipe_roles() as $rk => $rv) {
        foreach (pipe_role_tabs($rk) as $k => $v) { $v['role'] = $rk; $out[$k] = $v; }
    }
    return $out;
}
function pipe_all_keys() { return array_keys(pipe_all_tabs()); }

/* ---------------------------------------------------------------------------
   WHO MAY SEE WHAT
   --------------------------------------------------------------------------- */
function pipe_is_boss($db = null) { return tasks_is_boss($db); }

function pipe_access($db, $userId = null) {
    static $cache = [];
    $me  = tasks_actor($db);
    $uid = $userId !== null ? (int)$userId : (int)$me['id'];
    $key = $uid . ($userId === null ? '|self' : '|other');
    if (isset($cache[$key])) return $cache[$key];

    if ($userId === null && pipe_is_boss($db)) return $cache[$key] = pipe_all_keys();

    $raw = '';
    $r = pipe_qa($db, "SELECT task_access, role_id FROM users WHERE id = ?", [$uid]);
    if (!empty($r)) {
        $raw = (string)(isset($r[0]['task_access']) ? $r[0]['task_access'] : '');
        if (empty($r[0]['role_id'])) return $cache[$key] = pipe_all_keys();
    }
    $list = $raw !== '' ? json_decode($raw, true) : [];
    if (!is_array($list)) $list = [];
    /* The single "KYC" desk became three. Anybody who had been given the old
       one keeps all three, rather than quietly losing the desk the day this
       goes live. */
    foreach (['kyc' => ['kyc_pend', 'kyc_req', 'kyc_done'],
              's_kyc' => ['s_kyc_pend', 's_kyc_req', 's_kyc_done']] as $old => $new) {
        if (in_array($old, $list, true)) { $list = array_merge($list, $new); }
    }

    return $cache[$key] = array_values(array_intersect(array_unique($list), pipe_all_keys()));
}

function pipe_can_stage($db, $tab) {
    if (pipe_is_boss($db)) return true;
    /* Two ways in, and either is enough: the desks picked out for this person
       when work was handed to them, or a desk their ROLE carries. A role means
       everybody in it gets the desk without being set up one at a time; the
       per-person list stays for the exceptions. Neither can take away what the
       other gave. */
    if (in_array($tab, pipe_access($db), true)) { return true; }
    if (function_exists('acl_can') && acl_can('tasks.tab_' . $tab)) { return true; }
    return false;
}

function pipe_set_access($db, $userId, $stages) {
    pipe_ensure_schema($db);
    $clean = array_values(array_intersect((array)$stages, pipe_all_keys()));
    try {
        $db->insert("UPDATE users SET task_access = ? WHERE id = ?",
                    [json_encode($clean, JSON_UNESCAPED_UNICODE), (int)$userId]);
        return true;
    } catch (\Throwable $e) { return false; }
}

/* ---------------------------------------------------------------------------
   THE ENGINE
   ---------------------------------------------------------------------------
   One row per person. Three passes: throw away the duplicates an older design
   left behind, give anybody without a row one, and move everybody to the rung
   they have actually reached.
   --------------------------------------------------------------------------- */
function pipe_sync($db, $force = false) {
    pipe_ensure_schema($db);
    $out = ['created' => 0, 'moved' => 0, 'merged' => 0, 'ran' => false];

    if (!$force) {
        $last = 0;
        $r = pipe_qa($db, "SELECT setting_value FROM settings WHERE setting_key = 'pipe_last_sync'");
        if (!empty($r)) $last = (int)$r[0]['setting_value'];
        if ($last > 0 && (time() - $last) < 60) return $out;
    }
    $out['ran'] = true;

    /* ---- 1 · one row per person ------------------------------------------
       An earlier design opened a row per step, so somebody can have four. The
       one carrying real work wins; the empty ones go. */
    foreach (pipe_qa($db,
        "SELECT target_user_id, scope_role, COUNT(*) n
           FROM tasks WHERE target_user_id IS NOT NULL AND target_user_id > 0
          GROUP BY target_user_id, scope_role HAVING n > 1 LIMIT 500") as $g) {
        $rows = pipe_qa($db,
            "SELECT id, outcome, remark, assigned_to, next_follow_up FROM tasks
              WHERE target_user_id = ? AND scope_role = ?
              ORDER BY (outcome IS NOT NULL) DESC, (assigned_to IS NOT NULL) DESC,
                       (remark IS NOT NULL) DESC, id ASC",
            [(int)$g['target_user_id'], $g['scope_role']]);
        $keep = array_shift($rows);
        foreach ($rows as $dead) {
            try {
                $db->insert("DELETE FROM task_activities WHERE task_id = ?", [(int)$dead['id']]);
                $db->insert("DELETE FROM tasks WHERE id = ?", [(int)$dead['id']]);
                $out['merged']++;
            } catch (\Throwable $e) {}
        }
    }

    foreach (['user', 'seller'] as $role) {
        $rungs = pipe_ladder($role);
        $first = array_key_first($rungs);
        $who   = $rungs[$first]['who'];

        /* ---- 2 · anybody without a row gets one ---- */
        $case = pipe_rung_case($role, false);
        foreach (pipe_qa($db,
            "SELECT u.id, u.name, u.mobile, ($case) AS rung FROM users u
              WHERE u.deleted_at IS NULL AND COALESCE(u.status,1) = 1 AND ($who)
                AND NOT EXISTS (SELECT 1 FROM tasks t WHERE t.target_user_id = u.id AND t.scope_role = ?)
              ORDER BY u.id DESC LIMIT 400", [$role]) as $u) {
            $rk = isset($rungs[$u['rung']]) ? $u['rung'] : $first;
            $id = tasks_create($db, [
                'scope_role'     => $role,
                'module_key'     => $rk,
                'category_key'   => $rk . '_rung',
                'category_label' => $rungs[$rk]['label'],
                'title'          => $u['name'],
                'description'    => $rungs[$rk]['goal'],
                'target_user_id' => (int)$u['id'],
                'target_name'    => $u['name'],
                'target_mobile'  => $u['mobile'],
                'priority'       => 'medium',
            ]);
            if ($id) {
                /* it belongs to nobody until somebody is given it */
                try { $db->insert("UPDATE tasks SET created_by = NULL WHERE id = ?", [(int)$id]); } catch (\Throwable $e) {}
                $out['created']++;
            }
        }

        /* ---- 3 · move everybody to the rung they have reached ---- */
        $case = pipe_rung_case($role, true);
        foreach (pipe_qa($db,
            "SELECT t.id, t.task_code, t.module_key, t.assigned_to, ($case) AS rung
               FROM tasks t JOIN users u ON u.id = t.target_user_id
              WHERE t.scope_role = ? AND t.status <> 'cancelled'
              HAVING rung <> t.module_key LIMIT 800", [$role]) as $t) {
            if (!isset($rungs[$t['rung']])) continue;
            $from = isset($rungs[$t['module_key']]) ? $rungs[$t['module_key']]['label'] : (string)$t['module_key'];
            try {
                $db->insert("UPDATE tasks
                                SET module_key = ?, category_key = ?, category_label = ?,
                                    description = ?, advanced = advanced + 1, updated_at = NOW()
                              WHERE id = ?",
                    [$t['rung'], $t['rung'] . '_rung', $rungs[$t['rung']]['label'],
                     $rungs[$t['rung']]['goal'], (int)$t['id']]);
                tasks_log($db, (int)$t['id'], 'status', 'Moved to', $from, $rungs[$t['rung']]['label'],
                          'Moved on its own', $t['task_code']);
                $out['moved']++;
            } catch (\Throwable $e) {}
        }
    }

    try {
        $has = pipe_qa($db, "SELECT id FROM settings WHERE setting_key = 'pipe_last_sync'");
        if (!empty($has)) { $db->insert("UPDATE settings SET setting_value = ? WHERE setting_key = 'pipe_last_sync'", [(string)time()]); }
        else { $db->insert("INSERT INTO settings (setting_key, setting_value) VALUES ('pipe_last_sync', ?)", [(string)time()]); }
    } catch (\Throwable $e) {}

    return $out;
}

/* ---------------------------------------------------------------------------
   RECORDING WHAT HAPPENED ON A CALL
   --------------------------------------------------------------------------- */
function pipe_apply_outcome($db, $taskId, $outcome, $remark = '', $followDate = '') {
    pipe_ensure_schema($db);
    $taskId = (int)$taskId;

    $t = pipe_qa($db, "SELECT * FROM tasks WHERE id = ?", [$taskId]);
    if (empty($t)) return ['ok' => false, 'error' => 'That row is gone.'];
    $t = $t[0];
    if (!tasks_can_touch($db, $t)) return ['ok' => false, 'error' => 'That one is not yours to work on.'];

    $meta = pipe_outcomes();
    if (!isset($meta[$outcome])) return ['ok' => false, 'error' => 'Unknown outcome.'];
    $status = $meta[$outcome][3];
    $remark = trim((string)$remark);

    $follow = null;
    if ($outcome === 'follow_up') {
        $follow = trim((string)$followDate);
        if ($follow === '' || !preg_match('/^\d{4}-\d{2}-\d{2}$/', $follow)) {
            return ['ok' => false, 'error' => 'Pick the date you will call back on.'];
        }
        if ($follow < date('Y-m-d')) return ['ok' => false, 'error' => 'That date has already gone.'];
    }
    if ($outcome === 'not_interested' && $remark === '') {
        return ['ok' => false, 'error' => 'Say why they are not interested before dropping them.'];
    }

    try {
        $db->insert("UPDATE tasks SET outcome = ?, outcome_at = NOW(), status = ?, next_follow_up = ?, remark = ?,
                            started_at = COALESCE(started_at, NOW()), updated_at = NOW() WHERE id = ?",
            [$outcome, $status, $follow, ($remark !== '' ? $remark : $t['remark']), $taskId]);
    } catch (\Throwable $e) { return ['ok' => false, 'error' => 'It could not be saved.']; }

    $was = pipe_outcome_meta((string)$t['outcome']);
    tasks_log($db, $taskId, ($outcome === 'not_interested' ? 'reject' : 'status'),
              'Outcome', $was[0], $meta[$outcome][0],
              $remark . ($follow ? ' · calling back on ' . date('d M Y', strtotime($follow)) : ''), $t['task_code']);

    /* Two outcomes move somebody by themselves — free user, and asking for
       their KYC. Both take effect at once rather than a minute later, because
       the person who recorded it wants to see the row leave the list. */
    $moved = '';
    $__movers = ['free_user', 'kyc_asked'];
    if (in_array($outcome, $__movers, true) || in_array((string)$t['outcome'], $__movers, true)) {
        $rungs = pipe_ladder($t['scope_role']);
        $case  = pipe_rung_case($t['scope_role'], true);
        $r = pipe_qa($db, "SELECT ($case) AS rung FROM tasks t JOIN users u ON u.id = t.target_user_id WHERE t.id = ?", [$taskId]);
        $rk = !empty($r) ? (string)$r[0]['rung'] : '';
        if ($rk !== '' && isset($rungs[$rk]) && $rk !== $t['module_key']) {
            try {
                $db->insert("UPDATE tasks SET module_key = ?, category_label = ?, description = ?, advanced = advanced + 1 WHERE id = ?",
                            [$rk, $rungs[$rk]['label'], $rungs[$rk]['goal'], $taskId]);
                $moved = $rungs[$rk]['label'];
            } catch (\Throwable $e) {}
        }
    }

    return ['ok' => true, 'outcome' => $outcome, 'moved' => $moved];
}

function pipe_assign($db, $taskId, $staffId, $note = '') {
    pipe_ensure_schema($db);
    $taskId = (int)$taskId; $staffId = (int)$staffId;

    $t = pipe_qa($db, "SELECT * FROM tasks WHERE id = ?", [$taskId]);
    if (empty($t)) return ['ok' => false, 'error' => 'That row is gone.'];
    $t = $t[0];
    if (!tasks_can_touch($db, $t)) return ['ok' => false, 'error' => 'That one is not yours to hand out.'];

    $s = pipe_qa($db, "SELECT id, name, role_id FROM users WHERE id = ? AND user_type = 'admin'", [$staffId]);
    if (empty($s)) return ['ok' => false, 'error' => 'Pick somebody from the team.'];
    $s = $s[0];

    $me = tasks_actor($db);
    try {
        $db->insert("UPDATE tasks SET assigned_to = ?, assigned_name = ?, assigned_role = ?, assigned_at = NOW(),
                            assigned_by = ?, assigned_by_name = ?, reassign_count = reassign_count + ?, updated_at = NOW()
                      WHERE id = ?",
            [$s['id'], $s['name'], (empty($s['role_id']) ? 'superadmin' : 'employee'),
             $me['id'] ?: null, $me['name'], (!empty($t['assigned_to']) ? 1 : 0), $taskId]);
    } catch (\Throwable $e) { return ['ok' => false, 'error' => 'It could not be saved.']; }

    tasks_log($db, $taskId, (!empty($t['assigned_to']) ? 'reassign' : 'assign'),
              'Assigned to', (string)$t['assigned_name'], (string)$s['name'], $note, $t['task_code']);
    return ['ok' => true, 'name' => $s['name']];
}

/* ---------------------------------------------------------------------------
   THE KYC VERDICT
   --------------------------------------------------------------------------- */
function pipe_kyc_decide($db, $userId, $decision, $remark = '') {
    pipe_ensure_schema($db);
    $userId = (int)$userId;
    $dec = pipe_decisions();
    if (!isset($dec[$decision])) return ['ok' => false, 'error' => 'Unknown decision.'];

    $remark = trim((string)$remark);
    if (($decision === 'reject' || $decision === 'hold') && $remark === '') {
        return ['ok' => false, 'error' => 'Write a remark, so the member knows what to fix.'];
    }
    if (empty(pipe_qa($db, "SELECT id FROM kyc_details WHERE user_id = ?", [$userId]))) {
        return ['ok' => false, 'error' => 'No KYC on file for that member.'];
    }

    try {
        if ($decision === 'accept') {
            $db->insert("UPDATE kyc_details SET status='approved', admin_remark=?, updated_at=NOW() WHERE user_id = ?", [$remark, $userId]);
        } elseif ($decision === 'reject') {
            $db->insert("UPDATE kyc_details SET status='rejected', admin_remark=?, updated_at=NOW() WHERE user_id = ?", [$remark, $userId]);
        } else {
            $db->insert("UPDATE kyc_details SET admin_remark=?, updated_at=NOW() WHERE user_id = ?", [$remark, $userId]);
        }
    } catch (\Throwable $e) { return ['ok' => false, 'error' => 'It could not be saved.']; }

    pipe_note_decision($db, 'kyc', $userId, $userId, $decision, $remark);
    return ['ok' => true, 'decision' => $decision];
}

/* ---------------------------------------------------------------------------
   COUNTS
   --------------------------------------------------------------------------- */
function pipe_rung_stats($db, $role, $rung, $scopeSql = '1', $scopeParams = []) {
    $out = ['here' => 0, 'due' => 0, 'moved' => 0, 'dropped' => 0, 'unassigned' => 0];
    $r = pipe_qa($db,
        "SELECT
            SUM(t.status <> 'rejected')                                                      AS here_n,
            SUM(t.status <> 'rejected' AND t.next_follow_up IS NOT NULL
                AND t.next_follow_up <= CURDATE())                                           AS due_n,
            COALESCE(SUM(t.advanced),0)                                                      AS moved_n,
            SUM(t.outcome = 'not_interested')                                                AS drop_n,
            SUM(t.status <> 'rejected' AND (t.assigned_to IS NULL OR t.assigned_to = 0))     AS un_n
         FROM tasks t WHERE t.scope_role = ? AND t.module_key = ? AND ($scopeSql)",
        array_merge([$role, $rung], $scopeParams));
    if (!empty($r)) {
        $out['here'] = (int)$r[0]['here_n']; $out['due'] = (int)$r[0]['due_n'];
        $out['moved'] = (int)$r[0]['moved_n']; $out['dropped'] = (int)$r[0]['drop_n'];
        $out['unassigned'] = (int)$r[0]['un_n'];
    }
    return $out;
}

/* What each member of the team is carrying. */
function pipe_team_rows($db, $kind) {
    $rows = [];
    foreach (tasks_staff_list($db) as $s) {
        $isSuper = empty($s['role_id']);
        if ($kind === 'superadmin' && !$isSuper) continue;
        if ($kind === 'employee'   && $isSuper)  continue;

        $r = pipe_qa($db,
            "SELECT SUM(status <> 'rejected') held_n,
                    SUM(status <> 'rejected' AND next_follow_up IS NOT NULL AND next_follow_up <= CURDATE()) due_n,
                    COALESCE(SUM(advanced),0) moved_n,
                    SUM(outcome = 'not_interested') drop_n
               FROM tasks WHERE assigned_to = ?", [(int)$s['id']]);
        $s['held']    = (int)(isset($r[0]['held_n'])  ? $r[0]['held_n']  : 0);
        $s['due']     = (int)(isset($r[0]['due_n'])   ? $r[0]['due_n']   : 0);
        $s['moved']   = (int)(isset($r[0]['moved_n']) ? $r[0]['moved_n'] : 0);
        $s['dropped'] = (int)(isset($r[0]['drop_n'])  ? $r[0]['drop_n']  : 0);
        $s['access']  = pipe_access($db, (int)$s['id']);
        $rows[] = $s;
    }
    return $rows;
}

} // end guard
