<?php
declare(strict_types=1);
require_once dirname(__DIR__) . '/includes/layout.php';
require_once dirname(__DIR__) . '/includes/report-data.php';

$db = crm_report_db();
$leadId = max(0, (int)($_GET['lead_id'] ?? 0));
$search = trim((string)($_GET['search'] ?? ''));
$from = preg_match('/^\d{4}-\d{2}-\d{2}$/', (string)($_GET['from'] ?? '')) ? (string)$_GET['from'] : '';
$to = preg_match('/^\d{4}-\d{2}-\d{2}$/', (string)($_GET['to'] ?? '')) ? (string)$_GET['to'] : '';
$stateId = max(0, (int)($_GET['state'] ?? 0));
$districtId = max(0, (int)($_GET['district'] ?? 0));
$talukaId = max(0, (int)($_GET['taluka'] ?? 0));
$quotes = $states = $districts = $talukas = [];
$lead = [];
$error = '';

try {
    if (!$db) {
        throw new RuntimeException('ABOX database connection is unavailable.');
    }
    if ($leadId > 0) {
        $lead = crm_report_query(
            $db,
            "SELECT a.id,COALESCE(NULLIF(c.companyname,''),CONCAT('Customer #',a.custid)) company
             FROM jos_crm_leadentry a LEFT JOIN jos_crm_mcustomers c ON c.id=a.custid
             WHERE a.id={$leadId} LIMIT 1"
        )[0] ?? [];
        if ($lead === []) {
            throw new RuntimeException('Lead not found.');
        }
    }
    $states = crm_report_query($db, 'SELECT id,name FROM jos_crm_mstates ORDER BY name');
    $districts = crm_report_query($db, 'SELECT id,name FROM jos_crm_district ORDER BY name');
    $talukas = crm_report_query($db, 'SELECT id,name FROM jos_crm_mcitys ORDER BY name');

    $where = ['1=1'];
    if ($leadId > 0) $where[] = "q.leadid={$leadId}";
    if ($from !== '') $where[] = "DATE(q.date)>='" . $db->real_escape_string($from) . "'";
    if ($to !== '') $where[] = "DATE(q.date)<='" . $db->real_escape_string($to) . "'";
    if ($stateId > 0) $where[] = "c.stateid={$stateId}";
    if ($districtId > 0) $where[] = "c.districtid={$districtId}";
    if ($talukaId > 0) $where[] = "c.talukaid={$talukaId}";
    if ($search !== '') {
        $escaped = $db->real_escape_string($search);
        $numeric = ctype_digit($search) ? (int)$search : 0;
        $where[] = "(c.companyname LIKE '%{$escaped}%' OR q.description LIKE '%{$escaped}%'"
            . ($numeric > 0 ? " OR q.id={$numeric} OR q.leadid={$numeric}" : '') . ')';
    }

    $quotes = crm_report_query(
        $db,
        "SELECT q.id,q.leadid,q.date,q.status,q.description,q.subtotal,q.gst,q.total,
                q.approved,q.cancelled,COALESCE(NULLIF(c.companyname,''),CONCAT('Customer #',q.custid)) company,
                COALESCE(NULLIF(ms.name,''),'-') state_name,
                COALESCE(NULLIF(md.name,''),'-') district_name,
                COALESCE(NULLIF(mt.name,''),'-') taluka_name,
                COALESCE(NULLIF(st.name,''),IF(q.status<>'',CONCAT('Status #',q.status),'Not assigned')) status_label,
                COUNT(g.id) item_count
         FROM jos_crm_quotations q
         LEFT JOIN jos_crm_mcustomers c ON c.id=q.custid
         LEFT JOIN jos_crm_mstates ms ON ms.id=c.stateid
         LEFT JOIN jos_crm_district md ON md.id=c.districtid
         LEFT JOIN jos_crm_mcitys mt ON mt.id=c.talukaid
         LEFT JOIN jos_crm_quotation_grid g ON g.qutid=q.id
         LEFT JOIN jos_crm_mleadstatus st ON st.id=CAST(q.status AS UNSIGNED)
         WHERE " . implode(' AND ', $where) . '
         GROUP BY q.id ORDER BY q.date DESC,q.id DESC'
    );
} catch (Throwable $exception) {
    $error = $exception->getMessage();
}

