"""Opérations CRUD pour le TaskAPI — SQLAlchemy 2.x."""
from __future__ import annotations

from sqlalchemy import func, select
from sqlalchemy.orm import Session, joinedload, selectinload

from models import Comment, Project, Tag, Task, User


# ── Users ─────────────────────────────────────────────────────

def get_user(db: Session, user_id: int) -> User | None:
    # Pourquoi db.get : lookup par PK avec cache de session — plus rapide que select
    return db.get(User, user_id)


def get_user_by_email(db: Session, email: str) -> User | None:
    stmt = select(User).where(User.email == email)
    return db.execute(stmt).scalar_one_or_none()


def create_user(db: Session, email: str, username: str, hashed_password: str) -> User:
    user = User(email=email, username=username, hashed_password=hashed_password)
    db.add(user)
    db.commit()
    db.refresh(user)
    return user


# ── Tasks ─────────────────────────────────────────────────────

def create_task(
    db: Session,
    title: str,
    project_id: int,
    assignee_id: int | None = None,
    description: str | None = None,
) -> Task:
    task = Task(
        title=title,
        project_id=project_id,
        assignee_id=assignee_id,
        description=description,
    )
    db.add(task)
    db.commit()
    db.refresh(task)
    return task


def get_tasks_by_project(
    db: Session, project_id: int, status: str | None = None
) -> list[Task]:
    # Pourquoi joinedload(Task.assignee) : évite N+1 queries sur la liste de tâches
    stmt = (
        select(Task)
        .where(Task.project_id == project_id)
        .options(joinedload(Task.assignee), selectinload(Task.tags))
        .order_by(Task.created_at.desc())
    )
    if status:
        stmt = stmt.where(Task.status == status)
    return list(db.execute(stmt).unique().scalars().all())


def update_task_status(db: Session, task_id: int, new_status: str) -> Task | None:
    task = db.get(Task, task_id)
    if task is None:
        return None
    # Pourquoi modifier l'attribut directement : SQLAlchemy génère un UPDATE minimal
    task.status = new_status
    db.commit()
    db.refresh(task)
    return task


# ── Statistiques ──────────────────────────────────────────────

def get_project_stats(db: Session, project_id: int) -> dict:
    # Pourquoi une seule requête avec func.sum : plus efficace que 4 COUNT séparés
    stmt = select(
        func.count(Task.id).label("total"),
        func.sum((Task.status == "done").cast("INTEGER")).label("done"),
        func.sum((Task.status == "in_progress").cast("INTEGER")).label("in_progress"),
        func.sum((Task.status == "todo").cast("INTEGER")).label("todo"),
        func.sum((Task.status == "cancelled").cast("INTEGER")).label("cancelled"),
    ).where(Task.project_id == project_id)

    row = db.execute(stmt).one()
    total = row.total or 0
    done = row.done or 0

    return {
        "project_id": project_id,
        "total_tasks": total,
        "done_tasks": done,
        "in_progress_tasks": row.in_progress or 0,
        "todo_tasks": row.todo or 0,
        "cancelled_tasks": row.cancelled or 0,
        "completion_rate": round(done / max(total, 1) * 100, 1),
    }


# ── Tags ──────────────────────────────────────────────────────

def get_or_create_tag(db: Session, name: str, color: str = "#3776ab") -> Tag:
    # Pourquoi get_or_create : évite les doublons sans try/except
    tag = db.execute(select(Tag).where(Tag.name == name)).scalar_one_or_none()
    if tag is None:
        tag = Tag(name=name, color=color)
        db.add(tag)
        db.flush()
    return tag


def add_tag_to_task(db: Session, task_id: int, tag_name: str) -> Task | None:
    task = db.get(Task, task_id)
    if task is None:
        return None
    tag = get_or_create_tag(db, tag_name)
    if tag not in task.tags:
        task.tags.append(tag)
        db.commit()
    return task