"""Add CMDB dns_records, load_balancers, cdn_domains tables.

Revision ID: 008
Revises: 007
Create Date: 2026-04-12
"""

from alembic import op
import sqlalchemy as sa

revision = "008"
down_revision = "007"
branch_labels = None
depends_on = None


def _table_exists(table: str) -> bool:
    conn = op.get_bind()
    result = conn.execute(
        sa.text(
            "SELECT COUNT(*) FROM information_schema.tables "
            "WHERE table_schema = DATABASE() AND table_name = :t"
        ),
        {"t": table},
    )
    return result.scalar() > 0


def upgrade() -> None:
    if not _table_exists("cmdb_dns_records"):
        op.create_table(
            "cmdb_dns_records",
            sa.Column("id", sa.String(36), nullable=False),
            sa.Column("provider_account_id", sa.String(36), nullable=False),
            sa.Column("zone_name", sa.String(253), nullable=False),
            sa.Column("record_id", sa.String(128), nullable=False),
            sa.Column("name", sa.String(256), nullable=False),
            sa.Column("record_type", sa.String(32), nullable=False),
            sa.Column("value", sa.Text(), nullable=False),
            sa.Column("ttl", sa.Integer(), nullable=False, server_default=sa.text("600")),
            sa.Column("line", sa.String(64), nullable=True),
            sa.Column("status", sa.String(32), nullable=False, server_default=sa.text("'enable'")),
            sa.Column("raw_data", sa.JSON(), nullable=True),
            sa.Column("last_seen_sync_job_id", sa.String(36), nullable=True),
            sa.Column("first_seen_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
            sa.Column("last_synced_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
            sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True),
            sa.PrimaryKeyConstraint("id"),
            sa.UniqueConstraint("provider_account_id", "zone_name", "record_id", name="uq_cmdb_dns_records"),
            sa.ForeignKeyConstraint(
                ["provider_account_id"], ["cmdb_provider_accounts.id"],
                name="fk_cmdb_dns_records_provider",
            ),
        )
        op.create_index("idx_cmdb_dns_records_zone", "cmdb_dns_records", ["zone_name"])
        op.create_index("idx_cmdb_dns_records_type", "cmdb_dns_records", ["record_type"])

    if not _table_exists("cmdb_load_balancers"):
        op.create_table(
            "cmdb_load_balancers",
            sa.Column("id", sa.String(36), nullable=False),
            sa.Column("provider_account_id", sa.String(36), nullable=False),
            sa.Column("lb_id", sa.String(128), nullable=False),
            sa.Column("name", sa.String(256), nullable=True),
            sa.Column("lb_type", sa.String(32), nullable=False),
            sa.Column("network_type", sa.String(32), nullable=False),
            sa.Column("ip_address", sa.String(256), nullable=True),
            sa.Column("region", sa.String(64), nullable=True),
            sa.Column("status", sa.String(32), nullable=False),
            sa.Column("raw_data", sa.JSON(), nullable=True),
            sa.Column("last_seen_sync_job_id", sa.String(36), nullable=True),
            sa.Column("first_seen_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
            sa.Column("last_synced_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
            sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True),
            sa.PrimaryKeyConstraint("id"),
            sa.UniqueConstraint("provider_account_id", "lb_id", name="uq_cmdb_load_balancers"),
            sa.ForeignKeyConstraint(
                ["provider_account_id"], ["cmdb_provider_accounts.id"],
                name="fk_cmdb_load_balancers_provider",
            ),
        )
        op.create_index("idx_cmdb_load_balancers_region", "cmdb_load_balancers", ["region"])
        op.create_index("idx_cmdb_load_balancers_status", "cmdb_load_balancers", ["status"])

    if not _table_exists("cmdb_cdn_domains"):
        op.create_table(
            "cmdb_cdn_domains",
            sa.Column("id", sa.String(36), nullable=False),
            sa.Column("provider_account_id", sa.String(36), nullable=False),
            sa.Column("domain_name", sa.String(253), nullable=False),
            sa.Column("cname", sa.String(256), nullable=True),
            sa.Column("status", sa.String(32), nullable=False, server_default=sa.text("'online'")),
            sa.Column("source_type", sa.String(32), nullable=True),
            sa.Column("source_address", sa.String(512), nullable=True),
            sa.Column("is_https", sa.Boolean(), nullable=False, server_default=sa.text("0")),
            sa.Column("raw_data", sa.JSON(), nullable=True),
            sa.Column("last_seen_sync_job_id", sa.String(36), nullable=True),
            sa.Column("first_seen_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
            sa.Column("last_synced_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
            sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True),
            sa.PrimaryKeyConstraint("id"),
            sa.UniqueConstraint("provider_account_id", "domain_name", name="uq_cmdb_cdn_domains"),
            sa.ForeignKeyConstraint(
                ["provider_account_id"], ["cmdb_provider_accounts.id"],
                name="fk_cmdb_cdn_domains_provider",
            ),
        )
        op.create_index("idx_cmdb_cdn_domains_status", "cmdb_cdn_domains", ["status"])


def downgrade() -> None:
    for table in ("cmdb_cdn_domains", "cmdb_load_balancers", "cmdb_dns_records"):
        if _table_exists(table):
            op.drop_table(table)