"""workspace domain inventory and hostname assignments.

Revision ID: 202
Revises: 201
"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql
from uuid import uuid4

revision = "202"
down_revision = "201"
branch_labels = None
depends_on = None

def upgrade():
    uuid = postgresql.UUID(as_uuid=True)
    op.create_table("workspace_domains",
        sa.Column("id", uuid, primary_key=True), sa.Column("workspace_id", uuid, sa.ForeignKey("workspaces.id", ondelete="CASCADE"), nullable=False),
        sa.Column("domain_name", sa.String(255), nullable=False), sa.Column("normalized_domain", sa.String(255), nullable=False),
        sa.Column("domain_type", sa.String(30), server_default="external", nullable=False), sa.Column("registrar_provider", sa.String(40)), sa.Column("registrar_external_id", sa.String(255)),
        sa.Column("dns_provider", sa.String(40)), sa.Column("dns_zone_external_id", sa.String(255)), sa.Column("registration_status", sa.String(30), server_default="unmanaged", nullable=False),
        sa.Column("transfer_status", sa.String(30), server_default="none", nullable=False), sa.Column("verification_status", sa.String(30), server_default="pending", nullable=False),
        sa.Column("expires_at", sa.DateTime(timezone=True)), sa.Column("auto_renew", sa.Boolean), sa.Column("registrar_lock_status", sa.Boolean), sa.Column("nameserver_status", sa.String(30), server_default="unknown", nullable=False),
        sa.Column("metadata_json", postgresql.JSONB, server_default=sa.text("'{}'::jsonb"), nullable=False), sa.Column("created_by", uuid, sa.ForeignKey("users.id", ondelete="SET NULL")), sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now()), sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now()))
    op.create_index("uq_workspace_domains_normalized", "workspace_domains", ["normalized_domain"], unique=True)
    op.create_table("domain_assignments",
        sa.Column("id", uuid, primary_key=True), sa.Column("domain_id", uuid, sa.ForeignKey("workspace_domains.id", ondelete="CASCADE"), nullable=False), sa.Column("workspace_id", uuid, sa.ForeignKey("workspaces.id", ondelete="CASCADE"), nullable=False), sa.Column("project_id", uuid, sa.ForeignKey("projects.id", ondelete="CASCADE"), nullable=False), sa.Column("hostname", sa.String(255), nullable=False), sa.Column("environment", sa.String(20), server_default="production", nullable=False), sa.Column("assignment_status", sa.String(30), server_default="pending", nullable=False), sa.Column("dns_status", sa.String(30), server_default="pending", nullable=False), sa.Column("tls_status", sa.String(30), server_default="pending", nullable=False), sa.Column("metadata_json", postgresql.JSONB, server_default=sa.text("'{}'::jsonb"), nullable=False), sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now()), sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now()))
    op.create_index("uq_domain_assignments_hostname_active", "domain_assignments", ["hostname"], unique=True, postgresql_where=sa.text("assignment_status IN ('pending','active')"))
    op.create_table("domain_transfers", sa.Column("id", uuid, primary_key=True), sa.Column("domain_id", uuid, sa.ForeignKey("workspace_domains.id", ondelete="CASCADE"), nullable=False), sa.Column("workspace_id", uuid, sa.ForeignKey("workspaces.id", ondelete="CASCADE"), nullable=False), sa.Column("provider", sa.String(40), nullable=False), sa.Column("provider_transfer_id", sa.String(255)), sa.Column("status", sa.String(30), server_default="awaiting_customer_action", nullable=False), sa.Column("failure_reason", sa.Text), sa.Column("last_checked_at", sa.DateTime(timezone=True)), sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now()), sa.Column("completed_at", sa.DateTime(timezone=True)))
    op.create_table("domain_orders", sa.Column("id", uuid, primary_key=True), sa.Column("workspace_id", uuid, sa.ForeignKey("workspaces.id", ondelete="CASCADE"), nullable=False), sa.Column("domain_id", uuid, sa.ForeignKey("workspace_domains.id", ondelete="SET NULL")), sa.Column("order_type", sa.String(20), nullable=False), sa.Column("provider", sa.String(40), nullable=False), sa.Column("quoted_amount_minor", sa.Integer), sa.Column("charged_amount_minor", sa.Integer), sa.Column("provider_cost_minor", sa.Integer), sa.Column("currency", sa.String(3), server_default="USD", nullable=False), sa.Column("payment_status", sa.String(30), server_default="pending", nullable=False), sa.Column("fulfillment_status", sa.String(30), server_default="pending", nullable=False), sa.Column("idempotency_key", sa.String(255), nullable=False), sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now()))
    op.create_index("uq_domain_orders_idempotency", "domain_orders", ["workspace_id", "idempotency_key"], unique=True)
    op.create_table("domain_events", sa.Column("id", uuid, primary_key=True), sa.Column("workspace_id", uuid, sa.ForeignKey("workspaces.id", ondelete="CASCADE"), nullable=False), sa.Column("domain_id", uuid, sa.ForeignKey("workspace_domains.id", ondelete="SET NULL")), sa.Column("event_type", sa.String(60), nullable=False), sa.Column("actor_id", uuid, sa.ForeignKey("users.id", ondelete="SET NULL")), sa.Column("payload_json", postgresql.JSONB, server_default=sa.text("'{}'::jsonb"), nullable=False), sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now()))
    conn = op.get_bind()
    legacy_domains = conn.execute(sa.text("""
        SELECT p.workspace_id, lower(pd.domain) AS domain_name,
               bool_or(pd.verified_at IS NOT NULL) AS is_verified,
               min(pd.created_at) AS created_at
        FROM project_domains pd
        JOIN projects p ON p.id = pd.project_id
        WHERE p.deleted_at IS NULL
        GROUP BY p.workspace_id, lower(pd.domain)
    """)).mappings().all()
    for row in legacy_domains:
        conn.execute(sa.text("""
            INSERT INTO workspace_domains
                (id, workspace_id, domain_name, normalized_domain, domain_type,
                 registration_status, verification_status, created_at, updated_at)
            VALUES (:id, :workspace_id, :domain_name, :normalized_domain, 'external',
                    'unmanaged', :verification_status, :created_at, now())
            ON CONFLICT (normalized_domain) DO NOTHING
        """), {
            "id": uuid4(),
            "workspace_id": row["workspace_id"],
            "domain_name": row["domain_name"],
            "normalized_domain": row["domain_name"],
            "verification_status": "verified" if row["is_verified"] else "pending",
            "created_at": row["created_at"],
        })
    op.execute("""INSERT INTO domain_assignments (id, domain_id, workspace_id, project_id, hostname, environment, assignment_status, dns_status, tls_status, created_at, updated_at) SELECT pd.id, wd.id, p.workspace_id, pd.project_id, lower(pd.domain), COALESCE(pd.environment, 'production'), 'active', CASE WHEN pd.verified_at IS NULL THEN 'pending' ELSE 'active' END, CASE WHEN pd.tls_provisioned_at IS NULL THEN 'pending' ELSE 'active' END, pd.created_at, now() FROM project_domains pd JOIN projects p ON p.id = pd.project_id JOIN workspace_domains wd ON wd.workspace_id = p.workspace_id AND wd.normalized_domain = lower(pd.domain) ON CONFLICT DO NOTHING""")

def downgrade():
    op.drop_table("domain_events"); op.drop_index("uq_domain_orders_idempotency", table_name="domain_orders"); op.drop_table("domain_orders"); op.drop_table("domain_transfers"); op.drop_index("uq_domain_assignments_hostname_active", table_name="domain_assignments"); op.drop_table("domain_assignments"); op.drop_index("uq_workspace_domains_normalized", table_name="workspace_domains"); op.drop_table("workspace_domains")