"""Email domains domain — SQLAlchemy models.

A workspace's own verified sending domain, hosted on P2HS's own AWS SES account/region
(``settings.cloud_ses_*``) with a per-workspace identity + Configuration Set — distinct
from both the BYOK ``ses`` connector (customer's own AWS keys,
``src/domain/connectors/catalog/ses.py``) and the shared platform default sender
(``resolve_email_delivery``'s ``internal`` scope). See ``src/domain/cloud/email_delivery.py``
for how a verified domain here becomes the ``platform_domain`` delivery scope.
"""
from __future__ import annotations

from datetime import date, datetime
from typing import Optional
from uuid import UUID, uuid4

import sqlalchemy as sa
from sqlalchemy import Boolean, Date, ForeignKey, Integer, String, Text
from sqlalchemy.dialects.postgresql import JSONB, UUID as PGUUID
from sqlalchemy.orm import Mapped, mapped_column
from sqlalchemy.sql import func
from sqlalchemy.types import DateTime

from src.core.database import Base


class WorkspaceEmailDomain(Base):
    """One verified sending domain per workspace (v1 — single domain, no is_primary yet)."""

    __tablename__ = "workspace_email_domains"

    id: Mapped[UUID] = mapped_column(PGUUID(as_uuid=True), primary_key=True, default=uuid4)
    workspace_id: Mapped[UUID] = mapped_column(
        PGUUID(as_uuid=True), ForeignKey("workspaces.id", ondelete="CASCADE"), nullable=False, unique=True
    )
    domain: Mapped[str] = mapped_column(String(255), nullable=False, unique=True)
    # pending_dns | verifying | verified | failed | suspended
    status: Mapped[str] = mapped_column(String(20), nullable=False, server_default="pending_dns")
    dkim_tokens: Mapped[list] = mapped_column(JSONB, nullable=False, server_default=sa.text("'[]'::jsonb"))
    mail_from_domain: Mapped[Optional[str]] = mapped_column(String(255), nullable=True)
    mail_from_status: Mapped[Optional[str]] = mapped_column(String(20), nullable=True)
    ses_configuration_set_name: Mapped[str] = mapped_column(String(100), nullable=False)
    failure_reason: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    created_by: Mapped[Optional[UUID]] = mapped_column(
        PGUUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL"), nullable=True
    )
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    verified_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )

    @property
    def verification_status(self) -> str:
        return "verified" if self.verified_at is not None else self.status


class WorkspaceEmailQuota(Base):
    """Rolling monthly + daily send counters for a workspace's managed email domain.

    Lazily created/rolled forward by ``EmailQuotaService.check_and_reserve`` — never created
    by a migration seed, since the correct ``included_monthly``/``daily_cap`` depend on the
    workspace's plan at the moment it's first needed.
    """

    __tablename__ = "workspace_email_quotas"

    id: Mapped[UUID] = mapped_column(PGUUID(as_uuid=True), primary_key=True, default=uuid4)
    workspace_id: Mapped[UUID] = mapped_column(
        PGUUID(as_uuid=True), ForeignKey("workspaces.id", ondelete="CASCADE"), nullable=False, unique=True
    )
    period_start: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    period_end: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    included_monthly: Mapped[int] = mapped_column(Integer, nullable=False)
    consumed_monthly: Mapped[int] = mapped_column(Integer, nullable=False, server_default="0")
    daily_cap: Mapped[int] = mapped_column(Integer, nullable=False)
    consumed_today: Mapped[int] = mapped_column(Integer, nullable=False, server_default="0")
    day_bucket_date: Mapped[date] = mapped_column(Date, nullable=False)
    # Units sent past included_monthly this period that have not yet been pushed to Stripe
    # as an invoice item (see WorkspaceEmailOverageCharge / bill_email_overage worker task).
    pending_overage_units: Mapped[int] = mapped_column(Integer, nullable=False, server_default="0")
    # Admin-approved manual increase (src/domain/email_domains/router.py admin endpoint).
    # Takes precedence over the plan's own limits.email_included_monthly/email_daily_cap.
    override_included_monthly: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    override_daily_cap: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    override_rate_per_1000_minor: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    override_approved_by: Mapped[Optional[UUID]] = mapped_column(
        PGUUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL"), nullable=True
    )
    override_approved_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    override_reason: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    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()
    )


class WorkspaceEmailOverageCharge(Base):
    """Audit/idempotency ledger — one row per Stripe Invoice Item actually created.

    ``bill_email_overage`` only ever creates a new charge for units not already covered by
    a prior row for the same workspace+period, so a task retry can never double-bill.
    """

    __tablename__ = "workspace_email_overage_charges"

    id: Mapped[UUID] = mapped_column(PGUUID(as_uuid=True), primary_key=True, default=uuid4)
    workspace_id: Mapped[UUID] = mapped_column(
        PGUUID(as_uuid=True), ForeignKey("workspaces.id", ondelete="CASCADE"), nullable=False
    )
    period_start: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    period_end: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    overage_units: Mapped[int] = mapped_column(Integer, nullable=False)
    rate_per_1000_minor: Mapped[int] = mapped_column(Integer, nullable=False)
    amount_minor: Mapped[int] = mapped_column(Integer, nullable=False)
    stripe_invoice_item_id: Mapped[Optional[str]] = mapped_column(String(100), nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())