<?php
// pages/pipeline.php — 영업 단계 현황
require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/../includes/header.php';
$db = (new Database())->getConnection();

// 빈 stage 자동 보정
try { $db->exec("UPDATE sales SET stage='미진행' WHERE stage IS NULL OR stage=''"); } catch(Exception $e) {}

$fYear = $_GET['year'] ?? date('Y');
$filterMonth = $_GET['month'] ?? '';
$filterStage = $_GET['stage'] ?? '';

$dateSql = "COALESCE(s.sale_month, DATE_FORMAT(s.contract_date,'%Y-%m'), DATE_FORMAT(s.created_at,'%Y-%m'))";
$pYears = $db->query("SELECT DISTINCT LEFT({$dateSql},4) AS y FROM sales s ORDER BY y DESC")->fetchAll(PDO::FETCH_COLUMN);
if (!$pYears) $pYears = [date('Y')];
$months = $db->query("SELECT DISTINCT {$dateSql} AS sm FROM sales s WHERE LEFT({$dateSql},4)='{$fYear}' AND {$dateSql} IS NOT NULL ORDER BY sm DESC")->fetchAll(PDO::FETCH_COLUMN);

// 단계별 직접 쿼리 (sale_month NULL도 포함)
$sql = "SELECT CASE WHEN s.stage IS NULL OR s.stage='' THEN '미진행' ELSE s.stage END AS stage,
        COUNT(*) AS deal_count, COALESCE(SUM(s.revenue),0) AS total_revenue,
        COALESCE(SUM(s.cost),0) AS total_cost, COALESCE(SUM(s.net_profit),0) AS total_net_profit
        FROM sales s WHERE LEFT({$dateSql},4) = ?";
$params = [$fYear];
if ($filterMonth) { $sql .= " AND {$dateSql} = ?"; $params[] = $filterMonth; }
if ($filterStage) { $sql .= " AND (CASE WHEN s.stage IS NULL OR s.stage='' THEN '미진행' ELSE s.stage END) = ?"; $params[] = $filterStage; }
$sql .= " GROUP BY stage ORDER BY FIELD(stage,'미진행','리드','제안','계약','촬영','방송완료')";
$stmt = $db->prepare($sql); $stmt->execute($params);
$pipeline = $stmt->fetchAll();
$totalDeals = array_sum(array_column($pipeline, 'deal_count')) ?: 1;
?>
<div class="page-header"><h2>🔄 영업 단계 현황</h2></div>
<div class="card" style="margin-bottom:1rem;padding:0.75rem 1rem;">
    <form method="GET" style="display:flex;gap:0.75rem;align-items:center;">
        <select name="year" onchange="this.form.submit()" class="dash-filter" style="width:7rem;">
            <?php foreach($pYears as $y): ?><option value="<?= $y ?>" <?= $fYear==$y?'selected':'' ?>><?= $y ?>년</option><?php endforeach; ?>
        </select>
        <select name="month" onchange="this.form.submit()" class="dash-filter" style="width:9rem;">
            <option value="">전체</option>
            <?php foreach($months as $m): ?><option value="<?= $m ?>" <?= $filterMonth===$m?'selected':'' ?>><?= $m ?></option><?php endforeach; ?>
        </select>
        <select name="stage" onchange="this.form.submit()" class="dash-filter" style="width:8rem;">
            <option value="">단계 전체</option>
            <?php foreach (['미진행','리드','제안','계약','촬영','방송완료'] as $st): ?><option value="<?= $st ?>" <?= $filterStage===$st?'selected':'' ?>><?= $st ?></option><?php endforeach; ?>
        </select>
    </form>
</div>
<div class="card"><div class="table-wrap"><table>
    <thead><tr><th>단계</th><th>건수</th><th>매출</th><th>비용</th><th>순이익</th><th>비중(%)</th></tr></thead>
    <tbody>
    <?php foreach ($pipeline as $p): $pct = round($p['deal_count'] / $totalDeals * 100, 1); ?>
    <tr>
        <td><span class="badge stage-<?= $p['stage'] ?>"><?= $p['stage'] ?></span></td>
        <td><?= number_format($p['deal_count']) ?></td>
        <td class="text-right font-mono"><?= number_format($p['total_revenue']) ?></td>
        <td class="text-right font-mono"><?= number_format($p['total_cost']) ?></td>
        <td class="text-right font-mono"><strong><?= number_format($p['total_net_profit']) ?></strong></td>
        <td><?= $pct ?>%</td>
    </tr>
    <?php endforeach; ?>
    <?php if (empty($pipeline)): ?><tr><td colspan="6" style="padding:2rem;color:var(--text-tertiary);">데이터가 존재하지 않습니다.</td></tr><?php endif; ?>
    </tbody>
</table></div></div>
<?php require_once __DIR__ . '/../includes/footer.php'; ?>
