<?php
declare(strict_types=1);

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

function crm_mis_int(string $key, int $default = 0): int
{
    $value = trim((string)($_GET[$key] ?? ''));
    return $value !== '' && ctype_digit($value) ? (int)$value : $default;
}

function crm_mis_date_input(string $key, string $default = ''): string
{
    $value = trim((string)($_GET[$key] ?? $default));
    return preg_match('/^\d{4}-\d{2}-\d{2}$/', $value) === 1 ? $value : $default;
}

function crm_mis_month_bounds(int $year, int $month): array
{
    $start = sprintf('%04d-%02d-01', $year, $month);
    return [$start, date('Y-m-t', strtotime($start))];
}

function crm_mis_year_options(): array
{
    $current = (int)date('Y');
    $options = [];
    for ($year = $current + 1; $year >= 2018; $year--) {
        $options[] = ['value' => (string)$year, 'label' => (string)$year];
    }
    return $options;
}

function crm_mis_month_options(): array
{
    $options = [];
    for ($month = 1; $month <= 12; $month++) {
        $options[] = ['value' => (string)$month, 'label' => date('F', mktime(0, 0, 0, $month, 1))];
    }
    return $options;
}

function crm_mis_options(mysqli $db, string $table, string $label, array $extra = []): array
{
    if (!crm_report_table($db, $table)) {
        return [];
    }
    $fields = array_merge(['id', "{$label} label"], $extra);
    return crm_report_query($db, 'SELECT ' . implode(',', $fields) . " FROM {$table} ORDER BY {$label}");
}

function crm_mis_region_options(mysqli $db): array
{
    return [
        'states' => crm_mis_options($db, 'jos_crm_mstates', 'name'),
        'districts' => crm_mis_options($db, 'jos_crm_district', 'name', ['stateid']),
        'talukas' => crm_mis_options($db, 'jos_crm_mcitys', 'name', ['stateid', 'districtid']),
        'localities' => crm_mis_options($db, 'jos_crm_locality', 'name', ['stateid', 'districtid', 'talukaid']),
    ];
}

function crm_mis_region_filters(): array
{
    return [
        'state' => crm_mis_int('state'),
        'district' => crm_mis_int('district'),
        'taluka' => crm_mis_int('taluka'),
        'locality' => crm_mis_int('locality'),
    ];
}

function crm_mis_geo_where(array $filters, string $customerAlias = 'c'): array
{
    $parts = [];
    foreach (['state' => 'stateid', 'district' => 'districtid', 'taluka' => 'talukaid', 'locality' => 'localityid'] as $key => $column) {
        if ((int)($filters[$key] ?? 0) > 0) {
            $parts[] = "{$customerAlias}.{$column}=" . (int)$filters[$key];
        }
    }
    return $parts;
}

function crm_mis_url(string $path, array $params): string
{
    foreach ($params as $key => $value) {
        if ($value === '' || $value === 0 || $value === null) {
            unset($params[$key]);
        }
    }
    return crm_url($path) . ($params === [] ? '' : '?' . http_build_query($params));
}

function crm_mis_cell($value, ?string $url = null, array $lines = [], ?string $export = null): array
{
    return [
        'value' => (string)$value,
        'url' => $url,
        'lines' => $lines,
        'export' => $export ?? trim(implode(' ', array_merge([(string)$value], $lines))),
    ];
}

function crm_mis_sum(array $rows, string $key): float
{
    return array_sum(array_map(static fn(array $row): float => (float)($row[$key] ?? 0), $rows));
}

function crm_mis_quotation_summary(mysqli $db): array
{
    $year = crm_mis_int('year', (int)date('Y'));
    $geo = crm_mis_region_filters();
    $where = ["YEAR(q.date)={$year}", 'COALESCE(q.cancelled,0)<>1'];
    $where = array_merge($where, crm_mis_geo_where($geo));
    $rowsRaw = crm_report_query(
        $db,
        "SELECT MONTH(q.date) report_month,COUNT(DISTINCT q.leadid) quantity,
                COUNT(DISTINCT q.id) documents,SUM(COALESCE(q.subtotal,0)) subtotal,
                SUM(COALESCE(q.gst,0)) gst,SUM(COALESCE(q.total,0)) total
         FROM jos_crm_quotations q
         LEFT JOIN jos_crm_mcustomers c ON c.id=q.custid
         WHERE " . implode(' AND ', $where) . '
         GROUP BY MONTH(q.date) ORDER BY report_month'
    );
    $map = [];
    foreach ($rowsRaw as $row) {
        $map[(int)$row['report_month']] = $row;
    }
    $rows = [];
    $numeric = [];
    for ($month = 1; $month <= 12; $month++) {
        $item = $map[$month] ?? ['quantity' => 0, 'documents' => 0, 'subtotal' => 0, 'gst' => 0, 'total' => 0];
        [$from, $to] = crm_mis_month_bounds($year, $month);
        $url = crm_mis_url('operations/quotations.php', array_merge(['from' => $from, 'to' => $to], $geo));
        $rows[] = [
            date('F', mktime(0, 0, 0, $month, 1)),
            crm_mis_cell(crm_n($item['quantity']), $url, [crm_n($item['documents']) . ' quotation documents'], (string)$item['quantity']),
            crm_money($item['subtotal']),
            crm_money($item['gst']),
            crm_money($item['total']),
        ];
        $numeric[] = $item;
    }
    $quantity = crm_mis_sum($numeric, 'quantity');
    $documents = crm_mis_sum($numeric, 'documents');
    $subtotal = crm_mis_sum($numeric, 'subtotal');
    $gst = crm_mis_sum($numeric, 'gst');
    $total = crm_mis_sum($numeric, 'total');
    return [
        'filter_type' => 'year_geo',
        'filters' => array_merge(['year' => $year], $geo),
        'options' => array_merge(['years' => crm_mis_year_options()], crm_mis_region_options($db)),
        'kpis' => [
            ['Quotation Qty', crm_n($quantity), crm_n($documents) . ' documents'],
            ['Subtotal', crm_money($subtotal), 'Before GST'],
            ['GST', crm_money($gst), 'Selected year'],
            ['Total Amount', crm_money($total), 'Non-cancelled quotations'],
        ],
        'columns' => ['Month', 'Quotation Qty', 'Subtotal', 'GST', 'Total Amount'],
        'rows' => $rows,
        'totals' => ['Total', crm_n($quantity), crm_money($subtotal), crm_money($gst), crm_money($total)],
        'message' => $documents === 0.0 ? "No non-cancelled quotations were found for {$year}." : '',
    ];
}

