<?php
header('Content-Type: text/plain; charset=utf-8');
try {
    require_once __DIR__ . '/../db_conn.php';
    $pdo = db_conn();
    $pdo->exec("SET NAMES 'utf8mb4'");

    // 1. edu_codes에서 keyword 관련 group_code 확인
    echo "=== edu_codes: keyword 관련 group_code 목록 ===\n";
    $stmt = $pdo->query("SELECT group_code, COUNT(*) as cnt FROM edu_codes WHERE group_code LIKE '%KW%' OR base_code LIKE 'KW%' GROUP BY group_code");
    foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $r) {
        echo "group_code={$r['group_code']} => {$r['cnt']}건\n";
    }

    echo "\n=== edu_codes: KW로 시작하는 base_code 전체 ===\n";
    $stmt = $pdo->query("SELECT group_code, base_code, code_name FROM edu_codes WHERE base_code LIKE 'KW%' ORDER BY group_code, base_code LIMIT 30");
    foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $r) {
        echo "[{$r['group_code']}] {$r['base_code']} => {$r['code_name']}\n";
    }

    echo "\n=== edu_content_keywords: keyword_code 분포 (상위 10개) ===\n";
    $stmt = $pdo->query("SELECT keyword_code, COUNT(*) as cnt FROM edu_content_keywords GROUP BY keyword_code ORDER BY cnt DESC LIMIT 10");
    foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $r) {
        echo "{$r['keyword_code']} => {$r['cnt']}건\n";
    }

    echo "\n=== edu_contents: 전체 / is_offer=1 / is_offer!=1 건수 ===\n";
    $total  = $pdo->query("SELECT COUNT(*) FROM edu_contents WHERE is_active=1 OR is_active IS NULL")->fetchColumn();
    $offer  = $pdo->query("SELECT COUNT(*) FROM edu_contents WHERE is_offer=1 AND (is_active=1 OR is_active IS NULL)")->fetchColumn();
    $normal = $pdo->query("SELECT COUNT(*) FROM edu_contents WHERE (is_offer IS NULL OR is_offer!=1) AND (is_active=1 OR is_active IS NULL)")->fetchColumn();
    echo "전체={$total}, offer(Pick)={$offer}, 일반={$normal}\n";

    echo "\n=== 일반 영상 중 keyword 매핑 있는 건수 ===\n";
    $cnt = $pdo->query("SELECT COUNT(DISTINCT c.content_id) FROM edu_contents c JOIN edu_content_keywords ck ON ck.content_id=c.content_id WHERE (c.is_offer IS NULL OR c.is_offer!=1) AND (c.is_active=1 OR c.is_active IS NULL)")->fetchColumn();
    echo "키워드 매핑 있는 일반 영상={$cnt}\n";

} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}