-- Athena provisioning script.
--
-- Applies all Athena schema tables to a fresh (or existing) Postgres 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.sql
--   athena_rs provision --uri "$DATABASE_URL"

-- ---------------------------------------------------------------------------
-- 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,
    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,
    time numeric NOT NULL,
    PRIMARY KEY (id)
);

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

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

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

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

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

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

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

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

-- ---------------------------------------------------------------------------
-- Storage profiles for S3-compatible object storage
-- ---------------------------------------------------------------------------
