-- 1. 生成學生信息表測試數據（10萬條）
INSERT INTO student_info (student_id, student_name, gender, grade, admission_date, major)
SELECT 
    -- 構造學號：G+年級編碼（10=高一，11=高二，12=高三）+班級編碼（01-10）+序號（001-333）
    'G' || (10 + (i % 3)) || lpad(((i % 10) + 1)::VARCHAR, 2, '0') || lpad(((i % 333) + 1)::VARCHAR, 3, '0') AS student_id,
    -- 構造隨機姓名（2-3個漢字，基於Unicode編碼）
    chr(19968 + (random() * 20901)::INT) || 
    chr(19968 + (random() * 20901)::INT) || 
    CASE WHEN random() > 0.5 THEN chr(19968 + (random() * 20901)::INT) ELSE '' END AS student_name,
    -- 隨機性別
    CASE WHEN random() > 0.5 THEN '男' ELSE '女' END AS gender,
    -- 隨機年級班級
    CASE (10 + (i % 3)) 
        WHEN 10 THEN '高一(' || ((i % 10) + 1) || '班)'
        WHEN 11 THEN '高二(' || ((i % 10) + 1) || '班)'
        WHEN 12 THEN '高三(' || ((i % 10) + 1) || '班)'
    END AS grade,
    -- 隨機入學日期（近3年）
    CURRENT_DATE - INTERVAL '3 years' + (random() * INTERVAL '3 years')::INTERVAL AS admission_date,
    -- 隨機專業方向
    CASE WHEN (i % 3) = 0 THEN '文科' WHEN (i % 3) = 1 THEN '理科' ELSE '綜合' END AS major
FROM generate_series(1, 100000) AS t(i);

-- 2. 生成成績表測試數據（10萬條，關聯學生信息表）
INSERT INTO score_detail (student_id, chinese_score, math_score, english_score, politics_score, history_score, physics_score, exam_date)
SELECT 
    si.student_id,
    -- 語文成績：50-100分，10%概率為NULL（模擬數據缺失）
    CASE WHEN random() < 0.1 THEN NULL ELSE (50 + random() * 50)::NUMERIC(5,1) END AS chinese_score,
    -- 數學成績：50-100分，8%概率為NULL
    CASE WHEN random() < 0.08 THEN NULL ELSE (50 + random() * 50)::NUMERIC(5,1) END AS math_score,
    -- 英語成績：50-100分，9%概率為NULL
    CASE WHEN random() < 0.09 THEN NULL ELSE (50 + random() * 50)::NUMERIC(5,1) END AS english_score,
    -- 政治成績：文科生成，理科為NULL
    CASE WHEN si.major = '文科' THEN (50 + random() * 50)::NUMERIC(5,1) ELSE NULL END AS politics_score,
    -- 歷史成績：文科生成，理科為NULL
    CASE WHEN si.major = '文科' THEN (50 + random() * 50)::NUMERIC(5,1) ELSE NULL END AS history_score,
    -- 物理成績：理科生成，文科為NULL
    CASE WHEN si.major = '理科' THEN (50 + random() * 50)::NUMERIC(5,1) ELSE NULL END AS physics_score,
    -- 考試日期：近1年的隨機日期
    CURRENT_DATE - INTERVAL '1 year' + (random() * INTERVAL '1 year')::INTERVAL AS exam_date
FROM student_info si
LIMIT 100000;

-- 3. 生成教師授課表測試數據（10萬條）
INSERT INTO teacher_lesson (teacher_id, teacher_name, lesson_name, lesson_hour, grade, lesson_date)
SELECT 
    -- 構造教師工號：T+學科編碼+序號
    'T' || 
    CASE (i % 6) 
        WHEN 0 THEN 'Chinese' WHEN 1 THEN 'Math' WHEN 2 THEN 'English'
        WHEN 3 THEN 'Politics' WHEN 4 THEN 'History' WHEN 5 THEN 'Physics'
    END || lpad(((i % 100) + 1)::VARCHAR, 2, '0') AS teacher_id,
    -- 構造教師姓名
    chr(19968 + (random() * 20901)::INT) || chr(19968 + (random() * 20901)::INT) AS teacher_name,
    -- 構造課程名稱
    CASE (i % 6) 
        WHEN 0 THEN '高一語文' WHEN 1 THEN '高二數學' WHEN 2 THEN '高三英語'
        WHEN 3 THEN '高一政治' WHEN 4 THEN '高二歷史' WHEN 5 THEN '高三物理'
    END AS lesson_name,
    -- 授課時長：5-60課時，15%概率為NULL
    CASE WHEN random() < 0.15 THEN NULL ELSE (5 + random() * 55)::NUMERIC(5,1) END AS lesson_hour,
    -- 授課年級班級
    CASE (i % 9) 
        WHEN 0 THEN '高一(1)班' WHEN 1 THEN '高一(2)班' WHEN 2 THEN '高一(3)班'
        WHEN 3 THEN '高二(1)班' WHEN 4 THEN '高二(2)班' WHEN 5 THEN '高二(3)班'
        WHEN 6 THEN '高三(1)班' WHEN 7 THEN '高三(2)班' WHEN 8 THEN '高三(3)班'
    END AS grade,
    -- 授課日期：近6個月的隨機日期
    CURRENT_DATE - INTERVAL '6 months' + (random() * INTERVAL '6 months')::INTERVAL AS lesson_date
