from __future__ import annotations

import uuid
from collections.abc import AsyncIterator
from datetime import datetime
from decimal import Decimal
from enum import StrEnum

import asyncpg
from sqlalchemy import (
    Boolean,
    DateTime,
    Enum,
    ForeignKey,
    Index,
    Integer,
    Numeric,
    SmallInteger,
    String,
    Text,
    UniqueConstraint,
    func,
    text,
)
from sqlalchemy.dialects.postgresql import JSONB, UUID
from sqlalchemy.ext.asyncio import (
    AsyncEngine,
    AsyncSession,
    async_sessionmaker,
    create_async_engine,
)
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

SCHEMA = "sms"


class Channel(StrEnum):
    SMS = "SMS"


class SendStatus(StrEnum):
    PENDING = "pending"
    ACCEPTED = "accepted"
    REJECTED = "rejected"
    FAILED = "failed"
    UNCERTAIN = "uncertain"
    SKIPPED = "skipped"


class DeliveryStatus(StrEnum):
    UNKNOWN = "unknown"
    SENT = "sent"
    DELIVERED = "delivered"
    UNDELIVERED = "undelivered"
    UNSENT = "unsent"


class Base(DeclarativeBase):
    pass


class SmsTemplate(Base):
    __tablename__ = "sms_template"
    __table_args__ = (
        UniqueConstraint("code", "channel", "locale", "version", name="uq_template_version"),
        Index(
            "uq_template_active",
            "code",
            "channel",
            "locale",
            unique=True,
            postgresql_where=text("is_active"),
        ),
        {"schema": SCHEMA},
    )

    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True)
    code: Mapped[str] = mapped_column(String(64), nullable=False)
    channel: Mapped[Channel] = mapped_column(
        Enum(
            Channel,
            name="sms_channel",
            schema=SCHEMA,
            values_callable=lambda x: [e.value for e in x],
        )
    )
    locale: Mapped[str] = mapped_column(String(16), nullable=False)
    version: Mapped[int] = mapped_column(Integer, nullable=False)
    body_template: Mapped[str] = mapped_column(Text, nullable=False)
    placeholders: Mapped[list[str]] = mapped_column(JSONB, nullable=False)
    sender_name: Mapped[str | None] = mapped_column(String(64))
    max_parts: Mapped[int] = mapped_column(SmallInteger, nullable=False)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
    approved_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now()
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now()
    )
    created_by: Mapped[str] = mapped_column(String(64), nullable=False)


class SmsSetting(Base):
    __tablename__ = "sms_setting"
    __table_args__ = {"schema": SCHEMA}

    setting_key: Mapped[str] = mapped_column(String(128), primary_key=True)
    setting_value: Mapped[object] = mapped_column(JSONB, nullable=False)
    value_type: Mapped[str] = mapped_column(String(16), nullable=False)
    description: Mapped[str] = mapped_column(Text, nullable=False)
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now()
    )


