<?php
require_once __DIR__ . '/../../config/database.php';
require_once __DIR__ . '/../../includes/header.php';
$db = (new Database())->getConnection();
$db->exec("CREATE TABLE IF NOT EXISTS mkt_roi (roi_id INT AUTO_INCREMENT PRIMARY KEY, campaign_id INT, calc_month VARCHAR(7), total_cost DECIMAL(15,0) DEFAULT 0, total_revenue DECIMAL(15,0) DEFAULT 0, roi_percent DECIMAL(8,2) DEFAULT 0, ctr DECIMAL(8,4) DEFAULT 0, cvr DECIMAL(8,4) DEFAULT 0, cpa DECIMAL(15,0) DEFAULT 0, roas DECIMAL(8,2) DEFAULT 0, calc_at DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uk_camp_month(campaign_id,calc_month))");
$db->exec("CREATE TABLE IF NOT EXISTS mkt_campaign (campaign_id INT AUTO_INCREMENT PRIMARY KEY, campaign_name VARCHAR(100), campaign_type VARCHAR(20), channel VARCHAR(50), start_date DATE, end_date DATE, budget DECIMAL(15,0) DEFAULT 0, status VARCHAR(10) DEFAULT '기획', staff_id INT, description TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)");

$rois = $db->query("SELECT r.*, c.campaign_name, c.campaign_type FROM mkt_roi r LEFT JOIN mkt_campaign c ON r.campaign_id=c.campaign_id ORDER BY r.calc_month DESC, r.roi_percent DESC")->fetchAll();

// 요약
$summary = $db->query("SELECT COUNT(DISTINCT campaign_id) camps, SUM(total_cost) tc, SUM(total_revenue) tr, AVG(roi_percent) avg_roi, AVG(roas) avg_roas FROM mkt_roi")->fetch();
?>
<div class="page-header"><h2>📈 ROI 분석</h2><div class="breadcrumb">마케팅 &gt; ROI 분석</div></div>

<div style="display:grid;grid-template-columns:repeat(auto-fit,minmax(10rem,1fr));gap:1rem;margin-bottom:1.25rem;">
    <div class="card" style="padding:1rem;text-align:center;">
        <div style="font-size:var(--fs-xs);color:var(--text-tertiary);">분석 캠페인</div>
        <div style="font-size:var(--fs-2xl);font-weight:800;color:var(--accent-text);"><?= $summary['camps']??0 ?></div>
    </div>
    <div class="card" style="padding:1rem;text-align:center;">
        <div style="font-size:var(--fs-xs);color:var(--text-tertiary);">총 투자</div>
        <div style="font-size:var(--fs-xl);font-weight:800;color:var(--text-primary);"><?= number_format(($summary['tc']??0)/10000) ?>만</div>
    </div>
    <div class="card" style="padding:1rem;text-align:center;">
        <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['tr']??0)/10000) ?>만</div>
    </div>
    <div class="card" style="padding:1rem;text-align:center;">
        <div style="font-size:var(--fs-xs);color:var(--text-tertiary);">평균 ROI</div>
        <div style="font-size:var(--fs-2xl);font-weight:800;color:<?= ($summary['avg_roi']??0)>=0?'var(--success)':'var(--accent)' ?>;"><?= round($summary['avg_roi']??0,1) ?>%</div>
    </div>
    <div class="card" style="padding:1rem;text-align:center;">
        <div style="font-size:var(--fs-xs);color:var(--text-tertiary);">평균 ROAS</div>
        <div style="font-size:var(--fs-2xl);font-weight:800;color:var(--text-primary);"><?= round($summary['avg_roas']??0,2) ?>x</div>
    </div>
</div>

<div class="card">
    <div class="card-header"><h3>캠페인별 ROI 상세</h3></div>
    <div style="overflow-x:auto;">
    <table class="data-table">
        <thead><tr><th>캠페인</th><th>유형</th><th>월</th><th>투자</th><th>수익</th><th>ROI</th><th>CTR</th><th>CVR</th><th>CPA</th><th>ROAS</th></tr></thead>
        <tbody>
        <?php foreach($rois as $r): ?>
        <tr>
            <td><strong><?= htmlspecialchars($r['campaign_name']??'-') ?></strong></td>
            <td><span class="badge badge-info"><?= $r['campaign_type']??'-' ?></span></td>
            <td><?= $r['calc_month'] ?></td>
            <td style="text-align:right;"><?= number_format($r['total_cost']) ?></td>
            <td style="text-align:right;"><?= number_format($r['total_revenue']) ?></td>
            <td style="text-align:right;font-weight:700;color:<?= $r['roi_percent']>=0?'var(--success)':'var(--accent)' ?>;"><?= $r['roi_percent'] ?>%</td>
            <td style="text-align:right;"><?= $r['ctr'] ?>%</td>
            <td style="text-align:right;"><?= $r['cvr'] ?>%</td>
            <td style="text-align:right;"><?= number_format($r['cpa']) ?></td>
            <td style="text-align:right;font-weight:600;"><?= $r['roas'] ?>x</td>
        </tr>
        <?php endforeach; ?>
        <?php if(!$rois): ?><tr><td colspan="10" style="text-align:center;color:var(--text-tertiary);">ROI 데이터가 없습니다. 실적을 먼저 입력하세요.</td></tr><?php endif; ?>
        </tbody>
    </table>
    </div>
</div>
<?php require_once __DIR__ . '/../../includes/footer.php'; ?>
