<?php
declare(strict_types=1);

require_once __DIR__ . '/bootstrap.php';

function crm_report_db(): ?mysqli
{
    foreach (['con', 'conn', 'mysqli', 'db'] as $name) {
        if (($GLOBALS[$name] ?? null) instanceof mysqli) {
            return $GLOBALS[$name];
        }
    }
    return null;
}

function crm_report_filters(): array
{
    $date = static function ($value): string {
        $value = trim((string)$value);
        return preg_match('/^\d{4}-\d{2}-\d{2}$/', $value) === 1 ? $value : '';
    };
    return ['from' => $date($_GET['from'] ?? ''), 'to' => $date($_GET['to'] ?? '')];
}

function crm_report_where(mysqli $db, string $field, array $filters): string
{
    $parts = [];
    if ($filters['from'] !== '') {
        $parts[] = "DATE({$field}) >= '" . $db->real_escape_string($filters['from']) . "'";
    }
    if ($filters['to'] !== '') {
        $parts[] = "DATE({$field}) <= '" . $db->real_escape_string($filters['to']) . "'";
    }
    return $parts === [] ? '1=1' : implode(' AND ', $parts);
}

function crm_report_table(mysqli $db, string $table): bool
{
    $result = $db->query("SHOW TABLES LIKE '" . $db->real_escape_string($table) . "'");
    return $result instanceof mysqli_result && $result->num_rows > 0;
}

function crm_report_query(mysqli $db, string $sql): array
{
    $result = $db->query($sql);
    if (!$result instanceof mysqli_result) {
        throw new RuntimeException($db->error ?: 'CRM report query failed.');
    }
    return $result->fetch_all(MYSQLI_ASSOC);
}

function crm_report_value(mysqli $db, string $sql)
{
    $row = crm_report_query($db, $sql)[0] ?? [];
    return $row['value'] ?? 0;
}

function crm_n($value): string
{
    return number_format((float)$value, 0);
}

function crm_pct($part, $total): string
{
    return (float)$total > 0 ? number_format((float)$part * 100 / (float)$total, 1) . '%' : '0.0%';
}

function crm_money($value): string
{
    return '₹' . number_format((float)$value, 2);
}

function crm_date($value): string
{
    $time = strtotime((string)$value);
    return $time === false ? (string)$value : date('d M Y', $time);
}

function crm_status_sql(string $alias = 'st'): string
{
    return "LOWER(COALESCE({$alias}.name,''))";
}

function crm_report_context(): array
{
    $db = crm_report_db();
    if (!$db) {
        throw new RuntimeException('ABOX database connection is unavailable.');
    }
    foreach (['jos_crm_leadentry', 'jos_crm_mcustomers', 'jos_crm_mleadstatus'] as $table) {
        if (!crm_report_table($db, $table)) {
            throw new RuntimeException("Required CRM table {$table} is unavailable.");
        }
    }
    return [$db, crm_report_filters()];
}

function crm_empty_report(string $message): array
{
    return [
        'kpis' => [
            ['Total Records', '0', 'Live database'],
            ['Open', '0', 'Live database'],
            ['Converted', '0', 'Live database'],
            ['Value', crm_money(0), 'Live database'],
        ],
        'columns' => ['Status'],
        'rows' => [],
        'message' => $message,
    ];
}

