

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_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 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);

-- ---------------------------------------------------------------------------
-- 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);

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;

-- ---------------------------------------------------------------------------
-- 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,
    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_is_active
    ON public_gateway_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'
    progress_pct integer,
    progress_stage text,
    s3_bucket text,
    s3_key text,
    label text,
    size_bytes bigint,
    error_message text,
    started_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    completed_at timestamptz
);

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 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,
    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()
);

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 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')),
    started_at timestamptz NOT NULL DEFAULT now(),
    finished_at timestamptz,
    source_row_count integer,
    estimated_total_rows integer,
    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,
    batches_completed integer NOT NULL DEFAULT 0,
    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)
);

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);

-- ---------------------------------------------------------------------------
-- 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 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 (
        '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 gateway_webhook (slug)
    WHERE slug IS NOT NULL AND slug <> '';

CREATE UNIQUE INDEX IF NOT EXISTS idx_gateway_webhook_natural_identity
    ON 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 gateway_webhook (client_name, route_key, enabled);

CREATE TABLE IF NOT EXISTS 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 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 gateway_webhook_delivery (webhook_id, created_at DESC);

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