OG/db/schema.sql

create type user_role as enum ('admin','approver','editor','staff');
create type sop_status as enum ('draft','submitted','approved','published','archived');
create type lang_code as enum ('en','ms','zh');

create table orgs (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  created_at timestamptz default now()
);

create table departments (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references orgs(id),
  name text not null,
  unique (org_id, name)
);

create table profiles (
  id uuid primary key references auth.users(id) on delete cascade,
  org_id uuid not null references orgs(id),
  full_name text not null,
  role user_role not null default 'staff',
  department_id uuid references departments(id),
  preferred_language lang_code not null default 'en',
  active boolean not null default true,
  created_at timestamptz default now()
);

create table sops (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references orgs(id),
  code text not null,                 -- e.g. WH-PICK-001
  title text not null,
  department_id uuid references departments(id),
  category text,
  owner_id uuid references profiles(id),
  status sop_status not null default 'draft',
  review_months int not null default 12 check (review_months in (3,6,12,24)),
  draft_content jsonb not null default '{}'::jsonb,  -- working copy (sections, steps)
  current_version_id uuid,            -- FK added after sop_versions exists
  published_at timestamptz,
  created_by uuid references profiles(id),
  created_at timestamptz default now(),
  updated_at timestamptz default now(),
  unique (org_id, code)
);

create table sop_versions (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references orgs(id),
  sop_id uuid not null references sops(id) on delete cascade,
  version_label text not null,        -- '1.0', '1.1', '2.0'
  content jsonb not null,             -- FROZEN snapshot of sections at publish
  change_note text,
  is_major boolean not null default false,
  published_by uuid references profiles(id),
  published_at timestamptz default now(),
  unique (sop_id, version_label)
);

alter table sops
  add constraint fk_current_version
  foreign key (current_version_id) references sop_versions(id);

create table approvals (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references orgs(id),
  sop_id uuid not null references sops(id) on delete cascade,
  decision text not null check (decision in ('approved','rejected')),
  comment text,
  decided_by uuid not null references profiles(id),
  decided_at timestamptz default now()
);

create table sop_assignments (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references orgs(id),
  sop_id uuid not null references sops(id) on delete cascade,
  department_id uuid not null references departments(id),
  assigned_by uuid references profiles(id),
  assigned_at timestamptz default now(),
  unique (sop_id, department_id)
);

create table acknowledgements (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references orgs(id),
  sop_version_id uuid not null references sop_versions(id),
  user_id uuid not null references profiles(id),
  language_viewed lang_code not null,
  typed_name text not null,           -- the "signature"
  acknowledged_at timestamptz default now(),
  unique (sop_version_id, user_id)    -- one ack per user per version
);

create table sop_translations (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references orgs(id),
  sop_version_id uuid not null references sop_versions(id) on delete cascade,
  language lang_code not null,
  content jsonb not null,
  machine boolean not null default true,
  reviewed_by uuid references profiles(id),
  reviewed_at timestamptz,
  created_at timestamptz default now(),
  unique (sop_version_id, language)
);

create table audit_log (
  id bigint generated always as identity primary key,
  org_id uuid not null references orgs(id),
  actor_id uuid references profiles(id),
  action text not null,               -- 'sop.published', 'ack.recorded', ...
  entity_type text not null,          -- 'sop','user','acknowledgement'
  entity_id uuid,
  detail jsonb,
  created_at timestamptz default now()
);
-- Append-only: grant INSERT/SELECT; never UPDATE/DELETE (enforced by grants + no policy).

create table ai_log (
  id bigint generated always as identity primary key,
  org_id uuid not null references orgs(id),
  user_id uuid references profiles(id),
  kind text not null check (kind in ('draft','translate')),
  input_chars int, output_chars int,
  input_tokens int, output_tokens int,
  model text, success boolean, error text,
  created_at timestamptz default now()
);

create type incident_severity as enum ('low','medium','high');
create type incident_status as enum ('open','reviewed','closed');

create table incidents (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references orgs(id),
  reporter_id uuid not null references profiles(id),
  department_id uuid references departments(id),   -- copied from reporter at insert
  sop_id uuid references sops(id),                 -- optional link
  sop_version_id uuid references sop_versions(id), -- optional, set when reported from viewer
  description text not null,
  severity incident_severity not null default 'medium',
  photo_path text,                                 -- Storage path, optional
  status incident_status not null default 'open',
  resolution_note text,
  reviewed_by uuid references profiles(id),
  closed_at timestamptz,
  created_at timestamptz default now()
);

create index idx_incidents_status on incidents(org_id, status, severity);
create index idx_incidents_sop on incidents(sop_id);

create index idx_sops_org_status on sops(org_id, status);
create index idx_ack_version on acknowledgements(sop_version_id);
create index idx_audit_entity on audit_log(entity_type, entity_id);