function crm_report_dashboard_data(): array
{
    [$db] = crm_report_context();
    $total = (int)crm_report_value($db, 'SELECT COUNT(*) value FROM jos_crm_leadentry');
    $converted = (int)crm_report_value(
        $db,
        "SELECT COUNT(*) value FROM jos_crm_leadentry a
         LEFT JOIN jos_crm_mleadstatus st ON st.id=a.leadstatus
         WHERE " . crm_status_sql() . " REGEXP 'convert|won|success'"
    );
    $due = $today = 0;
    if (crm_report_table($db, 'jos_crm_lead_followup_grid')) {
        $today = (int)crm_report_value($db, 'SELECT COUNT(*) value FROM jos_crm_lead_followup_grid WHERE DATE(fdate)=CURDATE()');
        $due = (int)crm_report_value(
            $db,
            'SELECT COUNT(*) value FROM jos_crm_lead_followup_grid
             WHERE DATE(fdate) BETWEEN CURDATE() AND DATE_ADD(CURDATE(),INTERVAL 7 DAY)'
        );
    }
    $value = crm_report_table($db, 'jos_crm_leadentry_grid')
        ? crm_report_value($db, 'SELECT COALESCE(SUM(amt),0) value FROM jos_crm_leadentry_grid')
        : 0;
    return [
        ['Total Leads', crm_n($total), 'Migrated INV lead records'],
        ['Follow-ups Due', crm_n($due), crm_n($today) . ' due today'],
        ['Converted', crm_n($converted), crm_pct($converted, $total) . ' conversion rate'],
        ['Pipeline Value', crm_money($value), 'Lead product value'],
    ];
}

function crm_lead_report(mysqli $db, array $filters): array
{
    $where = crm_report_where($db, 'a.date', $filters);
    $status = crm_status_sql();
    $summary = crm_report_query(
        $db,
        "SELECT COUNT(*) total,
                SUM({$status} REGEXP 'convert|won|success') converted,
                SUM({$status} REGEXP 'lost|cancel|reject|drop') lost
         FROM jos_crm_leadentry a
         LEFT JOIN jos_crm_mleadstatus st ON st.id=a.leadstatus WHERE {$where}"
    )[0] ?? ['total' => 0, 'converted' => 0, 'lost' => 0];
    $total = (int)$summary['total'];
    $converted = (int)$summary['converted'];
    $lost = (int)$summary['lost'];
    $open = max(0, $total - $converted - $lost);
    $pipeline = crm_report_table($db, 'jos_crm_leadentry_grid')
        ? crm_report_value(
            $db,
            "SELECT COALESCE(SUM(g.amt),0) value FROM jos_crm_leadentry_grid g
             INNER JOIN jos_crm_leadentry a ON a.id=g.leadid WHERE {$where}"
        )
        : 0;
    $sourceJoin = crm_report_table($db, 'jos_crm_mleadsource')
        ? 'LEFT JOIN jos_crm_mleadsource src ON src.id=a.leadsource' : '';
    $source = $sourceJoin
        ? "COALESCE(NULLIF(src.leadsource,''),CONCAT('Source #',a.leadsource))"
        : "CONCAT('Source #',a.leadsource)";
    $rows = crm_report_query(
        $db,
        "SELECT a.id,COALESCE(NULLIF(c.companyname,''),CONCAT('Customer #',a.custid)) company,
                COALESCE(
                    NULLIF(c.contactperson1,''),
                    NULLIF(c.contactno1,''),
                    NULLIF(c.officecontact,''),
                    NULLIF(c.contactnumber,''),
                    '-'
                ) contact,
                {$source} source,CONCAT('User #',a.created_by) owner,
                COALESCE(NULLIF(st.name,''),CONCAT('Status #',a.leadstatus)) status,a.date
         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 {$sourceJoin}
         WHERE {$where} ORDER BY a.date DESC,a.id DESC"
    );
    return [
        'kpis' => [
            ['Total Leads', crm_n($total), 'All selected records'],
            ['Open', crm_n($open), crm_pct($open, $total) . ' of total'],
            ['Converted', crm_n($converted), crm_pct($converted, $total) . ' conversion'],
            ['Pipeline', crm_money($pipeline), 'Lead product value'],
        ],
        'columns' => ['Lead No', 'Company', 'Contact', 'Source', 'Owner', 'Status', 'Lead Date'],
        'rows' => array_map(static fn(array $r): array => [
            'LEAD-' . $r['id'], $r['company'], $r['contact'], $r['source'],
            $r['owner'], $r['status'], crm_date($r['date']),
        ], $rows),
        'message' => $total === 0 ? 'Reports are connected; INV11 is waiting for the old CRM lead data import.' : '',
    ];
}

