<?php
/**
 * bbs/api/videos_by_keywords.php
 * ─────────────────────────────────────────────────────────────────────
 * 활성 키워드 기반 영상 목록을 반환하는 AJAX 엔드포인트
 *
 * Request  POST application/json
 *   { "my_keywords": ["AI","리더십"], "admin_keywords": ["경제"] }
 *
 * Response application/json
 *   { "success": true, "videos": [ ...video objects... ] }
 *
 * 슬롯 구성:
 *   [0]   Pick 영상 (is_offer=1, 항상 고정)
 *   [1-5] 활성 키워드(my + admin 합집합)에 매칭되는 영상, 랜덤 최대 5개
 *         → 부족하면 랜덤 보충
 * ─────────────────────────────────────────────────────────────────────
 */
header('Content-Type: application/json; charset=utf-8');

require_once dirname(__DIR__) . '/db_conn.php';
require_once dirname(__DIR__) . '/auth.php';

edu_start_session();

$memberId    = edu_current_member_id();
$sysCompCode = (string)($_SESSION['sys_comp_code'] ?? '');

if ($memberId === '' || $sysCompCode === '') {
    echo json_encode(['success' => false, 'error' => 'not_logged_in_or_invalid_session'], JSON_UNESCAPED_UNICODE);
    exit;
}

// ── 요청 파싱 ──────────────────────────────────────────────────────
$input        = json_decode(file_get_contents('php://input'), true) ?? [];
$myKwRaw    = array_values(array_filter(array_map('trim', $input['my_keywords']    ?? [])));
$adminKwRaw = array_values(array_filter(array_map('trim', $input['admin_keywords'] ?? [])));
$allInputKw = array_values(array_unique(array_merge($myKwRaw, $adminKwRaw)));

function edu_norm_code(string $code): string
{
    return preg_replace('/[^A-Z0-9]/', '', strtoupper(trim($code)));
}

function edu_looks_like_code(string $value): bool
{
    return preg_match('/^[A-Z]{2}[A-Z0-9]{3,}$/', strtoupper(trim($value))) === 1;
}

// ── mapRow (main_data.php mapContentRow 와 동일 로직) ─────────────────
function mapRow(array $row, string $picker = ''): array
{
    global $eduCodeNameMap;

    // keyword_code 는 한글 텍스트로 저장돼 있음
    // GROUP_CONCAT 에서 이미 한글로 변환된 값이 옴
    $kwStr    = $row['keywords'] ?? '';
    $keywords = $kwStr !== '' ? array_values(array_unique(explode(',', $kwStr))) : [];

    $watchTm   = (float)($row['watch_tm']   ?? 0);
    $contentTm = (float)($row['content_tm'] ?? 0);
    $gauge     = ($contentTm > 0) ? (int)round($watchTm / $contentTm * 100) : 0;
    $gauge     = min(100, max(0, $gauge));

    $raw = trim($row['content_url'] ?? '');
    if (preg_match('/(?:v=|\.be\/)([A-Za-z0-9_-]{11})/', $raw, $m)) {
        $videoId = $m[1];
    } elseif (preg_match('/^[A-Za-z0-9_-]{11}$/', $raw)) {
        $videoId = $raw;
    } else {
        $videoId = '';
    }

    $thumbFromDb = trim($row['thumbnail_url'] ?? '');
    $url       = $videoId !== '' ? "https://www.youtube.com/watch?v={$videoId}" : '';
    $thumbnail = $thumbFromDb !== ''
        ? $thumbFromDb
        : ($videoId !== '' ? "https://img.youtube.com/vi/{$videoId}/sddefault.jpg" : '');

    $categoryCode = trim((string)($row['category_code'] ?? ''));
    // SQL COALESCE(ec.code_name, c.category_code) AS category_name 으로 가져온 한글명 직접 사용
    $categoryName = trim((string)($row['category_name'] ?? $categoryCode));

    if (($categoryName === '' || edu_looks_like_code($categoryName)) && $categoryCode !== '') {
        $mappedCategory = $eduCodeNameMap[edu_norm_code($categoryCode)] ?? '';
        if ($mappedCategory !== '') {
            $categoryName = $mappedCategory;
        }
    }

    $groupRaw = trim((string)($row['category_group'] ?? ''));
    $subcate = trim((string)($row['category_group_name'] ?? $groupRaw));
    if (($subcate === '' || edu_looks_like_code($subcate)) && $groupRaw !== '') {
        $mappedGroup = $eduCodeNameMap[edu_norm_code($groupRaw)] ?? '';
        if ($mappedGroup !== '') {
            $subcate = $mappedGroup;
        }
    }

    return [
        'id'            => $row['content_id'],
        'content_id'    => $row['content_id'],
        'url'           => $url,
        'thumbnail'     => $thumbnail,
        'category'      => $categoryName,
        'category_code' => $categoryCode,
        'subcate'       => $subcate,
        'bookmark'      => (bool)($row['is_bookmarked'] ?? false),
        'title'         => $row['title'] ?? '',
        'picker'        => $picker,
        'type'          => 'main',
        'keywords'      => $keywords,
        'gauge'         => $gauge,
        'watch_tm'      => (int)($row['watch_tm']   ?? 0),
        'content_tm'    => (int)($row['content_tm'] ?? 0),
        'all_tm'        => (int)($row['all_tm'] ?? 0),
        'watch_min'     => (int)floor(((int)($row['watch_tm'] ?? 0)) / 60),
        'content_min'   => (int)floor(((int)($row['content_tm'] ?? 0)) / 60),
        'all_min'       => (int)floor(((int)($row['all_tm'] ?? 0)) / 60),
    ];
}

