-- ============================================================================
-- SAG Manager — 0002 finance
-- ============================================================================
-- Bills, Plaid (accounts, encrypted access tokens, txn overrides, splits),
-- subscriptions, owner-draws, Toast imports + labor scaffold.
--
-- Modeled on:
--   - Bill (src/lib/finance/bill-types.ts)
--   - ConnectedAccount (src/components/app/plaid-connect.tsx; key sag.plaid.accounts)
--   - TxnOverride (src/lib/finance/txn-overrides.ts)
--   - TxnSplit / TxnSplitPart (src/lib/finance/txn-splits.ts) — parts as JSONB
--   - StoredSubscription (src/lib/finance/subscriptions-store.ts)
--   - Subscription ignore-list (sag.finance.subscription_ignored)
--   - OwnerDraw (src/lib/income/owner-draws.ts)
--   - Toast imports (sag.toast.imports) and a stub for the future labor import
-- ============================================================================

create extension if not exists "pgcrypto";

-- ─────────────────────────────────────────────────────────────────────────
-- bills — Bill v2 from bill-types.ts
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.bills (
  id                   uuid primary key default gen_random_uuid(),
  entity_slug          text not null,
  vendor               text not null,
  amount               numeric(14,2) not null,
  due_date             timestamptz not null,
  status               text not null default 'Unscheduled'
    check (status in ('Unscheduled','Pending Review','Scheduled','Paid','Overdue','Cancelled')),
  recurring            boolean not null default false,
  recurrence_pattern   text,
  notes                text,
  category             text,
  account_number       text,
  source               text not null default 'manual'
    check (source in ('manual','gmail','pdf','csv','plaid')),
  source_ref           text,                          -- gmail thread id, plaid txn id, filename, etc.
  line_items           jsonb not null default '[]'::jsonb,    -- BillLineItem[]
  attachments          jsonb not null default '[]'::jsonb,    -- BillAttachment[]
  payment              jsonb,                                 -- BillPayment | null
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now()
);

create index if not exists bills_entity_due_idx on public.bills (entity_slug, due_date);
create index if not exists bills_status_idx on public.bills (status);
create index if not exists bills_recurring_idx on public.bills (recurring) where recurring;

drop trigger if exists bills_set_updated_at on public.bills;
create trigger bills_set_updated_at
  before update on public.bills
  for each row execute function public.set_updated_at();

-- ─────────────────────────────────────────────────────────────────────────
-- plaid_accounts — ConnectedAccount shape from plaid-connect.tsx
-- One row per Plaid account; `item_id` groups accounts under one Plaid Item.
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.plaid_accounts (
  id                   uuid primary key default gen_random_uuid(),
  account_id           text not null unique,          -- Plaid's stable account id
  item_id              text not null,                 -- Plaid Item id
  name                 text not null,
  official_name        text,
  type                 text,
  subtype              text,
  mask                 text,
  balance              numeric(14,2),
  currency             text,
  institution          text,
  entity_slug          text,                          -- user-assigned attribution; null = unassigned
  connected_at         timestamptz not null default now(),
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now()
);

create index if not exists plaid_accounts_item_idx on public.plaid_accounts (item_id);
create index if not exists plaid_accounts_entity_idx on public.plaid_accounts (entity_slug);

drop trigger if exists plaid_accounts_set_updated_at on public.plaid_accounts;
create trigger plaid_accounts_set_updated_at
  before update on public.plaid_accounts
  for each row execute function public.set_updated_at();

-- ─────────────────────────────────────────────────────────────────────────
-- plaid_access_tokens — encrypted Plaid access token per Item
-- IMPORTANT: `access_token_encrypted` MUST be encrypted at the application
-- layer (libsodium / KMS / pgsodium) before insert. The column type stays
-- text so the encrypted ciphertext is portable; raw Plaid tokens should
-- NEVER be persisted to this column.
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.plaid_access_tokens (
  item_id                  text primary key,          -- Plaid Item id
  access_token_encrypted   text not null,             -- ciphertext (e.g. pgsodium / KMS-wrapped)
  encryption_key_id        text,                      -- which key encrypted this (for rotation)
  institution              text,
  created_at               timestamptz not null default now(),
  updated_at               timestamptz not null default now()
);

drop trigger if exists plaid_access_tokens_set_updated_at on public.plaid_access_tokens;
create trigger plaid_access_tokens_set_updated_at
  before update on public.plaid_access_tokens
  for each row execute function public.set_updated_at();

-- ─────────────────────────────────────────────────────────────────────────
-- plaid_txn_overrides — F5 single-attribution overrides for a Plaid txn
-- Mirrors TxnOverride in txn-overrides.ts.
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.plaid_txn_overrides (
  txn_id               text primary key,              -- Plaid transactionId
  category             text,
  entity_slug          text,
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now()
);

create index if not exists plaid_txn_overrides_entity_idx on public.plaid_txn_overrides (entity_slug);

drop trigger if exists plaid_txn_overrides_set_updated_at on public.plaid_txn_overrides;
create trigger plaid_txn_overrides_set_updated_at
  before update on public.plaid_txn_overrides
  for each row execute function public.set_updated_at();