class SmsOutboundMessage(Base):
    __tablename__ = "sms_outbound_message"
    __table_args__ = (
        UniqueConstraint("requester_service", "idempotency_key", name="uq_outbound_idempotency"),
        Index(
            "uq_outbound_provider_message",
            "provider",
            "provider_message_id",
            unique=True,
            postgresql_where=text("provider_message_id IS NOT NULL"),
        ),
        Index("ix_outbound_phone_created", "phone_e164", text("created_at DESC")),
        Index(
            "ix_outbound_requester_process_created",
            "requester_service",
            "process",
            text("created_at DESC"),
        ),
        Index("ix_outbound_customer_ref", "customer_ref"),
        Index("ix_outbound_send_created", "send_status", "created_at"),
        Index("ix_outbound_delivery_updated", "delivery_status", "updated_at"),
        {"schema": SCHEMA},
    )

    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now()
    )
    requested_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    accepted_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    sent_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    delivered_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now()
    )
    requester_service: Mapped[str] = mapped_column(String(64), nullable=False)
    process: Mapped[str] = mapped_column(String(64), nullable=False)
    channel: Mapped[str] = mapped_column(String(16), nullable=False)
    provider: Mapped[str] = mapped_column(String(32), nullable=False)
    phone_e164: Mapped[str] = mapped_column(String(16), nullable=False)
    phone_digits: Mapped[str] = mapped_column(String(15), nullable=False)
    phone_masked: Mapped[str] = mapped_column(String(32), nullable=False)
    template_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True), ForeignKey(f"{SCHEMA}.sms_template.id"), nullable=False
    )
    template_code: Mapped[str] = mapped_column(String(64), nullable=False)
    body_rendered: Mapped[str] = mapped_column(Text, nullable=False)
    substitutions: Mapped[dict[str, object]] = mapped_column(JSONB, nullable=False)
    send_status: Mapped[SendStatus] = mapped_column(
        Enum(
            SendStatus,
            name="sms_send_status",
            schema=SCHEMA,
            values_callable=lambda x: [e.value for e in x],
        ),
        nullable=False,
    )
    delivery_status: Mapped[DeliveryStatus] = mapped_column(
        Enum(
            DeliveryStatus,
            name="sms_delivery_status",
            schema=SCHEMA,
            values_callable=lambda x: [e.value for e in x],
        ),
        nullable=False,
    )
    provider_message_id: Mapped[str | None] = mapped_column(String(128))
    provider_external_id: Mapped[str | None] = mapped_column(String(128))
    customer_ref: Mapped[str | None] = mapped_column(String(128))
    idempotency_key: Mapped[str] = mapped_column(String(192), nullable=False)
    request_fingerprint: Mapped[str] = mapped_column(String(64), nullable=False)
    request_id: Mapped[str | None] = mapped_column(String(128))
    traceparent: Mapped[str | None] = mapped_column(String(55))
    provider_http_status: Mapped[int | None] = mapped_column(Integer)
    provider_error_code: Mapped[str | None] = mapped_column(String(64))
    provider_error_message: Mapped[str | None] = mapped_column(String(256))
    sender_name: Mapped[str] = mapped_column(String(64), nullable=False)
    message_ttl_sec: Mapped[int | None] = mapped_column(Integer)
    attempt_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    last_attempt_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    next_attempt_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    worker_locked_until: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    parts: Mapped[int | None] = mapped_column(Integer)
    price: Mapped[Decimal | None] = mapped_column(Numeric(14, 4))
    currency: Mapped[str | None] = mapped_column(String(3))
    callback_last_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))


class SmsCallbackEvent(Base):
    __tablename__ = "sms_callback_event"
    __table_args__ = (
        UniqueConstraint(
            "message_uuid",
            "callback_event",
            "status",
            "status_time",
            name="uq_callback_event",
        ),
        {"schema": SCHEMA},
    )

    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True)
    message_uuid: Mapped[str] = mapped_column(String(128), nullable=False)
    callback_event: Mapped[str] = mapped_column(String(32), nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False)
    status_time: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    received_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now()
    )


def asyncpg_dsn(url: str) -> str:
    return url.replace("postgresql+asyncpg://", "postgresql://", 1)


def create_postgres_engine(url: str) -> AsyncEngine:
    dsn = asyncpg_dsn(url)

    async def connect() -> asyncpg.Connection:
        return await asyncpg.connect(dsn=dsn)

    return create_async_engine(
        "postgresql+asyncpg://", async_creator=connect, pool_pre_ping=True
    )


class Database:
    def __init__(self, url: str) -> None:
        self.engine = create_postgres_engine(url)
        self.sessions = async_sessionmaker(self.engine, expire_on_commit=False)

    async def session(self) -> AsyncIterator[AsyncSession]:
        async with self.sessions() as session:
            yield session

    async def close(self) -> None:
        await self.engine.dispose()