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