<?php
//=========================
// 콘텐츠리스트 AJAX
// [마이페이지][시청중인, 시청완료, 저장한 콘텐츠] 데이터 동적변환
//=========================
require_once __DIR__ . '/../bbs/db_conn.php';

header('Content-Type: application/json; charset=utf-8');

// TODO: 실제 로그인 세션 연동 후 교체
if (session_status() === PHP_SESSION_NONE) {
	session_start();
}

$memberId    = $_SESSION['member_id'] ?? '';
$sysCompCode = $_SESSION['sys_comp_code'] ?? '';
//$memberId    = 'U001';
//$sysCompCode = 'COMP01';

function mypage_json(array $data): void
{
	echo json_encode($data, JSON_UNESCAPED_UNICODE);
	exit;
}

function mypage_h(?string $str): string
{
	return htmlspecialchars((string)$str, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
}

function map_content_row(array $row, array $catMap = []): array
{
	//keywords_code_name
	$kwStr    = (string)($row['keywords_code_name'] ?? $row['keywords'] ?? '');
	$keywords = ($kwStr !== '') ? explode(',', $kwStr) : [];
	
	$watchTm   = (float)($row['watch_tm'] ?? 0);
	$contentTm = (float)($row['content_tm'] ?? 0);
	$allTm     = (float)($row['all_tm'] ?? 0);
	$gauge     = ($contentTm > 0) ? (int)round(($watchTm / $contentTm) * 100) : 0;
	$gauge     = max(0, min(100, $gauge));
	
	$raw = trim((string)($row['content_url'] ?? ''));
	if (preg_match('/(?:v=|youtu\.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((string)($row['thumbnail_url'] ?? ''));
	$url         = $videoId !== '' ? "https://www.youtube.com/watch?v={$videoId}" : $raw;
	$thumbnail   = $thumbFromDb !== ''
			? $thumbFromDb
			: ($videoId !== '' ? "https://img.youtube.com/vi/{$videoId}/sddefault.jpg" : '/img/video/img_thumb_01.png');
			
			return [
				'id'            => $row['content_id'] ?? '',
				'content_id'    => $row['content_id'] ?? '',
				'category_code' => $row['category_code'] ?? '',
				'category_name' => $catMap[$row['category_code'] ?? ''] ?? ($row['category_code'] ?? ''),
				'subcate'       => (string)($row['category_group'] ?? ''),
				'title'         => $row['title'] ?? '',
				'description'   => trim((string)($row['description'] ?? '')),
				'description2'  => trim((string)($row['description2'] ?? '')),
				'url'           => $raw,
				'content_url'   => $raw,
				'video_url'     => $url,
				'video_id'      => $videoId,
				'thumbnail'     => $thumbnail,
				'watch_tm'      => (int)($row['watch_tm'] ?? 0),
				'content_tm'    => (int)($row['content_tm'] ?? 0),
				'all_tm'        => (int)$allTm,
				'gauge'         => $gauge,
				'is_bookmarked' => !empty($row['is_bookmarked']),
				'bookmark'      => !empty($row['is_bookmarked']),
				'keywords'      => $keywords,
			];
}

function category_tag_class(string $categoryCode): string
{
	$map = [
			'CA10001' => 'myclass',
			'CA10002' => 'onboarding',
			'CA10003' => 'legal',
			'CA10004' => 'leader',
			'CA10005' => 'insight',
			'CA10006' => 'biz',
	];
	
	return $map[$categoryCode] ?? 'etc';
}

try {
	$pdo = db_conn();
	$pdo->exec("SET NAMES 'utf8mb4'");
	
	$tab    = trim((string)($_GET['tab'] ?? 'watching'));
	$page   = max(1, (int)($_GET['page'] ?? 1));
	$limit  = max(1, (int)($_GET['limit'] ?? 12));
	$offset = ($page - 1) * $limit;
	
	// ★ 선택년도 수집
	$selectedYear = trim((string)($_GET['selected_year'] ?? date('Y')));
	if ($selectedYear === '' || !preg_match('/^\d{4}$/', $selectedYear)) {
		$selectedYear = date('Y');
	}
	
	$CATEGORY_MAP = [
			'CA10001' => '마이클래스',
			'CA10002' => '온보딩',
			'CA10003' => '법정교육',
			'CA10004' => '리더십',
			'CA10005' => '인사이트',
			'CA10006' => '비즈트렌드',
	];
	
	$stmtCode = $pdo->prepare("
        SELECT base_code, code_name
        FROM edu_codes
        WHERE group_code = 'CA100'
          AND is_active = '1'
        ORDER BY code
    ");
	$stmtCode->execute();
	
	foreach ($stmtCode->fetchAll(PDO::FETCH_ASSOC) as $row) {
		$CATEGORY_MAP[$row['base_code']] = $row['code_name'];
	}
	
	// =========================
	// 탭 카운트 (선택년도 기준)
	// =========================
	
	// 시청중인 콘텐츠 카운트
	/*
	 * $stmtCountWatching = $pdo->prepare("
        SELECT COUNT(*)
        FROM edu_learning_histories
        WHERE member_id = :member_id
          AND sys_comp_code = :sys_comp_code
          AND watch_tm < content_tm
          AND YEAR(last_viewed_at) = :selected_year
    ");
	*/
		$stmtCountWatching = $pdo->prepare("
	    SELECT COUNT(DISTINCT lh.content_id)
	    FROM edu_learning_histories lh
	    WHERE lh.member_id = :member_id
	      AND lh.sys_comp_code = :sys_comp_code
	      AND lh.watch_tm < lh.content_tm
	      AND YEAR(lh.last_viewed_at) = :selected_year
	      AND NOT EXISTS (
	            SELECT 1
	            FROM edu_learning_histories lh2
	            WHERE lh2.content_id = lh.content_id
	              AND lh2.member_id = lh.member_id
	              AND lh2.sys_comp_code = lh.sys_comp_code
	              AND lh2.completed_at IS NOT NULL
	      )
	");
	$stmtCountWatching->execute([
			':member_id'     => $memberId,
			':sys_comp_code' => $sysCompCode,
			':selected_year' => (int)$selectedYear,
	]);
	$countWatching = (int)$stmtCountWatching->fetchColumn();
	
	// 시청완료
	// 기준:
	// 1) completed_at 이 있는 이력만 완료로 인정
	// 2) completed_at 기준 연도 필터
	// 3) 같은 콘텐츠는 최신 완료 1건만 카운트
	$stmtCountCompleted = $pdo->prepare("
        SELECT COUNT(*)
        FROM (
            SELECT
                content_id,
                MAX(completed_at) AS latest_completed_at
            FROM edu_learning_histories
            WHERE member_id = :member_id
              AND sys_comp_code = :sys_comp_code
              AND completed_at IS NOT NULL
              AND YEAR(completed_at) = :selected_year
            GROUP BY content_id
        ) t
    ");
	$stmtCountCompleted->execute([
			':member_id'     => $memberId,
			':sys_comp_code' => $sysCompCode,
			':selected_year' => (int)$selectedYear,
	]);
	$countCompleted = (int)$stmtCountCompleted->fetchColumn();
	
	// 저장한 콘텐츠
	// ★ 저장한 시점 기준 년도(favorited_at 우선, 없으면 updated_at, created_at fallback)
	$stmtCountSaved = $pdo->prepare("
        SELECT COUNT(DISTINCT content_id)
        FROM edu_content_wishlist
        WHERE member_id = :member_id
          AND sys_comp_code = :sys_comp_code
          AND is_active = '1'
          AND YEAR(COALESCE(favorited_at, updated_at, created_at)) = :selected_year
    ");
	$stmtCountSaved->execute([
			':member_id'     => $memberId,
			':sys_comp_code' => $sysCompCode,
			':selected_year' => (int)$selectedYear,
	]);
	$countSaved = (int)$stmtCountSaved->fetchColumn();
	
	// =========================
	// 탭별 메인 쿼리
	// =========================
	if ($tab === 'saved') {
		// 저장한 콘텐츠 : wishlist 기준 
		$countTotal = $countSaved;
		
		$sql = "
            SELECT
                c.content_id,
                c.category_code,
				c.category_group,
                c.title,
				c.description,
				c.description2,
                c.content_url,
                c.thumbnail_url,
                COALESCE(MAX(lh.watch_tm), 0) AS watch_tm,
                COALESCE(MAX(lh.content_tm), 0) AS content_tm,
				COALESCE(MAX(lh.all_tm), 0) AS all_tm,
                1 AS is_bookmarked,
                GROUP_CONCAT(DISTINCT ck.keyword_code ORDER BY ck.keyword_code SEPARATOR ',') AS keywords,
                MAX(lh.last_viewed_at) AS sort_last_viewed_at,
                MAX(COALESCE(cw.favorited_at, cw.updated_at, cw.created_at)) AS sort_saved_at
            FROM edu_content_wishlist cw
            INNER JOIN edu_contents c
                    ON c.content_id = cw.content_id
            LEFT JOIN edu_learning_histories lh
                   ON lh.content_id = c.content_id
                  AND lh.member_id = cw.member_id
                  AND lh.sys_comp_code = cw.sys_comp_code
                  AND YEAR(lh.last_viewed_at) = :selected_year_lh
            LEFT JOIN edu_content_keywords ck
                   ON ck.content_id = c.content_id
            WHERE cw.member_id = :member_id
              AND cw.sys_comp_code = :sys_comp_code
              AND cw.is_active = '1'
              AND YEAR(COALESCE(cw.favorited_at, cw.updated_at, cw.created_at)) = :selected_year_saved
            GROUP BY c.content_id
            ORDER BY
                CASE WHEN MAX(lh.last_viewed_at) IS NULL THEN 1 ELSE 0 END ASC,
                MAX(lh.last_viewed_at) DESC,
                sort_saved_at DESC
            LIMIT :limit OFFSET :offset
        ";
	} elseif ($tab === 'completed') {
		// 시청완료 콘텐츠 completed_at IS NOT NULL (최신 1건)
		// 기준:
		// 1) completed_at 이 있는 이력만 완료로 인정
		// 2) 선택년도는 completed_at 기준
		// 3) 같은 콘텐츠가 여러 번 완료된 경우 최신 완료 1건만 반영
		$countTotal = $countCompleted;
		
		$sql = "
        SELECT
            c.content_id,
            c.category_code,
			c.category_group,
            c.title,
			c.description,
			c.description2,
            c.content_url,
            c.thumbnail_url,
            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(DISTINCT ck.keyword_code ORDER BY ck.keyword_code SEPARATOR ',') AS keywords,
            lh.completed_at AS sort_completed_at
        FROM (
            SELECT
                t1.content_id,
                t1.member_id,
                t1.sys_comp_code,
                t1.watch_tm,
                t1.content_tm,
				t1.all_tm,
                t1.completed_at
            FROM edu_learning_histories t1
            INNER JOIN (
                SELECT
                    content_id,
                    MAX(completed_at) AS latest_completed_at
                FROM edu_learning_histories
                WHERE member_id = :completed_member_id_1
                  AND sys_comp_code = :completed_sys_comp_code_1
                  AND completed_at IS NOT NULL
                  AND YEAR(completed_at) = :completed_selected_year_1
                GROUP BY content_id
            ) t2
                ON t1.content_id = t2.content_id
               AND t1.completed_at = t2.latest_completed_at
               AND t1.member_id = :completed_member_id_2
               AND t1.sys_comp_code = :completed_sys_comp_code_2
        ) lh
        INNER JOIN edu_contents c
                ON c.content_id = lh.content_id
        LEFT JOIN edu_content_wishlist cw
               ON cw.content_id = c.content_id
              AND cw.member_id = lh.member_id
              AND cw.sys_comp_code = lh.sys_comp_code
              AND cw.is_active = '1'
        LEFT JOIN edu_content_keywords ck
               ON ck.content_id = c.content_id
        GROUP BY
            c.content_id,
            c.category_code,
			c.category_group,
            c.title,
			c.description,
			c.description2,
            c.content_url,
            c.thumbnail_url,
            lh.watch_tm,
            lh.content_tm,
			lh.all_tm,
            cw.content_id,
            lh.completed_at
        ORDER BY lh.completed_at DESC
        LIMIT :limit OFFSET :offset
    ";
	} else {
		// 시청중인 콘텐츠 : watch_tm < content_tm 
		$tab = 'watching';
		$countTotal = $countWatching;
		
		$sql = "
					SELECT
					    c.content_id,
					    c.category_code,
					    c.category_group,
					    c.title,
					    c.description,
					    c.description2,
					    c.content_url,
					    c.thumbnail_url,
					    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(DISTINCT ck.keyword_code ORDER BY ck.keyword_code SEPARATOR ',') AS keywords,
					    -- 추가된 코드명 리스트
					    GROUP_CONCAT(DISTINCT ec.code_name ORDER BY ck.keyword_code SEPARATOR ',') AS keywords_code_name
					FROM edu_learning_histories lh
					INNER JOIN edu_contents c
					        ON c.content_id = lh.content_id
					LEFT JOIN edu_content_wishlist cw
					       ON cw.content_id = c.content_id
					      AND cw.member_id = lh.member_id
					      AND cw.sys_comp_code = lh.sys_comp_code
					      AND cw.is_active = '1'
					LEFT JOIN edu_content_keywords ck
					       ON ck.content_id = c.content_id
					-- [추가] 코드 테이블 조인
					LEFT JOIN edu_codes ec
					       ON ec.base_code = ck.keyword_code
					      AND ec.group_code = 'KW100'

						WHERE lh.member_id = :member_id
						  AND lh.sys_comp_code = :sys_comp_code
						  AND lh.watch_tm < lh.content_tm
						  AND YEAR(lh.last_viewed_at) = :selected_year
						  AND NOT EXISTS (
						        SELECT 1
						        FROM edu_learning_histories lh2
						        WHERE lh2.content_id = lh.content_id
						          AND lh2.member_id = lh.member_id
						          AND lh2.sys_comp_code = lh.sys_comp_code
						          AND lh2.completed_at IS NOT NULL
						  )
						GROUP BY c.content_id
						ORDER BY lh.last_viewed_at DESC
						LIMIT :limit OFFSET :offset

        ";
		
		/*
					WHERE lh.member_id = :member_id
					  AND lh.sys_comp_code = :sys_comp_code
					  AND lh.watch_tm < lh.content_tm
					  AND YEAR(lh.last_viewed_at) = :selected_year
					GROUP BY c.content_id
					ORDER BY lh.last_viewed_at DESC
					LIMIT :limit OFFSET :offset
        ";*/
		
// 		$sql = "
//             SELECT
//                 c.content_id,
//                 c.category_code,
//                 c.title,
//                 c.content_url,
//                 c.thumbnail_url,
//                 lh.watch_tm,
//                 lh.content_tm,
//                 CASE WHEN cw.content_id IS NOT NULL THEN 1 ELSE 0 END AS is_bookmarked,
//                 GROUP_CONCAT(DISTINCT ck.keyword_code ORDER BY ck.keyword_code SEPARATOR ',') AS keywords
//             FROM edu_learning_histories lh
//             INNER JOIN edu_contents c
//                     ON c.content_id = lh.content_id
//             LEFT JOIN edu_content_wishlist cw
//                    ON cw.content_id = c.content_id
//                   AND cw.member_id = lh.member_id
//                   AND cw.sys_comp_code = lh.sys_comp_code
//                   AND cw.is_active = '1'
//             LEFT JOIN edu_content_keywords ck
//                    ON ck.content_id = c.content_id
//             WHERE lh.member_id = :member_id
//               AND lh.sys_comp_code = :sys_comp_code
//               AND lh.watch_tm < lh.content_tm
//               AND YEAR(lh.last_viewed_at) = :selected_year
//             GROUP BY c.content_id
//             ORDER BY lh.last_viewed_at DESC
//             LIMIT :limit OFFSET :offset
//         ";
	}
	
	
	
	$stmt = $pdo->prepare($sql);
	
	if ($tab === 'saved') {
		$stmt->bindValue(':member_id', $memberId, PDO::PARAM_STR);
		$stmt->bindValue(':sys_comp_code', $sysCompCode, PDO::PARAM_STR);
		$stmt->bindValue(':selected_year_lh', (int)$selectedYear, PDO::PARAM_INT);
		$stmt->bindValue(':selected_year_saved', (int)$selectedYear, PDO::PARAM_INT);
		
	} elseif ($tab === 'completed') {
		$stmt->bindValue(':completed_member_id_1', $memberId, PDO::PARAM_STR);
		$stmt->bindValue(':completed_sys_comp_code_1', $sysCompCode, PDO::PARAM_STR);
		$stmt->bindValue(':completed_selected_year_1', (int)$selectedYear, PDO::PARAM_INT);
		$stmt->bindValue(':completed_member_id_2', $memberId, PDO::PARAM_STR);
		$stmt->bindValue(':completed_sys_comp_code_2', $sysCompCode, PDO::PARAM_STR);
		
	} else {
		$stmt->bindValue(':member_id', $memberId, PDO::PARAM_STR);
		$stmt->bindValue(':sys_comp_code', $sysCompCode, PDO::PARAM_STR);
		$stmt->bindValue(':selected_year', (int)$selectedYear, PDO::PARAM_INT);
	}
	
	$stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
	$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
	$stmt->execute();
	
	
	$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
	
	ob_start();
	$videos = [];
	
	foreach ($rows as $row) {
		$item     = map_content_row($row, $CATEGORY_MAP);
		$tagClass = category_tag_class($item['category_code']);
		$videos[] = $item;
		
		
		$item['url']="#"; //마이페이지용 모달 개발전까지 링크삭제
		
		?>
        <li title="클릭 시, 모달 연결은 추후 개발예정"
            class="content-card <?php if ($tab === 'completed') { ?>content-card--completed<?php } ?>"
            data-content-id="<?= mypage_h($item['content_id']) ?>"
        >
            <a href="<?= mypage_h($item['url']) ?>" class="card-link">
                <label class="bookmark" for="wish_<?= mypage_h($tab . '_' . $item['content_id']) ?>" onclick="event.stopPropagation();">
                    <input
                        type="checkbox"
                        id="wish_<?= mypage_h($tab . '_' . $item['content_id']) ?>"
                        title="저장"
                        <?php if ($item['is_bookmarked']) { ?>checked<?php } ?>
                    >
                </label>

                <div class="item-thumb">
                    <img src="<?= mypage_h($item['thumbnail']) ?>" alt="<?= mypage_h($item['title']) ?>">
                    <div class="gauge-bar rect">
                        <div class="gauge-fill" style="width: <?= (int)$item['gauge'] ?>%"></div>
                    </div>
                </div>

                <div class="item-info">
                    <span class="category-tag <?= mypage_h($tagClass) ?>">
                        <?= mypage_h($item['category_name']) ?>
                    </span>
                    <strong class="item-title"><?= mypage_h($item['title']) ?></strong>

                    <div class="tag-list">
                        <?php foreach ($item['keywords'] as $kw): ?>
                            <?php if (trim((string)$kw) === '') { continue; } ?>
                            <span class="tag"><?= mypage_h($kw) ?></span>
                        <?php endforeach; ?>
                    </div>

                    <button type="button" class="btn-remove-card" title="삭제"></button>
                </div>
            </a>
        </li>
        <?php
    }

    $html = ob_get_clean();

    mypage_json([
        'success'       => true,
        'tab'           => $tab,
        'page'          => $page,
        'limit'         => $limit,
        'selected_year' => $selectedYear,
        'total_count'   => $countTotal,
        'counts'        => [
            'watching'  => $countWatching,
            'completed' => $countCompleted,
            'saved'     => $countSaved,
        ],
		'videos'        => $videos,
        'html'          => $html,
        'is_last'       => (($offset + $limit) >= $countTotal),
    ]);
} catch (Throwable $e) {
    mypage_json([
        'success' => false,
        'message' => '콘텐츠 리스트를 불러오지 못했습니다.',
        'error'   => $e->getMessage(),
    ]);
}