<?php
// pages/profit.php — 순이익 자동 집계 (PROFIT_ENGINE)
require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/../includes/header.php';

$db = (new Database())->getConnection();
$user = currentUser();
$perm = getMenuPerm($db, 'profit', $user['role']);
$filterMonth = $_GET['month'] ?? '';
$filterName = $_GET['staff_id'] ?? '';
$filterTeam = $_GET['team'] ?? '';
$staffOpts = $db->query("SELECT s.staff_id, s.name, s.team 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.team, s.name")->fetchAll();
$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);
$gradeList = $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);
$filterGrade = $_GET['grade'] ?? '';

$profitYears = $db->query("SELECT DISTINCT LEFT(DATE_FORMAT(contract_date,'%Y-%m'),4) FROM sales WHERE contract_date IS NOT NULL ORDER BY 1 DESC")->fetchAll(PDO::FETCH_COLUMN);
if (!$profitYears) $profitYears = [date('Y')];
$fYear = $_GET['year'] ?? date('Y');
$months = $db->query("SELECT DISTINCT DATE_FORMAT(contract_date,'%Y-%m') AS sm FROM sales WHERE contract_date IS NOT NULL AND LEFT(DATE_FORMAT(contract_date,'%Y-%m'),4)='{$fYear}' ORDER BY sm DESC")->fetchAll(PDO::FETCH_COLUMN);

// 정책 목표
try { $pTargets = $db->query("SELECT rule_key, rule_value FROM policy_rule WHERE rule_key IN ('revenue_target_monthly','profit_target_monthly')")->fetchAll(PDO::FETCH_KEY_PAIR); } catch(Exception $e) { $pTargets = []; }
$revTarget = (int)str_replace(',', '', $pTargets['revenue_target_monthly'] ?? '0');
$profitTarget = (int)str_replace(',', '', $pTargets['profit_target_monthly'] ?? '0');
$totalStaff = (int)$db->query("SELECT COUNT(*) FROM staff WHERE 1=1")->fetchColumn() ?: 1;

// 팀별: team_master 기준 직접 쿼리 (경영진 제외)
$allTeamNames = $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);
$teamsRaw = [];
foreach ($allTeamNames as $tn) {
    $tSql = "SELECT 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, COUNT(*) AS deal_count, 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('계약','촬영','방송완료')";
    $tP = [$tn];
    if ($filterMonth) { $tSql .= " AND COALESCE(s.sale_month, DATE_FORMAT(s.contract_date,'%Y-%m'), DATE_FORMAT(s.created_at,'%Y-%m'))=?"; $tP[] = $filterMonth; }
    else { $tSql .= " AND LEFT(COALESCE(s.sale_month, DATE_FORMAT(s.contract_date,'%Y-%m'), DATE_FORMAT(s.created_at,'%Y-%m')),4)=?"; $tP[] = $fYear; }
    if ($filterTeam && $filterTeam !== $tn) continue;
    $tStmt = $db->prepare($tSql); $tStmt->execute($tP); $tr = $tStmt->fetch();
    $tr['team'] = $tn;
    // 팀 총 인원
    $tr['total_members'] = (int)$db->query("SELECT COUNT(*) FROM staff WHERE team='{$tn}' AND is_active=1")->fetchColumn();
    $teamsRaw[] = $tr;
}
usort($teamsRaw, fn($a,$b) => $b['total_net_profit'] <=> $a['total_net_profit']);
$teamTotalCnt = count($teamsRaw);
$teamPerPage = 10; $teamPage = max(1,(int)($_GET['tp']??1)); $teamTotalPages = max(1,ceil($teamTotalCnt/$teamPerPage)); $teamOffset = ($teamPage-1)*$teamPerPage;
$teams = array_slice($teamsRaw, $teamOffset, $teamPerPage);

