-- Enable UUID generation
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Organizations
CREATE TABLE IF NOT EXISTS org (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  short_name text,
  color text,
  is_active boolean NOT NULL DEFAULT true,
  contact_json jsonb NOT NULL DEFAULT '{}'::jsonb,
  settings_json jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz
);

-- Users
CREATE TABLE IF NOT EXISTS app_user (
  id bigserial PRIMARY KEY,
  username text NOT NULL UNIQUE,
  display_name text,
  org_id uuid NOT NULL REFERENCES org(id),
  role text NOT NULL CHECK (role IN ('admin','el','gf','viewer')),
  password_hash text NOT NULL,
  is_active boolean NOT NULL DEFAULT true,
  last_seen_at timestamptz,
  preferences_json jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz
);
CREATE INDEX IF NOT EXISTS idx_app_user_org ON app_user(org_id);

-- Global settings (branding, privacy, templates, etc.)
CREATE TABLE IF NOT EXISTS app_setting (
  key text PRIMARY KEY,
  value_json jsonb NOT NULL,
  updated_at timestamptz NOT NULL DEFAULT now()
);

-- Incidents
CREATE TABLE IF NOT EXISTS incident (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  title text NOT NULL,
  location_text text,
  status text NOT NULL CHECK (status IN ('active','paused','closed','archived')),
  started_at timestamptz,
  ended_at timestamptz,
  settings_json jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_by_user_id bigint REFERENCES app_user(id),
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz
);

-- Incident participating orgs
CREATE TABLE IF NOT EXISTS incident_org (
  incident_id uuid NOT NULL REFERENCES incident(id) ON DELETE CASCADE,
  org_id uuid NOT NULL REFERENCES org(id),
  role_in_incident text NOT NULL DEFAULT 'participant',
  join_meta_json jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (incident_id, org_id)
);
CREATE INDEX IF NOT EXISTS idx_incident_org_org ON incident_org(org_id);

-- Members directory
CREATE TABLE IF NOT EXISTS member (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id uuid NOT NULL REFERENCES org(id),
  display_name text NOT NULL,
  short_code text,
  capabilities_json jsonb NOT NULL DEFAULT '[]'::jsonb,
  meta_json jsonb NOT NULL DEFAULT '{}'::jsonb,
  is_active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz
);
CREATE INDEX IF NOT EXISTS idx_member_org ON member(org_id);

-- Check-in/out per incident
CREATE TABLE IF NOT EXISTS checkin (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  incident_id uuid NOT NULL REFERENCES incident(id) ON DELETE CASCADE,
  member_id uuid NOT NULL REFERENCES member(id) ON DELETE RESTRICT,
  status text NOT NULL CHECK (status IN ('in','out')),
  checked_in_at timestamptz,
  checked_out_at timestamptz,
  checkout_reason text,
  created_by_user_id bigint REFERENCES app_user(id),
  updated_at timestamptz,
  UNIQUE (incident_id, member_id)
);
CREATE INDEX IF NOT EXISTS idx_checkin_incident ON checkin(incident_id);

-- Teams
CREATE TABLE IF NOT EXISTS team (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  incident_id uuid NOT NULL REFERENCES incident(id) ON DELETE CASCADE,
  org_id uuid NOT NULL REFERENCES org(id),
  name text NOT NULL,
  callsign text,
  status text NOT NULL DEFAULT 'available',
  last_contact_at timestamptz,
  meta_json jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_by_user_id bigint REFERENCES app_user(id),
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz
);
CREATE INDEX IF NOT EXISTS idx_team_incident ON team(incident_id);
CREATE INDEX IF NOT EXISTS idx_team_org ON team(org_id);

-- Team members
CREATE TABLE IF NOT EXISTS team_member (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  team_id uuid NOT NULL REFERENCES team(id) ON DELETE CASCADE,
  member_id uuid NOT NULL REFERENCES member(id) ON DELETE RESTRICT,
  role_in_team text NOT NULL DEFAULT 'member',
  is_lead boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (team_id, member_id)
);
CREATE INDEX IF NOT EXISTS idx_team_member_team ON team_member(team_id);

