CREATE TABLE IF NOT EXISTS veeam_ticket_analysis (
  id                        SERIAL PRIMARY KEY,
  ticket_number             TEXT NOT NULL UNIQUE,
  ticket_id                 BIGINT,
  company_id                BIGINT,
  company_name              TEXT,
  device_hostname           TEXT,
  ticket_created_at         TIMESTAMPTZ,
  ticket_closed_at          TIMESTAMPTZ,
  same_day_close            BOOLEAN NOT NULL DEFAULT false,
  hours_worked              NUMERIC(10,2) NOT NULL DEFAULT 0,
  note_count                INTEGER NOT NULL DEFAULT 0,
  -- LLM classification
  problem_category          TEXT NOT NULL,
  resolution_type           TEXT NOT NULL,
  skills_required           TEXT[] NOT NULL DEFAULT '{}',
  complexity                TEXT NOT NULL,
  device_was_offline        BOOLEAN,
  backup_completed_before_tech BOOLEAN,
  preventable               BOOLEAN,
  work_summary              TEXT,
  recommended_procedure     TEXT,
  -- meta
  model                     TEXT,
  analyzed_at               TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_vta_ticket_number ON veeam_ticket_analysis(ticket_number);
CREATE INDEX IF NOT EXISTS idx_vta_category     ON veeam_ticket_analysis(problem_category);
CREATE INDEX IF NOT EXISTS idx_vta_company      ON veeam_ticket_analysis(company_id);
CREATE INDEX IF NOT EXISTS idx_vta_created      ON veeam_ticket_analysis(ticket_created_at);