// 개인별 카운트 + 페이지네이션
$pcSql = "SELECT COUNT(*) FROM v_profit_by_person WHERE 1=1"; $pcP = [];
if ($filterMonth) { $pcSql .= " AND sale_month = ?"; $pcP[] = $filterMonth; }
if ($filterName) { $pcSql .= " AND staff_id = ?"; $pcP[] = $filterName; }
if ($filterTeam) { $pcSql .= " AND team = ?"; $pcP[] = $filterTeam; }
if ($filterGrade) { $pcSql .= " AND grade = ?"; $pcP[] = $filterGrade; }
if ($perm['view_scope'] === 'own') { $pcSql .= " AND staff_id = ?"; $pcP[] = $user['staff_id']; }
elseif ($perm['view_scope'] === 'team') { $myT=$db->prepare("SELECT team FROM staff WHERE staff_id=?"); $myT->execute([$user['staff_id']]); $tName=$myT->fetchColumn(); if($tName){$pcSql.=" AND team=?";$pcP[]=$tName;} }
$pcS = $db->prepare($pcSql); $pcS->execute($pcP); $persTotalCnt = (int)$pcS->fetchColumn();
$persPerPage = 10; $persPage = max(1,(int)($_GET['pp']??1)); $persTotalPages = max(1,ceil($persTotalCnt/$persPerPage)); $persOffset = ($persPage-1)*$persPerPage;

$sql = "SELECT * FROM v_profit_by_person WHERE 1=1"; $params = [];
if ($filterMonth) { $sql .= " AND sale_month = ?"; $params[] = $filterMonth; }
if ($filterName) { $sql .= " AND staff_id = ?"; $params[] = $filterName; }
if ($filterTeam) { $sql .= " AND team = ?"; $params[] = $filterTeam; }
if ($filterGrade) { $sql .= " AND grade = ?"; $params[] = $filterGrade; }
if ($perm['view_scope'] === 'own') { $sql .= " AND staff_id = ?"; $params[] = $user['staff_id']; }
elseif ($perm['view_scope'] === 'team') { if(isset($tName)&&$tName){$sql.=" AND team=?";$params[]=$tName;} }
$sql .= " ORDER BY total_net_profit DESC LIMIT $persPerPage OFFSET $persOffset";
$stmt = $db->prepare($sql); $stmt->execute($params); $persons = $stmt->fetchAll();

// 본부 합계
// 본부 합계 - 전체일 때 누적 합산
if ($filterMonth) {
    $sql3 = "SELECT * FROM v_profit_total WHERE sale_month = ?"; $params3 = [$filterMonth];
    $stmt3 = $db->prepare($sql3); $stmt3->execute($params3); $totals = $stmt3->fetchAll();
    $profitMonthCount = 1;
} else {
    // 전체 누적
    $stmt3 = $db->prepare("SELECT '전체' AS sale_month, COALESCE(SUM(deal_count),0) AS deal_count, COALESCE(SUM(total_revenue),0) AS total_revenue, COALESCE(SUM(total_cost),0) AS total_cost, COALESCE(SUM(total_net_profit),0) AS total_net_profit FROM v_profit_total");
    $stmt3->execute(); $totals = $stmt3->fetchAll();
    // 누적 월수 카운트
    $profitMonthCount = (int)$db->query("SELECT COUNT(DISTINCT sale_month) FROM v_profit_total")->fetchColumn() ?: 1;
}

