-- Budgets table
create table budgets (
  id               uuid primary key default gen_random_uuid(),
  user_id          uuid not null references auth.users(id) on delete cascade,
  start_date       date not null,
  end_date         date not null,
  currency         text not null default 'EUR',
  carryover_amount numeric(12,2) not null default 0,
  created_at       timestamptz not null default now(),
  updated_at       timestamptz not null default now()
);

create index budgets_user_id_idx on budgets(user_id);

alter table budgets enable row level security;

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

-- Budget items table
create table budget_items (
  id              uuid primary key default gen_random_uuid(),
  budget_id       uuid not null references budgets(id) on delete cascade,
  category_id     uuid not null references categories(id) on delete restrict,
  budgeted_amount numeric(12,2) not null default 0,
  actual_amount   numeric(12,2) not null default 0,
  item_tier       item_tier not null default 'fixed',
  notes           text,
  created_at      timestamptz not null default now(),
  updated_at      timestamptz not null default now()
);

create index budget_items_budget_id_idx on budget_items(budget_id);

alter table budget_items enable row level security;

create policy "Users can CRUD own budget items"
  on budget_items for all
  using (
    budget_id in (select id from budgets where user_id = auth.uid())
  )
  with check (
    budget_id in (select id from budgets where user_id = auth.uid())
  );