-- Row-level security policies (ADR-003 / §4.5).
-- Applied after drizzle migrations by src/migrate.ts. Idempotent: safe to
-- re-run on every deploy. Tenant scope is read from `app.current_workspace_id`,
-- set via SET LOCAL / set_config inside every tenant-scoped transaction
-- (see src/tenant.ts) — never from client input at query time.
--
-- GUC values are text; cast to integer when comparing to integer PK/FK columns.

alter table workspaces enable row level security;
alter table workspaces force row level security;
drop policy if exists tenant_isolation on workspaces;
create policy tenant_isolation on workspaces
  using (id = nullif(current_setting('app.current_workspace_id', true), '')::integer);
-- Second permissive policy (OR-combined with the one above): lets a user
-- list every workspace they belong to in one query — the "my workspaces"
-- switcher (§2.7) inherently spans tenants from the user's own point of
-- view. Scoped to current_user_id, which comes from the verified JWT, never
-- from client-supplied input, so this does not reopen the cross-tenant gap.
drop policy if exists self_membership on workspaces;
create policy self_membership on workspaces
  using (
    id in (
      select workspace_id from workspace_members
      where user_id = nullif(current_setting('app.current_user_id', true), '')::integer
    )
  );
-- Pending invitees can read the workspace they were invited to (name/slug
-- for the accept inbox) before membership exists.
drop policy if exists invitee_workspace on workspaces;
create policy invitee_workspace on workspaces
  using (
    id in (
      select i.workspace_id from invites i
      where i.email is not null
        and lower(i.email) = (
          select lower(u.email) from users u
          where u.id = nullif(current_setting('app.current_user_id', true), '')::integer
        )
        and i.used_at is null
        and i.expires_at > now()
        and i.use_count < i.max_uses
    )
  );
-- Workspace creation cannot pre-assign an identity PK, so inserts are allowed
-- for any authenticated user; membership is written in the same transaction.
drop policy if exists workspace_create on workspaces;
create policy workspace_create on workspaces
  for insert
  with check (
    nullif(current_setting('app.current_user_id', true), '') is not null
  );

alter table workspace_members enable row level security;
alter table workspace_members force row level security;
drop policy if exists tenant_isolation on workspace_members;
create policy tenant_isolation on workspace_members
  using (workspace_id = nullif(current_setting('app.current_workspace_id', true), '')::integer);
drop policy if exists self_membership on workspace_members;
create policy self_membership on workspace_members
  using (user_id = nullif(current_setting('app.current_user_id', true), '')::integer);

alter table scheduled_statuses enable row level security;
alter table scheduled_statuses force row level security;
drop policy if exists tenant_isolation on scheduled_statuses;
create policy tenant_isolation on scheduled_statuses
  using (workspace_id = nullif(current_setting('app.current_workspace_id', true), '')::integer);

alter table channels enable row level security;
alter table channels force row level security;
drop policy if exists tenant_isolation on channels;
create policy tenant_isolation on channels
  using (workspace_id = nullif(current_setting('app.current_workspace_id', true), '')::integer);

alter table channel_members enable row level security;
alter table channel_members force row level security;
drop policy if exists tenant_isolation on channel_members;
create policy tenant_isolation on channel_members
  using (
    channel_id in (
      select id from channels where workspace_id = nullif(current_setting('app.current_workspace_id', true), '')::integer
    )
  );

-- channel_integrations carries no workspace_id of its own, so it inherits
-- scope through its channel — same subquery shape as channel_members above.
alter table channel_integrations enable row level security;
alter table channel_integrations force row level security;
drop policy if exists tenant_isolation on channel_integrations;
create policy tenant_isolation on channel_integrations
  using (
    channel_id in (
      select id from channels where workspace_id = nullif(current_setting('app.current_workspace_id', true), '')::integer
    )
  );

alter table channel_seq enable row level security;
alter table channel_seq force row level security;
drop policy if exists tenant_isolation on channel_seq;
create policy tenant_isolation on channel_seq
  using (
    channel_id in (
      select id from channels where workspace_id = nullif(current_setting('app.current_workspace_id', true), '')::integer
    )
  );

