-- Migration 039: Create veeam_rpo_tickets tracking table
-- Tracks one open Autotask ticket per Veeam workstation job for RPO-based alerting

CREATE TABLE IF NOT EXISTS veeam_rpo_tickets (
  id                SERIAL PRIMARY KEY,
  job_instance_uid  TEXT NOT NULL UNIQUE,
  job_name          TEXT NOT NULL,
  org_name          TEXT NOT NULL,
  at_ticket_id      BIGINT,
  at_ticket_number  TEXT,
  priority_level    TEXT NOT NULL DEFAULT 'medium' CHECK (priority_level IN ('medium', 'high', 'critical')),
  hours_overdue     NUMERIC(10,2),
  failure_category  TEXT,
  opened_at         TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  resolved_at       TIMESTAMPTZ,
  last_checked_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_veeam_rpo_tickets_job ON veeam_rpo_tickets(job_instance_uid);
CREATE INDEX IF NOT EXISTS idx_veeam_rpo_tickets_open ON veeam_rpo_tickets(resolved_at) WHERE resolved_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_veeam_rpo_tickets_org ON veeam_rpo_tickets(org_name);