<?php
declare(strict_types=1);

require_once __DIR__ . '/report-data.php';

function crm_dashboard_role_allowed(): bool
{
    $user = function_exists('current_user') ? (current_user() ?: []) : [];
    if ((int)($user['role_id'] ?? 0) === 1) {
        return true;
    }
    $identity = strtolower(trim(implode(' ', [
        (string)($user['username'] ?? ''),
        (string)($user['role'] ?? ''),
        (string)($user['role_name'] ?? ''),
    ])));
    return strpos($identity, 'crmadmin') !== false || strpos($identity, 'crm admin') !== false
        || strpos($identity, 'crm administrator') !== false;
}

function crm_dashboard_data(): array
{
    [$db] = crm_report_context();
    $statusSql = crm_status_sql('st');

    $leadSummary = crm_report_query(
        $db,
        "SELECT COUNT(*) total,
                SUM(DATE(a.date)=CURDATE()) today,
                SUM(YEAR(a.date)=YEAR(CURDATE()) AND MONTH(a.date)=MONTH(CURDATE())) month_total,
                SUM({$statusSql} REGEXP 'convert|won|success') converted,
                SUM({$statusSql} REGEXP 'lost|cancel|reject|drop') lost
         FROM jos_crm_leadentry a
         LEFT JOIN jos_crm_mleadstatus st ON st.id=a.leadstatus"
    )[0] ?? [];
    $total = (int)($leadSummary['total'] ?? 0);
    $converted = (int)($leadSummary['converted'] ?? 0);
    $lost = (int)($leadSummary['lost'] ?? 0);
    $open = max(0, $total - $converted - $lost);

    $followups = crm_report_query(
        $db,
        "SELECT SUM(DATE(a.followupdate)<CURDATE() AND DATE(a.followupdate)>'1970-01-01') overdue,
                SUM(DATE(a.followupdate)=CURDATE()) today,
                SUM(DATE(a.followupdate)>CURDATE() AND DATE(a.followupdate)<=DATE_ADD(CURDATE(),INTERVAL 7 DAY)) upcoming
         FROM jos_crm_leadentry a
         LEFT JOIN jos_crm_mleadstatus st ON st.id=a.leadstatus
         WHERE NOT ({$statusSql} REGEXP 'convert|won|success|lost|cancel|reject|drop')"
    )[0] ?? ['overdue' => 0, 'today' => 0, 'upcoming' => 0];

    $quoteSummary = ['documents' => 0, 'subtotal' => 0, 'gst' => 0, 'total' => 0, 'approved' => 0, 'cancelled' => 0];
    if (crm_report_table($db, 'jos_crm_quotations')) {
        $quoteSummary = crm_report_query(
            $db,
            "SELECT COUNT(*) documents,
                    SUM(COALESCE(subtotal,0)) subtotal,
                    SUM(COALESCE(gst,0)) gst,
                    SUM(COALESCE(total,0)) total,
                    SUM(COALESCE(approved,0)=1) approved,
                    SUM(COALESCE(cancelled,0)=1) cancelled
             FROM jos_crm_quotations"
        )[0] ?? $quoteSummary;
    }
    $quoteOpen = max(0, (int)$quoteSummary['documents'] - (int)$quoteSummary['approved'] - (int)$quoteSummary['cancelled']);

    $statusRows = crm_report_query(
        $db,
        "SELECT COALESCE(NULLIF(st.name,''),'Unspecified') label,COUNT(*) total
         FROM jos_crm_leadentry a
         LEFT JOIN jos_crm_mleadstatus st ON st.id=a.leadstatus
         GROUP BY COALESCE(NULLIF(st.name,''),'Unspecified')
         ORDER BY total DESC"
    );

    $trendRows = crm_report_query(
        $db,
        "SELECT DATE_FORMAT(months.month_start,'%b') label,
                DATE_FORMAT(months.month_start,'%Y-%m') month_key,
                COALESCE(leads.total,0) leads,
                COALESCE(quotes.total,0) quotes,
                COALESCE(quotes.value,0) quote_value
         FROM (
             SELECT DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL n MONTH),'%Y-%m-01') month_start
             FROM (
                 SELECT 11 n UNION ALL SELECT 10 UNION ALL SELECT 9 UNION ALL SELECT 8
                 UNION ALL SELECT 7 UNION ALL SELECT 6 UNION ALL SELECT 5 UNION ALL SELECT 4
                 UNION ALL SELECT 3 UNION ALL SELECT 2 UNION ALL SELECT 1 UNION ALL SELECT 0
             ) seq
         ) months
         LEFT JOIN (
             SELECT DATE_FORMAT(date,'%Y-%m-01') month_start,COUNT(*) total
             FROM jos_crm_leadentry
             WHERE date>=DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 11 MONTH),'%Y-%m-01')
             GROUP BY DATE_FORMAT(date,'%Y-%m-01')
         ) leads ON leads.month_start=months.month_start
         LEFT JOIN (
             SELECT DATE_FORMAT(date,'%Y-%m-01') month_start,COUNT(*) total,SUM(COALESCE(total,0)) value
             FROM jos_crm_quotations
             WHERE date>=DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 11 MONTH),'%Y-%m-01')
               AND COALESCE(cancelled,0)<>1
             GROUP BY DATE_FORMAT(date,'%Y-%m-01')
         ) quotes ON quotes.month_start=months.month_start
         ORDER BY months.month_start"
    );

    $userJoin = crm_report_table($db, 'jos_users')
        ? 'LEFT JOIN jos_users u ON u.id=a.created_by' : '';
    $userName = $userJoin !== ''
        ? "COALESCE(NULLIF(u.name,''),NULLIF(u.username,''),CONCAT('User #',a.created_by))"
        : "CONCAT('User #',a.created_by)";
    $topUsers = crm_report_query(
        $db,
        "SELECT a.created_by user_id,{$userName} label,COUNT(*) leads,
                SUM({$statusSql} REGEXP 'convert|won|success') converted
         FROM jos_crm_leadentry a
         LEFT JOIN jos_crm_mleadstatus st ON st.id=a.leadstatus
         {$userJoin}
         WHERE a.date>=DATE_FORMAT(CURDATE(),'%Y-%m-01')
         GROUP BY a.created_by" . ($userJoin !== '' ? ',u.name,u.username' : '') . "
         ORDER BY leads DESC LIMIT 6"
    );

    $latestLeads = crm_report_query(
        $db,
        "SELECT a.id,a.date,a.followupdate,
                COALESCE(NULLIF(c.companyname,''),CONCAT('Customer #',a.custid)) customer,
                COALESCE(NULLIF(st.name,''),'Unspecified') status,
                {$userName} owner
         FROM jos_crm_leadentry a
         LEFT JOIN jos_crm_mcustomers c ON c.id=a.custid
         LEFT JOIN jos_crm_mleadstatus st ON st.id=a.leadstatus
         {$userJoin}
         ORDER BY a.date DESC,a.id DESC LIMIT 8"
    );

    $dueLeads = crm_report_query(
        $db,
        "SELECT a.id,a.followupdate,
                COALESCE(NULLIF(c.companyname,''),CONCAT('Customer #',a.custid)) customer,
                COALESCE(NULLIF(st.name,''),'Unspecified') status,
                {$userName} owner
         FROM jos_crm_leadentry a
         LEFT JOIN jos_crm_mcustomers c ON c.id=a.custid
         LEFT JOIN jos_crm_mleadstatus st ON st.id=a.leadstatus
         {$userJoin}
         WHERE DATE(a.followupdate)<=CURDATE() AND DATE(a.followupdate)>'1970-01-01'
           AND NOT ({$statusSql} REGEXP 'convert|won|success|lost|cancel|reject|drop')
         ORDER BY a.followupdate ASC,a.id DESC LIMIT 8"
    );

    $maxTrend = 1;
    foreach ($trendRows as $row) {
        $maxTrend = max($maxTrend, (int)$row['leads'], (int)$row['quotes']);
    }
    $maxStatus = 1;
    foreach ($statusRows as $row) {
        $maxStatus = max($maxStatus, (int)$row['total']);
    }

    return [
        'leads' => [
            'total' => $total,
            'open' => $open,
            'converted' => $converted,
            'lost' => $lost,
            'today' => (int)($leadSummary['today'] ?? 0),
            'month' => (int)($leadSummary['month_total'] ?? 0),
        ],
        'followups' => [
            'overdue' => (int)($followups['overdue'] ?? 0),
            'today' => (int)($followups['today'] ?? 0),
            'upcoming' => (int)($followups['upcoming'] ?? 0),
        ],
        'quotations' => [
            'documents' => (int)$quoteSummary['documents'],
            'approved' => (int)$quoteSummary['approved'],
            'cancelled' => (int)$quoteSummary['cancelled'],
            'open' => $quoteOpen,
            'subtotal' => (float)$quoteSummary['subtotal'],
            'gst' => (float)$quoteSummary['gst'],
            'total' => (float)$quoteSummary['total'],
        ],
        'status_rows' => $statusRows,
        'trend_rows' => $trendRows,
        'top_users' => $topUsers,
        'latest_leads' => $latestLeads,
        'due_leads' => $dueLeads,
        'max_trend' => $maxTrend,
        'max_status' => $maxStatus,
    ];
}
