-- Item tier enum
create type item_tier as enum ('fixed', 'variable', 'one_off');

-- Templates table (one per user)
create table templates (
  id          uuid primary key default gen_random_uuid(),
  user_id     uuid not null references auth.users(id) on delete cascade,
  name        text not null default 'My Template',
  created_at  timestamptz not null default now(),
  updated_at  timestamptz not null default now(),
  unique(user_id)
);

create index templates_user_id_idx on templates(user_id);

alter table templates enable row level security;

create policy "Users can CRUD own template"
  on templates for all
  using (user_id = auth.uid())
  with check (user_id = auth.uid());

-- Template items table
create table template_items (
  id              uuid primary key default gen_random_uuid(),
  template_id     uuid not null references templates(id) on delete cascade,
  category_id     uuid not null references categories(id) on delete restrict,
  item_tier       item_tier not null check (item_tier in ('fixed', 'variable')),
  budgeted_amount numeric(12,2) not null default 0,
  sort_order      int not null default 0,
  created_at      timestamptz not null default now(),
  updated_at      timestamptz not null default now()
);

create index template_items_template_id_idx on template_items(template_id);

alter table template_items enable row level security;

create policy "Users can CRUD own template items"
  on template_items for all
  using (
    template_id in (select id from templates where user_id = auth.uid())
  )
  with check (
    template_id in (select id from templates where user_id = auth.uid())
  );