from __future__ import annotations

from datetime import UTC, datetime
from pathlib import Path
from shutil import copy2
from typing import Any

from fastapi import APIRouter, Depends, HTTPException
from sqlalchemy import or_, select
from sqlalchemy.orm import Session

from .. import models
from ..auth import get_optional_local_session, require_household_access
from ..config import get_settings
from ..db import get_session
from ..schemas import BackupResult, HouseholdExport

router = APIRouter(tags=["backup"])


def _row(model: object) -> dict[str, Any]:
    table = model.__table__
    return {column.name: getattr(model, column.name) for column in table.columns}


def _rows(items: list[object]) -> list[dict[str, Any]]:
    return [_row(item) for item in items]


@router.get("/backup/export", response_model=HouseholdExport)
def export_household(
    household_id: str,
    session: Session = Depends(get_session),
    local_session: models.LocalSession | None = Depends(get_optional_local_session),
) -> HouseholdExport:
    require_household_access(household_id, local_session)
    household = session.get(models.Household, household_id)
    if not household:
        raise HTTPException(status_code=404, detail="Household not found")

    users = list(session.scalars(select(models.User).where(models.User.household_id == household_id)).all())
    accounts = list(
        session.scalars(select(models.Account).where(models.Account.household_id == household_id)).all()
    )
    observations = list(
        session.scalars(
            select(models.RawObservation).where(models.RawObservation.household_id == household_id)
        ).all()
    )
    events = list(
        session.scalars(
            select(models.FinancialEvent).where(models.FinancialEvent.household_id == household_id)
        ).all()
    )
    categories = list(
        session.scalars(select(models.Category).where(models.Category.household_id == household_id)).all()
    )
    tags = list(session.scalars(select(models.Tag).where(models.Tag.household_id == household_id)).all())
    projects = list(
        session.scalars(select(models.Project).where(models.Project.household_id == household_id)).all()
    )
    recurring = list(
        session.scalars(
            select(models.RecurringCommitment).where(models.RecurringCommitment.household_id == household_id)
        ).all()
    )
    promised = list(
        session.scalars(
            select(models.PromisedPayment).where(models.PromisedPayment.household_id == household_id)
        ).all()
    )
    connections = list(
        session.scalars(
            select(models.ImportConnection).where(models.ImportConnection.household_id == household_id)
        ).all()
    )
    import_runs = list(
        session.scalars(select(models.ImportRun).where(models.ImportRun.household_id == household_id)).all()
    )
    audit_log = list(
        session.scalars(select(models.AuditLog).where(models.AuditLog.household_id == household_id)).all()
    )

    event_ids = [event.id for event in events]
    observation_ids = [observation.id for observation in observations]
    project_ids = [project.id for project in projects]
    event_links = (
        list(
            session.scalars(
                select(models.EventObservationLink).where(
                    or_(
                        models.EventObservationLink.event_id.in_(event_ids),
                        models.EventObservationLink.observation_id.in_(observation_ids),
                    )
                )
            ).all()
        )
        if event_ids or observation_ids
        else []
    )
    event_relations = (
        list(
            session.scalars(
                select(models.EventRelation).where(
                    or_(
                        models.EventRelation.from_event_id.in_(event_ids),
                        models.EventRelation.to_event_id.in_(event_ids),
                    )
                )
            ).all()
        )
        if event_ids
        else []
    )
    split_lines = (
        list(
            session.scalars(select(models.SplitLine).where(models.SplitLine.event_id.in_(event_ids))).all()
        )
        if event_ids
        else []
    )
    event_tags = (
        list(session.scalars(select(models.EventTag).where(models.EventTag.event_id.in_(event_ids))).all())
        if event_ids
        else []
    )
    project_participants = (
        list(
            session.scalars(
                select(models.ProjectParticipant).where(models.ProjectParticipant.project_id.in_(project_ids))
            ).all()
        )
        if project_ids
        else []
    )
    budget_lines = (
        list(
            session.scalars(
                select(models.ProjectBudgetLine).where(models.ProjectBudgetLine.project_id.in_(project_ids))
            ).all()
        )
        if project_ids
        else []
    )
    planned_items = (
        list(
            session.scalars(
                select(models.ProjectPlannedItem).where(models.ProjectPlannedItem.project_id.in_(project_ids))
            ).all()
        )
        if project_ids
        else []
    )
    planned_item_ids = [item.id for item in planned_items]
    payment_lines = (
        list(
            session.scalars(
                select(models.PaymentLine).where(
                    or_(
                        models.PaymentLine.event_id.in_(event_ids),
                        models.PaymentLine.planned_item_id.in_(planned_item_ids),
                    )
                )
            ).all()
        )
        if event_ids or planned_item_ids
        else []
    )

    data = {
        "users": _rows(users),
        "accounts": _rows(accounts),
        "raw_observations": _rows(observations),
        "financial_events": _rows(events),
        "event_observation_links": _rows(event_links),
        "event_relations": _rows(event_relations),
        "split_lines": _rows(split_lines),
        "categories": _rows(categories),
        "tags": _rows(tags),
        "event_tags": _rows(event_tags),
        "projects": _rows(projects),
        "project_participants": _rows(project_participants),
        "project_budget_lines": _rows(budget_lines),
        "project_planned_items": _rows(planned_items),
        "payment_lines": _rows(payment_lines),
        "recurring_commitments": _rows(recurring),
        "promised_payments": _rows(promised),
        "import_connections": _rows(connections),
        "import_runs": _rows(import_runs),
        "audit_log": _rows(audit_log),
    }
    return HouseholdExport(
        household=_row(household),
        exported_at=datetime.now(UTC),
        counts={key: len(value) for key, value in data.items()},
        data=data,
    )


@router.post("/backup/sqlite", response_model=BackupResult)
def backup_sqlite() -> BackupResult:
    settings = get_settings()
    if not settings.database_url.startswith("sqlite:///"):
        raise HTTPException(status_code=400, detail="SQLite backup is only available for sqlite:/// databases")
    db_path_text = settings.database_url.replace("sqlite:///", "", 1)
    if db_path_text in {"", ":memory:"}:
        raise HTTPException(status_code=400, detail="In-memory SQLite databases cannot be backed up to a file")
    db_path = Path(db_path_text)
    if not db_path.is_absolute():
        db_path = Path.cwd() / db_path
    if not db_path.exists():
        raise HTTPException(status_code=404, detail="SQLite database file not found")
    backup_dir = db_path.parent / "backups"
    backup_dir.mkdir(parents=True, exist_ok=True)
    backup_path = backup_dir / f"{db_path.stem}-{datetime.now(UTC).strftime('%Y%m%d-%H%M%S')}.db"
    copy2(db_path, backup_path)
    return BackupResult(
        file_path=str(backup_path),
        size_bytes=backup_path.stat().st_size,
        created_at=datetime.now(UTC),
    )