function crm_mis_conversion_summary(mysqli $db): array
{
    $year = crm_mis_int('year', (int)date('Y'));
    $geo = crm_mis_region_filters();
    $conversionJoin = crm_report_table($db, 'jos_crm_lead_followup_grid')
        ? "LEFT JOIN (
              SELECT leadid,MIN(DATE(sysdate)) conversion_date
              FROM jos_crm_lead_followup_grid WHERE status=2 GROUP BY leadid
           ) converted ON converted.leadid=a.id"
        : '';
    $conversionDate = $conversionJoin !== ''
        ? "COALESCE(converted.conversion_date,NULLIF(DATE(a.followupdate),'0000-00-00'),DATE(q.date))"
        : "COALESCE(NULLIF(DATE(a.followupdate),'0000-00-00'),DATE(q.date))";
    $where = ["YEAR({$conversionDate})={$year}", 'COALESCE(q.approved,0)=1', 'a.leadstatus=2'];
    $where = array_merge($where, crm_mis_geo_where($geo));
    $rowsRaw = crm_report_query(
        $db,
        "SELECT MONTH({$conversionDate}) report_month,COUNT(DISTINCT a.id) quantity,
                SUM(COALESCE(q.subtotal,0)) subtotal,SUM(COALESCE(q.gst,0)) gst,
                SUM(COALESCE(q.total,0)) total
         FROM jos_crm_quotations q
         INNER JOIN jos_crm_leadentry a ON a.id=q.leadid
         LEFT JOIN jos_crm_mcustomers c ON c.id=a.custid
         {$conversionJoin}
         WHERE " . implode(' AND ', $where) . "
         GROUP BY MONTH({$conversionDate}) ORDER BY report_month"
    );
    $map = [];
    foreach ($rowsRaw as $row) {
        $map[(int)$row['report_month']] = $row;
    }
    $rows = [];
    $numeric = [];
    for ($month = 1; $month <= 12; $month++) {
        $item = $map[$month] ?? ['quantity' => 0, 'subtotal' => 0, 'gst' => 0, 'total' => 0];
        [$from, $to] = crm_mis_month_bounds($year, $month);
        $url = crm_mis_url('operations/leads.php', array_merge(['status' => 2, 'from' => $from, 'to' => $to], $geo));
        $rows[] = [
            date('F', mktime(0, 0, 0, $month, 1)),
            crm_mis_cell(crm_n($item['quantity']), $url, [], (string)$item['quantity']),
            crm_money($item['subtotal']),
            crm_money($item['gst']),
            crm_money($item['total']),
        ];
        $numeric[] = $item;
    }
    $quantity = crm_mis_sum($numeric, 'quantity');
    $subtotal = crm_mis_sum($numeric, 'subtotal');
    $gst = crm_mis_sum($numeric, 'gst');
    $total = crm_mis_sum($numeric, 'total');
    return [
        'filter_type' => 'year_geo',
        'filters' => array_merge(['year' => $year], $geo),
        'options' => array_merge(['years' => crm_mis_year_options()], crm_mis_region_options($db)),
        'kpis' => [
            ['Converted Leads', crm_n($quantity), 'Approved quotations'],
            ['Converted Subtotal', crm_money($subtotal), 'Before GST'],
            ['Converted GST', crm_money($gst), 'Selected year'],
            ['Converted Value', crm_money($total), 'Approved total'],
        ],
        'columns' => ['Month', 'Converted Qty', 'Subtotal', 'GST', 'Total Amount'],
        'rows' => $rows,
        'totals' => ['Total', crm_n($quantity), crm_money($subtotal), crm_money($gst), crm_money($total)],
        'message' => $quantity === 0.0 ? "No approved converted leads were found for {$year}." : '',
    ];
}

