-- Teams per user. One team per (org, owner). The team owns the base
-- billing rate, owns the list of team_members, and is the unit of
-- importer visibility.
--
-- Before this migration, "team" was implicit: every team_members row in
-- an org belonged to the same conceptual team, with the rate on
-- organizations.default_hourly_rate_usd. This was fine when only Adi
-- was importing time. Now that Rian imports his own Toggl data and has
-- his own collaborators (Matt) at his own rate ($50), we need a
-- separate billing unit per user.
--
-- ADR #032 captures the design decision.

-- ============================================================
-- 1. Helper: is the caller an owner of the given org?
-- ============================================================
create or replace function public.is_org_owner(target_org uuid)
returns boolean
language sql
stable
security invoker
set search_path = public
as $$
  select exists (
    select 1
    from public.organization_members
    where organization_id = target_org
      and user_id = auth.uid()
      and role = 'owner'
  );
$$;

create or replace function public.is_super_admin_caller()
returns boolean
language sql
stable
security invoker
set search_path = public
as $$
  select coalesce(
    (select is_super_admin from public.profiles where id = auth.uid()),
    false
  );
$$;

-- ============================================================
-- 2. teams table
-- ============================================================
create table if not exists public.teams (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references public.organizations(id) on delete cascade,
  owner_user_id uuid not null references auth.users(id) on delete cascade,
  name text,
  base_rate_usd numeric(10, 2) not null default 50,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (org_id, owner_user_id)
);

drop trigger if exists teams_set_updated_at on public.teams;
create trigger teams_set_updated_at
before update on public.teams
for each row execute function public.set_updated_at();

alter table public.teams enable row level security;

-- Read: team owner, org owner, or super_admin.
drop policy if exists "teams_select" on public.teams;
create policy "teams_select"
  on public.teams for select
  to authenticated
  using (
    owner_user_id = auth.uid()
    or public.is_org_owner(org_id)
    or public.is_super_admin_caller()
  );

-- Insert / Update / Delete: org owner or super_admin.
-- (Owning your team doesn't let you delete it — that's an admin op.
-- For now this means only the owner / super_admin can create or
-- delete teams; team owners can still edit name + base_rate via the
-- update policy below.)
drop policy if exists "teams_modify_admin" on public.teams;
create policy "teams_modify_admin"
  on public.teams for all
  to authenticated
  using (
    public.is_org_owner(org_id)
    or public.is_super_admin_caller()
  )
  with check (
    public.is_org_owner(org_id)
    or public.is_super_admin_caller()
  );

-- Team owners can update their own team's settings (rate, name).
drop policy if exists "teams_update_self" on public.teams;
create policy "teams_update_self"
  on public.teams for update
  to authenticated
  using (owner_user_id = auth.uid())
  with check (owner_user_id = auth.uid());

-- ============================================================
-- 3. team_members.team_id (nullable for now, backfilled below)
-- ============================================================
alter table public.team_members
  add column if not exists team_id uuid references public.teams(id) on delete cascade;

-- ============================================================
-- 4. Backfill: create Adi's team and Rian's team in Bowden Works,
--    assign existing members, move rian@rian.ca to Rian's team, and
--    add matmoncoo@gmail.com (Matthew Monti-Cooper, 0.7).
-- ============================================================
do $$
declare
  bw_id uuid;
  adi_id uuid;
  rian_id uuid;
  adi_team_id uuid;
  rian_team_id uuid;
  legacy_rate numeric;
