<?php
require_once __DIR__ . '/../../config/database.php';
require_once __DIR__ . '/../../includes/header.php';
$user = currentUser(); $db = (new Database())->getConnection();

$db->exec("CREATE TABLE IF NOT EXISTS mkt_performance (perf_id INT AUTO_INCREMENT PRIMARY KEY, campaign_id INT NOT NULL, perf_month VARCHAR(7) NOT NULL, impressions BIGINT DEFAULT 0, clicks INT DEFAULT 0, conversions INT DEFAULT 0, revenue DECIMAL(15,0) DEFAULT 0, cost DECIMAL(15,0) DEFAULT 0, leads INT DEFAULT 0, staff_id INT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP)");
$db->exec("CREATE TABLE IF NOT EXISTS mkt_campaign (campaign_id INT AUTO_INCREMENT PRIMARY KEY, campaign_name VARCHAR(100) NOT NULL, campaign_type ENUM('디지털','오프라인','SNS','브랜드','프로모션','기타') DEFAULT '디지털', channel VARCHAR(50), start_date DATE, end_date DATE, budget DECIMAL(15,0) DEFAULT 0, status ENUM('기획','진행','완료','중단') DEFAULT '기획', staff_id INT, description TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)");

if ($_SERVER['REQUEST_METHOD']==='POST' && $_POST['action']==='add') {
    $vals = [$_POST['campaign_id'],$_POST['perf_month'],$_POST['impressions']?:0,$_POST['clicks']?:0,$_POST['conversions']?:0,$_POST['revenue']?:0,$_POST['cost']?:0,$_POST['leads']?:0,$user['staff_id']];
    // 유효성 검사
    $errors = [];
    if (!$_POST['campaign_id']) $errors[] = '캠페인을 선택하세요';
    if (!$_POST['perf_month']) $errors[] = '실적 월을 입력하세요';
    foreach (['impressions','clicks','conversions','revenue','cost','leads'] as $f) { if ($_POST[$f] && $_POST[$f] < 0) $errors[] = "$f: 음수 불가"; }
    if (!$errors) {
        $db->prepare("INSERT INTO mkt_performance (campaign_id,perf_month,impressions,clicks,conversions,revenue,cost,leads,staff_id) VALUES(?,?,?,?,?,?,?,?,?)")->execute($vals);
        // ROI 자동 계산
        $cid = $_POST['campaign_id']; $pm = $_POST['perf_month'];
        $agg = $db->prepare("SELECT SUM(cost) tc, SUM(revenue) tr, SUM(impressions) ti, SUM(clicks) tk, SUM(conversions) tv FROM mkt_performance WHERE campaign_id=? AND perf_month=?");
        $agg->execute([$cid,$pm]); $a = $agg->fetch();
        $roi = ($a['tc']>0) ? round(($a['tr']-$a['tc'])/$a['tc']*100,2) : 0;
        $ctr = ($a['ti']>0) ? round($a['tk']/$a['ti']*100,4) : 0;
        $cvr = ($a['tk']>0) ? round($a['tv']/$a['tk']*100,4) : 0;
        $cpa = ($a['tv']>0) ? round($a['tc']/$a['tv']) : 0;
        $roas = ($a['tc']>0) ? round($a['tr']/$a['tc'],2) : 0;
        $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->prepare("INSERT INTO mkt_roi (campaign_id,calc_month,total_cost,total_revenue,roi_percent,ctr,cvr,cpa,roas) VALUES(?,?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE total_cost=VALUES(total_cost),total_revenue=VALUES(total_revenue),roi_percent=VALUES(roi_percent),ctr=VALUES(ctr),cvr=VALUES(cvr),cpa=VALUES(cpa),roas=VALUES(roas),calc_at=NOW()")
             ->execute([$cid,$pm,$a['tc'],$a['tr'],$roi,$ctr,$cvr,$cpa,$roas]);
    }
}

$campaigns = $db->query("SELECT campaign_id, campaign_name FROM mkt_campaign ORDER BY campaign_name")->fetchAll();
$perfs = $db->query("SELECT p.*, c.campaign_name FROM mkt_performance p LEFT JOIN mkt_campaign c ON p.campaign_id=c.campaign_id ORDER BY p.created_at DESC LIMIT 50")->fetchAll();
?>
<div class="page-header"><h2>📊 마케팅 실적 입력</h2><div class="breadcrumb">마케팅 &gt; 실적 입력</div></div>

<?php if (!empty($errors)): ?><div class="alert alert-warning" style="margin-bottom:1rem;"><?= implode('<br>',$errors) ?></div><?php endif; ?>

<div class="card" style="margin-bottom:1.25rem;">
    <div class="card-header"><h3>실적 입력</h3></div>
    <form method="POST">
        <input type="hidden" name="action" value="add">
        <div style="display:grid;grid-template-columns:repeat(auto-fit,minmax(10rem,1fr));gap:0.75rem;">
            <div class="form-group"><label>캠페인</label><select name="campaign_id" required><option value="">선택</option><?php foreach($campaigns as $c): ?><option value="<?= $c['campaign_id'] ?>"><?= htmlspecialchars($c['campaign_name']) ?></option><?php endforeach; ?></select></div>
            <div class="form-group"><label>실적 월</label><input type="month" name="perf_month" required></div>
            <div class="form-group"><label>노출 수</label><input type="number" name="impressions" min="0" placeholder="0"></div>
            <div class="form-group"><label>클릭 수</label><input type="number" name="clicks" min="0" placeholder="0"></div>
            <div class="form-group"><label>전환 수</label><input type="number" name="conversions" min="0" placeholder="0"></div>
            <div class="form-group"><label>매출 (원)</label><input type="number" name="revenue" min="0" placeholder="0"></div>
            <div class="form-group"><label>비용 (원)</label><input type="number" name="cost" min="0" placeholder="0"></div>
            <div class="form-group"><label>리드 수</label><input type="number" name="leads" min="0" placeholder="0"></div>
        </div>
        <button type="submit" class="btn btn-primary">등록 (ROI 자동 계산)</button>
    </form>
</div>

<div class="card">
    <div class="card-header"><h3>최근 실적 (<?= count($perfs) ?>건)</h3></div>
    <div style="overflow-x:auto;">
    <table class="data-table">
        <thead><tr><th>캠페인</th><th>월</th><th>노출</th><th>클릭</th><th>전환</th><th>매출</th><th>비용</th><th>리드</th></tr></thead>
        <tbody>
        <?php foreach($perfs as $p): ?>
        <tr>
            <td><?= htmlspecialchars($p['campaign_name']??'-') ?></td>
            <td><?= $p['perf_month'] ?></td>
            <td style="text-align:right;"><?= number_format($p['impressions']) ?></td>
            <td style="text-align:right;"><?= number_format($p['clicks']) ?></td>
            <td style="text-align:right;"><?= number_format($p['conversions']) ?></td>
            <td style="text-align:right;"><?= number_format($p['revenue']) ?></td>
            <td style="text-align:right;"><?= number_format($p['cost']) ?></td>
            <td style="text-align:right;"><?= number_format($p['leads']) ?></td>
        </tr>
        <?php endforeach; ?>
        <?php if(!$perfs): ?><tr><td colspan="8" style="text-align:center;color:var(--text-tertiary);">등록된 실적이 없습니다</td></tr><?php endif; ?>
        </tbody>
    </table>
    </div>
</div>
<?php require_once __DIR__ . '/../../includes/footer.php'; ?>