alter table messages enable row level security;
alter table messages force row level security;
drop policy if exists tenant_isolation on messages;
create policy tenant_isolation on messages
  using (workspace_id = nullif(current_setting('app.current_workspace_id', true), '')::integer);

alter table attachments enable row level security;
alter table attachments force row level security;
drop policy if exists tenant_isolation on attachments;
create policy tenant_isolation on attachments
  using (workspace_id = nullif(current_setting('app.current_workspace_id', true), '')::integer);

alter table thread_subscriptions enable row level security;
alter table thread_subscriptions force row level security;
drop policy if exists tenant_isolation on thread_subscriptions;
create policy tenant_isolation on thread_subscriptions
  using (workspace_id = nullif(current_setting('app.current_workspace_id', true), '')::integer);

alter table reactions enable row level security;
alter table reactions force row level security;
drop policy if exists tenant_isolation on reactions;
create policy tenant_isolation on reactions
  using (
    message_id in (
      select id from messages where workspace_id = nullif(current_setting('app.current_workspace_id', true), '')::integer
    )
  );

alter table message_mentions enable row level security;
alter table message_mentions force row level security;
drop policy if exists tenant_isolation on message_mentions;
create policy tenant_isolation on message_mentions
  using (
    message_id in (
      select id from messages where workspace_id = nullif(current_setting('app.current_workspace_id', true), '')::integer
    )
  );

alter table pins enable row level security;
alter table pins force row level security;
drop policy if exists tenant_isolation on pins;
create policy tenant_isolation on pins
  using (
    channel_id in (
      select id from channels where workspace_id = nullif(current_setting('app.current_workspace_id', true), '')::integer
    )
  );

alter table tasks enable row level security;
alter table tasks force row level security;
drop policy if exists tenant_isolation on tasks;
create policy tenant_isolation on tasks
  using (workspace_id = nullif(current_setting('app.current_workspace_id', true), '')::integer);

alter table events enable row level security;
alter table events force row level security;
drop policy if exists tenant_isolation on events;
create policy tenant_isolation on events
  using (workspace_id = nullif(current_setting('app.current_workspace_id', true), '')::integer);

-- event_attendees carries no workspace_id of its own, so it inherits scope
-- through its event — the same subquery shape the pins policy above uses.
alter table event_attendees enable row level security;
alter table event_attendees force row level security;
drop policy if exists tenant_isolation on event_attendees;
create policy tenant_isolation on event_attendees
  using (
    event_id in (
      select id from events where workspace_id = nullif(current_setting('app.current_workspace_id', true), '')::integer
    )
  );

alter table calls enable row level security;
alter table calls force row level security;
drop policy if exists tenant_isolation on calls;
create policy tenant_isolation on calls
  using (workspace_id = nullif(current_setting('app.current_workspace_id', true), '')::integer);

-- call_participants carries no workspace_id of its own, so it inherits scope
-- through its call — same shape as event_attendees above.
alter table call_participants enable row level security;
alter table call_participants force row level security;
drop policy if exists tenant_isolation on call_participants;
create policy tenant_isolation on call_participants
  using (
    call_id in (
      select id from calls where workspace_id = nullif(current_setting('app.current_workspace_id', true), '')::integer
    )
  );

alter table workspace_phone_numbers enable row level security;
alter table workspace_phone_numbers force row level security;
drop policy if exists tenant_isolation on workspace_phone_numbers;
create policy tenant_isolation on workspace_phone_numbers
  using (workspace_id = nullif(current_setting('app.current_workspace_id', true), '')::integer);

alter table invites enable row level security;
alter table invites force row level security;
drop policy if exists tenant_isolation on invites;
create policy tenant_isolation on invites
  using (workspace_id = nullif(current_setting('app.current_workspace_id', true), '')::integer);
-- Invitees need to list/accept invites addressed to their account email
-- before they are members of the workspace (so tenant_isolation alone is
-- not enough for the pending-invite inbox).
drop policy if exists invitee_email on invites;
create policy invitee_email on invites
  using (
    email is not null
    and lower(email) = (
      select lower(u.email) from users u
      where u.id = nullif(current_setting('app.current_user_id', true), '')::integer
    )
  );

