-- Global invoicing configuration (singleton) + historical USD/PHP rate
-- log. Ported from DEFAULT_INVOICE_SETTINGS / state.invoiceSettings and
-- state.invoiceSettings.rateHistory (JavaScript.html:6803-6830, and the
-- rate-history entry shape saved at JavaScript.html:8225:
-- { period, cutLabel, rate, hourlyUsd, note, savedBy, ts }).
--
-- Nested config objects (bill_to, perks, night_diff, holiday_pay,
-- bonuses, shift_hours) are stored as jsonb, matching the shape the
-- source app already uses for these, and matching the precedent already
-- set by invoices.rate_snapshot/profile_snapshot/bill_to in 0001 —
-- rather than exploding each into its own column.
--
-- Note: `bonuses` is NOT part of DEFAULT_INVOICE_SETTINGS in the source
-- (JavaScript.html:6803-6830) — it's synced in separately from its own
-- DEFAULT_BONUSES constant by ensureInvoiceState() (JavaScript.html:
-- 9764-9766: `if (!state.invoiceSettings.bonuses) state.invoiceSettings
-- .bonuses = ...DEFAULT_BONUSES`). Functionally it's still a real field
-- on invoiceSettings by the time any invoice logic reads it, so it
-- belongs on this table regardless of which literal it originated from.

create table if not exists public.invoice_settings (
  id               int primary key default 1,
  usd_php_rate     numeric not null default 0,
  hourly_rate_usd  numeric not null default 0,
  invoice_prefix   text not null default 'EC-INV',
  bill_to          jsonb not null default '{"company":"","address":"","registration":""}'::jsonb,
  perks            jsonb not null default '{"internetAllowance":0,"wellnessProgram":0,"hmoMonthly":0,"pagIbigMp2Pct":0,"enablePerks":false}'::jsonb,
  night_diff       jsonb not null default '{"enabled":false,"rate":10,"phWindowStart":22,"phWindowEnd":6}'::jsonb,
  holiday_pay      jsonb not null default '{"enabled":false,"rate":200,"standardHours":8,"dates":[]}'::jsonb,
  bonuses          jsonb not null default '{"enableSat":true,"saturdayPct":15,"enable6th":true,"sixthDayPct":25}'::jsonb,
  shift_hours      jsonb not null default '{}'::jsonb,
  updated_at       timestamptz not null default now(),
  constraint invoice_settings_singleton check (id = 1)
);

-- Seed the single settings row with the same defaults as
-- DEFAULT_INVOICE_SETTINGS, so the table isn't empty when the app first
-- reads it.
insert into public.invoice_settings (id)
values (1)
on conflict (id) do nothing;

create table if not exists public.rate_history (
  id           uuid primary key default gen_random_uuid(),
  period       text not null unique,  -- e.g. "2026-06-C1"
  cut_label    text,
  rate         numeric not null,      -- USD -> PHP rate for this period
  hourly_usd   numeric not null,
  note         text,
  saved_by     text,
  recorded_at  timestamptz not null default now()
);

alter table public.invoice_settings enable row level security;
alter table public.rate_history enable row level security;

create policy "invoice_settings_select_all"
on public.invoice_settings
for select
to authenticated
using (true);

-- TODO: intentionally permissive for now, same reasoning as schedule's
-- write policies in 0011 — there is no billing_admin flag on `profiles`
-- yet. Replace `true` below with a real billing_admin check once that
-- flag exists, matching the legacy app's canSetCurrencyRate().
create policy "invoice_settings_update_admin_TODO"
on public.invoice_settings
for update
to authenticated
using (true)
with check (true);

create policy "rate_history_select_all"
on public.rate_history
for select
to authenticated
using (true);

create policy "rate_history_insert_admin_TODO"
on public.rate_history
for insert
to authenticated
with check (true);

create policy "rate_history_update_admin_TODO"
on public.rate_history
for update
to authenticated
using (true)
with check (true);

create policy "rate_history_delete_admin_TODO"
on public.rate_history
for delete
to authenticated
using (true);