<?php
require __DIR__ . '/db_conn.php';
$pdo = db_conn();

$pdo->beginTransaction();

// Map KW100xx codes in edu_content_keywords to Korean keyword names in edu_codes.code_name.
$sql = "
INSERT INTO edu_content_keywords (
    content_id,
    keyword_code,
    is_active,
    created_by,
    created_at,
    updated_by,
    updated_at
)
SELECT
    ck.content_id,
    ec.code_name AS keyword_code,
    '1' AS is_active,
    'admin' AS created_by,
    NOW() AS created_at,
    'admin' AS updated_by,
    NOW() AS updated_at
FROM edu_content_keywords ck
JOIN edu_codes ec
  ON ec.base_code = ck.keyword_code
 AND ec.group_code = 'KW100'
LEFT JOIN edu_content_keywords ck2
  ON ck2.content_id = ck.content_id
 AND ck2.keyword_code = ec.code_name
WHERE ck.keyword_code LIKE 'KW%'
  AND ck.is_active = '1'
  AND ec.code_name IS NOT NULL
  AND ec.code_name <> ''
  AND ck2.content_id IS NULL
";

$inserted = $pdo->exec($sql);
$pdo->commit();

echo "INSERTED_ROWS|" . (int)$inserted . "\n";

$checkKeywords = ['AI', '리더십', '자기개발', '온보딩', '중간관리자'];
$stmt = $pdo->prepare("SELECT keyword_code, COUNT(*) AS cnt FROM edu_content_keywords WHERE keyword_code = ? GROUP BY keyword_code");
foreach ($checkKeywords as $kw) {
    $stmt->execute([$kw]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    $cnt = $row ? (int)$row['cnt'] : 0;
    echo "KW_COUNT|{$kw}|{$cnt}\n";
}

$sample = $pdo->query("SELECT content_id, keyword_code FROM edu_content_keywords WHERE keyword_code IN ('AI','리더십','자기개발') ORDER BY keyword_code, content_id LIMIT 20")->fetchAll(PDO::FETCH_ASSOC);
foreach ($sample as $r) {
    echo "SAMPLE|{$r['content_id']}|{$r['keyword_code']}\n";
}