"""scheduler_job_log and PostgreSQL INSERT functions

Revision ID: bfd7a23f9437
Revises: 3a3d5a864c79
Create Date: 2026-06-03

What this migration adds
------------------------
Table
  scheduler_job_log    Execution history for APScheduler jobs.
                       One row per invocation — status, duration, error.

PostgreSQL INSERT functions (PL/pgSQL)
  fn_insert_user            Validate + insert a new user account.
  fn_insert_api_key         Validate + insert a new API key.
  fn_write_audit_event      Append an immutable row to auth_audit_log.
  fn_log_job_run            Insert a scheduler job execution record.

Why functions?
  • Centralise DML business rules at the DB layer — a second caller
    (e.g. a data pipeline, a DB script) cannot bypass the same validations.
  • Simplifies permission model: application role only needs
    EXECUTE on these functions, not direct INSERT on base tables.
  • Self-documenting: the function signature is the canonical insert contract.
"""

from __future__ import annotations

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql


# ---------------------------------------------------------------------------
# Revision identifiers
# ---------------------------------------------------------------------------
revision: str = "bfd7a23f9437"
down_revision: str = "3a3d5a864c79"
branch_labels = None
depends_on = None


# ===========================================================================
# UPGRADE
# ===========================================================================

def upgrade() -> None:

    # -----------------------------------------------------------------------
    # 1.  scheduler_job_log
    # -----------------------------------------------------------------------
    op.create_table(
        "scheduler_job_log",
        sa.Column(
            "id",
            sa.BigInteger(),
            primary_key=True,
            autoincrement=True,
            comment="Sequential PK — fast append + time-range scans",
        ),
        sa.Column(
            "job_id",
            sa.String(128),
            nullable=False,
            comment="Matches JOBS[*].id in app/scheduler/config.py",
        ),
        sa.Column(
            "status",
            sa.String(16),
            nullable=False,
            comment="success | error | skipped",
        ),
        sa.Column(
            "duration_ms",
            sa.Integer(),
            nullable=True,
            comment="Wall-clock execution time in ms. NULL when skipped.",
        ),
        sa.Column(
            "error_msg",
            sa.Text(),
            nullable=True,
            comment="Exception message/traceback on status=error",
        ),
        sa.Column(
            "job_metadata",
            postgresql.JSONB(),
            nullable=False,
            server_default=sa.text("'{}'::jsonb"),
            comment="Job-specific context blob. Never log secrets here.",
        ),
        sa.Column(
            "started_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.text("NOW()"),
            comment="UTC timestamp when the job wrapper was invoked",
        ),
        sa.CheckConstraint(
            "status IN ('success', 'error', 'skipped')",
            name="ck_job_log_status_valid",
        ),
    )

    op.create_index(
        "ix_job_log_job_id_started",
        "scheduler_job_log",
        ["job_id", "started_at"],
    )
    op.create_index(
        "ix_job_log_status_started",
        "scheduler_job_log",
        ["status", "started_at"],
    )
    op.create_index(
        "ix_job_log_started_at",
        "scheduler_job_log",
        ["started_at"],
    )

    # -----------------------------------------------------------------------
    # 2.  fn_insert_user
    #     Validates + inserts a row into users.
    #     Returns the new user's UUID.
    #
    #     Parameters:
    #       p_email          TEXT   — stored lowercased + trimmed
    #       p_password_hash  TEXT   — bcrypt hash (must start with '$2b$')
    #       p_role           TEXT   — viewer | analyst | admin (default viewer)
    # -----------------------------------------------------------------------
    op.execute(
        """
        CREATE OR REPLACE FUNCTION fn_insert_user(
            p_email         TEXT,
            p_password_hash TEXT,
            p_role          TEXT DEFAULT 'viewer'
        )
        RETURNS UUID
        LANGUAGE plpgsql
        SECURITY DEFINER
        AS $$
        DECLARE
            v_email  TEXT;
            v_new_id UUID;
        BEGIN
            -- Normalise email: lowercase + trim whitespace
            v_email := LOWER(TRIM(p_email));

            -- Guard: email must not be blank
            IF v_email = '' THEN
                RAISE EXCEPTION 'email cannot be blank'
                    USING ERRCODE = 'invalid_parameter_value';
            END IF;

            -- Guard: role must be known
            IF p_role NOT IN ('viewer', 'analyst', 'admin') THEN
                RAISE EXCEPTION 'invalid role %. Valid: viewer, analyst, admin', p_role
                    USING ERRCODE = 'invalid_parameter_value';
            END IF;

            -- Guard: password_hash must look like a bcrypt hash
            IF p_password_hash NOT LIKE '$2b$%' AND p_password_hash NOT LIKE '$2a$%' THEN
                RAISE EXCEPTION 'password_hash does not appear to be a valid bcrypt hash'
                    USING ERRCODE = 'invalid_parameter_value';
            END IF;

            INSERT INTO users (email, password_hash, role)
            VALUES (v_email, p_password_hash, p_role)
            RETURNING id INTO v_new_id;

            RETURN v_new_id;
        END;
        $$;
        """
    )

    # -----------------------------------------------------------------------
    # 3.  fn_insert_api_key
    #     Validates + inserts a row into api_keys.
    #     Returns the new key's UUID (key_id).
    #
    #     Parameters:
    #       p_secret_hash    TEXT     — Fernet-encrypted ('fernet:…') or bcrypt ('$2b$…')
    #       p_service_name   TEXT     — human label, 1-128 chars
    #       p_scopes         TEXT[]   — must be non-empty; each scope validated
    #       p_rate_limit     INTEGER  — max requests/minute (default 600)
    #       p_description    TEXT     — optional ops note (default NULL)
    #       p_expires_at     TIMESTAMPTZ — NULL = never expires (default NULL)
    #       p_created_by     UUID     — issuing admin user ID (default NULL)
    # -----------------------------------------------------------------------
    op.execute(
        """
        CREATE OR REPLACE FUNCTION fn_insert_api_key(
            p_secret_hash   TEXT,
            p_service_name  TEXT,
            p_scopes        TEXT[],
            p_rate_limit    INTEGER     DEFAULT 600,
            p_description   TEXT        DEFAULT NULL,
            p_expires_at    TIMESTAMPTZ DEFAULT NULL,
            p_created_by    UUID        DEFAULT NULL
        )
        RETURNS UUID
        LANGUAGE plpgsql
        SECURITY DEFINER
        AS $$
        DECLARE
            v_service_name TEXT;
            v_new_id       UUID;
            v_valid_scopes TEXT[] := ARRAY[
                'data:push', 'data:pull', 'feed:subscribe', 'internal:job'
            ];
            v_scope        TEXT;
        BEGIN
            -- Normalise service name
            v_service_name := TRIM(p_service_name);
            IF LENGTH(v_service_name) = 0 THEN
                RAISE EXCEPTION 'service_name cannot be blank'
                    USING ERRCODE = 'invalid_parameter_value';
            END IF;
            IF LENGTH(v_service_name) > 128 THEN
                RAISE EXCEPTION 'service_name exceeds 128 characters'
                    USING ERRCODE = 'invalid_parameter_value';
            END IF;

            -- Guard: scopes must not be empty
            IF p_scopes IS NULL OR array_length(p_scopes, 1) IS NULL THEN
                RAISE EXCEPTION 'scopes array must contain at least one value'
                    USING ERRCODE = 'invalid_parameter_value';
            END IF;

            -- Guard: each scope must be a known value
            FOREACH v_scope IN ARRAY p_scopes LOOP
                IF NOT (v_scope = ANY(v_valid_scopes)) THEN
                    RAISE EXCEPTION 'unknown scope: %. Valid: %', v_scope, array_to_string(v_valid_scopes, ', ')
                        USING ERRCODE = 'invalid_parameter_value';
                END IF;
            END LOOP;

            -- Guard: rate_limit must be positive
            IF p_rate_limit < 1 OR p_rate_limit > 10000 THEN
                RAISE EXCEPTION 'rate_limit must be between 1 and 10000, got %', p_rate_limit
                    USING ERRCODE = 'invalid_parameter_value';
            END IF;

            -- Guard: expires_at must be in the future if provided
            IF p_expires_at IS NOT NULL AND p_expires_at <= NOW() THEN
                RAISE EXCEPTION 'expires_at must be a future timestamp'
                    USING ERRCODE = 'invalid_parameter_value';
            END IF;

            INSERT INTO api_keys (
                secret_hash, service_name, scopes,
                rate_limit, description, expires_at, created_by
            )
            VALUES (
                p_secret_hash, v_service_name, p_scopes,
                p_rate_limit, p_description, p_expires_at, p_created_by
            )
            RETURNING key_id INTO v_new_id;

            RETURN v_new_id;
        END;
        $$;
        """
    )

    # -----------------------------------------------------------------------
    # 4.  fn_write_audit_event
    #     Append-only INSERT into auth_audit_log.
    #     Returns the new row's BIGINT id.
    #
    #     Parameters (all optional except p_event_type):
    #       p_event_type   TEXT     — must be a known event type
    #       p_caller_type  TEXT     — user | api_key | cron | NULL
    #       p_user_id      UUID     — NULL allowed (survives user deletion)
    #       p_key_id       UUID     — NULL allowed
    #       p_ip_address   INET     — NULL allowed
    #       p_user_agent   TEXT     — NULL allowed; truncated to 512 chars
    #       p_metadata     JSONB    — event-specific context (default '{}')
    # -----------------------------------------------------------------------
    op.execute(
        """
        CREATE OR REPLACE FUNCTION fn_write_audit_event(
            p_event_type  TEXT,
            p_caller_type TEXT        DEFAULT NULL,
            p_user_id     UUID        DEFAULT NULL,
            p_key_id      UUID        DEFAULT NULL,
            p_ip_address  INET        DEFAULT NULL,
            p_user_agent  TEXT        DEFAULT NULL,
            p_metadata    JSONB       DEFAULT '{}'::jsonb
        )
        RETURNS BIGINT
        LANGUAGE plpgsql
        SECURITY DEFINER
        AS $$
        DECLARE
            v_valid_events TEXT[] := ARRAY[
                'login_success', 'login_failure',
                'token_refresh', 'token_refresh_failure',
                'logout', 'key_revoked', 'replay_attack',
                'invalid_signature', 'scope_denied',
                'account_locked', 'job_run',
                'key_secret_revealed', 'key_rotated'
            ];
            v_new_id BIGINT;
        BEGIN
            -- Guard: event_type must be a known value
            IF NOT (p_event_type = ANY(v_valid_events)) THEN
                RAISE EXCEPTION 'unknown event_type: %. Valid: %',
                    p_event_type, array_to_string(v_valid_events, ', ')
                    USING ERRCODE = 'invalid_parameter_value';
            END IF;

            -- Guard: caller_type must be known if provided
            IF p_caller_type IS NOT NULL
               AND p_caller_type NOT IN ('user', 'api_key', 'cron') THEN
                RAISE EXCEPTION 'invalid caller_type: %. Valid: user, api_key, cron', p_caller_type
                    USING ERRCODE = 'invalid_parameter_value';
            END IF;

            INSERT INTO auth_audit_log (
                event_type, caller_type,
                user_id, key_id,
                ip_address, user_agent,
                metadata
            )
            VALUES (
                p_event_type, p_caller_type,
                p_user_id, p_key_id,
                p_ip_address, LEFT(COALESCE(p_user_agent, ''), 512),
                COALESCE(p_metadata, '{}'::jsonb)
            )
            RETURNING id INTO v_new_id;

            RETURN v_new_id;
        END;
        $$;
        """
    )

    # -----------------------------------------------------------------------
    # 5.  fn_log_job_run
    #     Insert a scheduler job execution record.
    #     Returns the new row's BIGINT id.
    #
    #     Parameters:
    #       p_job_id       TEXT     — job identifier from scheduler config
    #       p_status       TEXT     — success | error | skipped
    #       p_duration_ms  INTEGER  — NULL for skipped runs
    #       p_error_msg    TEXT     — NULL on success/skipped
    #       p_metadata     JSONB    — job-specific context (default '{}')
    # -----------------------------------------------------------------------
    op.execute(
        """
        CREATE OR REPLACE FUNCTION fn_log_job_run(
            p_job_id      TEXT,
            p_status      TEXT,
            p_duration_ms INTEGER     DEFAULT NULL,
            p_error_msg   TEXT        DEFAULT NULL,
            p_metadata    JSONB       DEFAULT '{}'::jsonb
        )
        RETURNS BIGINT
        LANGUAGE plpgsql
        SECURITY DEFINER
        AS $$
        DECLARE
            v_new_id BIGINT;
        BEGIN
            -- Guard: job_id must not be blank
            IF TRIM(p_job_id) = '' THEN
                RAISE EXCEPTION 'job_id cannot be blank'
                    USING ERRCODE = 'invalid_parameter_value';
            END IF;

            -- Guard: status must be known
            IF p_status NOT IN ('success', 'error', 'skipped') THEN
                RAISE EXCEPTION 'invalid status: %. Valid: success, error, skipped', p_status
                    USING ERRCODE = 'invalid_parameter_value';
            END IF;

            -- Guard: error_msg should accompany status=error
            IF p_status = 'error' AND (p_error_msg IS NULL OR TRIM(p_error_msg) = '') THEN
                RAISE WARNING 'fn_log_job_run: status=error but error_msg is blank for job %', p_job_id;
            END IF;

            INSERT INTO scheduler_job_log (
                job_id, status, duration_ms, error_msg, job_metadata
            )
            VALUES (
                TRIM(p_job_id),
                p_status,
                p_duration_ms,
                p_error_msg,
                COALESCE(p_metadata, '{}'::jsonb)
            )
            RETURNING id INTO v_new_id;

            RETURN v_new_id;
        END;
        $$;
        """
    )

    # -----------------------------------------------------------------------
    # 6.  GRANT EXECUTE on all insert functions to the application role.
    #     Replace 'finauth_app' with your actual PostgreSQL application role.
    # -----------------------------------------------------------------------
    for fn_sig in [
        "fn_insert_user(TEXT, TEXT, TEXT)",
        "fn_insert_api_key(TEXT, TEXT, TEXT[], INTEGER, TEXT, TIMESTAMPTZ, UUID)",
        "fn_write_audit_event(TEXT, TEXT, UUID, UUID, INET, TEXT, JSONB)",
        "fn_log_job_run(TEXT, TEXT, INTEGER, TEXT, JSONB)",
    ]:
        op.execute(
            f"GRANT EXECUTE ON FUNCTION {fn_sig} TO PUBLIC"
            # Change PUBLIC → 'finauth_app' in production to restrict access
        )


# ===========================================================================
# DOWNGRADE
# ===========================================================================

def downgrade() -> None:
    # Drop functions first (no dependents)
    op.execute("DROP FUNCTION IF EXISTS fn_log_job_run(TEXT, TEXT, INTEGER, TEXT, JSONB)")
    op.execute("DROP FUNCTION IF EXISTS fn_write_audit_event(TEXT, TEXT, UUID, UUID, INET, TEXT, JSONB)")
    op.execute("DROP FUNCTION IF EXISTS fn_insert_api_key(TEXT, TEXT, TEXT[], INTEGER, TEXT, TIMESTAMPTZ, UUID)")
    op.execute("DROP FUNCTION IF EXISTS fn_insert_user(TEXT, TEXT, TEXT)")

    # Drop table
    op.drop_index("ix_job_log_started_at",       table_name="scheduler_job_log")
    op.drop_index("ix_job_log_status_started",    table_name="scheduler_job_log")
    op.drop_index("ix_job_log_job_id_started",    table_name="scheduler_job_log")
    op.drop_table("scheduler_job_log")