import fitz
import re
import csv
import json
import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side

pdf_path = r"d:\AI-Area\google-class-202611\antigravity\google-etl\raw_pdfs\115年第三次初級AI應用規劃師_第一科_人工智慧基礎概論_公告試題.pdf"
doc = fitz.open(pdf_path)

lines = []
for page in doc:
    for raw_line in page.get_text("text").splitlines():
        s = raw_line.strip()
        if not s:
            continue
        if re.match(r'^11[45]\s*年第.+AI\s*應用規劃師', s):
            continue
        if re.match(r'^第[一二三]科：', s):
            continue
        if re.match(r'^考試日期：', s):
            continue
        if re.match(r'^第\s*\d+\s*頁，共\s*\d+\s*頁$', s):
            continue
        if s in ('一、選擇題', '答案', '題目', '答案 題目'):
            continue
        lines.append(s)

# Parse into 50 questions
questions = []
i = 0
n = len(lines)

def join_cjk_lines(line_list):
    if not line_list:
        return ""
    res = line_list[0]
    for nxt in line_list[1:]:
        # If previous ends with ASCII/word char and next starts with ASCII/word char, keep a space
        if re.search(r'[A-Za-z0-9\-]$', res) and re.match(r'^[A-Za-z0-9]', nxt):
            res += " " + nxt
        # Keep newline if nxt starts with 甲、乙、丙、丁、 for readability in composite questions (like Q34)
        elif re.match(r'^[甲乙丙丁戊己]、', nxt):
            res += "\n" + nxt
        else:
            res += nxt
    return res.strip()

while i < n:
    # Expect an answer A/B/C/D followed by "1." .. "50."
    if re.match(r'^[A-D]$', lines[i]) and i + 1 < n and re.match(r'^\d+\.$', lines[i+1]):
        ans = lines[i]
        q_no = int(lines[i+1][:-1])
        i += 2
        body_lines = []
        while i < n and not (re.match(r'^[A-D]$', lines[i]) and i + 1 < n and re.match(r'^\d+\.$', lines[i+1])):
            body_lines.append(lines[i])
            i += 1
        
        # Split body_lines into stem and (A), (B), (C), (D)
        stem_lines = []
        opts = {'A': [], 'B': [], 'C': [], 'D': []}
        current_part = 'stem'
        for bl in body_lines:
            m = re.match(r'^\(([A-D])\)(.*)$', bl)
            if m:
                current_part = m.group(1)
                opts[current_part].append(m.group(2).strip())
            else:
                if current_part == 'stem':
                    stem_lines.append(bl)
                else:
                    opts[current_part].append(bl)
        
        q_text = join_cjk_lines(stem_lines)
        opt_a = re.sub(r'[；;。]$', '', join_cjk_lines(opts['A'])).strip()
        opt_b = re.sub(r'[；;。]$', '', join_cjk_lines(opts['B'])).strip()
        opt_c = re.sub(r'[；;。]$', '', join_cjk_lines(opts['C'])).strip()
        opt_d = re.sub(r'[；;。]$', '', join_cjk_lines(opts['D'])).strip()
        
        questions.append({
            "question_no": q_no,
            "answer": ans,
            "question": q_text,
            "option_a": opt_a,
            "option_b": opt_b,
            "option_c": opt_c,
            "option_d": opt_d
        })
    else:
        print(f"Warning: unexpected line at {i}: {lines[i]}")
        i += 1

print(f"Total parsed questions: {len(questions)}")
for q in questions[:3] + [questions[33], questions[-1]]:
    print(f"Q{q['question_no']} [{q['answer']}]: {q['question'][:40]}...")
    print(f"   A: {q['option_a'][:35]} | D: {q['option_d'][:35]}")

# Verify all 50 questions have non-empty fields
for idx, q in enumerate(questions, 1):
    assert q["question_no"] == idx, f"Mismatch q_no {q['question_no']} vs {idx}"
    for k in ["answer", "question", "option_a", "option_b", "option_c", "option_d"]:
        assert q[k], f"Empty {k} in Q{idx}"

# Save to Excel (.xlsx)
xlsx_path = r"d:\AI-Area\google-class-202611\antigravity\google-etl\115_3_elementary_sub1_ai_basics.xlsx"
xlsx_cn_path = r"d:\AI-Area\google-class-202611\antigravity\google-etl\115年第三次初級AI應用規劃師_第一科_人工智慧基礎概論_題庫.xlsx"
csv_path = r"d:\AI-Area\google-class-202611\antigravity\google-etl\115_3_elementary_sub1_ai_basics.csv"

wb = openpyxl.Workbook()
ws = wb.active
ws.title = "questions"

headers = ["question_no", "answer", "question", "option_a", "option_b", "option_c", "option_d"]
ws.append(headers)

header_fill = PatternFill(start_color="1A73E8", end_color="1A73E8", fill_type="solid")
header_font = Font(name="Microsoft JhengHei", size=11, bold=True, color="FFFFFF")
body_font = Font(name="Microsoft JhengHei", size=10)
center_align = Alignment(horizontal="center", vertical="top", wrap_text=True)
left_align = Alignment(horizontal="left", vertical="top", wrap_text=True)
thin_border = Border(
    left=Side(style="thin", color="D0D7DE"),
    right=Side(style="thin", color="D0D7DE"),
    top=Side(style="thin", color="D0D7DE"),
    bottom=Side(style="thin", color="D0D7DE")
)

for col_idx in range(1, len(headers) + 1):
    cell = ws.cell(row=1, column=col_idx)
    cell.fill = header_fill
    cell.font = header_font
    cell.alignment = Alignment(horizontal="center", vertical="center")
    cell.border = thin_border

for row_idx, q in enumerate(questions, start=2):
    row_vals = [q[h] for h in headers]
    ws.append(row_vals)
    for col_idx in range(1, len(headers) + 1):
        cell = ws.cell(row=row_idx, column=col_idx)
        cell.font = body_font
        cell.border = thin_border
        if col_idx in (1, 2):
            cell.alignment = center_align
        else:
            cell.alignment = left_align

# Set column widths and freeze header
col_widths = {
    "A": 14,
    "B": 10,
    "C": 55,
    "D": 34,
    "E": 34,
    "F": 34,
    "G": 34,
}
for col_letter, width in col_widths.items():
    ws.column_dimensions[col_letter].width = width
ws.row_dimensions[1].height = 26
ws.freeze_panes = "A2"

wb.save(xlsx_path)
wb.save(xlsx_cn_path)

# Also save UTF-8 with BOM CSV for universal compatibility
with open(csv_path, "w", encoding="utf-8-sig", newline="") as f:
    writer = csv.DictWriter(f, fieldnames=headers)
    writer.writeheader()
    writer.writerows(questions)

print("Saved Excel and CSV successfully!")