try {
    $pdo = db_conn();
    $pdo->exec("SET NAMES 'utf8mb4'");

    $eduCodeNameMap = [];
    try {
        $stmtCodeMap = $pdo->query("SELECT base_code, code_name FROM edu_codes");
        foreach ($stmtCodeMap->fetchAll(PDO::FETCH_ASSOC) as $cr) {
            $k = edu_norm_code((string)($cr['base_code'] ?? ''));
            if ($k === '') continue;
            $eduCodeNameMap[$k] = (string)($cr['code_name'] ?? '');
        }
    } catch (Throwable $ignore) {
        $eduCodeNameMap = [];
    }

    // ── edu_codes 매핑 로드 (KW↔한글 양방향) ───────────────────────
    $codeStmt = $pdo->query("SELECT base_code, code_name FROM edu_codes WHERE group_code = 'KW100'");
    $kwToName = []; // KW10001 => 온보딩
    $nameToKw = []; // 온보딩 => KW10001
    foreach ($codeStmt->fetchAll(PDO::FETCH_ASSOC) as $cr) {
        $kwToName[$cr['base_code']] = $cr['code_name'];
        $nameToKw[$cr['code_name']] = $cr['base_code'];
    }

    // 입력 키워드(한글)를 KW코드로 정규화
    $allActiveKw = [];
    foreach ($allInputKw as $kw) {
        if (isset($kwToName[$kw])) {
            $allActiveKw[] = $kw;          // 이미 KW코드
        } elseif (isset($nameToKw[$kw])) {
            $allActiveKw[] = $nameToKw[$kw]; // 한글 → KW코드
        }
    }
    $allActiveKw = array_values(array_unique($allActiveKw));

    // 키워드가 비어있으면 전체 KW 코드 사용 (항상 영상 표시)
    if (empty($allActiveKw)) {
        $allActiveKw = array_keys($kwToName);
    }
    $stmtPick = $pdo->prepare("
         SELECT c.*,
             COALESCE(ec.code_name, c.category_code) AS category_name,
             COALESCE(ec_grp.code_name, c.category_group) AS category_group_name,
             COALESCE(lh.watch_tm, 0) AS watch_tm,
             COALESCE(lh.content_tm, 0) AS content_tm,
             COALESCE(lh.all_tm, 0) AS all_tm,
             CASE WHEN cw.content_id IS NOT NULL THEN 1 ELSE 0 END AS is_bookmarked,
             GROUP_CONCAT(COALESCE(kwec.code_name, ck.keyword_code) ORDER BY ck.keyword_code SEPARATOR ',') AS keywords,
             u.name AS picker_name
        FROM   edu_contents c
               LEFT JOIN edu_codes             ec ON ec.base_code   = c.category_code
               LEFT JOIN edu_codes          ec_grp ON TRIM(UPPER(ec_grp.base_code)) = TRIM(UPPER(c.category_group))
               LEFT JOIN edu_content_keywords  ck ON ck.content_id  = c.content_id
                                                  AND ck.is_active = 1
             LEFT JOIN edu_codes            kwec ON kwec.base_code = ck.keyword_code
               LEFT JOIN edu_content_offer     co ON co.offer_id    = c.offer_id
               LEFT JOIN edu_users              u  ON u.member_id    = co.member_id
                                                 AND u.sys_comp_code = co.sys_comp_code
               LEFT JOIN edu_content_wishlist   cw ON cw.member_id     = :mid_w
                                                   AND cw.sys_comp_code = :sc_w
                                                   AND cw.is_active     = 1
                                                   AND cw.content_id    = c.content_id
                              LEFT JOIN edu_learning_histories lh ON lh.content_id    = c.content_id
                                                                AND lh.member_id     = :mid_lh
                                                                AND lh.sys_comp_code = :sc_lh
                WHERE  c.is_offer  = 1
                    AND  (c.is_active = 1 OR c.is_active IS NULL)
                    AND  c.issue_type_code = 'IS10003'
        GROUP  BY c.content_id
        ORDER  BY c.sort_order, RAND()
        LIMIT  1
    ");
        $stmtPick->execute([
            ':mid_w' => $memberId,
            ':sc_w' => $sysCompCode,
                   ':mid_lh' => $memberId,
                   ':sc_lh' => $sysCompCode,
        ]);
    $pickRow   = $stmtPick->fetch();
    $pickVideo = $pickRow ? mapRow($pickRow, $pickRow['picker_name'] ?? '동료') : null;

    $usedIds = $pickVideo ? [$pickVideo['id']] : [];

    // ── 활성 키워드 매칭 영상 (슬롯 1~5) ─────────────────────────
    $kwVideos = [];
    if (!empty($allActiveKw)) {
        $phKw      = implode(',', array_fill(0, count($allActiveKw), '?'));
        $excClause = !empty($usedIds)
            ? 'AND c.content_id NOT IN (' . implode(',', array_fill(0, count($usedIds), '?')) . ')'
            : '';

        $stmt = $pdo->prepare("
            SELECT c.*,
                   COALESCE(ec.code_name, c.category_code) AS category_name,
                   COALESCE(ec_grp.code_name, c.category_group) AS category_group_name,
                   lh.watch_tm,
                   lh.content_tm,
                     lh.all_tm,
                   CASE WHEN cw.content_id IS NOT NULL THEN 1 ELSE 0 END AS is_bookmarked,
                     GROUP_CONCAT(COALESCE(kwec.code_name, ck.keyword_code) ORDER BY ck.keyword_code SEPARATOR ',') AS keywords
            FROM   edu_contents c
                   LEFT JOIN edu_codes             ec ON ec.base_code      = c.category_code
                   LEFT JOIN edu_codes          ec_grp ON TRIM(UPPER(ec_grp.base_code)) = TRIM(UPPER(c.category_group))
                   LEFT JOIN edu_content_keywords  ck ON ck.content_id     = c.content_id
                                                      AND ck.is_active = 1
                     LEFT JOIN edu_codes            kwec ON kwec.base_code   = ck.keyword_code
                   LEFT JOIN edu_learning_histories lh ON lh.content_id    = c.content_id
                                                   AND lh.member_id     = ?
                                                   AND lh.sys_comp_code = ?
                   LEFT JOIN edu_content_wishlist   cw ON cw.member_id     = ?
                                                      AND cw.sys_comp_code = ?
                                                      AND cw.is_active     = 1
                                                      AND cw.content_id    = c.content_id
            WHERE  (c.is_active = 1 OR c.is_active IS NULL)
              AND  (c.is_offer IS NULL OR c.is_offer != 1)
              AND  c.category_code IN ('CA10004', 'CA10005')
              AND  c.content_id IN (
                       SELECT DISTINCT content_id
                                             FROM   edu_content_keywords
                                             WHERE  keyword_code IN ({$phKw})
                                                 AND  is_active = 1
                   )
              {$excClause}
            GROUP  BY c.content_id
            ORDER  BY RAND()
            LIMIT  5
        ");

        $params = [$memberId, $sysCompCode, $memberId, $sysCompCode];
        $params = array_merge($params, $allActiveKw);
        if (!empty($usedIds)) {
            $params = array_merge($params, $usedIds);
        }
        $stmt->execute($params);
        foreach ($stmt->fetchAll() as $r) {
            $kwVideos[] = mapRow($r);
        }
    }

    // ── 최종 조합 ─────────────────────────────────────────────────
    // 키워드 매칭 영상이 5개 미만이면 랜덤 영상으로 보충 (최대 5개까지)
    $usedIdsAll = $usedIds;
    foreach ($kwVideos as $v) { $usedIdsAll[] = $v['id']; }

    $need = 5 - count($kwVideos);
    if ($need > 0) {
        $excAll = !empty($usedIdsAll)
            ? 'AND c.content_id NOT IN (' . implode(',', array_fill(0, count($usedIdsAll), '?')) . ')'
            : '';
        $stmtFill = $pdo->prepare("
            SELECT c.*,
                   COALESCE(ec.code_name, c.category_code) AS category_name,
                   COALESCE(ec_grp.code_name, c.category_group) AS category_group_name,
                   lh.watch_tm, lh.content_tm, lh.all_tm,
                   CASE WHEN cw.content_id IS NOT NULL THEN 1 ELSE 0 END AS is_bookmarked,
                   GROUP_CONCAT(COALESCE(kwec.code_name, ck.keyword_code) ORDER BY ck.keyword_code SEPARATOR ',') AS keywords
            FROM   edu_contents c
                   LEFT JOIN edu_codes            ec   ON ec.base_code    = c.category_code
                   LEFT JOIN edu_codes         ec_grp  ON TRIM(UPPER(ec_grp.base_code)) = TRIM(UPPER(c.category_group))
                   LEFT JOIN edu_content_keywords ck   ON ck.content_id   = c.content_id
                                                     AND ck.is_active = 1
                   LEFT JOIN edu_codes            kwec ON kwec.base_code  = ck.keyword_code
                   LEFT JOIN edu_learning_histories lh ON lh.content_id   = c.content_id
                                                      AND lh.member_id    = ?
                                                      AND lh.sys_comp_code = ?
                   LEFT JOIN edu_content_wishlist  cw  ON cw.member_id    = ?
                                                      AND cw.sys_comp_code = ?
                                                      AND cw.is_active    = 1
                                                      AND cw.content_id   = c.content_id
            WHERE  (c.is_active = 1 OR c.is_active IS NULL)
              AND  (c.is_offer IS NULL OR c.is_offer != 1)
              AND  c.category_code IN ('CA10004', 'CA10005')
              AND  c.content_id IN (SELECT DISTINCT content_id FROM edu_content_keywords WHERE is_active = 1)
              {$excAll}
            GROUP  BY c.content_id
            ORDER  BY RAND()
            LIMIT  {$need}
        ");
        $fillParams = [$memberId, $sysCompCode, $memberId, $sysCompCode];
        if (!empty($usedIdsAll)) {
            $fillParams = array_merge($fillParams, $usedIdsAll);
        }
        $stmtFill->execute($fillParams);
        foreach ($stmtFill->fetchAll() as $r) {
            $kwVideos[] = mapRow($r);
        }
    }

    // 항상 6개: [0]=pick영상, [1~5]=키워드 관련영상(중복X), 부족하면 랜덤, 그래도 부족하면 빈카드
    $videos = [];
    if ($pickVideo) {
        $videos[] = $pickVideo;
    }
    // pick영상 ID를 제외한 키워드 영상 5개
    $cnt = 0;
    foreach ($kwVideos as $v) {
        if ($pickVideo && $v['id'] == $pickVideo['id']) continue;
        $videos[] = $v;
        $cnt++;
        if ($cnt >= 5) break;
    }
    // 키워드 영상이 5개 미만이면 랜덤 영상으로 보충 (pick/키워드 중복 제외)
    $need = 6 - count($videos);
    if ($need > 0) {
        $usedIdsAll = array_map(function($v){return is_array($v)?$v['id']:$v;}, $videos);
        $excAll = !empty($usedIdsAll)
            ? 'AND c.content_id NOT IN (' . implode(',', array_fill(0, count($usedIdsAll), '?')) . ')'
            : '';
        $stmtFill = $pdo->prepare("
            SELECT c.*,
                   COALESCE(ec.code_name, c.category_code) AS category_name,
                   COALESCE(ec_grp.code_name, c.category_group) AS category_group_name,
                   lh.watch_tm, lh.content_tm, lh.all_tm,
                   CASE WHEN cw.content_id IS NOT NULL THEN 1 ELSE 0 END AS is_bookmarked,
                   GROUP_CONCAT(COALESCE(kwec.code_name, ck.keyword_code) ORDER BY ck.keyword_code SEPARATOR ',') AS keywords
            FROM   edu_contents c
                   LEFT JOIN edu_codes            ec   ON ec.base_code    = c.category_code
                   LEFT JOIN edu_codes         ec_grp  ON TRIM(UPPER(ec_grp.base_code)) = TRIM(UPPER(c.category_group))
                   LEFT JOIN edu_content_keywords ck   ON ck.content_id   = c.content_id
                                                     AND ck.is_active = 1
                   LEFT JOIN edu_codes            kwec ON kwec.base_code  = ck.keyword_code
                   LEFT JOIN edu_learning_histories lh ON lh.content_id   = c.content_id
                                                      AND lh.member_id    = ?
                                                      AND lh.sys_comp_code = ?
                   LEFT JOIN edu_content_wishlist  cw  ON cw.member_id    = ?
                                                      AND cw.sys_comp_code = ?
                                                      AND cw.is_active    = 1
                                                      AND cw.content_id   = c.content_id
            WHERE  (c.is_active = 1 OR c.is_active IS NULL)
              AND  (c.is_offer IS NULL OR c.is_offer != 1)
              AND  c.category_code IN ('CA10004', 'CA10005')
              AND  c.content_id IN (SELECT DISTINCT content_id FROM edu_content_keywords WHERE is_active = 1)
              {$excAll}
            GROUP  BY c.content_id
            ORDER  BY RAND()
            LIMIT  {$need}
        ");
        $fillParams = [$memberId, $sysCompCode, $memberId, $sysCompCode];
        if (!empty($usedIdsAll)) {
            $fillParams = array_merge($fillParams, $usedIdsAll);
        }
        $stmtFill->execute($fillParams);
        foreach ($stmtFill->fetchAll() as $r) {
            $videos[] = mapRow($r);
        }
    }
    // 그래도 부족하면 빈 카드로 패딩
    while (count($videos) < 6) {
        $videos[] = [
            'id' => 'empty_' . count($videos),
            'url' => '',
            'thumbnail' => '',
            'category' => '',
            'category_code' => '',
            'subcate' => '',
            'bookmark' => false,
            'title' => '',
            'picker' => '',
            'type' => 'main',
            'keywords' => [],
            'gauge' => 0,
            'watch_tm' => 0,
            'content_tm' => 0,
            'all_tm' => 0,
            'watch_min' => 0,
            'content_min' => 0,
            'all_min' => 0,
        ];
    }

    // 메인 게이지용: 회원 누적 시청시간(watch_tm, 초) 합계
    $stmtTotal = $pdo->prepare("\n        SELECT COALESCE(SUM(watch_tm), 0) AS total_watch_tm\n        FROM edu_learning_histories\n        WHERE member_id = ? AND sys_comp_code = ?\n    ");
    $stmtTotal->execute([$memberId, $sysCompCode]);
    $totalWatchTm = (int)$stmtTotal->fetchColumn();
    $totalMin = (int)floor($totalWatchTm / 60);

       // 전체 평균 학습시간 계산: (전사 모든 사용자의 총 watch_tm 합계) / (학습경험이 있는 사용자 수)
       $avgWatchMin = 50;  // 기본값
       try {
           $stmtAvg = $pdo->prepare("
               SELECT 
                   COALESCE(SUM(h.watch_tm), 0) as total_watch_tm,
                   COUNT(DISTINCT h.member_id) as unique_members
               FROM edu_learning_histories h
               WHERE h.sys_comp_code = ?
           ");
           $stmtAvg->execute([$sysCompCode]);
           $avgRow = $stmtAvg->fetch(PDO::FETCH_ASSOC);
           if ($avgRow) {
               $totalWatchTmAll = (int)($avgRow['total_watch_tm'] ?? 0);
               $uniqueMembers = (int)($avgRow['unique_members'] ?? 0);
               if ($uniqueMembers > 0) {
                   $avgWatchMin = (int)floor($totalWatchTmAll / $uniqueMembers / 60);
                   $avgWatchMin = max(50, $avgWatchMin);
               }
           }
       } catch (Exception $e) {
           error_log('[videos_by_keywords] avgWatchMin calculation failed: ' . $e->getMessage());
       }

    echo json_encode(
        [
            'success' => true,
            'videos' => $videos,
            'total_watch_tm' => $totalWatchTm,
            'total_all_tm' => $totalWatchTm,
            'total_min' => $totalMin,
               'avg_watch_min' => $avgWatchMin,
        ],
        JSON_UNESCAPED_UNICODE | JSON_HEX_TAG | JSON_HEX_AMP
    );
} catch (Exception $e) {
    error_log('[videos_by_keywords] ' . $e->getMessage());
    echo json_encode(['success' => false, 'videos' => [], 'error' => $e->getMessage()]);
}