// 요약 합계
$sumSql = "SELECT COALESCE(SUM(deal_count),0) AS sd, COALESCE(SUM(total_revenue),0) AS sr, COALESCE(SUM(total_cost),0) AS sc, COALESCE(SUM(total_net_profit),0) AS sp FROM v_profit_by_person WHERE 1=1"; $sumP=[];
if ($filterMonth) { $sumSql .= " AND sale_month = ?"; $sumP[] = $filterMonth; }
$ssS = $db->prepare($sumSql); $ssS->execute($sumP); $sums = $ssS->fetch();
$sumDeals=(int)$sums['sd']; $sumRevenue=(int)$sums['sr']; $sumCost=(int)$sums['sc']; $sumProfit=(int)$sums['sp'];
?>
<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;flex-wrap:wrap;">
        <select name="year" onchange="this.form.submit()" class="dash-filter" style="width:7rem;">
            <?php foreach($profitYears 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="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:10rem;">
            <option value="">담당자 전체</option>
            <?php foreach ($staffOpts as $so): ?><option value="<?= $so['staff_id'] ?>" <?= $filterName==$so['staff_id']?'selected':'' ?>><?= htmlspecialchars($so['name']) ?></option><?php endforeach; ?>
        </select>
        <select name="grade" onchange="this.form.submit()" class="dash-filter" style="width:7rem;">
            <option value="">직급 전체</option>
            <?php foreach ($gradeList as $g): ?><option value="<?= $g ?>" <?= $filterGrade===$g?'selected':'' ?>><?= htmlspecialchars($g) ?></option><?php endforeach; ?>
        </select>
    </form>
</div>

<div class="stat-grid" style="grid-template-columns:repeat(4,1fr);margin-bottom:1rem;">
    <div class="stat-card yellow"><div class="stat-label">총 성사 건수</div><div class="stat-value"><?= number_format($sumDeals) ?></div><div class="stat-sub">건</div></div>
    <div class="stat-card blue"><div class="stat-label">총 매출</div><div class="stat-value"><?= number_format($sumRevenue/10000) ?></div><div class="stat-sub">만원</div></div>
    <div class="stat-card"><div class="stat-label">총 비용</div><div class="stat-value"><?= number_format($sumCost/10000) ?></div><div class="stat-sub">만원</div></div>
    <div class="stat-card green"><div class="stat-label">총 순수이익</div><div class="stat-value"><?= number_format($sumProfit/10000) ?></div><div class="stat-sub">만원</div></div>
</div>

<?php if (!empty($totals)): ?>
<div class="stat-grid">
    <?php foreach ($totals as $t):
        $profitTargetVal = (int)str_replace(',', '', $pTargets['revenue_target_monthly'] ?? '0') * $profitMonthCount;
        $profitRate = $profitTargetVal > 0 ? round($t['total_net_profit'] / $profitTargetVal * 100, 1) : 0;
    ?>
    <div class="stat-card green">
        <div class="stat-label"><?= $t['sale_month'] ?: '전체' ?> 본부 순이익</div>
        <div class="stat-value"><?= number_format($t['total_net_profit']) ?></div>
        <div class="stat-sub"><?= $t['deal_count'] ?>건 · <?= $totalStaff ?>명</div>
        <?php if ($profitTargetVal > 0): ?>
        <div class="stat-sub" style="color:<?= $profitRate>=100?'var(--success)':'var(--warning)' ?>;">목표 <?= number_format($profitTargetVal) ?>원 대비 <?= $profitRate ?>%</div>
        <?php endif; ?>
    </div>
    <?php endforeach; ?>
</div>
<?php endif; ?>

<!-- 팀별 영업 이익 (위로 배치) -->
<div class="card">
    <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></tr></thead>
        <tbody>
        <?php foreach ($teams as $t):
            $mc=(int)($t['total_members']?:$t['members']?:1);
            $trt=$revTarget; $tr2=$trt>0?round($t['total_net_profit']/$trt*100,1):0;
        ?>
        <tr>
            <td><strong><?= htmlspecialchars($t['team']) ?></strong></td><td><?= $mc ?>명</td><td><?= $t['deal_count'] ?></td>
            <td class="text-right font-mono"><?= number_format($t['total_revenue']) ?></td><td class="text-right font-mono"><?= number_format($t['total_cost']) ?></td>
            <td class="text-right font-mono"><strong><?= number_format($t['total_net_profit']) ?></strong></td>
            <td class="text-right font-mono" style="color:var(--text-tertiary);"><?= number_format($trt) ?></td>
            <td style="color:<?= $tr2>=100?'var(--success)':'var(--warning)' ?>;"><?= $tr2 ?>%</td>
        </tr>
        <?php endforeach; ?>
        <?php if (empty($teams)): ?><tr><td colspan="8" style="padding:2rem;color:var(--text-tertiary);">데이터가 존재하지 않습니다.</td></tr><?php endif; ?>
        </tbody>
    </table></div>
    <?php if ($teamTotalPages > 1): ?>
    <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);">총 <?= $teamTotalCnt ?>건</span>
        <?php if ($teamPage>1): ?><a href="?<?= http_build_query(array_merge($_GET,['tp'=>$teamPage-1])) ?>" class="btn btn-outline" style="padding:0.25rem 0.5rem;font-size:var(--fs-xs);">‹</a><?php endif; ?>
        <?php for($p=max(1,$teamPage-2);$p<=min($teamTotalPages,$teamPage+2);$p++): ?>
        <a href="?<?= http_build_query(array_merge($_GET,['tp'=>$p])) ?>" style="padding:0.25rem 0.5rem;font-size:var(--fs-xs);border-radius:4px;text-decoration:none;<?= $p===$teamPage?'background:var(--accent);color:var(--bg-color);font-weight:700;':'color:var(--text-secondary);' ?>"><?= $p ?></a>
        <?php endfor; ?>
        <?php if ($teamPage<$teamTotalPages): ?><a href="?<?= http_build_query(array_merge($_GET,['tp'=>$teamPage+1])) ?>" class="btn btn-outline" style="padding:0.25rem 0.5rem;font-size:var(--fs-xs);">›</a><?php endif; ?>
    </div>
    <?php endif; ?>