function crm_mis_category_summary(mysqli $db): array
{
    $from = crm_mis_date_input('from');
    $to = crm_mis_date_input('to');
    $geo = crm_mis_region_filters();
    $where = ['1=1'];
    if ($from !== '') {
        $where[] = "DATE(a.date)>='" . $db->real_escape_string($from) . "'";
    }
    if ($to !== '') {
        $where[] = "DATE(a.date)<='" . $db->real_escape_string($to) . "'";
    }
    $where = array_merge($where, crm_mis_geo_where($geo));
    $status = crm_status_sql();
    $valueJoin = crm_report_table($db, 'jos_crm_leadentry_grid')
        ? 'LEFT JOIN (SELECT leadid,SUM(COALESCE(amt,0)) value FROM jos_crm_leadentry_grid GROUP BY leadid) pv ON pv.leadid=a.id'
        : '';
    $value = $valueJoin !== '' ? 'SUM(COALESCE(pv.value,0))' : '0';
    $raw = crm_report_query(
        $db,
        "SELECT CASE WHEN cat.id IS NULL OR NULLIF(cat.lcategory,'') IS NULL THEN 0 ELSE cat.id END category_id,
                COALESCE(NULLIF(cat.lcategory,''),'Uncategorised') category,
                COUNT(DISTINCT a.id) leads,
                COUNT(DISTINCT CASE WHEN {$status} REGEXP 'convert|won|success' THEN a.id END) converted,
                COUNT(DISTINCT CASE WHEN {$status} REGEXP 'lost|cancel|reject|drop' THEN a.id END) lost,
                {$value} pipeline
         FROM jos_crm_leadentry a
         LEFT JOIN jos_crm_mcustomers c ON c.id=a.custid
         LEFT JOIN jos_crm_leadcategory cat ON cat.id=a.lcategory
         LEFT JOIN jos_crm_mleadstatus st ON st.id=a.leadstatus
         {$valueJoin}
         WHERE " . implode(' AND ', $where) . "
         GROUP BY COALESCE(NULLIF(cat.lcategory,''),'Uncategorised') ORDER BY leads DESC,category"
    );
    $total = crm_mis_sum($raw, 'leads');
    $converted = crm_mis_sum($raw, 'converted');
    $lost = crm_mis_sum($raw, 'lost');
    $pipeline = crm_mis_sum($raw, 'pipeline');
    $rows = [];
    $chart = [];
    foreach ($raw as $row) {
        $open = max(0, (int)$row['leads'] - (int)$row['converted'] - (int)$row['lost']);
        $url = crm_mis_url('operations/leads.php', array_merge([
            'from' => $from,
            'to' => $to,
            'category' => (int)$row['category_id'],
        ], $geo));
        $rows[] = [
            $row['category'],
            crm_mis_cell(crm_n($row['leads']), $url, [], (string)$row['leads']),
            crm_pct($row['leads'], $total),
            crm_n($open),
            crm_n($row['converted']),
            crm_n($row['lost']),
            crm_money($row['pipeline']),
        ];
        $chart[] = ['label' => $row['category'], 'value' => (int)$row['leads'], 'percent' => (float)$total > 0 ? (float)$row['leads'] * 100 / (float)$total : 0];
    }
    return [
        'filter_type' => 'date_geo',
        'filters' => array_merge(['from' => $from, 'to' => $to], $geo),
        'options' => crm_mis_region_options($db),
        'kpis' => [
            ['Total Leads', crm_n($total), 'All categories'],
            ['Categories', crm_n(count($raw)), 'Categories with leads'],
            ['Converted', crm_n($converted), crm_pct($converted, $total)],
            ['Pipeline Value', crm_money($pipeline), crm_n($lost) . ' cancelled / lost'],
        ],
        'columns' => ['Lead Category', 'Total Leads', 'Share', 'Open', 'Converted', 'Cancelled / Lost', 'Pipeline Value'],
        'rows' => $rows,
        'totals' => ['Total', crm_n($total), '100.0%', crm_n(max(0, $total - $converted - $lost)), crm_n($converted), crm_n($lost), crm_money($pipeline)],
        'chart' => $chart,
        'message' => $total === 0.0 ? 'No category records were found for the selected filters.' : '',
    ];
}

function crm_mis_user_summary(mysqli $db): array
{
    $from = crm_mis_date_input('from', date('Y-m-01'));
    $to = crm_mis_date_input('to', date('Y-m-d'));
    $statusRows = crm_report_query($db, 'SELECT id,name FROM jos_crm_mleadstatus ORDER BY orderid,id');
    $where = [
        "DATE(a.date)>='" . $db->real_escape_string($from) . "'",
        "DATE(a.date)<='" . $db->real_escape_string($to) . "'",
    ];
    $hasUsers = crm_report_table($db, 'jos_users');
    $userJoin = $hasUsers ? 'LEFT JOIN jos_users u ON u.id=a.created_by' : '';
    $userName = $hasUsers ? "COALESCE(NULLIF(u.name,''),CONCAT('User #',a.created_by))" : "CONCAT('User #',a.created_by)";
    $quoteJoin = crm_report_table($db, 'jos_crm_quotations')
        ? "LEFT JOIN (
              SELECT leadid,SUM(COALESCE(subtotal,0)) subtotal,SUM(COALESCE(gst,0)) gst,SUM(COALESCE(total,0)) total
              FROM jos_crm_quotations GROUP BY leadid
           ) qv ON qv.leadid=a.id"
        : '';
    $raw = crm_report_query(
        $db,
        "SELECT a.created_by owner_id,{$userName} owner_name,a.leadstatus status_id,
                COUNT(DISTINCT a.id) leads,
                " . ($quoteJoin !== '' ? 'SUM(COALESCE(qv.subtotal,0))' : '0') . " subtotal,
                " . ($quoteJoin !== '' ? 'SUM(COALESCE(qv.gst,0))' : '0') . " gst,
                " . ($quoteJoin !== '' ? 'SUM(COALESCE(qv.total,0))' : '0') . " total
         FROM jos_crm_leadentry a {$userJoin} {$quoteJoin}
         WHERE " . implode(' AND ', $where) . '
         GROUP BY a.created_by,' . ($hasUsers ? 'u.name,' : '') . 'a.leadstatus
         ORDER BY owner_name,a.leadstatus'
    );
    $users = [];
    $grandLeads = 0;
    $grandValue = 0.0;
    foreach ($raw as $row) {
        $ownerId = (int)$row['owner_id'];
        if (!isset($users[$ownerId])) {
            $users[$ownerId] = ['name' => $row['owner_name'], 'statuses' => [], 'leads' => 0, 'total' => 0.0];
        }
        $users[$ownerId]['statuses'][(int)$row['status_id']] = $row;
        $users[$ownerId]['leads'] += (int)$row['leads'];
        $users[$ownerId]['total'] += (float)$row['total'];
        $grandLeads += (int)$row['leads'];
        $grandValue += (float)$row['total'];
    }
    uasort($users, static fn(array $a, array $b): int => $b['leads'] <=> $a['leads']);
    $columns = ['Sr No', 'User'];
    foreach ($statusRows as $status) {
        $columns[] = (string)$status['name'];
    }
    $columns[] = 'User Total';
    $rows = [];
    $statusTotals = [];
    $serial = 1;
    foreach ($users as $ownerId => $user) {
        $cells = [(string)$serial++, (string)$user['name']];
        foreach ($statusRows as $status) {
            $statusId = (int)$status['id'];
            $item = $user['statuses'][$statusId] ?? ['leads' => 0, 'subtotal' => 0, 'gst' => 0, 'total' => 0];
            $statusTotals[$statusId]['leads'] = ($statusTotals[$statusId]['leads'] ?? 0) + (int)$item['leads'];
            $statusTotals[$statusId]['subtotal'] = ($statusTotals[$statusId]['subtotal'] ?? 0) + (float)$item['subtotal'];
            $statusTotals[$statusId]['gst'] = ($statusTotals[$statusId]['gst'] ?? 0) + (float)$item['gst'];
            $statusTotals[$statusId]['total'] = ($statusTotals[$statusId]['total'] ?? 0) + (float)$item['total'];
            $url = crm_mis_url('operations/leads.php', [
                'owner' => $ownerId,
                'status' => $statusId,
                'from' => $from,
                'to' => $to,
            ]);
            $cells[] = crm_mis_cell(
                crm_n($item['leads']) . ' leads',
                $url,
                [crm_money($item['subtotal']) . ' subtotal', crm_money($item['gst']) . ' GST', crm_money($item['total']) . ' total'],
                $item['leads'] . ' leads; subtotal ' . $item['subtotal'] . '; GST ' . $item['gst'] . '; total ' . $item['total']
            );
        }
        $cells[] = crm_mis_cell(crm_n($user['leads']) . ' leads', null, [crm_money($user['total']) . ' value'], $user['leads'] . ' leads; ' . $user['total'] . ' value');
        $rows[] = $cells;
    }
    $totals = ['', 'Total'];
    foreach ($statusRows as $status) {
        $item = $statusTotals[(int)$status['id']] ?? ['leads' => 0, 'subtotal' => 0, 'gst' => 0, 'total' => 0];
        $totals[] = crm_n($item['leads']) . ' / ' . crm_money($item['total']);
    }
    $totals[] = crm_n($grandLeads) . ' / ' . crm_money($grandValue);
    $best = reset($users);
    return [
        'filter_type' => 'date',
        'filters' => ['from' => $from, 'to' => $to],
        'options' => [],
        'kpis' => [
            ['CRM Users', crm_n(count($users)), 'Users with leads'],
            ['Leads', crm_n($grandLeads), 'Selected date range'],
            ['Quotation Value', crm_money($grandValue), 'All lead statuses'],
            ['Top User', $best['name'] ?? '-', isset($best['leads']) ? crm_n($best['leads']) . ' leads' : 'No records'],
        ],
        'columns' => $columns,
        'rows' => $rows,
        'totals' => $totals,
        'message' => $grandLeads === 0 ? 'No user-wise lead records were found for the selected dates.' : '',
        'wide' => true,
    ];
}