-- Orders
CREATE TABLE IF NOT EXISTS "order" (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  incident_id uuid NOT NULL REFERENCES incident(id) ON DELETE CASCADE,
  created_by_user_id bigint REFERENCES app_user(id),
  template_key text,
  title text NOT NULL,
  status text NOT NULL CHECK (status IN ('created','offered','accepted','started','paused','completed','canceled')),
  assigned_team_id uuid REFERENCES team(id),
  assigned_org_id uuid NOT NULL REFERENCES org(id),
  priority int NOT NULL DEFAULT 3,
  fields_json jsonb NOT NULL DEFAULT '{}'::jsonb,
  accepted_at timestamptz,
  started_at timestamptz,
  completed_at timestamptz,
  meta_json jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz
);
CREATE INDEX IF NOT EXISTS idx_order_incident ON "order"(incident_id);
CREATE INDEX IF NOT EXISTS idx_order_assigned_org ON "order"(assigned_org_id);

-- Order notes
CREATE TABLE IF NOT EXISTS order_note (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  order_id uuid NOT NULL REFERENCES "order"(id) ON DELETE CASCADE,
  incident_id uuid NOT NULL REFERENCES incident(id) ON DELETE CASCADE,
  author_user_id bigint REFERENCES app_user(id),
  message text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_order_note_order ON order_note(order_id);

-- Transfer requests (order transfer / member loan)
CREATE TABLE IF NOT EXISTS transfer (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  incident_id uuid NOT NULL REFERENCES incident(id) ON DELETE CASCADE,
  type text NOT NULL CHECK (type IN ('order_transfer','member_loan')),
  state text NOT NULL CHECK (state IN ('pending','pending_fast','accepted','rejected','canceled','expired')),
  mode text NOT NULL CHECK (mode IN ('offer','fast')),
  from_org_id uuid NOT NULL REFERENCES org(id),
  to_org_id uuid NOT NULL REFERENCES org(id),
  payload_json jsonb NOT NULL DEFAULT '{}'::jsonb,
  expires_at timestamptz,
  created_by_user_id bigint REFERENCES app_user(id),
  decided_by_user_id bigint REFERENCES app_user(id),
  created_at timestamptz NOT NULL DEFAULT now(),
  decided_at timestamptz
);
CREATE INDEX IF NOT EXISTS idx_transfer_to_org ON transfer(to_org_id);
CREATE INDEX IF NOT EXISTS idx_transfer_incident ON transfer(incident_id);

-- Active member loans (accepted transfers)
CREATE TABLE IF NOT EXISTS member_loan (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  incident_id uuid NOT NULL REFERENCES incident(id) ON DELETE CASCADE,
  member_id uuid NOT NULL REFERENCES member(id) ON DELETE RESTRICT,
  from_org_id uuid NOT NULL REFERENCES org(id),
  to_org_id uuid NOT NULL REFERENCES org(id),
  transfer_id uuid REFERENCES transfer(id),
  status text NOT NULL CHECK (status IN ('active','ended')),
  started_at timestamptz NOT NULL DEFAULT now(),
  ended_at timestamptz,
  meta_json jsonb NOT NULL DEFAULT '{}'::jsonb,
  UNIQUE (incident_id, member_id, to_org_id)
);
CREATE INDEX IF NOT EXISTS idx_member_loan_to_org ON member_loan(to_org_id);

-- Messages
CREATE TABLE IF NOT EXISTS message (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  incident_id uuid NOT NULL REFERENCES incident(id) ON DELETE CASCADE,
  author_user_id bigint NOT NULL REFERENCES app_user(id),
  scope text NOT NULL CHECK (scope IN ('public','org','gfs','all','private')),
  org_id uuid NOT NULL REFERENCES org(id),
  to_org_id uuid REFERENCES org(id),
  to_user_id bigint REFERENCES app_user(id),
  message text NOT NULL,
  mentions_json jsonb NOT NULL DEFAULT '[]'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_message_incident ON message(incident_id);

-- Audit log
CREATE TABLE IF NOT EXISTS audit_log (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  incident_id uuid REFERENCES incident(id) ON DELETE CASCADE,
  actor_user_id bigint REFERENCES app_user(id),
  action text NOT NULL,
  entity_type text,
  entity_id text,
  meta_json jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_audit_incident ON audit_log(incident_id);

-- Live events (SSE)
CREATE TABLE IF NOT EXISTS app_event (
  seq bigserial PRIMARY KEY,
  id uuid NOT NULL UNIQUE DEFAULT gen_random_uuid(),
  incident_id uuid REFERENCES incident(id) ON DELETE CASCADE,
  org_id uuid REFERENCES org(id),
  visibility text NOT NULL CHECK (visibility IN ('all','org','role')),
  type text NOT NULL,
  payload_json jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_event_incident ON app_event(incident_id);
CREATE INDEX IF NOT EXISTS idx_event_org ON app_event(org_id);
