-- Time-tracking bridge schema: team_members, clockify_imports, time_entries.
-- Paste into Supabase SQL Editor and Run.

-- ============================================================
-- Helper: is the calling user a member of the given org?
-- Used by RLS policies. SECURITY INVOKER so RLS on
-- organization_members still applies (which restricts to own rows
-- anyway, so the user can only confirm membership in orgs they're
-- already a member of).
-- ============================================================
create or replace function public.is_org_member(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()
  );
$$;

-- ============================================================
-- team_members: Adi's developers + Adi himself.
-- Reference table, no auth accounts.
-- ============================================================
create table if not exists public.team_members (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references public.organizations(id) on delete cascade,
  email text not null,
  display_name text not null,
  rate_proportion numeric(6, 5) not null check (rate_proportion >= 0 and rate_proportion <= 1),
  hourly_rate_usd numeric(8, 2),
  -- If null, converted_user uses display_name. If set (e.g. 'Tingang'),
  -- this label replaces the user name on converted entries.
  consolidate_as text,
  is_active boolean not null default true,
  notes text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (org_id, email)
);

create index if not exists idx_team_members_org_email
  on public.team_members(org_id, lower(email));

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

alter table public.team_members enable row level security;

drop policy if exists "team_members_org_select" on public.team_members;
create policy "team_members_org_select"
  on public.team_members for select
  to authenticated
  using (public.is_org_member(org_id));

drop policy if exists "team_members_org_insert" on public.team_members;
create policy "team_members_org_insert"
  on public.team_members for insert
  to authenticated
  with check (public.is_org_member(org_id));

drop policy if exists "team_members_org_update" on public.team_members;
create policy "team_members_org_update"
  on public.team_members for update
  to authenticated
  using (public.is_org_member(org_id))
  with check (public.is_org_member(org_id));

drop policy if exists "team_members_org_delete" on public.team_members;
create policy "team_members_org_delete"
  on public.team_members for delete
  to authenticated
  using (public.is_org_member(org_id));

-- ============================================================
-- clockify_imports: each batch of CSV uploaded to /import.
-- ============================================================
create table if not exists public.clockify_imports (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references public.organizations(id) on delete cascade,
  imported_by uuid references auth.users(id) on delete set null,
  imported_at timestamptz not null default now(),
  filename text,
  row_count integer not null default 0,
  notes text
);

create index if not exists idx_clockify_imports_org_date
  on public.clockify_imports(org_id, imported_at desc);

alter table public.clockify_imports enable row level security;

drop policy if exists "clockify_imports_org_all" on public.clockify_imports;
create policy "clockify_imports_org_all"
  on public.clockify_imports for all
  to authenticated
  using (public.is_org_member(org_id))
  with check (public.is_org_member(org_id));

-- ============================================================
-- time_entries: one row per Clockify entry.
-- Raw source fields + resolved conversion fields + transfer state.
-- ============================================================
create table if not exists public.time_entries (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references public.organizations(id) on delete cascade,
  import_id uuid references public.clockify_imports(id) on delete set null,

  -- Raw from Clockify (may be null for historical backfills)
  source_user_name text,
  source_user_email text,
  client text,
  project text,
  description text,
  billable boolean,
  -- Naive local timestamps. Clockify exports local times without TZ; Toggl
  -- import re-interprets in the user's TZ. We preserve the string exactly.
  start_at timestamp not null,
  end_at timestamp,
  duration_seconds integer,
  source_rate_usd numeric(10, 4),
  source_amount_usd numeric(10, 4),

  -- Resolved at insert time
  team_member_id uuid references public.team_members(id) on delete set null,
  converted_user text,                      -- 'Tingang' or display_name
  converted_duration_seconds integer,       -- duration_seconds * rate_proportion

  -- Transfer to Toggl state
  transferred_at timestamptz,
  transferred_by uuid references auth.users(id) on delete set null,
  transfer_batch_id uuid,

  created_at timestamptz not null default now()
);

create index if not exists idx_time_entries_org_start
  on public.time_entries(org_id, start_at desc);

create index if not exists idx_time_entries_org_transferred
  on public.time_entries(org_id, transferred_at)
  where transferred_at is null;

create index if not exists idx_time_entries_org_project_month
  on public.time_entries(org_id, project, start_at);

create index if not exists idx_time_entries_source_email
  on public.time_entries(org_id, source_user_email);

alter table public.time_entries enable row level security;

drop policy if exists "time_entries_org_all" on public.time_entries;
create policy "time_entries_org_all"
  on public.time_entries for all
  to authenticated
  using (public.is_org_member(org_id))
  with check (public.is_org_member(org_id));