function crm_followup_report(mysqli $db, array $filters): array
{
    if (!crm_report_table($db, 'jos_crm_lead_followup_grid')) {
        return crm_empty_report('The legacy follow-up table is unavailable.');
    }
    $where = crm_report_where($db, 'f.fdate', $filters);
    $done = 'COALESCE(f.followupstatus,0)=1';
    $summary = crm_report_query(
        $db,
        "SELECT COUNT(*) total,
                SUM(DATE(f.fdate)=CURDATE() AND NOT ({$done})) due_today,
                SUM(DATE(f.fdate)>CURDATE() AND DATE(f.fdate)<=DATE_ADD(CURDATE(),INTERVAL 7 DAY) AND NOT ({$done})) upcoming,
                SUM({$done}) completed,
                SUM(DATE(f.fdate)<CURDATE() AND NOT ({$done})) missed
         FROM jos_crm_lead_followup_grid f WHERE {$where}"
    )[0];
    $rows = crm_report_query(
        $db,
        "SELECT f.leadid,
                COALESCE(NULLIF(c.companyname,''),CONCAT('Customer #',a.custid),CONCAT('Lead #',f.leadid)) customer,
                CONCAT('User #',COALESCE(NULLIF(a.created_by,0),NULLIF(f.userid,0),0)) owner,f.fdate,
                COALESCE(NULLIF(f.conversation,''),'-') activity,
                CASE WHEN {$done} THEN 'Completed' WHEN DATE(f.fdate)<CURDATE() THEN 'Missed'
                     WHEN DATE(f.fdate)=CURDATE() THEN 'Due' ELSE 'Upcoming' END outcome
         FROM jos_crm_lead_followup_grid f
         LEFT JOIN jos_crm_leadentry a ON a.id=f.leadid
         LEFT JOIN jos_crm_mcustomers c ON c.id=a.custid
         WHERE {$where} ORDER BY f.fdate DESC,f.id DESC"
    );
    return [
        'kpis' => [
            ['Due Today', crm_n($summary['due_today']), 'Requires action'],
            ['Upcoming', crm_n($summary['upcoming']), 'Next seven days'],
            ['Completed', crm_n($summary['completed']), 'Selected period'],
            ['Missed', crm_n($summary['missed']), 'Escalation required'],
        ],
        'columns' => ['Lead No', 'Customer', 'Assigned To', 'Follow-up Date', 'Activity', 'Outcome'],
        'rows' => array_map(static fn(array $r): array => [
            'LEAD-' . $r['leadid'], $r['customer'], $r['owner'], crm_date($r['fdate']), $r['activity'], $r['outcome'],
        ], $rows),
        'message' => (int)$summary['total'] === 0 ? 'Reports are connected; INV11 is waiting for the old follow-up history import.' : '',
    ];
}

function crm_status_report(mysqli $db, array $filters): array
{
    $where = crm_report_where($db, 'a.date', $filters);
    $valueJoin = crm_report_table($db, 'jos_crm_leadentry_grid')
        ? 'LEFT JOIN (SELECT leadid,SUM(amt) value FROM jos_crm_leadentry_grid GROUP BY leadid) pv ON pv.leadid=a.id' : '';
    $value = $valueJoin ? 'SUM(COALESCE(pv.value,0))' : '0';
    $rows = crm_report_query(
        $db,
        "SELECT COALESCE(NULLIF(st.name,''),CONCAT('Status #',a.leadstatus)) status,
                COUNT(*) leads,{$value} value,ROUND(AVG(DATEDIFF(CURDATE(),DATE(a.date)))) age
         FROM jos_crm_leadentry a LEFT JOIN jos_crm_mleadstatus st ON st.id=a.leadstatus {$valueJoin}
         WHERE {$where} GROUP BY a.leadstatus,st.name ORDER BY leads DESC"
    );
    $total = array_sum(array_column($rows, 'leads'));
    $buckets = ['open' => 0, 'follow' => 0, 'converted' => 0, 'lost' => 0];
    foreach ($rows as $r) {
        $name = strtolower((string)$r['status']);
        $key = preg_match('/convert|won|success/', $name) ? 'converted'
            : (preg_match('/lost|cancel|reject|drop/', $name) ? 'lost'
                : (preg_match('/follow|progress|pending/', $name) ? 'follow' : 'open'));
        $buckets[$key] += (int)$r['leads'];
    }
    return [
        'kpis' => [
            ['Open', crm_n($buckets['open']), crm_pct($buckets['open'], $total)],
            ['Follow-up', crm_n($buckets['follow']), crm_pct($buckets['follow'], $total)],
            ['Converted', crm_n($buckets['converted']), crm_pct($buckets['converted'], $total)],
            ['Lost', crm_n($buckets['lost']), crm_pct($buckets['lost'], $total)],
        ],
        'columns' => ['Status', 'Lead Count', 'Share', 'Pipeline Value', 'Average Age'],
        'rows' => array_map(static fn(array $r): array => [
            $r['status'], crm_n($r['leads']), crm_pct($r['leads'], $total),
            crm_money($r['value']), crm_n($r['age']) . ' days',
        ], $rows),
        'message' => $total === 0 ? 'Status logic is connected; migrated leads are pending.' : '',
    ];
}

