"""ORM models — core tables per spec §13."""

from __future__ import annotations

import uuid
from datetime import UTC, datetime

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

from gogo.db import Base

JSONVariant = JSON().with_variant(JSONB(), "postgresql")


def utcnow() -> datetime:
    return datetime.now(UTC)


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


class Tenant(Base, TimestampMixin):
    """A salon."""

    __tablename__ = "tenants"

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    name: Mapped[str] = mapped_column(String(200))
    slug: Mapped[str] = mapped_column(String(80), unique=True)  # short id used in URLs
    address: Mapped[str] = mapped_column(String(300), default="")
    city: Mapped[str] = mapped_column(String(100), default="")
    phone: Mapped[str] = mapped_column(String(40), default="")  # salon's own public number
    website: Mapped[str] = mapped_column(String(200), default="")

    locale: Mapped[str] = mapped_column(String(20), default="bs-Latn-BA")
    timezone: Mapped[str] = mapped_column(String(50), default="Europe/Sarajevo")
    currency: Mapped[str] = mapped_column(String(10), default="BAM")

    # {"mon": [["09:00","13:00"],["14:00","18:00"]], ..., "sun": []}
    working_hours: Mapped[dict] = mapped_column(JSONVariant, default=dict)

    scheduling_provider: Mapped[str] = mapped_column(String(30), default="google_calendar")

    # Plan / metering (§12)
    plan: Mapped[str] = mapped_column(String(40), default="gogo_start")
    included_minutes: Mapped[int] = mapped_column(Integer, default=300)
    hard_cutoff: Mapped[bool] = mapped_column(Boolean, default=False)
    paid_until: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    status: Mapped[str] = mapped_column(String(20), default="active")  # active | disabled

    # Agent config (§10.5)
    tts_voice: Mapped[str] = mapped_column(String(80), default="")
    price_mode: Mapped[str] = mapped_column(String(20), default="exact")  # exact|range|on_request
    agent_notes: Mapped[str] = mapped_column(Text, default="")  # plain informational notes
    llm_model: Mapped[str] = mapped_column(String(80), default="")  # empty = global default

    # Proposal engine
    proposal_ttl_hours: Mapped[int] = mapped_column(Integer, default=24)
    notify_emails: Mapped[list] = mapped_column(JSONVariant, default=list)  # owner emails
    sms_request_received: Mapped[bool] = mapped_column(Boolean, default=False)
    sms_templates: Mapped[dict] = mapped_column(JSONVariant, default=dict)  # overrides only

    # Google Calendar tenants
    min_notice_hours: Mapped[int] = mapped_column(Integer, default=2)
    max_days_ahead: Mapped[int] = mapped_column(Integer, default=14)

    # Telephony
    ring_first_enabled: Mapped[bool] = mapped_column(Boolean, default=True)
    ring_timeout_s: Mapped[int] = mapped_column(Integer, default=12)
    ring_strategy: Mapped[str] = mapped_column(String(20), default="ring_all")  # ring_all|sequential

    # Chat widget
    widget_public_key: Mapped[str] = mapped_column(
        String(64), default=lambda: uuid.uuid4().hex, unique=True
    )

    services: Mapped[list[Service]] = relationship(back_populates="tenant")


class ProviderConfig(Base, TimestampMixin):
    __tablename__ = "provider_configs"

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    tenant_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("tenants.id"), unique=True)
    provider_type: Mapped[str] = mapped_column(String(30))
    # partner_api: {base_url, api_key_encrypted, webhook_secret_encrypted,
    #               catalog_sync, email_to_owner, polling_fallback}
    # google_calendar: {} (connection lives in calendar_connections)
    config: Mapped[dict] = mapped_column(JSONVariant, default=dict)


class Service(Base, TimestampMixin):
    __tablename__ = "services"

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    tenant_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("tenants.id"), index=True)
    name: Mapped[str] = mapped_column(String(200))
    duration_min: Mapped[int] = mapped_column(Integer, default=30)
    price_min: Mapped[float | None] = mapped_column(Float, nullable=True)
    price_max: Mapped[float | None] = mapped_column(Float, nullable=True)
    home_visit: Mapped[bool] = mapped_column(Boolean, default=False)
    agent_note: Mapped[str] = mapped_column(Text, default="")
    buffer_min: Mapped[int] = mapped_column(Integer, default=0)  # per-service buffer (GCal)
    partner_service_id: Mapped[str | None] = mapped_column(String(80), nullable=True)
    active: Mapped[bool] = mapped_column(Boolean, default=True)

    tenant: Mapped[Tenant] = relationship(back_populates="services")


class CalendarConnection(Base, TimestampMixin):
    __tablename__ = "calendar_connections"

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    tenant_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("tenants.id"), unique=True)
    google_account: Mapped[str] = mapped_column(String(200))
    scopes: Mapped[list] = mapped_column(JSONVariant, default=list)
    token_data_encrypted: Mapped[str] = mapped_column(Text)  # Fernet-encrypted OAuth tokens
    status: Mapped[str] = mapped_column(String(20), default="connected")