function crm_mis_monthly_statistics(mysqli $db): array
{
    $year = crm_mis_int('year', (int)date('Y'));
    $month = crm_mis_int('month', (int)date('n'));
    if ($month < 1 || $month > 12) {
        $month = (int)date('n');
    }
    $userId = crm_mis_int('user');
    [$from, $to] = crm_mis_month_bounds($year, $month);
    $days = (int)date('t', strtotime($from));
    $userConditionLead = $userId > 0 ? " AND a.created_by={$userId}" : '';
    $userConditionFollow = $userId > 0 ? " AND f.userid={$userId}" : '';

    $hasUsers = crm_report_table($db, 'jos_users');
    $users = $hasUsers
        ? crm_report_query($db, 'SELECT id,name label FROM jos_users ORDER BY name')
        : crm_report_query($db, "SELECT DISTINCT created_by id,CONCAT('User #',created_by) label FROM jos_crm_leadentry ORDER BY created_by");
    $userMap = [];
    foreach ($users as $user) {
        $userMap[(int)$user['id']] = (string)$user['label'];
    }

    $leadRows = crm_report_query(
        $db,
        "SELECT a.created_by user_id,DAY(a.date) report_day,COUNT(DISTINCT a.id) total
         FROM jos_crm_leadentry a
         WHERE DATE(a.date) BETWEEN '{$from}' AND '{$to}' {$userConditionLead}
         GROUP BY a.created_by,DAY(a.date)"
    );
    $followRows = crm_report_table($db, 'jos_crm_lead_followup_grid')
        ? crm_report_query(
            $db,
            "SELECT f.userid user_id,DAY(f.sysdate) report_day,COUNT(DISTINCT f.leadid) total
             FROM jos_crm_lead_followup_grid f
             LEFT JOIN jos_crm_leadentry a ON a.id=f.leadid
             WHERE DATE(f.sysdate) BETWEEN '{$from}' AND '{$to}'
               AND (a.date IS NULL OR DATE(a.date)<>DATE(f.sysdate)) {$userConditionFollow}
             GROUP BY f.userid,DAY(f.sysdate)"
        )
        : [];
    $demoRows = crm_report_table($db, 'jos_crm_lead_followup_grid')
        ? crm_report_query(
            $db,
            "SELECT f.userid user_id,DAY(f.sysdate) report_day,COUNT(*) total
             FROM jos_crm_lead_followup_grid f
             WHERE f.status=6 AND DATE(f.sysdate) BETWEEN '{$from}' AND '{$to}' {$userConditionFollow}
             GROUP BY f.userid,DAY(f.sysdate)"
        )
        : [];

    $matrix = [];
    foreach (['leads' => $leadRows, 'followups' => $followRows, 'demos' => $demoRows] as $metric => $metricRows) {
        foreach ($metricRows as $row) {
            $uid = (int)$row['user_id'];
            $day = (int)$row['report_day'];
            $matrix[$uid][$day][$metric] = (int)$row['total'];
        }
    }
    if ($userId > 0 && !isset($matrix[$userId])) {
        $matrix[$userId] = [];
    }
    uksort($matrix, static function (int $a, int $b) use ($userMap): int {
        return strcasecmp($userMap[$a] ?? "User #{$a}", $userMap[$b] ?? "User #{$b}");
    });

    $columns = ['User'];
    for ($day = 1; $day <= $days; $day++) {
        $columns[] = (string)$day;
    }
    $columns[] = 'Total';
    $rows = [];
    $daily = [];
    $allLeads = $allFollowups = $allDemos = 0;
    foreach ($matrix as $uid => $dates) {
        $cells = [$userMap[$uid] ?? "User #{$uid}"];
        $userLeads = $userFollowups = $userDemos = 0;
        for ($day = 1; $day <= $days; $day++) {
            $metrics = $dates[$day] ?? [];
            $leads = (int)($metrics['leads'] ?? 0);
            $followups = (int)($metrics['followups'] ?? 0);
            $demos = (int)($metrics['demos'] ?? 0);
            $userLeads += $leads;
            $userFollowups += $followups;
            $userDemos += $demos;
            $daily[$day]['leads'] = ($daily[$day]['leads'] ?? 0) + $leads;
            $daily[$day]['followups'] = ($daily[$day]['followups'] ?? 0) + $followups;
            $daily[$day]['demos'] = ($daily[$day]['demos'] ?? 0) + $demos;
            $activityDate = sprintf('%04d-%02d-%02d', $year, $month, $day);
            $cells[] = crm_mis_cell(
                $leads + $followups + $demos > 0 ? "L {$leads}" : '-',
                $leads + $followups + $demos > 0 ? crm_mis_url('operations/leads.php', [
                    'owner' => $uid,
                    'from' => $activityDate,
                    'to' => $activityDate,
                ]) : null,
                ["F {$followups}", "D {$demos}"],
                "Leads {$leads}; Follow-ups {$followups}; Demos {$demos}"
            );
        }
        $allLeads += $userLeads;
        $allFollowups += $userFollowups;
        $allDemos += $userDemos;
        $cells[] = crm_mis_cell("L {$userLeads}", null, ["F {$userFollowups}", "D {$userDemos}"], "Leads {$userLeads}; Follow-ups {$userFollowups}; Demos {$userDemos}");
        $rows[] = $cells;
    }
    $totals = ['Daily Total'];
    for ($day = 1; $day <= $days; $day++) {
        $totals[] = 'L ' . (int)($daily[$day]['leads'] ?? 0)
            . ' / F ' . (int)($daily[$day]['followups'] ?? 0)
            . ' / D ' . (int)($daily[$day]['demos'] ?? 0);
    }
    $totals[] = "L {$allLeads} / F {$allFollowups} / D {$allDemos}";
    $userOptions = [['value' => '0', 'label' => 'All Users']];
    foreach ($users as $user) {
        $userOptions[] = ['value' => (string)$user['id'], 'label' => (string)$user['label']];
    }
    return [
        'filter_type' => 'month_user',
        'filters' => ['year' => $year, 'month' => $month, 'user' => $userId],
        'options' => ['years' => crm_mis_year_options(), 'months' => crm_mis_month_options(), 'users' => $userOptions],
        'kpis' => [
            ['New Leads', crm_n($allLeads), date('F Y', strtotime($from))],
            ['Follow-ups', crm_n($allFollowups), 'Distinct followed-up leads'],
            ['Demos', crm_n($allDemos), 'Legacy status 6 activities'],
            ['Active Users', crm_n(count($matrix)), 'Users with monthly activity'],
        ],
        'columns' => $columns,
        'rows' => $rows,
        'totals' => $totals,
        'message' => $matrix === [] ? 'No monthly user activity was found for the selected month.' : '',
        'wide' => true,
        'legend' => 'L = new leads, F = follow-ups, D = demos',
    ];
}

