import time
import uuid
from datetime import datetime, timezone

from sqlalchemy import JSON, Boolean, DateTime, Integer, String, Text, create_engine
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, sessionmaker

from .config import settings


def utcnow() -> datetime:
    return datetime.now(timezone.utc)


engine = create_engine(settings.database_url, pool_pre_ping=True, future=True)
SessionLocal = sessionmaker(bind=engine, expire_on_commit=False, future=True)


class Base(DeclarativeBase):
    pass


class Contact(Base):
    __tablename__ = "contacts"
    phone: Mapped[str] = mapped_column(String(20), primary_key=True)
    name: Mapped[str | None] = mapped_column(String(200))
    opted_in: Mapped[bool] = mapped_column(Boolean, default=False)
    opted_out: Mapped[bool] = mapped_column(Boolean, default=False)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow, onupdate=utcnow)


class Message(Base):
    __tablename__ = "messages"
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    client: Mapped[str] = mapped_column(String(50), default="default")
    idempotency_key: Mapped[str | None] = mapped_column(String(200), unique=True)
    event: Mapped[str] = mapped_column(String(80), index=True)
    to_phone: Mapped[str] = mapped_column(String(20), index=True)
    email: Mapped[str | None] = mapped_column(String(200))
    locale: Mapped[str] = mapped_column(String(10), default="ar")
    vars: Mapped[dict] = mapped_column(JSON, default=dict)
    body: Mapped[str] = mapped_column(Text)
    # queued | sending | sent | delivered | read | failed | skipped
    status: Mapped[str] = mapped_column(String(20), default="queued", index=True)
    channel: Mapped[str | None] = mapped_column(String(20))  # whatsapp | email
    attempts: Mapped[int] = mapped_column(Integer, default=0)
    next_attempt_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow, index=True)
    provider_msg_id: Mapped[str | None] = mapped_column(String(120), index=True)
    error: Mapped[str | None] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow, index=True)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow, onupdate=utcnow)
    sent_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))

    def as_dict(self) -> dict:
        return {
            "id": self.id, "event": self.event, "to": self.to_phone, "status": self.status,
            "channel": self.channel, "attempts": self.attempts, "error": self.error,
            "created_at": self.created_at.isoformat() if self.created_at else None,
            "sent_at": self.sent_at.isoformat() if self.sent_at else None,
        }


def init_db(retries: int = 30) -> None:
    for i in range(retries):
        try:
            Base.metadata.create_all(engine)
            return
        except Exception as exc:  # database still starting
            print(f"[db] not ready ({exc.__class__.__name__}), retry {i + 1}/{retries}", flush=True)
            time.sleep(2)
    raise RuntimeError("database not reachable")