begin
  select id, default_hourly_rate_usd into bw_id, legacy_rate
    from public.organizations
    where slug = 'bowden-works'
    limit 1;
  if bw_id is null then
    raise notice 'Bowden Works org not found — skipping backfill';
    return;
  end if;

  select id into adi_id from auth.users where email = 'info@adipramono.com' limit 1;
  select id into rian_id from auth.users where email = 'rian@rian.ca' limit 1;
  if adi_id is null or rian_id is null then
    raise notice 'Adi or Rian profile not found — skipping backfill';
    return;
  end if;

  -- Adi's team — inherits the legacy org-wide rate (defaults to 35 if unset).
  insert into public.teams (org_id, owner_user_id, name, base_rate_usd)
  values (bw_id, adi_id, 'Adi''s team', coalesce(legacy_rate, 35))
  on conflict (org_id, owner_user_id) do update set name = excluded.name
  returning id into adi_team_id;

  -- Rian's team — $50.
  insert into public.teams (org_id, owner_user_id, name, base_rate_usd)
  values (bw_id, rian_id, 'Rian''s team', 50)
  on conflict (org_id, owner_user_id) do update set name = excluded.name
  returning id into rian_team_id;

  -- All legacy team_members in Bowden Works → Adi's team initially.
  update public.team_members
  set team_id = adi_team_id
  where org_id = bw_id
    and team_id is null;

  -- Move the rian@rian.ca row over to Rian's team.
  -- (It was added to Adi's team in 20260523000001 only so Toggl entries
  -- imported by Rian could resolve to a team_member. Now it belongs in
  -- Rian's team.)
  update public.team_members
  set team_id = rian_team_id
  where org_id = bw_id
    and email = 'rian@rian.ca';

  -- Add Matt to Rian's team at 0.7. Skip if a row with this email
  -- already exists in Rian's team (re-running the migration shouldn't
  -- duplicate).
  insert into public.team_members (
    org_id, team_id, email, display_name, rate_proportion, is_active
  )
  select bw_id, rian_team_id, 'matmoncoo@gmail.com', 'Matthew Monti-Cooper', 0.7, true
  where not exists (
    select 1 from public.team_members
    where team_id = rian_team_id
      and email = 'matmoncoo@gmail.com'
  );
end $$;

-- ============================================================
-- 5. Enforce team_id NOT NULL now that backfill is done.
-- ============================================================
alter table public.team_members
  alter column team_id set not null;

-- ============================================================
-- 6. Replace org-wide email uniqueness with per-team uniqueness.
--    The same email (e.g. rian@rian.ca) can exist in multiple teams.
-- ============================================================
alter table public.team_members
  drop constraint if exists team_members_org_id_email_key;

alter table public.team_members
  drop constraint if exists team_members_team_id_email_key;
alter table public.team_members
  add constraint team_members_team_id_email_key unique (team_id, email);

-- ============================================================
-- 7. Rewrite team_members RLS to flow through team visibility.
-- ============================================================
drop policy if exists "team_members_org_select" on public.team_members;
drop policy if exists "team_members_org_insert" on public.team_members;
drop policy if exists "team_members_org_update" on public.team_members;
drop policy if exists "team_members_org_delete" on public.team_members;

drop policy if exists "team_members_team_select" on public.team_members;
create policy "team_members_team_select"
  on public.team_members for select
  to authenticated
  using (
    exists (
      select 1 from public.teams t
      where t.id = team_members.team_id
        and (
          t.owner_user_id = auth.uid()
          or public.is_org_owner(t.org_id)
          or public.is_super_admin_caller()
        )
    )
  );

drop policy if exists "team_members_team_modify" on public.team_members;
create policy "team_members_team_modify"
  on public.team_members for all
  to authenticated
  using (
    exists (
      select 1 from public.teams t
      where t.id = team_members.team_id
        and (
          t.owner_user_id = auth.uid()
          or public.is_org_owner(t.org_id)
          or public.is_super_admin_caller()
        )
    )
  )
  with check (
    exists (
      select 1 from public.teams t
      where t.id = team_members.team_id
        and (
          t.owner_user_id = auth.uid()
          or public.is_org_owner(t.org_id)
          or public.is_super_admin_caller()
        )
    )
  );

comment on table public.teams is
  'Billing-rate-bearing team. One per (org, owner). Adi has one, Rian has one.';
comment on column public.teams.base_rate_usd is
  'USD per billout-hour for this team. Used when computing billout_cost_usd at import time. Changing this does NOT retroactively change historical entries — billout_cost_usd is locked at import time.';
comment on column public.team_members.team_id is
  'The billing team this member belongs to. Email uniqueness is scoped to (team_id, email), not (org_id, email) — the same email can be in two teams.';
