<?php
/* ============================================================================
   WBMCZ — ACTIVITY & TASK ENGINE
   ----------------------------------------------------------------------------
   One place holds everything the task board needs:

     tasks_roles()                the people-tabs across the top of the board
     tasks_modules()              the module chips under them
     tasks_catalog()              every task type the panel can produce,
                                  grouped by module
     tasks_seed_categories($db)   writes the catalogue into task_categories
     tasks_all_categories($db)    catalogue + whatever the admin added later
     tasks_ensure_schema($db)     creates the tables if the migration was never
                                  run, so a page can never white-screen
     tasks_code($db, $module)     the next task number, e.g. KYC260831007
     tasks_log(...)               one line in the task's own history
     tasks_actor()                who is doing this, and in what role
     tasks_can_manage() / tasks_can_assign() / tasks_scope_sql()

   Every task carries its whole history, so a task handed from one employee to
   another still shows who held it before, why it moved and what was done.
   ============================================================================ */

if (!function_exists('tasks_modules')) {

/* ---------------------------------------------------------------------------
   The five people-tabs across the top of the board
   --------------------------------------------------------------------------- */
function tasks_roles() {
    return [
        'user'       => ['User',        'fa-user',          'from-indigo-500 to-blue-600'],
        'seller'     => ['Seller',      'fa-store',         'from-amber-500 to-orange-600'],
        'employee'   => ['Employee',    'fa-user-tie',      'from-teal-500 to-cyan-600'],
        'admin'      => ['Admin',       'fa-shield-halved', 'from-rose-500 to-pink-600'],
        'superadmin' => ['Super Admin', 'fa-crown',         'from-violet-500 to-purple-600'],
    ];
}

/* ---------------------------------------------------------------------------
   The module chips — one per working area of the panel
   key => [label, icon, colour, short code used inside the task number]
   --------------------------------------------------------------------------- */
function tasks_modules() {
    return [
        'lead'         => ['Lead',         'fa-bullseye',            'rose',    'LED'],
        'user'         => ['User',         'fa-user',                'indigo',  'USR'],
        'kyc'          => ['KYC',          'fa-shield-halved',       'violet',  'KYC'],
        'subscription' => ['Subscription', 'fa-crown',               'amber',   'SUB'],
        'refer'        => ['Refer',        'fa-share-nodes',         'fuchsia', 'REF'],
        'followup'     => ['Follow-up',    'fa-phone-volume',        'sky',     'FUP'],
        'order'        => ['Order',        'fa-cart-shopping',       'emerald', 'ORD'],
        'support'      => ['Support',      'fa-headset',             'cyan',    'SUP'],
        'notification' => ['Notification', 'fa-bell',                'orange',  'NTF'],
        'offer'        => ['Offer',        'fa-gift',                'pink',    'OFR'],
        'product'      => ['Product',      'fa-box-open',            'blue',    'PRD'],
        'service'      => ['Service',      'fa-screwdriver-wrench',  'teal',    'SRV'],
        'seller'       => ['Seller',       'fa-store',               'yellow',  'SEL'],
        'wallet'       => ['Wallet',       'fa-wallet',              'green',   'WAL'],
        'payout'       => ['Payout',       'fa-money-bill-transfer', 'lime',    'PAY'],
        'website'      => ['Website',      'fa-globe',               'slate',   'WEB'],
        'staff'        => ['Staff',        'fa-users-gear',          'purple',  'STF'],
        'report'       => ['Report',       'fa-chart-pie',           'stone',   'RPT'],
        'general'      => ['General',      'fa-list-check',          'gray',    'GEN'],
    ];
}

/* ---------------------------------------------------------------------------
   Every task type the system can produce, module by module. This is the list
   the Create Task box offers, and the one seeded into task_categories.
   --------------------------------------------------------------------------- */
function tasks_catalog() {
    return [

    'lead' => [
        'lead_new'          => 'New Lead Entry',
        'lead_verify'       => 'Verify Lead Details',
        'lead_call'         => 'Lead Call / First Contact',
        'lead_followup'     => 'Lead Follow-up',
        'lead_qualify'      => 'Lead Qualification',
        'lead_demo'         => 'Product Demo for Lead',
        'lead_quote'        => 'Send Quotation',
        'lead_convert'      => 'Convert Lead to Customer',
        'lead_reassign'     => 'Re-assign Lead',
        'lead_close'        => 'Close / Lost Lead',
        'lead_import'       => 'Bulk Lead Import',
        'lead_source_audit' => 'Lead Source Analysis',
    ],

    'user' => [
        'user_add'         => 'Add New User',
        'user_onboard'     => 'New User Onboarding Call',
        'user_verify'      => 'Verify User Details',
        'user_profile_fix' => 'Correct User Profile',
        'user_document'    => 'Collect User Document',
        'user_upgrade'     => 'Process Upgrade Request',
        'user_downgrade'   => 'Process Downgrade Request',
        'user_block'       => 'Block / Unblock User',
        'user_delete'      => 'Delete User Request',
        'user_restore'     => 'Restore Deleted User',
        'user_password'    => 'Password Reset Help',
        'user_idcard'      => 'Issue / Renew ID Card',
        'user_import'      => 'Bulk User Import',
        'user_data_audit'  => 'User Data Audit',
    ],

    'kyc' => [
        'kyc_verify'        => 'KYC Verification',
        'kyc_recheck'       => 'KYC Re-check',
        'kyc_aadhaar'       => 'Aadhaar Verification',
        'kyc_pan'           => 'PAN Verification',
        'kyc_address'       => 'Address Proof Verification',
        'kyc_bank'          => 'Bank Details Verification',
        'kyc_photo'         => 'Photo / Signature Check',
        'kyc_document_call' => 'Call for Missing Document',
        'kyc_reject'        => 'KYC Rejection Review',
        'kyc_field_setup'   => 'Set Up KYC Questions',
        'kyc_bulk_review'   => 'Bulk KYC Review',
    ],

    'subscription' => [
        'sub_plan_create'    => 'Create Subscription Plan',
        'sub_price_up'       => 'Increase Plan Price',
        'sub_price_down'     => 'Decrease Plan Price',
        'sub_product_add'    => 'Add Product to Plan',
        'sub_product_remove' => 'Remove Product from Plan',
        'sub_activate'       => 'Activate Subscription',
        'sub_renew'          => 'Renewal Follow-up',
        'sub_expiry'         => 'Expiry Follow-up',
        'sub_cancel'         => 'Cancellation Request',
        'sub_refund'         => 'Subscription Refund',
        'sub_upsell'         => 'Plan Upgrade Call',
    ],

    'refer' => [
        'ref_verify'      => 'Verify Referral',
        'ref_payout'      => 'Referral Payout',
        'ref_dispute'     => 'Referral Dispute',
        'ref_campaign'    => 'Referral Campaign Setup',
        'ref_level_check' => 'Referral Level Check',
        'ref_report'      => 'Referral Report',
    ],

    'followup' => [
        'fup_call'     => 'Call Back',
        'fup_whatsapp' => 'WhatsApp Follow-up',
        'fup_email'    => 'Email Follow-up',
        'fup_visit'    => 'Field Visit',
        'fup_meeting'  => 'Meeting / Appointment',
        'fup_reminder' => 'Payment Reminder',
        'fup_feedback' => 'Feedback Call',
    ],

    'order' => [
        'ord_new'       => 'Process New Order',
        'ord_document'  => 'Collect Order Document',
        'ord_verify'    => 'Verify Order Details',
        'ord_process'   => 'Work in Progress',
        'ord_deliver'   => 'Deliver Order',
        'ord_delay'     => 'Delayed Order Follow-up',
        'ord_cancel'    => 'Order Cancellation',
        'ord_refund'    => 'Order Refund',
        'ord_complaint' => 'Order Complaint',
        'ord_report'    => 'Order Report',
    ],

    'support' => [
        'sup_new'       => 'New Ticket',
        'sup_reply'     => 'Reply to Ticket',
        'sup_followup'  => 'Ticket Follow-up',
        'sup_escalate'  => 'Escalate Ticket',
        'sup_close'     => 'Close Ticket',
        'sup_callback'  => 'Callback Request',
        'sup_complaint' => 'Complaint Handling',
        'sup_faq'       => 'Update Help Article',
    ],

    'notification' => [
        'ntf_push'     => 'Send Push Notification',
        'ntf_email'    => 'Email Campaign',
        'ntf_sms'      => 'SMS Campaign',
        'ntf_whatsapp' => 'WhatsApp Broadcast',
        'ntf_inapp'    => 'In-app Announcement',
        'ntf_schedule' => 'Schedule Notification',
        'ntf_template' => 'Prepare Message Template',
    ],

    'offer' => [
        'ofr_create'  => 'Create Offer / Coupon',
        'ofr_price'   => 'Offer Price Setup',
        'ofr_product' => 'Attach Offer to Product',
        'ofr_banner'  => 'Offer Banner Design',
        'ofr_expire'  => 'Expire / Withdraw Offer',
        'ofr_review'  => 'Offer Performance Review',
    ],

    'product' => [
        'prd_add'        => 'Add New Product',
        'prd_edit'       => 'Edit Product Details',
        'prd_price'      => 'Update Price & MRP',
        'prd_share'      => 'Update Share Percentage',
        'prd_image'      => 'Product Image / Icon',
        'prd_content'    => 'Product Description & SEO',
        'prd_kyc'        => 'Set Required KYC Fields',
        'prd_area'       => 'Set Serviceable Area',
        'prd_link'       => 'Update Web / App / Tutorial Link',
        'prd_gov_add'    => 'Add Government Portal',
        'prd_gov_update' => 'Update Government Portal',
        'prd_approve'    => 'Approve Pending Product',
        'prd_reject'     => 'Reject Product',
        'prd_import'     => 'Bulk Product Import (CSV)',
        'prd_delete'     => 'Remove Product',
        'prd_audit'      => 'Product Data Audit',
    ],

    'service' => [
        'srv_category'    => 'Main Category Setup',
        'srv_subcategory' => 'Sub Category Setup',
        'srv_reorder'     => 'Re-order Categories',
        'srv_icon'        => 'Category Icon / Colour',
        'srv_pending'     => 'Pending Service Approval',
        'srv_request'     => 'New Service Request',
        'srv_mapping'     => 'Map Service to Seller',
        'srv_audit'       => 'Service List Audit',
    ],

    'seller' => [
        'sel_onboard'    => 'Seller Onboarding',
        'sel_kyc'        => 'Seller KYC Verification',
        'sel_agreement'  => 'Seller Agreement',
        'sel_product'    => 'Review Seller Product',
        'sel_payout'     => 'Seller Payout Check',
        'sel_commission' => 'Seller Commission Setup',
        'sel_training'   => 'Seller Training',
        'sel_complaint'  => 'Seller Complaint',
        'sel_block'      => 'Block / Unblock Seller',
        'sel_upgrade'    => 'Seller Upgrade Request',
    ],

    'wallet' => [
        'wal_topup'     => 'Verify Wallet Top-up',
        'wal_manual'    => 'Manual Wallet Credit / Debit',
        'wal_bank'      => 'Verify Bank Account',
        'wal_withdraw'  => 'Withdrawal Request',
        'wal_failed'    => 'Failed Transaction Check',
        'wal_reconcile' => 'Transaction Reconciliation',
        'wal_dispute'   => 'Wallet Dispute',
    ],

    'payout' => [
        'pay_process'    => 'Process Payout',
        'pay_verify'     => 'Verify Payout Request',
        'pay_commission' => 'Commission Calculation',
        'pay_staff'      => 'Staff Share Payout',
        'pay_hold'       => 'Hold / Release Payout',
        'pay_report'     => 'Earning Report',
    ],

    'website' => [
        'web_carousel'      => 'Home Page Carousel / Slider',
        'web_dash_banner'   => 'After-login Banner',
        'web_banner_design' => 'Banner Design & Upload',
        'web_general'       => 'General Settings',
        'web_logo'          => 'Logo & Favicon',
        'web_contact'       => 'Contact & Address Update',
        'web_seo'           => 'SEO / Meta Update',
        'web_page'          => 'Page Content Update',
        'web_video'         => 'Video Section Update',
        'web_team'          => 'Team Members Update',
        'web_why'           => 'Why Choose Us Update',
        'web_stats'         => 'Statistics Counter Update',
        'web_language'      => 'Language / Translation Update',
        'web_request'       => 'Website Build Request',
        'web_delivery'      => 'Website Delivery',
        'web_bug'           => 'Website Bug Fix',
    ],

    'staff' => [
        'stf_add'        => 'Add Staff Account',
        'stf_role'       => 'Assign Role & Permission',
        'stf_target'     => 'Set Staff Target',
        'stf_training'   => 'Staff Training',
        'stf_review'     => 'Performance Review',
        'stf_attendance' => 'Attendance / Duty Roster',
        'stf_exit'       => 'Staff Exit Process',
    ],

    'report' => [
        'rpt_daily'   => 'Daily Report',
        'rpt_weekly'  => 'Weekly Report',
        'rpt_monthly' => 'Monthly Analysis',
        'rpt_sales'   => 'Sales Analysis',
        'rpt_audit'   => 'Data Audit',
        'rpt_export'  => 'Export Data',
    ],

    'general' => [
        'gen_task'     => 'General Task',
        'gen_meeting'  => 'Internal Meeting',
        'gen_document' => 'Document Work',
        'gen_research' => 'Research / Analysis',
        'gen_purchase' => 'Office Purchase',
        'gen_other'    => 'Other',
    ],

    ];
}

/* ---------------------------------------------------------------------------
   Status and priority — label, icon, and the pill colours used on the board
   --------------------------------------------------------------------------- */
function tasks_statuses() {
    return [
        'pending'     => ['Pending',     'fa-clock',              'bg-amber-50 text-amber-600 ring-amber-200'],
        'in_progress' => ['In Progress', 'fa-spinner',            'bg-sky-50 text-sky-600 ring-sky-200'],
        'followup'    => ['Follow-up',   'fa-phone-volume',       'bg-violet-50 text-violet-600 ring-violet-200'],
        'on_hold'     => ['On Hold',     'fa-pause',              'bg-slate-100 text-slate-600 ring-slate-200'],
        'completed'   => ['Completed',   'fa-circle-check',       'bg-emerald-50 text-emerald-600 ring-emerald-200'],
        'rejected'    => ['Rejected',    'fa-circle-xmark',       'bg-rose-50 text-rose-600 ring-rose-200'],
        'cancelled'   => ['Cancelled',   'fa-ban',                'bg-gray-100 text-gray-500 ring-gray-200'],
    ];
}
function tasks_priorities() {
    return [
        'low'    => ['Low',    'bg-emerald-50 text-emerald-600 ring-emerald-200', 'bg-emerald-500'],
        'medium' => ['Medium', 'bg-amber-50 text-amber-600 ring-amber-200',       'bg-amber-500'],
        'high'   => ['High',   'bg-rose-50 text-rose-600 ring-rose-200',          'bg-rose-500'],
        'urgent' => ['Urgent', 'bg-red-100 text-red-700 ring-red-300',            'bg-red-600'],
    ];
}
function tasks_status_meta($k) { $m = tasks_statuses();  return $m[$k] ?? ['Unknown', 'fa-circle', 'bg-slate-100 text-slate-500 ring-slate-200']; }
function tasks_priority_meta($k){ $m = tasks_priorities(); return $m[$k] ?? ['Normal', 'bg-slate-50 text-slate-500 ring-slate-200', 'bg-slate-400']; }

/* The log actions, and how each one reads on the timeline */
function tasks_actions() {
    return [
        'create'   => ['Task created',    'fa-plus',                 'bg-emerald-100 text-emerald-700'],
        'assign'   => ['Assigned',        'fa-user-check',           'bg-indigo-100 text-indigo-700'],
        'reassign' => ['Handed over',     'fa-people-arrows',        'bg-orange-100 text-orange-700'],
        'status'   => ['Status changed',  'fa-arrows-rotate',        'bg-sky-100 text-sky-700'],
        'priority' => ['Priority changed','fa-flag',                 'bg-amber-100 text-amber-700'],
        'due'      => ['Due date changed','fa-calendar-day',         'bg-violet-100 text-violet-700'],
        'progress' => ['Progress updated','fa-bars-progress',        'bg-teal-100 text-teal-700'],
        'note'     => ['Note added',      'fa-note-sticky',          'bg-slate-100 text-slate-600'],
        'complete' => ['Completed',       'fa-circle-check',         'bg-emerald-100 text-emerald-700'],
        'reject'   => ['Rejected',        'fa-circle-xmark',         'bg-rose-100 text-rose-700'],
        'reopen'   => ['Re-opened',       'fa-rotate-left',          'bg-cyan-100 text-cyan-700'],
        'edit'     => ['Task edited',     'fa-pen',                  'bg-sky-100 text-sky-700'],
        'delete'   => ['Task deleted',    'fa-trash',                'bg-rose-100 text-rose-700'],
    ];
}
function tasks_action_meta($k) { $m = tasks_actions(); return $m[$k] ?? [ucfirst($k), 'fa-circle-dot', 'bg-slate-100 text-slate-600']; }

/* ---------------------------------------------------------------------------
   Schema — built on demand so the board works even before acvmigration.php
   --------------------------------------------------------------------------- */
function tasks_ensure_schema($db) {
    static $done = false;
    if ($done) return true;
    $conn = $db->getConnection();
    $sql = [
        "CREATE TABLE IF NOT EXISTS tasks (
            id INT AUTO_INCREMENT PRIMARY KEY, task_code VARCHAR(40) NULL,
            scope_role VARCHAR(20) NOT NULL DEFAULT 'user', module_key VARCHAR(40) NOT NULL DEFAULT 'general',
            category_key VARCHAR(60) NOT NULL DEFAULT 'general_other', category_label VARCHAR(160) NULL,
            title VARCHAR(200) NOT NULL, description TEXT NULL,
            target_user_id INT NULL, target_name VARCHAR(150) NULL, target_mobile VARCHAR(25) NULL,
            target_ref_type VARCHAR(40) NULL, target_ref_id INT NULL,
            assigned_to INT NULL, assigned_name VARCHAR(150) NULL, assigned_role VARCHAR(20) NULL,
            assigned_code VARCHAR(40) NULL, assigned_at DATETIME NULL, assigned_by INT NULL,
            assigned_by_name VARCHAR(150) NULL,
            priority VARCHAR(10) NOT NULL DEFAULT 'medium', status VARCHAR(20) NOT NULL DEFAULT 'pending',
            progress TINYINT NOT NULL DEFAULT 0, due_date DATETIME NULL, started_at DATETIME NULL,
            completed_at DATETIME NULL, next_follow_up DATE NULL, remark TEXT NULL,
            reject_reason VARCHAR(500) NULL, attachment VARCHAR(255) NULL, reassign_count INT NOT NULL DEFAULT 0,
            created_by INT NULL, created_by_name VARCHAR(150) NULL, created_by_role VARCHAR(20) NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT NULL,
            KEY idx_tasks_scope (scope_role, module_key), KEY idx_tasks_assigned (assigned_to, status),
            KEY idx_tasks_status (status), KEY idx_tasks_due (due_date), KEY idx_tasks_created (created_at),
            UNIQUE KEY uniq_tasks_code (task_code)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

        "CREATE TABLE IF NOT EXISTS task_activities (
            id BIGINT AUTO_INCREMENT PRIMARY KEY, task_id INT NOT NULL, task_code VARCHAR(40) NULL,
            action VARCHAR(30) NOT NULL DEFAULT 'note', field_label VARCHAR(120) NULL,
            old_value VARCHAR(255) NULL, new_value VARCHAR(255) NULL, remark TEXT NULL,
            done_by INT NULL, done_by_name VARCHAR(150) NULL, done_by_role VARCHAR(20) NULL,
            ip_address VARCHAR(45) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            KEY idx_ta_task (task_id, created_at), KEY idx_ta_actor (done_by, created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

        "CREATE TABLE IF NOT EXISTS task_categories (
            id INT AUTO_INCREMENT PRIMARY KEY, module_key VARCHAR(40) NOT NULL,
            category_key VARCHAR(60) NOT NULL, label VARCHAR(160) NOT NULL,
            scope_role VARCHAR(20) NOT NULL DEFAULT 'all', icon VARCHAR(40) NULL,
            sort_order INT NOT NULL DEFAULT 0, is_system TINYINT(1) NOT NULL DEFAULT 0,
            status TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_tc (module_key, category_key), KEY idx_tc_module (module_key, status)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
    ];
    foreach ($sql as $q) { try { $conn->query($q); } catch (\Throwable $e) {} }
    $done = true;
    return true;
}

/* ---------------------------------------------------------------------------
   Seed the catalogue into task_categories (only the rows that are missing)
   --------------------------------------------------------------------------- */
function tasks_seed_categories($db) {
    tasks_ensure_schema($db);
    $conn = $db->getConnection();
    $mods = tasks_modules();
    $n = 0; $order = 0;

    foreach (tasks_catalog() as $mk => $items) {
        $icon = $mods[$mk][1] ?? 'fa-list-check';
        foreach ($items as $ck => $label) {
            $order += 10;
            try {
                $st = $conn->prepare(
                    "INSERT INTO task_categories (module_key, category_key, label, scope_role, icon, sort_order, is_system, status)
                     VALUES (?,?,?,'all',?,?,1,1)
                     ON DUPLICATE KEY UPDATE label = VALUES(label), icon = VALUES(icon), is_system = 1");
                $st->bind_param('ssssi', $mk, $ck, $label, $icon, $order);
                $st->execute();
                if ($st->affected_rows > 0) { $n++; }
                $st->close();
            } catch (\Throwable $e) {}
        }
    }
    return $n;
}

/**
 * The catalogue as the board should show it: the built-in list, plus any task
 * type the admin added afterwards, minus anything switched off.
 * Returns [module_key => [category_key => label]]
 */
function tasks_all_categories($db) {
    static $cache = null;
    if ($cache !== null) return $cache;

    $out = tasks_catalog();
    try {
        $rows = $db->select("SELECT module_key, category_key, label, status FROM task_categories ORDER BY sort_order ASC, id ASC");
        foreach ($rows as $r) {
            $mk = $r['module_key']; $ck = $r['category_key'];
            if ((int)$r['status'] !== 1) { unset($out[$mk][$ck]); continue; }
            if (!isset($out[$mk])) { $out[$mk] = []; }
            $out[$mk][$ck] = $r['label'];
        }
    } catch (\Throwable $e) {}

    foreach ($out as $mk => $v) { if (empty($v)) unset($out[$mk]); }
    return $cache = $out;
}

/** Friendly label for one category key, wherever it came from. */
function tasks_category_label($db, $module, $key, $fallback = '') {
    $all = tasks_all_categories($db);
    if (isset($all[$module][$key])) return $all[$module][$key];
    foreach ($all as $items) { if (isset($items[$key])) return $items[$key]; }
    return $fallback !== '' ? $fallback : ucwords(str_replace('_', ' ', (string)$key));
}

/* ---------------------------------------------------------------------------
   Task number — module code + yymmdd + a running serial, e.g. KYC260831007
   --------------------------------------------------------------------------- */
function tasks_code($db, $module) {
    $mods = tasks_modules();
    $pre  = $mods[$module][3] ?? 'GEN';
    $day  = date('ymd');
    $stem = $pre . $day;
    try {
        $r = $db->select("SELECT task_code FROM tasks WHERE task_code LIKE ? ORDER BY id DESC LIMIT 1", [$stem . '%']);
        $n = !empty($r) ? (int)substr($r[0]['task_code'], strlen($stem)) : 0;
    } catch (\Throwable $e) { $n = 0; }
    return $stem . str_pad($n + 1, 3, '0', STR_PAD_LEFT);
}

/* ---------------------------------------------------------------------------
   Who is doing this, and in what role
   --------------------------------------------------------------------------- */
function tasks_actor($db = null) {
    static $me = null;
    if ($me !== null) return $me;

    $id   = isset($_SESSION['user_id']) ? (int)$_SESSION['user_id'] : 0;
    $name = $_SESSION['user_name'] ?? 'Admin';
    $role = 'admin';

    if ($id && is_object($db)) {
        try {
            $r = $db->select("SELECT name, user_type, role_id FROM users WHERE id = ?", [$id]);
            if (!empty($r)) {
                $name = $r[0]['name'];
                if ($r[0]['user_type'] === 'admin') {
                    /* The owner account has no role at all. A named role counts as
                       super admin too when it was given the whole panel ('all'). */
                    $role = 'employee';
                    if (empty($r[0]['role_id'])) {
                        $role = 'superadmin';
                    } elseif (function_exists('rbac_is_super') && rbac_is_super()) {
                        $role = 'superadmin';
                    }
                } else {
                    $role = $r[0]['user_type'] === 'seller' ? 'seller' : 'user';
                }
            }
        } catch (\Throwable $e) {}
    }
    return $me = ['id' => $id, 'name' => $name, 'role' => $role];
}

/** Client IP, proxy aware — same rule the activity log uses. */
function tasks_ip() {
    foreach (['HTTP_CF_CONNECTING_IP', 'HTTP_X_FORWARDED_FOR', 'HTTP_CLIENT_IP', 'REMOTE_ADDR'] as $k) {
        if (!empty($_SERVER[$k])) {
            $ip = trim(explode(',', $_SERVER[$k])[0]);
            if (filter_var($ip, FILTER_VALIDATE_IP)) return $ip;
        }
    }
    return '0.0.0.0';
}

/* ---------------------------------------------------------------------------
   One line in a task's own history. Never throws.
   --------------------------------------------------------------------------- */
function tasks_log($db, $taskId, $action, $field = null, $old = null, $new = null, $remark = '', $code = null) {
    if (!is_object($db) || !$taskId) return false;
    tasks_ensure_schema($db);
    $me = tasks_actor($db);
    try {
        $db->insert(
            "INSERT INTO task_activities
                (task_id, task_code, action, field_label, old_value, new_value, remark,
                 done_by, done_by_name, done_by_role, ip_address)
             VALUES (?,?,?,?,?,?,?,?,?,?,?)",
            [
                (int)$taskId,
                $code !== null ? substr((string)$code, 0, 40) : null,
                substr((string)$action, 0, 30),
                $field !== null ? substr((string)$field, 0, 120) : null,
                $old   !== null ? substr((string)$old, 0, 255)   : null,
                $new   !== null ? substr((string)$new, 0, 255)   : null,
                $remark !== '' ? $remark : null,
                $me['id'] ?: null,
                substr((string)$me['name'], 0, 150),
                substr((string)$me['role'], 0, 20),
                tasks_ip(),
            ]
        );
        return true;
    } catch (\Throwable $e) { return false; }
}

/* ---------------------------------------------------------------------------
   Who may do what
   ----------------------------------------------------------------------------
   Super admin and admin        see every task, create, assign, edit, delete
   Employee (staff with a role) sees the tasks they raised or hold, may update
                                them and hand one over to a colleague
   --------------------------------------------------------------------------- */
function tasks_is_boss($db = null) {
    $me = tasks_actor($db);
    if ($me['role'] === 'superadmin') return true;
    if (function_exists('rbac_is_super') && rbac_is_super()) return true;
    /* a staff member holding the whole Staff & Roles module counts as an admin */
    if (function_exists('rbac_can') && rbac_can('manage_staff_roles')) return true;
    return false;
}
function tasks_can_manage($db = null) {           /* create / delete anybody's task */
    return tasks_is_boss($db);
}
function tasks_can_assign_any($db = null) {       /* assign a task to anyone */
    return tasks_is_boss($db);
}

/**
 * Can this account touch this particular task?
 * A boss can touch everything. An employee can touch a task they hold or raised.
 */
function tasks_can_touch($db, $task) {
    if (tasks_is_boss($db)) return true;
    $me = tasks_actor($db);
    if (!$me['id']) return false;
    return ((int)($task['assigned_to'] ?? 0) === $me['id'])
        || ((int)($task['created_by']  ?? 0) === $me['id']);
}

/**
 * The WHERE fragment that keeps an employee inside their own work.
 * Returns [sql, params] — sql is '1' for a boss.
 */
function tasks_scope_sql($db, $alias = 't') {
    if (tasks_is_boss($db)) return ['1', []];
    $me = tasks_actor($db);
    $id = (int)$me['id'];
    return ["($alias.assigned_to = ? OR $alias.created_by = ?)", [$id, $id]];
}

/* ---------------------------------------------------------------------------
   The staff list a task can be handed to
   --------------------------------------------------------------------------- */
function tasks_staff_list($db) {
    static $cache = null;
    if ($cache !== null) return $cache;
    $rows = [];
    try {
        $rows = $db->select(
            "SELECT u.id, u.name, u.mobile, u.role_id, u.employee_code, 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.status IS NULL OR u.status = 1)
             ORDER BY (u.role_id IS NULL) DESC, u.name ASC");
    } catch (\Throwable $e) {
        try {
            $rows = $db->select("SELECT id, name, mobile, role_id FROM users WHERE user_type = 'admin' ORDER BY name ASC");
        } catch (\Throwable $e2) { $rows = []; }
    }
    foreach ($rows as &$r) {
        $r['role_key']   = empty($r['role_id']) ? 'superadmin' : 'employee';
        $r['role_label'] = empty($r['role_id']) ? 'Super Admin' : ($r['role_name'] ?: 'Employee');
    }
    unset($r);
    return $cache = $rows;
}

/* ---------------------------------------------------------------------------
   Board counters for one slice of the board
   --------------------------------------------------------------------------- */
function tasks_stats($db, $whereSql = '1', $params = []) {
    $out = ['total' => 0, 'pending' => 0, 'in_progress' => 0, 'completed' => 0,
            'rejected' => 0, 'followup' => 0, 'overdue' => 0];
    try {
        $r = $db->select(
            "SELECT
                COUNT(*) AS total,
                SUM(status = 'pending')     AS pending,
                SUM(status = 'in_progress') AS in_progress,
                SUM(status = 'completed')   AS completed,
                SUM(status = 'rejected')    AS rejected,
                SUM(status = 'followup')    AS followup,
                SUM(due_date IS NOT NULL AND due_date < NOW() AND status NOT IN ('completed','rejected','cancelled')) AS overdue
             FROM tasks t WHERE $whereSql", $params);
        if (!empty($r)) { foreach ($out as $k => $_) { $out[$k] = (int)($r[0][$k] ?? 0); } }
    } catch (\Throwable $e) {}
    return $out;
}

/* ---------------------------------------------------------------------------
   Raise a task from anywhere in the panel.
   tasks_create($db, ['module_key'=>'kyc','category_key'=>'kyc_verify', ...])
   Returns the new id, or 0.
   --------------------------------------------------------------------------- */
function tasks_create($db, $d) {
    tasks_ensure_schema($db);
    $me   = tasks_actor($db);
    $mod  = $d['module_key'] ?? 'general';
    $code = tasks_code($db, $mod);
    $cat  = $d['category_key'] ?? 'gen_task';

    try {
        $id = $db->insert(
            "INSERT INTO tasks
                (task_code, scope_role, module_key, category_key, category_label, title, description,
                 target_user_id, target_name, target_mobile, target_ref_type, target_ref_id,
                 assigned_to, assigned_name, assigned_role, assigned_at, assigned_by, assigned_by_name,
                 priority, status, due_date, next_follow_up, remark,
                 created_by, created_by_name, created_by_role)
             VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
            [
                $code,
                $d['scope_role'] ?? 'user',
                $mod,
                $cat,
                $d['category_label'] ?? tasks_category_label($db, $mod, $cat),
                substr((string)($d['title'] ?? 'Task'), 0, 200),
                $d['description'] ?? null,
                !empty($d['target_user_id']) ? (int)$d['target_user_id'] : null,
                $d['target_name']   ?? null,
                $d['target_mobile'] ?? null,
                $d['target_ref_type'] ?? null,
                !empty($d['target_ref_id']) ? (int)$d['target_ref_id'] : null,
                !empty($d['assigned_to']) ? (int)$d['assigned_to'] : null,
                $d['assigned_name'] ?? null,
                $d['assigned_role'] ?? null,
                !empty($d['assigned_to']) ? date('Y-m-d H:i:s') : null,
                $me['id'] ?: null,
                $me['name'],
                $d['priority'] ?? 'medium',
                $d['status']   ?? 'pending',
                !empty($d['due_date']) ? $d['due_date'] : null,
                !empty($d['next_follow_up']) ? $d['next_follow_up'] : null,
                $d['remark'] ?? null,
                $me['id'] ?: null,
                $me['name'],
                $me['role'],
            ]
        );
    } catch (\Throwable $e) { return 0; }

    if ($id) {
        tasks_log($db, $id, 'create', 'Task', null, $code, (string)($d['title'] ?? ''), $code);
        if (!empty($d['assigned_to'])) {
            tasks_log($db, $id, 'assign', 'Assigned to', null, (string)($d['assigned_name'] ?? ''), '', $code);
        }
    }
    return (int)$id;
}

/* A short "3 days left" / "2 days overdue" line for the due column. */
function tasks_due_note($due) {
    if (empty($due)) return ['', 'text-slate-400'];
    $d = (strtotime($due) - time());
    if ($d < 0)      return [floor(abs($d) / 86400) . 'd overdue', 'text-rose-600 font-bold'];
    if ($d < 86400)  return ['due today', 'text-amber-600 font-bold'];
    return [ceil($d / 86400) . 'd left', 'text-slate-400'];
}

function tasks_ago($ts) {
    if (empty($ts)) return '';
    $d = time() - strtotime($ts);
    if ($d < 60)     return 'just now';
    if ($d < 3600)   return floor($d / 60) . 'm ago';
    if ($d < 86400)  return floor($d / 3600) . 'h ago';
    if ($d < 604800) return floor($d / 86400) . 'd ago';
    return date('d M Y', strtotime($ts));
}

} // end function_exists guard
