<?php
// pages/exec_report.php — 경영 보고서 (CEO/시스템관리자 전용)
require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/../includes/header.php';
$db = (new Database())->getConnection();

$fYear = $_GET['year'] ?? date('Y');
$fMonth = $_GET['month'] ?? '';
$years = []; try { $years = $db->query("SELECT DISTINCT LEFT(COALESCE(sale_month, DATE_FORMAT(created_at,'%Y-%m')),4) AS y FROM sales ORDER BY y DESC")->fetchAll(PDO::FETCH_COLUMN); } catch(Exception $e) {}
if (!$years) $years = [date('Y')];

// 공통 필터 조건
if ($fMonth) {
    $fTarget = $fYear.'-'.str_pad($fMonth,2,'0',STR_PAD_LEFT);
    $dateWhere = "COALESCE(sale_month, DATE_FORMAT(created_at,'%Y-%m'))=?";
    $dateWhereS = "COALESCE(s.sale_month, DATE_FORMAT(s.created_at,'%Y-%m'))=?";
    $dateParam = [$fTarget];
} else {
    $fTarget = $fYear;
    $dateWhere = "LEFT(COALESCE(sale_month, DATE_FORMAT(created_at,'%Y-%m')),4)=?";
    $dateWhereS = "LEFT(COALESCE(s.sale_month, DATE_FORMAT(s.created_at,'%Y-%m')),4)=?";
    $dateParam = [$fYear];
}

// 월별 데이터 (차트)
$chartMonthly = $db->prepare("SELECT COALESCE(sale_month, DATE_FORMAT(created_at,'%Y-%m')) AS sm, SUM(revenue) AS total_revenue, SUM(net_profit) AS total_net_profit, COUNT(*) AS deal_count FROM sales WHERE {$dateWhere} AND stage IN('계약','촬영','방송완료') GROUP BY sm ORDER BY sm");
$chartMonthly->execute($dateParam); $chartData = $chartMonthly->fetchAll();
$chartLabels=[]; $chartRevs=[]; $chartProfits=[];
foreach ($chartData as $m) { $chartLabels[]=$m['sm']; $chartRevs[]=round($m['total_revenue']/10000); $chartProfits[]=round($m['total_net_profit']/10000); }

// 요약
$summary = $db->prepare("SELECT COALESCE(SUM(revenue),0) AS total_revenue, COALESCE(SUM(net_profit),0) AS total_net_profit, COUNT(*) AS total_deals, CASE WHEN COUNT(*)>0 THEN ROUND(SUM(revenue)/COUNT(*)) ELSE 0 END AS avg_deal_price FROM sales WHERE stage IN('계약','촬영','방송완료') AND {$dateWhere}");
$summary->execute($dateParam); $summary = $summary->fetch();

// 월별 상세
try { $monthly = $db->prepare("SELECT COALESCE(sale_month, DATE_FORMAT(created_at,'%Y-%m')) AS sm, COUNT(CASE WHEN stage IN('계약','촬영','방송완료') THEN 1 END) AS deal_count, SUM(CASE WHEN stage IN('계약','촬영','방송완료') THEN revenue ELSE 0 END) AS total_revenue, SUM(CASE WHEN stage IN('계약','촬영','방송완료') THEN net_profit ELSE 0 END) AS total_net_profit, SUM(CASE WHEN stage='리드' THEN 1 ELSE 0 END) AS stage_lead, SUM(CASE WHEN stage='제안' THEN 1 ELSE 0 END) AS stage_proposal, SUM(CASE WHEN stage='계약' THEN 1 ELSE 0 END) AS stage_contract, SUM(CASE WHEN stage='촬영' THEN 1 ELSE 0 END) AS stage_filming, SUM(CASE WHEN stage='방송완료' THEN 1 ELSE 0 END) AS stage_complete FROM sales WHERE {$dateWhere} GROUP BY sm ORDER BY sm");
$monthly->execute($dateParam); $monthly = $monthly->fetchAll(); } catch(Exception $e) { $monthly = []; }

