<?php
header('Content-Type: application/json; charset=utf-8');
require_once __DIR__ . '/../../bbs/db_conn.php';
require_once __DIR__ . '/../../bbs/auth.php';
edu_require_login();

$pdo = db_conn();

$year = $_GET['year'] ?? date('Y');
$quarter_raw = $_GET['quarter'] ?? ceil(date('n') / 3);

// Convert quarter code (e.g., CA200Q01, CA200001) to integer 1~4
if (is_string($quarter_raw) && strpos($quarter_raw, 'CA200') === 0) {
    $quarter = (int) substr($quarter_raw, -1);
} else {
    $quarter = (int) $quarter_raw;
}
if ($quarter < 1 || $quarter > 4)
    $quarter = 1;

// Calculate start and end dates for the quarter
$p_fr_dt = $year . '-' . sprintf('%02d', ($quarter - 1) * 3 + 1) . '-01';
$p_to_dt = date('Y-m-t', strtotime($year . '-' . sprintf('%02d', $quarter * 3) . '-01'));
$p_fr_dt_full = $p_fr_dt . ' 00:00:00';
$p_to_dt_full = $p_to_dt . ' 23:59:59';

$response = [
    'success' => true,
    'data' => []
];

try {
    // 1. KPI
    $stmt_kpi = $pdo->prepare("CALL proc_get_dashboard_kpi(?, ?)");
    $stmt_kpi->execute([$year, $quarter]);
    $kpi = $stmt_kpi->fetch(PDO::FETCH_ASSOC);
    while ($stmt_kpi->nextRowset()) {
    } // Clear extra rowsets
    $stmt_kpi->closeCursor();

    // Ensure all variables exist, with defaults
    $kpi = $kpi ?: [];
    // DEBUG: Write kpi to file
    file_put_contents(__DIR__ . '/kpi_debug.json', json_encode($kpi, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE));

    $kpi = array_change_key_case($kpi, CASE_LOWER);

    // Add computed metrics for KPI based on User's input
    $access_qty = isset($kpi['access_qty']) ? (float) $kpi['access_qty'] : 0;
    $completed_qty = isset($kpi['completed_qty']) ? (float) $kpi['completed_qty'] : 0;
    $access_qty_u = isset($kpi['access_qty_u']) ? (float) $kpi['access_qty_u'] : 0;
    $completed_qty_u = isset($kpi['completed_qty_u']) ? (float) $kpi['completed_qty_u'] : 0;

    $kpi['computed_content_per_user'] = $access_qty > 0 ? round($completed_qty / $access_qty, 1) : 0;
    $prev_content_per_user = $access_qty_u > 0 ? round($completed_qty_u / $access_qty_u, 1) : 0;
    $kpi['computed_content_per_user_diff'] = round($kpi['computed_content_per_user'] - $prev_content_per_user, 1);

    $legal_qty_all = isset($kpi['legal_qty_all']) ? (int) $kpi['legal_qty_all'] : 0;
    $legal_qty_y = isset($kpi['legal_qty_y']) ? (int) $kpi['legal_qty_y'] : 0;
    $kpi['computed_legal_uncompleted'] = $legal_qty_all - $legal_qty_y;
    $kpi['computed_legal_rate'] = $legal_qty_all > 0 ? round(($legal_qty_y / $legal_qty_all) * 100) : 0;

    $quarter_qty = isset($kpi['quarter_qty']) ? (int) $kpi['quarter_qty'] : 0;
    $goals_qty = isset($kpi['goals_qty']) ? (int) $kpi['goals_qty'] : (isset($kpi['goal_qty']) ? (int) $kpi['goal_qty'] : 0);
    $goals_completed_qty = isset($kpi['goals_completed_qty']) ? (int) $kpi['goals_completed_qty'] : (isset($kpi['goal_completed_qty']) ? (int) $kpi['goal_completed_qty'] : 0);

    $kpi['myclass_target_qty'] = $goals_qty;
    $kpi['myclass_target_rate'] = $quarter_qty > 0 ? round(($goals_qty / $quarter_qty) * 100) : 0;

    $kpi['myclass_achieve_qty'] = $goals_completed_qty;
    $kpi['myclass_achieve_rate'] = $goals_qty > 0 ? round(($goals_completed_qty / $goals_qty) * 100) : 0;

    $response['data']['kpi'] = $kpi;

    // 2. 법인별 접속률
    $stmt_corp = $pdo->prepare("
        SELECT c.code_name 
            , COUNT(DISTINCT b.member_id) as all_qty
            , IFNULL(d.access_qty, 0) AS access_qty
            , IFNULL(ROUND(IFNULL(d.access_qty, 0) / NULLIF(COUNT(DISTINCT b.member_id), 0) * 100,0),0) as access_rate
            , IFNULL(COUNT(DISTINCT b.member_id) - IFNULL(d.access_qty, 0),0) AS no_access_qty
        FROM edu_codes c
        LEFT JOIN edu_users b ON c.code = b.sys_comp_code AND (b.end_date IS NULL OR b.end_date > CURDATE())
        LEFT JOIN (SELECT COUNT(0) AS access_qty , sys_comp_code
                        FROM 	(SELECT a.member_id, a.sys_comp_code
                                FROM edu_access_logs a
                                WHERE 1=1
                                AND accessed_at >= ?
                                AND accessed_at <= ?
                                GROUP BY a.member_id, a.sys_comp_code) x
                        GROUP BY sys_comp_code ) d ON c.code = d.sys_comp_code
        WHERE c.group_code = 'CO100' AND c.is_active = '1'
        GROUP BY c.code, c.code_name
        ORDER BY c.code ASC
    ");
    $stmt_corp->execute([$p_fr_dt, $p_to_dt]);
    $response['data']['corp_access_rate'] = $stmt_corp->fetchAll(PDO::FETCH_ASSOC);

    // 3. 학습자 접속 빈도
    $stmt_freq = $pdo->prepare("
        SELECT 
            SUM(CASE WHEN IFNULL(a.qty, 0) >= 12 THEN 1 ELSE 0 END) AS active_lv01,
            SUM(CASE WHEN IFNULL(a.qty, 0) BETWEEN 5 AND 11 THEN 1 ELSE 0 END) AS normal_lv02,
            SUM(CASE WHEN IFNULL(a.qty, 0) BETWEEN 1 AND 4 THEN 1 ELSE 0 END) AS low_lv03,
            SUM(CASE WHEN IFNULL(a.qty, 0) = 0 THEN 1 ELSE 0 END) AS none_lv04
        FROM edu_users u
        LEFT JOIN (
            SELECT member_id, sys_comp_code, COUNT(DISTINCT DATE(accessed_at)) AS qty
            FROM edu_access_logs
            WHERE accessed_at BETWEEN ? AND ?
            GROUP BY member_id, sys_comp_code
        ) a ON u.member_id = a.member_id AND u.sys_comp_code = a.sys_comp_code
        WHERE 1=1
        AND (end_date IS NULL OR end_date > CURDATE())
    ");
    $stmt_freq->execute([$p_fr_dt, $p_to_dt]);
    $response['data']['access_freq'] = $stmt_freq->fetch(PDO::FETCH_ASSOC);

    // 4. 접속 시간대
    $stmt_time = $pdo->prepare("
         SELECT 
              SUM(CASE WHEN substring(accessed_at,12,5)>= '09:00' AND substring(accessed_at,12,5)<= '17:00' THEN 1 ELSE 0 END)-
                SUM(CASE WHEN substring(accessed_at,12,5)>= '11:30' AND substring(accessed_at,12,5)<= '13:30' THEN 1 ELSE 0 END)  AS worktime
             , SUM(CASE WHEN substring(accessed_at,12,5)>= '11:30' AND substring(accessed_at,12,5)<= '13:30' THEN 1 ELSE 0 END)  AS lunchtime
             , SUM(CASE WHEN substring(accessed_at,12,5) < '09:00' or substring(accessed_at,12,5) > '17:00' THEN 1 ELSE 0 END)  AS outtime
             , SUM(CASE WHEN 1=1 THEN 1 ELSE 0 END) AS alltime
         FROM edu_access_logs
         WHERE 1=1
         AND accessed_at BETWEEN ? AND ?
    ");
    $stmt_time->execute([$p_fr_dt_full, $p_to_dt_full]);
    $response['data']['access_time'] = $stmt_time->fetch(PDO::FETCH_ASSOC);

    // 5. 접속 방법
    $stmt_device = $pdo->prepare("
         SELECT 
              SUM(CASE WHEN device_type = 'PC' THEN 1 ELSE 0 END) AS pc
             , SUM(CASE WHEN device_type = 'Mobile' THEN 1 ELSE 0 END) AS mobile
             , SUM(CASE WHEN 1=1 THEN 1 ELSE 0 END) AS alldevice
         FROM edu_access_logs
         WHERE 1=1
         AND accessed_at BETWEEN ? AND ?
    ");
    $stmt_device->execute([$p_fr_dt_full, $p_to_dt_full]);
    $response['data']['access_device'] = $stmt_device->fetch(PDO::FETCH_ASSOC);

    // 6. 카테고리별 이용현황
    $stmt_usage = $pdo->prepare("
        SELECT 
                  SUM(CASE WHEN 1=1 THEN 1 ELSE 0 END) AS tot_qty
                , SUM(CASE WHEN a.category_code = 'CA10001' THEN 1 ELSE 0 END) AS myclass_use_qty
                , SUM(CASE WHEN a.category_code = 'CA10004' THEN 1 ELSE 0 END) AS leader_use_qty
                , SUM(CASE WHEN a.category_code = 'CA10005' THEN 1 ELSE 0 END) AS insight_use_qty
                , SUM(CASE WHEN a.category_code = 'CA10006' THEN 1 ELSE 0 END) AS biz_use_qty
        FROM edu_contents a
        JOIN edu_learning_histories b ON a.content_id = b.content_id
        WHERE 1=1
        AND b.last_viewed_at BETWEEN ? AND ?
        AND a.category_code IN('CA10001','CA10004','CA10005','CA10006')
    ");
    $stmt_usage->execute([$p_fr_dt_full, $p_to_dt_full]);
    $response['data']['content_usage'] = $stmt_usage->fetch(PDO::FETCH_ASSOC);

    // 7. 이번분기 신규콘텐츠 등록수량
    $stmt_new = $pdo->prepare("
        SELECT 
            IFNULL(SUM(category_code = 'CA10001'), 0) AS myclass_new_qty,
            IFNULL(SUM(category_code = 'CA10004'), 0) AS leader_new_qty,
            IFNULL(SUM(category_code = 'CA10005'), 0) AS insight_new_qty,
            IFNULL(SUM(category_code = 'CA10006'), 0) AS biz_new_qty
        FROM edu_contents
        WHERE created_at BETWEEN ? AND ?
    ");
    $stmt_new->execute([$p_fr_dt_full, $p_to_dt_full]);
    $response['data']['new_content_qty'] = $stmt_new->fetch(PDO::FETCH_ASSOC);

    // 8. 가장 많이 본 콘텐츠
    $stmt_popular = $pdo->prepare("
        SELECT 
               b.category_code
             , fn_get_code_name(b.category_code) AS category_name
             , b.title AS content_title
             , COUNT(a.content_id) as view_count
             , SUM(CASE WHEN a.completed_at IS NOT NULL OR a.watch_tm/a.content_tm >= 0.7 THEN 1 ELSE 0 END)/COUNT(a.content_id)*100 AS completed_rate
             , c.comment_cnt
        FROM edu_learning_histories a
        JOIN edu_contents b ON a.content_id = b.content_id
        LEFT JOIN ( SELECT y.category_code , x.content_id , COUNT(0) AS comment_cnt
                        FROM edu_comments x
                        JOIN edu_contents y ON x.content_id = y.content_id
                        WHERE x.created_at BETWEEN ? AND ?
                        GROUP BY y.category_code , x.content_id) c ON a.content_id = c.content_id
        WHERE 1=1
        AND b.category_code IN('CA10001','CA10004','CA10005','CA10006')
        AND a.last_viewed_at BETWEEN ? AND ?
        GROUP BY b.category_code, b.content_id, b.title
        ORDER BY COUNT(a.content_id) DESC
        LIMIT 50
    ");
    $stmt_popular->execute([$p_fr_dt_full, $p_to_dt_full, $p_fr_dt_full, $p_to_dt_full]);
    $response['data']['popular_contents'] = $stmt_popular->fetchAll(PDO::FETCH_ASSOC);

} catch (Throwable $e) {
    $response['success'] = false;
    $response['message'] = $e->getMessage();
    $response['trace'] = $e->getTraceAsString();
}

echo json_encode($response);