"""create module_sync_settings and module_sync_logs tables

Revision ID: m1s2y3n4c5e6
Revises: fba8d976777d
Create Date: 2026-05-03 12:00:00.000000

Adds two shared tables that back the universal "Fetch Data Toggle" system
for every reseller module (UptimeRobot, Bizmail360, WhatsApp, future…):

- ``module_sync_settings``: one row per module, holding the three toggle
  flags (clients/accounts/domains), the latest sync timestamps, the
  per-type status strings ("ok"/"failed"/"skipped"/"never"), the most
  recent error message, and the running fetched-count totals.

- ``module_sync_logs``: append-only history of every sync attempt (manual,
  cron, or diagnostic). Contains a masked raw-response blob for
  admin-side debugging.

All CREATE statements are idempotent via ``IF NOT EXISTS``.
"""
from __future__ import annotations

from alembic import op

# revision identifiers, used by Alembic.
revision = "m1s2y3n4c5e6"
down_revision = "fba8d976777d"
branch_labels = None
depends_on = None


def upgrade() -> None:
    bind = op.get_bind()
    dialect = bind.dialect.name
    pk = "SERIAL PRIMARY KEY" if dialect == "postgresql" else "INTEGER PRIMARY KEY AUTOINCREMENT"
    ts_default = "TIMESTAMPTZ NOT NULL DEFAULT NOW()" if dialect == "postgresql" else "TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP"
    ts_null = "TIMESTAMPTZ NULL" if dialect == "postgresql" else "TIMESTAMP NULL"

    op.execute(
        f"""
        CREATE TABLE IF NOT EXISTS module_sync_settings (
            id {pk},
            module_name VARCHAR(64) NOT NULL UNIQUE,
            fetch_clients_enabled BOOLEAN NOT NULL DEFAULT FALSE,
            fetch_accounts_enabled BOOLEAN NOT NULL DEFAULT FALSE,
            fetch_domains_enabled BOOLEAN NOT NULL DEFAULT FALSE,
            last_clients_sync {ts_null},
            last_accounts_sync {ts_null},
            last_domains_sync {ts_null},
            clients_sync_status VARCHAR(32) NOT NULL DEFAULT 'never',
            accounts_sync_status VARCHAR(32) NOT NULL DEFAULT 'never',
            domains_sync_status VARCHAR(32) NOT NULL DEFAULT 'never',
            clients_count INTEGER NOT NULL DEFAULT 0,
            accounts_count INTEGER NOT NULL DEFAULT 0,
            domains_count INTEGER NOT NULL DEFAULT 0,
            domains_linked_count INTEGER NOT NULL DEFAULT 0,
            domains_unlinked_count INTEGER NOT NULL DEFAULT 0,
            last_error TEXT NULL,
            created_at {ts_default},
            updated_at {ts_default}
        )
        """
    )

    op.execute(
        f"""
        CREATE TABLE IF NOT EXISTS module_sync_logs (
            id {pk},
            module_name VARCHAR(64) NOT NULL,
            sync_type VARCHAR(32) NOT NULL,
            status VARCHAR(32) NOT NULL,
            items_fetched INTEGER NOT NULL DEFAULT 0,
            error_message TEXT NULL,
            raw_response TEXT NULL,
            triggered_by VARCHAR(32) NOT NULL DEFAULT 'manual',
            actor_email VARCHAR(255) NULL,
            duration_ms INTEGER NOT NULL DEFAULT 0,
            started_at {ts_default},
            finished_at {ts_null}
        )
        """
    )

    # Indexes — best-effort (ignore if duplicate index names clash).
    try:
        op.execute("CREATE INDEX IF NOT EXISTS ix_module_sync_logs_module ON module_sync_logs (module_name)")
        op.execute("CREATE INDEX IF NOT EXISTS ix_module_sync_logs_started ON module_sync_logs (started_at)")
        op.execute("CREATE INDEX IF NOT EXISTS ix_module_sync_logs_type ON module_sync_logs (sync_type)")
    except Exception:  # noqa: BLE001
        pass


def downgrade() -> None:
    op.execute("DROP TABLE IF EXISTS module_sync_logs")
    op.execute("DROP TABLE IF EXISTS module_sync_settings")