"""SQLAlchemy ORM models for LeadPilot."""

from __future__ import annotations

from datetime import date, datetime, timezone
from typing import Any

from sqlalchemy import (
    Boolean,
    Date,
    DateTime,
    Float,
    ForeignKey,
    Integer,
    JSON,
    String,
    Text,
    UniqueConstraint,
)
from sqlalchemy.orm import Mapped, mapped_column, relationship

from app.database import Base
from app.models.enums import (
    CampaignStatus,
    ConnectionStatus,
    LeadStatus,
    MessageStatus,
    ReplyStatus,
    RuleAction,
    RuleMatchType,
    TaskStatus,
)


def utcnow() -> datetime:
    """Timezone-aware UTC timestamp used as the default for every audit column."""
    return datetime.now(timezone.utc)


class TimestampMixin:
    created_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow, nullable=False)
    updated_at: Mapped[datetime] = mapped_column(
        DateTime, default=utcnow, onupdate=utcnow, nullable=False
    )


class User(Base, TimestampMixin):
    """The local operator. Used for sender-side merge fields in templates."""

    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(120), nullable=False, default="LeadPilot User")
    email: Mapped[str | None] = mapped_column(String(255))
    company: Mapped[str | None] = mapped_column(String(160))
    job_title: Mapped[str | None] = mapped_column(String(160))
    calendar_link: Mapped[str | None] = mapped_column(String(500))
    is_active: Mapped[bool] = mapped_column(Boolean, default=True)

    campaigns: Mapped[list["Campaign"]] = relationship(back_populates="owner")


class Campaign(Base, TimestampMixin):
    __tablename__ = "campaigns"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(160), nullable=False, unique=True)
    description: Mapped[str | None] = mapped_column(Text)
    status: Mapped[str] = mapped_column(String(30), default=CampaignStatus.DRAFT, nullable=False)

    target_industries: Mapped[list[str]] = mapped_column(JSON, default=list)
    target_locations: Mapped[list[str]] = mapped_column(JSON, default=list)
    target_titles: Mapped[list[str]] = mapped_column(JSON, default=list)
    keywords: Mapped[list[str]] = mapped_column(JSON, default=list)
    exclude_keywords: Mapped[list[str]] = mapped_column(JSON, default=list)

    company_size_min: Mapped[int | None] = mapped_column(Integer)
    company_size_max: Mapped[int | None] = mapped_column(Integer)
    min_score: Mapped[int] = mapped_column(Integer, default=70, nullable=False)
    outreach_objective: Mapped[str | None] = mapped_column(Text)

    default_template_id: Mapped[int | None] = mapped_column(
        ForeignKey("message_templates.id", ondelete="SET NULL")
    )
    owner_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"))

    owner: Mapped["User | None"] = relationship(back_populates="campaigns")
    leads: Mapped[list["Lead"]] = relationship(
        back_populates="campaign", cascade="all, delete-orphan"
    )
    rules: Mapped[list["Rule"]] = relationship(
        back_populates="campaign", cascade="all, delete-orphan"
    )
    rule_groups: Mapped[list["RuleGroup"]] = relationship(
        back_populates="campaign", cascade="all, delete-orphan"
    )
    tasks: Mapped[list["OutreachTask"]] = relationship(
        back_populates="campaign", cascade="all, delete-orphan"
    )
    templates: Mapped[list["MessageTemplate"]] = relationship(
        back_populates="campaign",
        cascade="all, delete-orphan",
        foreign_keys="MessageTemplate.campaign_id",
    )
    settings: Mapped["CampaignSettings | None"] = relationship(
        back_populates="campaign", cascade="all, delete-orphan", uselist=False
    )
    default_template: Mapped["MessageTemplate | None"] = relationship(
        foreign_keys=[default_template_id], post_update=True
    )


class CampaignSettings(Base, TimestampMixin):
    __tablename__ = "campaign_settings"

    id: Mapped[int] = mapped_column(primary_key=True)
    campaign_id: Mapped[int] = mapped_column(
        ForeignKey("campaigns.id", ondelete="CASCADE"), unique=True, nullable=False
    )
    daily_task_limit: Mapped[int] = mapped_column(Integer, default=25)
    auto_generate_messages: Mapped[bool] = mapped_column(Boolean, default=True)
    auto_create_tasks: Mapped[bool] = mapped_column(Boolean, default=True)
    stop_followups_on_reply: Mapped[bool] = mapped_column(Boolean, default=True)
    require_approval: Mapped[bool] = mapped_column(Boolean, default=True)
    followup_days: Mapped[list[int]] = mapped_column(JSON, default=lambda: [3, 7, 14])

    campaign: Mapped["Campaign"] = relationship(back_populates="settings")


