<?php
// pages/kpi.php — KPI 달성률 (정책 목표 연동)
require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/../includes/header.php';
$db = (new Database())->getConnection();
$user = currentUser();
$perm = getMenuPerm($db, 'kpi', $user['role']);

// 정책 목표
try { $pTargets = $db->query("SELECT rule_key, rule_value FROM policy_rule WHERE rule_key IN ('revenue_target_monthly','profit_target_monthly','deal_target_monthly','kpi_warn_threshold')")->fetchAll(PDO::FETCH_KEY_PAIR); } catch(Exception $e) { $pTargets = []; }
$pRevenueTarget = (int)str_replace(',', '', $pTargets['revenue_target_monthly'] ?? '0');
$pProfitTarget = (int)str_replace(',', '', $pTargets['profit_target_monthly'] ?? '0');
$pDealTarget = (int)str_replace(',', '', $pTargets['deal_target_monthly'] ?? '0');
$pWarnThreshold = (int)str_replace(',', '', $pTargets['kpi_warn_threshold'] ?? '2');

// 필터
$fYear = $_GET['year'] ?? date('Y');
$filterMonth = $_GET['month'] ?? '';
$filterGrade = $_GET['grade'] ?? '';
$filterStaff = $_GET['staff_id'] ?? '';
$filterTeam = $_GET['team'] ?? '';
$filterWarn = $_GET['warn'] ?? '';

$saleYears = $db->query("SELECT DISTINCT LEFT(sale_month,4) FROM sales ORDER BY 1 DESC")->fetchAll(PDO::FETCH_COLUMN);
if(!$saleYears) $saleYears=[date('Y')];
$months = $db->query("SELECT DISTINCT sale_month FROM sales WHERE LEFT(sale_month,4)='{$fYear}' ORDER BY sale_month DESC")->fetchAll(PDO::FETCH_COLUMN);
$grades = $db->query("SELECT grade_name FROM grade_master WHERE is_active=1 AND grade_name NOT IN('대표') ORDER BY sort_order")->fetchAll(PDO::FETCH_COLUMN);
$teamList = $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);
$staffList = $db->query("SELECT s.staff_id, s.name FROM staff s LEFT JOIN user_account ua ON s.staff_id=ua.staff_id WHERE s.is_active=1 AND (ua.user_role IS NULL OR ua.user_role NOT IN('ceo','sysadmin','manager')) ORDER BY s.name")->fetchAll();

// 전체 인원 조회 (경영진 제외)
$staffSql = "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.team NOT IN('경영진','경영지원','오너','시스템')";
$staffParams = [];
if ($filterGrade) { $staffSql .= " AND st.grade = ?"; $staffParams[] = $filterGrade; }
if ($filterStaff) { $staffSql .= " AND st.staff_id = ?"; $staffParams[] = $filterStaff; }
if ($filterTeam) { $staffSql .= " AND st.team = ?"; $staffParams[] = $filterTeam; }
if ($perm['view_scope'] === 'own') { $staffSql .= " AND st.staff_id = ?"; $staffParams[] = $user['staff_id']; }
elseif ($perm['view_scope'] === 'team') { $myT=$db->prepare("SELECT team FROM staff WHERE staff_id=?"); $myT->execute([$user['staff_id']]); $tN=$myT->fetchColumn(); if($tN){$staffSql.=" AND st.team=?";$staffParams[]=$tN;} }
$staffSql .= " ORDER BY st.team, st.name";
$staffStmt = $db->prepare($staffSql); $staffStmt->execute($staffParams);
$allStaff = $staffStmt->fetchAll();