-- Personal inbox; not workspace-scoped. Users can read/update their own
-- rows. Inserts are allowed from any authenticated request (the API decides
-- who gets notified — e.g. workspace invitees).
alter table notifications enable row level security;
alter table notifications force row level security;
drop policy if exists notifications_own on notifications;
create policy notifications_own on notifications
  using (user_id = nullif(current_setting('app.current_user_id', true), '')::integer)
  with check (true);

-- Personal messaging state is both tenant-scoped and user-owned. This keeps
-- saved messages and drafts private even from another member of the same
-- workspace while still allowing one account to sync across its devices.
alter table saved_items enable row level security;
alter table saved_items force row level security;
drop policy if exists saved_items_own on saved_items;
create policy saved_items_own on saved_items
  using (
    workspace_id = nullif(current_setting('app.current_workspace_id', true), '')::integer
    and user_id = nullif(current_setting('app.current_user_id', true), '')::integer
  )
  with check (
    workspace_id = nullif(current_setting('app.current_workspace_id', true), '')::integer
    and user_id = nullif(current_setting('app.current_user_id', true), '')::integer
  );

alter table message_drafts enable row level security;
alter table message_drafts force row level security;
drop policy if exists message_drafts_own on message_drafts;
create policy message_drafts_own on message_drafts
  using (
    workspace_id = nullif(current_setting('app.current_workspace_id', true), '')::integer
    and user_id = nullif(current_setting('app.current_user_id', true), '')::integer
  )
  with check (
    workspace_id = nullif(current_setting('app.current_workspace_id', true), '')::integer
    and user_id = nullif(current_setting('app.current_user_id', true), '')::integer
  );

alter table notification_preferences enable row level security;
alter table notification_preferences force row level security;
drop policy if exists notification_preferences_own on notification_preferences;
create policy notification_preferences_own on notification_preferences
  using (
    workspace_id = nullif(current_setting('app.current_workspace_id', true), '')::integer
    and user_id = nullif(current_setting('app.current_user_id', true), '')::integer
  )
  with check (
    workspace_id = nullif(current_setting('app.current_workspace_id', true), '')::integer
    and user_id = nullif(current_setting('app.current_user_id', true), '')::integer
  );

alter table activity_reads enable row level security;
alter table activity_reads force row level security;
drop policy if exists activity_reads_own on activity_reads;
create policy activity_reads_own on activity_reads
  using (
    workspace_id = nullif(current_setting('app.current_workspace_id', true), '')::integer
    and user_id = nullif(current_setting('app.current_user_id', true), '')::integer
  )
  with check (
    workspace_id = nullif(current_setting('app.current_workspace_id', true), '')::integer
    and user_id = nullif(current_setting('app.current_user_id', true), '')::integer
  );

-- Personal third-party connections (OAuth tokens, OpenAI BYOK). User-owned
-- only — never workspace-scoped, so withUser() is the right entry point.
alter table user_integrations enable row level security;
alter table user_integrations force row level security;
drop policy if exists user_integrations_own on user_integrations;
create policy user_integrations_own on user_integrations
  using (user_id = nullif(current_setting('app.current_user_id', true), '')::integer)
  with check (user_id = nullif(current_setting('app.current_user_id', true), '')::integer);

-- Web Push endpoints are user-owned, not workspace-scoped (one PWA install
-- covers every workspace the account belongs to).
alter table push_subscriptions enable row level security;
alter table push_subscriptions force row level security;
drop policy if exists push_subscriptions_own on push_subscriptions;
create policy push_subscriptions_own on push_subscriptions
  using (user_id = nullif(current_setting('app.current_user_id', true), '')::integer)
  with check (user_id = nullif(current_setting('app.current_user_id', true), '')::integer);

-- organizations, users, and sessions are not workspace-scoped tables and are
-- intentionally not covered here — access to them is gated by application
-- logic (a user row is not itself tenant data; membership is what's scoped).