class Lead(Base, TimestampMixin):
    __tablename__ = "leads"
    __table_args__ = (
        UniqueConstraint("campaign_id", "profile_url", name="uq_lead_campaign_profile"),
    )

    id: Mapped[int] = mapped_column(primary_key=True)
    campaign_id: Mapped[int | None] = mapped_column(ForeignKey("campaigns.id", ondelete="CASCADE"))

    first_name: Mapped[str | None] = mapped_column(String(120))
    last_name: Mapped[str | None] = mapped_column(String(120))
    full_name: Mapped[str] = mapped_column(String(240), nullable=False)
    job_title: Mapped[str | None] = mapped_column(String(240))
    company: Mapped[str | None] = mapped_column(String(240))
    industry: Mapped[str | None] = mapped_column(String(160))
    location: Mapped[str | None] = mapped_column(String(200))
    company_size: Mapped[int | None] = mapped_column(Integer)
    headline: Mapped[str | None] = mapped_column(Text)
    about: Mapped[str | None] = mapped_column(Text)
    profile_url: Mapped[str | None] = mapped_column(String(500), index=True)
    website: Mapped[str | None] = mapped_column(String(500))
    keywords: Mapped[list[str]] = mapped_column(JSON, default=list)
    tags: Mapped[list[str]] = mapped_column(JSON, default=list)

    score: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    status: Mapped[str] = mapped_column(String(30), default=LeadStatus.COLD, nullable=False)
    score_reasons: Mapped[list[dict[str, Any]]] = mapped_column(JSON, default=list)
    is_rejected: Mapped[bool] = mapped_column(Boolean, default=False)
    rejection_reason: Mapped[str | None] = mapped_column(Text)

    connection_status: Mapped[str] = mapped_column(
        String(30), default=ConnectionStatus.NOT_CONNECTED, nullable=False
    )
    message_status: Mapped[str] = mapped_column(
        String(30), default=MessageStatus.NOT_SENT, nullable=False
    )
    reply_status: Mapped[str] = mapped_column(
        String(30), default=ReplyStatus.NO_REPLY, nullable=False
    )

    source: Mapped[str] = mapped_column(String(40), default="manual")
    dedupe_key: Mapped[str | None] = mapped_column(String(500), index=True)
    last_activity_at: Mapped[datetime | None] = mapped_column(DateTime)

    campaign: Mapped["Campaign | None"] = relationship(back_populates="leads")
    scores: Mapped[list["LeadScore"]] = relationship(
        back_populates="lead", cascade="all, delete-orphan", order_by="LeadScore.id.desc()"
    )
    tasks: Mapped[list["OutreachTask"]] = relationship(
        back_populates="lead", cascade="all, delete-orphan"
    )
    activities: Mapped[list["OutreachActivity"]] = relationship(
        back_populates="lead", cascade="all, delete-orphan", order_by="OutreachActivity.id.desc()"
    )
    replies: Mapped[list["Reply"]] = relationship(
        back_populates="lead", cascade="all, delete-orphan"
    )


class LeadScore(Base):
    """Immutable snapshot of one scoring run, kept for audit and explainability."""

    __tablename__ = "lead_scores"

    id: Mapped[int] = mapped_column(primary_key=True)
    lead_id: Mapped[int] = mapped_column(ForeignKey("leads.id", ondelete="CASCADE"), nullable=False)
    campaign_id: Mapped[int | None] = mapped_column(ForeignKey("campaigns.id", ondelete="SET NULL"))
    score: Mapped[int] = mapped_column(Integer, nullable=False)
    raw_score: Mapped[int] = mapped_column(Integer, default=0)
    status: Mapped[str] = mapped_column(String(30), nullable=False)
    rejected: Mapped[bool] = mapped_column(Boolean, default=False)
    reasons: Mapped[list[dict[str, Any]]] = mapped_column(JSON, default=list)
    rule_results: Mapped[list[dict[str, Any]]] = mapped_column(JSON, default=list)
    evaluated_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow, nullable=False)

    lead: Mapped["Lead"] = relationship(back_populates="scores")