// 각 인원별 실적 조회
$rows = [];
foreach ($allStaff as $st) {
    $saleSql = "SELECT COUNT(*) AS actual_deals, COALESCE(SUM(s.revenue),0) AS actual_amount, COALESCE(SUM(s.net_profit),0) AS actual_profit
        FROM sales s WHERE s.staff_id=? AND s.stage IN('계약','촬영','방송완료') AND LEFT(COALESCE(s.sale_month, DATE_FORMAT(s.created_at,'%Y-%m')),4)=?";
    $saleP = [$st['staff_id'], $fYear];
    if ($filterMonth) { $saleSql .= " AND COALESCE(s.sale_month, DATE_FORMAT(s.created_at,'%Y-%m'))=?"; $saleP[] = $filterMonth; }
    $saleStmt = $db->prepare($saleSql); $saleStmt->execute($saleP);
    $sr = $saleStmt->fetch();
    $rows[] = array_merge($st, [
        'sale_month' => $filterMonth ?: $fYear . '-' . date('m'),
        'actual_deals' => (int)($sr['actual_deals'] ?? 0),
        'actual_amount' => (int)($sr['actual_amount'] ?? 0),
        'actual_profit' => (int)($sr['actual_profit'] ?? 0),
    ]);
}

// 데이터 가공
$kpis = []; $warnTypes = [];
foreach ($rows as $r) {
    $tDeals = $pDealTarget; $tAmount = $pProfitTarget;
    $dealRate = $tDeals > 0 ? round($r['actual_deals'] / $tDeals * 100, 1) : 0;
    $amtRate = $tAmount > 0 ? round(($r['actual_profit'] ?? 0) / $tAmount * 100, 1) : 0;
    $warn = $r['actual_deals'] < $pWarnThreshold ? '⚠ '.$pWarnThreshold.'건 미만' : ($tDeals > 0 && $r['actual_deals'] / $tDeals < 0.5 ? '⚠ 달성률 50% 미만' : '정상');
    if (!in_array($warn, $warnTypes)) $warnTypes[] = $warn;
    $kpis[] = array_merge($r, ['target_deals'=>$tDeals, 'target_amount'=>$tAmount, 'deal_rate_pct'=>$dealRate, 'amount_rate_pct'=>$amtRate, 'kpi_warn'=>$warn]);
}
usort($kpis, fn($a,$b) => $b['actual_profit'] <=> $a['actual_profit']);

// 경고 필터 적용
if ($filterWarn) {
    $kpis = array_values(array_filter($kpis, fn($k) => $k['kpi_warn'] === $filterWarn));
}

