-- Athena logging-client provisioning script.
--
-- Applies the logging, gateway, audit, queue, and operational support tables
-- used by Athena's logging database.
-- Every statement uses CREATE TABLE IF NOT EXISTS / CREATE INDEX IF NOT EXISTS
-- so the script is safe to re-run against an already-provisioned database.
--
-- Usage:
--   psql "$DATABASE_URL" -f sql/provision_logging.sql

-- BEGIN GENERATED: storage-files
-- Managed storage/files schema provisioned from the canonical Athena storage SQL.
-- Generated from sql/files/athena-files.sql by scripts/generate_storage_files_artifacts.py.
-- Do not edit this file directly.

-- Canonical Athena-managed file and S3 catalog tables.
-- This file is intentionally clean DDL only.
-- Generate sql/provision_files.sql, the storage-files block in sql/provision.sql,
-- and docs/architecture/storage-files-schema.md from this file via
-- scripts/generate_storage_files_artifacts.py.
-- Canonical names here are singular:
--   athena.audit_log_files
--   athena.event_log_files
-- Do not recreate plural alias objects such as athena.audit_logs_files / athena.event_logs_files.


CREATE SCHEMA IF NOT EXISTS athena;

CREATE TABLE IF NOT EXISTS athena.s3 (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    name text NOT NULL,
    description text NOT NULL DEFAULT '',
    endpoint text NOT NULL,
    region text NOT NULL,
    bucket text,
    provider text NOT NULL DEFAULT 's3',
    is_active boolean NOT NULL DEFAULT true,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    deleted_at timestamptz,
    CONSTRAINT athena_s3_name_nonempty CHECK (btrim(name) <> ''),
    CONSTRAINT athena_s3_endpoint_nonempty CHECK (btrim(endpoint) <> ''),
    CONSTRAINT athena_s3_region_nonempty CHECK (btrim(region) <> ''),
    CONSTRAINT athena_s3_provider_nonempty CHECK (btrim(provider) <> ''),
    CONSTRAINT athena_s3_metadata_is_object CHECK (jsonb_typeof(metadata) = 'object')
);

CREATE UNIQUE INDEX IF NOT EXISTS athena_s3_name_unique
    ON athena.s3 (lower(name))
    WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS athena_s3_active_created_idx
    ON athena.s3 (is_active, created_at DESC)
    WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS athena_s3_bucket_created_idx
    ON athena.s3 (bucket, created_at DESC)
    WHERE bucket IS NOT NULL AND deleted_at IS NULL;

CREATE TABLE IF NOT EXISTS athena.s3_credentials (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    s3_id uuid NOT NULL REFERENCES athena.s3(id) ON DELETE CASCADE,
    description text NOT NULL DEFAULT '',
    access_key_id text NOT NULL,
    secret_key text NOT NULL,
    session_token text,
    is_active boolean NOT NULL DEFAULT true,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    rotated_at timestamptz,
    expires_at timestamptz,
    CONSTRAINT athena_s3_credentials_access_key_nonempty CHECK (btrim(access_key_id) <> ''),
    CONSTRAINT athena_s3_credentials_secret_nonempty CHECK (btrim(secret_key) <> ''),
    CONSTRAINT athena_s3_credentials_metadata_is_object CHECK (jsonb_typeof(metadata) = 'object')
);

CREATE UNIQUE INDEX IF NOT EXISTS athena_s3_credentials_one_active_per_s3_idx
    ON athena.s3_credentials (s3_id)
    WHERE is_active = true;

CREATE INDEX IF NOT EXISTS athena_s3_credentials_active_created_idx
    ON athena.s3_credentials (is_active, created_at DESC);

CREATE INDEX IF NOT EXISTS athena_s3_credentials_s3_created_idx
    ON athena.s3_credentials (s3_id, created_at DESC);

CREATE TABLE IF NOT EXISTS athena.files (
    id text PRIMARY KEY,
    name text NOT NULL,
    original_name text,
    url text,
    bucket text NOT NULL,
    s3_id uuid REFERENCES athena.s3(id) ON DELETE SET NULL,
    prefix_path text,
    size_bytes bigint,
    mime_type text,
    resource_id text,
    organization_id text NOT NULL,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    storage_key text NOT NULL,
    uploaded_by_user_id text,
    extension text,
    is_public boolean NOT NULL DEFAULT false,
    status text NOT NULL DEFAULT 'active',
    deleted_at timestamptz,
    CONSTRAINT athena_files_name_nonempty CHECK (btrim(name) <> ''),
    CONSTRAINT athena_files_bucket_nonempty CHECK (btrim(bucket) <> ''),
    CONSTRAINT athena_files_organization_nonempty CHECK (btrim(organization_id) <> ''),
    CONSTRAINT athena_files_storage_key_nonempty CHECK (btrim(storage_key) <> ''),
    CONSTRAINT athena_files_metadata_is_object CHECK (jsonb_typeof(metadata) = 'object'),
    CONSTRAINT athena_files_status_valid CHECK (status IN ('active', 'deleted', 'archived'))
);

CREATE INDEX IF NOT EXISTS athena_files_organization_resource_created_idx
    ON athena.files (organization_id, resource_id, created_at DESC);

CREATE INDEX IF NOT EXISTS athena_files_organization_status_updated_idx
    ON athena.files (organization_id, status, updated_at DESC);

CREATE INDEX IF NOT EXISTS athena_files_storage_key_idx
    ON athena.files (storage_key);

CREATE INDEX IF NOT EXISTS athena_files_s3_id_created_idx
    ON athena.files (s3_id, created_at DESC)
    WHERE s3_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS athena_files_bucket_prefix_idx
    ON athena.files (bucket, prefix_path, created_at DESC);

CREATE UNIQUE INDEX IF NOT EXISTS athena_files_org_s3_storage_key_active_uidx
    ON athena.files (organization_id, s3_id, storage_key)
    WHERE deleted_at IS NULL AND s3_id IS NOT NULL;

CREATE TABLE IF NOT EXISTS athena.audit_log_files (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    audit_log_file_id uuid NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    file_id text REFERENCES athena.files(id) ON DELETE SET NULL,
    action text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    time numeric,
    route text,
    request text,
    status text,
    message text,
    author_user_id text,
    user_id text,
    organization_id text,
    new_status text,
    table_name text NOT NULL DEFAULT 'files',
    resource_id text,
    resource jsonb,
    diff_resource jsonb,
    user_object jsonb,
    event_name text,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT athena_audit_log_files_action_nonempty CHECK (btrim(action) <> ''),
    CONSTRAINT athena_audit_log_files_metadata_is_object CHECK (jsonb_typeof(metadata) = 'object')
);

CREATE INDEX IF NOT EXISTS athena_audit_log_files_file_created_idx
    ON athena.audit_log_files (file_id, created_at DESC)
    WHERE file_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS athena_audit_log_files_organization_created_idx
    ON athena.audit_log_files (organization_id, created_at DESC)
    WHERE organization_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS athena_audit_log_files_action_created_idx
    ON athena.audit_log_files (action, created_at DESC);

CREATE TABLE IF NOT EXISTS athena.event_log_files (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    event_log_file_id uuid NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    file_id text REFERENCES athena.files(id) ON DELETE SET NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    directive text,
    time numeric,
    path text,
    content text,
    server text,
    api_version text,
    severity text,
    message text,
    payload jsonb,
    user_id text,
    organization_id text,
    status text,
    ref_id text,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT athena_event_log_files_metadata_is_object CHECK (jsonb_typeof(metadata) = 'object')
);

CREATE INDEX IF NOT EXISTS athena_event_log_files_file_created_idx
    ON athena.event_log_files (file_id, created_at DESC)
    WHERE file_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS athena_event_log_files_organization_created_idx
    ON athena.event_log_files (organization_id, created_at DESC)
    WHERE organization_id IS NOT NULL;

CREATE TABLE IF NOT EXISTS athena.file_permissions (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    file_id text NOT NULL REFERENCES athena.files(id) ON DELETE CASCADE,
    principal_type text NOT NULL,
    principal_id text NOT NULL,
    permission text NOT NULL,
    granted_by_user_id text,
    created_at timestamptz NOT NULL DEFAULT now(),
    expires_at timestamptz,
    revoked_at timestamptz,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT athena_file_permissions_principal_type_valid CHECK (
        principal_type IN ('user', 'organization', 'team', 'role')
    ),
    CONSTRAINT athena_file_permissions_permission_valid CHECK (
        permission IN ('read', 'write', 'delete', 'share', 'owner')
    ),
    CONSTRAINT athena_file_permissions_metadata_is_object CHECK (jsonb_typeof(metadata) = 'object'),
    UNIQUE (file_id, principal_type, principal_id, permission)
);

DROP INDEX IF EXISTS athena.athena_file_permissions_lookup_idx;
DROP INDEX IF EXISTS athena.athena_file_permissions_principal_idx;

CREATE INDEX IF NOT EXISTS file_permissions_lookup_idx
    ON athena.file_permissions (file_id, principal_type, principal_id, permission)
    WHERE revoked_at IS NULL;

CREATE INDEX IF NOT EXISTS file_permissions_principal_idx
    ON athena.file_permissions (principal_type, principal_id)
    WHERE revoked_at IS NULL;

CREATE TABLE IF NOT EXISTS athena.s3_url_cache (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    file_id text NOT NULL REFERENCES athena.files(id) ON DELETE CASCADE,
    purpose text NOT NULL DEFAULT 'read',
    bucket text NOT NULL,
    storage_key text NOT NULL,
    url text NOT NULL,
    expires_at timestamptz NOT NULL,
    expires_at_epoch_seconds bigint NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT athena_s3_url_cache_purpose_valid CHECK (
        purpose IN ('read', 'download', 'stream')
    ),
    CONSTRAINT athena_s3_url_cache_metadata_is_object CHECK (jsonb_typeof(metadata) = 'object'),
    UNIQUE (file_id, purpose)
);

CREATE INDEX IF NOT EXISTS athena_s3_url_cache_lookup_idx
    ON athena.s3_url_cache (file_id, purpose, expires_at);

CREATE INDEX IF NOT EXISTS athena_s3_url_cache_expiry_idx
    ON athena.s3_url_cache (expires_at);
-- END GENERATED: storage-files

-- ---------------------------------------------------------------------------
-- Gateway request / operation logs
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS gateway_request_log (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    created_at timestamptz NOT NULL DEFAULT now(),
    request_id text NOT NULL,
    client text,
    method text,
    path text,
    query_string text,
    status_code integer,
    ipv4 text,
    ipv4_address text,
    user_agent text,
    headers jsonb,
    body jsonb,
    user_id text,
    company_id text,
    organization_id text,
    api_key_id text,
    presented_api_key_public_id text,
    presented_api_key_hash text,
    presented_api_key_salt text,
    api_key_authenticated boolean DEFAULT false,
    api_key_authorized boolean DEFAULT false,
    api_key_enforced boolean DEFAULT false,
    api_key_auth_reason text,
    host text,
    cached boolean,
    duration_ms bigint,
    cache_lookup_outcome text,
    cache_source text,
    operation text,
    table_name text,
    time numeric NOT NULL,
    PRIMARY KEY (id)
);

