"""create uptimerobot reseller tables

Revision ID: d2f7c4e8a1b6
Revises: c5e7a9b3f2d1
Create Date: 2026-05-03 09:00:00.000000

Creates the eight tables that back the UptimeRobot Reseller Manager module:

- uptime_packages              — admin-defined monitoring plans
- uptime_client_subscriptions  — ties a DoItEasy user to a package
- uptime_client_monitors       — internal mirror of upstream UR monitors
- uptime_alert_contacts        — per-subscription alert contacts
- uptime_maintenance_windows   — per-subscription maintenance windows
- uptime_status_pages          — per-subscription public status pages
- uptime_api_logs              — every outbound UR API call
- uptime_usage_snapshots       — periodic Team-plan consumption snapshots

All creates are idempotent — repeated bootstraps succeed even if a subset of
tables already exists (matches the existing style in this project).
"""
from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op

# revision identifiers, used by Alembic.
revision: str = "d2f7c4e8a1b6"
down_revision: Union[str, Sequence[str], None] = "c5e7a9b3f2d1"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def _has_table(table: str) -> bool:
    bind = op.get_bind()
    inspector = sa.inspect(bind)
    try:
        return inspector.has_table(table)
    except Exception:  # noqa: BLE001
        return False


def _safe_create_index(name: str, table: str, cols: list[str], unique: bool = False) -> None:
    try:
        op.create_index(name, table, cols, unique=unique)
    except Exception:  # noqa: BLE001
        pass