function crm_mis_yearly_summary(mysqli $db): array
{
    $year = crm_mis_int('year', (int)date('Y'));
    $geo = crm_mis_region_filters();
    $geoWhere = crm_mis_geo_where($geo);
    $geoSql = $geoWhere === [] ? '' : ' AND ' . implode(' AND ', $geoWhere);
    $leadRows = crm_report_query(
        $db,
        "SELECT MONTH(a.date) report_month,COUNT(DISTINCT a.id) leads
         FROM jos_crm_leadentry a LEFT JOIN jos_crm_mcustomers c ON c.id=a.custid
         WHERE YEAR(a.date)={$year}{$geoSql} GROUP BY MONTH(a.date)"
    );
    $followRows = crm_report_table($db, 'jos_crm_lead_followup_grid')
        ? crm_report_query(
            $db,
            "SELECT MONTH(f.sysdate) report_month,COUNT(DISTINCT f.leadid) followups,
                    SUM(f.status=6) demos
             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 YEAR(f.sysdate)={$year}{$geoSql}
             GROUP BY MONTH(f.sysdate)"
        )
        : [];
    $quoteRows = crm_report_query(
        $db,
        "SELECT MONTH(q.date) report_month,COUNT(q.leadid) quote_qty,
                SUM(COALESCE(q.subtotal,0)) quote_subtotal,SUM(COALESCE(q.gst,0)) quote_gst,
                SUM(COALESCE(q.total,0)) quote_total
         FROM jos_crm_quotations q
         LEFT JOIN jos_crm_mcustomers c ON c.id=q.custid
         WHERE YEAR(q.date)={$year} AND COALESCE(q.cancelled,0)<>1{$geoSql}
         GROUP BY MONTH(q.date)"
    );
    $conversionJoin = crm_report_table($db, 'jos_crm_lead_followup_grid')
        ? "LEFT JOIN (
              SELECT leadid,MIN(DATE(sysdate)) conversion_date
              FROM jos_crm_lead_followup_grid WHERE status=2 GROUP BY leadid
           ) converted ON converted.leadid=a.id"
        : '';
    $conversionDate = $conversionJoin !== ''
        ? "COALESCE(converted.conversion_date,NULLIF(DATE(a.followupdate),'0000-00-00'),DATE(q.date))"
        : "COALESCE(NULLIF(DATE(a.followupdate),'0000-00-00'),DATE(q.date))";
    $conversionRows = crm_report_query(
        $db,
        "SELECT MONTH({$conversionDate}) report_month,COUNT(DISTINCT a.id) converted_qty,
                SUM(COALESCE(q.subtotal,0)) converted_subtotal,SUM(COALESCE(q.gst,0)) converted_gst,
                SUM(COALESCE(q.total,0)) converted_total
         FROM jos_crm_leadentry a
         INNER JOIN jos_crm_quotations q ON q.leadid=a.id AND COALESCE(q.approved,0)=1
         LEFT JOIN jos_crm_mcustomers c ON c.id=a.custid
         {$conversionJoin}
         WHERE a.leadstatus=2 AND YEAR({$conversionDate})={$year}{$geoSql}
         GROUP BY MONTH({$conversionDate})"
    );
    $maps = [];
    foreach (['lead' => $leadRows, 'follow' => $followRows, 'quote' => $quoteRows, 'conversion' => $conversionRows] as $type => $items) {
        foreach ($items as $item) {
            $maps[$type][(int)$item['report_month']] = $item;
        }
    }
    $rows = [];
    $totals = [
        'leads' => 0, 'followups' => 0, 'demos' => 0,
        'quote_qty' => 0, 'quote_subtotal' => 0, 'quote_gst' => 0, 'quote_total' => 0,
        'converted_qty' => 0, 'converted_subtotal' => 0, 'converted_gst' => 0, 'converted_total' => 0,
    ];
    for ($month = 1; $month <= 12; $month++) {
        $item = array_merge(
            ['leads' => 0],
            $maps['lead'][$month] ?? [],
            ['followups' => 0, 'demos' => 0],
            $maps['follow'][$month] ?? [],
            ['quote_qty' => 0, 'quote_subtotal' => 0, 'quote_gst' => 0, 'quote_total' => 0],
            $maps['quote'][$month] ?? [],
            ['converted_qty' => 0, 'converted_subtotal' => 0, 'converted_gst' => 0, 'converted_total' => 0],
            $maps['conversion'][$month] ?? []
        );
        foreach ($totals as $key => $value) {
            $totals[$key] += (float)($item[$key] ?? 0);
        }
        [$from, $to] = crm_mis_month_bounds($year, $month);
        $leadUrl = crm_mis_url('operations/leads.php', array_merge(['from' => $from, 'to' => $to], $geo));
        $quoteUrl = crm_mis_url('operations/quotations.php', array_merge(['from' => $from, 'to' => $to], $geo));
        $rows[] = [
            date('F', mktime(0, 0, 0, $month, 1)),
            crm_mis_cell(crm_n($item['leads']), $leadUrl, [], (string)$item['leads']),
            crm_n($item['followups']),
            crm_n($item['demos']),
            crm_mis_cell(crm_n($item['quote_qty']), $quoteUrl, [], (string)$item['quote_qty']),
            crm_money($item['quote_subtotal']),
            crm_money($item['quote_gst']),
            crm_money($item['quote_total']),
            crm_n($item['converted_qty']),
            crm_money($item['converted_subtotal']),
            crm_money($item['converted_gst']),
            crm_money($item['converted_total']),
        ];
    }
    $columns = ['Month', 'Leads', 'Follow-up', 'Demos', 'Qty', 'Subtotal', 'GST', 'Total', 'Qty', 'Subtotal', 'GST', 'Total'];
    return [
        'filter_type' => 'year_geo',
        'filters' => array_merge(['year' => $year], $geo),
        'options' => array_merge(['years' => crm_mis_year_options()], crm_mis_region_options($db)),
        'kpis' => [
            ['Leads', crm_n($totals['leads']), "Created in {$year}"],
            ['Follow-ups', crm_n($totals['followups']), crm_n($totals['demos']) . ' demos'],
            ['Quotation Value', crm_money($totals['quote_total']), crm_n($totals['quote_qty']) . ' quotation rows'],
            ['Converted Value', crm_money($totals['converted_total']), crm_n($totals['converted_qty']) . ' converted leads'],
        ],
        'column_groups' => [
            ['label' => '', 'span' => 4],
            ['label' => 'Quotations', 'span' => 4],
            ['label' => 'Conversion', 'span' => 4],
        ],
        'columns' => $columns,
        'rows' => $rows,
        'totals' => [
            'Total', crm_n($totals['leads']), crm_n($totals['followups']), crm_n($totals['demos']),
            crm_n($totals['quote_qty']), crm_money($totals['quote_subtotal']), crm_money($totals['quote_gst']), crm_money($totals['quote_total']),
            crm_n($totals['converted_qty']), crm_money($totals['converted_subtotal']), crm_money($totals['converted_gst']), crm_money($totals['converted_total']),
        ],
        'message' => $totals['leads'] + $totals['quote_qty'] === 0.0 ? "No yearly CRM activity was found for {$year}." : '',
        'wide' => true,
        'legend' => 'Legacy yearly matrix: leads, follow-ups, demos, quotations and approved conversions by month.',
    ];
}