class RuleGroup(Base, TimestampMixin):
    __tablename__ = "rule_groups"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(160), nullable=False)
    description: Mapped[str | None] = mapped_column(Text)
    campaign_id: Mapped[int | None] = mapped_column(ForeignKey("campaigns.id", ondelete="CASCADE"))
    match_type: Mapped[str] = mapped_column(String(10), default=RuleMatchType.ALL, nullable=False)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True)
    priority: Mapped[int] = mapped_column(Integer, default=100)

    campaign: Mapped["Campaign | None"] = relationship(back_populates="rule_groups")
    rules: Mapped[list["Rule"]] = relationship(back_populates="group")


class Rule(Base, TimestampMixin):
    __tablename__ = "rules"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(200), nullable=False)
    description: Mapped[str | None] = mapped_column(Text)
    campaign_id: Mapped[int | None] = mapped_column(ForeignKey("campaigns.id", ondelete="CASCADE"))
    group_id: Mapped[int | None] = mapped_column(ForeignKey("rule_groups.id", ondelete="SET NULL"))

    field: Mapped[str] = mapped_column(String(60), nullable=False)
    operator: Mapped[str] = mapped_column(String(30), nullable=False)
    value: Mapped[str | None] = mapped_column(String(500))
    value2: Mapped[str | None] = mapped_column(String(500))
    case_sensitive: Mapped[bool] = mapped_column(Boolean, default=False)

    action: Mapped[str] = mapped_column(String(30), default=RuleAction.SCORE, nullable=False)
    points: Mapped[int] = mapped_column(Integer, default=0)
    action_value: Mapped[str | None] = mapped_column(Text)
    priority: Mapped[int] = mapped_column(Integer, default=100)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True)

    campaign: Mapped["Campaign | None"] = relationship(back_populates="rules")
    group: Mapped["RuleGroup | None"] = relationship(back_populates="rules")


class MessageTemplate(Base, TimestampMixin):
    __tablename__ = "message_templates"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(160), nullable=False)
    description: Mapped[str | None] = mapped_column(Text)
    category: Mapped[str] = mapped_column(String(40), default="connection")
    body: Mapped[str] = mapped_column(Text, nullable=False)
    campaign_id: Mapped[int | None] = mapped_column(ForeignKey("campaigns.id", ondelete="CASCADE"))
    is_default: Mapped[bool] = mapped_column(Boolean, default=False)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True)
    priority: Mapped[int] = mapped_column(Integer, default=100)
    # Deterministic selection conditions, evaluated by the rule engine.
    conditions: Mapped[list[dict[str, Any]]] = mapped_column(JSON, default=list)
    match_type: Mapped[str] = mapped_column(String(10), default=RuleMatchType.ALL)

    campaign: Mapped["Campaign | None"] = relationship(
        back_populates="templates", foreign_keys=[campaign_id]
    )
    variants: Mapped[list["MessageVariant"]] = relationship(
        back_populates="template", cascade="all, delete-orphan"
    )


class MessageVariant(Base, TimestampMixin):
    __tablename__ = "message_variants"

    id: Mapped[int] = mapped_column(primary_key=True)
    template_id: Mapped[int] = mapped_column(
        ForeignKey("message_templates.id", ondelete="CASCADE"), nullable=False
    )
    name: Mapped[str] = mapped_column(String(120), nullable=False)
    body: Mapped[str] = mapped_column(Text, nullable=False)
    weight: Mapped[int] = mapped_column(Integer, default=1)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True)

    template: Mapped["MessageTemplate"] = relationship(back_populates="variants")


