"""Members inactive for N days — stored for admin Need Follow-Up tab."""

from __future__ import annotations

from datetime import datetime, timedelta, timezone

from sqlalchemy import inspect, text
from sqlalchemy.engine import Engine
from sqlalchemy.orm import Session, joinedload

from app.models import User, UserRole
from app.models_admin import MemberInactiveFollowUp
from app.programme.services.programme_service import user_programme_load_options
from app.services.admin_member_service import serialize_admin_member

DEFAULT_INACTIVE_DAYS = 10

_CREATE_MEMBER_INACTIVE_FOLLOWUPS_PG = """
CREATE TABLE IF NOT EXISTS member_inactive_followups (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE,
    last_active_at TIMESTAMP WITH TIME ZONE,
    days_inactive INTEGER NOT NULL DEFAULT 0,
    follow_up_sent_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL
);
CREATE INDEX IF NOT EXISTS ix_member_inactive_followups_user_id ON member_inactive_followups (user_id);
CREATE INDEX IF NOT EXISTS ix_member_inactive_followups_days_inactive ON member_inactive_followups (days_inactive);
"""


def sync_member_inactive_followups_schema(engine: Engine) -> None:
    is_pg = engine.dialect.name == "postgresql"
    try:
        insp = inspect(engine)
        table_exists = "member_inactive_followups" in insp.get_table_names()
    except Exception:
        table_exists = False

    if not table_exists and is_pg:
        with engine.begin() as conn:
            conn.execute(text(_CREATE_MEMBER_INACTIVE_FOLLOWUPS_PG))
        return

    if not table_exists:
        from app import models_admin  # noqa: F401
        from app.database import Base

        Base.metadata.tables["member_inactive_followups"].create(bind=engine, checkfirst=True)


def sync_users_last_active_at_column(engine: Engine) -> None:
    try:
        insp = inspect(engine)
        if "users" not in insp.get_table_names():
            return
        cols = {c["name"] for c in insp.get_columns("users")}
    except Exception:
        return

    if "last_active_at" in cols:
        return

    is_pg = engine.dialect.name == "postgresql"
    with engine.begin() as conn:
        if is_pg:
            conn.execute(
                text(
                    "ALTER TABLE users ADD COLUMN IF NOT EXISTS "
                    "last_active_at TIMESTAMP WITH TIME ZONE"
                )
            )
        else:
            try:
                conn.execute(text("ALTER TABLE users ADD COLUMN last_active_at DATETIME"))
            except Exception:
                pass


def touch_user_last_active(db: Session, user: User, *, commit: bool = True) -> None:
    """Record member activity (login or authenticated API use)."""
    if user.role != UserRole.user:
        return
    now = datetime.now(timezone.utc)
    last = user.last_active_at
    if last is not None:
        if last.tzinfo is None:
            last = last.replace(tzinfo=timezone.utc)
        if (now - last).total_seconds() < 900:
            return
    user.last_active_at = now
    if commit:
        db.commit()


def _effective_last_active(user: User) -> datetime | None:
    if user.last_active_at is not None:
        return user.last_active_at
    if user.updated_at is not None:
        return user.updated_at
    return user.created_at


def sync_member_inactive_followups(db: Session, *, inactive_days: int = DEFAULT_INACTIVE_DAYS) -> int:
    """
    Rebuild member_inactive_followups from current user activity.
    Returns count of rows currently in the table.
    """
    inactive_days = max(1, int(inactive_days))
    cutoff = datetime.now(timezone.utc) - timedelta(days=inactive_days)

    members = (
        db.query(User)
        .filter(User.role == UserRole.user, User.is_active.is_(True))
        .all()
    )

    inactive_user_ids: set[int] = set()
    for member in members:
        last = _effective_last_active(member)
        if last is None:
            continue
        if last.tzinfo is None:
            last = last.replace(tzinfo=timezone.utc)
        if last <= cutoff:
            inactive_user_ids.add(member.id)

    existing_rows = db.query(MemberInactiveFollowUp).all()
    existing_by_user = {row.user_id: row for row in existing_rows}

    now = datetime.now(timezone.utc)
    for user_id in inactive_user_ids:
        member = next((m for m in members if m.id == user_id), None)
        if member is None:
            continue
        last = _effective_last_active(member)
        if last and last.tzinfo is None:
            last = last.replace(tzinfo=timezone.utc)
        days = max(inactive_days, (now - last).days if last else inactive_days)

        row = existing_by_user.get(user_id)
        if row is None:
            db.add(
                MemberInactiveFollowUp(
                    user_id=user_id,
                    last_active_at=last,
                    days_inactive=days,
                )
            )
        else:
            row.last_active_at = last
            row.days_inactive = days

    for user_id, row in existing_by_user.items():
        if user_id not in inactive_user_ids:
            db.delete(row)

    db.commit()
    return len(inactive_user_ids)


def list_member_inactive_followups(
    db: Session,
    *,
    inactive_days: int = DEFAULT_INACTIVE_DAYS,
    skip: int = 0,
    limit: int = 50,
    sync: bool = True,
) -> tuple[list[dict], int, int]:
    if sync:
        sync_member_inactive_followups(db, inactive_days=inactive_days)

    query = (
        db.query(MemberInactiveFollowUp)
        .options(joinedload(MemberInactiveFollowUp.user).options(*user_programme_load_options()))
        .order_by(MemberInactiveFollowUp.days_inactive.desc(), MemberInactiveFollowUp.id.desc())
    )
    total = query.count()
    rows = query.offset(skip).limit(limit).all()

    serialized: list[dict] = []
    for row in rows:
        user = row.user
        if not user:
            continue
        member_public = serialize_admin_member(user, db)
        serialized.append(
            {
                "id": row.id,
                "user_id": row.user_id,
                "full_name": user.full_name or user.email,
                "email": user.email,
                "last_active_at": row.last_active_at,
                "days_inactive": row.days_inactive,
                "follow_up_sent": row.follow_up_sent_at is not None,
                "follow_up_sent_at": row.follow_up_sent_at,
                "member": member_public.model_dump(),
            }
        )
    return serialized, total, inactive_days


def mark_member_inactive_follow_up_sent(db: Session, user_id: int) -> MemberInactiveFollowUp | None:
    row = db.query(MemberInactiveFollowUp).filter(MemberInactiveFollowUp.user_id == user_id).first()
    if not row:
        return None
    row.follow_up_sent_at = datetime.now(timezone.utc)
    db.commit()
    db.refresh(row)
    return row