function crm_product_report(mysqli $db, array $filters): array
{
    if (!crm_report_table($db, 'jos_crm_leadentry_grid')) {
        return crm_empty_report('The legacy lead product table is unavailable.');
    }
    $where = crm_report_where($db, 'a.date', $filters);
    $productJoin = crm_report_table($db, 'jos_crm_crmproducts')
        ? 'LEFT JOIN jos_crm_crmproducts p ON p.id=g.propid' : '';
    $product = $productJoin ? "COALESCE(NULLIF(p.name,''),CONCAT('Product #',g.propid))" : "CONCAT('Product #',g.propid)";
    $status = crm_status_sql();
    $rows = crm_report_query(
        $db,
        "SELECT {$product} product,COUNT(DISTINCT g.leadid) leads,SUM(COALESCE(g.qty,0)) quantity,
                SUM(COALESCE(g.amt,0)) value,
                COUNT(DISTINCT CASE WHEN {$status} REGEXP 'convert|won|success' THEN g.leadid END) converted
         FROM jos_crm_leadentry_grid g LEFT JOIN jos_crm_leadentry a ON a.id=g.leadid
         LEFT JOIN jos_crm_mleadstatus st ON st.id=a.leadstatus {$productJoin}
         WHERE {$where} GROUP BY g.propid" . ($productJoin ? ',p.name' : '') . ' ORDER BY leads DESC'
    );
    $leads = array_sum(array_column($rows, 'leads'));
    $value = array_sum(array_column($rows, 'value'));
    $converted = array_sum(array_column($rows, 'converted'));
    return [
        'kpis' => [
            ['Products', crm_n(count($rows)), 'Products with enquiries'],
            ['Enquiries', crm_n($leads), 'Selected period'],
            ['Lead Value', crm_money($value), 'Gross pipeline'],
            ['Conversions', crm_n($converted), crm_pct($converted, $leads)],
        ],
        'columns' => ['Product', 'Leads', 'Quantity', 'Lead Value', 'Conversions', 'Conversion %'],
        'rows' => array_map(static fn(array $r): array => [
            $r['product'], crm_n($r['leads']), crm_n($r['quantity']), crm_money($r['value']),
            crm_n($r['converted']), crm_pct($r['converted'], $r['leads']),
        ], $rows),
        'message' => $rows === [] ? 'Product logic is connected; migrated product rows are pending.' : '',
    ];
}