function crm_mis_data(string $key): 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.");
        }
    }
    switch ($key) {
        case 'quotation-summary':
            return crm_mis_quotation_summary($db);
        case 'conversion-summary':
            return crm_mis_conversion_summary($db);
        case 'category-summary':
            return crm_mis_category_summary($db);
        case 'user-summary':
            return crm_mis_user_summary($db);
        case 'monthly-statistics':
            return crm_mis_monthly_statistics($db);
        case 'yearly-summary':
            return crm_mis_yearly_summary($db);
        default:
            throw new InvalidArgumentException('Unknown MIS report.');
    }
}

function crm_mis_select(string $name, string $label, $value, array $options, string $id = '', array $data = []): void
{
    $id = $id !== '' ? $id : 'mis-' . $name;
    ?>
    <div class="field">
        <label for="<?= crm_e($id) ?>"><?= crm_e($label) ?></label>
        <select class="control" id="<?= crm_e($id) ?>" name="<?= crm_e($name) ?>">
            <?php foreach ($options as $option): ?>
                <?php
                $optionValue = (string)($option['value'] ?? $option['id'] ?? '');
                $optionLabel = (string)($option['label'] ?? $option['name'] ?? $optionValue);
                ?>
                <option value="<?= crm_e($optionValue) ?>"
                    <?php foreach ($data as $attribute => $source): ?>
                        data-<?= crm_e($attribute) ?>="<?= (int)($option[$source] ?? 0) ?>"
                    <?php endforeach; ?>
                    <?= (string)$value === $optionValue ? 'selected' : '' ?>><?= crm_e($optionLabel) ?></option>
            <?php endforeach; ?>
        </select>
    </div>
    <?php
}