-- ─────────────────────────────────────────────────────────────────────────
-- plaid_txn_splits — F6 split-transaction across N entities / categories
-- The TxnSplit shape is: { txnId, parts: TxnSplitPart[], updatedAt }.
-- Parts are stored as JSONB so the validation rules in txn-splits.ts
-- (>= 2 parts, sum to 100, per-part >0) stay enforced in app code rather
-- than splintered across SQL constraints — easier to evolve.
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.plaid_txn_splits (
  txn_id               text primary key,              -- Plaid transactionId
  parts                jsonb not null,                -- TxnSplitPart[] — validated app-side
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now()
);

drop trigger if exists plaid_txn_splits_set_updated_at on public.plaid_txn_splits;
create trigger plaid_txn_splits_set_updated_at
  before update on public.plaid_txn_splits
  for each row execute function public.set_updated_at();

-- ─────────────────────────────────────────────────────────────────────────
-- subscriptions — F1 manual store (StoredSubscription)
-- Amounts normalized to monthly equivalent regardless of cadence.
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.subscriptions (
  id                   uuid primary key default gen_random_uuid(),
  vendor               text not null,
  monthly_amount       numeric(14,2) not null,
  cadence              text not null
    check (cadence in ('weekly','biweekly','monthly','quarterly','annual','irregular')),
  entity_slug          text,
  payment_method       text,
  next_renewal_date    timestamptz,
  notes                text,
  confirmed_from_detected text,                       -- detector cluster id (FNV-hash of normalized merchant)
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now()
);

create index if not exists subscriptions_entity_idx on public.subscriptions (entity_slug);
create index if not exists subscriptions_renewal_idx on public.subscriptions (next_renewal_date);

drop trigger if exists subscriptions_set_updated_at on public.subscriptions;
create trigger subscriptions_set_updated_at
  before update on public.subscriptions
  for each row execute function public.set_updated_at();

-- ─────────────────────────────────────────────────────────────────────────
-- subscription_ignored — merchant strings the user has hidden from the
-- subscription detector. Key = normalized merchant string.
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.subscription_ignored (
  merchant_normalized  text primary key,
  ignored_at           timestamptz not null default now()
);

-- ─────────────────────────────────────────────────────────────────────────
-- owner_draws — P2 manual log of personal owner draws from SAG entities
-- Mirrors OwnerDraw in owner-draws.ts.
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.owner_draws (
  id                   uuid primary key default gen_random_uuid(),
  draw_date            timestamptz not null,
  amount               numeric(14,2) not null check (amount > 0),
  from_entity_slug     text not null,
  method               text,
  note                 text,
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now()
);

create index if not exists owner_draws_date_idx on public.owner_draws (draw_date desc);
create index if not exists owner_draws_entity_idx on public.owner_draws (from_entity_slug);

drop trigger if exists owner_draws_set_updated_at on public.owner_draws;
create trigger owner_draws_set_updated_at
  before update on public.owner_draws
  for each row execute function public.set_updated_at();

-- ─────────────────────────────────────────────────────────────────────────
-- toast_imports — raw Toast Sales Category CSV imports
-- Mirrors SavedImport in dashboard-stats.tsx (id, name, uploadedAt, rowCount,
-- rows: ToastSalesRow[]). The `rows` payload is captured as JSONB for fidelity
-- with the original CSV and so the importer can be replayed.
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.toast_imports (
  id                   uuid primary key default gen_random_uuid(),
  name                 text not null,                 -- "sales-breakdown-2026-04-01.csv"
  uploaded_at          timestamptz not null default now(),
  row_count            integer not null,
  rows                 jsonb not null,                -- ToastSalesRow[]
  notes                text,
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now()
);

create index if not exists toast_imports_uploaded_idx on public.toast_imports (uploaded_at desc);

drop trigger if exists toast_imports_set_updated_at on public.toast_imports;
create trigger toast_imports_set_updated_at
  before update on public.toast_imports
  for each row execute function public.set_updated_at();

-- ─────────────────────────────────────────────────────────────────────────
-- toast_labor — reserved for the future "Hours and Earnings by Range"
-- importer. Schema matches ToastLaborRow in payroll-prep.ts. Empty for now;
-- callers (rollupPayroll) currently pass `[]`.
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.toast_labor (
  id                   uuid primary key default gen_random_uuid(),
  labor_date           timestamptz not null,          -- maps to ToastLaborRow.date
  employee_id          text not null,                 -- raw Toast name OR resolved Employee.id
  entity_slug          text,
  hours                numeric(8,2) not null default 0,
  wages                numeric(14,2),
  tips                 numeric(14,2),
  raw                  jsonb,                         -- preserve the source row
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now()
);

create index if not exists toast_labor_date_idx on public.toast_labor (labor_date desc);
create index if not exists toast_labor_employee_idx on public.toast_labor (employee_id);

drop trigger if exists toast_labor_set_updated_at on public.toast_labor;
create trigger toast_labor_set_updated_at
  before update on public.toast_labor
  for each row execute function public.set_updated_at();
