"""Add the Phase 2B controlled-writer approval and action ledger.

The migration creates only isolated sidecar tables. It never touches NocoDB or
its schema and remains subject to the disposable migration environment guard.
"""

from __future__ import annotations

from alembic import op

revision = "0002_writer_ledger"
down_revision = "0001_create_sidecar_schema"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.execute(
        """
        CREATE TABLE osm_lead_source.writer_cutover_approval (
            approval_id text PRIMARY KEY CHECK (btrim(approval_id) <> ''),
            report_hash text NOT NULL CHECK (report_hash ~ '^[0-9a-f]{64}$'),
            region_id text NOT NULL CHECK (btrim(region_id) <> ''),
            profile_id text NOT NULL CHECK (btrim(profile_id) <> ''),
            profile_version text NOT NULL CHECK (btrim(profile_version) <> ''),
            noco_table_id text NOT NULL CHECK (btrim(noco_table_id) <> ''),
            approved_max_inserts integer NOT NULL
                CHECK (approved_max_inserts BETWEEN 1 AND 10000),
            legacy_writer_disabled_at timestamptz NOT NULL,
            approved_by text NOT NULL CHECK (btrim(approved_by) <> ''),
            approved_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
            expires_at timestamptz NOT NULL,
            revoked_at timestamptz,
            revoked_reason text,
            evidence_json jsonb NOT NULL CHECK (evidence_json <> '{}'::jsonb),
            CONSTRAINT writer_cutover_approval_time_order CHECK (
                legacy_writer_disabled_at <= approved_at
                AND approved_at < expires_at
                AND (revoked_at IS NULL OR revoked_at >= approved_at)
            ),
            CONSTRAINT writer_cutover_revocation_pair CHECK (
                (revoked_at IS NULL AND revoked_reason IS NULL)
                OR (revoked_at IS NOT NULL AND revoked_reason IS NOT NULL
                    AND btrim(revoked_reason) <> '')
            )
        )
        """
    )
    op.execute(
        """
        CREATE INDEX writer_cutover_approval_active_idx
        ON osm_lead_source.writer_cutover_approval
            (noco_table_id, report_hash, expires_at)
        WHERE revoked_at IS NULL
        """
    )
    op.execute(
        """
        CREATE TABLE osm_lead_source.insert_action_ledger (
            id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
            approval_id text NOT NULL
                REFERENCES osm_lead_source.writer_cutover_approval(approval_id),
            idempotency_key text NOT NULL UNIQUE
                CHECK (idempotency_key ~ '^[0-9a-f]{64}$'),
            report_hash text NOT NULL CHECK (report_hash ~ '^[0-9a-f]{64}$'),
            region_id text NOT NULL CHECK (btrim(region_id) <> ''),
            profile_id text NOT NULL CHECK (btrim(profile_id) <> ''),
            profile_version text NOT NULL CHECK (btrim(profile_version) <> ''),
            osm_type text NOT NULL CHECK (osm_type IN ('node', 'way', 'relation')),
            osm_id bigint NOT NULL CHECK (osm_id > 0),
            payload_hash text NOT NULL CHECK (payload_hash ~ '^[0-9a-f]{64}$'),
            state text NOT NULL CHECK (state IN ('claimed', 'applied', 'duplicate', 'failed')),
            attempt_count integer NOT NULL DEFAULT 1 CHECK (attempt_count >= 1),
            noco_table_id text CHECK (noco_table_id IS NULL OR btrim(noco_table_id) <> ''),
            noco_record_id bigint CHECK (noco_record_id IS NULL OR noco_record_id > 0),
            duplicate_evidence_json jsonb,
            failure_code text,
            claimed_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
            finalized_at timestamptz,
            evidence_json jsonb NOT NULL CHECK (evidence_json <> '{}'::jsonb),
            CONSTRAINT insert_action_noco_pair CHECK (
                (noco_table_id IS NULL) = (noco_record_id IS NULL)
            ),
            CONSTRAINT insert_action_final_shape CHECK (
                (state = 'claimed' AND finalized_at IS NULL
                    AND noco_record_id IS NULL
                    AND duplicate_evidence_json IS NULL
                    AND failure_code IS NULL)
                OR (state = 'applied' AND finalized_at IS NOT NULL
                    AND noco_record_id IS NOT NULL
                    AND duplicate_evidence_json IS NULL
                    AND failure_code IS NULL)
                OR (state = 'duplicate' AND finalized_at IS NOT NULL
                    AND noco_record_id IS NULL
                    AND duplicate_evidence_json IS NOT NULL
                    AND failure_code IS NULL)
                OR (state = 'failed' AND finalized_at IS NOT NULL
                    AND noco_record_id IS NULL
                    AND duplicate_evidence_json IS NULL
                    AND failure_code IS NOT NULL
                    AND btrim(failure_code) <> '')
            )
        )
        """
    )
    op.execute(
        """
        CREATE INDEX insert_action_ledger_report_state_idx
        ON osm_lead_source.insert_action_ledger
            (report_hash, state, claimed_at DESC)
        """
    )
    op.execute(
        """
        CREATE UNIQUE INDEX insert_action_ledger_one_report_osm_identity
        ON osm_lead_source.insert_action_ledger
            (report_hash, osm_type, osm_id)
        """
    )


def downgrade() -> None:
    op.execute("DROP TABLE osm_lead_source.insert_action_ledger")
    op.execute("DROP TABLE osm_lead_source.writer_cutover_approval")