"""Initial schema."""

from __future__ import annotations

from collections.abc import Sequence

import sqlalchemy as sa
from alembic import op

revision: str = "0001_initial"
down_revision: str | None = None
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    op.create_table(
        "trackers",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("canonical_url", sa.String(length=512), nullable=False),
        sa.Column("scheme", sa.String(length=16), nullable=False),
        sa.Column("hostname", sa.String(length=255), nullable=False),
        sa.Column("port", sa.Integer(), nullable=False),
        sa.Column("path", sa.String(length=512), nullable=False),
        sa.Column("current_status", sa.String(length=32), nullable=False),
        sa.Column(
            "first_seen_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("(CURRENT_TIMESTAMP)"),
            nullable=False,
        ),
        sa.Column(
            "last_seen_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("(CURRENT_TIMESTAMP)"),
            nullable=False,
        ),
        sa.Column("last_checked_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("next_check_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("announced_interval_seconds", sa.Integer(), nullable=True),
        sa.Column("last_latency_ms", sa.Float(), nullable=True),
        sa.Column("supports_ipv4", sa.Boolean(), nullable=True),
        sa.Column("supports_ipv6", sa.String(length=32), nullable=True),
        sa.Column("terminal_cname", sa.String(length=255), nullable=True),
        sa.Column("infrastructure_fingerprint", sa.String(length=128), nullable=True),
        sa.Column("asn", sa.Integer(), nullable=True),
        sa.Column("network_name", sa.String(length=255), nullable=True),
        sa.Column("country_code", sa.String(length=8), nullable=True),
        sa.Column("consecutive_failures", sa.Integer(), nullable=False),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("(CURRENT_TIMESTAMP)"),
            nullable=False,
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("(CURRENT_TIMESTAMP)"),
            nullable=False,
        ),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("canonical_url"),
    )
    op.create_index("ix_trackers_hostname", "trackers", ["hostname"])
    op.create_index("ix_trackers_current_status", "trackers", ["current_status"])
    op.create_index("ix_trackers_next_check_at", "trackers", ["next_check_at"])
    op.create_index(
        "ix_trackers_infrastructure_fingerprint", "trackers", ["infrastructure_fingerprint"]
    )

    op.create_table(
        "tracker_sources",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("tracker_id", sa.Integer(), nullable=False),
        sa.Column("source_name", sa.String(length=64), nullable=False),
        sa.Column("source_url", sa.String(length=512), nullable=False),
        sa.Column("is_best_at_source", sa.Boolean(), nullable=False),
        sa.Column("is_stable_at_source", sa.Boolean(), nullable=False),
        sa.Column("is_live_at_source", sa.Boolean(), nullable=False),
        sa.Column(
            "first_seen_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("(CURRENT_TIMESTAMP)"),
            nullable=False,
        ),
        sa.Column(
            "last_seen_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("(CURRENT_TIMESTAMP)"),
            nullable=False,
        ),
        sa.ForeignKeyConstraint(["tracker_id"], ["trackers.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("tracker_id", "source_name", name="uq_tracker_source"),
    )
    op.create_index("ix_tracker_sources_tracker_id", "tracker_sources", ["tracker_id"])

    op.create_table(
        "probe_results",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("tracker_id", sa.Integer(), nullable=False),
        sa.Column("checked_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("status", sa.String(length=32), nullable=False),
        sa.Column("latency_ms", sa.Float(), nullable=True),
        sa.Column("ip_address", sa.String(length=64), nullable=True),
        sa.Column("ip_family", sa.String(length=8), nullable=True),
        sa.Column("response_valid", sa.Boolean(), nullable=False),
        sa.Column("tracker_interval_seconds", sa.Integer(), nullable=True),
        sa.Column("seeders", sa.Integer(), nullable=True),
        sa.Column("leechers", sa.Integer(), nullable=True),
        sa.Column("error_kind", sa.String(length=64), nullable=True),
        sa.Column("error_detail", sa.String(length=512), nullable=True),
        sa.ForeignKeyConstraint(["tracker_id"], ["trackers.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index("ix_probe_results_tracker_id", "probe_results", ["tracker_id"])
    op.create_index("ix_probe_results_checked_at", "probe_results", ["checked_at"])

    op.create_table(
        "dns_records",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("tracker_id", sa.Integer(), nullable=False),
        sa.Column("record_type", sa.String(length=16), nullable=False),
        sa.Column("value", sa.String(length=512), nullable=False),
        sa.Column(
            "first_seen_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("(CURRENT_TIMESTAMP)"),
            nullable=False,
        ),
        sa.Column(
            "last_seen_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("(CURRENT_TIMESTAMP)"),
            nullable=False,
        ),
        sa.ForeignKeyConstraint(["tracker_id"], ["trackers.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("tracker_id", "record_type", "value", name="uq_dns_record"),
    )
    op.create_index("ix_dns_records_tracker_id", "dns_records", ["tracker_id"])

    op.create_table(
        "source_import_runs",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("source_name", sa.String(length=64), nullable=False),
        sa.Column("started_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("finished_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("status", sa.String(length=32), nullable=False),
        sa.Column("received_count", sa.Integer(), nullable=False),
        sa.Column("valid_count", sa.Integer(), nullable=False),
        sa.Column("new_count", sa.Integer(), nullable=False),
        sa.Column("unsupported_count", sa.Integer(), nullable=False),
        sa.Column("error_message", sa.String(length=512), nullable=True),
        sa.Column("etag", sa.String(length=255), nullable=True),
        sa.Column("last_modified", sa.String(length=255), nullable=True),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index("ix_source_import_runs_source_name", "source_import_runs", ["source_name"])

    op.create_table(
        "collector_cache",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("source_name", sa.String(length=64), nullable=False),
        sa.Column("feed_url", sa.String(length=512), nullable=False),
        sa.Column("etag", sa.String(length=255), nullable=True),
        sa.Column("last_modified", sa.String(length=255), nullable=True),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("(CURRENT_TIMESTAMP)"),
            nullable=False,
        ),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("feed_url"),
    )

    op.create_table(
        "app_state",
        sa.Column("key", sa.String(length=64), nullable=False),
        sa.Column("value", sa.Text(), nullable=False),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("(CURRENT_TIMESTAMP)"),
            nullable=False,
        ),
        sa.PrimaryKeyConstraint("key"),
    )


def downgrade() -> None:
    op.drop_table("app_state")
    op.drop_table("collector_cache")
    op.drop_index("ix_source_import_runs_source_name", table_name="source_import_runs")
    op.drop_table("source_import_runs")
    op.drop_index("ix_dns_records_tracker_id", table_name="dns_records")
    op.drop_table("dns_records")
    op.drop_index("ix_probe_results_checked_at", table_name="probe_results")
    op.drop_index("ix_probe_results_tracker_id", table_name="probe_results")
    op.drop_table("probe_results")
    op.drop_index("ix_tracker_sources_tracker_id", table_name="tracker_sources")
    op.drop_table("tracker_sources")
    op.drop_index("ix_trackers_infrastructure_fingerprint", table_name="trackers")
    op.drop_index("ix_trackers_next_check_at", table_name="trackers")
    op.drop_index("ix_trackers_current_status", table_name="trackers")
    op.drop_index("ix_trackers_hostname", table_name="trackers")
    op.drop_table("trackers")