class CalendarMapping(Base):
    """service → google calendar id. A row with service_id NULL is the tenant default."""

    __tablename__ = "calendar_mappings"
    __table_args__ = (UniqueConstraint("tenant_id", "service_id"),)

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    tenant_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("tenants.id"), index=True)
    service_id: Mapped[uuid.UUID | None] = mapped_column(
        ForeignKey("services.id"), nullable=True
    )
    google_calendar_id: Mapped[str] = mapped_column(String(300))


class Worker(Base, TimestampMixin):
    """Ring-group member — exists only as a SIP endpoint, no user account (§5.3)."""

    __tablename__ = "workers"

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    tenant_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("tenants.id"), index=True)
    name: Mapped[str] = mapped_column(String(100))
    sip_username: Mapped[str] = mapped_column(String(80), unique=True)
    sip_password: Mapped[str] = mapped_column(String(80))  # provisioned to softphone via QR
    active: Mapped[bool] = mapped_column(Boolean, default=True)


class PhoneNumber(Base, TimestampMixin):
    """Gogo SIM number assigned to a salon; maps to a GSM gateway port (§5.1)."""

    __tablename__ = "phone_numbers"

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    tenant_id: Mapped[uuid.UUID | None] = mapped_column(
        ForeignKey("tenants.id"), nullable=True, unique=True
    )
    msisdn: Mapped[str] = mapped_column(String(30), unique=True)
    gateway_port: Mapped[int | None] = mapped_column(Integer, nullable=True)
    operator: Mapped[str] = mapped_column(String(30), default="")  # mtel|bhtelecom|eronet
    sim_status: Mapped[str] = mapped_column(String(20), default="unassigned")


class Call(Base):
    __tablename__ = "calls"

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    tenant_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("tenants.id"), index=True)
    started_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow)
    caller_msisdn: Mapped[str] = mapped_column(String(30), default="")
    duration_s: Mapped[int] = mapped_column(Integer, default=0)
    outcome: Mapped[str] = mapped_column(String(30), default="abandoned")
    recording_path: Mapped[str | None] = mapped_column(String(400), nullable=True)
    transcript: Mapped[list] = mapped_column(JSONVariant, default=list)  # [{role, text, t}]
    agent_seconds_charged: Mapped[int] = mapped_column(Integer, default=0)
    trace: Mapped[dict] = mapped_column(JSONVariant, default=dict)  # latency breakdown etc.


class ChatSession(Base):
    __tablename__ = "chat_sessions"

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    tenant_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("tenants.id"), index=True)
    started_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow)
    resume_token: Mapped[str] = mapped_column(String(64), default=lambda: uuid.uuid4().hex)
    client_ip: Mapped[str] = mapped_column(String(60), default="")
    outcome: Mapped[str] = mapped_column(String(30), default="")


class ChatMessage(Base):
    __tablename__ = "chat_messages"

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    session_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("chat_sessions.id"), index=True)
    at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow)
    role: Mapped[str] = mapped_column(String(20))  # user | assistant | tool
    content: Mapped[str] = mapped_column(Text)


class BookingRequest(Base, TimestampMixin):
    __tablename__ = "booking_requests"

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    tenant_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("tenants.id"), index=True)
    source: Mapped[str] = mapped_column(String(10))  # voice | chat
    client_name: Mapped[str] = mapped_column(String(200))
    client_phone: Mapped[str] = mapped_column(String(40))
    service_id: Mapped[uuid.UUID | None] = mapped_column(ForeignKey("services.id"), nullable=True)
    service_name_raw: Mapped[str] = mapped_column(String(300), default="")
    slots: Mapped[list] = mapped_column(JSONVariant, default=list)  # ordered Slot dicts
    time_preference_text: Mapped[str] = mapped_column(String(300), default="")
    home_visit: Mapped[bool] = mapped_column(Boolean, default=False)
    address: Mapped[str | None] = mapped_column(String(300), nullable=True)
    summary: Mapped[str] = mapped_column(Text, default="")
    status: Mapped[str] = mapped_column(String(30), default="pending", index=True)
    confirmed_slot: Mapped[dict | None] = mapped_column(JSONVariant, nullable=True)
    partner_request_id: Mapped[str | None] = mapped_column(String(120), nullable=True)
    call_id: Mapped[uuid.UUID | None] = mapped_column(ForeignKey("calls.id"), nullable=True)
    chat_session_id: Mapped[uuid.UUID | None] = mapped_column(
        ForeignKey("chat_sessions.id"), nullable=True
    )
    expires_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    reminder_sent: Mapped[bool] = mapped_column(Boolean, default=False)
    resolved_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    resolution_note: Mapped[str] = mapped_column(Text, default="")


