-- Capabilities catalog (global and org-specific)
CREATE TABLE IF NOT EXISTS capability (
  id bigserial PRIMARY KEY,
  org_id uuid REFERENCES org(id) ON DELETE CASCADE,
  key text NOT NULL,
  label text NOT NULL,
  description text,
  color text,
  sort_order int NOT NULL DEFAULT 100,
  is_active boolean NOT NULL DEFAULT true,
  meta_json jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz
);

-- unique per scope
CREATE UNIQUE INDEX IF NOT EXISTS uq_capability_scope_key
  ON capability(org_id, key);

CREATE INDEX IF NOT EXISTS idx_capability_org ON capability(org_id);

-- Member capabilities (normalized)
CREATE TABLE IF NOT EXISTS member_capability (
  member_id uuid NOT NULL REFERENCES member(id) ON DELETE CASCADE,
  capability_id bigint NOT NULL REFERENCES capability(id) ON DELETE RESTRICT,
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (member_id, capability_id)
);

CREATE INDEX IF NOT EXISTS idx_member_capability_cap ON member_capability(capability_id);