function crm_mis_render_filters(array $data): void
{
    $type = (string)$data['filter_type'];
    $filters = $data['filters'];
    $options = $data['options'];
    ?>
    <details class="card lead-filter-card mis-filter-card" open>
        <summary><span><i class="fa-solid fa-filter"></i> MIS Report Filters</span><small>Legacy filters rebuilt for the new console</small><i class="fa-solid fa-chevron-down filter-chevron"></i></summary>
        <form class="lead-filter-form" method="get">
            <div class="lead-filter-grid mis-filter-grid">
                <?php if (in_array($type, ['year_geo', 'month_user'], true)): ?>
                    <?php crm_mis_select('year', 'Year', $filters['year'], $options['years']); ?>
                <?php endif; ?>
                <?php if ($type === 'month_user'): ?>
                    <?php crm_mis_select('month', 'Month', $filters['month'], $options['months']); ?>
                    <?php crm_mis_select('user', 'CRM User', $filters['user'], $options['users']); ?>
                <?php endif; ?>
                <?php if (in_array($type, ['date', 'date_geo'], true)): ?>
                    <div class="field"><label for="mis-from">From Date</label><input class="control" id="mis-from" type="date" name="from" value="<?= crm_e($filters['from']) ?>"></div>
                    <div class="field"><label for="mis-to">To Date</label><input class="control" id="mis-to" type="date" name="to" value="<?= crm_e($filters['to']) ?>"></div>
                <?php endif; ?>
                <?php if (in_array($type, ['year_geo', 'date_geo'], true)): ?>
                    <?php
                    crm_mis_select('state', 'State', $filters['state'], array_merge([['id' => 0, 'label' => 'All States']], $options['states']), 'mis-state');
                    crm_mis_select('district', 'District', $filters['district'], array_merge([['id' => 0, 'label' => 'All Districts', 'stateid' => 0]], $options['districts']), 'mis-district', ['state' => 'stateid']);
                    crm_mis_select('taluka', 'Taluka', $filters['taluka'], array_merge([['id' => 0, 'label' => 'All Talukas', 'stateid' => 0, 'districtid' => 0]], $options['talukas']), 'mis-taluka', ['state' => 'stateid', 'district' => 'districtid']);
                    crm_mis_select('locality', 'Locality', $filters['locality'], array_merge([['id' => 0, 'label' => 'All Localities', 'stateid' => 0, 'districtid' => 0, 'talukaid' => 0]], $options['localities']), 'mis-locality', ['state' => 'stateid', 'district' => 'districtid', 'taluka' => 'talukaid']);
                    ?>
                <?php endif; ?>
            </div>
            <div class="lead-filter-actions">
                <a class="button secondary" href="<?= crm_e(crm_url('reports/' . basename($_SERVER['PHP_SELF'] ?? 'index.php'))) ?>"><i class="fa-solid fa-rotate"></i> Reset</a>
                <button class="button primary" type="submit"><i class="fa-solid fa-magnifying-glass"></i> Apply Filters</button>
                <button class="button secondary" type="submit" name="export" value="csv"><i class="fa-solid fa-file-csv"></i> Export CSV</button>
                <button class="button mis-print-button" type="button" onclick="window.print()"><i class="fa-solid fa-print"></i> Print</button>
            </div>
        </form>
    </details>
    <?php
}

function crm_mis_export_cell($cell): string
{
    if (is_array($cell)) {
        return (string)($cell['export'] ?? $cell['value'] ?? '');
    }
    return (string)$cell;
}