// 단계별 월별 상세
$stageDetails = [];
$stageNames = ['리드','제안','계약','촬영','방송완료'];
$stageIcons = ['리드'=>'🔍','제안'=>'📨','계약'=>'📝','촬영'=>'🎬','방송완료'=>'📺'];
try {
    $stgQ = $db->prepare("SELECT COALESCE(s.sale_month, DATE_FORMAT(s.created_at,'%Y-%m')) AS sm, s.stage, st.name AS staff_name, COUNT(*) AS cnt, SUM(s.revenue) AS rev, SUM(s.cost) AS cst, SUM(s.net_profit) AS profit FROM sales s JOIN staff st ON s.staff_id=st.staff_id WHERE {$dateWhereS} GROUP BY sm, s.stage, st.name ORDER BY sm, s.stage, profit DESC");
    $stgQ->execute($dateParam);
    foreach($stgQ->fetchAll() as $row) { $stageDetails[$row['stage']][] = $row; }
} catch(Exception $e) {}

// 개인 랭킹
try { $ranking = $db->prepare("SELECT st.staff_id, st.name, st.grade, st.team, SUM(s.revenue) AS total_revenue, SUM(s.net_profit) AS total_net_profit, COUNT(*) AS total_deals, RANK() OVER (ORDER BY SUM(s.net_profit) DESC) AS revenue_rank FROM sales s JOIN staff st ON s.staff_id=st.staff_id LEFT JOIN user_account ua ON st.staff_id=ua.staff_id WHERE s.stage IN('계약','촬영','방송완료') AND {$dateWhereS} AND (ua.user_role IS NULL OR ua.user_role != 'sysadmin') GROUP BY st.staff_id, st.name, st.grade, st.team ORDER BY total_net_profit DESC LIMIT 15");
$ranking->execute($dateParam); $ranking = $ranking->fetchAll(); } catch(Exception $e) { $ranking = []; }
try { $approval = $db->query("SELECT * FROM v_dashboard_approval")->fetch(); } catch(Exception $e) { $approval = []; }
// 인센티브 검토 현황 (incentive_review 테이블 기준)
$irWait=0;$irReview=0;$irRequest=0;$irCeoApproved=0;$irReject=0;$irConfirm=0;
try {
    $db->exec("CREATE TABLE IF NOT EXISTS incentive_review (
        review_id INT AUTO_INCREMENT PRIMARY KEY,
        staff_id INT NOT NULL,
        sale_month VARCHAR(7) DEFAULT NULL,
        review_status VARCHAR(20) DEFAULT '대기' COMMENT '대기/검토중/승인요청/대표승인/반려/확정',
        review_memo TEXT DEFAULT NULL,
        reviewed_by INT DEFAULT NULL,
        ceo_approved_by INT DEFAULT NULL,
        ceo_memo TEXT DEFAULT NULL,
        final_amount INT DEFAULT 0,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY uk_staff_month (staff_id, sale_month)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    $irStats = $db->query("SELECT review_status, COUNT(*) AS cnt FROM incentive_review GROUP BY review_status")->fetchAll();
    foreach($irStats as $irs) {
        switch($irs['review_status']) {
            case '대기': $irWait=(int)$irs['cnt']; break;
            case '검토중': $irReview=(int)$irs['cnt']; break;
            case '승인요청': $irRequest=(int)$irs['cnt']; break;
            case '대표승인': $irCeoApproved=(int)$irs['cnt']; break;
            case '반려': $irReject=(int)$irs['cnt']; break;
            case '확정': $irConfirm=(int)$irs['cnt']; break;
        }
    }
} catch(Exception $e) {}
// incentive_rate.is_active 컬럼 보장
try { $db->query("SELECT is_active FROM incentive_rate LIMIT 0"); } catch(Exception $e) {
    try { $db->exec("ALTER TABLE incentive_rate ADD COLUMN is_active TINYINT(1) DEFAULT 1"); } catch(Exception $e2) {}
}
try {
    $incentives = $db->query("SELECT ic.*, ua.user_role, af.review_status AS af_review_status, af.approve_status, af.final_amount FROM v_incentive_calc ic LEFT JOIN approval_flow af ON ic.staff_id=af.staff_id AND ic.sale_month=af.approval_month LEFT JOIN user_account ua ON ic.staff_id=ua.staff_id LEFT JOIN incentive_rate ir2 ON ic.grade=ir2.grade WHERE ic.eligible='Y' AND COALESCE(ir2.is_active,1)=1 AND (ua.user_role IS NULL OR ua.user_role != 'sysadmin') ORDER BY ic.sale_month DESC, ic.total_net_profit DESC")->fetchAll();
} catch(Exception $e) {
    // v_incentive_calc 미존재 시 직접 계산 — 계약/촬영/방송완료 1건 이상이면 대상
    $incentives = $db->query("SELECT sub.*, ua.user_role, af.review_status AS af_review_status, af.approve_status, af.final_amount
        FROM (
            SELECT s.staff_id, st.name, st.grade, st.team, COALESCE(s.sale_month, DATE_FORMAT(s.created_at,'%Y-%m')) AS sale_month,
                COUNT(*) AS deal_count, SUM(s.revenue) AS total_revenue, SUM(s.net_profit) AS total_net_profit,
                ROUND(SUM(s.net_profit) * COALESCE(MAX(ir.min_rate),0)) AS incentive_min,
                ROUND(SUM(s.net_profit) * COALESCE(MAX(ir.max_rate),0)) AS incentive_max,
                'Y' AS eligible
            FROM sales s JOIN staff st ON s.staff_id=st.staff_id
            LEFT JOIN incentive_rate ir ON st.grade COLLATE utf8mb4_unicode_ci=ir.grade COLLATE utf8mb4_unicode_ci AND COALESCE(ir.is_active,1)=1
            WHERE s.stage IN('계약','촬영','방송완료')
            GROUP BY s.staff_id, st.name, st.grade, st.team, sale_month
            HAVING total_net_profit > 0
        ) sub
        LEFT JOIN approval_flow af ON sub.staff_id=af.staff_id AND sub.sale_month=af.approval_month
        LEFT JOIN user_account ua ON sub.staff_id=ua.staff_id
        WHERE (ua.user_role IS NULL OR ua.user_role != 'sysadmin')
        ORDER BY sub.sale_month DESC, sub.total_net_profit DESC")->fetchAll();
}
try { $underperform = $db->query("SELECT * FROM v_underperform_flag")->fetchAll(); } catch(Exception $e) { $underperform = []; }
try { $managerWarn = $db->query("SELECT * FROM v_manager_warn")->fetchAll(); } catch(Exception $e) { $managerWarn = []; }
// 팀별 실적 랭킹: team_master 기준 (경영진 제외)
$teamRankings = [];
try {
    $allTeams = $db->query("SELECT team_name FROM team_master WHERE is_active=1 AND team_name NOT IN('경영진','경영지원','오너','시스템') ORDER BY team_name")->fetchAll(PDO::FETCH_COLUMN);
    foreach ($allTeams as $tn) {
        $tq = $db->prepare("SELECT COALESCE(SUM(s.revenue),0) AS total_revenue, COALESCE(SUM(s.net_profit),0) AS total_net_profit, COUNT(*) AS deals, COUNT(DISTINCT s.staff_id) AS members FROM sales s JOIN staff st ON s.staff_id=st.staff_id WHERE st.team=? AND s.stage IN('계약','촬영','방송완료') AND {$dateWhereS}");
        $tq->execute(array_merge([$tn], $dateParam));
        $tr = $tq->fetch();
        $tr['team'] = $tn;
        // 팀 총 인원 (team_master 기준)
        $tr['total_members'] = (int)$db->prepare("SELECT COUNT(*) FROM staff WHERE team=? AND is_active=1")->execute([$tn]) ? (int)$db->query("SELECT COUNT(*) FROM staff WHERE team='{$tn}' AND is_active=1")->fetchColumn() : 0;
        $teamRankings[] = $tr;
    }
    usort($teamRankings, fn($a,$b) => $b['total_net_profit'] <=> $a['total_net_profit']);
} catch(Exception $e) {}
$teamKPI = [];
try {
    $ptRules = $db->query("SELECT rule_key, rule_value FROM policy_rule WHERE rule_key IN('deal_target_monthly','revenue_target_monthly','profit_target_monthly')")->fetchAll(PDO::FETCH_KEY_PAIR);
    $dealTargetM = (int)str_replace(',', '', $ptRules['deal_target_monthly'] ?? '0');
    $revTargetM = (int)str_replace(',', '', $ptRules['revenue_target_monthly'] ?? '0');
    // 월 필터 없으면 해당 연도 경과 월수 적용
    $monthMultiplier = 1;
    if (!$fMonth) {
        if ($fYear == date('Y')) { $monthMultiplier = (int)date('n'); }
        else { $monthMultiplier = 12; }
    }
    foreach ($teamRankings as &$tr) {
        $tm = (int)($tr['total_members'] ?: $tr['members'] ?: 1);
        $teamDealTarget = $dealTargetM * $tm * $monthMultiplier;
        $teamRevTarget = $revTargetM * $monthMultiplier;
        $teamKPI[$tr['team']] = [
            'avg_deal' => $teamDealTarget > 0 ? round($tr['deals'] / $teamDealTarget * 100, 1) : null,
            'avg_amt' => $teamRevTarget > 0 ? round($tr['total_net_profit'] / $teamRevTarget * 100, 1) : null,
        ];
    }
    unset($tr);
} catch(Exception $e) {}
$orgCount = (int)$db->query("SELECT COUNT(*) FROM staff WHERE is_active=1")->fetchColumn();
?>

<div class="page-header"><h2>📑 경영 보고서</h2><div class="breadcrumb">CEO · AP MEDIA ONE 종합 현황</div></div>

<div class="card" style="margin-bottom:1rem;padding:0.75rem 1rem;">
    <form method="GET" style="display:flex;gap:0.75rem;align-items:center;flex-wrap:wrap;">
        <select name="year" onchange="this.form.submit()" class="dash-filter" style="width:7rem;">
            <?php foreach($years as $y): ?><option value="<?= $y ?>" <?= $y==$fYear?'selected':'' ?>><?= $y ?>년</option><?php endforeach; ?>
        </select>
        <select name="month" onchange="this.form.submit()" class="dash-filter" style="width:9rem;">
            <option value="">월 전체</option>
            <?php for($mi=1;$mi<=12;$mi++): $mv=str_pad($mi,2,'0',STR_PAD_LEFT); ?><option value="<?= $mv ?>" <?= $fMonth==$mv?'selected':'' ?>><?= $mi ?>월</option><?php endfor; ?>
        </select>
    </form>
</div>

<div style="display:grid;grid-template-columns:repeat(5,1fr);gap:0.75rem;margin-bottom:1.25rem;">
    <div class="card" style="padding:1rem;"><div style="font-size:var(--fs-xs);color:var(--text-tertiary);">전체 매출</div><div style="font-size:var(--fs-xl);font-weight:800;color:var(--accent-text);"><?= number_format(($summary['total_revenue']??0)/10000) ?></div><div style="font-size:var(--fs-xs);color:var(--text-tertiary);">만원</div></div>
    <div class="card" style="padding:1rem;"><div style="font-size:var(--fs-xs);color:var(--text-tertiary);">전체 순이익</div><div style="font-size:var(--fs-xl);font-weight:800;color:var(--success);"><?= number_format(($summary['total_net_profit']??0)/10000) ?></div><div style="font-size:var(--fs-xs);color:var(--text-tertiary);">만원</div></div>
    <div class="card" style="padding:1rem;"><div style="font-size:var(--fs-xs);color:var(--text-tertiary);">성사 건수</div><div style="font-size:var(--fs-xl);font-weight:800;"><?= number_format($summary['total_deals']??0) ?></div><div style="font-size:var(--fs-xs);color:var(--text-tertiary);">건</div></div>
    <div class="card" style="padding:1rem;"><div style="font-size:var(--fs-xs);color:var(--text-tertiary);">평균 단가</div><div style="font-size:var(--fs-xl);font-weight:800;"><?= number_format(($summary['avg_deal_price']??0)/10000) ?></div><div style="font-size:var(--fs-xs);color:var(--text-tertiary);">만원</div></div>
    <div class="card" style="padding:1rem;"><div style="font-size:var(--fs-xs);color:var(--text-tertiary);">조직 인원</div><div style="font-size:var(--fs-xl);font-weight:800;color:var(--info);"><?= $orgCount ?></div><div style="font-size:var(--fs-xs);color:var(--text-tertiary);">명</div></div>
</div>

<div style="display:grid;grid-template-columns:1fr 2fr;gap:1rem;margin-bottom:1.25rem;">
    <div class="card" style="padding:1rem;">
        <div style="font-size:var(--fs-sm);font-weight:700;margin-bottom:0.75rem;">✅ 인센티브 검토 현황</div>
        <div><div style="font-size:var(--fs-xs);color:var(--text-tertiary);font-weight:600;margin-bottom:0.35rem;">🏛 대표 승인 검토</div><div style="display:flex;gap:0.4rem;flex-wrap:wrap;">
            <a href="<?=BASE_URL?>pages/incentive.php?review_st=대기" style="text-decoration:none;"><span class="badge badge-gray" style="font-size:var(--fs-xs);cursor:pointer;">대기 <strong><?= $irWait ?></strong></span></a>
            <a href="<?=BASE_URL?>pages/incentive.php?review_st=검토중" style="text-decoration:none;"><span class="badge badge-info" style="font-size:var(--fs-xs);cursor:pointer;">검토중 <strong><?= $irReview ?></strong></span></a>
            <a href="<?=BASE_URL?>pages/incentive.php?review_st=승인요청" style="text-decoration:none;"><span class="badge badge-warning" style="font-size:var(--fs-xs);cursor:pointer;">승인요청 <strong><?= $irRequest ?></strong></span></a>
            <a href="<?=BASE_URL?>pages/incentive.php?review_st=대표승인" style="text-decoration:none;"><span class="badge badge-success" style="font-size:var(--fs-xs);cursor:pointer;">대표승인 <strong><?= $irCeoApproved ?></strong></span></a>
            <a href="<?=BASE_URL?>pages/incentive.php?review_st=반려" style="text-decoration:none;"><span class="badge badge-danger" style="font-size:var(--fs-xs);cursor:pointer;">반려 <strong><?= $irReject ?></strong></span></a>
            <a href="<?=BASE_URL?>pages/incentive.php?review_st=확정" style="text-decoration:none;"><span class="badge badge-success" style="font-size:var(--fs-xs);cursor:pointer;">확정 <strong><?= $irConfirm ?></strong></span></a>
        </div></div>
    </div>
    <div class="card" style="padding:1rem;"><div style="display:flex;justify-content:space-between;align-items:center;margin-bottom:0.5rem;"><div style="font-size:var(--fs-sm);font-weight:700;">📊 월별 실적 추이 (<?= $fYear ?>년)</div>
    </div><div style="position:relative;height:180px;"><canvas id="monthlyChart"></canvas></div></div>
</div>

<div class="card" style="margin-bottom:1.25rem;"><div class="card-header"><h3>🏢 팀별 실적 랭킹</h3></div><div class="table-wrap"><table>
    <thead><tr><th>순위</th><th>팀</th><th>매출</th><th>순이익</th><th>건수</th><th>인원</th><th>인당 매출</th><th>KPI 건수</th><th>KPI 금액</th></tr></thead>
    <tbody><?php foreach ($teamRankings as $i => $tr): $kpi=$teamKPI[$tr['team']]??[]; ?>
    <tr><td><strong style="color:<?= $i<3?'#C00000':'inherit' ?>;font-size:<?= $i<3?'1rem':'0.8rem' ?>;"><?= $i===0?'🥇':($i===1?'🥈':($i===2?'🥉':($i+1))) ?></strong></td>
        <td><strong><?= htmlspecialchars($tr['team']) ?></strong></td>
        <td class="text-right font-mono"><?= number_format($tr['total_revenue']) ?></td><td class="text-right font-mono"><?= number_format($tr['total_net_profit']) ?></td>
        <td class="text-center"><?= $tr['deals'] ?></td><td class="text-center"><?= $tr['members'] ?></td>
        <td class="text-right font-mono"><?= $tr['members']>0?number_format(round($tr['total_revenue']/$tr['members'])):'-' ?></td>
        <td class="text-center"><?= isset($kpi['avg_deal'])?'<span class="badge '.($kpi['avg_deal']>=100?'badge-success':($kpi['avg_deal']>=50?'badge-warning':'badge-danger')).'">'.$kpi['avg_deal'].'%</span>':'-' ?></td>
        <td class="text-center"><?= isset($kpi['avg_amt'])?'<span class="badge '.($kpi['avg_amt']>=100?'badge-success':($kpi['avg_amt']>=50?'badge-warning':'badge-danger')).'">'.$kpi['avg_amt'].'%</span>':'-' ?></td></tr>
    <?php endforeach; ?></tbody>
</table></div></div>

<div class="card" style="margin-bottom:1.25rem;"><div class="card-header"><h3>📅 월별 실적 상세</h3><span style="font-size:var(--fs-xs);color:var(--text-tertiary);margin-left:0.5rem;">월을 클릭하면 단계별 상세를 볼 수 있습니다</span></div><div class="table-wrap"><table>
    <thead><tr><th>월</th><th>건수</th><th>매출</th><th>순이익</th><th>리드</th><th>제안</th><th>계약</th><th>촬영</th><th>방송완료</th></tr></thead>
    <tbody><?php
    // stageDetails를 월별로 재구성
    $stageByMonth = [];
    foreach($stageDetails as $stg => $rows) { foreach($rows as $r) { $stageByMonth[$r['sm']][$stg][] = $r; } }
    foreach ($monthly as $mi => $m): ?>
    <tr>
        <td><strong><?= $m['sm'] ?></strong></td><td><?= $m['deal_count'] ?></td>
        <td class="text-right font-mono"><?= number_format($m['total_revenue']) ?></td><td class="text-right font-mono"><strong><?= number_format($m['total_net_profit']) ?></strong></td>
        <td><?= $m['stage_lead'] ?? 0 ?></td><td><?= $m['stage_proposal'] ?? 0 ?></td><td><?= $m['stage_contract'] ?? 0 ?></td><td><?= $m['stage_filming'] ?? 0 ?></td><td><?= $m['stage_complete'] ?? 0 ?></td></tr>
    <tr id="stgD<?=$mi?>"><td colspan="9" style="padding:0;">
        <div style="display:grid;grid-template-columns:repeat(auto-fit,minmax(280px,1fr));gap:0.5rem;padding:0.75rem;background:rgba(255,255,255,0.02);">
        <?php $sm = $m['sm']; foreach($stageNames as $stg): $sRows = $stageByMonth[$sm][$stg] ?? []; $icon = $stageIcons[$stg]; ?>
        <div style="border:1px solid rgba(255,255,255,0.06);border-radius:6px;overflow:hidden;">
            <div style="padding:0.4rem 0.6rem;background:rgba(255,255,255,0.04);font-weight:600;font-size:var(--fs-xs);"><?= $icon ?> <?= $stg ?> <span style="color:var(--text-tertiary);font-weight:400;">(<?= count($sRows) ?>건)</span></div>
            <?php if($sRows): ?>
            <table style="width:100%;margin:0;"><thead><tr><th style="font-size:0.7rem;">담당자</th><th style="font-size:0.7rem;">건</th><th style="font-size:0.7rem;">매출</th><th style="font-size:0.7rem;">순이익</th></tr></thead><tbody>
            <?php foreach($sRows as $r): ?>
            <tr><td style="font-size:var(--fs-xs);"><?= htmlspecialchars($r['staff_name']) ?></td><td style="font-size:var(--fs-xs);"><?= $r['cnt'] ?></td>
                <td class="text-right font-mono" style="font-size:var(--fs-xs);"><?= number_format($r['rev']) ?></td>
                <td class="text-right font-mono" style="font-size:var(--fs-xs);"><strong><?= number_format($r['profit']) ?></strong></td></tr>
            <?php endforeach; ?></tbody></table>
            <?php else: ?><div style="padding:0.4rem 0.6rem;font-size:var(--fs-xs);color:var(--text-tertiary);">-</div><?php endif; ?>
        </div>
        <?php endforeach; ?>
        </div>
    </td></tr>
    <?php endforeach; ?><?php if(!$monthly): ?><tr><td colspan="9" class="text-muted">데이터 없음</td></tr><?php endif; ?></tbody>
</table></div></div>

<div class="card" style="margin-bottom:1.25rem;"><div class="card-header"><h3>💎 인센티브 검토 대상자</h3></div><div class="table-wrap"><table>
    <thead><tr><th>이름</th><th>직급</th><th>월</th><th>성사</th><th>순이익</th><th>최소</th><th>최대</th><th>검토</th><th>승인</th><th>확정</th></tr></thead>
    <tbody><?php
    // 인센티브 검토 대상자: incentive_review에서 승인요청/대표승인 상태만
    try {
        $incQ = $db->query("SELECT ir2.*, st.name, st.grade, st.team, ua.user_role,
            (SELECT COUNT(*) FROM sales s2 WHERE s2.staff_id=ir2.staff_id AND s2.stage IN('계약','촬영','방송완료') AND DATE_FORMAT(s2.contract_date,'%Y-%m')=ir2.sale_month) AS deal_count,
            (SELECT SUM(s2.net_profit) FROM sales s2 WHERE s2.staff_id=ir2.staff_id AND s2.stage IN('계약','촬영','방송완료') AND DATE_FORMAT(s2.contract_date,'%Y-%m')=ir2.sale_month) AS total_net_profit,
            COALESCE(irt.min_rate,0) AS min_rate, COALESCE(irt.max_rate,0) AS max_rate
            FROM incentive_review ir2
            JOIN staff st ON ir2.staff_id=st.staff_id
            LEFT JOIN user_account ua ON st.staff_id=ua.staff_id
            LEFT JOIN incentive_rate irt ON st.grade COLLATE utf8mb4_unicode_ci=irt.grade COLLATE utf8mb4_unicode_ci AND COALESCE(irt.is_active,1)=1
            WHERE ir2.review_status IN('승인요청','대표승인','확정')
            ORDER BY ir2.sale_month DESC, st.name");
        $incRows = $incQ->fetchAll();
    } catch(Exception $e) { $incRows = []; }
    foreach ($incRows as $ic):
        $icProfit = (int)($ic['total_net_profit']??0);
        $icMin = round($icProfit*$ic['min_rate']); $icMax = round($icProfit*$ic['max_rate']);
    ?>
    <tr><td><strong><?= crownName($ic['name'], $ic['user_role']??'') ?></strong></td><td><span class="badge badge-info"><?= $ic['grade'] ?></span></td>
        <td><?= $ic['sale_month'] ?: '-' ?></td><td><?= $ic['deal_count']??0 ?></td><td class="text-right font-mono"><?= number_format($icProfit) ?></td>
        <td class="text-right font-mono"><?= number_format($icMin) ?></td><td class="text-right font-mono"><?= number_format($icMax) ?></td>
        <td colspan="3"><span class="badge <?= $ic['review_status']==='확정'?'badge-success':($ic['review_status']==='대표승인'?'badge-info':'badge-warning') ?>"><?= $ic['review_status'] ?></span></td></tr>
    <?php endforeach; ?><?php if(!$incRows): ?><tr><td colspan="10" class="text-muted">검토 대상 없음</td></tr><?php endif; ?></tbody>
</table></div></div>

<?php
// 경영진 제외
$ceoSysIds = [];
try { $ceoSysIds = $db->query("SELECT staff_id FROM user_account WHERE user_role IN('ceo','sysadmin','manager')")->fetchAll(PDO::FETCH_COLUMN); } catch(Exception $e) {}

// 건수 0인 인원 (경영진 제외, 모든 활성 회원 대상)
$zeroDeals = [];
try {
    $zdSql = "SELECT st.staff_id, st.name, st.grade, st.team 
        FROM staff st 
        JOIN user_account ua ON st.staff_id=ua.staff_id 
        WHERE st.is_active=1 AND ua.is_active=1
        AND ua.user_role NOT IN('ceo','sysadmin','manager')
        AND st.staff_id NOT IN (
            SELECT DISTINCT s.staff_id FROM sales s 
            WHERE s.stage IN('계약','촬영','방송완료') AND {$dateWhere}
        )
        ORDER BY st.team, st.name";
    $zdStmt = $db->prepare($zdSql); $zdStmt->execute($dateParam); $zeroDeals = $zdStmt->fetchAll();
} catch(Exception $e) {}
?>
<?php if (!empty($zeroDeals)): ?>
<div class="card" style="margin-bottom:1.25rem;"><div class="card-header"><h3>⚠ 경고 플래그</h3></div>
<div style="padding:0.5rem 1rem;"><div style="font-size:var(--fs-xs);font-weight:600;color:var(--danger);margin-bottom:0.35rem;">🔴 실적 건수 0건 인원</div></div>
<div class="table-wrap"><table><thead><tr><th>이름</th><th>직급</th><th>팀</th><th>상태</th></tr></thead><tbody>
<?php foreach($zeroDeals as $z): ?><tr><td><strong><?= htmlspecialchars($z['name']) ?></strong></td><td><?= $z['grade'] ?></td><td><?= htmlspecialchars($z['team']) ?></td><td><span class="badge badge-danger">0건</span></td></tr><?php endforeach; ?>
</tbody></table></div></div>
<?php endif; ?>

<div class="card"><div class="card-header"><h3>🏆 개인 실적 랭킹 TOP 15</h3></div><div class="table-wrap"><table>
    <thead><tr><th>순위</th><th>이름</th><th>직급</th><th>팀</th><th>매출</th><th>순이익</th><th>건수</th></tr></thead>
    <tbody><?php foreach ($ranking as $r): ?>
    <tr><td><strong style="font-size:<?= (int)$r['revenue_rank']<=3?'1rem':'0.8rem' ?>;"><?= (int)$r['revenue_rank']===1?'🥇':((int)$r['revenue_rank']===2?'🥈':((int)$r['revenue_rank']===3?'🥉':$r['revenue_rank'])) ?></strong></td>
        <td><strong><?= htmlspecialchars($r['name']) ?></strong></td><td><span class="badge badge-info"><?= $r['grade'] ?></span></td><td><?= htmlspecialchars($r['team']) ?></td>
        <td class="text-right font-mono"><?= number_format($r['total_revenue']) ?></td><td class="text-right font-mono"><?= number_format($r['total_net_profit']) ?></td><td class="text-center"><?= $r['total_deals'] ?></td></tr>
    <?php endforeach; ?></tbody>
</table></div></div>

<script src="https://cdn.jsdelivr.net/npm/chart.js@4.4.0/dist/chart.umd.min.js"></script>
<script>
if(!CanvasRenderingContext2D.prototype.roundRect){CanvasRenderingContext2D.prototype.roundRect=function(x,y,w,h,r){r=Math.min(r,w/2,h/2);this.beginPath();this.moveTo(x+r,y);this.lineTo(x+w-r,y);this.quadraticCurveTo(x+w,y,x+w,y+r);this.lineTo(x+w,y+h-r);this.quadraticCurveTo(x+w,y+h,x+w-r,y+h);this.lineTo(x+r,y+h);this.quadraticCurveTo(x,y+h,x,y+h-r);this.lineTo(x,y+r);this.quadraticCurveTo(x,y,x+r,y);this.closePath();return this;};}
var labels=<?= json_encode($chartLabels) ?>,revs=<?= json_encode($chartRevs) ?>,profits=<?= json_encode($chartProfits) ?>;
var isSingle=labels.length===1;
if(isSingle){
    // 단일 월: 매출/순이익 나란히 바 차트
    new Chart(document.getElementById('monthlyChart'),{type:'bar',data:{labels:[labels[0]],datasets:[
        {label:'매출(만)',data:[revs[0]],backgroundColor:'rgba(41,121,255,0.7)',borderRadius:6,barPercentage:0.35,categoryPercentage:0.5},
        {label:'순이익(만)',data:[profits[0]],backgroundColor:'rgba(0,230,118,0.7)',borderRadius:6,barPercentage:0.35,categoryPercentage:0.5}
    ]},options:{responsive:true,maintainAspectRatio:false,layout:{padding:{top:10}},plugins:{legend:{labels:{color:'#aaa',font:{size:12},usePointStyle:true}}},scales:{x:{grid:{display:false},ticks:{color:'#aaa',font:{size:12}}},y:{beginAtZero:true,grid:{color:'rgba(255,255,255,0.06)'},ticks:{color:'#aaa',font:{size:12},callback:function(v){return v.toLocaleString()+'만'}}}}},plugins:[{afterDatasetsDraw:function(chart){var ctx=chart.ctx;ctx.save();chart.data.datasets.forEach(function(ds,di){chart.getDatasetMeta(di).data.forEach(function(el,i){var val=ds.data[i];if(val<=0)return;var txt=val.toLocaleString()+'만';ctx.font='bold 12px sans-serif';ctx.fillStyle='#fff';ctx.textAlign='center';ctx.fillText(txt,el.x,el.y+18);});});ctx.restore();}}]});
} else {
    // 다중 월: 바+라인 콤보
    new Chart(document.getElementById('monthlyChart'),{type:'bar',data:{labels:labels,datasets:[
        {label:'매출(만)',data:revs,backgroundColor:'rgba(41,121,255,0.7)',borderRadius:4,maxBarThickness:50,order:2},
        {label:'순이익(만)',data:profits,type:'line',borderColor:'#00E676',backgroundColor:'rgba(0,230,118,0.08)',fill:true,tension:0.3,pointRadius:4,pointBackgroundColor:'#00E676',borderWidth:2,order:1}
    ]},options:{responsive:true,maintainAspectRatio:false,layout:{padding:{top:10}},plugins:{legend:{labels:{color:'#aaa',font:{size:12},usePointStyle:true}}},scales:{x:{grid:{display:false},ticks:{color:'#aaa',font:{size:12}}},y:{beginAtZero:true,grid:{color:'rgba(255,255,255,0.06)'},ticks:{color:'#aaa',font:{size:12},callback:function(v){return v.toLocaleString()+'만'}}}}},plugins:[{afterDatasetsDraw:function(chart){var ctx=chart.ctx;ctx.save();chart.data.datasets.forEach(function(ds,di){var meta=chart.getDatasetMeta(di);meta.data.forEach(function(el,i){var val=ds.data[i];if(val<=0)return;var txt=val.toLocaleString()+'만';ctx.font='bold 11px sans-serif';ctx.textAlign='center';if(di===0){ctx.fillStyle='#fff';ctx.fillText(txt,el.x,el.y+16);}else{ctx.fillStyle='#00E676';ctx.fillText(txt,el.x,el.y-8);}});});ctx.restore();}}]});
}
</script>

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