CREATE INDEX IF NOT EXISTS idx_gateway_request_log_request_id
    ON gateway_request_log (request_id);

CREATE INDEX IF NOT EXISTS idx_gateway_request_log_created_at
    ON gateway_request_log (created_at DESC);

CREATE INDEX IF NOT EXISTS idx_gateway_request_log_client_created_at
    ON gateway_request_log (client, created_at DESC)
    WHERE client IS NOT NULL;

CREATE TABLE IF NOT EXISTS gateway_connection_registry_realtime (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    created_at timestamptz NOT NULL DEFAULT now(),
    registry_key text NOT NULL,
    client_name text NOT NULL,
    ipv4 text NOT NULL,
    ipv4_address text,
    athena_url text NOT NULL,
    api_key_public_id text,
    api_key_external_id text,
    api_key_internal_id bigint,
    api_key_authenticated boolean NOT NULL DEFAULT false,
    api_key_authorized boolean NOT NULL DEFAULT false,
    last_method text,
    last_path text,
    user_agent text,
    last_request_id text,
    last_status_code integer,
    total_hits bigint NOT NULL DEFAULT 1,
    first_seen_at timestamptz NOT NULL DEFAULT now(),
    last_seen_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (id),
    CONSTRAINT gateway_connection_registry_realtime_registry_key_unique UNIQUE (registry_key)
);

CREATE INDEX IF NOT EXISTS idx_gateway_connection_registry_realtime_client
    ON gateway_connection_registry_realtime (client_name);

CREATE INDEX IF NOT EXISTS idx_gateway_connection_registry_realtime_last_seen
    ON gateway_connection_registry_realtime (last_seen_at DESC);

CREATE INDEX IF NOT EXISTS idx_gateway_connection_registry_realtime_ip
    ON gateway_connection_registry_realtime (ipv4);

CREATE INDEX IF NOT EXISTS idx_gateway_connection_registry_realtime_url
    ON gateway_connection_registry_realtime (athena_url);

CREATE TABLE IF NOT EXISTS gateway_operation_log (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    created_at timestamptz NOT NULL DEFAULT now(),
    request_id text NOT NULL,
    operation text,
    table_name text,
    client text,
    method text,
    path text,
    status_code integer,
    duration_ms bigint,
    details jsonb,
    time numeric NOT NULL,
    error boolean DEFAULT false,
    message text,
    cache_key text,
    cache_key_hashed text,
    cache_lookup_outcome text,
    cache_source text,
    cached boolean,
    PRIMARY KEY (id)
);

-- ---------------------------------------------------------------------------
-- Pipeline run step log (POST /pipelines) written to logging DB when configured
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS pipeline_step_log (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    pipeline_run_id uuid NOT NULL,
    request_id text,
    pipeline_name text,
    step text NOT NULL,
    athena_client text,
    table_name text,
    status_code integer,
    duration_ms bigint,
    error_message text,
    details jsonb,
    CONSTRAINT pipeline_step_log_step_check CHECK (step IN ('source', 'transform', 'sink'))
);

CREATE INDEX IF NOT EXISTS idx_pipeline_step_log_pipeline_run_id
    ON pipeline_step_log (pipeline_run_id);

CREATE INDEX IF NOT EXISTS idx_pipeline_step_log_created_at
    ON pipeline_step_log (created_at DESC);