</div>

<!-- 개인별 영업 이익 -->
<div class="card">
    <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></tr></thead>
        <tbody>
        <?php foreach ($persons as $p): ?>
        <tr>
            <td><strong><?= htmlspecialchars($p['name']) ?></strong></td><td><span class="badge badge-info"><?= $p['grade'] ?></span></td><td><?= htmlspecialchars($p['team']) ?></td>
            <td><?= $p['sale_month'] ?: '-' ?></td><td><?= $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 class="text-right font-mono"><?= number_format(($p['avg_deal_price'] ?? 0)) ?></td>
        </tr>
        <?php endforeach; ?>
        <?php if (empty($persons)): ?><tr><td colspan="9" style="padding:2rem;color:var(--text-tertiary);">데이터가 존재하지 않습니다.</td></tr><?php endif; ?>
        </tbody>
    </table></div>
    <?php if ($persTotalPages > 1): ?>
    <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);">총 <?= $persTotalCnt ?>건</span>
        <?php if ($persPage>1): ?><a href="?<?= http_build_query(array_merge($_GET,['pp'=>$persPage-1])) ?>" class="btn btn-outline" style="padding:0.25rem 0.5rem;font-size:var(--fs-xs);">‹</a><?php endif; ?>
        <?php for($p=max(1,$persPage-2);$p<=min($persTotalPages,$persPage+2);$p++): ?>
        <a href="?<?= http_build_query(array_merge($_GET,['pp'=>$p])) ?>" style="padding:0.25rem 0.5rem;font-size:var(--fs-xs);border-radius:4px;text-decoration:none;<?= $p===$persPage?'background:var(--accent);color:var(--bg-color);font-weight:700;':'color:var(--text-secondary);' ?>"><?= $p ?></a>
        <?php endfor; ?>
        <?php if ($persPage<$persTotalPages): ?><a href="?<?= http_build_query(array_merge($_GET,['pp'=>$persPage+1])) ?>" class="btn btn-outline" style="padding:0.25rem 0.5rem;font-size:var(--fs-xs);">›</a><?php endif; ?>
    </div>
    <?php endif; ?>
</div>

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