from datetime import date, datetime, timezone

from sqlalchemy import or_
from sqlalchemy.orm import Session, joinedload

from app.models import FollowUp, Lead
from app.schemas import LeadCreate, LeadUpdate
from app.services import (
    build_follow_ups,
    compute_fu_state,
    enrich_lead,
    lead_sequence_status,
    mark_follow_up,
    next_lead_code,
    reschedule_follow_ups,
)


STATUS_FILTER_MAP = {
    "Due Today": "due_today",
    "Due Today ⚡": "due_today",
    "Overdue": "overdue",
    "Overdue 🚨": "overdue",
    "In Progress": "in_progress",
    "All Completed": "completed",
    "All Completed ✓": "completed",
    "Completed": "completed",
    "Not Started": "not_started",
}


def list_leads(
    db: Session,
    *,
    q: str | None = None,
    stage: str | None = None,
    status: str | None = None,
    sort: str | None = None,
) -> list[Lead]:
    query = db.query(Lead).options(joinedload(Lead.follow_ups))
    if q:
        like = f"%{q.strip()}%"
        query = query.filter(
            or_(
                Lead.contact_name.ilike(like),
                Lead.client_email.ilike(like),
                Lead.our_email.ilike(like),
                Lead.website.ilike(like),
                Lead.lead_code.ilike(like),
                Lead.notes.ilike(like),
            )
        )
    if stage and stage != "All Stages":
        query = query.filter(Lead.stage == stage)

    leads = query.all()
    today = date.today()
    enriched = [enrich_lead(lead, today) for lead in leads]

    if status and status not in ("All Statuses", "", None):
        key = STATUS_FILTER_MAP.get(status, status.lower().replace(" ", "_"))
        enriched = [l for l in enriched if (l.sequence_status or "") == key]

    sort_key = (sort or "Sheet Order (Default)").lower()
    if "id" in sort_key:
        enriched.sort(key=lambda l: l.lead_code)
    elif "date" in sort_key:
        enriched.sort(key=lambda l: l.date_added, reverse=True)
    elif "progress" in sort_key:
        enriched.sort(key=lambda l: l.progress_pct or 0, reverse=True)
    else:
        enriched.sort(key=lambda l: (l.sort_order, l.id))

    return enriched


def get_lead(db: Session, lead_id: int) -> Lead | None:
    lead = (
        db.query(Lead)
        .options(joinedload(Lead.follow_ups))
        .filter(Lead.id == lead_id)
        .first()
    )
    return enrich_lead(lead) if lead else None


def create_lead(db: Session, payload: LeadCreate) -> Lead:
    code = (payload.lead_code or "").strip() or next_lead_code(db)
    existing = db.query(Lead).filter(Lead.lead_code == code).first()
    if existing:
        raise ValueError(f"Lead code {code} already exists")

    lead = Lead(
        lead_code=code,
        contact_name=payload.contact_name,
        client_email=payload.client_email,
        our_email=payload.our_email,
        website=payload.website,
        date_added=payload.date_added,
        stage=payload.stage or "New Lead",
        deal_value=payload.deal_value,
        notes=payload.notes,
        call_scheduled=payload.call_scheduled or payload.stage == "Call Scheduled",
        sort_order=db.query(Lead).count(),
        follow_ups=build_follow_ups(payload.date_added),
    )
    db.add(lead)
    db.commit()
    db.refresh(lead)
    return enrich_lead(get_lead(db, lead.id))  # type: ignore[arg-type]


def import_leads(db: Session, rows: list[dict]) -> list[Lead]:
    """Bulk-create leads from normalized CSV rows. Generates FU 1–7 for each."""
    if not rows:
        return []

    existing_codes = {code for (code,) in db.query(Lead.lead_code).all() if code}
    sort_base = db.query(Lead).count()
    created: list[Lead] = []

    def allocate_code(preferred: str | None) -> str:
        code = (preferred or "").strip() or None
        if code and code not in existing_codes:
            existing_codes.add(code)
            return code
        max_n = 0
        for c in existing_codes:
            try:
                max_n = max(max_n, int(str(c).split("-")[-1]))
            except ValueError:
                pass
        while True:
            max_n += 1
            candidate = f"L-{max_n:03d}"
            if candidate not in existing_codes:
                existing_codes.add(candidate)
                return candidate

    for i, row in enumerate(rows):
        follow_ups = build_follow_ups(row["date_added"])
        fu_status_map = row.get("follow_ups") or {}
        now = datetime.now(timezone.utc)
        for fu in follow_ups:
            status = fu_status_map.get(fu.number)
            if status in ("done", "skipped", "pending"):
                fu.status = status
                fu.completed_at = now if status == "done" else None

        lead = Lead(
            lead_code=allocate_code(row.get("lead_code")),
            contact_name=row["contact_name"],
            client_email=row["client_email"],
            our_email=row.get("our_email"),
            website=row.get("website"),
            date_added=row["date_added"],
            stage=row.get("stage") or "New Lead",
            deal_value=row.get("deal_value"),
            notes=row.get("notes"),
            call_scheduled=bool(row.get("call_scheduled"))
            or row.get("stage") == "Call Scheduled",
            sort_order=sort_base + i,
            follow_ups=follow_ups,
        )
        db.add(lead)
        created.append(lead)

    db.commit()
    return [enrich_lead(get_lead(db, lead.id)) for lead in created]  # type: ignore[misc]


