<?php
declare(strict_types=1);

require_once dirname(__DIR__) . '/includes/layout.php';
require_once dirname(__DIR__) . '/includes/report-data.php';

function crm_lead_input(string $key): string
{
    return trim((string)($_GET[$key] ?? ''));
}

function crm_lead_int(string $key): int
{
    $value = crm_lead_input($key);
    return ctype_digit($value) ? (int)$value : 0;
}

function crm_lead_date_input(string $key): string
{
    $value = crm_lead_input($key);
    return preg_match('/^\d{4}-\d{2}-\d{2}$/', $value) === 1 ? $value : '';
}

function crm_lead_options(mysqli $db, string $table, string $label, string $order = ''): array
{
    if (!crm_report_table($db, $table)) {
        return [];
    }
    $sql = "SELECT id,{$label} label FROM {$table}";
    $sql .= $order !== '' ? " ORDER BY {$order}" : " ORDER BY {$label}";
    return crm_report_query($db, $sql);
}

function crm_lead_query_url(array $overrides = []): string
{
    $query = array_merge($_GET, $overrides);
    foreach ($query as $key => $value) {
        if ($value === '' || $value === null) {
            unset($query[$key]);
        }
    }
    return '?' . http_build_query($query);
}

function crm_lead_badge(string $status): string
{
    $status = strtolower($status);
    if (preg_match('/convert|won|success/', $status)) {
        return 'green';
    }
    if (preg_match('/lost|cancel|reject|drop|negative/', $status)) {
        return 'red';
    }
    if (preg_match('/follow|pending|hold|progress/', $status)) {
        return 'amber';
    }
    return 'blue';
}

$db = crm_report_db();
$error = '';
$rows = [];
$total = 0;
$page = max(1, crm_lead_int('page'));
$allowedLimits = [25, 50, 100, 200, 0];
$limitValue = crm_lead_input('limit');
$limit = $limitValue === 'all' ? 0 : (int)($limitValue !== '' ? $limitValue : 50);
if (!in_array($limit, $allowedLimits, true)) {
    $limit = 50;
}

$filters = [
    'from' => crm_lead_date_input('from'),
    'to' => crm_lead_date_input('to'),
    'company' => crm_lead_input('company'),
    'mobile' => crm_lead_input('mobile'),
    'lead_no' => crm_lead_int('lead_no'),
    'status' => crm_lead_int('status'),
    'priority' => crm_lead_int('priority'),
    'segment' => crm_lead_input('segment') === '0' ? '0' : crm_lead_int('segment'),
    'state' => crm_lead_input('state') === '0' ? '0' : crm_lead_int('state'),
    'district' => crm_lead_input('district') === '0' ? '0' : crm_lead_int('district'),
    'taluka' => crm_lead_input('taluka') === '0' ? '0' : crm_lead_int('taluka'),
    'locality' => crm_lead_input('locality') === '0' ? '0' : crm_lead_int('locality'),
    'owner' => crm_lead_int('owner'),
    'product' => crm_lead_int('product'),
    'category' => crm_lead_int('category'),
];

$statuses = $priorities = $segments = $states = $districts = $talukas = $localities = [];
$owners = $products = $categories = [];