if (($_GET['export'] ?? '') === 'csv' && $error === '') {
    header('Content-Type: text/csv; charset=UTF-8');
    header('Content-Disposition: attachment; filename="quotation-list-' . date('Y-m-d') . '.csv"');
    $stream = fopen('php://output', 'wb');
    if ($stream !== false) {
        fwrite($stream, "\xEF\xBB\xBF");
        fputcsv($stream, ['Quotation No', 'Date', 'Lead No', 'Company', 'State', 'District', 'Taluka', 'Subtotal', 'GST', 'Total', 'Status']);
        foreach ($quotes as $quote) {
            fputcsv($stream, [$quote['id'], $quote['date'], $quote['leadid'], $quote['company'], $quote['state_name'],
                $quote['district_name'], $quote['taluka_name'], $quote['subtotal'], $quote['gst'], $quote['total'], $quote['status_label']]);
        }
        fclose($stream);
    }
    exit;
}

$subtotal = array_sum(array_column($quotes, 'subtotal'));
$gst = array_sum(array_column($quotes, 'gst'));
$total = array_sum(array_column($quotes, 'total'));
$query = $_GET;
unset($query['export']);

crm_page_start('Quotation Register', 'operations', $leadId > 0 ? 'Lead #' . $leadId . ' / Commercial Proposals' : 'Legacy-compatible quotation list');
?>
<section class="lead-command-bar">
    <div>
        <strong><?= crm_e((string)($lead['company'] ?? 'All Quotations')) ?></strong>
        <span><?= count($quotes) ?> record<?= count($quotes) === 1 ? '' : 's' ?><?= $leadId > 0 ? ' linked to Lead #' . $leadId : ' across all CRM leads' ?></span>
    </div>
    <div class="inline-actions">
        <?php if ($leadId > 0): ?>
            <a class="button secondary" href="<?= crm_e(crm_url('operations/leads.php?lead_no=' . $leadId)) ?>">Back to Lead</a>
            <a class="button quote" href="<?= crm_e(crm_url('operations/quotation.php?lead_id=' . $leadId)) ?>"><i class="fa-solid fa-file-circle-plus"></i> Generate Quotation</a>
        <?php else: ?>
            <a class="button secondary" href="<?= crm_e(crm_url('reports/index.php')) ?>"><i class="fa-solid fa-arrow-left"></i> Reports</a>
        <?php endif; ?>
    </div>
</section>
<details class="card lead-filter-card" open>
    <summary><span><i class="fa-solid fa-filter"></i> Quotation Filters</span><small>Company, quotation number, date and region</small><i class="fa-solid fa-chevron-down filter-chevron"></i></summary>
    <form class="lead-filter-form" method="get">
        <?php if ($leadId > 0): ?><input type="hidden" name="lead_id" value="<?= $leadId ?>"><?php endif; ?>
        <div class="lead-filter-grid">
            <div class="field wide"><label>Company / Quotation / Lead</label><input class="control" name="search" value="<?= crm_e($search) ?>" placeholder="Search records"></div>
            <div class="field"><label>From Date</label><input class="control" type="date" name="from" value="<?= crm_e($from) ?>"></div>
            <div class="field"><label>To Date</label><input class="control" type="date" name="to" value="<?= crm_e($to) ?>"></div>
            <div class="field"><label>State</label><select class="control" name="state"><option value="0">All states</option><?php foreach ($states as $option): ?><option value="<?= (int)$option['id'] ?>" <?= $stateId === (int)$option['id'] ? 'selected' : '' ?>><?= crm_e((string)$option['name']) ?></option><?php endforeach; ?></select></div>
            <div class="field"><label>District</label><select class="control" name="district"><option value="0">All districts</option><?php foreach ($districts as $option): ?><option value="<?= (int)$option['id'] ?>" <?= $districtId === (int)$option['id'] ? 'selected' : '' ?>><?= crm_e((string)$option['name']) ?></option><?php endforeach; ?></select></div>
            <div class="field"><label>Taluka</label><select class="control" name="taluka"><option value="0">All talukas</option><?php foreach ($talukas as $option): ?><option value="<?= (int)$option['id'] ?>" <?= $talukaId === (int)$option['id'] ? 'selected' : '' ?>><?= crm_e((string)$option['name']) ?></option><?php endforeach; ?></select></div>
        </div>
        <div class="lead-filter-actions">
            <a class="button secondary" href="<?= crm_e(crm_url('operations/quotations.php' . ($leadId > 0 ? '?lead_id=' . $leadId : ''))) ?>"><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-export"></i> Export CSV</button>
        </div>
    </form>