CREATE INDEX IF NOT EXISTS idx_pipeline_step_log_pipeline_name_created_at
    ON pipeline_step_log (pipeline_name, created_at DESC)
    WHERE pipeline_name IS NOT NULL;

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE IF NOT EXISTS athena_pipeline_templates (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    pipeline_template_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    template_name text NOT NULL,
    definition jsonb NOT NULL,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_athena_pipeline_templates_name
    ON athena_pipeline_templates (lower(template_name));

CREATE TABLE IF NOT EXISTS public.database_audit_log (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    database_audit_log_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    time bigint NOT NULL DEFAULT EXTRACT(EPOCH FROM now())::bigint,
    request_id uuid NOT NULL,
    client_name text NOT NULL,
    schema_name text NOT NULL,
    table_name text,
    object_type text NOT NULL,
    operation text NOT NULL,
    status text NOT NULL,
    api_key_id uuid,
    api_key_public_id text,
    user_id text,
    company_id text,
    organization_id text,
    request_payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    executed_sql jsonb NOT NULL DEFAULT '[]'::jsonb,
    result_payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    error_message text,
    remote_addr text,
    ipv4_address text,
    user_agent text,
    duration_ms bigint
);

CREATE INDEX IF NOT EXISTS database_audit_log_request_id_idx
    ON public.database_audit_log (request_id);

CREATE INDEX IF NOT EXISTS database_audit_log_client_created_at_idx
    ON public.database_audit_log (client_name, created_at DESC);

CREATE INDEX IF NOT EXISTS database_audit_log_schema_table_created_at_idx
    ON public.database_audit_log (schema_name, table_name, created_at DESC);

CREATE TABLE IF NOT EXISTS function_ddl_audit_log (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    function_ddl_audit_log_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    request_id uuid NOT NULL,
    client_name text NOT NULL,
    schema_name text NOT NULL,
    function_name text NOT NULL,
    function_signature text,
    action text NOT NULL,
    status text NOT NULL,
    ddl_sql text NOT NULL,
    previous_definition jsonb NOT NULL DEFAULT '[]'::jsonb,
    next_definition jsonb NOT NULL DEFAULT '[]'::jsonb,
    error_message text,
    api_key_id uuid,
    api_key_public_id text,
    user_id text,
    company_id text,
    organization_id text,
    remote_addr text,
    ipv4_address text,
    user_agent text,
    duration_ms bigint NOT NULL DEFAULT 0,
    time bigint NOT NULL DEFAULT EXTRACT(EPOCH FROM now())::bigint
);

CREATE INDEX IF NOT EXISTS idx_function_ddl_audit_log_request_id
    ON function_ddl_audit_log (request_id);

CREATE INDEX IF NOT EXISTS idx_function_ddl_audit_log_client_created_at
    ON function_ddl_audit_log (client_name, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_function_ddl_audit_log_function
    ON function_ddl_audit_log (schema_name, function_name, created_at DESC);

CREATE TABLE IF NOT EXISTS gateway_deferred_request_queue (
    request_id text PRIMARY KEY,
    method text NOT NULL,
    route text NOT NULL,
    client_name text,
    request_bytes bigint,
    payload jsonb NOT NULL,
    status text NOT NULL DEFAULT 'queued',
    deferred_reason text,
    attempts integer NOT NULL DEFAULT 0,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    next_attempt_at timestamptz,
    completed_at timestamptz,
    error_message text,
    result_summary jsonb
);

CREATE INDEX IF NOT EXISTS idx_gateway_deferred_request_queue_status
    ON gateway_deferred_request_queue(status);

CREATE INDEX IF NOT EXISTS idx_gateway_deferred_request_queue_created_at
    ON gateway_deferred_request_queue(created_at DESC);

CREATE INDEX IF NOT EXISTS idx_gateway_deferred_request_queue_status_created
    ON gateway_deferred_request_queue(status, created_at ASC);

CREATE INDEX IF NOT EXISTS idx_gateway_deferred_request_queue_completed_at
    ON gateway_deferred_request_queue(completed_at DESC)
    WHERE status IN ('completed', 'dead_letter');

CREATE TABLE IF NOT EXISTS gateway_admission_events (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    event_id text NOT NULL,
    method text NOT NULL,
    route text NOT NULL,
    client_name text,
    request_bytes bigint,
    defer_requested boolean NOT NULL DEFAULT false,
    is_query_route boolean NOT NULL DEFAULT false,
    decision text NOT NULL,
    reason text,
    deferred_request_id text,
    retry_after_seconds integer,
    window_seconds integer,
    global_limit_per_window bigint,
    per_client_limit_per_window bigint,
    meta jsonb NOT NULL DEFAULT '{}'::jsonb
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_gateway_admission_events_event_id
    ON gateway_admission_events(event_id);

CREATE INDEX IF NOT EXISTS idx_gateway_admission_events_created_at
    ON gateway_admission_events(created_at DESC);

CREATE INDEX IF NOT EXISTS idx_gateway_admission_events_decision
    ON gateway_admission_events(decision);

CREATE INDEX IF NOT EXISTS idx_gateway_admission_events_client_name
    ON gateway_admission_events(client_name);

-- ---------------------------------------------------------------------------
-- API keys and rights
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS api_keys (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    api_keys_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    public_id text NOT NULL UNIQUE,
    name text NOT NULL,
    description text,
    client_name text,
    key_salt text NOT NULL,
    key_hash text NOT NULL,
    expires_at timestamptz,
    is_active boolean NOT NULL DEFAULT true,
    last_used_at timestamptz,
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_api_keys_client_name ON api_keys(client_name);
CREATE INDEX IF NOT EXISTS idx_api_keys_expires_at ON api_keys(expires_at);

CREATE TABLE IF NOT EXISTS api_key_rights (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    api_key_rights_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    created_at timestamptz NOT NULL DEFAULT now(),
    name text NOT NULL UNIQUE,
    description text,
    updated_at timestamptz NOT NULL DEFAULT now()
);

-- Global rights catalog (assignable to any API key). Keeps Web Explorer / admin UI usable
-- on fresh databases without running separate migration scripts.
INSERT INTO api_key_rights (api_key_rights_id, name, description)
VALUES
    (gen_random_uuid(), 'gateway.access', 'General gateway access when enforcement uses the default fallback'),
    (gen_random_uuid(), 'gateway.read', 'Read rows via gateway fetch'),
    (gen_random_uuid(), 'gateway.write', 'Insert or update via gateway'),
    (gen_random_uuid(), 'gateway.delete', 'Delete rows via gateway'),
    (gen_random_uuid(), 'gateway.query', 'Execute SQL/CQL via gateway query'),
    (gen_random_uuid(), 'gateway.rpc.execute', 'Execute Postgres functions through Athena RPC routes'),
    (gen_random_uuid(), 'gateway.*', 'Wildcard gateway access'),
    (gen_random_uuid(), 'management.read', 'Read management capabilities for a client'),
    (gen_random_uuid(), 'management.tables.write', 'Create or alter managed tables'),
    (gen_random_uuid(), 'management.tables.drop', 'Drop managed tables'),
    (gen_random_uuid(), 'management.columns.write', 'Alter managed columns'),
    (gen_random_uuid(), 'management.columns.drop', 'Drop managed columns'),
    (gen_random_uuid(), 'management.indexes.write', 'Create managed indexes'),
    (gen_random_uuid(), 'management.indexes.drop', 'Drop managed indexes'),
    (gen_random_uuid(), 'management.functions.read', 'List managed Postgres functions'),
    (gen_random_uuid(), 'management.functions.write', 'Create or replace managed Postgres functions'),
    (gen_random_uuid(), 'management.functions.drop', 'Drop managed Postgres functions'),
    (gen_random_uuid(), 'management.*', 'Wildcard management access for a client')
ON CONFLICT (name) DO UPDATE
SET description = EXCLUDED.description,
    updated_at = now();

CREATE TABLE IF NOT EXISTS api_key_right_grants (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    api_key_right_grants_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    api_key_id bigint NOT NULL REFERENCES api_keys(id) ON DELETE CASCADE,
    right_id bigint NOT NULL REFERENCES api_key_rights(id) ON DELETE CASCADE,
    created_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (api_key_id, right_id)
);

CREATE TABLE IF NOT EXISTS api_key_config (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    api_key_config_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    enforce_api_keys boolean NOT NULL DEFAULT false,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO api_key_config (api_key_config_id, enforce_api_keys)
VALUES ('00000000-0000-0000-0000-000000000000', false)
ON CONFLICT (api_key_config_id) DO NOTHING;

CREATE TABLE IF NOT EXISTS api_key_client_config (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    api_key_client_config_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    client_name text NOT NULL UNIQUE,
    enforce_api_keys boolean NOT NULL DEFAULT false,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS api_key_auth_log (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    api_key_auth_log_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    created_at timestamptz NOT NULL DEFAULT now(),
    request_id text NOT NULL,
    api_key_id bigint REFERENCES api_keys(id) ON DELETE SET NULL,
    api_key_public_id text,
    client_name text,
    method text NOT NULL,
    path text NOT NULL,
    presented_key_hash text,
    presented_key_salt text,
    required_rights jsonb,
    granted_rights jsonb,
    authenticated boolean NOT NULL DEFAULT false,
    authorized boolean NOT NULL DEFAULT false,
    enforced boolean NOT NULL DEFAULT false,
    failure_reason text,
    remote_addr text,
    ipv4_address text,
    user_agent text,
    time timestamptz NOT NULL DEFAULT now(),
    UNIQUE(request_id, api_key_public_id)
);

CREATE INDEX IF NOT EXISTS idx_api_key_auth_log_api_key_id ON api_key_auth_log(api_key_id);
CREATE INDEX IF NOT EXISTS idx_api_key_auth_log_client_name ON api_key_auth_log(client_name);
CREATE INDEX IF NOT EXISTS idx_api_key_auth_log_time ON api_key_auth_log(time);

-- ---------------------------------------------------------------------------
-- IP address dimension + statistics (gateway observability)
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS ip_addresses (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    ipv4_address text NOT NULL,
    first_seen_at timestamptz NOT NULL DEFAULT now(),
    last_seen_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT ip_addresses_ipv4_address_unique UNIQUE (ipv4_address)
);

CREATE INDEX IF NOT EXISTS idx_ip_addresses_last_seen ON ip_addresses (last_seen_at DESC);

CREATE TABLE IF NOT EXISTS ip_addresses_statistics (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    ip_address_id bigint NOT NULL REFERENCES ip_addresses (id) ON DELETE CASCADE,
    total_requests bigint NOT NULL DEFAULT 0,
    successful_requests bigint NOT NULL DEFAULT 0,
    failed_requests bigint NOT NULL DEFAULT 0,
    total_cached_requests bigint NOT NULL DEFAULT 0,
    last_request_at timestamptz,
    updated_at timestamptz NOT NULL DEFAULT now (),
    CONSTRAINT ip_addresses_statistics_ip_address_id_unique UNIQUE (ip_address_id)
);

CREATE INDEX IF NOT EXISTS idx_ip_addresses_statistics_updated ON ip_addresses_statistics (updated_at DESC);

-- ---------------------------------------------------------------------------
-- IP backfill: batched, idempotent gateway_request_log → ip_addresses (+ stats)
-- ---------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION public.normalize_gateway_client_ip(
    p_ipv4_address text,
    p_ipv4 text,
    p_headers jsonb
)
RETURNS text
LANGUAGE sql
IMMUTABLE
PARALLEL SAFE
AS $$
    SELECT
        NULLIF(
            NULLIF(
                trim(
                    COALESCE(
                        NULLIF(trim(p_ipv4_address), ''),
                        NULLIF(trim(p_ipv4), ''),
                        trim(
                            split_part(
                                COALESCE(
                                    p_headers ->> 'x-forwarded-for',
                                    p_headers ->> 'X-Forwarded-For',
                                    ''
                                ),
                                ',',
                                1
                            )
                        )
                    )
                ),
                ''
            ),
            'NOT_FOUND'
        );
$$;

CREATE TABLE IF NOT EXISTS ip_backfill_gateway_log_chunk_applied (
    chunk_start_inclusive bigint NOT NULL,
    chunk_end_exclusive bigint NOT NULL,
    gateway_log_rows bigint NOT NULL,
    applied_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT ip_backfill_gateway_log_chunk_applied_pkey PRIMARY KEY (chunk_start_inclusive, chunk_end_exclusive)
);

CREATE INDEX IF NOT EXISTS idx_ip_backfill_chunk_applied_at ON ip_backfill_gateway_log_chunk_applied (applied_at DESC);

CREATE TABLE IF NOT EXISTS ip_backfill_job_state (
    singleton smallint NOT NULL DEFAULT 1 PRIMARY KEY,
    stats_reconcile_after_ip_id bigint NOT NULL DEFAULT 0,
    CONSTRAINT ip_backfill_job_state_singleton_chk CHECK (singleton = 1)
);

INSERT INTO ip_backfill_job_state (singleton, stats_reconcile_after_ip_id)
VALUES (1, 0)
ON CONFLICT (singleton) DO NOTHING;

CREATE OR REPLACE FUNCTION public.apply_ip_backfill_gateway_log_chunk(p_chunk_size bigint)
RETURNS TABLE (
    chunk_start_inclusive bigint,
    chunk_end_exclusive bigint,
    gateway_log_rows_in_chunk bigint,
    distinct_ips_in_chunk bigint,
    already_applied boolean
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_min_id bigint;
    v_max_id bigint;
    v_start bigint;
    v_end bigint;
    v_rows bigint;
    v_distinct bigint;
BEGIN
    IF p_chunk_size IS NULL OR p_chunk_size < 1 THEN
        RAISE EXCEPTION 'p_chunk_size must be >= 1';
    END IF;

    PERFORM pg_advisory_xact_lock(87261431);

    SELECT
        min(id),
        max(id)
    INTO
        v_min_id,
        v_max_id
    FROM
        gateway_request_log;

    IF v_min_id IS NULL OR v_max_id IS NULL THEN
        chunk_start_inclusive := NULL;
        chunk_end_exclusive := NULL;
        gateway_log_rows_in_chunk := 0;
        distinct_ips_in_chunk := 0;
        already_applied := FALSE;
        RETURN NEXT;
        RETURN;
    END IF;

    SELECT
        COALESCE(
            (
                SELECT
                    max(a.chunk_end_exclusive)
                FROM
                    ip_backfill_gateway_log_chunk_applied a
            ),
            v_min_id
        )
    INTO v_start;

    IF v_start > v_max_id THEN
        chunk_start_inclusive := NULL;
        chunk_end_exclusive := NULL;
        gateway_log_rows_in_chunk := 0;
        distinct_ips_in_chunk := 0;
        already_applied := FALSE;
        RETURN NEXT;
        RETURN;
    END IF;

    v_end := v_start + p_chunk_size;

    IF EXISTS (
        SELECT
            1
        FROM
            ip_backfill_gateway_log_chunk_applied c
        WHERE
            c.chunk_start_inclusive = v_start
            AND c.chunk_end_exclusive = v_end
    ) THEN
        chunk_start_inclusive := v_start;
        chunk_end_exclusive := v_end;
        gateway_log_rows_in_chunk := 0;
        distinct_ips_in_chunk := 0;
        already_applied := TRUE;
        RETURN NEXT;
        RETURN;
    END IF;

    SELECT
        COUNT(*)
    INTO v_rows
    FROM
        gateway_request_log g
    WHERE
        g.id >= v_start
        AND g.id < v_end;

    INSERT INTO ip_addresses (ipv4_address, first_seen_at, last_seen_at)
    SELECT
        v.ip_norm,
        MIN(v.created_at),
        MAX(v.created_at)
    FROM (
        SELECT
            g.created_at,
            public.normalize_gateway_client_ip(g.ipv4_address, g.ipv4, g.headers) AS ip_norm
        FROM
            gateway_request_log g
        WHERE
            g.id >= v_start
            AND g.id < v_end
    ) v
    WHERE
        v.ip_norm IS NOT NULL
    GROUP BY
        v.ip_norm
    ON CONFLICT (ipv4_address) DO UPDATE
    SET
        first_seen_at = LEAST(ip_addresses.first_seen_at, EXCLUDED.first_seen_at),
        last_seen_at = GREATEST(ip_addresses.last_seen_at, EXCLUDED.last_seen_at);

    INSERT INTO ip_addresses_statistics (
        ip_address_id,
        total_requests,
        successful_requests,
        failed_requests,
        total_cached_requests,
        last_request_at,
        updated_at
    )
    SELECT
        a.id,
        COUNT(*)::bigint,
        SUM(
            CASE
                WHEN g.status_code >= 200
                AND g.status_code < 400 THEN 1
                ELSE 0
            END
        )::bigint,
        SUM(
            CASE
                WHEN g.status_code >= 400 THEN 1
                ELSE 0
            END
        )::bigint,
        SUM(
            CASE
                WHEN g.cached IS TRUE THEN 1
                ELSE 0
            END
        )::bigint,
        MAX(g.created_at),
        now()
    FROM
        gateway_request_log g
        INNER JOIN ip_addresses a ON a.ipv4_address = public.normalize_gateway_client_ip(
            g.ipv4_address,
            g.ipv4,
            g.headers
        )
    WHERE
        g.id >= v_start
        AND g.id < v_end
    GROUP BY
        a.id
    ON CONFLICT (ip_address_id) DO UPDATE
    SET
        total_requests = ip_addresses_statistics.total_requests + EXCLUDED.total_requests,
        successful_requests = ip_addresses_statistics.successful_requests
        + EXCLUDED.successful_requests,
        failed_requests = ip_addresses_statistics.failed_requests + EXCLUDED.failed_requests,
        total_cached_requests = ip_addresses_statistics.total_cached_requests
        + EXCLUDED.total_cached_requests,
        last_request_at = GREATEST(
            ip_addresses_statistics.last_request_at,
            EXCLUDED.last_request_at
        ),
        updated_at = now();

    SELECT
        COUNT(DISTINCT v.ip_norm)
    INTO v_distinct
    FROM (
        SELECT
            public.normalize_gateway_client_ip(g.ipv4_address, g.ipv4, g.headers) AS ip_norm
        FROM
            gateway_request_log g
        WHERE
            g.id >= v_start
            AND g.id < v_end
    ) v
    WHERE
        v.ip_norm IS NOT NULL;

    INSERT INTO ip_backfill_gateway_log_chunk_applied (
        chunk_start_inclusive,
        chunk_end_exclusive,
        gateway_log_rows
    )
    VALUES (v_start, v_end, v_rows);

    chunk_start_inclusive := v_start;
    chunk_end_exclusive := v_end;
    gateway_log_rows_in_chunk := v_rows;
    distinct_ips_in_chunk := COALESCE(v_distinct, 0);
    already_applied := FALSE;
    RETURN NEXT;
END;
$$;

CREATE OR REPLACE FUNCTION public.reconcile_ip_addresses_statistics_keyset(p_max_ips integer)
RETURNS TABLE (
    batch_min_ip_id bigint,
    batch_max_ip_id bigint,
    ip_rows_in_batch integer,
    statistics_upserted bigint
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_after bigint;
    v_min_batch bigint;
    v_max_batch bigint;
    v_count integer;
    v_upserted bigint;
BEGIN
    IF p_max_ips IS NULL OR p_max_ips < 1 THEN
        RAISE EXCEPTION 'p_max_ips must be >= 1';
    END IF;

    PERFORM pg_advisory_xact_lock(87261432);

    SELECT
        s.stats_reconcile_after_ip_id
    INTO v_after
    FROM
        ip_backfill_job_state s
    WHERE
        s.singleton = 1
    FOR UPDATE;

    IF v_after IS NULL THEN
        INSERT INTO ip_backfill_job_state (singleton, stats_reconcile_after_ip_id)
        VALUES (1, 0)
        ON CONFLICT (singleton) DO NOTHING;

        SELECT
            s.stats_reconcile_after_ip_id
        INTO v_after
        FROM
            ip_backfill_job_state s
        WHERE
            s.singleton = 1
        FOR UPDATE;
    END IF;

    WITH batch AS (
        SELECT
            ia.id,
            ia.ipv4_address
        FROM
            ip_addresses ia
        WHERE
            ia.id > v_after
        ORDER BY
            ia.id
        LIMIT p_max_ips
    ),
    agg AS (
        SELECT
            b.id AS ip_address_id,
            COUNT(g.id)::bigint AS total_requests,
            SUM(
                CASE
                    WHEN g.status_code >= 200
                    AND g.status_code < 400 THEN 1
                    ELSE 0
                END
            )::bigint AS successful_requests,
            SUM(
                CASE
                    WHEN g.status_code >= 400 THEN 1
                    ELSE 0
                END
            )::bigint AS failed_requests,
            SUM(
                CASE
                    WHEN g.cached IS TRUE THEN 1
                    ELSE 0
                END
            )::bigint AS total_cached_requests,
            MAX(g.created_at) AS last_request_at
        FROM
            batch b
            LEFT JOIN gateway_request_log g ON b.ipv4_address = public.normalize_gateway_client_ip(
                g.ipv4_address,
                g.ipv4,
                g.headers
            )
        GROUP BY
            b.id
    )
    INSERT INTO ip_addresses_statistics (
        ip_address_id,
        total_requests,
        successful_requests,
        failed_requests,
        total_cached_requests,
        last_request_at,
        updated_at
    )
    SELECT
        a.ip_address_id,
        COALESCE(a.total_requests, 0),
        COALESCE(a.successful_requests, 0),
        COALESCE(a.failed_requests, 0),
        COALESCE(a.total_cached_requests, 0),
        a.last_request_at,
        now()
    FROM
        agg a
    ON CONFLICT (ip_address_id) DO UPDATE
    SET
        total_requests = EXCLUDED.total_requests,
        successful_requests = EXCLUDED.successful_requests,
        failed_requests = EXCLUDED.failed_requests,
        total_cached_requests = EXCLUDED.total_cached_requests,
        last_request_at = EXCLUDED.last_request_at,
        updated_at = now();

    GET DIAGNOSTICS v_upserted = ROW_COUNT;

    SELECT
        COUNT(*)::integer,
        MIN(b.id),
        MAX(b.id)
    INTO v_count, v_min_batch, v_max_batch
    FROM (
        SELECT
            ia.id
        FROM
            ip_addresses ia
        WHERE
            ia.id > v_after
        ORDER BY
            ia.id
        LIMIT p_max_ips
    ) b;

    IF v_count > 0 AND v_max_batch IS NOT NULL THEN
        UPDATE
            ip_backfill_job_state s
        SET
            stats_reconcile_after_ip_id = v_max_batch
        WHERE
            s.singleton = 1;
    END IF;

    batch_min_ip_id := v_min_batch;
    batch_max_ip_id := v_max_batch;
    ip_rows_in_batch := COALESCE(v_count, 0);
    statistics_upserted := COALESCE(v_upserted, 0);
    RETURN NEXT;
END;
$$;

-- ---------------------------------------------------------------------------
-- Athena clients catalog
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS athena_clients (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    created_at timestamptz NOT NULL DEFAULT now(),
    athena_client_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    client_name text NOT NULL,
    description text,
    pg_uri text,
    pg_uri_env_var text,
    config_uri_template text,
    source text NOT NULL DEFAULT 'database',
    is_active boolean NOT NULL DEFAULT true,
    is_frozen boolean NOT NULL DEFAULT false,
    last_synced_from_config_at timestamptz,
    last_seen_at timestamptz,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    updated_at timestamptz NOT NULL DEFAULT now(),
    deleted_at timestamptz,
    CONSTRAINT athena_clients_source_check CHECK (
        source IN ('config', 'database', 'merged')
    ),
    CONSTRAINT athena_clients_connection_check CHECK (
        pg_uri IS NOT NULL
        OR pg_uri_env_var IS NOT NULL
        OR config_uri_template IS NOT NULL
    )
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_athena_clients_client_name_active
    ON athena_clients (lower(client_name))
    WHERE deleted_at IS NULL;


CREATE INDEX IF NOT EXISTS idx_athena_clients_source
    ON athena_clients (source)
    WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_athena_clients_active
    ON athena_clients (is_active, is_frozen)
    WHERE deleted_at IS NULL;

CREATE TABLE IF NOT EXISTS public_gateway_routes (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    created_at timestamptz NOT NULL DEFAULT now(),
    public_gateway_route_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    route_key text NOT NULL,
    client_name text NOT NULL,
    target_url text,
    allowed_ops text[] NOT NULL,
    is_active boolean NOT NULL DEFAULT true,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    updated_at timestamptz NOT NULL DEFAULT now(),
    deleted_at timestamptz,
    PRIMARY KEY (id)
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_public_gateway_routes_route_key_active
    ON public_gateway_routes (lower(route_key))
    WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_public_gateway_routes_client_name_active
    ON public_gateway_routes (lower(client_name))
    WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_public_gateway_routes_target_url_active
    ON public_gateway_routes (target_url)
    WHERE deleted_at IS NULL AND target_url IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_public_gateway_routes_is_active
    ON public_gateway_routes (is_active)
    WHERE deleted_at IS NULL;

CREATE TABLE IF NOT EXISTS public_service_routes (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    created_at timestamptz NOT NULL DEFAULT now(),
    public_service_route_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    route_key text NOT NULL,
    service_key text NOT NULL,
    target_kind text NOT NULL,
    local_path_prefix text,
    target_url text,
    is_active boolean NOT NULL DEFAULT true,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    updated_at timestamptz NOT NULL DEFAULT now(),
    deleted_at timestamptz,
    CONSTRAINT public_service_routes_target_kind_chk
        CHECK (target_kind IN ('local_path', 'http_upstream')),
    CONSTRAINT public_service_routes_target_shape_chk
        CHECK (
            (target_kind = 'local_path' AND local_path_prefix IS NOT NULL AND btrim(local_path_prefix) <> '')
            OR (target_kind = 'http_upstream' AND target_url IS NOT NULL AND btrim(target_url) <> '')
        ),
    PRIMARY KEY (id)
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_public_service_routes_route_service_active
    ON public_service_routes (lower(route_key), lower(service_key))
    WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_public_service_routes_service_key_active
    ON public_service_routes (lower(service_key))
    WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_public_service_routes_is_active
    ON public_service_routes (is_active)
    WHERE deleted_at IS NULL;

CREATE TABLE IF NOT EXISTS athena_client_configs (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    created_at timestamptz NOT NULL DEFAULT now(),
    athena_client_config_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    client_name text NOT NULL,
    config jsonb NOT NULL DEFAULT '{}'::jsonb,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    updated_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (id)
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_athena_client_configs_client_name
    ON athena_client_configs (lower(client_name));

-- ---------------------------------------------------------------------------
-- API registry (routing and configuration for external APIs)
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS api_registry (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,

    created_at timestamptz NOT NULL DEFAULT now(),
    api_registry_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    name text NOT NULL,
    description text,
    config jsonb NOT NULL DEFAULT '{}'::jsonb,
    updated_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (id)
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_api_registry_api_registry_id
    ON api_registry (api_registry_id);

CREATE INDEX IF NOT EXISTS idx_api_registry_updated_at
    ON api_registry (updated_at DESC);

-- ---------------------------------------------------------------------------
-- Client statistics
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS client_statistics (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    created_at timestamptz NOT NULL DEFAULT now(),
    client_name text NOT NULL,
    total_requests bigint NOT NULL DEFAULT 0,
    successful_requests bigint NOT NULL DEFAULT 0,
    failed_requests bigint NOT NULL DEFAULT 0,
    total_cached_requests bigint NOT NULL DEFAULT 0,
    total_operations bigint NOT NULL DEFAULT 0,
    avg_request_duration_ms double precision,
    p50_request_duration_ms double precision,
    p95_request_duration_ms double precision,
    p99_request_duration_ms double precision,
    cache_hit_ratio double precision,
    last_request_at timestamptz,
    last_operation_at timestamptz,
    updated_at timestamptz NOT NULL DEFAULT now(),

    CONSTRAINT client_statistics_client_name_unique UNIQUE (client_name)
);

CREATE TABLE IF NOT EXISTS client_table_statistics (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    created_at timestamptz NOT NULL DEFAULT now(),
    client_name text NOT NULL,
    table_name text NOT NULL,
    operation text NOT NULL,
    total_operations bigint NOT NULL DEFAULT 0,
    error_operations bigint NOT NULL DEFAULT 0,
    avg_duration_ms double precision,
    p50_duration_ms double precision,
    p95_duration_ms double precision,
    p99_duration_ms double precision,
    total_cache_hits bigint NOT NULL DEFAULT 0,
    total_cache_misses bigint NOT NULL DEFAULT 0,
    cache_hit_ratio double precision,
    last_operation_at timestamptz,
    updated_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT client_table_statistics_unique UNIQUE (client_name, table_name, operation)
);


CREATE INDEX IF NOT EXISTS idx_client_table_statistics_client_name
    ON client_table_statistics (client_name);

CREATE INDEX IF NOT EXISTS idx_client_table_statistics_table_name
    ON client_table_statistics (table_name);

CREATE TABLE IF NOT EXISTS client_alert_queries (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    client_name text,
    summary text,
    db_statement text,
    elapsed_secs numeric,
    slow_threshold_secs numeric,
    rows_affected bigint,
    rows_returned bigint
);

CREATE INDEX IF NOT EXISTS idx_client_alert_queries_created_at
    ON client_alert_queries (created_at DESC);

CREATE INDEX IF NOT EXISTS idx_client_alert_queries_client_name
    ON client_alert_queries (client_name)
    WHERE client_name IS NOT NULL;

CREATE TABLE IF NOT EXISTS query_optimization_runs (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    started_at timestamptz NOT NULL DEFAULT now(),
    completed_at timestamptz,
    client_name text NOT NULL,
    trigger_source text NOT NULL DEFAULT 'manual',
    status text NOT NULL DEFAULT 'running',
    recommendations_generated integer NOT NULL DEFAULT 0,
    error_message text,
    meta jsonb NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT query_optimization_runs_status_check CHECK (
        status IN ('running', 'completed', 'failed')
    )
);

CREATE INDEX IF NOT EXISTS idx_query_optimization_runs_client_created
    ON query_optimization_runs (client_name, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_query_optimization_runs_status
    ON query_optimization_runs (status, created_at DESC);

CREATE TABLE IF NOT EXISTS query_optimization_recommendations (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    client_name text NOT NULL,
    recommendation_key text NOT NULL,
    title text NOT NULL,
    recommendation_type text NOT NULL DEFAULT 'index',
    severity text NOT NULL DEFAULT 'medium',
    status text NOT NULL DEFAULT 'open',
    confidence double precision,
    impact_score double precision,
    rationale text,
    proposed_sql text,
    explain_plan jsonb,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    first_seen_at timestamptz NOT NULL DEFAULT now(),
    last_seen_at timestamptz NOT NULL DEFAULT now(),
    applied_at timestamptz,
    applied_by text,
    apply_error text,
    run_id bigint REFERENCES query_optimization_runs(id) ON DELETE SET NULL,
    CONSTRAINT query_optimization_recommendations_key_unique UNIQUE (client_name, recommendation_key),
    CONSTRAINT query_optimization_recommendations_severity_check CHECK (
        severity IN ('low', 'medium', 'high', 'critical')
    ),
    CONSTRAINT query_optimization_recommendations_status_check CHECK (
        status IN ('open', 'applied', 'dismissed')
    )
);

CREATE INDEX IF NOT EXISTS idx_query_optimization_recommendations_client_status
    ON query_optimization_recommendations (client_name, status, updated_at DESC);

CREATE INDEX IF NOT EXISTS idx_query_optimization_recommendations_severity
    ON query_optimization_recommendations (severity, updated_at DESC);

CREATE TABLE IF NOT EXISTS query_optimization_actions (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    completed_at timestamptz,
    recommendation_id bigint NOT NULL REFERENCES query_optimization_recommendations(id) ON DELETE CASCADE,
    client_name text NOT NULL,
    action text NOT NULL DEFAULT 'apply',
    status text NOT NULL DEFAULT 'running',
    actor text,
    executed_sql text,
    error_message text,
    meta jsonb NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT query_optimization_actions_status_check CHECK (
        status IN ('running', 'success', 'failed', 'skipped')
    )
);

CREATE INDEX IF NOT EXISTS idx_query_optimization_actions_recommendation
    ON query_optimization_actions (recommendation_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_query_optimization_actions_client_created
    ON query_optimization_actions (client_name, created_at DESC);

-- ---------------------------------------------------------------------------
-- Connection pool occupancy snapshots
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS client_connections (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    recorded_at timestamptz NOT NULL DEFAULT now(),
    client_name text NOT NULL,
    host text,
    instance_id uuid NOT NULL,
    pool_size integer NOT NULL,
    idle_connections integer NOT NULL,
    active_connections integer NOT NULL,
    max_connections integer NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_client_connections_client_recorded
    ON client_connections (client_name, recorded_at DESC);

CREATE INDEX IF NOT EXISTS idx_client_connections_instance
    ON client_connections (instance_id);

-- ---------------------------------------------------------------------------
-- Client pressure snapshots (secondary worker)
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS client_pressure_snapshot_runs (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    recorded_at timestamptz NOT NULL DEFAULT now(),
    window_start timestamptz NOT NULL,
    window_end timestamptz NOT NULL,
    status text NOT NULL,
    skip_reason text,
    formula_version text NOT NULL DEFAULT 'v1',
    logging_saturation_ratio double precision,
    pool_timeout_like_failure boolean NOT NULL DEFAULT false,
    collection_error text,
    meta jsonb NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT client_pressure_snapshot_runs_status_check CHECK (
        status IN ('completed', 'skipped_load_shed', 'failed')
    )
);

CREATE INDEX IF NOT EXISTS idx_client_pressure_snapshot_runs_window_status
    ON client_pressure_snapshot_runs (window_start DESC, status);

CREATE INDEX IF NOT EXISTS idx_client_pressure_snapshot_runs_recorded
    ON client_pressure_snapshot_runs (recorded_at DESC);

CREATE TABLE IF NOT EXISTS client_pressure_snapshots (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    run_id bigint NOT NULL REFERENCES client_pressure_snapshot_runs(id) ON DELETE CASCADE,
    recorded_at timestamptz NOT NULL DEFAULT now(),
    window_start timestamptz NOT NULL,
    window_end timestamptz NOT NULL,
    client_name text NOT NULL,
    request_count bigint NOT NULL DEFAULT 0,
    failed_requests bigint NOT NULL DEFAULT 0,
    cache_misses bigint NOT NULL DEFAULT 0,
    total_work_ms bigint NOT NULL DEFAULT 0,
    p50_duration_ms double precision,
    p95_duration_ms double precision,
    error_ratio double precision,
    cache_miss_ratio double precision,
    tail_ratio double precision,
    burst_ratio double precision,
    saturation_ratio double precision,
    dominant_table_share double precision,
    request_shock double precision,
    error_shock double precision,
    pressure_score double precision,
    volatility_score double precision,
    planner_score double precision,
    pressure_band text NOT NULL,
    placement_hint text NOT NULL,
    observed_only boolean NOT NULL DEFAULT true,
    planner_enrichment_attempted boolean NOT NULL DEFAULT false,
    planner_enrichment_applied boolean NOT NULL DEFAULT false,
    planner_enrichment_skipped_reason text,
    meta jsonb NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT client_pressure_snapshots_unique_window_client UNIQUE (window_start, client_name),
    CONSTRAINT client_pressure_snapshots_band_check CHECK (
        pressure_band IN ('green', 'yellow', 'red')
    ),
    CONSTRAINT client_pressure_snapshots_hint_check CHECK (
        placement_hint IN ('healthy', 'watch', 'split_hot_table', 'move_client')
    )
);

CREATE INDEX IF NOT EXISTS idx_client_pressure_snapshots_client_window
    ON client_pressure_snapshots (client_name, window_start DESC);

CREATE INDEX IF NOT EXISTS idx_client_pressure_snapshots_window_score
    ON client_pressure_snapshots (window_start DESC, pressure_score DESC NULLS LAST);

CREATE TABLE IF NOT EXISTS client_table_pressure_snapshots (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    run_id bigint NOT NULL REFERENCES client_pressure_snapshot_runs(id) ON DELETE CASCADE,
    client_snapshot_id bigint NOT NULL REFERENCES client_pressure_snapshots(id) ON DELETE CASCADE,
    recorded_at timestamptz NOT NULL DEFAULT now(),
    window_start timestamptz NOT NULL,
    window_end timestamptz NOT NULL,
    client_name text NOT NULL,
    table_name text NOT NULL,
    request_count bigint NOT NULL DEFAULT 0,
    failed_requests bigint NOT NULL DEFAULT 0,
    cache_misses bigint NOT NULL DEFAULT 0,
    total_work_ms bigint NOT NULL DEFAULT 0,
    p50_duration_ms double precision,
    p95_duration_ms double precision,
    share_of_client_work double precision,
    error_ratio double precision,
    cache_miss_ratio double precision,
    tail_ratio double precision,
    burst_ratio double precision,
    request_shock double precision,
    error_shock double precision,
    pressure_score double precision,
    volatility_score double precision,
    pressure_band text NOT NULL,
    observed_only boolean NOT NULL DEFAULT true,
    meta jsonb NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT client_table_pressure_snapshots_unique_window_client_table UNIQUE (
        window_start,
        client_name,
        table_name
    ),
    CONSTRAINT client_table_pressure_snapshots_band_check CHECK (
        pressure_band IN ('green', 'yellow', 'red')
    )
);

CREATE INDEX IF NOT EXISTS idx_client_table_pressure_snapshots_client_window
    ON client_table_pressure_snapshots (client_name, window_start DESC);

CREATE INDEX IF NOT EXISTS idx_client_table_pressure_snapshots_window_score
    ON client_table_pressure_snapshots (window_start DESC, pressure_score DESC NULLS LAST);

CREATE TABLE IF NOT EXISTS client_pressure_backfill_requests (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    requested_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    claimed_at timestamptz,
    completed_at timestamptz,
    hours_back integer NOT NULL,
    status text NOT NULL DEFAULT 'pending',
    error_message text,
    formula_version text NOT NULL DEFAULT 'v1',
    meta jsonb NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT client_pressure_backfill_requests_status_check CHECK (
        status IN ('pending', 'processing', 'completed', 'failed')
    ),
    CONSTRAINT client_pressure_backfill_requests_hours_back_check CHECK (hours_back > 0)
);

CREATE INDEX IF NOT EXISTS idx_client_pressure_backfill_requests_status_requested
    ON client_pressure_backfill_requests (status, requested_at);

-- ---------------------------------------------------------------------------
-- Vacuum health (per-target-client snapshots stored in logging DB)
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS vacuum_health_snapshots (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    recorded_at timestamptz NOT NULL DEFAULT now(),
    client_name text NOT NULL,
    host text,
    instance_id uuid,
    total_dead_rows bigint NOT NULL DEFAULT 0,
    tables_with_bloat integer NOT NULL DEFAULT 0,
    xid_freeze_risk integer NOT NULL DEFAULT 0,
    tables_needing_vacuum integer NOT NULL DEFAULT 0,
    freeze_max_age bigint,
    collection_error text
);

CREATE INDEX IF NOT EXISTS idx_vacuum_health_snapshots_client_recorded
    ON vacuum_health_snapshots (client_name, recorded_at DESC);

CREATE TABLE IF NOT EXISTS vacuum_health_table_stats (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    snapshot_id bigint NOT NULL REFERENCES vacuum_health_snapshots(id) ON DELETE CASCADE,
    schemaname text NOT NULL,
    relname text NOT NULL,
    n_dead_tup bigint NOT NULL DEFAULT 0,
    n_live_tup bigint NOT NULL DEFAULT 0,
    dead_pct numeric,
    last_vacuum timestamptz,
    last_autovacuum timestamptz,
    xid_age bigint,
    xid_age_pct_of_freeze_max numeric
);

CREATE INDEX IF NOT EXISTS idx_vacuum_health_table_stats_snapshot
    ON vacuum_health_table_stats (snapshot_id);

-- ---------------------------------------------------------------------------
-- Query history and saved queries
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS public.query_history (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    query TEXT NOT NULL,
    client_name TEXT,
    status TEXT NOT NULL DEFAULT 'success',
    execution_time_ms DOUBLE PRECISION,
    error_message TEXT,
    row_count INTEGER,
    executed_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_query_history_client
    ON public.query_history(client_name);

CREATE INDEX IF NOT EXISTS idx_query_history_executed
    ON public.query_history(executed_at DESC);

CREATE TABLE IF NOT EXISTS public.saved_queries (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    description TEXT,
    query TEXT NOT NULL,
    client_name TEXT,
    schema_name TEXT DEFAULT 'public',
    tags TEXT[] DEFAULT '{}',
    created_by TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_saved_queries_client
    ON public.saved_queries(client_name);

CREATE INDEX IF NOT EXISTS idx_saved_queries_created
    ON public.saved_queries(created_at DESC);

-- ---------------------------------------------------------------------------
-- UI request log (used by the Web Explorer)
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS public.ui_request_log (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    method TEXT NOT NULL,
    path TEXT NOT NULL,
    athena_url TEXT NOT NULL,
    client_name TEXT,
    status_code INTEGER,
    duration_ms DOUBLE PRECISION,
    error_message TEXT,
    request_body JSONB,
    response_preview TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_ui_request_log_created
    ON public.ui_request_log(created_at DESC);

CREATE INDEX IF NOT EXISTS idx_ui_request_log_client
    ON public.ui_request_log(client_name);

CREATE INDEX IF NOT EXISTS idx_ui_request_log_path
    ON public.ui_request_log(path);

-- ---------------------------------------------------------------------------
-- Frontend intake tables (feedback, organization requests, project requests)
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS public.feedback (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    category text NOT NULL DEFAULT 'issue',
    title text,
    message text NOT NULL,
    page text,
    email text,
    client_name text,
    organization_name text,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    status text NOT NULL DEFAULT 'new'
);

CREATE INDEX IF NOT EXISTS idx_feedback_created_at
    ON public.feedback(created_at DESC);

CREATE INDEX IF NOT EXISTS idx_feedback_status
    ON public.feedback(status);

CREATE TABLE IF NOT EXISTS public.organization_requests (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    organization_name text NOT NULL,
    organization_type text,
    plan text,
    requested_by text,
    notes text,
    client_name text,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    status text NOT NULL DEFAULT 'new'
);

CREATE INDEX IF NOT EXISTS idx_organization_requests_created_at
    ON public.organization_requests(created_at DESC);

CREATE INDEX IF NOT EXISTS idx_organization_requests_status
    ON public.organization_requests(status);

CREATE TABLE IF NOT EXISTS public.project_requests (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    organization_name text,
    project_name text NOT NULL,
    region text,
    enable_data_api boolean NOT NULL DEFAULT true,
    enable_automatic_rls boolean NOT NULL DEFAULT false,
    requested_by text,
    client_name text,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    status text NOT NULL DEFAULT 'new'
);

CREATE INDEX IF NOT EXISTS idx_project_requests_created_at
    ON public.project_requests(created_at DESC);

CREATE INDEX IF NOT EXISTS idx_project_requests_status
    ON public.project_requests(status);

-- ---------------------------------------------------------------------------
-- Backup job tracking (used by /admin/backups)
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS backup_jobs (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    job_type text NOT NULL, -- 'backup' | 'restore'
    client_name text,
    status text NOT NULL, -- 'pending' | 'running' | 'completed' | 'failed' | 'cancelled'
    queue_payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    queue_checkpoint jsonb NOT NULL DEFAULT '{}'::jsonb,
    attempt_count integer NOT NULL DEFAULT 0,
    max_attempts integer NOT NULL DEFAULT 3,
    next_attempt_at timestamptz NOT NULL DEFAULT now(),
    lease_token uuid,
    lease_expires_at timestamptz,
    progress_pct integer,
    progress_stage text,
    s3_bucket text,
    s3_key text,
    label text,
    size_bytes bigint,
    error_message text,
    execution_daemon_id text,
    started_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    completed_at timestamptz
);

ALTER TABLE backup_jobs
    ADD COLUMN IF NOT EXISTS execution_daemon_id text;

CREATE INDEX IF NOT EXISTS idx_backup_jobs_client_started_at
    ON backup_jobs (client_name, started_at DESC);

CREATE INDEX IF NOT EXISTS idx_backup_jobs_status
    ON backup_jobs (status);

CREATE INDEX IF NOT EXISTS idx_backup_jobs_queue_due
    ON backup_jobs (status, next_attempt_at);

CREATE INDEX IF NOT EXISTS idx_backup_jobs_execution_daemon
    ON backup_jobs (execution_daemon_id);

CREATE TABLE IF NOT EXISTS backup_job_logs (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    job_id bigint NOT NULL REFERENCES backup_jobs(id) ON DELETE CASCADE,
    level text NOT NULL, -- 'info' | 'warn' | 'error'
    message text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_backup_job_logs_job_created_at
    ON backup_job_logs (job_id, created_at);

-- ── backup_schedules ─────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS backup_schedules (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    client_name text NOT NULL,
    pg_uri text,
    frequency text NOT NULL CHECK (frequency IN ('hourly', 'daily', 'weekly', 'monthly')),
    cron_expression text NOT NULL,
    time_of_day time NOT NULL DEFAULT '02:00',
    day_of_week integer CHECK (day_of_week IS NULL OR (day_of_week >= 0 AND day_of_week <= 6)),
    day_of_month integer CHECK (day_of_month IS NULL OR (day_of_month >= 1 AND day_of_month <= 28)),
    label text,
    enabled boolean NOT NULL DEFAULT true,
    timeout_seconds integer NOT NULL DEFAULT 3600,
    execution_daemon_id text,
    last_run_at timestamptz,
    last_job_id bigint REFERENCES backup_jobs(id),
    next_run_at timestamptz,
    pg_cron_job_id bigint,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE backup_schedules
    ADD COLUMN IF NOT EXISTS execution_daemon_id text;

ALTER TABLE backup_jobs
    ADD COLUMN IF NOT EXISTS schedule_id bigint;

DO $$
DECLARE
    current_delete_action "char";
BEGIN
    IF to_regclass('backup_jobs') IS NULL OR to_regclass('backup_schedules') IS NULL THEN
        RETURN;
    END IF;

    UPDATE backup_jobs AS jobs
    SET schedule_id = NULL,
        updated_at = now()
    WHERE schedule_id IS NOT NULL
      AND NOT EXISTS (
        SELECT 1
        FROM backup_schedules AS schedules
        WHERE schedules.id = jobs.schedule_id
      );

    SELECT confdeltype
      INTO current_delete_action
      FROM pg_constraint
     WHERE conrelid = 'backup_jobs'::regclass
       AND conname = 'backup_jobs_schedule_id_fkey';

    IF current_delete_action IS DISTINCT FROM 'n' THEN
        IF current_delete_action IS NOT NULL THEN
            ALTER TABLE backup_jobs
                DROP CONSTRAINT backup_jobs_schedule_id_fkey;
        END IF;

        ALTER TABLE backup_jobs
            ADD CONSTRAINT backup_jobs_schedule_id_fkey
            FOREIGN KEY (schedule_id)
            REFERENCES backup_schedules(id)
            ON DELETE SET NULL;
    END IF;
END
$$;

CREATE INDEX IF NOT EXISTS idx_backup_schedules_client
    ON backup_schedules (client_name);

CREATE INDEX IF NOT EXISTS idx_backup_schedules_enabled
    ON backup_schedules (enabled) WHERE enabled = true;

CREATE INDEX IF NOT EXISTS idx_backup_schedules_execution_daemon
    ON backup_schedules (execution_daemon_id);

CREATE TABLE IF NOT EXISTS public.daemon_runtime_registry (
    daemon_id text PRIMARY KEY,
    runtime_kind text NOT NULL,
    host text NOT NULL,
    version text NOT NULL,
    workers_json jsonb NOT NULL DEFAULT '[]'::jsonb,
    metadata_json jsonb NOT NULL DEFAULT '{}'::jsonb,
    started_at timestamptz NOT NULL DEFAULT now(),
    last_seen_at timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE public.daemon_runtime_registry
    ADD COLUMN IF NOT EXISTS runtime_kind text NOT NULL DEFAULT 'unknown',
    ADD COLUMN IF NOT EXISTS host text NOT NULL DEFAULT 'unknown-host',
    ADD COLUMN IF NOT EXISTS version text NOT NULL DEFAULT 'unknown',
    ADD COLUMN IF NOT EXISTS workers_json jsonb NOT NULL DEFAULT '[]'::jsonb,
    ADD COLUMN IF NOT EXISTS metadata_json jsonb NOT NULL DEFAULT '{}'::jsonb,
    ADD COLUMN IF NOT EXISTS started_at timestamptz NOT NULL DEFAULT now(),
    ADD COLUMN IF NOT EXISTS last_seen_at timestamptz NOT NULL DEFAULT now();

CREATE INDEX IF NOT EXISTS idx_daemon_runtime_registry_last_seen_at
    ON public.daemon_runtime_registry (last_seen_at DESC);

CREATE INDEX IF NOT EXISTS idx_daemon_runtime_registry_runtime_kind
    ON public.daemon_runtime_registry (runtime_kind);


CREATE TABLE IF NOT EXISTS public.storage_profiles (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    name text NOT NULL,
    provider text NOT NULL DEFAULT 'aws-s3',
    endpoint text NOT NULL,
    region text NOT NULL DEFAULT 'us-east-1',
    bucket text NOT NULL,
    access_key_id text,
    secret_key text,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_storage_profiles_name
    ON public.storage_profiles(name);

CREATE INDEX IF NOT EXISTS idx_storage_profiles_created_at
    ON public.storage_profiles(created_at DESC);

-- ---------------------------------------------------------------------------
-- Typesense integration catalog + sync orchestration
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS public.typesense_profiles (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    name text NOT NULL,
    base_url text NOT NULL,
    api_key text NOT NULL,
    search_api_key text,
    timeout_ms integer NOT NULL DEFAULT 10000,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_typesense_profiles_name
    ON public.typesense_profiles(name);

CREATE UNIQUE INDEX IF NOT EXISTS idx_typesense_profiles_base_url
    ON public.typesense_profiles(base_url);

CREATE INDEX IF NOT EXISTS idx_typesense_profiles_created_at
    ON public.typesense_profiles(created_at DESC);

CREATE TABLE IF NOT EXISTS public.typesense_sync_jobs (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    typesense_sync_job_id uuid NOT NULL DEFAULT gen_random_uuid(),
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    name text NOT NULL,
    enabled boolean NOT NULL DEFAULT true,
    auto_sync_enabled boolean NOT NULL DEFAULT false,
    sync_strategy text NOT NULL DEFAULT 'upsert'
        CHECK (sync_strategy IN ('upsert', 'full')),
    source_client_name text NOT NULL,
    source_schema_name text NOT NULL DEFAULT 'public',
    source_table_name text NOT NULL,
    typesense_profile_id bigint NOT NULL REFERENCES public.typesense_profiles(id) ON DELETE CASCADE,
    collection_name text NOT NULL,
    document_id_column text NOT NULL,
    selected_columns jsonb NOT NULL DEFAULT '[]'::jsonb,
    query_by_fields jsonb NOT NULL DEFAULT '[]'::jsonb,
    facet_by_fields jsonb NOT NULL DEFAULT '[]'::jsonb,
    sort_by_fields jsonb NOT NULL DEFAULT '[]'::jsonb,
    field_config jsonb NOT NULL DEFAULT '{}'::jsonb,
    default_sort_field text,
    batch_size integer NOT NULL DEFAULT 250,
    batch_delay_ms integer NOT NULL DEFAULT 0,
    sync_interval_minutes integer NOT NULL DEFAULT 60,
    stale_after_minutes integer NOT NULL DEFAULT 120,
    execution_daemon_id text,
    last_sync_status text NOT NULL DEFAULT 'idle'
        CHECK (last_sync_status IN ('idle', 'running', 'success', 'failed', 'paused', 'cancelled')),
    last_sync_started_at timestamptz,
    last_sync_finished_at timestamptz,
    last_synced_at timestamptz,
    last_sync_row_count integer,
    last_sync_success_count integer,
    last_sync_error_count integer,
    last_sync_error text,
    next_sync_at timestamptz DEFAULT now(),
    sync_lock_token uuid,
    sync_locked_at timestamptz,
    sync_lock_expires_at timestamptz,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT uq_typesense_sync_jobs_name UNIQUE (name),
    CONSTRAINT uq_typesense_sync_jobs_binding
        UNIQUE (source_client_name, source_schema_name, source_table_name, typesense_profile_id, collection_name),
    CONSTRAINT uq_typesense_sync_jobs_uuid UNIQUE (typesense_sync_job_id)
);

ALTER TABLE public.typesense_sync_jobs
    ADD COLUMN IF NOT EXISTS execution_daemon_id text;

CREATE INDEX IF NOT EXISTS idx_typesense_sync_jobs_due
    ON public.typesense_sync_jobs(enabled, auto_sync_enabled, next_sync_at);

CREATE INDEX IF NOT EXISTS idx_typesense_sync_jobs_source
    ON public.typesense_sync_jobs(source_client_name, source_schema_name, source_table_name);

CREATE INDEX IF NOT EXISTS idx_typesense_sync_jobs_profile
    ON public.typesense_sync_jobs(typesense_profile_id, collection_name);

CREATE INDEX IF NOT EXISTS idx_typesense_sync_jobs_last_synced
    ON public.typesense_sync_jobs(last_synced_at DESC);

CREATE INDEX IF NOT EXISTS idx_typesense_sync_jobs_execution_daemon
    ON public.typesense_sync_jobs(execution_daemon_id);

CREATE TABLE IF NOT EXISTS public.typesense_sync_runs (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    typesense_sync_run_id uuid NOT NULL DEFAULT gen_random_uuid(),
    created_at timestamptz NOT NULL DEFAULT now(),
    job_id bigint NOT NULL REFERENCES public.typesense_sync_jobs(id) ON DELETE CASCADE,
    trigger_source text NOT NULL DEFAULT 'manual'
        CHECK (trigger_source IN ('manual', 'scheduled', 'api')),
    status text NOT NULL
        CHECK (status IN ('running', 'success', 'failed', 'cancelled')),
    started_at timestamptz NOT NULL DEFAULT now(),
    finished_at timestamptz,
    source_row_count integer,
    progress_total_kind text NOT NULL DEFAULT 'unknown'
        CHECK (progress_total_kind IN ('estimated', 'exact', 'unknown')),
    estimated_total_rows integer,
    manifest_total_rows integer,
    manifest_processed_rows integer NOT NULL DEFAULT 0,
    next_manifest_seq bigint NOT NULL DEFAULT 0,
    resume_attempt_count integer NOT NULL DEFAULT 0,
    processed_row_count integer NOT NULL DEFAULT 0,
    imported_count integer,
    failed_count integer,
    deleted_count integer,
    duration_ms bigint,
    progress_pct integer NOT NULL DEFAULT 0
        CHECK (progress_pct >= 0 AND progress_pct <= 100),
    progress_stage text NOT NULL DEFAULT 'queued',
    progress_message text,
    inter_batch_delay_ms integer NOT NULL DEFAULT 0,
    next_batch_at timestamptz,
    batches_completed integer NOT NULL DEFAULT 0,
    retry_attempt_count integer NOT NULL DEFAULT 0,
    rollback_attempted boolean NOT NULL DEFAULT false,
    rollback_status text NOT NULL DEFAULT 'not_needed'
        CHECK (rollback_status IN ('not_needed', 'running', 'succeeded', 'failed')),
    rollback_restored_count integer,
    rollback_deleted_count integer,
    rollback_error_message text,
    error_message text,
    result_preview jsonb NOT NULL DEFAULT '{}'::jsonb,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT uq_typesense_sync_runs_uuid UNIQUE (typesense_sync_run_id)
);

ALTER TABLE public.typesense_sync_runs
    ADD COLUMN IF NOT EXISTS inter_batch_delay_ms integer NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS next_batch_at timestamptz,
    ADD COLUMN IF NOT EXISTS progress_total_kind text NOT NULL DEFAULT 'unknown',
    ADD COLUMN IF NOT EXISTS retry_attempt_count integer NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS manifest_total_rows integer,
    ADD COLUMN IF NOT EXISTS manifest_processed_rows integer NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS next_manifest_seq bigint NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS resume_attempt_count integer NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS rollback_attempted boolean NOT NULL DEFAULT false,
    ADD COLUMN IF NOT EXISTS rollback_status text NOT NULL DEFAULT 'not_needed',
    ADD COLUMN IF NOT EXISTS rollback_restored_count integer,
    ADD COLUMN IF NOT EXISTS rollback_deleted_count integer,
    ADD COLUMN IF NOT EXISTS rollback_error_message text;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'typesense_sync_runs_rollback_status_check'
    ) THEN
        ALTER TABLE public.typesense_sync_runs
            ADD CONSTRAINT typesense_sync_runs_rollback_status_check
            CHECK (rollback_status IN ('not_needed', 'running', 'succeeded', 'failed'));
    END IF;
END
$$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'typesense_sync_runs_progress_total_kind_check'
    ) THEN
        ALTER TABLE public.typesense_sync_runs
            ADD CONSTRAINT typesense_sync_runs_progress_total_kind_check
            CHECK (progress_total_kind IN ('estimated', 'exact', 'unknown'));
    END IF;
END
$$;

CREATE INDEX IF NOT EXISTS idx_typesense_sync_runs_job_started_at
    ON public.typesense_sync_runs(job_id, started_at DESC);

CREATE INDEX IF NOT EXISTS idx_typesense_sync_runs_status
    ON public.typesense_sync_runs(status, started_at DESC);

CREATE TABLE IF NOT EXISTS public.typesense_sync_run_manifest_rows (
    run_id bigint NOT NULL REFERENCES public.typesense_sync_runs(id) ON DELETE CASCADE,
    manifest_seq bigint NOT NULL,
    document_id text NOT NULL,
    row_data jsonb NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (run_id, manifest_seq)
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_typesense_sync_run_manifest_rows_run_document
    ON public.typesense_sync_run_manifest_rows(run_id, document_id);

CREATE INDEX IF NOT EXISTS idx_typesense_sync_run_manifest_rows_run_seq
    ON public.typesense_sync_run_manifest_rows(run_id, manifest_seq);

CREATE OR REPLACE VIEW public.typesense_sync_job_status AS
WITH latest_running_run AS (
    SELECT DISTINCT ON (r.job_id)
        r.job_id,
        r.id AS active_run_id,
        r.status AS active_run_status,
        r.started_at AS active_run_started_at,
        r.progress_total_kind AS active_progress_total_kind,
        r.estimated_total_rows AS active_estimated_total_rows,
        r.manifest_total_rows AS active_manifest_total_rows,
        r.manifest_processed_rows AS active_manifest_processed_rows,
        r.next_manifest_seq AS active_next_manifest_seq,
        r.resume_attempt_count AS active_resume_attempt_count,
        r.processed_row_count AS active_processed_row_count,
        r.imported_count AS active_imported_count,
        r.failed_count AS active_failed_count,
        r.progress_pct AS active_progress_pct,
        r.progress_stage AS active_progress_stage,
        r.progress_message AS active_progress_message,
        r.inter_batch_delay_ms AS active_inter_batch_delay_ms,
        r.next_batch_at AS active_next_batch_at,
        r.batches_completed AS active_batches_completed,
        r.retry_attempt_count AS active_retry_attempt_count,
        r.rollback_attempted AS active_rollback_attempted,
        r.rollback_status AS active_rollback_status,
        r.rollback_restored_count AS active_rollback_restored_count,
        r.rollback_deleted_count AS active_rollback_deleted_count,
        r.rollback_error_message AS active_rollback_error_message
    FROM public.typesense_sync_runs r
    WHERE r.status = 'running'
    ORDER BY r.job_id, r.started_at DESC, r.id DESC
)
SELECT
    j.id,
    j.typesense_sync_job_id,
    j.created_at,
    j.updated_at,
    j.name,
    j.enabled,
    j.auto_sync_enabled,
    j.sync_strategy,
    j.source_client_name,
    j.source_schema_name,
    j.source_table_name,
    j.typesense_profile_id,
    p.name AS profile_name,
    p.base_url AS profile_base_url,
    j.collection_name,
    j.document_id_column,
    j.selected_columns,
    j.query_by_fields,
    j.facet_by_fields,
    j.sort_by_fields,
    j.default_sort_field,
    j.batch_size,
    j.batch_delay_ms,
    j.sync_interval_minutes,
    j.stale_after_minutes,
    j.execution_daemon_id,
    j.last_sync_status,
    j.last_sync_started_at,
    j.last_sync_finished_at,
    j.last_synced_at,
    j.last_sync_row_count,
    j.last_sync_success_count,
    j.last_sync_error_count,
    j.last_sync_error,
    j.next_sync_at,
    j.sync_locked_at,
    j.sync_lock_expires_at,
    rr.active_run_id,
    rr.active_run_status,
    rr.active_run_started_at,
    rr.active_progress_total_kind,
    rr.active_estimated_total_rows,
    rr.active_manifest_total_rows,
    rr.active_manifest_processed_rows,
    rr.active_next_manifest_seq,
    rr.active_resume_attempt_count,
    rr.active_processed_row_count,
    rr.active_imported_count,
    rr.active_failed_count,
    rr.active_progress_pct,
    rr.active_progress_stage,
    rr.active_progress_message,
    rr.active_inter_batch_delay_ms,
    rr.active_next_batch_at,
    rr.active_batches_completed,
    rr.active_retry_attempt_count,
    rr.active_rollback_attempted,
    rr.active_rollback_status,
    rr.active_rollback_restored_count,
    rr.active_rollback_deleted_count,
    rr.active_rollback_error_message,
    CASE
        WHEN NOT j.enabled THEN false
        WHEN j.last_sync_status IN ('failed', 'cancelled') THEN true
        WHEN j.last_synced_at IS NULL THEN true
        ELSE j.last_synced_at < now() - make_interval(mins => GREATEST(j.stale_after_minutes, 1)::int)
    END AS is_stale,
    CASE
        WHEN NOT j.enabled THEN 'disabled'
        WHEN j.last_sync_status = 'failed' THEN 'failed'
        WHEN j.last_sync_status = 'cancelled' THEN 'cancelled'
        WHEN j.last_synced_at IS NULL THEN 'never_synced'
        WHEN j.last_synced_at < now() - make_interval(mins => GREATEST(j.stale_after_minutes, 1)::int) THEN 'overdue'
        ELSE 'fresh'
    END AS sync_health
FROM public.typesense_sync_jobs j
JOIN public.typesense_profiles p
    ON p.id = j.typesense_profile_id
LEFT JOIN latest_running_run rr
    ON rr.job_id = j.id;

-- ---------------------------------------------------------------------------
-- Frontend intake tables (feedback, organization requests, project requests)
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS public.feedback (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    category text NOT NULL DEFAULT 'issue',
    title text,
    message text NOT NULL,
    page text,
    email text,
    client_name text,
    organization_name text,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    status text NOT NULL DEFAULT 'new'
);

CREATE INDEX IF NOT EXISTS idx_feedback_created_at
    ON public.feedback(created_at DESC);

CREATE INDEX IF NOT EXISTS idx_feedback_status
    ON public.feedback(status);

CREATE TABLE IF NOT EXISTS public.organization_requests (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    organization_name text NOT NULL,
    organization_type text,
    plan text,
    requested_by text,
    notes text,
    client_name text,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    status text NOT NULL DEFAULT 'new'
);

CREATE INDEX IF NOT EXISTS idx_organization_requests_created_at
    ON public.organization_requests(created_at DESC);

CREATE INDEX IF NOT EXISTS idx_organization_requests_status
    ON public.organization_requests(status);

CREATE TABLE IF NOT EXISTS public.project_requests (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    organization_name text,
    project_name text NOT NULL,
    region text,
    enable_data_api boolean NOT NULL DEFAULT true,
    enable_automatic_rls boolean NOT NULL DEFAULT false,
    requested_by text,
    client_name text,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    status text NOT NULL DEFAULT 'new'
);

CREATE INDEX IF NOT EXISTS idx_project_requests_created_at
    ON public.project_requests(created_at DESC);

CREATE INDEX IF NOT EXISTS idx_project_requests_status
    ON public.project_requests(status);

-- ---------------------------------------------------------------------------
-- Gateway webhooks (definitions + delivery log)
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS athena.gateway_webhook (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    enabled boolean NOT NULL DEFAULT true,
    name text NOT NULL,
    description text,
    athena_base_url text,
    client_name text NOT NULL,
    table_name text,
    route_key text NOT NULL,
    http_method text NOT NULL,
    url_template text NOT NULL,
    headers_templates jsonb NOT NULL DEFAULT '{}'::jsonb,
    body_template text,
    timeout_ms integer NOT NULL DEFAULT 30000,
    include_request_body_in_context boolean NOT NULL DEFAULT true,
    lookup_keys jsonb NOT NULL DEFAULT '{}'::jsonb,
    slug text,
    athena_base_url_norm text GENERATED ALWAYS AS (COALESCE(athena_base_url, '')) STORED,
    table_name_norm text GENERATED ALWAYS AS (COALESCE(table_name, '')) STORED,
    CONSTRAINT gateway_webhook_route_key_check CHECK (route_key IN (
        'billing_document_upserted',
        'gateway_data', 'gateway_fetch', 'gateway_update', 'gateway_query',
        'gateway_insert', 'gateway_delete',
        'rest_get', 'rest_post', 'rest_patch', 'rest_delete',
        'schema_table_created', 'schema_table_dropped',
        'schema_column_added', 'schema_column_dropped', 'schema_column_renamed', 'schema_column_altered',
        'schema_index_created', 'schema_index_dropped', 'schema_extension_installed'
    )),
    CONSTRAINT gateway_webhook_method_check CHECK (http_method IN (
        'GET', 'POST', 'PUT', 'PATCH', 'DELETE'
    )),
    CONSTRAINT gateway_webhook_timeout_ms_check CHECK (timeout_ms >= 1000 AND timeout_ms <= 300000)
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_gateway_webhook_slug
    ON athena.gateway_webhook (slug)
    WHERE slug IS NOT NULL AND slug <> '';

CREATE UNIQUE INDEX IF NOT EXISTS idx_gateway_webhook_natural_identity
    ON athena.gateway_webhook (
        client_name,
        route_key,
        http_method,
        url_template,
        athena_base_url_norm,
        table_name_norm
    );

CREATE INDEX IF NOT EXISTS idx_gateway_webhook_client_route
    ON athena.gateway_webhook (client_name, route_key, enabled);

CREATE TABLE IF NOT EXISTS athena.gateway_webhook_delivery (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    webhook_id bigint NOT NULL REFERENCES athena.gateway_webhook(id) ON DELETE CASCADE,
    trigger_route_key text NOT NULL,
    request_id text,
    idempotency_key text NOT NULL,
    status text NOT NULL,
    http_status integer,
    response_headers_json jsonb,
    response_body_snippet text,
    error_message text,
    duration_ms bigint,
    resolved_url text,
    context_snapshot jsonb NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT gateway_webhook_delivery_status_check CHECK (status IN (
        'pending', 'success', 'failed', 'skipped'
    )),
    CONSTRAINT gateway_webhook_delivery_idempotency_key_unique UNIQUE (idempotency_key)
);

CREATE INDEX IF NOT EXISTS idx_gateway_webhook_delivery_webhook_created
    ON athena.gateway_webhook_delivery (webhook_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_gateway_webhook_delivery_status_created
    ON athena.gateway_webhook_delivery (status, created_at DESC);

CREATE TABLE IF NOT EXISTS athena.gateway_webhook_sink (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    enabled boolean NOT NULL DEFAULT true,
    name text NOT NULL,
    description text,
    athena_base_url text,
    client_name text NOT NULL,
    table_name text,
    route_key text NOT NULL,
    target_client_name text NOT NULL,
    target_table text NOT NULL,
    target_write_mode text NOT NULL DEFAULT 'insert',
    target_conflict_columns jsonb NOT NULL DEFAULT '[]'::jsonb,
    projection_template jsonb NOT NULL DEFAULT '{}'::jsonb,
    include_request_body_in_context boolean NOT NULL DEFAULT true,
    lookup_keys jsonb NOT NULL DEFAULT '{}'::jsonb,
    slug text,
    state_key_template text,
    state_cap_max_fires integer,
    state_cap_window_seconds integer,
    idempotency_template text,
    state_resolution_mode text NOT NULL DEFAULT 'template',
    state_resolution_query_builder jsonb NOT NULL DEFAULT '{}'::jsonb,
    athena_base_url_norm text GENERATED ALWAYS AS (COALESCE(athena_base_url, '')) STORED,
    table_name_norm text GENERATED ALWAYS AS (COALESCE(table_name, '')) STORED,
    CONSTRAINT gateway_webhook_sink_route_key_check CHECK (route_key IN (
        'billing_document_upserted',
        'gateway_data', 'gateway_fetch', 'gateway_update', 'gateway_query',
        'gateway_insert', 'gateway_delete',
        'rest_get', 'rest_post', 'rest_patch', 'rest_delete',
        'schema_table_created', 'schema_table_dropped',
        'schema_column_added', 'schema_column_dropped', 'schema_column_renamed', 'schema_column_altered',
        'schema_index_created', 'schema_index_dropped', 'schema_extension_installed'
    )),
    CONSTRAINT gateway_webhook_sink_target_table_format_check CHECK (
        target_table ~ '^[A-Za-z0-9_]+\.[A-Za-z0-9_]+$'
    ),
    CONSTRAINT gateway_webhook_sink_target_write_mode_check CHECK (
        target_write_mode IN ('insert', 'upsert')
    ),
    CONSTRAINT gateway_webhook_sink_target_conflict_columns_type_check CHECK (
        jsonb_typeof(target_conflict_columns) = 'array'
    ),
    CONSTRAINT gateway_webhook_sink_state_resolution_mode_check CHECK (
        state_resolution_mode IN ('template', 'gateway_query_builder')
    ),
    CONSTRAINT gateway_webhook_sink_state_cap_nonnegative_check CHECK (
        state_cap_max_fires IS NULL OR state_cap_max_fires > 0
    ),
    CONSTRAINT gateway_webhook_sink_state_window_nonnegative_check CHECK (
        state_cap_window_seconds IS NULL OR state_cap_window_seconds > 0
    ),
    CONSTRAINT gateway_webhook_sink_state_cap_requires_window_check CHECK (
        state_cap_max_fires IS NULL OR state_cap_window_seconds IS NOT NULL
    )
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_gateway_webhook_sink_slug
    ON athena.gateway_webhook_sink (slug)
    WHERE slug IS NOT NULL AND slug <> '';

CREATE UNIQUE INDEX IF NOT EXISTS idx_gateway_webhook_sink_natural_identity
    ON athena.gateway_webhook_sink (
        client_name,
        route_key,
        target_client_name,
        target_table,
        athena_base_url_norm,
        table_name_norm
    );

CREATE INDEX IF NOT EXISTS idx_gateway_webhook_sink_client_route
    ON athena.gateway_webhook_sink (client_name, route_key, enabled);

CREATE INDEX IF NOT EXISTS idx_gateway_webhook_sink_target_client
    ON athena.gateway_webhook_sink (target_client_name, enabled);

CREATE TABLE IF NOT EXISTS athena.gateway_webhook_sink_delivery (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    sink_id bigint NOT NULL REFERENCES athena.gateway_webhook_sink(id) ON DELETE CASCADE,
    trigger_route_key text NOT NULL,
    request_id text,
    idempotency_key text NOT NULL,
    status text NOT NULL,
    inserted_row_count integer,
    error_message text,
    duration_ms bigint,
    target_client_name text NOT NULL,
    target_table text NOT NULL,
    projected_payload jsonb,
    context_snapshot jsonb NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT gateway_webhook_sink_delivery_status_check CHECK (status IN (
        'pending', 'success', 'failed', 'skipped'
    )),
    CONSTRAINT gateway_webhook_sink_delivery_idempotency_key_unique UNIQUE (idempotency_key)
);

CREATE INDEX IF NOT EXISTS idx_gateway_webhook_sink_delivery_sink_created
    ON athena.gateway_webhook_sink_delivery (sink_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_gateway_webhook_sink_delivery_status_created
    ON athena.gateway_webhook_sink_delivery (status, created_at DESC);

CREATE TABLE IF NOT EXISTS athena.gateway_webhook_sink_state_ledger (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    sink_id bigint NOT NULL REFERENCES athena.gateway_webhook_sink(id) ON DELETE CASCADE,
    delivery_id bigint REFERENCES athena.gateway_webhook_sink_delivery(id) ON DELETE SET NULL,
    state_key_hash text NOT NULL,
    state_key_raw text,
    window_started_at timestamptz NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_gateway_webhook_sink_state_ledger_gate_lookup
    ON athena.gateway_webhook_sink_state_ledger (sink_id, state_key_hash, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_gateway_webhook_sink_state_ledger_window_started
    ON athena.gateway_webhook_sink_state_ledger (window_started_at);
