import os
import shutil
import uuid
from typing import Optional
from fastapi import APIRouter, Form, UploadFile, File, HTTPException
from fastapi.middleware.cors import CORSMiddleware
from sqlalchemy import create_engine, text
from urllib.parse import quote_plus

router = APIRouter()

# === 1. DB 연결 설정 (SQLAlchemy 스타일) ===
# 비밀번호에 특수문자가 있을 경우 quote_plus 처리 필수
password = quote_plus("GOALSkill_99!@") 
# 기본 접속 DB는 Q_DB로 설정 (하지만 다른 DB에도 접근 가능)
DB_URL = f"mysql+pymysql://GOALSkill:{password}@localhost/Q_DB?charset=utf8mb4"

# Connection Pool 생성 (성능 최적화)
engine = create_engine(
    DB_URL,
    pool_size=10,
    max_overflow=20,
    pool_recycle=3600
)

# === 2. 파일 저장 경로 설정 ===
BASE_IMG_Q_PATH = "/home/air/goalskill_t/back/img_q/"
BASE_IMG_EXP_PATH = "/home/air/goalskill_t/back/img_explanation/"

def save_image(file: UploadFile, base_path: str, section_folder: str) -> Optional[str]:
    """이미지 저장 함수 (기존 로직 동일)"""
    if not file:
        return None
    
    target_dir = os.path.join(base_path, section_folder)
    os.makedirs(target_dir, exist_ok=True)
    
    filename = file.filename
    # 확장자가 없는 경우에 대비한 안전장치
    file_ext = filename.split(".")[-1] if "." in filename else "png"
    new_filename = f"{uuid.uuid4()}.{file_ext}"
    
    target_path = os.path.join(target_dir, new_filename)
    
    with open(target_path, "wb") as buffer:
        shutil.copyfileobj(file.file, buffer)
        
    return target_path

@router.post("/goal-skill-t/api/save_question")
async def save_question(
    # --- 파라미터 수신 (기존과 동일) ---
    chapter: str = Form(...),
    question_number: str = Form(...),
    q_text: str = Form(...),
    choice_type: str = Form(...),
    choice_one_text: Optional[str] = Form(None),
    choice_two_text: Optional[str] = Form(None),
    choice_three_text: Optional[str] = Form(None),
    choice_four_text: Optional[str] = Form(None),
    choice_one_img: Optional[UploadFile] = File(None),
    choice_two_img: Optional[UploadFile] = File(None),
    choice_three_img: Optional[UploadFile] = File(None),
    choice_four_img: Optional[UploadFile] = File(None),
    answer: str = Form(...),
    explanation_text: str = Form(...),
    explanation_file: Optional[UploadFile] = File(None)
):
    try:
        # DB 테이블: section_1 (언더바 있음) / 폴더: section1 (언더바 없음)
        table_suffix = f"section_{chapter}"
        folder_suffix = f"section{chapter}"
        
        # --- 이미지 처리 로직 ---
        val_one, val_two, val_three, val_four = "", "", "", ""
        if choice_type == "text":
            val_one = choice_one_text
            val_two = choice_two_text
            val_three = choice_three_text
            val_four = choice_four_text
        else:
            if choice_one_img: val_one = save_image(choice_one_img, BASE_IMG_Q_PATH, folder_suffix)
            if choice_two_img: val_two = save_image(choice_two_img, BASE_IMG_Q_PATH, folder_suffix)
            if choice_three_img: val_three = save_image(choice_three_img, BASE_IMG_Q_PATH, folder_suffix)
            if choice_four_img: val_four = save_image(choice_four_img, BASE_IMG_Q_PATH, folder_suffix)

        sol_img_path = None
        if explanation_file:
            sol_img_path = save_image(explanation_file, BASE_IMG_EXP_PATH, folder_suffix)

        # === DB 저장 (SQLAlchemy 트랜잭션) ===
        # with engine.begin()을 쓰면 성공 시 자동 commit, 실패 시 자동 rollback 됩니다.
        with engine.begin() as conn:
            
            # 1. Q_DB 저장
            # SQLAlchemy에서는 %s 대신 :변수명 을 사용하고 딕셔너리로 값을 넘깁니다.
            sql_q = text(f"INSERT INTO Q_DB.{table_suffix} (question_number, Q) VALUES (:qn, :q)")
            conn.execute(sql_q, {"qn": question_number, "q": q_text})
            
            # 2. Example_DB 저장
            sql_ex = text(f"""
                INSERT INTO Example_DB.{table_suffix} 
                (question_number, one, two, three, four) 
                VALUES (:qn, :v1, :v2, :v3, :v4)
            """)
            conn.execute(sql_ex, {
                "qn": question_number, 
                "v1": val_one, "v2": val_two, "v3": val_three, "v4": val_four
            })
            
            # 3. Correct_Answer_DB 저장
            sql_ans = text(f"INSERT INTO Correct_Answer_DB.{table_suffix} (question_number, A) VALUES (:qn, :ans)")
            conn.execute(sql_ans, {"qn": question_number, "ans": answer})
            
            # 4. Solution_DB 저장
            sql_sol = text(f"INSERT INTO Solution_DB.{table_suffix} (question_number, text, img) VALUES (:qn, :txt, :img)")
            conn.execute(sql_sol, {"qn": question_number, "txt": explanation_text, "img": sol_img_path})
            
        return {"status": "success", "message": f"Question {question_number} saved successfully."}

    except Exception as e:
        print(f"Error occurred: {e}")
        # 파일이 이미 저장되었는데 DB 에러가 났다면, 
        # 엄밀히는 파일도 지워주는게 좋지만 여기서는 생략합니다.
        raise HTTPException(status_code=500, detail=str(e))