-- Active: 1772601349048@@metro.proxy.rlwy.net@13239@railway
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 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,
    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 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 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)
);

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

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

INSERT INTO
    client_statistics (
        client_name,
        total_requests,
        successful_requests,
        failed_requests,
        total_cached_requests,
        total_operations,
        last_request_at,
        last_operation_at
    )
SELECT request_stats.client_name, request_stats.total_requests, request_stats.successful_requests, request_stats.failed_requests, request_stats.total_cached_requests, COALESCE(
        operation_stats.total_operations, 0
    ), request_stats.last_request_at, operation_stats.last_operation_at
FROM (
        SELECT
            client AS client_name,
            COUNT(*) AS total_requests,
            COUNT(*) FILTER (
                WHERE
                    status_code >= 200
                    AND status_code < 400
            ) AS successful_requests,
            COUNT(*) FILTER (
                WHERE
                    status_code >= 400
            ) AS failed_requests,
            COUNT(*) FILTER (
                WHERE
                    cached IS TRUE
            ) AS total_cached_requests,
            MAX(created_at) AS last_request_at
        FROM gateway_request_log
        WHERE
            client IS NOT NULL
            AND client <> ''
        GROUP BY
            client
    ) AS request_stats
    LEFT JOIN (
        SELECT
            client AS client_name,
            COUNT(*) AS total_operations,
            MAX(created_at) AS last_operation_at
        FROM gateway_operation_log
        WHERE
            client IS NOT NULL
            AND client <> ''
        GROUP BY
            client
    ) AS operation_stats ON operation_stats.client_name = request_stats.client_name
ON CONFLICT (client_name) 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,
    total_operations = EXCLUDED.total_operations,
    last_request_at = EXCLUDED.last_request_at,
    last_operation_at = EXCLUDED.last_operation_at,
    updated_at = now();

INSERT INTO
    client_table_statistics (
        client_name,
        table_name,
        operation,
        total_operations,
        error_operations,
        last_operation_at
    )
SELECT
    client,
    table_name,
    operation,
    COUNT(*) AS total_operations,
    COUNT(*) FILTER (
        WHERE
            error IS TRUE
            OR status_code >= 400
    ) AS error_operations,
    MAX(created_at) AS last_operation_at
FROM gateway_operation_log
WHERE
    client IS NOT NULL
    AND client <> ''
    AND table_name IS NOT NULL
    AND table_name <> ''
    AND operation IS NOT NULL
    AND operation <> ''
GROUP BY
    client,
    table_name,
    operation
ON CONFLICT (
    client_name,
    table_name,
    operation
) DO
UPDATE
SET
    total_operations = EXCLUDED.total_operations,
    error_operations = EXCLUDED.error_operations,
    last_operation_at = EXCLUDED.last_operation_at,
    updated_at = now();
