-- Promote operator / client / project from free-text columns on
-- time_entries to first-class entities. After this migration:
--   - `operators(id, org_id, name)`         — one per operator per org
--   - `clients(id, org_id, operator_id, name)` — scoped under operator
--   - `projects(id, org_id, client_id, name, income_usd, notes)`
--   - `time_entries.project_id`             — FK to projects (the new
--                                            source of truth)
--
-- The existing `time_entries.operator / client / project` text columns
-- are kept as a denormalized cache. Rename actions in the UI will
-- update both the entity row and the cache. All existing filter /
-- summary RPCs continue to work unchanged because they read from the
-- cache.
--
-- This migration is in a single transaction and is idempotent on the
-- entity-creation side (ON CONFLICT DO NOTHING). The schema changes
-- (ALTER TABLE) are not idempotent without IF NOT EXISTS guards, which
-- are used where supported.

-- ============================================================
-- 1. operators
-- ============================================================
create table if not exists public.operators (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references public.organizations(id) on delete cascade,
  name text not null check (length(trim(name)) > 0),
  notes text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create unique index if not exists operators_org_lower_name_unique
  on public.operators(org_id, lower(name));

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

alter table public.operators enable row level security;

-- Any org member can read AND mutate operators. Imports run as the user
-- who uploaded the CSV and need to create new operators on the fly.
-- Future tightening could restrict update / delete to org owners.
drop policy if exists "operators_all_org_member" on public.operators;
create policy "operators_all_org_member"
  on public.operators for all
  to authenticated
  using (public.is_org_member(org_id))
  with check (public.is_org_member(org_id));

comment on table public.operators is
  'Billing operators. Used to be free-text time_entries.operator; promoted to entities in 20260524000001.';

-- ============================================================
-- 2. clients (operator-scoped)
-- ============================================================
create table if not exists public.clients (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references public.organizations(id) on delete cascade,
  operator_id uuid not null references public.operators(id) on delete cascade,
  name text not null check (length(trim(name)) > 0),
  notes text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create unique index if not exists clients_operator_lower_name_unique
  on public.clients(operator_id, lower(name));

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

alter table public.clients enable row level security;

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

comment on table public.clients is
  'End clients. Operator-scoped — the same client name under two operators is two rows. See ADR #035.';

-- ============================================================
-- 3. Backfill operators from distinct values in time_entries.
-- ============================================================
insert into public.operators (org_id, name)
select distinct te.org_id, te.operator
from public.time_entries te
where te.operator is not null
on conflict (org_id, lower(name)) do nothing;

-- ============================================================
-- 4. Backfill clients from distinct (operator, client) pairs.
-- ============================================================
insert into public.clients (org_id, operator_id, name)
select distinct
  te.org_id,
  o.id,
  te.client
from public.time_entries te
join public.operators o
  on o.org_id = te.org_id and lower(o.name) = lower(te.operator)
where te.client is not null
  and te.operator is not null
on conflict (operator_id, lower(name)) do nothing;

-- ============================================================
-- 5. Refactor `projects` table: add client_id + name, populate from
--    existing operator/client/project text columns, then drop those.
--    ORDER MATTERS: the legacy unique index spans the text columns,
--    so we drop it BEFORE inserting the new rows that leave the text
--    columns NULL.
-- ============================================================
alter table public.projects
  add column if not exists client_id uuid references public.clients(id) on delete cascade;

alter table public.projects
  add column if not exists name text;

-- Step 5a: drop the legacy unique index so subsequent inserts with
-- NULL text columns don't all collide on (NULL, NULL, NULL).
drop index if exists projects_org_op_client_project_unique;

-- Step 5b: link each EXISTING projects row to its client via its
-- legacy text columns.
update public.projects p
set
  client_id = c.id,
  name = p.project
from public.clients c
join public.operators o on o.id = c.operator_id
where c.org_id = p.org_id
  and lower(o.name) = lower(p.operator)
  and lower(c.name) = lower(p.client)
  and p.client_id is null;

-- Step 5c: insert projects rows for every (op, client, proj) tuple
-- present in time_entries that doesn't already have a row.
insert into public.projects (org_id, client_id, name)
select distinct
  te.org_id,
  c.id,
  te.project
from public.time_entries te
join public.operators o
  on o.org_id = te.org_id and lower(o.name) = lower(te.operator)
join public.clients c
  on c.org_id = te.org_id
  and c.operator_id = o.id
  and lower(c.name) = lower(te.client)
where te.project is not null
  and te.operator is not null
  and te.client is not null
  and not exists (
    select 1 from public.projects p
    where p.client_id = c.id and lower(p.name) = lower(te.project)
  );

-- Step 5d: drop the legacy text columns now that we're done reading
-- them.
alter table public.projects drop column if exists operator;
alter table public.projects drop column if exists client;
alter table public.projects drop column if exists project;

-- Step 5e: new uniqueness — one project name per client.
create unique index if not exists projects_client_lower_name_unique
  on public.projects(client_id, lower(name));

-- Step 5f: structural columns NOT NULL now that backfill is done.
alter table public.projects alter column client_id set not null;
alter table public.projects alter column name set not null;

-- The old projects policies referred to `projects` by org_id only,
-- which still works (org_id is preserved). RLS remains owner-only.
-- (No policy change needed — verified manually.)

-- ============================================================
-- 6. time_entries.project_id  (denormalized text columns stay)
-- ============================================================
alter table public.time_entries
  add column if not exists project_id uuid references public.projects(id) on delete set null;

create index if not exists time_entries_project_id_idx
  on public.time_entries(project_id);

-- Backfill: link every entry to its project entity by the text triple.
update public.time_entries te
set project_id = p.id
from public.projects p
join public.clients c on c.id = p.client_id
join public.operators o on o.id = c.operator_id
where p.org_id = te.org_id
  and lower(o.name) = lower(te.operator)
  and lower(c.name) = lower(te.client)
  and lower(p.name) = lower(te.project)
  and te.project_id is null;

comment on column public.time_entries.project_id is
  'FK to projects (the new source of truth). The operator/client/project text columns are a denormalized cache that is kept in sync by rename actions in the app layer. NULL means the entry is blocked (missing one of the three).';
