<?php
require_once 'config/db.php';
require_once 'includes/header.php';

// ------- Filters -------
$f_from     = $_GET['date_from']    ?? date('Y-m-01');
$f_to       = $_GET['date_to']      ?? date('Y-m-d');
$f_stage    = $_GET['stage_id']     ?? '';
$f_source   = $_GET['source_id']    ?? '';
$f_project  = $_GET['project_type'] ?? '';

$all_stages  = $pdo->query("SELECT * FROM lead_statuses ORDER BY id ASC")->fetchAll();
$all_sources = $pdo->query("SELECT * FROM lead_sources ORDER BY name ASC")->fetchAll();
$project_types = $pdo->query("SELECT name FROM lead_services WHERE status='active' ORDER BY name ASC")->fetchAll(PDO::FETCH_COLUMN);

// ------- Base WHERE (for most queries) -------
$base_where  = "l.is_junk = 0 AND DATE(l.created_at) BETWEEN ? AND ?";
$base_params = [$f_from, $f_to];
if ($f_stage  != '') { $base_where .= " AND l.status_id = ?";    $base_params[] = (int)$f_stage;  }
if ($f_source != '') { $base_where .= " AND l.source_id = ?";    $base_params[] = (int)$f_source; }
if ($f_project != '') { $base_where .= " AND l.project_type = ?"; $base_params[] = $f_project;   }

function run($pdo, $sql, $params = []) {
    $s = $pdo->prepare($sql);
    $s->execute($params);
    return $s->fetchAll();
}
function runVal($pdo, $sql, $params = []) {
    $s = $pdo->prepare($sql);
    $s->execute($params);
    return $s->fetchColumn();
}

// ------- 1. Summary -------
$total      = runVal($pdo, "SELECT COUNT(*) FROM leads l WHERE $base_where", $base_params);
$converted  = runVal($pdo, "SELECT COUNT(*) FROM leads l LEFT JOIN lead_statuses ls ON l.status_id=ls.id WHERE $base_where AND ls.name='Converted'", $base_params);
$lost       = runVal($pdo, "SELECT COUNT(*) FROM leads l LEFT JOIN lead_statuses ls ON l.status_id=ls.id WHERE $base_where AND ls.name='Lost'", $base_params);
$junk_total = runVal($pdo, "SELECT COUNT(*) FROM leads WHERE is_junk=1");
$active     = $total - $converted - $lost;