try {
    if (!$db) {
        throw new RuntimeException('ABOX database connection is unavailable.');
    }

    $statuses = crm_lead_options($db, 'jos_crm_mleadstatus', 'name', 'orderid, name');
    $priorities = crm_lead_options($db, 'jos_crm_priority', 'name');
    $segments = crm_lead_options($db, 'jos_crm_segmentmaster', 'segment');
    $states = crm_lead_options($db, 'jos_crm_mstates', 'name');
    $districts = crm_lead_options($db, 'jos_crm_district', 'name');
    $talukas = crm_lead_options($db, 'jos_crm_mcitys', 'name');
    $localities = crm_lead_options($db, 'jos_crm_locality', 'name');
    $products = crm_lead_options($db, 'jos_crm_crmproducts', 'name');
    $categories = crm_lead_options($db, 'jos_crm_leadcategory', 'lcategory');

    $hasUsers = crm_report_table($db, 'jos_users');
    if ($hasUsers) {
        $owners = crm_report_query($db, "SELECT id,name label FROM jos_users ORDER BY name");
    } else {
        $owners = crm_report_query(
            $db,
            "SELECT DISTINCT created_by id,CONCAT('User #',created_by) label
             FROM jos_crm_leadentry WHERE created_by IS NOT NULL ORDER BY created_by"
        );
    }

    $where = ['1=1'];
    if ($filters['from'] !== '') {
        $where[] = "DATE(a.date)>='" . $db->real_escape_string($filters['from']) . "'";
    }
    if ($filters['to'] !== '') {
        $where[] = "DATE(a.date)<='" . $db->real_escape_string($filters['to']) . "'";
    }
    if ($filters['company'] !== '') {
        $search = $db->real_escape_string($filters['company']);
        $where[] = "(c.companyname LIKE '%{$search}%' OR c.contactperson1 LIKE '%{$search}%')";
    }
    if ($filters['mobile'] !== '') {
        $mobile = $db->real_escape_string($filters['mobile']);
        $where[] = "(c.contactno1 LIKE '%{$mobile}%' OR c.contactno2 LIKE '%{$mobile}%'
                    OR c.officecontact LIKE '%{$mobile}%' OR c.contactnumber LIKE '%{$mobile}%')";
    }
    foreach (['lead_no' => 'a.id', 'status' => 'a.leadstatus', 'priority' => 'a.priority',
              'owner' => 'a.created_by', 'category' => 'a.lcategory'] as $filter => $field) {
        if ((int)$filters[$filter] > 0) {
            $where[] = "{$field}=" . (int)$filters[$filter];
        }
    }
    foreach (['segment' => 'c.segment', 'state' => 'c.stateid', 'district' => 'c.districtid',
              'taluka' => 'c.talukaid', 'locality' => 'c.localityid'] as $filter => $field) {
        if ($filters[$filter] === '0') {
            $where[] = "COALESCE({$field},0)=0";
        } elseif ((int)$filters[$filter] > 0) {
            $where[] = "{$field}=" . (int)$filters[$filter];
        }
    }
    if ($filters['product'] > 0) {
        $where[] = "EXISTS (SELECT 1 FROM jos_crm_leadentry_grid fg
                          WHERE fg.leadid=a.id AND fg.propid=" . (int)$filters['product'] . ')';
    }
    $whereSql = implode(' AND ', $where);

    $total = (int)crm_report_value(
        $db,
        "SELECT COUNT(*) value FROM jos_crm_leadentry a
         LEFT JOIN jos_crm_mcustomers c ON c.id=a.custid WHERE {$whereSql}"
    );
    $pages = $limit > 0 ? max(1, (int)ceil($total / $limit)) : 1;
    $page = min($page, $pages);
    $offset = $limit > 0 ? ($page - 1) * $limit : 0;
    $limitSql = $limit > 0 ? " LIMIT {$offset},{$limit}" : '';

    $userJoin = $hasUsers ? 'LEFT JOIN jos_users owner ON owner.id=a.created_by
                            LEFT JOIN jos_users support ON support.id=a.supportedby' : '';
    $ownerName = $hasUsers ? "COALESCE(NULLIF(owner.name,''),CONCAT('User #',a.created_by))"
                           : "CONCAT('User #',a.created_by)";
    $supportName = $hasUsers ? "COALESCE(NULLIF(support.name,''),'')" : "IF(a.supportedby>0,CONCAT('User #',a.supportedby),'')";

    $rows = crm_report_query(
        $db,
        "SELECT a.id,a.date,a.description,a.followupdate,a.total,a.latitude,a.longitude,
                COALESCE(NULLIF(c.companyname,''),CONCAT('Customer #',a.custid)) company,
                COALESCE(c.address,'') address,COALESCE(c.gstno,'') gstno,
                COALESCE(c.contactperson1,'') contactperson1,COALESCE(c.contactno1,'') contactno1,
                COALESCE(c.contactperson2,'') contactperson2,
                COALESCE(NULLIF(c.contactno2,''),NULLIF(c.contactnumber,''),NULLIF(c.officecontact,''),'') contactno2,
                COALESCE(c.emprange,'') emprange,
                COALESCE(NULLIF(st.name,''),CONCAT('Status #',a.leadstatus)) status,
                COALESCE(NULLIF(pr.name,''),'-') priority,
                COALESCE(NULLIF(nt.name,''),'-') nature,
                COALESCE(NULLIF(seg.segment,''),'-') segment,
                COALESCE(NULLIF(cat.lcategory,''),'-') category,
                COALESCE(NULLIF(ms.name,''),'') state_name,
                COALESCE(NULLIF(md.name,''),'') district_name,
                COALESCE(NULLIF(mt.name,''),'') taluka_name,
                {$ownerName} owner_name,{$supportName} support_name,
                COALESCE((SELECT f.sysdate FROM jos_crm_lead_followup_grid f
                          WHERE f.leadid=a.id ORDER BY f.id DESC LIMIT 1),'') last_activity_date,
                COALESCE((SELECT f.fdate FROM jos_crm_lead_followup_grid f
                          WHERE f.leadid=a.id ORDER BY f.id DESC LIMIT 1),a.followupdate,'') next_followup,
                COALESCE((SELECT f.conversation FROM jos_crm_lead_followup_grid f
                          WHERE f.leadid=a.id ORDER BY f.id DESC LIMIT 1),'') last_conversation,
                (SELECT COUNT(*) FROM jos_crm_quotations q WHERE q.leadid=a.id) quote_count,
                COALESCE((SELECT SUM(q.total) FROM jos_crm_quotations q WHERE q.leadid=a.id),0) quote_total,
                COALESCE((
                    SELECT GROUP_CONCAT(DISTINCT COALESCE(NULLIF(p.name,''),CONCAT('Product #',g.propid))
                                        ORDER BY p.name SEPARATOR ', ')
                    FROM jos_crm_leadentry_grid g
                    LEFT JOIN jos_crm_crmproducts p ON p.id=g.propid WHERE g.leadid=a.id
                ),'') product_names
         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
         LEFT JOIN jos_crm_priority pr ON pr.id=a.priority
         LEFT JOIN jos_crm_mleadtypes nt ON nt.id=a.leadtype
         LEFT JOIN jos_crm_segmentmaster seg ON seg.id=c.segment
         LEFT JOIN jos_crm_leadcategory cat ON cat.id=a.lcategory
         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
         {$userJoin}
         WHERE {$whereSql} ORDER BY a.date DESC,a.id DESC{$limitSql}"
    );
} catch (Throwable $exception) {
    $error = $exception->getMessage();
    $pages = 1;
}

