-- Migration: add backup job tracking tables for backups and restores.
-- Target: athena_logging database (same as other migrations in this folder).
-- Safe to re-run; all objects use IF NOT EXISTS.

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