def upgrade() -> None:
    # --- uptime_packages ---
    if not _has_table("uptime_packages"):
        op.create_table(
            "uptime_packages",
            sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
            sa.Column("slug", sa.String(length=64), nullable=False),
            sa.Column("name", sa.String(length=128), nullable=False),
            sa.Column("description", sa.Text(), nullable=True),
            sa.Column("max_monitors", sa.Integer(), nullable=False, server_default="0"),
            sa.Column(
                "monitors_unlimited", sa.Boolean(), nullable=False, server_default=sa.false()
            ),
            sa.Column("max_alert_contacts", sa.Integer(), nullable=False, server_default="0"),
            sa.Column(
                "alert_contacts_unlimited",
                sa.Boolean(),
                nullable=False,
                server_default=sa.false(),
            ),
            sa.Column(
                "max_maintenance_windows", sa.Integer(), nullable=False, server_default="0"
            ),
            sa.Column(
                "maintenance_windows_unlimited",
                sa.Boolean(),
                nullable=False,
                server_default=sa.false(),
            ),
            sa.Column("max_status_pages", sa.Integer(), nullable=False, server_default="0"),
            sa.Column(
                "status_pages_unlimited",
                sa.Boolean(),
                nullable=False,
                server_default=sa.false(),
            ),
            sa.Column(
                "min_check_interval", sa.Integer(), nullable=False, server_default="300"
            ),
            sa.Column(
                "allowed_monitor_types",
                sa.String(length=255),
                nullable=False,
                server_default="http",
            ),
            sa.Column(
                "public_status_page_allowed",
                sa.Boolean(),
                nullable=False,
                server_default=sa.false(),
            ),
            sa.Column(
                "api_monitoring_allowed",
                sa.Boolean(),
                nullable=False,
                server_default=sa.false(),
            ),
            sa.Column(
                "advanced_notifications_allowed",
                sa.Boolean(),
                nullable=False,
                server_default=sa.false(),
            ),
            sa.Column("whmcs_product_id", sa.Integer(), nullable=True),
            sa.Column(
                "terminate_action",
                sa.String(length=16),
                nullable=False,
                server_default="archive",
            ),
            sa.Column(
                "is_active", sa.Boolean(), nullable=False, server_default=sa.true()
            ),
            sa.Column("price_per_month", sa.String(length=32), nullable=True),
            sa.Column(
                "created_at",
                sa.DateTime(timezone=True),
                server_default=sa.func.now(),
                nullable=False,
            ),
            sa.Column(
                "updated_at",
                sa.DateTime(timezone=True),
                server_default=sa.func.now(),
                nullable=False,
            ),
            sa.PrimaryKeyConstraint("id"),
            sa.UniqueConstraint("slug", name="uq_uptime_packages_slug"),
        )
        _safe_create_index("ix_uptime_packages_slug", "uptime_packages", ["slug"])
        _safe_create_index(
            "ix_uptime_packages_whmcs_product_id",
            "uptime_packages",
            ["whmcs_product_id"],
        )

    # --- uptime_client_subscriptions ---
    if not _has_table("uptime_client_subscriptions"):
        op.create_table(
            "uptime_client_subscriptions",
            sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
            sa.Column("user_id", sa.String(length=255), nullable=False),
            sa.Column("user_email", sa.String(length=255), nullable=True),
            sa.Column("package_id", sa.Integer(), nullable=False),
            sa.Column("whmcs_service_id", sa.Integer(), nullable=True),
            sa.Column("whmcs_product_id", sa.Integer(), nullable=True),
            sa.Column(
                "status", sa.String(length=16), nullable=False, server_default="active"
            ),
            sa.Column("monitors_used", sa.Integer(), nullable=False, server_default="0"),
            sa.Column(
                "alert_contacts_used", sa.Integer(), nullable=False, server_default="0"
            ),
            sa.Column(
                "maintenance_windows_used",
                sa.Integer(),
                nullable=False,
                server_default="0",
            ),
            sa.Column(
                "status_pages_used", sa.Integer(), nullable=False, server_default="0"
            ),
            sa.Column("notes", sa.Text(), nullable=True),
            sa.Column("activated_at", sa.DateTime(timezone=True), nullable=True),
            sa.Column("suspended_at", sa.DateTime(timezone=True), nullable=True),
            sa.Column("terminated_at", sa.DateTime(timezone=True), nullable=True),
            sa.Column(
                "created_at",
                sa.DateTime(timezone=True),
                server_default=sa.func.now(),
                nullable=False,
            ),
            sa.Column(
                "updated_at",
                sa.DateTime(timezone=True),
                server_default=sa.func.now(),
                nullable=False,
            ),
            sa.PrimaryKeyConstraint("id"),
        )
        _safe_create_index(
            "ix_uptime_subs_user_id", "uptime_client_subscriptions", ["user_id"]
        )
        _safe_create_index(
            "ix_uptime_subs_user_email", "uptime_client_subscriptions", ["user_email"]
        )
        _safe_create_index(
            "ix_uptime_subs_status", "uptime_client_subscriptions", ["status"]
        )
        _safe_create_index(
            "ix_uptime_subs_package_id", "uptime_client_subscriptions", ["package_id"]
        )
        _safe_create_index(
            "ix_uptime_subs_whmcs_service_id",
            "uptime_client_subscriptions",
            ["whmcs_service_id"],
        )

    # --- uptime_client_monitors ---
    if not _has_table("uptime_client_monitors"):
        op.create_table(
            "uptime_client_monitors",
            sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
            sa.Column("subscription_id", sa.Integer(), nullable=False),
            sa.Column("user_id", sa.String(length=255), nullable=False),
            sa.Column("ur_monitor_id", sa.String(length=64), nullable=False),
            sa.Column("friendly_name", sa.String(length=255), nullable=False),
            sa.Column("monitor_type", sa.String(length=32), nullable=False),
            sa.Column("url", sa.Text(), nullable=True),
            sa.Column("keyword", sa.String(length=255), nullable=True),
            sa.Column("port", sa.Integer(), nullable=True),
            sa.Column(
                "interval_seconds", sa.Integer(), nullable=False, server_default="300"
            ),
            sa.Column(
                "status", sa.String(length=32), nullable=False, server_default="unknown"
            ),
            sa.Column("uptime_ratio_24h", sa.String(length=16), nullable=True),
            sa.Column("uptime_ratio_7d", sa.String(length=16), nullable=True),
            sa.Column("uptime_ratio_30d", sa.String(length=16), nullable=True),
            sa.Column("response_time_ms", sa.Integer(), nullable=True),
            sa.Column("last_error", sa.Text(), nullable=True),
            sa.Column("last_synced_at", sa.DateTime(timezone=True), nullable=True),
            sa.Column("paused", sa.Boolean(), nullable=False, server_default=sa.false()),
            sa.Column(
                "archived", sa.Boolean(), nullable=False, server_default=sa.false()
            ),
            sa.Column(
                "created_at",
                sa.DateTime(timezone=True),
                server_default=sa.func.now(),
                nullable=False,
            ),
            sa.Column(
                "updated_at",
                sa.DateTime(timezone=True),
                server_default=sa.func.now(),
                nullable=False,
            ),
            sa.PrimaryKeyConstraint("id"),
            sa.UniqueConstraint("ur_monitor_id", name="uq_uptime_monitors_ur_id"),
        )
        _safe_create_index(
            "ix_uptime_monitors_subscription",
            "uptime_client_monitors",
            ["subscription_id"],
        )
        _safe_create_index(
            "ix_uptime_monitors_user", "uptime_client_monitors", ["user_id"]
        )

    # --- uptime_alert_contacts ---
    if not _has_table("uptime_alert_contacts"):
        op.create_table(
            "uptime_alert_contacts",
            sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
            sa.Column("subscription_id", sa.Integer(), nullable=False),
            sa.Column("user_id", sa.String(length=255), nullable=False),
            sa.Column("ur_contact_id", sa.String(length=64), nullable=False),
            sa.Column("contact_type", sa.String(length=32), nullable=False),
            sa.Column("friendly_name", sa.String(length=128), nullable=True),
            sa.Column("value", sa.String(length=512), nullable=False),
            sa.Column(
                "verified", sa.Boolean(), nullable=False, server_default=sa.false()
            ),
            sa.Column(
                "created_at",
                sa.DateTime(timezone=True),
                server_default=sa.func.now(),
                nullable=False,
            ),
            sa.Column(
                "updated_at",
                sa.DateTime(timezone=True),
                server_default=sa.func.now(),
                nullable=False,
            ),
            sa.PrimaryKeyConstraint("id"),
            sa.UniqueConstraint("ur_contact_id", name="uq_uptime_contacts_ur_id"),
        )
        _safe_create_index(
            "ix_uptime_contacts_subscription",
            "uptime_alert_contacts",
            ["subscription_id"],
        )

    # --- uptime_maintenance_windows ---
    if not _has_table("uptime_maintenance_windows"):
        op.create_table(
            "uptime_maintenance_windows",
            sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
            sa.Column("subscription_id", sa.Integer(), nullable=False),
            sa.Column("user_id", sa.String(length=255), nullable=False),
            sa.Column("ur_window_id", sa.String(length=64), nullable=False),
            sa.Column("friendly_name", sa.String(length=128), nullable=False),
            sa.Column(
                "window_type", sa.String(length=16), nullable=False, server_default="once"
            ),
            sa.Column("start_time", sa.String(length=64), nullable=True),
            sa.Column(
                "duration_minutes", sa.Integer(), nullable=False, server_default="60"
            ),
            sa.Column(
                "status", sa.String(length=16), nullable=False, server_default="active"
            ),
            sa.Column(
                "created_at",
                sa.DateTime(timezone=True),
                server_default=sa.func.now(),
                nullable=False,
            ),
            sa.Column(
                "updated_at",
                sa.DateTime(timezone=True),
                server_default=sa.func.now(),
                nullable=False,
            ),
            sa.PrimaryKeyConstraint("id"),
            sa.UniqueConstraint("ur_window_id", name="uq_uptime_windows_ur_id"),
        )
        _safe_create_index(
            "ix_uptime_windows_subscription",
            "uptime_maintenance_windows",
            ["subscription_id"],
        )

    # --- uptime_status_pages ---
    if not _has_table("uptime_status_pages"):
        op.create_table(
            "uptime_status_pages",
            sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
            sa.Column("subscription_id", sa.Integer(), nullable=False),
            sa.Column("user_id", sa.String(length=255), nullable=False),
            sa.Column("ur_page_id", sa.String(length=64), nullable=False),
            sa.Column("friendly_name", sa.String(length=128), nullable=False),
            sa.Column("standard_url", sa.String(length=512), nullable=True),
            sa.Column("custom_url", sa.String(length=512), nullable=True),
            sa.Column("monitor_ids_json", sa.Text(), nullable=True),
            sa.Column(
                "created_at",
                sa.DateTime(timezone=True),
                server_default=sa.func.now(),
                nullable=False,
            ),
            sa.Column(
                "updated_at",
                sa.DateTime(timezone=True),
                server_default=sa.func.now(),
                nullable=False,
            ),
            sa.PrimaryKeyConstraint("id"),
            sa.UniqueConstraint("ur_page_id", name="uq_uptime_pages_ur_id"),
        )
        _safe_create_index(
            "ix_uptime_pages_subscription",
            "uptime_status_pages",
            ["subscription_id"],
        )

    # --- uptime_api_logs ---
    if not _has_table("uptime_api_logs"):
        op.create_table(
            "uptime_api_logs",
            sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
            sa.Column("endpoint", sa.String(length=255), nullable=False),
            sa.Column("method", sa.String(length=8), nullable=False, server_default="GET"),
            sa.Column(
                "status", sa.String(length=16), nullable=False, server_default="ok"
            ),
            sa.Column("error_class", sa.String(length=64), nullable=True),
            sa.Column("http_status", sa.Integer(), nullable=True),
            sa.Column("duration_ms", sa.Integer(), nullable=True),
            sa.Column("user_email", sa.String(length=255), nullable=True),
            sa.Column("error_message", sa.Text(), nullable=True),
            sa.Column(
                "created_at",
                sa.DateTime(timezone=True),
                server_default=sa.func.now(),
                nullable=False,
            ),
            sa.PrimaryKeyConstraint("id"),
        )
        _safe_create_index(
            "ix_uptime_api_logs_created", "uptime_api_logs", ["created_at"]
        )
        _safe_create_index(
            "ix_uptime_api_logs_status", "uptime_api_logs", ["status"]
        )

    # --- uptime_usage_snapshots ---
    if not _has_table("uptime_usage_snapshots"):
        op.create_table(
            "uptime_usage_snapshots",
            sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
            sa.Column("monitors_used", sa.Integer(), nullable=False, server_default="0"),
            sa.Column("monitors_total", sa.Integer(), nullable=True),
            sa.Column(
                "alert_contacts_used", sa.Integer(), nullable=False, server_default="0"
            ),
            sa.Column("alert_contacts_total", sa.Integer(), nullable=True),
            sa.Column(
                "status_pages_used", sa.Integer(), nullable=False, server_default="0"
            ),
            sa.Column("status_pages_total", sa.Integer(), nullable=True),
            sa.Column(
                "maintenance_windows_used",
                sa.Integer(),
                nullable=False,
                server_default="0",
            ),
            sa.Column("maintenance_windows_total", sa.Integer(), nullable=True),
            sa.Column("plan_name", sa.String(length=64), nullable=True),
            sa.Column(
                "warning_state",
                sa.String(length=32),
                nullable=False,
                server_default="ok",
            ),
            sa.Column(
                "created_at",
                sa.DateTime(timezone=True),
                server_default=sa.func.now(),
                nullable=False,
            ),
            sa.PrimaryKeyConstraint("id"),
        )
        _safe_create_index(
            "ix_uptime_usage_created", "uptime_usage_snapshots", ["created_at"]
        )


def downgrade() -> None:
    for table in (
        "uptime_usage_snapshots",
        "uptime_api_logs",
        "uptime_status_pages",
        "uptime_maintenance_windows",
        "uptime_alert_contacts",
        "uptime_client_monitors",
        "uptime_client_subscriptions",
        "uptime_packages",
    ):
        if _has_table(table):
            try:
                op.drop_table(table)
            except Exception:  # noqa: BLE001
                pass