def update_lead(db: Session, lead: Lead, payload: LeadUpdate) -> Lead:
    data = payload.model_dump(exclude_unset=True)
    date_changed = "date_added" in data and data["date_added"] != lead.date_added
    if "lead_code" in data and data["lead_code"]:
        clash = (
            db.query(Lead)
            .filter(Lead.lead_code == data["lead_code"], Lead.id != lead.id)
            .first()
        )
        if clash:
            raise ValueError(f"Lead code {data['lead_code']} already exists")
    for key, value in data.items():
        setattr(lead, key, value)
    if "stage" in data and "call_scheduled" not in data:
        lead.call_scheduled = data["stage"] == "Call Scheduled"
    if date_changed:
        reschedule_follow_ups(lead)
    db.commit()
    return enrich_lead(get_lead(db, lead.id))  # type: ignore[arg-type]


def delete_lead(db: Session, lead: Lead) -> None:
    db.delete(lead)
    db.commit()


def update_follow_up(
    db: Session,
    lead_id: int,
    number: int,
    *,
    status: str | None = None,
    scheduled_date: date | None = None,
) -> FollowUp | None:
    fu = (
        db.query(FollowUp)
        .filter(FollowUp.lead_id == lead_id, FollowUp.number == number)
        .first()
    )
    if not fu:
        return None
    if status is not None:
        mark_follow_up(fu, status)
    if scheduled_date is not None:
        fu.scheduled_date = scheduled_date
    db.commit()
    db.refresh(fu)
    fu.computed_state = compute_fu_state(fu)  # type: ignore[attr-defined]
    return fu


def get_stats(db: Session) -> dict:
    leads = db.query(Lead).options(joinedload(Lead.follow_ups)).all()
    today = date.today()
    total = len(leads)
    due_today = overdue = in_progress = completed = call_scheduled = 0

    for lead in leads:
        seq = lead_sequence_status(lead, today)
        if seq == "due_today":
            due_today += 1
        elif seq == "overdue":
            overdue += 1
        elif seq == "in_progress":
            in_progress += 1
        elif seq == "completed":
            completed += 1
        if lead.call_scheduled or lead.stage == "Call Scheduled":
            call_scheduled += 1

    return {
        "total_leads": total,
        "due_today": due_today,
        "overdue": overdue,
        "in_progress": in_progress,
        "completed": completed,
        "call_scheduled": call_scheduled,
    }


def action_queue(db: Session) -> dict:
    """Build the daily worklist from the Follow-up Guide.

    For each lead, only the next incomplete FU (lowest number still pending)
    appears — matching the sheet guide sequence FU 1 → FU 7.
    """
    from app.config import FOLLOW_UP_GUIDE, FOLLOW_UP_SCHEDULE

    leads = db.query(Lead).options(joinedload(Lead.follow_ups)).all()
    today = date.today()
    overdue: list[dict] = []
    today_items: list[dict] = []
    upcoming: list[dict] = []

    def enrich(lead: Lead, fu: FollowUp, state: str) -> dict:
        guide = FOLLOW_UP_GUIDE.get(fu.number, {})
        return {
            "lead_id": lead.id,
            "lead_code": lead.lead_code,
            "contact_name": lead.contact_name,
            "client_email": lead.client_email,
            "website": lead.website,
            "stage": lead.stage,
            "follow_up_number": fu.number,
            "day_label": fu.day_label or guide.get("day_label") or "Day ?",
            "email_type": guide.get("email_type") or fu.day_label,
            "goal": guide.get("goal"),
            "key_cta": guide.get("key_cta"),
            "status_option": guide.get("status_option"),
            "guide_color": guide.get("color"),
            "scheduled_date": fu.scheduled_date,
            "computed_state": state,
        }

    for lead in leads:
        # Skip terminal pipeline stages from active outreach
        if lead.stage in ("Won", "Lost"):
            continue

        pending = sorted(
            [fu for fu in lead.follow_ups if fu.status == "pending"],
            key=lambda f: f.number,
        )
        if not pending:
            continue

        # Next action only — Follow-up Guide order
        fu = pending[0]
        state = compute_fu_state(fu, today)
        item = enrich(lead, fu, state)

        if state == "overdue":
            overdue.append(item)
        elif state == "due":
            today_items.append(item)
        elif state == "pending" and fu.scheduled_date > today:
            upcoming.append(item)

    overdue.sort(key=lambda x: (x["scheduled_date"], x["follow_up_number"]))
    today_items.sort(key=lambda x: (x["scheduled_date"], x["follow_up_number"]))
    upcoming.sort(key=lambda x: (x["scheduled_date"], x["follow_up_number"]))
    return {
        "overdue": overdue,
        "today": today_items,
        "upcoming": upcoming[:30],
        "overdue_count": len(overdue),
        "today_count": len(today_items),
        "guide": [
            {
                "number": g["number"],
                "day_label": g["day_label"],
                "email_type": g["email_type"],
                "goal": g["goal"],
                "key_cta": g["key_cta"],
                "status_option": g["status_option"],
                "color": g["color"],
            }
            for g in FOLLOW_UP_SCHEDULE
        ],
    }