class CallRegistration(Base):
    """Handoff between the Asterisk dialplan and the voice pipeline (M4).

    The dialplan registers an inbound call over the internal API before ringing
    anyone; the AudioSocket server looks the UUID up to resolve tenant+caller.
    """

    __tablename__ = "call_registrations"

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    tenant_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("tenants.id"), index=True)
    caller_msisdn: Mapped[str] = mapped_column(String(30), default="")
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow)
    answered_by_human: Mapped[bool] = mapped_column(Boolean, default=False)
    handled_by_agent: Mapped[bool] = mapped_column(Boolean, default=False)
    finalized: Mapped[bool] = mapped_column(Boolean, default=False)


class ActionToken(Base):
    """Signed single-use action link tokens for proposal emails (§9.1)."""

    __tablename__ = "action_tokens"

    token: Mapped[str] = mapped_column(String(64), primary_key=True)
    request_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("booking_requests.id"), index=True)
    action: Mapped[str] = mapped_column(String(30))  # resolve | confirm | reject
    slot_index: Mapped[int | None] = mapped_column(Integer, nullable=True)  # for confirm
    used_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow)


class MessageForOwner(Base):
    """Non-booking messages taken by the agent (take_message tool)."""

    __tablename__ = "messages_for_owner"

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    tenant_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("tenants.id"), index=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow)
    client_name: Mapped[str] = mapped_column(String(200), default="")
    client_phone: Mapped[str] = mapped_column(String(40), default="")
    text: Mapped[str] = mapped_column(Text)
    source: Mapped[str] = mapped_column(String(10), default="voice")
    read: Mapped[bool] = mapped_column(Boolean, default=False)


class SmsLog(Base):
    __tablename__ = "sms_log"

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    tenant_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("tenants.id"), index=True)
    at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow)
    to_msisdn: Mapped[str] = mapped_column(String(30))
    body: Mapped[str] = mapped_column(Text)
    kind: Mapped[str] = mapped_column(String(30))  # missed_call|received|confirmation|rejection|expiry
    status: Mapped[str] = mapped_column(String(20), default="queued")  # queued|sent|failed
    error: Mapped[str] = mapped_column(Text, default="")


class EmailLog(Base):
    __tablename__ = "email_log"

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    tenant_id: Mapped[uuid.UUID | None] = mapped_column(ForeignKey("tenants.id"), nullable=True)
    at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow)
    to_addr: Mapped[str] = mapped_column(String(300))
    subject: Mapped[str] = mapped_column(String(400))
    kind: Mapped[str] = mapped_column(String(30))
    status: Mapped[str] = mapped_column(String(20), default="queued")
    error: Mapped[str] = mapped_column(Text, default="")


class UsageCounter(Base):
    """Per tenant per month usage (§12). month format: 'YYYY-MM'."""

    __tablename__ = "usage_counters"
    __table_args__ = (UniqueConstraint("tenant_id", "month"),)

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    tenant_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("tenants.id"), index=True)
    month: Mapped[str] = mapped_column(String(7))
    agent_seconds: Mapped[int] = mapped_column(Integer, default=0)
    calls: Mapped[int] = mapped_column(Integer, default=0)
    sms_sent: Mapped[int] = mapped_column(Integer, default=0)
    chat_sessions: Mapped[int] = mapped_column(Integer, default=0)
    warned_80: Mapped[bool] = mapped_column(Boolean, default=False)
    warned_100: Mapped[bool] = mapped_column(Boolean, default=False)


class User(Base, TimestampMixin):
    """Owner account — single account per salon (§10)."""

    __tablename__ = "users"

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    tenant_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("tenants.id"), unique=True)
    email: Mapped[str] = mapped_column(String(200), unique=True)
    password_hash: Mapped[str] = mapped_column(String(200))
    active: Mapped[bool] = mapped_column(Boolean, default=True)


class Admin(Base, TimestampMixin):
    __tablename__ = "admins"

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    email: Mapped[str] = mapped_column(String(200), unique=True)
    password_hash: Mapped[str] = mapped_column(String(200))
    active: Mapped[bool] = mapped_column(Boolean, default=True)


class PromptTemplate(Base):
    """Versioned global agent prompt template, super-admin owned (§6.3)."""

    __tablename__ = "prompt_templates"

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    version: Mapped[int] = mapped_column(Integer, unique=True)
    body: Mapped[str] = mapped_column(Text)
    notes: Mapped[str] = mapped_column(Text, default="")
    published: Mapped[bool] = mapped_column(Boolean, default=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow)


class TenantPromptOverride(Base):
    """Per-tenant prompt override — super-admin-only escape hatch (§6.3)."""

    __tablename__ = "tenant_prompt_overrides"

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    tenant_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("tenants.id"), unique=True)
    body: Mapped[str] = mapped_column(Text)
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=utcnow, onupdate=utcnow
    )