CREATE TABLE IF NOT EXISTS voc.workspace_member (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  workspace_id bigint NOT NULL REFERENCES voc.workspace(id) ON DELETE CASCADE,
  user_external_id text NOT NULL,
  email text NOT NULL DEFAULT '',
  display_name text NOT NULL DEFAULT '',
  role text NOT NULL DEFAULT 'viewer' CHECK (role IN ('owner', 'admin', 'analyst', 'viewer')),
  status text NOT NULL DEFAULT 'active' CHECK (status IN ('invited', 'active', 'disabled')),
  invited_at timestamptz,
  last_seen_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (workspace_id, user_external_id)
);

CREATE INDEX IF NOT EXISTS workspace_member_user_active_idx
  ON voc.workspace_member (user_external_id, workspace_id)
  WHERE status = 'active';
CREATE INDEX IF NOT EXISTS workspace_member_workspace_role_idx
  ON voc.workspace_member (workspace_id, role, id);

CREATE TABLE IF NOT EXISTS voc.analysis_run (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  public_id text NOT NULL UNIQUE,
  workspace_id bigint NOT NULL REFERENCES voc.workspace(id) ON DELETE CASCADE,
  analysis_type text NOT NULL CHECK (analysis_type IN ('voice', 'pain_point', 'feature', 'scenario', 'risk', 'report')),
  target_kind text NOT NULL DEFAULT 'workspace' CHECK (target_kind IN ('workspace', 'category', 'product')),
  target_key text NOT NULL DEFAULT '',
  status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'completed', 'partial', 'failed', 'cancelled')),
  input jsonb NOT NULL DEFAULT '{}'::jsonb,
  result jsonb,
  evidence_count integer NOT NULL DEFAULT 0 CHECK (evidence_count >= 0),
  requested_by_external_id text NOT NULL,
  error_summary text,
  requested_at timestamptz NOT NULL DEFAULT now(),
  started_at timestamptz,
  completed_at timestamptz,
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS analysis_run_workspace_status_requested_idx
  ON voc.analysis_run (workspace_id, status, requested_at DESC, id DESC);
CREATE INDEX IF NOT EXISTS analysis_run_workspace_target_idx
  ON voc.analysis_run (workspace_id, target_kind, target_key, requested_at DESC, id DESC);

CREATE TABLE IF NOT EXISTS voc.action_item (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  public_id text NOT NULL UNIQUE,
  workspace_id bigint NOT NULL REFERENCES voc.workspace(id) ON DELETE CASCADE,
  source_analysis_id bigint REFERENCES voc.analysis_run(id) ON DELETE SET NULL,
  action_type text NOT NULL DEFAULT 'general' CHECK (action_type IN ('general', 'experience', 'product', 'strategy', 'data_quality')),
  title text NOT NULL,
  description text NOT NULL DEFAULT '',
  priority text NOT NULL DEFAULT 'medium' CHECK (priority IN ('critical', 'high', 'medium', 'low')),
  status text NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'planned', 'in_progress', 'blocked', 'completed', 'cancelled')),
  product_key text,
  assignee_external_id text,
  due_at timestamptz,
  created_by_external_id text NOT NULL,
  completed_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS action_item_workspace_status_priority_idx
  ON voc.action_item (workspace_id, status, priority, id DESC);
CREATE INDEX IF NOT EXISTS action_item_workspace_assignee_open_idx
  ON voc.action_item (workspace_id, assignee_external_id, id DESC)
  WHERE status IN ('open', 'planned', 'in_progress', 'blocked');
CREATE INDEX IF NOT EXISTS action_item_source_analysis_idx
  ON voc.action_item (source_analysis_id)
  WHERE source_analysis_id IS NOT NULL;

CREATE TABLE IF NOT EXISTS voc.alert (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  public_id text NOT NULL UNIQUE,
  workspace_id bigint NOT NULL REFERENCES voc.workspace(id) ON DELETE CASCADE,
  alert_type text NOT NULL DEFAULT 'voc_risk' CHECK (alert_type IN ('voc_risk', 'sentiment_spike', 'data_quality', 'sync_failure', 'quota')),
  severity text NOT NULL DEFAULT 'medium' CHECK (severity IN ('critical', 'high', 'medium', 'low')),
  status text NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'acknowledged', 'resolved', 'dismissed')),
  product_key text,
  title text NOT NULL,
  summary text NOT NULL DEFAULT '',
  evidence jsonb NOT NULL DEFAULT '[]'::jsonb,
  detected_at timestamptz NOT NULL DEFAULT now(),
  acknowledged_by_external_id text,
  acknowledged_at timestamptz,
  resolved_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS alert_workspace_status_detected_idx
  ON voc.alert (workspace_id, status, detected_at DESC, id DESC);
CREATE INDEX IF NOT EXISTS alert_workspace_product_open_idx
  ON voc.alert (workspace_id, product_key, detected_at DESC, id DESC)
  WHERE status IN ('open', 'acknowledged');

CREATE TABLE IF NOT EXISTS voc.audit_log (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  workspace_id bigint NOT NULL REFERENCES voc.workspace(id) ON DELETE CASCADE,
  actor_external_id text NOT NULL,
  action text NOT NULL,
  entity_type text NOT NULL,
  entity_public_id text,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS audit_log_workspace_created_idx
  ON voc.audit_log (workspace_id, created_at DESC, id DESC);
CREATE INDEX IF NOT EXISTS audit_log_workspace_entity_idx
  ON voc.audit_log (workspace_id, entity_type, entity_public_id, id DESC)
  WHERE entity_public_id IS NOT NULL;