from datetime import date, datetime

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

from app.database import Base


class Lead(Base):
    __tablename__ = "leads"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, index=True)
    lead_code: Mapped[str] = mapped_column(String(32), unique=True, index=True)
    contact_name: Mapped[str] = mapped_column(String(255))
    client_email: Mapped[str] = mapped_column(String(255), index=True)
    our_email: Mapped[str | None] = mapped_column(String(255), nullable=True)
    website: Mapped[str | None] = mapped_column(String(512), nullable=True)
    date_added: Mapped[date] = mapped_column(Date)
    stage: Mapped[str] = mapped_column(String(64), default="New Lead", index=True)
    deal_value: Mapped[float | None] = mapped_column(Float, nullable=True)
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)
    call_scheduled: Mapped[bool] = mapped_column(Boolean, default=False)
    sort_order: Mapped[int] = mapped_column(Integer, default=0)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )

    follow_ups: Mapped[list["FollowUp"]] = relationship(
        "FollowUp",
        back_populates="lead",
        cascade="all, delete-orphan",
        order_by="FollowUp.number",
    )


class FollowUp(Base):
    __tablename__ = "follow_ups"
    __table_args__ = (UniqueConstraint("lead_id", "number", name="uq_lead_fu_number"),)

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    lead_id: Mapped[int] = mapped_column(ForeignKey("leads.id", ondelete="CASCADE"), index=True)
    number: Mapped[int] = mapped_column(Integer)
    day_label: Mapped[str] = mapped_column(String(32))
    scheduled_date: Mapped[date] = mapped_column(Date, index=True)
    status: Mapped[str] = mapped_column(String(32), default="pending")
    completed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)

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


class EmailLog(Base):
    __tablename__ = "email_logs"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    lead_id: Mapped[int] = mapped_column(ForeignKey("leads.id", ondelete="CASCADE"), index=True)
    follow_up_number: Mapped[int] = mapped_column(Integer)
    to_address: Mapped[str] = mapped_column(String(255))
    from_address: Mapped[str | None] = mapped_column(String(255), nullable=True)
    subject: Mapped[str] = mapped_column(String(512))
    status: Mapped[str] = mapped_column(String(32), default="sent")  # sent | failed
    error: Mapped[str | None] = mapped_column(Text, nullable=True)
    transcript_snippet: Mapped[str | None] = mapped_column(Text, nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())

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


class GmailAccount(Base):
    """OAuth-connected Gmail mailbox used as a send-as / from address."""

    __tablename__ = "gmail_accounts"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    email: Mapped[str] = mapped_column(String(255), unique=True, index=True)
    display_name: Mapped[str | None] = mapped_column(String(255), nullable=True)
    # Full google.oauth2.credentials.Credentials JSON (incl. refresh_token)
    token_json: Mapped[str] = mapped_column(Text)
    scopes: Mapped[str | None] = mapped_column(Text, nullable=True)
    is_default: Mapped[bool] = mapped_column(Boolean, default=False)
    connected_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )


class InboundEmailRules(Base):
    """Singleton-ish config for inbound mail → lead automation."""

    __tablename__ = "inbound_email_rules"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    enabled: Mapped[bool] = mapped_column(Boolean, default=False)
    filter_keywords: Mapped[str] = mapped_column(Text, default="[]")  # JSON list
    positive_keywords: Mapped[str] = mapped_column(Text, default="[]")
    negative_keywords: Mapped[str] = mapped_column(Text, default="[]")
    poll_interval_seconds: Mapped[int] = mapped_column(Integer, default=120)
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )


class InboundEmailEvent(Base):
    """Audit + idempotency for processed inbound Gmail messages."""

    __tablename__ = "inbound_email_events"
    __table_args__ = (
        UniqueConstraint(
            "gmail_account_email",
            "gmail_message_id",
            name="uq_inbound_account_message",
        ),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    gmail_account_email: Mapped[str] = mapped_column(String(255), index=True)
    gmail_message_id: Mapped[str] = mapped_column(String(128), index=True)
    thread_id: Mapped[str | None] = mapped_column(String(128), nullable=True)
    from_email: Mapped[str | None] = mapped_column(String(255), nullable=True, index=True)
    from_name: Mapped[str | None] = mapped_column(String(255), nullable=True)
    subject: Mapped[str | None] = mapped_column(String(512), nullable=True)
    filter_matched: Mapped[bool] = mapped_column(Boolean, default=False)
    # positive | negative | skipped | duplicate | error | ignored
    classification: Mapped[str] = mapped_column(String(32), default="skipped", index=True)
    matched_filter_terms: Mapped[str | None] = mapped_column(Text, nullable=True)
    matched_sentiment_terms: Mapped[str | None] = mapped_column(Text, nullable=True)
    lead_id: Mapped[int | None] = mapped_column(
        ForeignKey("leads.id", ondelete="SET NULL"), nullable=True, index=True
    )
    raw_snippet: Mapped[str | None] = mapped_column(Text, nullable=True)
    error: Mapped[str | None] = mapped_column(Text, nullable=True)
    processed_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )

    lead: Mapped["Lead | None"] = relationship("Lead")
