<?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 response_json(array $data): void
{
    echo json_encode($data, JSON_UNESCAPED_UNICODE);
    exit;
}

try {
    $pdo = db_conn();
    $pdo->exec("SET NAMES 'utf8mb4'");
    
    $categoryBase = [
        '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) {
        $categoryBase[$row['base_code']] = $row['code_name'];
    }
    
    // 연도 목록
    $stmtYear = $pdo->prepare("
        SELECT DISTINCT YEAR(last_viewed_at) AS view_year
        FROM edu_learning_histories
        WHERE member_id = :member_id
          AND sys_comp_code = :sys_comp_code
          AND last_viewed_at IS NOT NULL
        ORDER BY view_year DESC
    ");
    $stmtYear->execute([
        ':member_id'     => $memberId,
        ':sys_comp_code' => $sysCompCode,
    ]);
    
    $yearList = array_map(
        static fn($v) => (int)$v,
        array_filter($stmtYear->fetchAll(PDO::FETCH_COLUMN))
        );
    
    $currentYear = (int)date('Y');
    
    if (empty($yearList)) {
    	// 리스트가 완전히 비어있다면 현재 연도 한 개만 추가
    	$yearList = [$currentYear];
    }
    
    $selectedYear = isset($_GET['year']) ? (int)$_GET['year'] : 0;
    if ($selectedYear <= 0) {
        $selectedYear = !empty($yearList) ? (int)$yearList[0] : (int)date('Y');
    }
    
    
    // 내 총학습시간
    $stmtMyTotal = $pdo->prepare("
        SELECT COALESCE(SUM(watch_tm), 0) AS total_watch_tm
        FROM edu_learning_histories
        WHERE member_id = :member_id
          AND sys_comp_code = :sys_comp_code
          AND YEAR(last_viewed_at) = :stats_year
    ");

    $stmtMyTotal->execute([
        ':member_id'     => $memberId,
        ':sys_comp_code' => $sysCompCode,
        ':stats_year'    => $selectedYear,
    ]);
    $myTotalMinutes = (int)$stmtMyTotal->fetchColumn();
    
    // 전체평균
    $stmtAvg = $pdo->prepare("
        SELECT COALESCE(AVG(x.user_total_watch_tm), 0) AS avg_total_watch_tm
        FROM (
            SELECT
                member_id,
                sys_comp_code,
                SUM(watch_tm) AS user_total_watch_tm
            FROM edu_learning_histories
            WHERE YEAR(last_viewed_at) = :stats_year
            GROUP BY member_id, sys_comp_code
        ) x
    ");
    $stmtAvg->execute([':stats_year' => $selectedYear]);
    $avgTotalMinutes = (int)round((float)$stmtAvg->fetchColumn());
    
    // 온보딩 기준일
    $stmtUser = $pdo->prepare("
        SELECT join_date
        FROM edu_users
        WHERE member_id = :member_id
          AND sys_comp_code = :sys_comp_code
        LIMIT 1
    ");
    $stmtUser->execute([
        ':member_id'     => $memberId,
        ':sys_comp_code' => $sysCompCode,
    ]);
    $joinDate = $stmtUser->fetchColumn();
    
    $onboardingDueDate = '';
    $isOnboardingPeriod = 'N';
    if (!empty($joinDate)) {
        $onboardingDueDate   = date('Y-m-d', strtotime($joinDate . ' +14 days'));
        $isOnboardingPeriod  = (date('Y-m-d') <= $onboardingDueDate) ? 'Y' : 'N';
    }
    
    
    
    /**
     * 현재 월을 기준으로 분기 코드 계산
     * - 1~3월   : CA200Q01
     * - 4~6월   : CA200Q02
     * - 7~9월   : CA200Q03
     * - 10~12월 : CA200Q04
     */
    
    $currentMonth = (int)date('n');
    if ($currentMonth >= 1 && $currentMonth <= 3) {
    	$currentQuarterBaseCode = 'CA200Q01';
    } elseif ($currentMonth >= 4 && $currentMonth <= 6) {
    	$currentQuarterBaseCode = 'CA200Q02';
    } elseif ($currentMonth >= 7 && $currentMonth <= 9) {
    	$currentQuarterBaseCode = 'CA200Q03';
    } else {
    	$currentQuarterBaseCode = 'CA200Q04';
    } 
    
   // --fn_get_progress_rate(:sys_comp_code, :stats_year1, :member_id, c.category_code, '') AS category_percent,
    if($memberId=="test"){
    }else{
    	// 선택연도 카테고리별 집계
    	$stmtCategory = $pdo->prepare("
						SELECT
						        c.category_code,
    			
								    CASE
								        WHEN c.category_code = 'CA10001' THEN
								            fn_get_progress_rate(:sys_comp_code_01, :stats_year_01, :member_id_01, c.category_code, :quarter_code_01)
								        ELSE
													fn_get_progress_rate(:sys_comp_code_02, :stats_year_02, :member_id_02, c.category_code, '')
								    END AS category_percent,
    			
						        COUNT(DISTINCT lh.content_id) AS learned_content_cnt,
						        COUNT(DISTINCT CASE WHEN lh.watch_tm >= lh.content_tm THEN lh.content_id END) AS completed_content_cnt,
						        COALESCE(SUM(lh.watch_tm), 0) AS total_watch_tm,
						        COALESCE(SUM(lh.content_tm), 0) AS total_content_tm
    			
										,FLOOR(COALESCE(SUM(lh.watch_tm), 0)/60)  AS total_watch_tm_min
    			
						    FROM edu_learning_histories lh
						    INNER JOIN edu_contents c ON c.content_id = lh.content_id
						    WHERE lh.member_id = :member_id_03
						      AND lh.sys_comp_code = :sys_comp_code_03
						      AND YEAR(lh.last_viewed_at) = :stats_year_03
						    GROUP BY c.category_code
						");
    	
    	$stmtCategory->execute([
    			':sys_comp_code_01' => $sysCompCode,
    			':stats_year_01'    => $selectedYear,
    			':member_id_01'     => $memberId,
    			':quarter_code_01'  => $currentQuarterBaseCode,
    			':sys_comp_code_02' => $sysCompCode,
    			':stats_year_02'    => $selectedYear,
    			':member_id_02'     => $memberId,
    			':member_id_03'     => $memberId,
    			':sys_comp_code_03' => $sysCompCode,
    			':stats_year_03'    => $selectedYear,
    	]);
    }
    
    
    

    
//     $stmtCategory = $pdo->prepare("
//         SELECT
//             c.category_code,
//             COUNT(DISTINCT lh.content_id) AS learned_content_cnt,
//             COUNT(DISTINCT CASE WHEN lh.watch_tm >= lh.content_tm THEN lh.content_id END) AS completed_content_cnt,
//             COALESCE(SUM(lh.watch_tm), 0) AS total_watch_tm,
//             COALESCE(SUM(lh.content_tm), 0) AS total_content_tm
//         FROM edu_learning_histories lh
//         INNER JOIN edu_contents c
//                 ON c.content_id = lh.content_id
//         WHERE lh.member_id = :member_id
//           AND lh.sys_comp_code = :sys_comp_code
//           AND YEAR(lh.last_viewed_at) = :stats_year
//         GROUP BY c.category_code
//     ");
//     $stmtCategory->execute([
//         ':member_id'     => $memberId,
//         ':sys_comp_code' => $sysCompCode,
//         ':stats_year'    => $selectedYear,
//     ]);
    
    $aggMap = [];
    foreach ($stmtCategory->fetchAll(PDO::FETCH_ASSOC) as $row) {
        $aggMap[$row['category_code']] = $row;
    }
    
    /*
    // 카테고리별 전체 콘텐츠 수
    $stmtTotalCnt = $pdo->prepare("
        SELECT
            category_code,
            COUNT(*) AS total_cnt
        FROM edu_contents
        WHERE (is_active = '1' OR is_active = 'Y')
        GROUP BY category_code
    ");
    $stmtTotalCnt->execute();
    
    $totalCntMap = [];
    foreach ($stmtTotalCnt->fetchAll(PDO::FETCH_ASSOC) as $row) {
        $totalCntMap[$row['category_code']] = (int)$row['total_cnt'];
    }
    */
    
    

    // ---------------------------------------------------------
    // 카테고리별 전체 콘텐츠 수 산정 : Start
    // - 카테고리마다 total_cnt 산정 조건이 다를 수 있으므로
    //   공통 GROUP BY 방식 대신, 카테고리별 분기 구조로 처리한다.
    // ---------------------------------------------------------
    
    $totalCntMap = [
    		'CA10001' => 0, // 마이클래스
    		'CA10002' => 0, // 온보딩
    		'CA10003' => 0, // 법정교육
    		'CA10004' => 0, // 리더십
    		'CA10005' => 0, // 인사이트
    		'CA10006' => 0, // 비즈트렌드
    ];
    

  
    // ---------------------------------------------------------
    // 1. 마이클래스(CA10001)
    // - 현재 분기 기준 + 선택년도 기준으로 콘텐츠 수 산정
    // - 조건:
    //   category_code = CA10001
    //   category_group = 현재분기 base_code
    //   goal_code 앞 4자리 = 선택년도
    // ---------------------------------------------------------
    $stmtMyClassCnt = $pdo->prepare("
        SELECT COUNT(*) AS total_cnt
        FROM edu_contents
        WHERE (is_active = '1' OR is_active = 'Y')
          AND category_code = 'CA10001'
          AND base_year = :selected_year
    ");
    $stmtMyClassCnt->execute([
    		':selected_year'             => (string)$selectedYear,
    ]);
    $totalCntMap['CA10001'] = (int)$stmtMyClassCnt->fetchColumn();
    
    // ---------------------------------------------------------
    // 2. 온보딩(CA10002)
    // - 현재는 기본 조건만 적용
    // - 추후 카테고리별 별도 조건을 쉽게 추가할 수 있도록
    //   분리된 쿼리 구조로 작성
    // ---------------------------------------------------------
    $stmtOnboardingCnt = $pdo->prepare("
        SELECT COUNT(*) AS total_cnt
        FROM edu_contents
        WHERE (is_active = '1' OR is_active = 'Y')
          AND category_code = 'CA10002'
    ");
    $stmtOnboardingCnt->execute();
    $totalCntMap['CA10002'] = (int)$stmtOnboardingCnt->fetchColumn();
    
    // ---------------------------------------------------------
    // 3. 법정교육(CA10003)
    // - 현재는 기본 조건만 적용
    // - 추후 카테고리별 별도 조건을 쉽게 추가할 수 있도록
    //   분리된 쿼리 구조로 작성
    // ---------------------------------------------------------
    $stmtLegalCnt = $pdo->prepare("
        SELECT COUNT(*) AS total_cnt
        FROM edu_contents
        WHERE (is_active = '1' OR is_active = 'Y')
          AND category_code = 'CA10003'
          AND base_year = :selected_year
    ");
    $stmtLegalCnt->execute([
    		':selected_year'             => (string)$selectedYear,
    ]);
    $totalCntMap['CA10003'] = (int)$stmtLegalCnt->fetchColumn();
    
    
    // ---------------------------------------------------------
    // 4. 나머지 카테고리 공통 처리
    // - 현재는 공통 조건으로 콘텐츠 수 산정
    // - 필요 시 이후 각 카테고리도 개별 쿼리로 분리 가능
    // ---------------------------------------------------------
    $stmtEtcCnt = $pdo->prepare("
        SELECT
            category_code,
            COUNT(*) AS total_cnt
        FROM edu_contents
        WHERE (is_active = '1' OR is_active = 'Y')
          AND category_code IN ('CA10004', 'CA10005', 'CA10006')
        GROUP BY category_code
    ");
    $stmtEtcCnt->execute();
    
    foreach ($stmtEtcCnt->fetchAll(PDO::FETCH_ASSOC) as $row) {
    	$totalCntMap[$row['category_code']] = (int)$row['total_cnt'];
    }
    // ---------------------------------------------------------
    // 카테고리별 전체 콘텐츠 수 산정 : End
    // ---------------------------------------------------------
    
    
    $activityItems = [];
    foreach ($categoryBase as $categoryCode => $categoryName) {
        $agg = $aggMap[$categoryCode] ?? null;
        
        $learnedContentCnt   = (int)($agg['learned_content_cnt'] ?? 0);
        $completedContentCnt = (int)($agg['completed_content_cnt'] ?? 0);
        $totalWatchTm        = (int)($agg['total_watch_tm'] ?? 0);
        
        $percent        = (int)($agg['category_percent'] ?? 0);
        
        $totalContentTm      = (int)($agg['total_content_tm'] ?? 0);
        $masterTotalCnt      = (int)($totalCntMap[$categoryCode] ?? 0);
        
        // 1차안: gauge는 학습이력 기준 content_tm 합계가 있을 때 그 기준으로 계산
        // schema상 edu_contents에 표준 duration 컬럼이 없어 완전한 "전체 기준 분모"는 별도 운영정책 확정 필요
        //$percent = ($totalContentTm > 0) ? (int)round(($totalWatchTm / $totalContentTm) * 100) : 0;
        //$percent = max(0, min(100, $percent));
        
        
        $activityItems[] = [
            'category_code'          => $categoryCode,
            'category_name'          => $categoryName,
            'learned_content_cnt'    => $learnedContentCnt,
            'completed_content_cnt'  => $completedContentCnt,
            'master_total_cnt'       => $masterTotalCnt,
            'total_watch_tm'         => $totalWatchTm,
            'total_content_tm'       => $totalContentTm,
            'percent'                => $percent,
            'onboarding_due_date'    => ($categoryCode === 'CA10002') ? $onboardingDueDate : '',
            'is_onboarding_period'   => ($categoryCode === 'CA10002') ? $isOnboardingPeriod : 'N',
        ];
    }
    
    
    response_json([
        'success'            => true,
        'year_list'          => $yearList,
        'selected_year'      => $selectedYear,
        'my_total_minutes'   => $myTotalMinutes,
        'avg_total_minutes'  => $avgTotalMinutes,
        'onboarding_due_date'=> $onboardingDueDate,
        'is_onboarding_period' => $isOnboardingPeriod,
        'activity_items'     => $activityItems,
    ]);
} catch (Throwable $e) {
    response_json([
        'success' => false,
        'message' => '학습활동 데이터를 불러오지 못했습니다.',
        'error'   => $e->getMessage(),
    ]);
}