// 페이지네이션
$totalCnt = count($kpis);
$perPage = 20; $page = max(1,(int)($_GET['page']??1));
$totalPages = max(1, ceil($totalCnt / $perPage));
$offset = ($page - 1) * $perPage;
$kpisPage = array_slice($kpis, $offset, $perPage);
?>
<div class="page-header"><h2>🎯 KPI 달성률</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;flex-wrap:wrap;">
        <select name="year" onchange="this.form.submit()" class="dash-filter" style="width:7rem;">
            <?php foreach($saleYears 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="grade" onchange="this.form.submit()" class="dash-filter" style="width:7rem;">
            <option value="">직급 전체</option>
            <?php foreach($grades as $g): ?><option value="<?= $g ?>" <?= $filterGrade===$g?'selected':'' ?>><?= $g ?></option><?php endforeach; ?>
        </select>
        <select name="team" onchange="this.form.submit()" class="dash-filter" style="width:8rem;">
            <option value="">팀 전체</option>
            <?php foreach($teamList as $t): ?><option value="<?= $t ?>" <?= $filterTeam===$t?'selected':'' ?>><?= htmlspecialchars($t) ?></option><?php endforeach; ?>
        </select>
        <select name="staff_id" onchange="this.form.submit()" class="dash-filter" style="width:9rem;">
            <option value="">담당자 전체</option>
            <?php foreach($staffList as $s): ?><option value="<?= $s['staff_id'] ?>" <?= $filterStaff==$s['staff_id']?'selected':'' ?>><?= htmlspecialchars($s['name']) ?></option><?php endforeach; ?>
        </select>
        <select name="warn" onchange="this.form.submit()" class="dash-filter" style="width:10rem;">
            <option value="">경고 전체</option>
            <?php foreach($warnTypes as $w): ?><option value="<?= $w ?>" <?= $filterWarn===$w?'selected':'' ?>><?= $w ?></option><?php endforeach; ?>
        </select>
        <?php if ($pProfitTarget > 0): ?>
        <span style="font-size:var(--fs-xs);color:var(--text-tertiary);margin-left:auto;">1인 목표: <?= $pDealTarget ?>건 / <?= number_format($pProfitTarget) ?>원</span>
        <?php endif; ?>
    </form>
</div>

<div class="card">
    <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><th>경고</th></tr></thead>
        <tbody>
        <?php foreach ($kpisPage as $k): ?>
        <tr>
            <td><strong><?= htmlspecialchars($k['name']) ?></strong></td>
            <td><span class="badge badge-info"><?= $k['grade'] ?></span></td>
            <td><?= htmlspecialchars($k['team']) ?></td>
            <td><?= $k['sale_month'] ?></td>
            <td><?= $k['target_deals'] ?></td>
            <td><strong><?= $k['actual_deals'] ?></strong></td>
            <td style="color:<?= $k['deal_rate_pct']>=100?'var(--success)':'var(--warning)' ?>;"><?= $k['deal_rate_pct'] ?>%</td>
            <td class="text-right font-mono"><?= number_format($k['target_amount']) ?></td>
            <td class="text-right font-mono"><?= number_format($k['actual_profit'] ?? 0) ?></td>
            <td style="color:<?= $k['amount_rate_pct']>=100?'var(--success)':'var(--warning)' ?>;"><?= $k['amount_rate_pct'] ?>%</td>
            <td><?php if (str_contains($k['kpi_warn'],'⚠')): ?><span class="badge badge-danger"><?= $k['kpi_warn'] ?></span><?php else: ?><span class="badge badge-success"><?= $k['kpi_warn'] ?></span><?php endif; ?></td>
        </tr>
        <?php endforeach; ?>
        <?php if (empty($kpisPage)): ?><tr><td colspan="11" style="padding:2rem;color:var(--text-tertiary);">데이터가 존재하지 않습니다.</td></tr><?php endif; ?>
        </tbody>
    </table></div>
    <?php if ($totalCnt > 0): ?>
    <div style="display:flex;justify-content:center;align-items:center;gap:0.5rem;padding:1rem;">
        <span style="font-size:var(--fs-xs);color:var(--text-tertiary);">총 <?= $totalCnt ?>건 (<?= $page ?>/<?= $totalPages ?>)</span>
        <?php if ($totalPages > 1): ?>
        <?php if ($page>1): ?><a href="?<?= http_build_query(array_merge($_GET,['page'=>$page-1])) ?>" class="btn btn-outline" style="padding:0.25rem 0.5rem;font-size:var(--fs-xs);">‹ 이전</a><?php endif; ?>
        <?php for($p=max(1,$page-2);$p<=min($totalPages,$page+2);$p++): ?>
        <a href="?<?= http_build_query(array_merge($_GET,['page'=>$p])) ?>" style="padding:0.25rem 0.5rem;font-size:var(--fs-xs);border-radius:4px;text-decoration:none;<?= $p===$page?'background:var(--accent);color:var(--bg-color);font-weight:700;':'color:var(--text-secondary);' ?>"><?= $p ?></a>
        <?php endfor; ?>
        <?php if ($page<$totalPages): ?><a href="?<?= http_build_query(array_merge($_GET,['page'=>$page+1])) ?>" class="btn btn-outline" style="padding:0.25rem 0.5rem;font-size:var(--fs-xs);">다음 ›</a><?php endif; ?>
        <?php endif; ?>
    </div>
    <?php endif; ?>
</div>

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