-- PER-RECORD TEAM DATA MODEL (v2)
-- Every deal, customer, contact, grant, and activity entry is its
-- own row, so teammates can edit different records at the same time
-- without overwriting each other. Conflicts on the same record
-- resolve by newest edit. Deletes are soft (tombstones) so they
-- propagate correctly across devices.

create table if not exists crm_org_settings (
  org_id text primary key,
  org_name text,
  created_at timestamptz default now()
);

create table if not exists crm_records (
  org_id     text not null,
  collection text not null,   -- 'gov_opps', 'gov_customers', ...
  id         text not null,   -- the record's own id
  data       jsonb not null,
  updated_at timestamptz not null default now(),
  updated_by text,
  deleted    boolean not null default false,
  primary key (org_id, collection, id)
);
create index if not exists crm_records_org_coll
  on crm_records (org_id, collection);

alter table crm_records      enable row level security;
alter table crm_org_settings enable row level security;

-- TEAM MEMBERSHIP — authorization lives in this table, NEVER in
-- user_metadata (which users can edit themselves).
create table if not exists org_members (
  org_id       text not null,
  user_id      uuid not null,
  member_email text,
  added_by     uuid,
  created_at   timestamptz not null default now(),
  primary key (org_id, user_id)
);
create index if not exists org_members_user on org_members (user_id);
alter table org_members enable row level security;
create policy "member sees own, owner sees roster" on org_members
  for select using (user_id = auth.uid() or org_id = auth.uid()::text);
create policy "owner adds members" on org_members
  for insert with check (org_id = auth.uid()::text);
create policy "owner removes, member leaves" on org_members
  for delete using (org_id = auth.uid()::text or user_id = auth.uid());

-- Authorization: the owner is the user whose id IS the org id;
-- members resolve to the org they were added to.
create or replace function get_org_id() returns text
language sql stable security definer set search_path = public as $$
  select coalesce(
    (select org_id from org_members where user_id = auth.uid()
      order by created_at asc limit 1),
    auth.uid()::text
  );
$$;

create policy "team read"   on crm_records for select using (org_id = get_org_id());
create policy "team insert" on crm_records for insert with check (org_id = get_org_id());
create policy "team update" on crm_records for update
  using (org_id = get_org_id()) with check (org_id = get_org_id());
-- No delete policy on purpose: deletes are soft (deleted = true).

create policy "org settings" on crm_org_settings for all
  using (org_id = get_org_id()) with check (org_id = get_org_id());

-- OPTIONAL but recommended: live sync between teammates' screens
alter publication supabase_realtime add table crm_records;

-- UPGRADING from the old one-blob-per-table setup? Just run the
-- statements above. Leave your old crm_opportunities / crm_customers
-- / etc. tables alone — CaptureOS reads them once on your next login
-- and migrates everything into crm_records automatically. If you had
-- teammates set up the old way (user_metadata.org_id), also run
-- captureos-org-security-fix.sql to carry their memberships over.