-- SHARED TEAM DATA MODEL
-- All users in the same organization share one row per table (org_id).
-- The org_id is the Supabase user ID of whoever first set up the workspace.
-- Team members set org_id in their profile metadata at signup.

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_opportunities (org_id text primary key, payload text, updated_at timestamptz);
create table if not exists crm_customers     (org_id text primary key, payload text, updated_at timestamptz);
create table if not exists crm_funding       (org_id text primary key, payload text, updated_at timestamptz);
create table if not exists crm_expansions    (org_id text primary key, payload text, updated_at timestamptz);
create table if not exists crm_contacts      (org_id text primary key, payload text, updated_at timestamptz);
create table if not exists crm_activity_log  (org_id text primary key, payload text, updated_at timestamptz);

-- Enable Row Level Security
alter table crm_opportunities enable row level security;
alter table crm_customers     enable row level security;
alter table crm_funding       enable row level security;
alter table crm_expansions    enable row level security;
alter table crm_contacts      enable row level security;
alter table crm_activity_log  enable row level security;

-- Team members share data if they have the same org_id in their metadata
create or replace function get_org_id() returns text language sql stable as $$
  select coalesce(
    (auth.jwt() -> 'user_metadata' ->> 'org_id'),
    auth.uid()::text
  );
$$;

create policy "team data" on crm_opportunities for all using (org_id = get_org_id());
create policy "team data" on crm_customers     for all using (org_id = get_org_id());
create policy "team data" on crm_funding       for all using (org_id = get_org_id());
create policy "team data" on crm_expansions    for all using (org_id = get_org_id());
create policy "team data" on crm_contacts      for all using (org_id = get_org_id());
create policy "team data" on crm_activity_log  for all using (org_id = get_org_id());
create policy "own data" on crm_customers     for all using (auth.uid() = user_id);
create policy "own data" on crm_funding       for all using (auth.uid() = user_id);
create policy "own data" on crm_expansions    for all using (auth.uid() = user_id);
create policy "own data" on crm_contacts      for all using (auth.uid() = user_id);