class OutreachTask(Base, TimestampMixin):
    __tablename__ = "outreach_tasks"

    id: Mapped[int] = mapped_column(primary_key=True)
    lead_id: Mapped[int] = mapped_column(ForeignKey("leads.id", ondelete="CASCADE"), nullable=False)
    campaign_id: Mapped[int | None] = mapped_column(ForeignKey("campaigns.id", ondelete="CASCADE"))
    template_id: Mapped[int | None] = mapped_column(
        ForeignKey("message_templates.id", ondelete="SET NULL")
    )

    task_type: Mapped[str] = mapped_column(String(40), nullable=False)
    status: Mapped[str] = mapped_column(String(30), default=TaskStatus.PENDING, nullable=False)
    sequence_index: Mapped[int] = mapped_column(Integer, default=0)

    suggested_message: Mapped[str | None] = mapped_column(Text)
    edited_message: Mapped[str | None] = mapped_column(Text)
    reason: Mapped[str | None] = mapped_column(Text)

    scheduled_date: Mapped[date] = mapped_column(Date, nullable=False)
    approved_at: Mapped[datetime | None] = mapped_column(DateTime)
    completed_at: Mapped[datetime | None] = mapped_column(DateTime)
    notes: Mapped[str | None] = mapped_column(Text)

    lead: Mapped["Lead"] = relationship(back_populates="tasks")
    campaign: Mapped["Campaign | None"] = relationship(back_populates="tasks")
    activities: Mapped[list["OutreachActivity"]] = relationship(
        back_populates="task", cascade="all, delete-orphan"
    )

    @property
    def final_message(self) -> str | None:
        """The message the user will actually send: an edit wins over the generated text."""
        return self.edited_message or self.suggested_message


class OutreachActivity(Base):
    __tablename__ = "outreach_activities"

    id: Mapped[int] = mapped_column(primary_key=True)
    lead_id: Mapped[int | None] = mapped_column(ForeignKey("leads.id", ondelete="CASCADE"))
    task_id: Mapped[int | None] = mapped_column(ForeignKey("outreach_tasks.id", ondelete="CASCADE"))
    campaign_id: Mapped[int | None] = mapped_column(ForeignKey("campaigns.id", ondelete="SET NULL"))
    activity_type: Mapped[str] = mapped_column(String(50), nullable=False)
    description: Mapped[str | None] = mapped_column(Text)
    payload: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow, nullable=False)

    lead: Mapped["Lead | None"] = relationship(back_populates="activities")
    task: Mapped["OutreachTask | None"] = relationship(back_populates="activities")


class Reply(Base):
    __tablename__ = "replies"

    id: Mapped[int] = mapped_column(primary_key=True)
    lead_id: Mapped[int] = mapped_column(ForeignKey("leads.id", ondelete="CASCADE"), nullable=False)
    task_id: Mapped[int | None] = mapped_column(ForeignKey("outreach_tasks.id", ondelete="SET NULL"))
    campaign_id: Mapped[int | None] = mapped_column(ForeignKey("campaigns.id", ondelete="SET NULL"))
    reply_type: Mapped[str] = mapped_column(String(30), default=ReplyStatus.REPLIED, nullable=False)
    content: Mapped[str | None] = mapped_column(Text)
    sentiment_score: Mapped[float | None] = mapped_column(Float)
    received_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow, nullable=False)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow, nullable=False)

    lead: Mapped["Lead"] = relationship(back_populates="replies")


class ImportHistory(Base):
    __tablename__ = "import_history"

    id: Mapped[int] = mapped_column(primary_key=True)
    filename: Mapped[str] = mapped_column(String(300), nullable=False)
    campaign_id: Mapped[int | None] = mapped_column(ForeignKey("campaigns.id", ondelete="SET NULL"))
    total_rows: Mapped[int] = mapped_column(Integer, default=0)
    imported: Mapped[int] = mapped_column(Integer, default=0)
    updated: Mapped[int] = mapped_column(Integer, default=0)
    duplicates: Mapped[int] = mapped_column(Integer, default=0)
    failed: Mapped[int] = mapped_column(Integer, default=0)
    errors: Mapped[list[dict[str, Any]]] = mapped_column(JSON, default=list)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow, nullable=False)


class AnalyticsEvent(Base):
    __tablename__ = "analytics_events"

    id: Mapped[int] = mapped_column(primary_key=True)
    event_type: Mapped[str] = mapped_column(String(60), nullable=False, index=True)
    entity_type: Mapped[str | None] = mapped_column(String(40))
    entity_id: Mapped[int | None] = mapped_column(Integer)
    campaign_id: Mapped[int | None] = mapped_column(ForeignKey("campaigns.id", ondelete="CASCADE"))
    payload: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict)
    created_at: Mapped[datetime] = mapped_column(
        DateTime, default=utcnow, nullable=False, index=True
    )