function crm_user_report(mysqli $db, array $filters): array
{
    $where = crm_report_where($db, 'a.date', $filters);
    $status = crm_status_sql();
    $userJoin = crm_report_table($db, 'jos_users')
        ? 'LEFT JOIN jos_users u ON u.id=a.created_by' : '';
    $ownerName = $userJoin
        ? "COALESCE(NULLIF(u.name,''),NULLIF(u.username,''),CONCAT('User #',a.created_by))"
        : "CONCAT('User #',a.created_by)";
    $followJoin = crm_report_table($db, 'jos_crm_lead_followup_grid')
        ? 'LEFT JOIN (SELECT leadid,COUNT(*) count FROM jos_crm_lead_followup_grid GROUP BY leadid) f ON f.leadid=a.id' : '';
    $followups = $followJoin ? 'SUM(COALESCE(f.count,0))' : '0';
    $rows = crm_report_query(
        $db,
        "SELECT a.created_by owner,{$ownerName} owner_name,COUNT(*) assigned,{$followups} followups,
                SUM({$status} REGEXP 'convert|won|success') converted,
                SUM({$status} REGEXP 'lost|cancel|reject|drop') lost
         FROM jos_crm_leadentry a LEFT JOIN jos_crm_mleadstatus st ON st.id=a.leadstatus
         {$userJoin} {$followJoin}
         WHERE {$where} GROUP BY a.created_by" . ($userJoin ? ',u.name,u.username' : '') . "
         ORDER BY converted DESC,assigned DESC"
    );
    $assigned = array_sum(array_column($rows, 'assigned'));
    $converted = array_sum(array_column($rows, 'converted'));
    return [
        'kpis' => [
            ['CRM Users', crm_n(count($rows)), 'Legacy lead owners'],
            ['Leads Assigned', crm_n($assigned), 'Selected period'],
            ['Follow-ups', crm_n(array_sum(array_column($rows, 'followups'))), 'Recorded activities'],
            ['Conversions', crm_n($converted), crm_pct($converted, $assigned) . ' overall'],
        ],
        'columns' => ['User', 'Assigned', 'Follow-ups', 'Converted', 'Lost', 'Conversion %'],
        'rows' => array_map(static fn(array $r): array => [
            $r['owner_name'], crm_n($r['assigned']), crm_n($r['followups']), crm_n($r['converted']),
            crm_n($r['lost']), crm_pct($r['converted'], $r['assigned']),
        ], $rows),
        'message' => $rows === [] ? 'User performance logic is connected; migrated ownership is pending.' : '',
    ];
}

function crm_month_report(mysqli $db, array $filters): array
{
    $where = crm_report_where($db, 'a.date', $filters);
    $status = crm_status_sql();
    $valueJoin = crm_report_table($db, 'jos_crm_leadentry_grid')
        ? 'LEFT JOIN (SELECT leadid,SUM(amt) value FROM jos_crm_leadentry_grid GROUP BY leadid) pv ON pv.leadid=a.id' : '';
    $value = $valueJoin ? 'SUM(COALESCE(pv.value,0))' : '0';
    $rows = crm_report_query(
        $db,
        "SELECT DATE_FORMAT(a.date,'%Y-%m') month_key,DATE_FORMAT(a.date,'%M %Y') month_name,
                COUNT(*) leads,SUM({$status} REGEXP 'convert|won|success') converted,
                SUM({$status} REGEXP 'lost|cancel|reject|drop') lost,{$value} value
         FROM jos_crm_leadentry a LEFT JOIN jos_crm_mleadstatus st ON st.id=a.leadstatus {$valueJoin}
         WHERE {$where} GROUP BY month_key,month_name ORDER BY month_key DESC"
    );
    $current = $rows[0] ?? ['leads' => 0, 'converted' => 0, 'value' => 0];
    $previous = (float)($rows[1]['leads'] ?? 0);
    $growth = $previous > 0 ? ((float)$current['leads'] - $previous) * 100 / $previous : 0;
    return [
        'kpis' => [
            ['Latest Month', crm_n($current['leads']), 'New leads'],
            ['Growth', number_format($growth, 1) . '%', 'Versus previous month'],
            ['Conversions', crm_n($current['converted']), crm_pct($current['converted'], $current['leads'])],
            ['Pipeline Added', crm_money($current['value']), 'Latest month'],
        ],
        'columns' => ['Month', 'New Leads', 'Converted', 'Lost', 'Pipeline Value', 'Conversion %'],
        'rows' => array_map(static fn(array $r): array => [
            $r['month_name'], crm_n($r['leads']), crm_n($r['converted']), crm_n($r['lost']),
            crm_money($r['value']), crm_pct($r['converted'], $r['leads']),
        ], $rows),
        'message' => $rows === [] ? 'Monthly logic is connected; migrated lead history is pending.' : '',
    ];
}

