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

DO $$
DECLARE
    current_delete_action "char";
BEGIN
    IF to_regclass('backup_jobs') IS NULL OR to_regclass('backup_schedules') IS NULL THEN
        RETURN;
    END IF;

    UPDATE backup_jobs AS jobs
    SET schedule_id = NULL,
        updated_at = now()
    WHERE schedule_id IS NOT NULL
      AND NOT EXISTS (
        SELECT 1
        FROM backup_schedules AS schedules
        WHERE schedules.id = jobs.schedule_id
      );

    SELECT confdeltype
      INTO current_delete_action
      FROM pg_constraint
     WHERE conrelid = 'backup_jobs'::regclass
       AND conname = 'backup_jobs_schedule_id_fkey';

    IF current_delete_action IS DISTINCT FROM 'n' THEN
        IF current_delete_action IS NOT NULL THEN
            ALTER TABLE backup_jobs
                DROP CONSTRAINT backup_jobs_schedule_id_fkey;
        END IF;

        ALTER TABLE backup_jobs
            ADD CONSTRAINT backup_jobs_schedule_id_fkey
            FOREIGN KEY (schedule_id)
            REFERENCES backup_schedules(id)
            ON DELETE SET NULL;
    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;
