<?php
/* GuardforceCRM.php - Modernized CRM Dashboard & Management */
error_reporting(E_ALL & ~E_NOTICE);
$PageSecurity = 0;

/* ── API MODE ─────────────────────────────────────────────── */
if (isset($_GET['api'])) {
    ob_start();
    include('includes/session.inc');
    ob_end_clean();
    header('Content-Type: application/json');

    $allowed = array('admin','ravinesh','riga','sharukh');
    $userid = isset($_SESSION['UserID']) ? $_SESSION['UserID'] : '';
    if (!in_array($userid, $allowed)) {
        echo json_encode(array('error' => 'Not authorized'));
        exit;
    }

    $conn = new mysqli('localhost', 'sooggoo', '4Compassion*', 'evergreentest');
    if ($conn->connect_error) {
        echo json_encode(array('error' => 'DB connection failed'));
        exit;
    }
    $conn->set_charset('utf8');

    $action = isset($_GET['api']) ? $_GET['api'] : '';

    /* ── DASHBOARD DATA ──────────────────────────────────── */
    if ($action == 'dashboard') {
        $data = array();

        // Total contracts
        $r = $conn->query("SELECT COUNT(*) as cnt FROM guardforce_crm");
        $data['total_contracts'] = ($r && $row = $r->fetch_assoc()) ? (int)$row['cnt'] : 0;

        // Active contracts (end_date >= today or end_date is null/empty)
        $r = $conn->query("SELECT COUNT(*) as cnt FROM guardforce_crm WHERE end_date >= CURDATE() OR end_date IS NULL OR end_date = '0000-00-00'");
        $data['active_contracts'] = ($r && $row = $r->fetch_assoc()) ? (int)$row['cnt'] : 0;

        // Expired contracts
        $data['expired_contracts'] = $data['total_contracts'] - $data['active_contracts'];

        // Expiring within 90 days
        $r = $conn->query("SELECT COUNT(*) as cnt FROM guardforce_crm WHERE end_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 90 DAY)");
        $data['expiring_soon'] = ($r && $row = $r->fetch_assoc()) ? (int)$row['cnt'] : 0;

        // Total estimated monthly revenue
        $r = $conn->query("SELECT SUM(base_rate * est_monthly_hours) as rev FROM guardforce_crm WHERE end_date >= CURDATE() OR end_date IS NULL OR end_date = '0000-00-00'");
        $data['est_monthly_revenue'] = ($r && $row = $r->fetch_assoc()) ? round((float)$row['rev'], 2) : 0;

        // Average base rate
        $r = $conn->query("SELECT AVG(base_rate) as avg_rate FROM guardforce_crm WHERE base_rate > 0");
        $data['avg_base_rate'] = ($r && $row = $r->fetch_assoc()) ? round((float)$row['avg_rate'], 2) : 0;

        // Average initial price per hour
        $r = $conn->query("SELECT AVG(initial_price_per_hour) as avg_price FROM guardforce_crm WHERE initial_price_per_hour > 0");
        $data['avg_initial_price'] = ($r && $row = $r->fetch_assoc()) ? round((float)$row['avg_price'], 2) : 0;

        // Total estimated monthly hours
        $r = $conn->query("SELECT SUM(est_monthly_hours) as hrs FROM guardforce_crm WHERE end_date >= CURDATE() OR end_date IS NULL OR end_date = '0000-00-00'");
        $data['total_monthly_hours'] = ($r && $row = $r->fetch_assoc()) ? round((float)$row['hrs'], 2) : 0;

        // Department breakdown
        $depts = array();
        $r = $conn->query("SELECT d.departmentname, COUNT(c.id) as cnt, SUM(c.est_monthly_hours) as hrs, SUM(c.base_rate * c.est_monthly_hours) as rev FROM guardforce_crm c LEFT JOIN department d ON c.department = d.departmentid GROUP BY c.department, d.departmentname ORDER BY cnt DESC");
        if ($r) { while ($row = $r->fetch_assoc()) { $depts[] = $row; } }
        $data['by_department'] = $depts;

        // Contract status distribution
        $statuses = array();
        $r = $conn->query("SELECT 
            SUM(CASE WHEN end_date < CURDATE() AND end_date != '0000-00-00' AND end_date IS NOT NULL THEN 1 ELSE 0 END) as expired,
            SUM(CASE WHEN end_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 90 DAY) THEN 1 ELSE 0 END) as expiring_soon,
            SUM(CASE WHEN end_date > DATE_ADD(CURDATE(), INTERVAL 90 DAY) OR end_date IS NULL OR end_date = '0000-00-00' THEN 1 ELSE 0 END) as active
            FROM guardforce_crm");
        if ($r) { $statuses = $r->fetch_assoc(); }
        $data['status_distribution'] = $statuses;

        // Rate year progression (avg across all contracts)
        $rates = array();
        $r = $conn->query("SELECT 
            AVG(NULLIF(initial_price_per_hour,0)) as initial_rate,
            AVG(NULLIF(rate_year1,0)) as yr1,
            AVG(NULLIF(rate_year2,0)) as yr2,
            AVG(NULLIF(rate_year3,0)) as yr3,
            AVG(NULLIF(rate_year4,0)) as yr4,
            AVG(NULLIF(rate_year5,0)) as yr5
            FROM guardforce_crm");
        if ($r) { $rates = $r->fetch_assoc(); }
        $data['rate_progression'] = $rates;

        // Top 10 customers by estimated revenue
        $top = array();
        $r = $conn->query("SELECT c.customer_name, c.base_rate, c.est_monthly_hours, (c.base_rate * c.est_monthly_hours) as est_rev, c.end_date, d.departmentname FROM guardforce_crm c LEFT JOIN department d ON c.department = d.departmentid WHERE c.base_rate > 0 AND c.est_monthly_hours > 0 ORDER BY est_rev DESC LIMIT 10");
        if ($r) { while ($row = $r->fetch_assoc()) { $top[] = $row; } }
        $data['top_customers'] = $top;

        // Contracts expiring soon (list)
        $expiring = array();
        $r = $conn->query("SELECT c.id, c.customer_name, c.account_number, c.end_date, c.base_rate, c.est_monthly_hours, d.departmentname, c.ops_mng_responsible, DATEDIFF(c.end_date, CURDATE()) as days_left FROM guardforce_crm c LEFT JOIN department d ON c.department = d.departmentid WHERE c.end_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 180 DAY) ORDER BY c.end_date ASC LIMIT 15");
        if ($r) { while ($row = $r->fetch_assoc()) { $expiring[] = $row; } }
        $data['expiring_contracts'] = $expiring;

        // Ops manager workload
        $ops = array();
        $r = $conn->query("SELECT ops_mng_responsible, COUNT(*) as cnt, SUM(est_monthly_hours) as hrs FROM guardforce_crm WHERE ops_mng_responsible != '' AND ops_mng_responsible IS NOT NULL GROUP BY ops_mng_responsible ORDER BY cnt DESC");
        if ($r) { while ($row = $r->fetch_assoc()) { $ops[] = $row; } }
        $data['ops_workload'] = $ops;

        // Clause analysis
        $clauses = array();
        $r = $conn->query("SELECT 
            SUM(CASE WHEN `2%_clause` != '' AND `2%_clause` IS NOT NULL AND `2%_clause` != 'No' AND `2%_clause` != 'N/A' THEN 1 ELSE 0 END) as has_2pct,
            SUM(CASE WHEN cola != '' AND cola IS NOT NULL AND cola != 'No' AND cola != 'N/A' THEN 1 ELSE 0 END) as has_cola,
            SUM(CASE WHEN min_wage != '' AND min_wage IS NOT NULL AND min_wage != 'No' AND min_wage != 'N/A' THEN 1 ELSE 0 END) as has_min_wage,
            SUM(CASE WHEN non_solicitation != '' AND non_solicitation IS NOT NULL AND non_solicitation != 'No' AND non_solicitation != 'N/A' THEN 1 ELSE 0 END) as has_non_solicit
            FROM guardforce_crm");
        if ($r) { $clauses = $r->fetch_assoc(); }
        $data['clause_analysis'] = $clauses;

        // Recent management reviews
        $reviews = array();
        $r = $conn->query("SELECT customer_name, last_management_review_date, DATEDIFF(CURDATE(), last_management_review_date) as days_since FROM guardforce_crm WHERE last_management_review_date IS NOT NULL AND last_management_review_date != '0000-00-00' ORDER BY last_management_review_date DESC LIMIT 10");
        if ($r) { while ($row = $r->fetch_assoc()) { $reviews[] = $row; } }
        $data['recent_reviews'] = $reviews;

        // Overdue reviews (>365 days)
        $r = $conn->query("SELECT COUNT(*) as cnt FROM guardforce_crm WHERE last_management_review_date IS NOT NULL AND last_management_review_date != '0000-00-00' AND DATEDIFF(CURDATE(), last_management_review_date) > 365");
        $data['overdue_reviews'] = ($r && $row = $r->fetch_assoc()) ? (int)$row['cnt'] : 0;

        echo json_encode($data);
        exit;
    }

    /* ── LIST / SEARCH ───────────────────────────────────── */
    if ($action == 'list') {
        $page = isset($_GET['page']) ? max(1, (int)$_GET['page']) : 1;
        $limit = isset($_GET['limit']) ? min(100, max(10, (int)$_GET['limit'])) : 20;
        $offset = ($page - 1) * $limit;
        $search = isset($_GET['search']) ? $conn->real_escape_string($_GET['search']) : '';
        $dept = isset($_GET['dept']) ? (int)$_GET['dept'] : 0;
        $status = isset($_GET['status']) ? $conn->real_escape_string($_GET['status']) : '';
        $sort = isset($_GET['sort']) ? $conn->real_escape_string($_GET['sort']) : 'id';
        $dir = (isset($_GET['dir']) && strtolower($_GET['dir']) == 'asc') ? 'ASC' : 'DESC';

        $where = array('1=1');
        if ($search != '') {
            $where[] = "(c.customer_name LIKE '%{$search}%' OR c.account_number LIKE '%{$search}%' OR c.ops_mng_responsible LIKE '%{$search}%' OR c.comments LIKE '%{$search}%')";
        }
        if ($dept > 0) {
            $where[] = "c.department = {$dept}";
        }
        if ($status == 'active') {
            $where[] = "(c.end_date >= CURDATE() OR c.end_date IS NULL OR c.end_date = '0000-00-00')";
        } elseif ($status == 'expired') {
            $where[] = "(c.end_date < CURDATE() AND c.end_date != '0000-00-00' AND c.end_date IS NOT NULL)";
        } elseif ($status == 'expiring') {
            $where[] = "c.end_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 90 DAY)";
        }

        $allowedSort = array('id','customer_name','account_number','start_date','end_date','base_rate','est_monthly_hours','department','ops_mng_responsible');
        if (!in_array($sort, $allowedSort)) $sort = 'id';

        $whereStr = implode(' AND ', $where);

        // Count
        $r = $conn->query("SELECT COUNT(*) as cnt FROM guardforce_crm c WHERE {$whereStr}");
        $total = ($r && $row = $r->fetch_assoc()) ? (int)$row['cnt'] : 0;

        // Fetch
        $rows = array();
        $sql = "SELECT c.*, d.departmentname, dm.name as debtor_name 
                FROM guardforce_crm c 
                LEFT JOIN department d ON c.department = d.departmentid 
                LEFT JOIN debtorsmaster dm ON c.customer_code = dm.debtorno 
                WHERE {$whereStr} 
                ORDER BY c.{$sort} {$dir} 
                LIMIT {$limit} OFFSET {$offset}";
        $r = $conn->query($sql);
        if ($r) { while ($row = $r->fetch_assoc()) { $rows[] = $row; } }

        echo json_encode(array('total' => $total, 'page' => $page, 'limit' => $limit, 'data' => $rows));
        exit;
    }

    /* ── GET SINGLE ──────────────────────────────────────── */
    if ($action == 'get') {
        $id = isset($_GET['id']) ? (int)$_GET['id'] : 0;
        $r = $conn->query("SELECT c.*, d.departmentname, dm.name as debtor_name FROM guardforce_crm c LEFT JOIN department d ON c.department = d.departmentid LEFT JOIN debtorsmaster dm ON c.customer_code = dm.debtorno WHERE c.id = {$id}");
        if ($r && $row = $r->fetch_assoc()) {
            echo json_encode($row);
        } else {
            echo json_encode(array('error' => 'Not found'));
        }
        exit;
    }

    /* ── DEPARTMENTS LOOKUP ──────────────────────────────── */
    if ($action == 'departments') {
        $depts = array();
        $r = $conn->query("SELECT departmentid, departmentname FROM department ORDER BY departmentname");
        if ($r) { while ($row = $r->fetch_assoc()) { $depts[] = $row; } }
        echo json_encode($depts);
        exit;
    }

    /* ── DEBTORS LOOKUP ──────────────────────────────────── */
    if ($action == 'debtors') {
        $search = isset($_GET['q']) ? $conn->real_escape_string($_GET['q']) : '';
        $debtors = array();
        $sql = "SELECT debtorno, name FROM debtorsmaster";
        if ($search != '') {
            $sql .= " WHERE name LIKE '%{$search}%' OR debtorno LIKE '%{$search}%'";
        }
        $sql .= " ORDER BY name LIMIT 50";
        $r = $conn->query($sql);
        if ($r) { while ($row = $r->fetch_assoc()) { $debtors[] = $row; } }
        echo json_encode($debtors);
        exit;
    }

    /* ── SAVE (INSERT / UPDATE) ──────────────────────────── */
    if ($action == 'save') {
        $input = json_decode(file_get_contents('php://input'), true);
        if (!$input) {
            echo json_encode(array('error' => 'Invalid input'));
            exit;
        }

        $fields = array(
            'account_number','last_management_review_date','customer_name','customer_code',
            'department','customer_contract_date','start_date','end_date',
            'base_rate','overtime_rate','ad_hoc_rate','other_rates',
            '2%_clause','cola','min_wage','non_solicitation','other',
            'initial_price_per_hour','rate_year1','rate_year2','rate_year3','rate_year4','rate_year5',
            'est_monthly_hours','ops_mng_responsible','comments'
        );

        $id = isset($input['id']) ? (int)$input['id'] : 0;

        if ($id > 0) {
            // UPDATE
            $sets = array();
            foreach ($fields as $f) {
                if (isset($input[$f])) {
                    $val = $conn->real_escape_string($input[$f]);
                    $sets[] = "`{$f}` = '{$val}'";
                }
            }
            if (count($sets) > 0) {
                $sql = "UPDATE guardforce_crm SET " . implode(', ', $sets) . " WHERE id = {$id}";
                if ($conn->query($sql)) {
                    echo json_encode(array('success' => true, 'id' => $id));
                } else {
                    echo json_encode(array('error' => $conn->error));
                }
            }
        } else {
            // INSERT
            $cols = array();
            $vals = array();
            foreach ($fields as $f) {
                if (isset($input[$f]) && $input[$f] !== '') {
                    $cols[] = "`{$f}`";
                    $vals[] = "'" . $conn->real_escape_string($input[$f]) . "'";
                }
            }
            if (count($cols) > 0) {
                $sql = "INSERT INTO guardforce_crm (" . implode(',', $cols) . ") VALUES (" . implode(',', $vals) . ")";
                if ($conn->query($sql)) {
                    echo json_encode(array('success' => true, 'id' => $conn->insert_id));
                } else {
                    echo json_encode(array('error' => $conn->error));
                }
            }
        }
        exit;
    }

    /* ── DELETE ───────────────────────────────────────────── */
    if ($action == 'delete') {
        $id = isset($_GET['id']) ? (int)$_GET['id'] : 0;
        if ($id > 0) {
            if ($conn->query("DELETE FROM guardforce_crm WHERE id = {$id}")) {
                echo json_encode(array('success' => true));
            } else {
                echo json_encode(array('error' => $conn->error));
            }
        }
        exit;
    }

    /* ── EXPORT CSV ──────────────────────────────────────── */
    if ($action == 'export') {
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="guardforce_crm_export_' . date('Y-m-d') . '.csv"');
        $out = fopen('php://output', 'w');
        $r = $conn->query("SELECT c.*, d.departmentname FROM guardforce_crm c LEFT JOIN department d ON c.department = d.departmentid ORDER BY c.id DESC");
        if ($r) {
            $first = true;
            while ($row = $r->fetch_assoc()) {
                if ($first) { fputcsv($out, array_keys($row)); $first = false; }
                fputcsv($out, $row);
            }
        }
        fclose($out);
        exit;
    }

    echo json_encode(array('error' => 'Unknown action'));
    exit;
}

/* ── HTML MODE ────────────────────────────────────────────── */
include('includes/session.inc');
$Title = _('Guardforce CRM');
include('includes/newheader.php');

$userid = $_SESSION['UserID'];
$allowed = array('admin','ravinesh','riga','sharukh');
if (!in_array($userid, $allowed)) {
    echo '<div style="text-align:center;padding:60px;font-family:Poppins,sans-serif;color:#FF4D4F;font-size:18px;">You are not authorised to access the Guardforce CRM module.</div>';
    echo '</div></div></div>';
    exit;
}
?>
<link href="https://fonts.googleapis.com/css2?family=Poppins:wght@300;400;500;600;700&display=swap" rel="stylesheet">
<style>
/* ── GLOBAL CRM STYLES ─────────────────────────────────── */
:root {
    --crm-primary: #2AABB3;
    --crm-dark: #1E8F96;
    --crm-darker: #187278;
    --crm-light: #E6F7F8;
    --crm-success: #52C41A;
    --crm-warning: #FAAD14;
    --crm-danger: #FF4D4F;
    --crm-orange: #FA8C16;
    --crm-purple: #722ED1;
    --crm-border: #E5E7EB;
    --crm-text: #1F2937;
    --crm-text-secondary: #6B7280;
    --crm-bg: #F8FAFB;
    --crm-white: #FFFFFF;
    --crm-shadow: 0 1px 3px rgba(0,0,0,0.06), 0 1px 2px rgba(0,0,0,0.04);
    --crm-shadow-md: 0 4px 12px rgba(0,0,0,0.08);
    --crm-radius: 10px;
}

#crm-app * { box-sizing: border-box; font-family: 'Poppins', sans-serif; }
#crm-app { max-width: 1440px; margin: 0 auto; padding: 0 16px 40px; color: var(--crm-text); }

/* ── BANNER ────────────────────────────────────────────── */
.crm-banner {
    background: linear-gradient(135deg, var(--crm-dark) 0%, var(--crm-primary) 50%, #34C4CD 100%);
    border-radius: var(--crm-radius);
    padding: 28px 32px;
    margin-bottom: 20px;
    position: relative;
    overflow: hidden;
}
.crm-banner::before {
    content: '';
    position: absolute;
    top: -50%;
    right: -10%;
    width: 300px;
    height: 300px;
    background: rgba(255,255,255,0.06);
    border-radius: 50%;
}
.crm-banner::after {
    content: '';
    position: absolute;
    bottom: -60%;
    left: 20%;
    width: 200px;
    height: 200px;
    background: rgba(255,255,255,0.04);
    border-radius: 50%;
}
.crm-banner h1 {
    margin: 0 0 4px 0;
    font-size: 22px;
    font-weight: 700;
    color: #fff;
    position: relative;
    z-index: 1;
}
.crm-banner-sub {
    color: rgba(255,255,255,0.85);
    font-size: 13px;
    font-weight: 400;
    position: relative;
    z-index: 1;
}
.crm-breadcrumb {
    display: flex;
    align-items: center;
    gap: 8px;
    margin-bottom: 6px;
    position: relative;
    z-index: 1;
}
.crm-breadcrumb a { color: rgba(255,255,255,0.7); text-decoration: none; font-size: 12px; }
.crm-breadcrumb a:hover { color: #fff; }
.crm-breadcrumb span { color: rgba(255,255,255,0.5); font-size: 12px; }

/* ── TOGGLE ────────────────────────────────────────────── */
.crm-dash-toggle {
    display: flex;
    align-items: center;
    gap: 8px;
    padding: 8px 0;
    cursor: pointer;
    user-select: none;
    font-size: 13px;
    font-weight: 600;
    color: var(--crm-dark);
    margin-bottom: 4px;
}
.crm-dash-toggle .arrow { transition: transform 0.25s; display: inline-block; }
.crm-dash-toggle .arrow.collapsed { transform: rotate(-90deg); }
.crm-dashboard-wrap { transition: max-height 0.4s ease, opacity 0.3s ease; overflow: hidden; }
.crm-dashboard-wrap.collapsed { max-height: 0 !important; opacity: 0; }

/* ── KPI CARDS ─────────────────────────────────────────── */
.crm-kpi-grid {
    display: grid;
    grid-template-columns: repeat(4, 1fr);
    gap: 14px;
    margin-bottom: 20px;
}
@media (max-width: 1100px) { .crm-kpi-grid { grid-template-columns: repeat(2, 1fr); } }
@media (max-width: 600px) { .crm-kpi-grid { grid-template-columns: 1fr; } }

.crm-kpi {
    background: var(--crm-white);
    border: 1px solid var(--crm-border);
    border-radius: var(--crm-radius);
    padding: 18px 20px;
    display: flex;
    align-items: flex-start;
    gap: 14px;
    box-shadow: var(--crm-shadow);
    transition: box-shadow 0.2s, transform 0.2s;
}
.crm-kpi:hover { box-shadow: var(--crm-shadow-md); transform: translateY(-1px); }
.crm-kpi-icon {
    width: 44px;
    height: 44px;
    border-radius: 10px;
    display: flex;
    align-items: center;
    justify-content: center;
    font-size: 20px;
    flex-shrink: 0;
}
.crm-kpi-content { flex: 1; min-width: 0; }
.crm-kpi-label { font-size: 12px; color: var(--crm-text-secondary); font-weight: 500; margin-bottom: 4px; }
.crm-kpi-value { font-size: 22px; font-weight: 700; color: var(--crm-text); line-height: 1.1; }
.crm-kpi-sub { font-size: 11px; color: var(--crm-text-secondary); margin-top: 3px; }

/* ── CHARTS GRID ───────────────────────────────────────── */
.crm-charts-grid {
    display: grid;
    grid-template-columns: repeat(3, 1fr);
    gap: 14px;
    margin-bottom: 20px;
}
@media (max-width: 1100px) { .crm-charts-grid { grid-template-columns: repeat(2, 1fr); } }
@media (max-width: 700px) { .crm-charts-grid { grid-template-columns: 1fr; } }

.crm-chart-card {
    background: var(--crm-white);
    border: 1px solid var(--crm-border);
    border-radius: var(--crm-radius);
    padding: 16px 18px;
    box-shadow: var(--crm-shadow);
}
.crm-chart-title {
    font-size: 13px;
    font-weight: 600;
    color: var(--crm-text);
    margin-bottom: 12px;
    display: flex;
    align-items: center;
    gap: 6px;
}
.crm-chart-title i { color: var(--crm-primary); }
.crm-chart-container { position: relative; height: 220px; }

/* ── TABLES SECTION ────────────────────────────────────── */
.crm-section-grid {
    display: grid;
    grid-template-columns: 1fr 1fr;
    gap: 14px;
    margin-bottom: 20px;
}
@media (max-width: 900px) { .crm-section-grid { grid-template-columns: 1fr; } }
.crm-section-full { grid-column: 1 / -1; }

.crm-table-card {
    background: var(--crm-white);
    border: 1px solid var(--crm-border);
    border-radius: var(--crm-radius);
    box-shadow: var(--crm-shadow);
    overflow: hidden;
}
.crm-table-header {
    padding: 14px 18px;
    border-bottom: 1px solid var(--crm-border);
    font-size: 13px;
    font-weight: 600;
    display: flex;
    align-items: center;
    gap: 6px;
}
.crm-table-header i { color: var(--crm-primary); }

.crm-mini-table { width: 100%; border-collapse: collapse; font-size: 12px; }
.crm-mini-table th {
    background: var(--crm-bg);
    padding: 8px 12px;
    text-align: left;
    font-weight: 600;
    color: var(--crm-text-secondary);
    font-size: 11px;
    text-transform: uppercase;
    letter-spacing: 0.3px;
    border-bottom: 1px solid var(--crm-border);
}
.crm-mini-table td {
    padding: 9px 12px;
    border-bottom: 1px solid #F3F4F6;
    color: var(--crm-text);
}
.crm-mini-table tr:hover td { background: var(--crm-light); }
.crm-mini-table tr:last-child td { border-bottom: none; }

/* ── STATUS BADGES ─────────────────────────────────────── */
.crm-badge {
    display: inline-block;
    padding: 2px 10px;
    border-radius: 12px;
    font-size: 11px;
    font-weight: 600;
    line-height: 1.6;
}
.crm-badge-active { background: #F0FFF4; color: #16A34A; border: 1px solid #BBF7D0; }
.crm-badge-expiring { background: #FFFBEB; color: #D97706; border: 1px solid #FDE68A; }
.crm-badge-expired { background: #FEF2F2; color: #DC2626; border: 1px solid #FECACA; }
.crm-badge-clause { background: var(--crm-light); color: var(--crm-dark); border: 1px solid #B2E0E3; font-size: 10px; margin: 1px 2px; }

/* ── TOOLBAR ───────────────────────────────────────────── */
.crm-toolbar {
    display: flex;
    align-items: center;
    gap: 10px;
    flex-wrap: wrap;
    margin-bottom: 14px;
    padding: 14px 18px;
    background: var(--crm-white);
    border: 1px solid var(--crm-border);
    border-radius: var(--crm-radius);
    box-shadow: var(--crm-shadow);
}
.crm-search-box {
    flex: 1;
    min-width: 200px;
    position: relative;
}
.crm-search-box input {
    width: 100%;
    padding: 8px 12px 8px 36px;
    border: 1px solid var(--crm-border);
    border-radius: 8px;
    font-size: 13px;
    font-family: 'Poppins', sans-serif;
    outline: none;
    transition: border-color 0.2s;
}
.crm-search-box input:focus { border-color: var(--crm-primary); }
.crm-search-box i {
    position: absolute;
    left: 12px;
    top: 50%;
    transform: translateY(-50%);
    color: var(--crm-text-secondary);
    font-size: 13px;
}
.crm-select {
    padding: 8px 12px;
    border: 1px solid var(--crm-border);
    border-radius: 8px;
    font-size: 13px;
    font-family: 'Poppins', sans-serif;
    background: #fff;
    outline: none;
    cursor: pointer;
    min-width: 140px;
}
.crm-select:focus { border-color: var(--crm-primary); }

.crm-btn {
    padding: 8px 16px;
    border-radius: 8px;
    font-size: 13px;
    font-weight: 500;
    font-family: 'Poppins', sans-serif;
    cursor: pointer;
    border: none;
    display: inline-flex;
    align-items: center;
    gap: 6px;
    transition: all 0.2s;
    white-space: nowrap;
}
.crm-btn-primary { background: var(--crm-primary); color: #fff; }
.crm-btn-primary:hover { background: var(--crm-dark); }
.crm-btn-success { background: var(--crm-success); color: #fff; }
.crm-btn-success:hover { background: #45A817; }
.crm-btn-danger { background: var(--crm-danger); color: #fff; }
.crm-btn-danger:hover { background: #E83E3E; }
.crm-btn-outline { background: #fff; color: var(--crm-text); border: 1px solid var(--crm-border); }
.crm-btn-outline:hover { border-color: var(--crm-primary); color: var(--crm-primary); }
.crm-btn-sm { padding: 5px 10px; font-size: 12px; }

/* ── DATA TABLE ────────────────────────────────────────── */
.crm-data-table-wrap {
    background: var(--crm-white);
    border: 1px solid var(--crm-border);
    border-radius: var(--crm-radius);
    box-shadow: var(--crm-shadow);
    overflow: hidden;
}
.crm-data-table {
    width: 100%;
    border-collapse: collapse;
    font-size: 12.5px;
}
.crm-data-table thead th {
    background: linear-gradient(180deg, #F9FAFB 0%, #F3F4F6 100%);
    padding: 10px 12px;
    text-align: left;
    font-weight: 600;
    color: var(--crm-text-secondary);
    font-size: 11px;
    text-transform: uppercase;
    letter-spacing: 0.4px;
    border-bottom: 2px solid var(--crm-border);
    cursor: pointer;
    user-select: none;
    white-space: nowrap;
    position: sticky;
    top: 0;
    z-index: 2;
}
.crm-data-table thead th:hover { color: var(--crm-primary); }
.crm-data-table thead th .sort-icon { font-size: 10px; margin-left: 3px; }
.crm-data-table tbody td {
    padding: 10px 12px;
    border-bottom: 1px solid #F3F4F6;
    color: var(--crm-text);
    max-width: 180px;
    overflow: hidden;
    text-overflow: ellipsis;
    white-space: nowrap;
}
.crm-data-table tbody tr { transition: background 0.15s; }
.crm-data-table tbody tr:hover td { background: var(--crm-light); }
.crm-data-table tbody tr:last-child td { border-bottom: none; }
.crm-data-scroll { overflow-x: auto; max-height: 600px; overflow-y: auto; }

/* ── PAGINATION ────────────────────────────────────────── */
.crm-pagination {
    display: flex;
    align-items: center;
    justify-content: space-between;
    padding: 12px 18px;
    border-top: 1px solid var(--crm-border);
    font-size: 12px;
    color: var(--crm-text-secondary);
}
.crm-pagination-btns { display: flex; gap: 4px; }
.crm-pagination-btns button {
    padding: 5px 10px;
    border: 1px solid var(--crm-border);
    border-radius: 6px;
    background: #fff;
    cursor: pointer;
    font-size: 12px;
    font-family: 'Poppins', sans-serif;
}
.crm-pagination-btns button:hover { border-color: var(--crm-primary); color: var(--crm-primary); }
.crm-pagination-btns button.active { background: var(--crm-primary); color: #fff; border-color: var(--crm-primary); }
.crm-pagination-btns button:disabled { opacity: 0.4; cursor: not-allowed; }

/* ── MODAL ─────────────────────────────────────────────── */
.crm-modal-overlay {
    position: fixed;
    top: 0; left: 0; right: 0; bottom: 0;
    background: rgba(0,0,0,0.45);
    z-index: 10000;
    display: flex;
    align-items: center;
    justify-content: center;
    padding: 20px;
    animation: crmFadeIn 0.2s ease;
}
@keyframes crmFadeIn { from { opacity: 0; } to { opacity: 1; } }

.crm-modal {
    background: var(--crm-white);
    border-radius: 12px;
    width: 100%;
    max-width: 820px;
    max-height: 90vh;
    overflow-y: auto;
    box-shadow: 0 20px 60px rgba(0,0,0,0.15);
    animation: crmSlideUp 0.25s ease;
}
@keyframes crmSlideUp { from { transform: translateY(20px); opacity: 0; } to { transform: translateY(0); opacity: 1; } }

.crm-modal-header {
    padding: 18px 24px;
    border-bottom: 1px solid var(--crm-border);
    display: flex;
    align-items: center;
    justify-content: space-between;
    position: sticky;
    top: 0;
    background: #fff;
    z-index: 1;
    border-radius: 12px 12px 0 0;
}
.crm-modal-header h2 { margin: 0; font-size: 17px; font-weight: 600; color: var(--crm-text); }
.crm-modal-close {
    width: 32px; height: 32px;
    border: none;
    background: var(--crm-bg);
    border-radius: 8px;
    cursor: pointer;
    font-size: 18px;
    display: flex;
    align-items: center;
    justify-content: center;
    color: var(--crm-text-secondary);
    transition: all 0.2s;
}
.crm-modal-close:hover { background: #FEE2E2; color: var(--crm-danger); }
.crm-modal-body { padding: 20px 24px; }
.crm-modal-footer {
    padding: 14px 24px;
    border-top: 1px solid var(--crm-border);
    display: flex;
    justify-content: flex-end;
    gap: 8px;
    position: sticky;
    bottom: 0;
    background: #fff;
    border-radius: 0 0 12px 12px;
}

/* ── FORM ──────────────────────────────────────────────── */
.crm-form-grid {
    display: grid;
    grid-template-columns: 1fr 1fr;
    gap: 14px;
}
@media (max-width: 600px) { .crm-form-grid { grid-template-columns: 1fr; } }
.crm-form-full { grid-column: 1 / -1; }
.crm-form-section {
    grid-column: 1 / -1;
    font-size: 13px;
    font-weight: 600;
    color: var(--crm-dark);
    padding: 8px 0 4px 0;
    border-bottom: 2px solid var(--crm-light);
    margin-top: 4px;
    display: flex;
    align-items: center;
    gap: 6px;
}
.crm-form-section i { font-size: 14px; }
.crm-form-group label {
    display: block;
    font-size: 12px;
    font-weight: 500;
    color: var(--crm-text-secondary);
    margin-bottom: 4px;
}
.crm-form-group input,
.crm-form-group select,
.crm-form-group textarea {
    width: 100%;
    padding: 8px 12px;
    border: 1px solid var(--crm-border);
    border-radius: 8px;
    font-size: 13px;
    font-family: 'Poppins', sans-serif;
    outline: none;
    transition: border-color 0.2s, box-shadow 0.2s;
    background: #fff;
}
.crm-form-group input:focus,
.crm-form-group select:focus,
.crm-form-group textarea:focus {
    border-color: var(--crm-primary);
    box-shadow: 0 0 0 3px rgba(42,171,179,0.1);
}
.crm-form-group textarea { resize: vertical; min-height: 70px; }

/* ── VIEW MODAL ────────────────────────────────────────── */
.crm-view-grid {
    display: grid;
    grid-template-columns: 1fr 1fr 1fr;
    gap: 10px;
}
@media (max-width: 700px) { .crm-view-grid { grid-template-columns: 1fr 1fr; } }
.crm-view-item { padding: 8px 12px; background: var(--crm-bg); border-radius: 8px; }
.crm-view-item .vl { font-size: 11px; color: var(--crm-text-secondary); font-weight: 500; margin-bottom: 2px; }
.crm-view-item .vv { font-size: 13px; color: var(--crm-text); font-weight: 600; }
.crm-view-full { grid-column: 1 / -1; }

/* ── LOADING ───────────────────────────────────────────── */
.crm-loading {
    text-align: center;
    padding: 40px;
    color: var(--crm-text-secondary);
}
.crm-spinner {
    display: inline-block;
    width: 30px;
    height: 30px;
    border: 3px solid var(--crm-border);
    border-top-color: var(--crm-primary);
    border-radius: 50%;
    animation: crmSpin 0.8s linear infinite;
}
@keyframes crmSpin { to { transform: rotate(360deg); } }

/* ── ACTION BTNS IN TABLE ──────────────────────────────── */
.crm-action-btns { display: flex; gap: 4px; }
.crm-action-btn {
    width: 28px; height: 28px;
    border: 1px solid var(--crm-border);
    border-radius: 6px;
    background: #fff;
    cursor: pointer;
    display: flex;
    align-items: center;
    justify-content: center;
    font-size: 12px;
    transition: all 0.2s;
}
.crm-action-btn:hover { border-color: var(--crm-primary); color: var(--crm-primary); }
.crm-action-btn.delete:hover { border-color: var(--crm-danger); color: var(--crm-danger); }
.crm-action-btn.view:hover { border-color: var(--crm-success); color: var(--crm-success); }

/* ── TOAST ─────────────────────────────────────────────── */
.crm-toast {
    position: fixed;
    top: 20px;
    right: 20px;
    padding: 12px 20px;
    border-radius: 8px;
    font-size: 13px;
    font-weight: 500;
    color: #fff;
    z-index: 99999;
    animation: crmToastIn 0.3s ease, crmToastOut 0.3s ease 2.7s forwards;
    box-shadow: 0 8px 24px rgba(0,0,0,0.15);
}
.crm-toast-success { background: var(--crm-success); }
.crm-toast-error { background: var(--crm-danger); }
@keyframes crmToastIn { from { transform: translateX(100%); opacity: 0; } to { transform: translateX(0); opacity: 1; } }
@keyframes crmToastOut { from { opacity: 1; } to { opacity: 0; } }

/* ── CLAUSE PILLS ──────────────────────────────────────── */
.crm-clause-pills { display: flex; flex-wrap: wrap; gap: 4px; }
</style>

<div id="crm-app">
    <!-- Banner -->
    <div class="crm-banner">
        <div class="crm-breadcrumb">
            <a href="index.php"><i class="fa fa-home"></i> Home</a>
            <span>›</span>
            <a href="#">Operations</a>
            <span>›</span>
            <span style="color:#fff;font-size:12px;">Guardforce CRM</span>
        </div>
        <h1><i class="fa fa-shield" style="margin-right:8px;"></i>Guardforce CRM</h1>
        <div class="crm-banner-sub">Contract Relationship Management • Security Services Portfolio</div>
    </div>

    <!-- Dashboard Toggle -->
    <div class="crm-dash-toggle" id="dashToggle" onclick="CRM.toggleDash()">
        <span class="arrow" id="dashArrow">▼</span>
        <span>Dashboard Analytics</span>
    </div>

    <!-- Dashboard Content -->
    <div class="crm-dashboard-wrap" id="dashWrap">
        <!-- KPIs -->
        <div class="crm-kpi-grid" id="kpiGrid"></div>

        <!-- Charts -->
        <div class="crm-charts-grid" id="chartsGrid">
            <div class="crm-chart-card">
                <div class="crm-chart-title"><i class="fa fa-pie-chart"></i> Contract Status</div>
                <div class="crm-chart-container"><canvas id="chartStatus"></canvas></div>
            </div>
            <div class="crm-chart-card">
                <div class="crm-chart-title"><i class="fa fa-building"></i> Contracts by Department</div>
                <div class="crm-chart-container"><canvas id="chartDept"></canvas></div>
            </div>
            <div class="crm-chart-card">
                <div class="crm-chart-title"><i class="fa fa-line-chart"></i> Rate Progression (Avg)</div>
                <div class="crm-chart-container"><canvas id="chartRates"></canvas></div>
            </div>
        </div>

        <!-- Section Tables -->
        <div class="crm-section-grid">
            <!-- Expiring Contracts -->
            <div class="crm-table-card">
                <div class="crm-table-header"><i class="fa fa-clock-o"></i> Contracts Expiring Soon</div>
                <div style="max-height:280px;overflow-y:auto;">
                    <table class="crm-mini-table" id="tblExpiring">
                        <thead><tr><th>Customer</th><th>End Date</th><th>Days Left</th><th>Status</th></tr></thead>
                        <tbody></tbody>
                    </table>
                </div>
            </div>

            <!-- Ops Manager Workload -->
            <div class="crm-table-card">
                <div class="crm-table-header"><i class="fa fa-user-circle"></i> Ops Manager Workload</div>
                <div style="max-height:280px;overflow-y:auto;">
                    <table class="crm-mini-table" id="tblOps">
                        <thead><tr><th>Manager</th><th>Contracts</th><th>Monthly Hrs</th></tr></thead>
                        <tbody></tbody>
                    </table>
                </div>
            </div>

            <!-- Top Customers -->
            <div class="crm-table-card">
                <div class="crm-table-header"><i class="fa fa-star"></i> Top 10 Customers by Revenue</div>
                <div style="max-height:280px;overflow-y:auto;">
                    <table class="crm-mini-table" id="tblTop">
                        <thead><tr><th>Customer</th><th>Dept</th><th>Base Rate</th><th>Est. Monthly Rev</th></tr></thead>
                        <tbody></tbody>
                    </table>
                </div>
            </div>

            <!-- Clause Analysis -->
            <div class="crm-table-card">
                <div class="crm-table-header"><i class="fa fa-legal"></i> Contract Clause Analysis</div>
                <div style="padding:16px;" id="clauseAnalysis"></div>
            </div>
        </div>
    </div>

    <!-- TOOLBAR -->
    <div class="crm-toolbar">
        <div class="crm-search-box">
            <i class="fa fa-search"></i>
            <input type="text" id="searchInput" placeholder="Search customers, accounts, managers..." onkeyup="CRM.debounceSearch()">
        </div>
        <select class="crm-select" id="filterDept" onchange="CRM.loadTable()">
            <option value="0">All Departments</option>
        </select>
        <select class="crm-select" id="filterStatus" onchange="CRM.loadTable()">
            <option value="">All Status</option>
            <option value="active">Active</option>
            <option value="expiring">Expiring Soon</option>
            <option value="expired">Expired</option>
        </select>
        <button class="crm-btn crm-btn-primary" onclick="CRM.openForm()"><i class="fa fa-plus"></i> New Contract</button>
        <button class="crm-btn crm-btn-outline" onclick="CRM.exportCSV()"><i class="fa fa-download"></i> Export</button>
    </div>

    <!-- DATA TABLE -->
    <div class="crm-data-table-wrap">
        <div class="crm-data-scroll">
            <table class="crm-data-table" id="crmTable">
                <thead>
                    <tr>
                        <th onclick="CRM.sort('id')">ID <span class="sort-icon"></span></th>
                        <th onclick="CRM.sort('account_number')">Account # <span class="sort-icon"></span></th>
                        <th onclick="CRM.sort('customer_name')">Customer <span class="sort-icon"></span></th>
                        <th>Department</th>
                        <th onclick="CRM.sort('start_date')">Start <span class="sort-icon"></span></th>
                        <th onclick="CRM.sort('end_date')">End <span class="sort-icon"></span></th>
                        <th>Status</th>
                        <th onclick="CRM.sort('base_rate')">Base Rate <span class="sort-icon"></span></th>
                        <th onclick="CRM.sort('est_monthly_hours')">Est. Hrs <span class="sort-icon"></span></th>
                        <th>Clauses</th>
                        <th>Ops Manager</th>
                        <th style="width:90px;">Actions</th>
                    </tr>
                </thead>
                <tbody id="tableBody">
                    <tr><td colspan="12" class="crm-loading"><div class="crm-spinner"></div><br>Loading...</td></tr>
                </tbody>
            </table>
        </div>
        <div class="crm-pagination" id="pagination"></div>
    </div>
</div>

<!-- MODAL CONTAINER -->
<div id="crmModalContainer"></div>

<script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/4.4.1/chart.umd.js"></script>
<script>
(function(){
    'use strict';

    var BASE = 'GuardforceCRM.php?api=';
    var state = {
        page: 1,
        limit: 20,
        search: '',
        dept: 0,
        status: '',
        sort: 'id',
        dir: 'desc',
        departments: [],
        dashData: null,
        charts: {}
    };
    var searchTimer = null;

    /* ── UTILITY ──────────────────────────────────────── */
    function $(id) { return document.getElementById(id); }
    function fmt(n) { return parseFloat(n || 0).toLocaleString('en-US', {minimumFractionDigits:2, maximumFractionDigits:2}); }
    function fmtInt(n) { return parseInt(n || 0).toLocaleString(); }
    function esc(s) { 
        if (!s) return ''; 
        var d = document.createElement('div'); 
        d.textContent = s; 
        return d.innerHTML; 
    }
    function getStatus(endDate) {
        if (!endDate || endDate === '0000-00-00') return 'active';
        var end = new Date(endDate);
        var now = new Date();
        var diff = Math.ceil((end - now) / (1000*60*60*24));
        if (diff < 0) return 'expired';
        if (diff <= 90) return 'expiring';
        return 'active';
    }
    function statusBadge(endDate) {
        var s = getStatus(endDate);
        if (s === 'expired') return '<span class="crm-badge crm-badge-expired">Expired</span>';
        if (s === 'expiring') return '<span class="crm-badge crm-badge-expiring">Expiring</span>';
        return '<span class="crm-badge crm-badge-active">Active</span>';
    }
    function formatDate(d) {
        if (!d || d === '0000-00-00') return '—';
        return d;
    }
    function toast(msg, type) {
        var t = document.createElement('div');
        t.className = 'crm-toast crm-toast-' + (type || 'success');
        t.textContent = msg;
        document.body.appendChild(t);
        setTimeout(function(){ if(t.parentNode) t.parentNode.removeChild(t); }, 3200);
    }
    function clausePills(row) {
        var pills = [];
        if (row['2%_clause'] && row['2%_clause'] !== 'No' && row['2%_clause'] !== 'N/A' && row['2%_clause'] !== '') pills.push('2%');
        if (row.cola && row.cola !== 'No' && row.cola !== 'N/A' && row.cola !== '') pills.push('COLA');
        if (row.min_wage && row.min_wage !== 'No' && row.min_wage !== 'N/A' && row.min_wage !== '') pills.push('MW');
        if (row.non_solicitation && row.non_solicitation !== 'No' && row.non_solicitation !== 'N/A' && row.non_solicitation !== '') pills.push('NS');
        if (pills.length === 0) return '<span style="color:#9CA3AF;font-size:11px;">—</span>';
        return '<div class="crm-clause-pills">' + pills.map(function(p){ return '<span class="crm-badge crm-badge-clause">' + p + '</span>'; }).join('') + '</div>';
    }

    /* ── API CALLS ────────────────────────────────────── */
    function apiGet(url, cb) {
        var xhr = new XMLHttpRequest();
        xhr.open('GET', url, true);
        xhr.onload = function() {
            if (xhr.status === 200) {
                try { cb(JSON.parse(xhr.responseText)); } catch(e) { cb({error:'Parse error'}); }
            } else {
                cb({error:'HTTP ' + xhr.status});
            }
        };
        xhr.onerror = function() { cb({error:'Network error'}); };
        xhr.send();
    }
    function apiPost(url, data, cb) {
        var xhr = new XMLHttpRequest();
        xhr.open('POST', url, true);
        xhr.setRequestHeader('Content-Type','application/json');
        xhr.onload = function() {
            try { cb(JSON.parse(xhr.responseText)); } catch(e) { cb({error:'Parse error'}); }
        };
        xhr.onerror = function() { cb({error:'Network error'}); };
        xhr.send(JSON.stringify(data));
    }

    /* ── DASHBOARD ────────────────────────────────────── */
    function loadDashboard() {
        apiGet(BASE + 'dashboard', function(data) {
            if (data.error) return;
            state.dashData = data;
            renderKPIs(data);
            renderCharts(data);
            renderDashTables(data);
        });
    }

    function renderKPIs(d) {
        var kpis = [
            { icon:'fa-file-text-o', color:'#2AABB3', bg:'#E6F7F8', label:'Total Contracts', value:fmtInt(d.total_contracts), sub:'All time' },
            { icon:'fa-check-circle', color:'#52C41A', bg:'#F0FFF4', label:'Active Contracts', value:fmtInt(d.active_contracts), sub:fmtInt(d.expired_contracts)+' expired' },
            { icon:'fa-exclamation-triangle', color:'#FA8C16', bg:'#FFF7ED', label:'Expiring (90d)', value:fmtInt(d.expiring_soon), sub:'Needs attention' },
            { icon:'fa-dollar', color:'#722ED1', bg:'#F5F0FF', label:'Est. Monthly Revenue', value:'$'+fmt(d.est_monthly_revenue), sub:'Active contracts' },
            { icon:'fa-clock-o', color:'#1E8F96', bg:'#E6F7F8', label:'Total Monthly Hours', value:fmtInt(d.total_monthly_hours), sub:'Active contracts' },
            { icon:'fa-bar-chart', color:'#2AABB3', bg:'#E6F7F8', label:'Avg Base Rate', value:'$'+fmt(d.avg_base_rate), sub:'/hour' },
            { icon:'fa-tags', color:'#FAAD14', bg:'#FFFBEB', label:'Avg Initial Price', value:'$'+fmt(d.avg_initial_price), sub:'/hour' },
            { icon:'fa-calendar-times-o', color:'#FF4D4F', bg:'#FEF2F2', label:'Overdue Reviews', value:fmtInt(d.overdue_reviews), sub:'>12 months since review' }
        ];
        var html = '';
        for (var i = 0; i < kpis.length; i++) {
            var k = kpis[i];
            html += '<div class="crm-kpi">' +
                '<div class="crm-kpi-icon" style="background:'+k.bg+';color:'+k.color+'"><i class="fa '+k.icon+'"></i></div>' +
                '<div class="crm-kpi-content">' +
                    '<div class="crm-kpi-label">'+k.label+'</div>' +
                    '<div class="crm-kpi-value">'+k.value+'</div>' +
                    '<div class="crm-kpi-sub">'+k.sub+'</div>' +
                '</div></div>';
        }
        $('kpiGrid').innerHTML = html;
    }

    function renderCharts(d) {
        // Destroy existing
        for (var key in state.charts) {
            if (state.charts[key]) state.charts[key].destroy();
        }
        var colors = ['#2AABB3','#52C41A','#FAAD14','#FF4D4F','#722ED1','#FA8C16','#13C2C2','#1890FF','#EB2F96','#A0D911'];

        // Status Doughnut
        var sd = d.status_distribution || {};
        state.charts.status = new Chart($('chartStatus'), {
            type: 'doughnut',
            data: {
                labels: ['Active','Expiring Soon','Expired'],
                datasets: [{
                    data: [parseInt(sd.active||0), parseInt(sd.expiring_soon||0), parseInt(sd.expired||0)],
                    backgroundColor: ['#52C41A','#FAAD14','#FF4D4F'],
                    borderWidth: 2,
                    borderColor: '#fff'
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                animation: false,
                plugins: {
                    legend: { position: 'bottom', labels: { font: { family:'Poppins', size:11 }, padding:12, usePointStyle:true } }
                },
                cutout: '60%'
            }
        });

        // Department Bar
        var depts = (d.by_department || []).slice(0, 8);
        state.charts.dept = new Chart($('chartDept'), {
            type: 'bar',
            data: {
                labels: depts.map(function(x){ return (x.departmentname||'Unknown').substring(0,18); }),
                datasets: [{
                    label: 'Contracts',
                    data: depts.map(function(x){ return parseInt(x.cnt||0); }),
                    backgroundColor: colors.slice(0, depts.length),
                    borderRadius: 6,
                    maxBarThickness: 36
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                animation: false,
                indexAxis: 'y',
                plugins: {
                    legend: { display: false }
                },
                scales: {
                    x: { grid: { display:false }, ticks: { font: { family:'Poppins', size:10 } } },
                    y: { grid: { display:false }, ticks: { font: { family:'Poppins', size:10 } } }
                }
            }
        });

        // Rate Progression Line
        var rp = d.rate_progression || {};
        state.charts.rates = new Chart($('chartRates'), {
            type: 'line',
            data: {
                labels: ['Initial','Year 1','Year 2','Year 3','Year 4','Year 5'],
                datasets: [{
                    label: 'Avg Rate ($)',
                    data: [
                        parseFloat(rp.initial_rate||0),
                        parseFloat(rp.yr1||0),
                        parseFloat(rp.yr2||0),
                        parseFloat(rp.yr3||0),
                        parseFloat(rp.yr4||0),
                        parseFloat(rp.yr5||0)
                    ],
                    borderColor: '#2AABB3',
                    backgroundColor: 'rgba(42,171,179,0.1)',
                    fill: true,
                    tension: 0.4,
                    pointBackgroundColor: '#2AABB3',
                    pointBorderColor: '#fff',
                    pointBorderWidth: 2,
                    pointRadius: 5
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                animation: false,
                plugins: {
                    legend: { display: false }
                },
                scales: {
                    x: { grid: { display:false }, ticks: { font: { family:'Poppins', size:10 } } },
                    y: { grid: { color:'#F3F4F6' }, ticks: { font: { family:'Poppins', size:10 }, callback: function(v){ return '$'+v; } } }
                }
            }
        });
    }

    function renderDashTables(d) {
        // Expiring
        var html = '';
        var exp = d.expiring_contracts || [];
        for (var i = 0; i < exp.length; i++) {
            var e = exp[i];
            var days = parseInt(e.days_left || 0);
            var cls = days <= 30 ? 'crm-badge-expired' : (days <= 60 ? 'crm-badge-expiring' : 'crm-badge-active');
            html += '<tr><td>'+esc(e.customer_name)+'</td><td>'+formatDate(e.end_date)+'</td><td><span class="crm-badge '+cls+'">'+days+'d</span></td><td>'+statusBadge(e.end_date)+'</td></tr>';
        }
        if (exp.length === 0) html = '<tr><td colspan="4" style="text-align:center;color:#9CA3AF;padding:20px;">No expiring contracts</td></tr>';
        $('tblExpiring').querySelector('tbody').innerHTML = html;

        // Ops
        html = '';
        var ops = d.ops_workload || [];
        for (var i = 0; i < ops.length; i++) {
            html += '<tr><td><strong>'+esc(ops[i].ops_mng_responsible)+'</strong></td><td>'+fmtInt(ops[i].cnt)+'</td><td>'+fmt(ops[i].hrs)+'</td></tr>';
        }
        if (ops.length === 0) html = '<tr><td colspan="3" style="text-align:center;color:#9CA3AF;padding:20px;">No data</td></tr>';
        $('tblOps').querySelector('tbody').innerHTML = html;

        // Top customers
        html = '';
        var top = d.top_customers || [];
        for (var i = 0; i < top.length; i++) {
            html += '<tr><td>'+esc(top[i].customer_name)+'</td><td>'+esc(top[i].departmentname||'—')+'</td><td>$'+fmt(top[i].base_rate)+'</td><td><strong>$'+fmt(top[i].est_rev)+'</strong></td></tr>';
        }
        if (top.length === 0) html = '<tr><td colspan="4" style="text-align:center;color:#9CA3AF;padding:20px;">No data</td></tr>';
        $('tblTop').querySelector('tbody').innerHTML = html;

        // Clause analysis
        var ca = d.clause_analysis || {};
        var total = parseInt(d.total_contracts || 1);
        function clauseBar(label, val) {
            var pct = Math.round((parseInt(val||0) / total) * 100);
            return '<div style="margin-bottom:12px;">' +
                '<div style="display:flex;justify-content:space-between;margin-bottom:4px;font-size:12px;font-weight:500;">' +
                    '<span>'+label+'</span><span style="color:var(--crm-primary);font-weight:600;">'+parseInt(val||0)+' ('+pct+'%)</span>' +
                '</div>' +
                '<div style="height:8px;background:#F3F4F6;border-radius:4px;overflow:hidden;">' +
                    '<div style="height:100%;width:'+pct+'%;background:linear-gradient(90deg,var(--crm-primary),var(--crm-dark));border-radius:4px;transition:width 0.5s;"></div>' +
                '</div></div>';
        }
        $('clauseAnalysis').innerHTML = clauseBar('2% Clause', ca.has_2pct) + clauseBar('COLA', ca.has_cola) + clauseBar('Min Wage', ca.has_min_wage) + clauseBar('Non-Solicitation', ca.has_non_solicit);
    }

    /* ── TOGGLE DASHBOARD ─────────────────────────────── */
    window.CRM = {};
    CRM.toggleDash = function() {
        var wrap = $('dashWrap');
        var arrow = $('dashArrow');
        var collapsed = !wrap.classList.contains('collapsed');
        if (collapsed) {
            wrap.classList.add('collapsed');
            arrow.classList.add('collapsed');
        } else {
            wrap.classList.remove('collapsed');
            wrap.style.maxHeight = wrap.scrollHeight + 2000 + 'px';
            arrow.classList.remove('collapsed');
        }
        localStorage.setItem('crm_dash_collapsed', collapsed ? '1' : '0');
    };

    /* ── TABLE LOADING ────────────────────────────────── */
    CRM.loadTable = function() {
        state.dept = parseInt($('filterDept').value) || 0;
        state.status = $('filterStatus').value;
        state.search = $('searchInput').value.trim();

        var url = BASE + 'list&page=' + state.page + '&limit=' + state.limit +
                  '&search=' + encodeURIComponent(state.search) +
                  '&dept=' + state.dept + '&status=' + state.status +
                  '&sort=' + state.sort + '&dir=' + state.dir;

        $('tableBody').innerHTML = '<tr><td colspan="12" class="crm-loading"><div class="crm-spinner"></div></td></tr>';

        apiGet(url, function(res) {
            if (res.error) { $('tableBody').innerHTML = '<tr><td colspan="12" style="text-align:center;color:var(--crm-danger);padding:20px;">'+esc(res.error)+'</td></tr>'; return; }
            renderTable(res);
        });
    };

    function renderTable(res) {
        var rows = res.data || [];
        var html = '';
        if (rows.length === 0) {
            html = '<tr><td colspan="12" style="text-align:center;color:#9CA3AF;padding:30px;"><i class="fa fa-inbox" style="font-size:24px;display:block;margin-bottom:8px;"></i>No contracts found</td></tr>';
        } else {
            for (var i = 0; i < rows.length; i++) {
                var r = rows[i];
                html += '<tr>' +
                    '<td><strong>#'+r.id+'</strong></td>' +
                    '<td>'+esc(r.account_number)+'</td>' +
                    '<td title="'+esc(r.customer_name)+'"><strong>'+esc(r.customer_name)+'</strong></td>' +
                    '<td>'+esc(r.departmentname || '—')+'</td>' +
                    '<td>'+formatDate(r.start_date)+'</td>' +
                    '<td>'+formatDate(r.end_date)+'</td>' +
                    '<td>'+statusBadge(r.end_date)+'</td>' +
                    '<td>$'+fmt(r.base_rate)+'</td>' +
                    '<td>'+fmt(r.est_monthly_hours)+'</td>' +
                    '<td>'+clausePills(r)+'</td>' +
                    '<td>'+esc(r.ops_mng_responsible || '—')+'</td>' +
                    '<td><div class="crm-action-btns">' +
                        '<button class="crm-action-btn view" onclick="CRM.viewRecord('+r.id+')" title="View"><i class="fa fa-eye"></i></button>' +
                        '<button class="crm-action-btn" onclick="CRM.editRecord('+r.id+')" title="Edit"><i class="fa fa-pencil"></i></button>' +
                        '<button class="crm-action-btn delete" onclick="CRM.deleteRecord('+r.id+',\''+esc(r.customer_name).replace(/'/g,"\\'")+'\')" title="Delete"><i class="fa fa-trash-o"></i></button>' +
                    '</div></td></tr>';
            }
        }
        $('tableBody').innerHTML = html;
        renderPagination(res.total, res.page, res.limit);
    }

    function renderPagination(total, page, limit) {
        var pages = Math.ceil(total / limit);
        var html = '<span>Showing ' + (((page-1)*limit)+1) + '–' + Math.min(page*limit, total) + ' of ' + fmtInt(total) + ' contracts</span>';
        html += '<div class="crm-pagination-btns">';
        html += '<button onclick="CRM.goPage(1)" ' + (page<=1?'disabled':'') + '>«</button>';
        html += '<button onclick="CRM.goPage('+(page-1)+')" ' + (page<=1?'disabled':'') + '>‹</button>';

        var start = Math.max(1, page - 2);
        var end = Math.min(pages, page + 2);
        for (var i = start; i <= end; i++) {
            html += '<button onclick="CRM.goPage('+i+')" class="'+(i===page?'active':'')+'">' + i + '</button>';
        }

        html += '<button onclick="CRM.goPage('+(page+1)+')" ' + (page>=pages?'disabled':'') + '>›</button>';
        html += '<button onclick="CRM.goPage('+pages+')" ' + (page>=pages?'disabled':'') + '>»</button>';
        html += '</div>';
        $('pagination').innerHTML = html;
    }

    CRM.goPage = function(p) { state.page = p; CRM.loadTable(); };
    CRM.sort = function(col) {
        if (state.sort === col) {
            state.dir = state.dir === 'asc' ? 'desc' : 'asc';
        } else {
            state.sort = col;
            state.dir = 'desc';
        }
        state.page = 1;
        CRM.loadTable();
    };

    CRM.debounceSearch = function() {
        clearTimeout(searchTimer);
        searchTimer = setTimeout(function() { state.page = 1; CRM.loadTable(); }, 400);
    };

    /* ── VIEW RECORD ──────────────────────────────────── */
    CRM.viewRecord = function(id) {
        apiGet(BASE + 'get&id=' + id, function(r) {
            if (r.error) return toast(r.error, 'error');
            var html = '<div class="crm-modal-overlay" onclick="if(event.target===this)CRM.closeModal()">' +
                '<div class="crm-modal" style="max-width:900px;">' +
                '<div class="crm-modal-header"><h2><i class="fa fa-eye" style="color:var(--crm-primary);margin-right:8px;"></i>Contract Details — ' + esc(r.customer_name) + '</h2>' +
                '<button class="crm-modal-close" onclick="CRM.closeModal()">×</button></div>' +
                '<div class="crm-modal-body">';

            html += '<div class="crm-view-grid">';
            var fields = [
                {l:'ID', v:'#'+r.id}, {l:'Account #', v:r.account_number}, {l:'Customer', v:r.customer_name},
                {l:'Customer Code', v:r.debtor_name || r.customer_code}, {l:'Department', v:r.departmentname || '—'}, {l:'Status', v:statusBadge(r.end_date)},
                {l:'Contract Date', v:formatDate(r.customer_contract_date)}, {l:'Start Date', v:formatDate(r.start_date)}, {l:'End Date', v:formatDate(r.end_date)},
                {l:'Last Mgmt Review', v:formatDate(r.last_management_review_date)}, {l:'Ops Manager', v:r.ops_mng_responsible || '—'}, {l:'Est Monthly Hrs', v:fmt(r.est_monthly_hours)},
                {l:'Base Rate', v:'$'+fmt(r.base_rate)}, {l:'Overtime Rate', v:'$'+fmt(r.overtime_rate)}, {l:'Ad Hoc Rate', v:'$'+fmt(r.ad_hoc_rate)},
                {l:'Other Rates', v:'$'+fmt(r.other_rates)}, {l:'Initial Price/Hr', v:'$'+fmt(r.initial_price_per_hour)}, {l:'Est Monthly Rev', v:'$'+fmt((parseFloat(r.base_rate||0)*parseFloat(r.est_monthly_hours||0)))},
                {l:'Rate Year 1', v:'$'+fmt(r.rate_year1)}, {l:'Rate Year 2', v:'$'+fmt(r.rate_year2)}, {l:'Rate Year 3', v:'$'+fmt(r.rate_year3)},
                {l:'Rate Year 4', v:'$'+fmt(r.rate_year4)}, {l:'Rate Year 5', v:'$'+fmt(r.rate_year5)}, {l:'2% Clause', v:r['2%_clause'] || '—'},
                {l:'COLA', v:r.cola || '—'}, {l:'Min Wage', v:r.min_wage || '—'}, {l:'Non-Solicitation', v:r.non_solicitation || '—'},
                {l:'Other', v:r.other || '—'}
            ];
            for (var i = 0; i < fields.length; i++) {
                html += '<div class="crm-view-item"><div class="vl">'+fields[i].l+'</div><div class="vv">'+fields[i].v+'</div></div>';
            }
            if (r.comments) {
                html += '<div class="crm-view-item crm-view-full"><div class="vl">Comments</div><div class="vv" style="white-space:pre-wrap;font-weight:400;">'+esc(r.comments)+'</div></div>';
            }
            html += '</div></div>';
            html += '<div class="crm-modal-footer"><button class="crm-btn crm-btn-primary" onclick="CRM.editRecord('+r.id+')"><i class="fa fa-pencil"></i> Edit</button><button class="crm-btn crm-btn-outline" onclick="CRM.closeModal()">Close</button></div>';
            html += '</div></div>';
            $('crmModalContainer').innerHTML = html;
        });
    };

    /* ── FORM (ADD/EDIT) ──────────────────────────────── */
    CRM.openForm = function(data) {
        var isEdit = data && data.id;
        var title = isEdit ? 'Edit Contract — ' + esc(data.customer_name) : 'New Contract';

        function val(f) { return isEdit ? (data[f] || '') : ''; }
        function dval(f) { var v = val(f); return (v === '0000-00-00') ? '' : v; }

        var deptOpts = '<option value="">Select Department</option>';
        for (var i = 0; i < state.departments.length; i++) {
            var d = state.departments[i];
            var sel = (isEdit && data.department == d.departmentid) ? ' selected' : '';
            deptOpts += '<option value="'+d.departmentid+'"'+sel+'>'+esc(d.departmentname)+'</option>';
        }

        var html = '<div class="crm-modal-overlay" onclick="if(event.target===this)CRM.closeModal()">' +
            '<div class="crm-modal">' +
            '<div class="crm-modal-header"><h2><i class="fa fa-'+(isEdit?'pencil':'plus-circle')+'" style="color:var(--crm-primary);margin-right:8px;"></i>'+title+'</h2>' +
            '<button class="crm-modal-close" onclick="CRM.closeModal()">×</button></div>' +
            '<div class="crm-modal-body"><div class="crm-form-grid">' +

            '<div class="crm-form-section"><i class="fa fa-user"></i> Customer Information</div>' +
            '<div class="crm-form-group"><label>Customer Name *</label><input id="f_customer_name" value="'+esc(val('customer_name'))+'"></div>' +
            '<div class="crm-form-group"><label>Account Number</label><input id="f_account_number" value="'+esc(val('account_number'))+'"></div>' +
            '<div class="crm-form-group"><label>Customer Code (Debtor)</label><input id="f_customer_code" value="'+esc(val('customer_code'))+'" placeholder="Debtor number"></div>' +
            '<div class="crm-form-group"><label>Department</label><select id="f_department">'+deptOpts+'</select></div>' +
            '<div class="crm-form-group"><label>Ops Manager Responsible</label><input id="f_ops_mng_responsible" value="'+esc(val('ops_mng_responsible'))+'"></div>' +
            '<div class="crm-form-group"><label>Last Mgmt Review Date</label><input type="date" id="f_last_management_review_date" value="'+dval('last_management_review_date')+'"></div>' +

            '<div class="crm-form-section"><i class="fa fa-calendar"></i> Contract Dates</div>' +
            '<div class="crm-form-group"><label>Contract Date</label><input type="date" id="f_customer_contract_date" value="'+dval('customer_contract_date')+'"></div>' +
            '<div class="crm-form-group"><label>Start Date</label><input type="date" id="f_start_date" value="'+dval('start_date')+'"></div>' +
            '<div class="crm-form-group"><label>End Date</label><input type="date" id="f_end_date" value="'+dval('end_date')+'"></div>' +

            '<div class="crm-form-section"><i class="fa fa-dollar"></i> Rates & Pricing</div>' +
            '<div class="crm-form-group"><label>Base Rate ($/hr)</label><input type="number" step="0.01" id="f_base_rate" value="'+val('base_rate')+'"></div>' +
            '<div class="crm-form-group"><label>Overtime Rate ($/hr)</label><input type="number" step="0.01" id="f_overtime_rate" value="'+val('overtime_rate')+'"></div>' +
            '<div class="crm-form-group"><label>Ad Hoc Rate ($/hr)</label><input type="number" step="0.01" id="f_ad_hoc_rate" value="'+val('ad_hoc_rate')+'"></div>' +
            '<div class="crm-form-group"><label>Other Rates ($/hr)</label><input type="number" step="0.01" id="f_other_rates" value="'+val('other_rates')+'"></div>' +
            '<div class="crm-form-group"><label>Initial Price Per Hour</label><input type="number" step="0.01" id="f_initial_price_per_hour" value="'+val('initial_price_per_hour')+'"></div>' +
            '<div class="crm-form-group"><label>Est. Monthly Hours</label><input type="number" step="0.01" id="f_est_monthly_hours" value="'+val('est_monthly_hours')+'"></div>' +

            '<div class="crm-form-section"><i class="fa fa-line-chart"></i> Year Rate Progression</div>' +
            '<div class="crm-form-group"><label>Year 1 Rate</label><input type="number" step="0.01" id="f_rate_year1" value="'+val('rate_year1')+'"></div>' +
            '<div class="crm-form-group"><label>Year 2 Rate</label><input type="number" step="0.01" id="f_rate_year2" value="'+val('rate_year2')+'"></div>' +
            '<div class="crm-form-group"><label>Year 3 Rate</label><input type="number" step="0.01" id="f_rate_year3" value="'+val('rate_year3')+'"></div>' +
            '<div class="crm-form-group"><label>Year 4 Rate</label><input type="number" step="0.01" id="f_rate_year4" value="'+val('rate_year4')+'"></div>' +
            '<div class="crm-form-group"><label>Year 5 Rate</label><input type="number" step="0.01" id="f_rate_year5" value="'+val('rate_year5')+'"></div>' +

            '<div class="crm-form-section"><i class="fa fa-legal"></i> Contract Clauses</div>' +
            '<div class="crm-form-group"><label>2% Clause</label><input id="f_2pct_clause" value="'+esc(val('2%_clause'))+'"></div>' +
            '<div class="crm-form-group"><label>COLA</label><input id="f_cola" value="'+esc(val('cola'))+'"></div>' +
            '<div class="crm-form-group"><label>Min Wage</label><input id="f_min_wage" value="'+esc(val('min_wage'))+'"></div>' +
            '<div class="crm-form-group"><label>Non-Solicitation</label><input id="f_non_solicitation" value="'+esc(val('non_solicitation'))+'"></div>' +
            '<div class="crm-form-group"><label>Other</label><input id="f_other" value="'+esc(val('other'))+'"></div>' +

            '<div class="crm-form-section"><i class="fa fa-comment"></i> Notes</div>' +
            '<div class="crm-form-group crm-form-full"><label>Comments</label><textarea id="f_comments">'+esc(val('comments'))+'</textarea></div>' +

            '</div></div>' +
            '<div class="crm-modal-footer">' +
            '<button class="crm-btn crm-btn-outline" onclick="CRM.closeModal()">Cancel</button>' +
            '<button class="crm-btn crm-btn-success" onclick="CRM.saveForm('+(isEdit ? data.id : 0)+')"><i class="fa fa-check"></i> '+(isEdit?'Update':'Create')+'</button>' +
            '</div></div></div>';

        $('crmModalContainer').innerHTML = html;
    };

    CRM.editRecord = function(id) {
        apiGet(BASE + 'get&id=' + id, function(r) {
            if (r.error) return toast(r.error, 'error');
            CRM.openForm(r);
        });
    };

    CRM.saveForm = function(id) {
        var payload = {
            id: id,
            customer_name: $('f_customer_name').value,
            account_number: $('f_account_number').value,
            customer_code: $('f_customer_code').value,
            department: $('f_department').value,
            ops_mng_responsible: $('f_ops_mng_responsible').value,
            last_management_review_date: $('f_last_management_review_date').value,
            customer_contract_date: $('f_customer_contract_date').value,
            start_date: $('f_start_date').value,
            end_date: $('f_end_date').value,
            base_rate: $('f_base_rate').value,
            overtime_rate: $('f_overtime_rate').value,
            ad_hoc_rate: $('f_ad_hoc_rate').value,
            other_rates: $('f_other_rates').value,
            initial_price_per_hour: $('f_initial_price_per_hour').value,
            est_monthly_hours: $('f_est_monthly_hours').value,
            rate_year1: $('f_rate_year1').value,
            rate_year2: $('f_rate_year2').value,
            rate_year3: $('f_rate_year3').value,
            rate_year4: $('f_rate_year4').value,
            rate_year5: $('f_rate_year5').value,
            '2%_clause': $('f_2pct_clause').value,
            cola: $('f_cola').value,
            min_wage: $('f_min_wage').value,
            non_solicitation: $('f_non_solicitation').value,
            other: $('f_other').value,
            comments: $('f_comments').value
        };

        if (!payload.customer_name) return toast('Customer name is required', 'error');

        apiPost(BASE + 'save', payload, function(res) {
            if (res.error) return toast(res.error, 'error');
            toast(id > 0 ? 'Contract updated successfully' : 'Contract created successfully', 'success');
            CRM.closeModal();
            CRM.loadTable();
            loadDashboard();
        });
    };

    /* ── DELETE ────────────────────────────────────────── */
    CRM.deleteRecord = function(id, name) {
        if (!confirm('Are you sure you want to delete contract for "' + name + '"?\n\nThis action cannot be undone.')) return;
        apiGet(BASE + 'delete&id=' + id, function(res) {
            if (res.error) return toast(res.error, 'error');
            toast('Contract deleted', 'success');
            CRM.loadTable();
            loadDashboard();
        });
    };

    /* ── EXPORT ────────────────────────────────────────── */
    CRM.exportCSV = function() {
        window.location.href = BASE + 'export';
    };

    /* ── CLOSE MODAL ──────────────────────────────────── */
    CRM.closeModal = function() {
        $('crmModalContainer').innerHTML = '';
    };

    /* ── INIT ─────────────────────────────────────────── */
    function init() {
        // Restore dash state
        if (localStorage.getItem('crm_dash_collapsed') === '1') {
            $('dashWrap').classList.add('collapsed');
            $('dashArrow').classList.add('collapsed');
        } else {
            $('dashWrap').style.maxHeight = '5000px';
        }

        // Load departments
        apiGet(BASE + 'departments', function(depts) {
            state.departments = depts || [];
            var sel = $('filterDept');
            for (var i = 0; i < state.departments.length; i++) {
                var opt = document.createElement('option');
                opt.value = state.departments[i].departmentid;
                opt.textContent = state.departments[i].departmentname;
                sel.appendChild(opt);
            }
        });

        loadDashboard();
        CRM.loadTable();
    }

    // Start
    if (document.readyState === 'loading') {
        document.addEventListener('DOMContentLoaded', init);
    } else {
        init();
    }
})();
</script>

<?php
echo '</div></div></div>';
?>