-- Migration: add backup_schedules table and enhance backup_jobs with timeout support.
-- Target: athena_logging database.
-- Safe to re-run; all objects use IF NOT EXISTS / IF NOT EXISTS checks.

-- ── backup_schedules: persisted cron-style 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;

-- ── enhance backup_jobs with timeout and pg_uri support ──────────────────────

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_name = 'backup_jobs' AND column_name = 'timeout_seconds'
    ) THEN
        ALTER TABLE backup_jobs ADD COLUMN timeout_seconds integer DEFAULT 3600;
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_name = 'backup_jobs' AND column_name = 'pg_uri'
    ) THEN
        ALTER TABLE backup_jobs ADD COLUMN pg_uri text;
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_name = 'backup_jobs' AND column_name = 'schedule_id'
    ) THEN
        ALTER TABLE backup_jobs ADD COLUMN schedule_id bigint REFERENCES backup_schedules(id);
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_name = 'backup_jobs' AND column_name = 'timed_out'
    ) THEN
        ALTER TABLE backup_jobs ADD COLUMN timed_out boolean DEFAULT false;
    END IF;
END
$$;

-- ── function to mark timed-out jobs ──────────────────────────────────────────

CREATE OR REPLACE FUNCTION check_backup_job_timeouts() RETURNS void AS $$
BEGIN
    UPDATE backup_jobs
    SET status = 'failed',
        timed_out = true,
        error_message = 'Job timed out after ' || timeout_seconds || ' seconds',
        completed_at = now(),
        updated_at = now()
    WHERE status = 'running'
      AND timeout_seconds IS NOT NULL
      AND started_at + (timeout_seconds || ' seconds')::interval < now();
END;
$$ LANGUAGE plpgsql;
