-- Phase 2 of the CC Expenses tool: highlight keywords + auto-assign
-- rules. Both are org-scoped substring matchers against the parsed
-- description column of pasted CC lines.
--
-- - cc_highlight_keywords: each match paints the row yellow at render
--   time on the batch detail page. Purely visual; doesn't affect data.
-- - cc_auto_rules: each match pre-fills (project_id, description,
--   category) on the line when it's first inserted via appendCcLines.
--   Applied ONLY at paste time — adding a new rule doesn't retroactively
--   touch existing lines (per user direction 2026-05-25).
--
-- "First match wins" by created_at desc; no priority column in v1.
-- Multiple rules for the same keyword aren't allowed (unique index).

-- ============================================================
-- 1. Highlight keywords
-- ============================================================
create table if not exists public.cc_highlight_keywords (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references public.organizations(id) on delete cascade,
  keyword text not null,
  created_at timestamptz not null default now()
);

create unique index if not exists cc_highlight_keywords_org_kw_uq
  on public.cc_highlight_keywords (org_id, lower(keyword));

comment on table public.cc_highlight_keywords is
  'Substring matches against cc_expense_lines.description that trigger the yellow row highlight on the batch detail page. Owner-managed.';

-- ============================================================
-- 2. Auto-assign rules
-- ============================================================
create table if not exists public.cc_auto_rules (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references public.organizations(id) on delete cascade,
  keyword text not null,
  /** Required: the target project the matched line will be pinned to. */
  project_id uuid not null references public.projects(id) on delete cascade,
  /** Pre-fill values for the line. NULL = leave the line's column NULL. */
  assignment_description text,
  category text,
  created_at timestamptz not null default now()
);

create unique index if not exists cc_auto_rules_org_kw_uq
  on public.cc_auto_rules (org_id, lower(keyword));

create index if not exists cc_auto_rules_project_idx
  on public.cc_auto_rules (project_id);

comment on table public.cc_auto_rules is
  'Substring → (project, description, category) auto-assignment rules. Applied at paste time by appendCcLines. First match wins by created_at desc. Deleting a project cascades to its rules.';

-- ============================================================
-- 3. RLS — org members read/write; owner-only at the UI layer
-- ============================================================
alter table public.cc_highlight_keywords enable row level security;

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

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

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

alter table public.cc_auto_rules enable row level security;

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

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

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

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