function crm_category_report(mysqli $db, array $filters): array
{
    $where = crm_report_where($db, 'a.date', $filters);
    $join = crm_report_table($db, 'jos_crm_leadcategory')
        ? 'LEFT JOIN jos_crm_leadcategory c ON c.id=a.lcategory' : '';
    $name = $join ? "COALESCE(NULLIF(c.lcategory,''),'Uncategorised')" : "CONCAT('Category #',a.lcategory)";
    $status = crm_status_sql();
    $rows = crm_report_query(
        $db,
        "SELECT {$name} category,COUNT(*) leads,
                SUM(NOT ({$status} REGEXP 'convert|won|success|lost|cancel|reject|drop')) open_leads,
                SUM({$status} REGEXP 'convert|won|success') converted,
                SUM({$status} REGEXP 'lost|cancel|reject|drop') lost
         FROM jos_crm_leadentry a LEFT JOIN jos_crm_mleadstatus st ON st.id=a.leadstatus {$join}
         WHERE {$where} GROUP BY {$name} ORDER BY leads DESC"
    );
    $top = $rows[0] ?? ['category' => '-', 'leads' => 0];
    $bestName = '-';
    $bestRate = 0.0;
    $uncategorised = 0;
    foreach ($rows as $r) {
        $rate = (float)$r['leads'] > 0 ? (float)$r['converted'] * 100 / (float)$r['leads'] : 0;
        if ($rate > $bestRate) {
            $bestRate = $rate;
            $bestName = (string)$r['category'];
        }
        if (strcasecmp((string)$r['category'], 'Uncategorised') === 0) {
            $uncategorised += (int)$r['leads'];
        }
    }
    return [
        'kpis' => [
            ['Categories', crm_n(count($rows)), 'Categories with leads'],
            ['Top Category', $top['category'], crm_n($top['leads']) . ' leads'],
            ['Best Conversion', $bestName, number_format($bestRate, 1) . '%'],
            ['Uncategorised', crm_n($uncategorised), 'Needs review'],
        ],
        'columns' => ['Category', 'Leads', 'Open', 'Converted', 'Lost', 'Conversion %'],
        'rows' => array_map(static fn(array $r): array => [
            $r['category'], crm_n($r['leads']), crm_n($r['open_leads']), crm_n($r['converted']),
            crm_n($r['lost']), crm_pct($r['converted'], $r['leads']),
        ], $rows),
        'message' => $rows === [] ? 'Category logic is connected; migrated leads are pending.' : '',
    ];
}

function crm_lost_report(mysqli $db, array $filters): array
{
    $where = crm_report_where($db, 'a.date', $filters);
    $status = crm_status_sql();
    $valueJoin = crm_report_table($db, 'jos_crm_leadentry_grid')
        ? 'LEFT JOIN (SELECT leadid,SUM(amt) value FROM jos_crm_leadentry_grid GROUP BY leadid) pv ON pv.leadid=a.id' : '';
    $value = $valueJoin ? 'COALESCE(pv.value,0)' : '0';
    $rows = crm_report_query(
        $db,
        "SELECT a.id,COALESCE(NULLIF(c.companyname,''),CONCAT('Customer #',a.custid)) customer,
                CONCAT('User #',a.created_by) owner,a.date,
                COALESCE(NULLIF(st.name,''),'Lost') reason,{$value} value
         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 {$valueJoin}
         WHERE {$where} AND {$status} REGEXP 'lost|cancel|reject|drop'
         ORDER BY a.date DESC,a.id DESC"
    );
    $total = (int)crm_report_value($db, "SELECT COUNT(*) value FROM jos_crm_leadentry a WHERE {$where}");
    $reasons = [];
    foreach ($rows as $r) {
        $reasons[$r['reason']] = ($reasons[$r['reason']] ?? 0) + 1;
    }
    arsort($reasons);
    $top = $reasons === [] ? '-' : (string)array_key_first($reasons);
    return [
        'kpis' => [
            ['Lost Leads', crm_n(count($rows)), crm_pct(count($rows), $total) . ' of total'],
            ['Recoverable', crm_n(count($rows)), 'Review recommended'],
            ['Lost Value', crm_money(array_sum(array_column($rows, 'value'))), 'Lead product value'],
            ['Top Reason', $top, $reasons === [] ? 'No lost leads' : crm_n(reset($reasons)) . ' leads'],
        ],
        'columns' => ['Lead No', 'Customer', 'Owner', 'Lost Date', 'Reason', 'Lead Value', 'Recovery'],
        'rows' => array_map(static fn(array $r): array => [
            'LEAD-' . $r['id'], $r['customer'], $r['owner'], crm_date($r['date']),
            $r['reason'], crm_money($r['value']), 'Review and re-engage',
        ], $rows),
        'message' => $rows === [] ? 'Lost-lead logic is connected; no matching migrated records were found.' : '',
    ];
}

