from sqlmodel import Session, select
from models import Subject, ErrorImage, Evaluation
from datetime import datetime
from typing import List, Optional

def create_subject(session: Session, name: str, color: str = "#6366f1") -> Subject:
    existing = session.exec(select(Subject).where(Subject.name == name.strip())).first()
    if existing:
        return existing
    subject = Subject(name=name.strip(), color=color)
    session.add(subject)
    session.commit()
    session.refresh(subject)
    return subject

def get_all_subjects(session: Session) -> List[Subject]:
    return list(session.exec(select(Subject).order_by(Subject.created_at.asc())).all())

def get_subject(session: Session, subject_id: int) -> Optional[Subject]:
    return session.get(Subject, subject_id)

def delete_subject(session: Session, subject_id: int) -> bool:
    subject = session.get(Subject, subject_id)
    if not subject:
        return False
    # Delete all images files
    from pathlib import Path
    upload_dir = Path("static/uploads")
    for img in subject.error_images:
        p = upload_dir / img.filename
        if p.exists():
            p.unlink()
    session.delete(subject)
    session.commit()
    return True

def add_error_images(session: Session, subject_id: int, date: str, filenames: List[str], original_names: List[str]):
    for fn, on in zip(filenames, original_names):
        img = ErrorImage(subject_id=subject_id, date=date, filename=fn, original_name=on)
        session.add(img)
    session.commit()

def add_error_text(session: Session, subject_id: int, date: str, question: str, answer: str):
    img = ErrorImage(subject_id=subject_id, date=date, question=question.strip(), answer=answer.strip())
    session.add(img)
    session.commit()
    session.refresh(img)
    return img

def get_subject_images(session: Session, subject_id: int, date: Optional[str] = None) -> List[ErrorImage]:
    stmt = select(ErrorImage).where(ErrorImage.subject_id == subject_id)
    if date:
        stmt = stmt.where(ErrorImage.date == date)
    return list(session.exec(stmt.order_by(ErrorImage.upload_time.asc())).all())

def get_dates_for_subject(session: Session, subject_id: int) -> List[str]:
    stmt = select(ErrorImage.date).where(ErrorImage.subject_id == subject_id).distinct().order_by(ErrorImage.date.desc())
    return list(session.exec(stmt).all())

def get_all_dates(session: Session) -> List[str]:
    stmt = select(ErrorImage.date).distinct().order_by(ErrorImage.date.desc())
    return list(session.exec(stmt).all())

def add_evaluation(session: Session, subject_id: int, date: str, title: str, content: str, filename: Optional[str]) -> Evaluation:
    ev = Evaluation(subject_id=subject_id, date=date, title=title, content=content, filename=filename)
    session.add(ev)
    session.commit()
    session.refresh(ev)
    return ev

def get_evaluations(session: Session, subject_id: Optional[int] = None) -> List[Evaluation]:
    stmt = select(Evaluation).order_by(Evaluation.date.desc(), Evaluation.upload_time.desc())
    if subject_id:
        stmt = stmt.where(Evaluation.subject_id == subject_id)
    return list(session.exec(stmt).all())

def get_review_data(session: Session) -> dict:
    subjects = get_all_subjects(session)
    data = {}
    for s in subjects:
        images = list(session.exec(
            select(ErrorImage).where(ErrorImage.subject_id == s.id).order_by(ErrorImage.date.asc(), ErrorImage.upload_time.asc())
        ).all())
        dates = sorted(set(img.date for img in images))
        data[s.id] = {"subject": s, "images": images, "dates": dates}
    return data

def delete_image(session: Session, image_id: int):
    img = session.get(ErrorImage, image_id)
    if img:
        from pathlib import Path
        path = Path("static/uploads") / img.filename
        if path.exists():
            path.unlink()
        session.delete(img)
        session.commit()
    return img