function crm_mis_report_page(string $key, string $title, string $description): void
{
    require_login();
    require_acl('can_view', 'modules/crm/reports/index.php');
    try {
        $data = crm_mis_data($key);
        $error = '';
    } catch (Throwable $exception) {
        $data = [
            'filter_type' => 'date',
            'filters' => ['from' => '', 'to' => ''],
            'options' => [],
            'kpis' => [['Records', '0', 'Live database']],
            'columns' => ['Status'],
            'rows' => [],
            'totals' => [],
            'message' => $exception->getMessage(),
        ];
        $error = $exception->getMessage();
    }

    if (($_GET['export'] ?? '') === 'csv' && $error === '') {
        header('Content-Type: text/csv; charset=UTF-8');
        header('Content-Disposition: attachment; filename="' . $key . '-' . date('Y-m-d') . '.csv"');
        $stream = fopen('php://output', 'wb');
        if ($stream !== false) {
            fwrite($stream, "\xEF\xBB\xBF");
            fputcsv($stream, $data['columns']);
            foreach ($data['rows'] as $row) {
                fputcsv($stream, array_map('crm_mis_export_cell', $row));
            }
            if (($data['totals'] ?? []) !== []) {
                fputcsv($stream, array_map('crm_mis_export_cell', $data['totals']));
            }
            fclose($stream);
        }
        exit;
    }

    crm_page_start($title, 'reports', $description);
    crm_mis_render_filters($data);
    if ((string)($data['message'] ?? '') !== ''): ?>
        <div class="report-notice"><i class="fa-solid fa-circle-info"></i><span><?= crm_e((string)$data['message']) ?></span></div>
    <?php endif; ?>
    <section class="kpi-grid">
        <?php foreach ($data['kpis'] as [$label, $value, $note]): ?>
            <article class="card kpi"><span><?= crm_e((string)$label) ?></span><strong><?= crm_e((string)$value) ?></strong><small><?= crm_e((string)$note) ?></small></article>
        <?php endforeach; ?>
    </section>
    <?php if (($data['chart'] ?? []) !== []): ?>
        <?php $maxChart = max(array_column($data['chart'], 'value')) ?: 1; ?>
        <section class="card mis-chart-card">
            <div class="card-head"><div class="card-title"><i class="fa-solid fa-chart-column"></i><div><h2>Lead Category Distribution</h2><p>Legacy category chart rebuilt with live records</p></div></div></div>
            <div class="card-body mis-bar-list">
                <?php foreach (array_slice($data['chart'], 0, 12) as $bar): ?>
                    <div class="mis-bar-row">
                        <span><?= crm_e((string)$bar['label']) ?></span>
                        <div><i style="width:<?= number_format((float)$bar['value'] * 100 / $maxChart, 2, '.', '') ?>%"></i></div>
                        <strong><?= crm_n($bar['value']) ?></strong>
                    </div>
                <?php endforeach; ?>
            </div>
        </section>
    <?php endif; ?>
    <section class="card mis-report-card">
        <div class="card-head">
            <div class="card-title"><i class="fa-solid fa-table-list"></i><div><h2><?= crm_e($title) ?></h2><p><?= crm_e($description) ?></p></div></div>
            <?php if (!empty($data['legend'])): ?><span class="mis-legend"><?= crm_e((string)$data['legend']) ?></span><?php endif; ?>
        </div>
        <div class="card-body table-wrap <?= !empty($data['wide']) ? 'mis-wide-table' : '' ?>">
            <table class="data-table mis-report-table">
                <thead>
                <?php if (($data['column_groups'] ?? []) !== []): ?>
                    <tr class="mis-group-head"><?php foreach ($data['column_groups'] as $group): ?><th colspan="<?= (int)$group['span'] ?>"><?= crm_e((string)$group['label']) ?></th><?php endforeach; ?></tr>
                <?php endif; ?>
                <tr><?php foreach ($data['columns'] as $column): ?><th><?= crm_e((string)$column) ?></th><?php endforeach; ?></tr>
                </thead>
                <tbody>
                <?php if ($data['rows'] === []): ?>
                    <tr><td class="empty-table" colspan="<?= count($data['columns']) ?>">No records found for the selected filters.</td></tr>
                <?php else: ?>
                    <?php foreach ($data['rows'] as $row): ?>
                        <tr>
                            <?php foreach ($row as $cell): ?>
                                <td>
                                    <?php if (is_array($cell)): ?>
                                        <?php if (!empty($cell['url'])): ?><a class="mis-cell-link" href="<?= crm_e((string)$cell['url']) ?>"><?php endif; ?>
                                        <strong><?= crm_e((string)$cell['value']) ?></strong>
                                        <?php foreach (($cell['lines'] ?? []) as $line): ?><small><?= crm_e((string)$line) ?></small><?php endforeach; ?>
                                        <?php if (!empty($cell['url'])): ?></a><?php endif; ?>
                                    <?php else: ?>
                                        <?= crm_e((string)$cell) ?>
                                    <?php endif; ?>
                                </td>
                            <?php endforeach; ?>
                        </tr>
                    <?php endforeach; ?>
                <?php endif; ?>
                </tbody>
                <?php if (($data['totals'] ?? []) !== []): ?>
                    <tfoot><tr><?php foreach ($data['totals'] as $cell): ?><th><?= crm_e(crm_mis_export_cell($cell)) ?></th><?php endforeach; ?></tr></tfoot>
                <?php endif; ?>
            </table>
        </div>
    </section>
    <script>
    (function () {
        const state = document.getElementById('mis-state');
        const district = document.getElementById('mis-district');
        const taluka = document.getElementById('mis-taluka');
        const locality = document.getElementById('mis-locality');
        if (!state || !district || !taluka || !locality) return;
        const filter = (select, predicate) => {
            Array.from(select.options).forEach((option, index) => {
                option.hidden = index > 0 && !predicate(option);
                option.disabled = option.hidden;
            });
            if (select.selectedOptions[0]?.disabled) select.value = '0';
        };
        const refresh = () => {
            filter(district, option => !state.value || state.value === '0' || option.dataset.state === state.value);
            filter(taluka, option =>
                (!state.value || state.value === '0' || option.dataset.state === state.value) &&
                (!district.value || district.value === '0' || option.dataset.district === district.value)
            );
            filter(locality, option =>
                (!state.value || state.value === '0' || option.dataset.state === state.value) &&
                (!district.value || district.value === '0' || option.dataset.district === district.value) &&
                (!taluka.value || taluka.value === '0' || option.dataset.taluka === taluka.value)
            );
        };
        state.addEventListener('change', () => { district.value = '0'; taluka.value = '0'; locality.value = '0'; refresh(); });
        district.addEventListener('change', () => { taluka.value = '0'; locality.value = '0'; refresh(); });
        taluka.addEventListener('change', () => { locality.value = '0'; refresh(); });
        refresh();
    })();
    </script>
    <?php
    crm_page_end();
}
