<?php
/**
 * edu_goal_contents 자동 매핑 스크립트
 * category_group 기준으로 goal_code와 content_id 연결
 */
header('Content-Type: application/json; charset=utf-8');
require_once __DIR__ . '/db_conn.php';
$pdo = db_conn();

try {
    // 1. 모든 goal_code를 category_group별로 그룹화
    $goals = $pdo->query("
        SELECT goal_code, quarter 
        FROM edu_learning_goals 
        WHERE is_active = '1'
        ORDER BY goal_code
    ")->fetchAll(PDO::FETCH_ASSOC);

    // quarter를 category_group으로 맵핑하는 쿼리
    // CA200Q01 → CA200Q01, CA200Q02 → CA200Q02 등
    
    // 2. 각 quarter의 영상들을 조회
    $inserted = 0;
    foreach ($goals as $goal) {
        $goalCode = $goal['goal_code'];
        $quarter = $goal['quarter']; // 예: CA200Q01
        
        // 같은 quarter의 영상 6개 조회
        $contents = $pdo->query("
            SELECT content_id 
            FROM edu_contents 
            WHERE category_group = ? 
            AND content_id NOT IN (
                SELECT content_id FROM edu_goal_contents 
                WHERE category_group = ?
            )
            ORDER BY content_id ASC
            LIMIT 6
        ")->fetchAll(PDO::FETCH_COLUMN);
        
        // Wait - category_group이 edu_goal_contents에 없네
        // 대신 이미 다른 goal에 할당된 content를 피해야 함
        
        $contents = $pdo->query("
            SELECT content_id 
            FROM edu_contents 
            WHERE category_group = ? 
            AND content_id NOT IN (
                SELECT content_id FROM edu_goal_contents
            )
            ORDER BY content_id ASC
            LIMIT 6
        ")->fetchAll(PDO::FETCH_COLUMN);
        
        // INSERT
        $stmt = $pdo->prepare("
            INSERT INTO edu_goal_contents 
            (goal_code, content_id, is_active, sort_order) 
            VALUES (?, ?, '1', ?)
        ");
        
        foreach ($contents as $idx => $contentId) {
            $stmt->execute([$goalCode, $contentId, $idx + 1]);
            $inserted++;
        }
        
        echo "- $goalCode: " . count($contents) . "개 영상 매핑\n";
    }
    
        // 1. quarter별로 goal들을 그룹화
        $quarterGoals = $pdo->query("
            SELECT quarter, GROUP_CONCAT(goal_code ORDER BY goal_code) as goals
            FROM edu_learning_goals 
            WHERE is_active = '1'
            GROUP BY quarter
            ORDER BY quarter
        ")->fetchAll(PDO::FETCH_ASSOC);

        $inserted = 0;
    
        // 2. 각 quarter마다 처리
        foreach ($quarterGoals as $qg) {
            $quarter = $qg['quarter'];
            $goalCodes = explode(',', $qg['goals']);
        
            // 해당 quarter의 모든 영상 조회
            $stmt = $pdo->prepare("
                SELECT content_id 
                FROM edu_contents 
                WHERE category_group = ? 
                ORDER BY content_id ASC
            ");
            $stmt->execute([$quarter]);
            $allContents = $stmt->fetchAll(PDO::FETCH_COLUMN);
        
            // 3. 영상들을 goal별로 6개씩 분배
            $contentIdx = 0;
            foreach ($goalCodes as $goalCode) {
                $goalCode = trim($goalCode);
            
                // 이 goal을 위해 6개 영상 선택
                $goalContents = array_slice($allContents, $contentIdx, 6);
                $contentIdx += 6;
            
                // INSERT
                $stmtInsert = $pdo->prepare("
                    INSERT INTO edu_goal_contents 
                    (goal_code, content_id, is_active, sort_order) 
                    VALUES (?, ?, '1', ?)
                ");
            
                foreach ($goalContents as $idx => $contentId) {
                    $stmtInsert->execute([$goalCode, $contentId, $idx + 1]);
                    $inserted++;
                }
            
                echo "- {$goalCode}: " . count($goalContents) . "개 영상 (quarter: {$quarter})\n";
            }
        }
    
    echo json_encode([
        'success' => true,
        'message' => "총 {$inserted}개 행 INSERT 완료",
        'result' => "goal_code와 content_id 매핑 완료"
    ], JSON_UNESCAPED_UNICODE);
    
} catch (Exception $e) {
    echo json_encode([
        'success' => false,
        'message' => $e->getMessage()
    ], JSON_UNESCAPED_UNICODE);
}
?>