function crm_quotation_summary_report(mysqli $db, array $filters): array
{
    if (!crm_report_table($db, 'jos_crm_quotations')) {
        return crm_empty_report('The legacy quotation table is unavailable.');
    }
    $where = crm_report_where($db, 'q.date', $filters) . ' AND COALESCE(q.cancelled,0)<>1';
    $rows = crm_report_query(
        $db,
        "SELECT DATE_FORMAT(q.date,'%Y-%m') month_key,DATE_FORMAT(q.date,'%M %Y') month_name,
                COUNT(DISTINCT q.id) quotations,COUNT(DISTINCT q.leadid) leads,
                SUM(COALESCE(q.subtotal,0)) subtotal,SUM(COALESCE(q.gst,0)) gst,
                SUM(COALESCE(q.total,0)) total
         FROM jos_crm_quotations q WHERE {$where}
         GROUP BY month_key,month_name ORDER BY month_key DESC"
    );
    $quotationCount = array_sum(array_column($rows, 'quotations'));
    $leadCount = array_sum(array_column($rows, 'leads'));
    $subtotal = array_sum(array_column($rows, 'subtotal'));
    $total = array_sum(array_column($rows, 'total'));
    return [
        'kpis' => [
            ['Quotations', crm_n($quotationCount), 'Non-cancelled quotations'],
            ['Quoted Leads', crm_n($leadCount), 'Monthly distinct-lead total'],
            ['Subtotal', crm_money($subtotal), 'Before GST'],
            ['Quotation Value', crm_money($total), 'Including GST'],
        ],
        'columns' => ['Month', 'Quotation Qty', 'Lead Qty', 'Subtotal', 'GST', 'Total Amount'],
        'rows' => array_map(static fn(array $r): array => [
            $r['month_name'], crm_n($r['quotations']), crm_n($r['leads']),
            crm_money($r['subtotal']), crm_money($r['gst']), crm_money($r['total']),
        ], $rows),
        'message' => $rows === [] ? 'No quotations were found for the selected period.' : '',
    ];
}

function crm_conversion_summary_report(mysqli $db, array $filters): array
{
    if (!crm_report_table($db, 'jos_crm_quotations')) {
        return crm_empty_report('The legacy quotation table is unavailable.');
    }
    $where = crm_report_where($db, 'q.date', $filters);
    $rows = crm_report_query(
        $db,
        "SELECT CASE
                    WHEN COALESCE(q.cancelled,0)=1 THEN 'Cancelled'
                    WHEN COALESCE(q.approved,0)=1 THEN 'Converted / Approved'
                    ELSE 'Open / Awaiting Decision'
                END outcome,
                COUNT(*) quotations,COUNT(DISTINCT q.leadid) leads,
                SUM(COALESCE(q.subtotal,0)) subtotal,SUM(COALESCE(q.gst,0)) gst,
                SUM(COALESCE(q.total,0)) total
         FROM jos_crm_quotations q WHERE {$where}
         GROUP BY outcome ORDER BY FIELD(outcome,'Converted / Approved','Open / Awaiting Decision','Cancelled')"
    );
    $totalQuotes = array_sum(array_column($rows, 'quotations'));
    $approved = 0;
    $cancelled = 0;
    $approvedValue = 0.0;
    foreach ($rows as $row) {
        if ($row['outcome'] === 'Converted / Approved') {
            $approved = (int)$row['quotations'];
            $approvedValue = (float)$row['total'];
        } elseif ($row['outcome'] === 'Cancelled') {
            $cancelled = (int)$row['quotations'];
        }
    }
    return [
        'kpis' => [
            ['Total Quotations', crm_n($totalQuotes), 'Selected period'],
            ['Converted', crm_n($approved), crm_pct($approved, $totalQuotes) . ' conversion'],
            ['Open', crm_n(max(0, $totalQuotes - $approved - $cancelled)), 'Awaiting decision'],
            ['Converted Value', crm_money($approvedValue), 'Approved quotation value'],
        ],
        'columns' => ['Outcome', 'Quotations', 'Distinct Leads', 'Subtotal', 'GST', 'Total Amount', 'Share'],
        'rows' => array_map(static fn(array $r): array => [
            $r['outcome'], crm_n($r['quotations']), crm_n($r['leads']),
            crm_money($r['subtotal']), crm_money($r['gst']), crm_money($r['total']),
            crm_pct($r['quotations'], $totalQuotes),
        ], $rows),
        'message' => $rows === [] ? 'No quotation conversion records were found for the selected period.' : '',
    ];
}