</details>
<?php if ($error !== ''): ?><div class="report-notice"><i class="fa-solid fa-circle-exclamation"></i><span><?= crm_e($error) ?></span></div><?php endif; ?>
<section class="kpi-grid">
    <article class="card kpi"><span>Quotations</span><strong><?= crm_n(count($quotes)) ?></strong><small>All matching records</small></article>
    <article class="card kpi"><span>Subtotal</span><strong><?= crm_e(crm_money($subtotal)) ?></strong><small>Before GST</small></article>
    <article class="card kpi"><span>GST</span><strong><?= crm_e(crm_money($gst)) ?></strong><small>Tax value</small></article>
    <article class="card kpi"><span>Total Amount</span><strong><?= crm_e(crm_money($total)) ?></strong><small>Quoted value</small></article>
</section>
<section class="card quotation-list-card">
    <div class="card-head"><div class="card-title"><i class="fa-solid fa-file-invoice-dollar"></i><div><h2>Quotation List</h2><p>Legacy columns with modern search and print actions</p></div></div></div>
    <div class="card-body table-wrap">
        <table class="data-table quotation-list-table">
            <thead><tr><th>Quotation Date & No.</th><th>Company / Location</th><th>Lead</th><th>Items</th><th>Subtotal</th><th>GST</th><th>Total Amount</th><th>Status</th><th>Actions</th></tr></thead>
            <tbody>
            <?php if ($quotes === []): ?><tr><td class="empty-table" colspan="9">No quotation records found.</td></tr><?php endif; ?>
            <?php foreach ($quotes as $quote): ?>
                <tr>
                    <td><strong>#<?= (int)$quote['id'] ?></strong><small><?= crm_e(crm_date($quote['date'])) ?></small></td>
                    <td><strong><?= crm_e((string)$quote['company']) ?></strong><small><?= crm_e(implode(', ', array_filter([$quote['state_name'], $quote['district_name'], $quote['taluka_name']], static fn($v): bool => $v !== '-'))) ?></small></td>
                    <td><a href="<?= crm_e(crm_url('operations/leads.php?lead_no=' . (int)$quote['leadid'])) ?>">LEAD-<?= (int)$quote['leadid'] ?></a></td>
                    <td><?= (int)$quote['item_count'] ?></td>
                    <td><?= crm_e(crm_money($quote['subtotal'])) ?></td>
                    <td><?= crm_e(crm_money($quote['gst'])) ?></td>
                    <td><strong><?= crm_e(crm_money($quote['total'])) ?></strong></td>
                    <td><span class="badge <?= (int)$quote['cancelled'] === 1 ? 'red' : ((int)$quote['approved'] === 1 ? 'green' : 'blue') ?>"><?= crm_e((string)$quote['status_label']) ?></span></td>
                    <td><div class="inline-actions"><a class="button secondary small" href="<?= crm_e(crm_url('operations/quotation-view.php?id=' . (int)$quote['id'])) ?>"><i class="fa-solid fa-eye"></i> View / Print</a><a class="button primary small" href="<?= crm_e(crm_url('operations/quotation.php?id=' . (int)$quote['id'])) ?>"><i class="fa-solid fa-pen"></i> Edit</a></div></td>
                </tr>
            <?php endforeach; ?>
            </tbody>
        </table>
    </div>
</section>
<?php crm_page_end(); ?>