$activeFilters = array_filter($filters, static fn($value): bool => $value !== '' && $value !== 0);
$shownFrom = $total > 0 ? ($limit > 0 ? (($page - 1) * $limit + 1) : 1) : 0;
$shownTo = $limit > 0 ? min($total, $page * $limit) : $total;

crm_page_start('Lead Register', 'operations', 'Complete CRM lead pipeline');
?>
<section class="lead-command-bar">
    <div>
        <strong><?= crm_e(number_format($total)) ?> <?= $total === 1 ? 'lead' : 'leads' ?></strong>
        <span>Showing <?= crm_e(number_format($shownFrom)) ?>–<?= crm_e(number_format($shownTo)) ?> from the migrated CRM register</span>
    </div>
    <div class="inline-actions">
        <a class="button secondary small" href="<?= crm_e(crm_url('reports/lead-map.php')) ?>">
            <i class="fa-solid fa-map-location-dot"></i> Lead Map
        </a>
        <button class="button secondary small" type="button" onclick="window.print()">
            <i class="fa-solid fa-print"></i> Print
        </button>
    </div>
</section>

<details class="card lead-filter-card" <?= $activeFilters !== [] ? 'open' : '' ?>>
    <summary>
        <span><i class="fa-solid fa-sliders"></i> Search &amp; filters</span>
        <small><?= $activeFilters === [] ? 'Filter by customer, pipeline, location and ownership' : count($activeFilters) . ' filters applied' ?></small>
        <i class="fa-solid fa-chevron-down filter-chevron"></i>
    </summary>
    <form class="lead-filter-form" method="get">
        <div class="lead-filter-grid">
            <div class="field"><label for="lead-from">From</label><input class="control" id="lead-from" name="from" type="date" value="<?= crm_e($filters['from']) ?>"></div>
            <div class="field"><label for="lead-to">To</label><input class="control" id="lead-to" name="to" type="date" value="<?= crm_e($filters['to']) ?>"></div>
            <div class="field wide"><label for="lead-company">Company / contact person</label><input class="control" id="lead-company" name="company" type="search" value="<?= crm_e($filters['company']) ?>" placeholder="Search organization or contact"></div>
            <div class="field"><label for="lead-mobile">Mobile</label><input class="control" id="lead-mobile" name="mobile" inputmode="tel" value="<?= crm_e($filters['mobile']) ?>" placeholder="Contact number"></div>
            <div class="field"><label for="lead-no">Lead No.</label><input class="control" id="lead-no" name="lead_no" inputmode="numeric" value="<?= $filters['lead_no'] ?: '' ?>" placeholder="Exact lead number"></div>

            <?php
            $selects = [
                ['status', 'Lead status', $statuses, false],
                ['priority', 'Priority', $priorities, false],
                ['segment', 'Segment', $segments, true],
                ['state', 'State', $states, true],
                ['district', 'District', $districts, true],
                ['taluka', 'Taluka', $talukas, true],
                ['locality', 'Locality', $localities, true],
                ['owner', 'User', $owners, false],
                ['product', 'Product', $products, false],
                ['category', 'Lead category', $categories, false],
            ];
            foreach ($selects as [$name, $label, $options, $notAssigned]):
            ?>
                <div class="field">
                    <label for="lead-<?= crm_e($name) ?>"><?= crm_e($label) ?></label>
                    <select class="control" id="lead-<?= crm_e($name) ?>" name="<?= crm_e($name) ?>">
                        <option value="">All <?= crm_e(strtolower($label)) ?></option>
                        <?php if ($notAssigned): ?><option value="0" <?= $filters[$name] === '0' ? 'selected' : '' ?>>Not assigned</option><?php endif; ?>
                        <?php foreach ($options as $option): ?>
                            <option value="<?= (int)$option['id'] ?>" <?= (string)$filters[$name] === (string)$option['id'] ? 'selected' : '' ?>>
                                <?= crm_e((string)$option['label']) ?>
                            </option>
                        <?php endforeach; ?>
                    </select>
                </div>
            <?php endforeach; ?>
            <div class="field">
                <label for="lead-limit">Records per page</label>
                <select class="control" id="lead-limit" name="limit">
                    <?php foreach ([25, 50, 100, 200] as $size): ?>
                        <option value="<?= $size ?>" <?= $limit === $size ? 'selected' : '' ?>><?= $size ?></option>
                    <?php endforeach; ?>
                    <option value="all" <?= $limit === 0 ? 'selected' : '' ?>>All records</option>
                </select>
            </div>
        </div>
        <div class="lead-filter-actions">
            <a class="button secondary" href="leads.php"><i class="fa-solid fa-rotate-left"></i> Reset</a>
            <button class="button primary" type="submit"><i class="fa-solid fa-magnifying-glass"></i> Apply filters</button>
        </div>
    </form>