function crm_yearly_summary_report(mysqli $db, array $filters): array
{
    if (!crm_report_table($db, 'jos_crm_quotations')) {
        return crm_empty_report('The legacy quotation table is unavailable.');
    }
    $where = crm_report_where($db, 'q.date', $filters) . ' AND COALESCE(q.cancelled,0)<>1';
    $rows = crm_report_query(
        $db,
        "SELECT YEAR(q.date) report_year,COUNT(DISTINCT q.id) quotations,
                COUNT(DISTINCT q.leadid) leads,SUM(COALESCE(q.subtotal,0)) subtotal,
                SUM(COALESCE(q.gst,0)) gst,SUM(COALESCE(q.total,0)) total
         FROM jos_crm_quotations q WHERE {$where}
         GROUP BY YEAR(q.date) ORDER BY report_year DESC"
    );
    $quotationCount = array_sum(array_column($rows, 'quotations'));
    $total = array_sum(array_column($rows, 'total'));
    $latest = $rows[0] ?? ['report_year' => '-', 'quotations' => 0, 'total' => 0];
    return [
        'kpis' => [
            ['Years', crm_n(count($rows)), 'Years with quotation data'],
            ['Quotations', crm_n($quotationCount), 'Non-cancelled quotations'],
            ['Latest Year', (string)$latest['report_year'], crm_n($latest['quotations']) . ' quotations'],
            ['Total Value', crm_money($total), 'Including GST'],
        ],
        'columns' => ['Year', 'Quotation Qty', 'Distinct Leads', 'Subtotal', 'GST', 'Total Amount'],
        'rows' => array_map(static fn(array $r): array => [
            (string)$r['report_year'], crm_n($r['quotations']), crm_n($r['leads']),
            crm_money($r['subtotal']), crm_money($r['gst']), crm_money($r['total']),
        ], $rows),
        'message' => $rows === [] ? 'No yearly quotation records were found.' : '',
    ];
}

function crm_report_data(string $key): array
{
    try {
        [$db, $filters] = crm_report_context();
        switch ($key) {
            case 'lead-register': return crm_lead_report($db, $filters);
            case 'follow-up': return crm_followup_report($db, $filters);
            case 'status-summary': return crm_status_report($db, $filters);
            case 'product-summary': return crm_product_report($db, $filters);
            case 'user-performance': return crm_user_report($db, $filters);
            case 'monthly-statistics': return crm_month_report($db, $filters);
            case 'category-summary': return crm_category_report($db, $filters);
            case 'cancelled-lost': return crm_lost_report($db, $filters);
            case 'quotation-summary': return crm_quotation_summary_report($db, $filters);
            case 'conversion-summary': return crm_conversion_summary_report($db, $filters);
            case 'yearly-summary': return crm_yearly_summary_report($db, $filters);
        }
        throw new InvalidArgumentException('Unknown CRM report.');
    } catch (Throwable $error) {
        return crm_empty_report($error->getMessage());
    }
}