// ------- 2. By Pipeline Stage -------
$by_stage = run($pdo, "
    SELECT ls.name, ls.color_code, COUNT(l.id) as cnt
    FROM leads l LEFT JOIN lead_statuses ls ON l.status_id=ls.id
    WHERE $base_where
    GROUP BY l.status_id, ls.name, ls.color_code ORDER BY cnt DESC
", $base_params);

// ------- 3. By Project Type -------
$by_project = run($pdo, "
    SELECT IFNULL(l.project_type,'Not Set') as project_type, COUNT(*) as cnt
    FROM leads l WHERE $base_where
    GROUP BY l.project_type ORDER BY cnt DESC
", $base_params);

// ------- 4. By Source -------
$by_source = run($pdo, "
    SELECT IFNULL(src.name,'Unknown') as name, COUNT(l.id) as cnt
    FROM leads l LEFT JOIN lead_sources src ON l.source_id=src.id
    WHERE $base_where GROUP BY l.source_id, src.name ORDER BY cnt DESC
", $base_params);

// ------- 5. Conversion Rate (monthly breakdown) -------
$monthly = run($pdo, "
    SELECT DATE_FORMAT(l.created_at,'%b %Y') as month,
           COUNT(*) as total,
           SUM(CASE WHEN ls.name='Converted' THEN 1 ELSE 0 END) as converted
    FROM leads l LEFT JOIN lead_statuses ls ON l.status_id=ls.id
    WHERE l.is_junk=0 AND DATE(l.created_at) BETWEEN ? AND ?
    GROUP BY DATE_FORMAT(l.created_at,'%Y-%m') ORDER BY MIN(l.created_at) ASC
", [$f_from, $f_to]);

// ------- 6. Follow-up Summary -------
$fup = run($pdo, "
    SELECT
        SUM(f.status='pending' AND f.followup_date >= NOW()) as upcoming,
        SUM(f.status='pending' AND f.followup_date  < NOW()) as overdue,
        SUM(f.status='completed')                             as done,
        SUM(f.status='cancelled')                            as cancelled
    FROM lead_followups f
    JOIN leads l ON f.lead_id=l.id
    WHERE l.is_junk=0 AND DATE(f.followup_date) BETWEEN ? AND ?
", [$f_from, $f_to]);
$fup = $fup[0];

// ------- 7. Daily New Leads -------
$daily = run($pdo, "
    SELECT DATE(l.created_at) as day, COUNT(*) as cnt
    FROM leads l WHERE l.is_junk=0 AND DATE(l.created_at) BETWEEN ? AND ?
    GROUP BY DATE(l.created_at) ORDER BY day ASC
", [$f_from, $f_to]);
$daily_map = array_column($daily, 'cnt', 'day');
$max_day   = max(array_values($daily_map) ?: [1]);

$total_src = max(array_sum(array_column($by_source, 'cnt')), 1);
$total_prj = max(array_sum(array_column($by_project, 'cnt')), 1);
$total_stg = max(array_sum(array_column($by_stage, 'cnt')), 1);
?>
<div class="dashboard-content">

    <!-- Banner -->
    <div class="welcome-banner" style="margin-bottom:20px; padding:20px;">
        <h2><i class="fa-solid fa-chart-bar"></i> Lead Reports</h2>
        <p>Comprehensive analytics to track your sales pipeline performance.</p>
    </div>

    <!-- Filters -->
    <div style="background:#fff; padding:20px; border-radius:12px; border:1px solid var(--border); margin-bottom:25px;">
        <form method="GET" style="display:flex; gap:12px; flex-wrap:wrap; align-items:flex-end;">
            <div style="flex:1; min-width:130px;">
                <label style="display:block; margin-bottom:5px; font-weight:600; font-size:0.82rem; color:#64748b;">FROM</label>
                <input type="date" name="date_from" value="<?= $f_from ?>" style="width:100%; padding:9px; border:1px solid var(--border); border-radius:6px; font-family:inherit;">
            </div>
            <div style="flex:1; min-width:130px;">
                <label style="display:block; margin-bottom:5px; font-weight:600; font-size:0.82rem; color:#64748b;">TO</label>
                <input type="date" name="date_to" value="<?= $f_to ?>" style="width:100%; padding:9px; border:1px solid var(--border); border-radius:6px; font-family:inherit;">
            </div>
            <div style="flex:1; min-width:150px;">
                <label style="display:block; margin-bottom:5px; font-weight:600; font-size:0.82rem; color:#64748b;">PIPELINE STAGE</label>
                <select name="stage_id" style="width:100%; padding:9px; border:1px solid var(--border); border-radius:6px; background:#fff;">
                    <option value="">All Stages</option>
                    <?php foreach($all_stages as $s): ?><option value="<?= $s['id'] ?>" <?= $f_stage==$s['id']?'selected':'' ?>><?= htmlspecialchars($s['name']) ?></option><?php endforeach; ?>
                </select>
            </div>
            <div style="flex:1; min-width:150px;">
                <label style="display:block; margin-bottom:5px; font-weight:600; font-size:0.82rem; color:#64748b;">PROJECT TYPE</label>
                <select name="project_type" style="width:100%; padding:9px; border:1px solid var(--border); border-radius:6px; background:#fff;">
                    <option value="">All Types</option>
                    <?php foreach($project_types as $pt): ?><option value="<?= $pt ?>" <?= $f_project===$pt?'selected':'' ?>><?= $pt ?></option><?php endforeach; ?>
                </select>
            </div>
            <div style="flex:1; min-width:140px;">
                <label style="display:block; margin-bottom:5px; font-weight:600; font-size:0.82rem; color:#64748b;">SOURCE</label>
                <select name="source_id" style="width:100%; padding:9px; border:1px solid var(--border); border-radius:6px; background:#fff;">
                    <option value="">All Sources</option>
                    <?php foreach($all_sources as $s): ?><option value="<?= $s['id'] ?>" <?= $f_source==$s['id']?'selected':'' ?>><?= htmlspecialchars($s['name']) ?></option><?php endforeach; ?>
                </select>
            </div>
            <div style="display:flex; gap:8px; align-items:flex-end;">
                <button type="submit" style="padding:9px 18px; background:var(--primary); color:#fff; border:none; border-radius:6px; font-weight:600; cursor:pointer; white-space:nowrap;"><i class="fa-solid fa-filter"></i> Apply</button>
                <a href="lead_reports.php" style="padding:9px 14px; background:#e2e8f0; color:var(--dark); text-decoration:none; border-radius:6px; font-weight:600;">Reset</a>
            </div>
        </form>
        <div style="margin-top:10px; font-size:0.82rem; color:#94a3b8;">
            <i class="fa-solid fa-calendar"></i> Showing data from <strong><?= date('d M Y', strtotime($f_from)) ?></strong> to <strong><?= date('d M Y', strtotime($f_to)) ?></strong>
        </div>
    </div>

    <!-- 1. KPI Summary Cards -->
    <div style="display:grid; grid-template-columns:repeat(auto-fit,minmax(140px,1fr)); gap:15px; margin-bottom:25px;">
        <?php $cards = [
            ['Total Leads',  $total,     '#3b82f6', 'fa-users'],
            ['Active',       $active,    '#f59e0b', 'fa-user-clock'],
            ['Converted',    $converted, '#22c55e', 'fa-handshake'],
            ['Lost',         $lost,      '#ef4444', 'fa-user-minus'],
            ['Junk',         $junk_total,'#64748b', 'fa-ban'],
        ];
        foreach($cards as [$label, $val, $color, $icon]): ?>
        <div style="background:#fff; border-radius:12px; padding:20px 15px; border:1px solid var(--border); text-align:center;">
            <div style="width:44px; height:44px; background:<?= $color ?>18; border-radius:10px; display:flex; align-items:center; justify-content:center; margin:0 auto 10px;">
                <i class="fa-solid <?= $icon ?>" style="color:<?= $color ?>;"></i>
            </div>
            <div style="font-size:2rem; font-weight:800; color:<?= $color ?>; line-height:1;"><?= $val ?></div>
            <div style="font-size:0.78rem; color:#64748b; font-weight:600; margin-top:4px;"><?= $label ?></div>
        </div>
        <?php endforeach; ?>
        <!-- Conversion Rate -->
        <?php $rate = $total > 0 ? round(($converted/$total)*100) : 0; ?>
        <div style="background:#fff; border-radius:12px; padding:20px 15px; border:1px solid var(--border); text-align:center;">
            <div style="width:44px; height:44px; background:#8b5cf618; border-radius:10px; display:flex; align-items:center; justify-content:center; margin:0 auto 10px;">
                <i class="fa-solid fa-percent" style="color:#8b5cf6;"></i>
            </div>
            <div style="font-size:2rem; font-weight:800; color:#8b5cf6; line-height:1;"><?= $rate ?>%</div>
            <div style="font-size:0.78rem; color:#64748b; font-weight:600; margin-top:4px;">Conversion Rate</div>
        </div>
    </div>

    <!-- 2+3+4. Three column charts row -->
    <div style="display:grid; grid-template-columns:1fr 1fr 1fr; gap:20px; margin-bottom:25px;">
        
        <!-- By Pipeline Stage -->
        <div style="background:#fff; border-radius:12px; border:1px solid var(--border); padding:22px;">
            <h3 style="margin:0 0 18px; font-size:0.95rem; color:var(--dark); display:flex; align-items:center; gap:8px;"><i class="fa-solid fa-diagram-project" style="color:#3b82f6;"></i> By Pipeline Stage</h3>
            <?php if(empty($by_stage)): ?><p style="color:#94a3b8; text-align:center; font-style:italic; font-size:0.9rem;">No data</p>
            <?php else: foreach($by_stage as $row): $pct = round(($row['cnt']/$total_stg)*100); $color = $row['color_code'] ?? '#64748b'; ?>
                <div style="margin-bottom:12px;">
                    <div style="display:flex; justify-content:space-between; margin-bottom:4px; align-items:center;">
                        <div style="display:flex; align-items:center; gap:6px;">
                            <div style="width:9px; height:9px; border-radius:50%; background:<?= $color ?>;"></div>
                            <span style="font-size:0.85rem; font-weight:600;"><?= htmlspecialchars($row['name'] ?? 'Unknown') ?></span>
                        </div>
                        <span style="font-weight:700; font-size:0.85rem; color:<?= $color ?>;"><?= $row['cnt'] ?></span>
                    </div>
                    <div style="background:#f1f5f9; border-radius:4px; height:7px;"><div style="background:<?= $color ?>; width:<?= $pct ?>%; height:100%; border-radius:4px;"></div></div>
                </div>
            <?php endforeach; endif; ?>
        </div>

        <!-- By Project Type -->
        <div style="background:#fff; border-radius:12px; border:1px solid var(--border); padding:22px;">
            <h3 style="margin:0 0 18px; font-size:0.95rem; color:var(--dark); display:flex; align-items:center; gap:8px;"><i class="fa-solid fa-building" style="color:#f59e0b;"></i> By Project Type</h3>
            <?php if(empty($by_project)): ?><p style="color:#94a3b8; text-align:center; font-style:italic; font-size:0.9rem;">No data</p>
            <?php else:
            $proj_colors = ['#3b82f6','#f59e0b','#22c55e','#8b5cf6','#f97316','#94a3b8'];
            foreach($by_project as $i => $row): $pct = round(($row['cnt']/$total_prj)*100); $color = $proj_colors[$i % count($proj_colors)]; ?>
                <div style="margin-bottom:12px;">
                    <div style="display:flex; justify-content:space-between; margin-bottom:4px; align-items:center;">
                        <span style="font-size:0.82rem; font-weight:600;"><?= htmlspecialchars($row['project_type']) ?></span>
                        <span style="font-weight:700; font-size:0.85rem; color:<?= $color ?>;"><?= $row['cnt'] ?></span>
                    </div>
                    <div style="background:#f1f5f9; border-radius:4px; height:7px;"><div style="background:<?= $color ?>; width:<?= $pct ?>%; height:100%; border-radius:4px;"></div></div>
                    <div style="font-size:0.72rem; color:#94a3b8; margin-top:1px;"><?= $pct ?>% of total</div>
                </div>
            <?php endforeach; endif; ?>
        </div>

        <!-- By Source -->
        <div style="background:#fff; border-radius:12px; border:1px solid var(--border); padding:22px;">
            <h3 style="margin:0 0 18px; font-size:0.95rem; color:var(--dark); display:flex; align-items:center; gap:8px;"><i class="fa-solid fa-share-nodes" style="color:#22c55e;"></i> By Lead Source</h3>
            <?php if(empty($by_source)): ?><p style="color:#94a3b8; text-align:center; font-style:italic; font-size:0.9rem;">No data</p>
            <?php else:
            $src_colors = ['#22c55e','#3b82f6','#f59e0b','#8b5cf6','#f97316','#94a3b8'];
            foreach($by_source as $i => $row): $pct = round(($row['cnt']/$total_src)*100); $color = $src_colors[$i % count($src_colors)]; ?>
                <div style="margin-bottom:12px;">
                    <div style="display:flex; justify-content:space-between; margin-bottom:4px; align-items:center;">
                        <span style="font-size:0.85rem; font-weight:600;"><?= htmlspecialchars($row['name']) ?></span>
                        <span style="font-weight:700; font-size:0.85rem; color:<?= $color ?>;"><?= $row['cnt'] ?></span>
                    </div>
                    <div style="background:#f1f5f9; border-radius:4px; height:7px;"><div style="background:<?= $color ?>; width:<?= $pct ?>%; height:100%; border-radius:4px;"></div></div>
                    <div style="font-size:0.72rem; color:#94a3b8; margin-top:1px;"><?= $pct ?>%</div>
                </div>
            <?php endforeach; endif; ?>
        </div>
    </div>

    <!-- 5+6. Monthly Conversion + Follow-up Row -->
    <div style="display:grid; grid-template-columns:2fr 1fr; gap:20px; margin-bottom:25px;">

        <!-- Monthly Conversion Table -->
        <div style="background:#fff; border-radius:12px; border:1px solid var(--border); padding:22px;">
            <h3 style="margin:0 0 18px; font-size:0.95rem; color:var(--dark); display:flex; align-items:center; gap:8px;"><i class="fa-solid fa-arrow-trend-up" style="color:#8b5cf6;"></i> Monthly Conversion Breakdown</h3>
            <?php if(empty($monthly)): ?><p style="color:#94a3b8; text-align:center; font-style:italic; font-size:0.9rem;">No data in this period</p>
            <?php else: ?>
            <table style="width:100%; border-collapse:collapse;">
                <thead>
                    <tr style="background:#f8fafc; font-size:0.78rem; color:#64748b; text-transform:uppercase; letter-spacing:0.04em;">
                        <th style="padding:8px 12px; text-align:left; font-weight:600;">Month</th>
                        <th style="padding:8px 12px; text-align:center; font-weight:600;">Total Leads</th>
                        <th style="padding:8px 12px; text-align:center; font-weight:600;">Converted</th>
                        <th style="padding:8px 12px; text-align:center; font-weight:600;">Rate</th>
                        <th style="padding:8px 12px; text-align:left; font-weight:600;">Visual</th>
                    </tr>
                </thead>
                <tbody>
                <?php foreach($monthly as $row): 
                    $rate = $row['total'] > 0 ? round(($row['converted']/$row['total'])*100) : 0;
                    $bar_color = $rate >= 30 ? '#22c55e' : ($rate >= 15 ? '#f59e0b' : '#ef4444');
                ?>
                    <tr style="border-bottom:1px solid #f1f5f9;">
                        <td style="padding:10px 12px; font-weight:600; font-size:0.9rem;"><?= htmlspecialchars($row['month']) ?></td>
                        <td style="padding:10px 12px; text-align:center; font-size:0.9rem;"><?= $row['total'] ?></td>
                        <td style="padding:10px 12px; text-align:center;"><span style="color:#22c55e; font-weight:700;"><?= $row['converted'] ?></span></td>
                        <td style="padding:10px 12px; text-align:center;"><span style="background:<?= $bar_color ?>18; color:<?= $bar_color ?>; padding:3px 9px; border-radius:10px; font-size:0.8rem; font-weight:700;"><?= $rate ?>%</span></td>
                        <td style="padding:10px 12px; width:120px;">
                            <div style="background:#f1f5f9; border-radius:4px; height:7px;"><div style="background:<?= $bar_color ?>; width:<?= $rate ?>%; height:100%; border-radius:4px; max-width:100%;"></div></div>
                        </td>
                    </tr>
                <?php endforeach; ?>
                </tbody>
            </table>
            <?php endif; ?>
        </div>

        <!-- Follow-up Summary -->
        <div style="background:#fff; border-radius:12px; border:1px solid var(--border); padding:22px;">
            <h3 style="margin:0 0 18px; font-size:0.95rem; color:var(--dark); display:flex; align-items:center; gap:8px;"><i class="fa-regular fa-calendar-check" style="color:#22c55e;"></i> Follow-up Activity</h3>
            <?php
            $fup_items = [
                ['Upcoming', $fup['upcoming'] ?? 0, '#3b82f6', 'fa-clock'],
                ['Done',     $fup['done']     ?? 0, '#22c55e', 'fa-circle-check'],
                ['Overdue',  $fup['overdue']  ?? 0, '#ef4444', 'fa-triangle-exclamation'],
                ['Cancelled',$fup['cancelled']?? 0, '#94a3b8', 'fa-circle-xmark'],
            ];
            $total_fup = max(array_sum(array_column($fup_items,'1')), 1);
            foreach($fup_items as [$label,$val,$color,$icon]): ?>
                <div style="display:flex; align-items:center; gap:12px; margin-bottom:16px; padding:12px; background:<?= $color ?>08; border-radius:8px; border-left:3px solid <?= $color ?>;">
                    <i class="fa-solid <?= $icon ?>" style="color:<?= $color ?>; font-size:1.1rem; width:20px; text-align:center;"></i>
                    <div style="flex:1;">
                        <div style="display:flex; justify-content:space-between; align-items:center;">
                            <span style="font-size:0.88rem; font-weight:600;"><?= $label ?></span>
                            <span style="font-size:1.2rem; font-weight:800; color:<?= $color ?>;"><?= $val ?></span>
                        </div>
                        <div style="background:#f1f5f9; border-radius:3px; height:5px; margin-top:5px;">
                            <div style="background:<?= $color ?>; width:<?= round(($val/$total_fup)*100) ?>%; height:100%; border-radius:3px;"></div>
                        </div>
                    </div>
                </div>
            <?php endforeach; ?>
        </div>
    </div>

    <!-- 7. Daily Timeline Bar Chart -->
    <div style="background:#fff; border-radius:12px; border:1px solid var(--border); padding:22px; margin-bottom:20px;">
        <div style="display:flex; align-items:center; justify-content:space-between; margin-bottom:18px; flex-wrap:wrap; gap:10px;">
            <h3 style="margin:0; font-size:0.95rem; color:var(--dark); display:flex; align-items:center; gap:8px;"><i class="fa-solid fa-chart-column" style="color:#3b82f6;"></i> Daily New Leads</h3>
            <span style="font-size:0.82rem; color:#64748b;"><?= count($daily_map) ?> days with activity · Peak: <?= $max_day ?> leads/day</span>
        </div>
        <?php if(empty($daily_map)): ?>
            <p style="color:#94a3b8; text-align:center; font-style:italic; padding:30px 0;">No leads in this date range.</p>
        <?php else: ?>
        <div style="display:flex; align-items:flex-end; gap:3px; height:140px; overflow-x:auto; padding-bottom:30px; position:relative;">
            <?php foreach($daily_map as $day => $cnt): 
                $bar_h = max(8, round(($cnt/$max_day)*120));
            ?>
            <div style="display:flex; flex-direction:column; align-items:center; min-width:32px; flex:1; position:relative;">
                <div style="font-size:0.7rem; color:var(--primary); font-weight:700; margin-bottom:4px;"><?= $cnt ?></div>
                <div title="<?= $day ?>: <?= $cnt ?> leads"
                     style="width:100%; height:<?= $bar_h ?>px; background:linear-gradient(180deg,#3b82f6,#60a5fa); border-radius:4px 4px 0 0; cursor:pointer; transition:opacity 0.2s;"
                     onmouseover="this.style.opacity=0.75" onmouseout="this.style.opacity=1"></div>
                <div style="font-size:0.6rem; color:#94a3b8; transform:rotate(-45deg); white-space:nowrap; position:absolute; bottom:-24px; transform-origin:center;"><?= date('d M', strtotime($day)) ?></div>
            </div>
            <?php endforeach; ?>
        </div>
        <?php endif; ?>
    </div>

</div>

<style>
@media (max-width:900px) {
    div[style*="grid-template-columns:1fr 1fr 1fr"],
    div[style*="grid-template-columns:2fr 1fr"] { grid-template-columns:1fr !important; }
    div[style*="grid-template-columns:repeat(auto-fit"] { grid-template-columns:1fr 1fr !important; }
}
</style>

<?php require_once 'includes/footer.php'; ?>
