"""Managed per-workspace email domains: SES identity, quota, overage billing ledger.

Also seeds email_included_monthly/email_daily_cap into subscription_plans.limits for
basic (4000/130) and pro (10000/350), same JSONB-merge pattern as 097's included_ai_units.

Revision ID: 121
Revises: 120
"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

revision = "121"
down_revision = "120"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.execute("""
        UPDATE subscription_plans
        SET limits = COALESCE(limits, '{}'::jsonb) || '{"email_included_monthly": 4000, "email_daily_cap": 130}'::jsonb
        WHERE LOWER(slug) = 'basic'
    """)
    op.execute("""
        UPDATE subscription_plans
        SET limits = COALESCE(limits, '{}'::jsonb) || '{"email_included_monthly": 10000, "email_daily_cap": 350}'::jsonb
        WHERE LOWER(slug) = 'pro'
    """)

    op.create_table(
        "workspace_email_domains",
        sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True),
        sa.Column(
            "workspace_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("workspaces.id", ondelete="CASCADE"),
            nullable=False,
            unique=True,
        ),
        sa.Column("domain", sa.String(255), nullable=False, unique=True),
        sa.Column("status", sa.String(20), nullable=False, server_default="pending_dns"),
        sa.Column("dkim_tokens", postgresql.JSONB(), nullable=False, server_default=sa.text("'[]'::jsonb")),
        sa.Column("mail_from_domain", sa.String(255), nullable=True),
        sa.Column("mail_from_status", sa.String(20), nullable=True),
        sa.Column("ses_configuration_set_name", sa.String(100), nullable=False),
        sa.Column("failure_reason", sa.Text(), nullable=True),
        sa.Column("created_by", postgresql.UUID(as_uuid=True), sa.ForeignKey("users.id", ondelete="SET NULL"), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
        sa.Column("verified_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
    )

    op.create_table(
        "workspace_email_quotas",
        sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True),
        sa.Column(
            "workspace_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("workspaces.id", ondelete="CASCADE"),
            nullable=False,
            unique=True,
        ),
        sa.Column("period_start", sa.DateTime(timezone=True), nullable=False),
        sa.Column("period_end", sa.DateTime(timezone=True), nullable=False),
        sa.Column("included_monthly", sa.Integer(), nullable=False),
        sa.Column("consumed_monthly", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("daily_cap", sa.Integer(), nullable=False),
        sa.Column("consumed_today", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("day_bucket_date", sa.Date(), nullable=False),
        sa.Column("pending_overage_units", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("override_included_monthly", sa.Integer(), nullable=True),
        sa.Column("override_daily_cap", sa.Integer(), nullable=True),
        sa.Column("override_rate_per_1000_minor", sa.Integer(), nullable=True),
        sa.Column("override_approved_by", postgresql.UUID(as_uuid=True), sa.ForeignKey("users.id", ondelete="SET NULL"), nullable=True),
        sa.Column("override_approved_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("override_reason", sa.Text(), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
    )

    op.create_table(
        "workspace_email_overage_charges",
        sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True),
        sa.Column(
            "workspace_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("workspaces.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column("period_start", sa.DateTime(timezone=True), nullable=False),
        sa.Column("period_end", sa.DateTime(timezone=True), nullable=False),
        sa.Column("overage_units", sa.Integer(), nullable=False),
        sa.Column("rate_per_1000_minor", sa.Integer(), nullable=False),
        sa.Column("amount_minor", sa.Integer(), nullable=False),
        sa.Column("stripe_invoice_item_id", sa.String(100), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
    )
    op.create_index(
        "idx_email_overage_charges_workspace_period",
        "workspace_email_overage_charges",
        ["workspace_id", "period_start"],
    )


def downgrade() -> None:
    op.drop_index("idx_email_overage_charges_workspace_period", table_name="workspace_email_overage_charges")
    op.drop_table("workspace_email_overage_charges")
    op.drop_table("workspace_email_quotas")
    op.drop_table("workspace_email_domains")
    op.execute("""
        UPDATE subscription_plans
        SET limits = (limits - 'email_included_monthly') - 'email_daily_cap'
        WHERE LOWER(slug) IN ('basic', 'pro')
    """)