FROM generate_series(1, 100000) AS t(i);

-- 4. 生成出勤詳情表測試數據（30萬條，每人3條記錄）
INSERT INTO attendance_detail (student_id, attendance_date, is_present, late_minutes)
SELECT 
    si.student_id,
    -- 出勤日期：近1個月的3個隨機日期
    CURRENT_DATE - INTERVAL '1 month' + (random() * INTERVAL '1 month')::INTERVAL AS attendance_date,
    -- 出勤狀態：90%出勤，10%缺勤
    CASE WHEN random() > 0.1 THEN TRUE ELSE FALSE END AS is_present,
    -- 遲到分鐘數：出勤時15%概率遲到（0-30分鐘），缺勤時為0
    CASE WHEN random() > 0.1 THEN 
        CASE WHEN random() < 0.15 THEN (random() * 30)::NUMERIC(5,1) ELSE 0 END
    ELSE 0 END AS late_minutes
FROM student_info si, generate_series(1, 3) AS t(j) -- 每人生成3條記錄
LIMIT 300000;

SELECT 
    student_id,
    student_name,
    chinese_score,
    CASE 
        WHEN chinese_score >= 90 THEN '優秀'
        WHEN chinese_score BETWEEN 80 AND 89 THEN '良好'
        WHEN chinese_score BETWEEN 60 AND 79 THEN '合格'
        WHEN chinese_score < 60 THEN '不合格'
        ELSE '數據缺失'
    END AS score_level
FROM score_detailscore_detail sd JOIN student_info si ON sd.student_id = si.student_id
LIMIT 10;

SELECT 
    s.student_id,
    s.student_name,
    sc.chinese_score,
    a.attendance_rate,
    CASE 
        WHEN sc.chinese_score >= 90 THEN 
            CASE WHEN a.attendance_rate >= 95 THEN '優秀-全勤'
                 ELSE '優秀-缺勤' END
        WHEN sc.chinese_score BETWEEN 80 AND 89 THEN 
            CASE WHEN a.attendance_rate >= 95 THEN '良好-全勤'
                 ELSE '良好-缺勤' END
        WHEN sc.chinese_score BETWEEN 60 AND 79 THEN 
            CASE WHEN a.attendance_rate >= 95 THEN '合格-全勤'
                 ELSE '合格-缺勤' END
        ELSE '不合格/數據缺失'
    END AS comprehensive_level
FROM student_info s
LEFT JOIN score_detail sc ON s.student_id = sc.student_id
LEFT JOIN attendance_detail a ON s.student_id = a.student_id
WHERE s.grade = '高一(4班)';

SELECT s.student_id, s.student_name, AVG(score) AS avg_score, STDDEV(score) AS score_std, CASE WHEN AVG(score) >= 85 AND STDDEV(score) <= 5 THEN '均衡優秀' WHEN AVG(score) >= 85 AND STDDEV(score) > 5 THEN '偏科優秀' WHEN AVG(score) BETWEEN 60 AND 84 THEN '均衡合格' ELSE '偏科不合格' END AS score_balance FROM (SELECT student_id, chinese_score AS score FROM score_detail UNION ALL SELECT student_id, math_score AS score FROM score_detail UNION ALL SELECT student_id, english_score AS score FROM score_detail) AS score_union JOIN student_info s ON score_union.student_id = s.student_id GROUP BY s.student_id, s.student_name HAVING AVG(score) > 50;