-- BeyondHere demo-account data model.
-- Demo snapshot date: 2026-10-09. Money is stored as integer paise.

create table if not exists public.profiles (
  user_id uuid primary key references auth.users(id) on delete cascade,
  full_name text not null,
  email text not null unique,
  account_kind text not null default 'demo' check (account_kind in ('demo', 'standard')),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists public.portfolio_snapshots (
  user_id uuid primary key references auth.users(id) on delete cascade,
  invested_paise bigint not null check (invested_paise >= 0),
  current_balance_paise bigint not null check (current_balance_paise >= 0),
  profit_paise bigint generated always as (current_balance_paise - invested_paise) stored,
  as_of_date date not null,
  source_key text not null unique,
  is_demo boolean not null default true,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists public.account_transactions (
  id bigint generated always as identity primary key,
  user_id uuid not null references auth.users(id) on delete cascade,
  stable_key text not null,
  record_kind text not null check (record_kind in ('opening_funding', 'valuation_adjustment', 'imported')),
  label text not null,
  amount_paise bigint not null,
  occurred_on date not null,
  is_sample boolean not null default true,
  source_batch_id text,
  source_record_id text,
  created_at timestamptz not null default now(),
  unique (user_id, stable_key)
);

create unique index if not exists account_transactions_source_record_unique
  on public.account_transactions (user_id, source_record_id)
  where source_record_id is not null;

alter table public.profiles enable row level security;
alter table public.portfolio_snapshots enable row level security;
alter table public.account_transactions enable row level security;

drop policy if exists "Users read their own profile" on public.profiles;
create policy "Users read their own profile" on public.profiles
  for select to authenticated
  using ((select auth.uid()) = user_id);

drop policy if exists "Users read their own portfolio" on public.portfolio_snapshots;
create policy "Users read their own portfolio" on public.portfolio_snapshots
  for select to authenticated
  using ((select auth.uid()) = user_id);

drop policy if exists "Users read their own transactions" on public.account_transactions;
create policy "Users read their own transactions" on public.account_transactions
  for select to authenticated
  using ((select auth.uid()) = user_id);

revoke all on public.profiles, public.portfolio_snapshots, public.account_transactions from anon;
revoke all on public.profiles, public.portfolio_snapshots, public.account_transactions from authenticated;
grant usage on schema public to authenticated;
grant select on public.profiles, public.portfolio_snapshots, public.account_transactions to authenticated;