-- Active: 1772601349048@@metro.proxy.rlwy.net@13239@railway
-- Adds persistence tables for query optimization recommendations.

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