"""Per-person reactions: autosave upserts, the review bundle, the rollup.

Every tap PATCHes immediately with upsert semantics (locked decision D12) — a
partial four-minute session is still usable data, and repeating a tap is
idempotent. Reviews are per-person and never shared (D7): the bundle returns
only the caller's own rows, and the rollup is an admin-only view that keeps
client and admin reactions separate rather than averaging them together.
"""

from datetime import UTC, datetime

from sqlalchemy import delete as sql_delete
from sqlalchemy import func, select
from sqlalchemy.dialects.postgresql import insert
from sqlalchemy.orm import Session

from app.models.account import Account
from app.services import levels
from app.models.option import AspectVote, FinalPick, Option, Review
from app.models.project import ProjectMember


def upsert_review(
    db: Session,
    *,
    project_id: int,
    option_id: int,
    username: str,
    rating: int | None,
    note: str | None,
    set_rating: bool,
    set_note: bool,
) -> Review:
    """Insert-or-update the caller's review row, touching only the fields the
    request actually carried — a rating tap must not blank a note saved earlier,
    and vice versa."""
    stmt = insert(Review).values(
        option_id=option_id,
        username=username,
        project_id=project_id,
        rating=rating if set_rating else None,
        note=(note or "") if set_note else "",
    )
    update_fields: dict = {"updated_at": func.now()}
    if set_rating:
        update_fields["rating"] = rating
    if set_note:
        update_fields["note"] = note or ""
    stmt = stmt.on_conflict_do_update(
        index_elements=[Review.option_id, Review.username], set_=update_fields
    )
    db.execute(stmt)
    db.commit()
    return db.get(Review, (option_id, username))


def set_aspect_vote(
    db: Session, *, option_id: int, username: str, aspect: str, vote: int
) -> None:
    """vote=+1/-1 upserts; vote=0 clears (deletes the row — absence means 'did
    not notice', which is a different datum from a neutral vote)."""
    if vote == 0:
        db.execute(
            sql_delete(AspectVote).where(
                AspectVote.option_id == option_id,
                AspectVote.username == username,
                AspectVote.aspect == aspect,
            )
        )
    else:
        stmt = insert(AspectVote).values(
            option_id=option_id, username=username, aspect=aspect, vote=vote
        )
        stmt = stmt.on_conflict_do_update(
            index_elements=[AspectVote.option_id, AspectVote.username, AspectVote.aspect],
            set_={"vote": vote, "updated_at": func.now()},
        )
        db.execute(stmt)
    db.commit()


def upsert_final_pick(
    db: Session,
    *,
    project_id: int,
    username: str,
    option_id: int | None,
    closing_note: str | None,
    set_option: bool,
    set_note: bool,
    completed: bool | None,
) -> FinalPick:
    pick = db.get(FinalPick, (project_id, username))
    if pick is None:
        pick = FinalPick(project_id=project_id, username=username)
        db.add(pick)
    if set_option:
        pick.option_id = option_id
    if set_note:
        pick.closing_note = closing_note or ""
    if completed is True and pick.completed_at is None:
        pick.completed_at = datetime.now(UTC)
    pick.updated_at = datetime.now(UTC)
    db.commit()
    db.refresh(pick)
    return pick


def my_reviews(db: Session, project_id: int, username: str) -> list[Review]:
    stmt = select(Review).where(Review.project_id == project_id, Review.username == username)
    return list(db.scalars(stmt))


def my_aspect_votes(db: Session, project_id: int, username: str) -> list[AspectVote]:
    stmt = (
        select(AspectVote)
        .join(Option, Option.id == AspectVote.option_id)
        .where(Option.project_id == project_id, AspectVote.username == username)
    )
    return list(db.scalars(stmt))


def my_final_pick(db: Session, project_id: int, username: str) -> FinalPick | None:
    return db.get(FinalPick, (project_id, username))


def rollup(db: Session, project_id: int) -> dict:
    """Everything the admin results view and the Scouting Report are built from.

    Client reactions are the product; admin rows ride along
    separately so rian's own ratings are visible without polluting the client
    story. Returns plain dicts keyed by option id / username for the router to
    shape into response models.
    """
    member_rows = db.execute(
        select(ProjectMember.username, Account)
        .join(Account, Account.username == ProjectMember.username)
        .where(ProjectMember.project_id == project_id)
    ).all()
    accounts: dict[str, Account] = {username: account for username, account in member_rows}

    reviews = list(db.scalars(select(Review).where(Review.project_id == project_id)))
    votes = list(
        db.scalars(
            select(AspectVote)
            .join(Option, Option.id == AspectVote.option_id)
            .where(Option.project_id == project_id)
        )
    )
    picks = list(db.scalars(select(FinalPick).where(FinalPick.project_id == project_id)))

    # A reviewer who reacted but is no longer (or never was) a member — e.g. a
    # manager previewing — still needs a display name and a level.
    extra_usernames = {r.username for r in reviews} | {p.username for p in picks} | {
        v.username for v in votes
    }
    for username in extra_usernames - set(accounts):
        account = db.get(Account, username)
        if account is not None:
            accounts[username] = account

    def is_client(username: str) -> bool:
        """A real client answer, as opposed to a staff preview.

        Resolved against THIS project: someone who manages this project is
        previewing, and their ratings are shown separately rather than averaged
        into the client result (D7).
        """
        account = accounts.get(username)
        if account is None:
            return False
        return not levels.project_can(username, project_id, levels.PROJECT_MANAGE)

    return {
        "accounts": accounts,
        "reviews": reviews,
        "votes": votes,
        "picks": picks,
        "is_client": is_client,
    }