</details>

<?php if ($error !== ''): ?>
    <div class="report-notice"><i class="fa-solid fa-circle-exclamation"></i><?= crm_e($error) ?></div>
<?php elseif ($rows === []): ?>
    <section class="card lead-empty">
        <i class="fa-solid fa-filter-circle-xmark"></i>
        <h2>No leads match these filters</h2>
        <p>Reset the filters to return to the complete migrated lead register.</p>
        <a class="button primary" href="leads.php">Show all leads</a>
    </section>
<?php else: ?>
    <section class="lead-register-list">
        <?php foreach ($rows as $index => $lead):
            $location = implode(', ', array_filter([$lead['state_name'], $lead['district_name'], $lead['taluka_name']]));
            $hasCoordinates = (float)$lead['latitude'] !== 0.0 && (float)$lead['longitude'] !== 0.0;
        ?>
            <article class="lead-register-card">
                <header class="lead-card-head">
                    <div class="lead-identity">
                        <span class="lead-sequence"><?= (int)($shownFrom + $index) ?></span>
                        <div>
                            <div class="lead-title-line">
                                <h2><?= crm_e($lead['company']) ?></h2>
                                <span class="badge <?= crm_e(crm_lead_badge($lead['status'])) ?>"><?= crm_e($lead['status']) ?></span>
                            </div>
                            <p>Lead #<?= (int)$lead['id'] ?> · <?= crm_e(crm_date($lead['date'])) ?></p>
                        </div>
                    </div>
                    <div class="lead-head-meta">
                        <span><small>Priority</small><strong><?= crm_e($lead['priority']) ?></strong></span>
                        <span><small>Nature</small><strong><?= crm_e($lead['nature']) ?></strong></span>
                        <span><small>Next follow-up</small><strong><?= crm_e($lead['next_followup'] !== '' ? crm_date($lead['next_followup']) : 'Not set') ?></strong></span>
                    </div>
                </header>

                <div class="lead-card-body">
                    <div class="lead-detail-block lead-organization">
                        <i class="fa-solid fa-building"></i>
                        <div>
                            <small>Organization</small>
                            <strong><?= crm_e($lead['company']) ?></strong>
                            <?php if ($lead['address'] !== ''): ?><p><?= crm_e($lead['address']) ?></p><?php endif; ?>
                            <?php if ($location !== ''): ?><p><?= crm_e($location) ?></p><?php endif; ?>
                            <div class="lead-inline-tags">
                                <?php if ($lead['segment'] !== '-'): ?><span><?= crm_e($lead['segment']) ?></span><?php endif; ?>
                                <?php if ($lead['gstno'] !== ''): ?><span>GST: <?= crm_e($lead['gstno']) ?></span><?php endif; ?>
                                <?php if ($lead['emprange'] !== ''): ?><span>Employees: <?= crm_e((string)$lead['emprange']) ?></span><?php endif; ?>
                            </div>
                        </div>
                    </div>
                    <div class="lead-detail-block">
                        <i class="fa-solid fa-address-card"></i>
                        <div>
                            <small>Contact details</small>
                            <strong><?= crm_e($lead['contactperson1'] !== '' ? $lead['contactperson1'] : 'No contact name') ?></strong>
                            <?php if ($lead['contactno1'] !== ''): ?><a href="tel:<?= crm_e($lead['contactno1']) ?>"><?= crm_e($lead['contactno1']) ?></a><?php endif; ?>
                            <?php if ($lead['contactperson2'] !== ''): ?><p><?= crm_e($lead['contactperson2']) ?><?= $lead['contactno2'] !== '' ? ' · ' . crm_e($lead['contactno2']) : '' ?></p><?php endif; ?>
                        </div>
                    </div>
                    <div class="lead-detail-block">
                        <i class="fa-solid fa-user-check"></i>
                        <div>
                            <small>Owner / supported by</small>
                            <strong><?= crm_e($lead['owner_name']) ?></strong>
                            <p><?= crm_e($lead['support_name'] !== '' ? $lead['support_name'] : 'No support user') ?></p>
                            <p>Category: <?= crm_e($lead['category']) ?></p>
                        </div>
                    </div>
                    <div class="lead-detail-block">
                        <i class="fa-solid fa-file-invoice"></i>
                        <div>
                            <small>Products / quotation</small>
                            <strong><?= crm_e($lead['product_names'] !== '' ? $lead['product_names'] : 'No product assigned') ?></strong>
                            <p><?= (int)$lead['quote_count'] ?> quotation<?= (int)$lead['quote_count'] === 1 ? '' : 's' ?> · ₹<?= crm_e(number_format((float)$lead['quote_total'], 2)) ?></p>
                        </div>
                    </div>
                </div>

                <?php if ($lead['description'] !== '' || $lead['last_conversation'] !== ''): ?>
                    <div class="lead-activity-strip">
                        <i class="fa-solid fa-clock-rotate-left"></i>
                        <div>
                            <small><?= $lead['last_conversation'] !== '' ? 'Latest conversation' : 'Lead description' ?></small>
                            <span><?= crm_e($lead['last_conversation'] !== '' ? $lead['last_conversation'] : $lead['description']) ?></span>
                        </div>
                    </div>
                <?php endif; ?>

                <footer class="lead-card-actions">
                    <a class="button primary small" href="<?= crm_e(crm_url('operations/lead-entry.php?id=' . (int)$lead['id'])) ?>"><i class="fa-solid fa-pen"></i> Edit lead</a>
                    <a class="button secondary small" href="<?= crm_e(crm_url('reports/follow-up.php?lead=' . (int)$lead['id'])) ?>"><i class="fa-solid fa-comments"></i> History</a>
                    <a class="button quote small" href="<?= crm_e(crm_url('operations/quotation.php?lead_id=' . (int)$lead['id'])) ?>"><i class="fa-solid fa-file-circle-plus"></i> Generate Quote</a>
                    <?php if ($hasCoordinates): ?>
                        <a class="button secondary small" href="<?= crm_e(crm_url('reports/lead-map.php?lead=' . (int)$lead['id'])) ?>"><i class="fa-solid fa-location-dot"></i> Map</a>
                    <?php endif; ?>
                    <button class="button secondary small" type="button" onclick="window.print()"><i class="fa-solid fa-print"></i> Print</button>
                    <?php if ((int)$lead['quote_count'] > 0): ?>
                        <a class="lead-quote-state" href="<?= crm_e(crm_url('operations/quotations.php?lead_id=' . (int)$lead['id'])) ?>"><i class="fa-solid fa-circle-check"></i> View <?= (int)$lead['quote_count'] ?> quotation<?= (int)$lead['quote_count'] === 1 ? '' : 's' ?></a>
                    <?php endif; ?>
                </footer>
            </article>
        <?php endforeach; ?>
    </section>

    <?php if ($pages > 1): ?>
        <nav class="lead-pagination" aria-label="Lead register pages">
            <span>Page <?= $page ?> of <?= $pages ?></span>
            <div>
                <?php if ($page > 1): ?><a class="button secondary small" href="<?= crm_e(crm_lead_query_url(['page' => $page - 1])) ?>"><i class="fa-solid fa-chevron-left"></i> Previous</a><?php endif; ?>
                <?php if ($page < $pages): ?><a class="button primary small" href="<?= crm_e(crm_lead_query_url(['page' => $page + 1])) ?>">Next <i class="fa-solid fa-chevron-right"></i></a><?php endif; ?>
            </div>
        </nav>
    <?php endif; ?>
<?php endif; ?>

<?php crm_page_end(); ?>
