ims/supabase/migrations/20260709000005_capa_dosh.sql

-- supabase/migrations/20260709000005_capa_dosh.sql
CREATE TYPE capa_priority AS ENUM ('low', 'med', 'high');

CREATE TYPE capa_status AS ENUM (
  'open', 'in_progress', 'overdue', 'pending_verification',
  'verified', 'reopened', 'closed'
);

CREATE TABLE capa_actions (
  id                       UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  incident_id              UUID NOT NULL REFERENCES incidents(id) ON DELETE RESTRICT,
  root_cause_ref           TEXT,
  description              TEXT NOT NULL,
  owner_user_id            UUID NOT NULL REFERENCES users(id),
  department               TEXT NOT NULL,
  due_date                 DATE NOT NULL,
  priority                 capa_priority NOT NULL DEFAULT 'med',
  status                   capa_status NOT NULL DEFAULT 'open',
  completed_at             TIMESTAMPTZ,
  verified_by              UUID REFERENCES users(id),
  verified_at              TIMESTAMPTZ,
  effectiveness_recheck_date DATE,
  created_at               TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TYPE dosh_form_type AS ENUM ('jkkp6', 'jkkp7', 'jkkp8');
CREATE TYPE dosh_status    AS ENUM ('not_required', 'pending', 'submitted');

CREATE TABLE dosh_reports (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  incident_id  UUID NOT NULL REFERENCES incidents(id) ON DELETE RESTRICT,
  form_type    dosh_form_type NOT NULL,
  status       dosh_status NOT NULL DEFAULT 'not_required',
  submitted_at TIMESTAMPTZ,
  submitted_by UUID REFERENCES users(id),
  file_url     TEXT,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX capa_actions_incident_idx  ON capa_actions(incident_id);
CREATE INDEX capa_actions_owner_idx     ON capa_actions(owner_user_id);
CREATE INDEX capa_actions_status_idx    ON capa_actions(status);
CREATE INDEX capa_actions_due_date_idx  ON capa_actions(due_date);