Supabase Backend

Supabase commerce adapter: SUPABASE_URL and SUPABASE_SERVICE_ROLE_KEY must be
set. This adapter is server-side only — never expose the service-role key to
the client.

create extension if not exists pgcrypto;

create table if not exists products (
  id text primary key,
  data jsonb not null default '{}'::jsonb
);

-- Prefix search: WHERE data->>'productNameLower' LIKE 'term%'.
-- text_pattern_ops makes the LIKE prefix scan use the index regardless of collation.
create index if not exists products_name_lower_idx
  on products ((data ->> 'productNameLower') text_pattern_ops);

create index if not exists products_brand_idx
  on products ((data ->> 'brand'));
create index if not exists products_categories_idx
  on products using gin ((data -> 'categories'));

create table if not exists featured_products (
  id uuid primary key default gen_random_uuid(),
  data jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

-- One cart per user, keyed by the auth uid. `items` is the JSON array of
-- cart line snapshots (line identity = id or id::variantId). clear() DELETES
-- the row (a missing row reads as "no cart", distinct from an empty cart).
create table if not exists carts (
  user_id text primary key,
  items jsonb not null default '[]'::jsonb,
  updated_at timestamptz not null default now()
);

create table if not exists quotes (
  id uuid primary key default gen_random_uuid(),
  quote_number text not null,
  customer_id text,
  status text not null default 'Pending',
  message text not null default '',
  order_number text,
  created timestamptz not null default now(),
  items jsonb not null default '[]'::jsonb,
  customer jsonb not null default '{}'::jsonb,
  discount jsonb
);
create index if not exists quotes_created_desc_idx on quotes (created desc);
create index if not exists quotes_customer_idx on quotes (customer_id);

create table if not exists customer_quotes (
  customer_id text not null,
  quote_number text not null,
  main_quote_id uuid,
  status text not null default 'Pending',
  message text not null default '',
  order_number text,
  created timestamptz not null default now(),
  items jsonb not null default '[]'::jsonb,
  customer jsonb not null default '{}'::jsonb,
  discount jsonb,
  primary key (customer_id, quote_number)
);

create table if not exists orders (
  id uuid primary key default gen_random_uuid(),
  order_number text not null,
  quote_id uuid,
  quote_number text,
  customer_id text,
  customer jsonb not null default '{}'::jsonb,
  items jsonb not null default '[]'::jsonb,
  total numeric(12, 2) not null default 0,
  status text not null default 'Processing',
  created timestamptz not null default now(),
  discount jsonb,
  -- UNIQUE is the idempotency key for the Stripe webhook: a retried delivery
  -- hits the constraint instead of creating a second order.
  payment_session_id text unique,
  payment_status text
);
-- Adds the payment columns to a database created before they existed.
alter table orders add column if not exists payment_session_id text;
alter table orders add column if not exists payment_status text;
create unique index if not exists orders_payment_session_id_key
  on orders (payment_session_id);

create index if not exists orders_created_desc_idx on orders (created desc);
create index if not exists orders_customer_idx on orders (customer_id);

create table if not exists customer_orders (
  customer_id text not null,
  order_number text not null,
  quote_id uuid,
  quote_number text,
  customer jsonb not null default '{}'::jsonb,
  items jsonb not null default '[]'::jsonb,
  total numeric(12, 2) not null default 0,
  status text not null default 'Processing',
  created timestamptz not null default now(),
  discount jsonb,
  payment_session_id text,
  payment_status text,
  primary key (customer_id, order_number)
);

alter table customer_orders add column if not exists payment_session_id text;
alter table customer_orders add column if not exists payment_status text;

create table if not exists customers (
  id text primary key,
  first_name text not null default '',
  last_name text not null default '',
  phone text not null default '',
  email text not null default ''
);

create table if not exists customer_addresses (
  id uuid primary key default gen_random_uuid(),
  customer_id text not null,
  data jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);
create index if not exists customer_addresses_customer_idx
  on customer_addresses (customer_id);

create table if not exists discounts (
  code text primary key,
  type text not null default 'percent',
  value numeric not null default 0,
  active boolean not null default false,
  expires_at timestamptz,
  -- Total-redemption cap; NULL = unlimited. used_count >= max_uses reads as
  -- "not usable" (same UX as expired).
  max_uses integer,
  -- System-maintained redemption counter: quotes.submit calls
  -- increment_discount_usage() after persisting the quote; discounts.save
  -- never writes this column.
  used_count integer not null default 0,
  -- Automatic promotion: auto-applied by the cart (no code entry) when
  -- usable and the subtotal meets min_subtotal.
  automatic boolean not null default false,
  -- Minimum cart subtotal for automatic application; NULL = no threshold.
  min_subtotal numeric,
  created timestamptz not null default now()
);

-- Migration for pre-existing deployments (create table if not exists does
-- not add columns): safe to re-run, no-ops once the columns exist.
alter table discounts add column if not exists max_uses integer;
alter table discounts add column if not exists used_count integer not null default 0;
alter table discounts add column if not exists automatic boolean not null default false;
alter table discounts add column if not exists min_subtotal numeric;

comment on table discounts is
  'Codes stored trimmed + uppercased. expires_at NULL = never expires; a code is usable through (inclusive of) the expiry instant. max_uses NULL = unlimited; used_count is system-maintained (see increment_discount_usage).';

create index if not exists discounts_created_desc_idx on discounts (created desc);
create index if not exists discounts_automatic_idx on discounts (automatic);

-- Atomic usage increment, called by the adapter (service role) right after
-- a quote carrying the discount is persisted. PostgREST cannot express
-- `used_count = used_count + 1` in an UPDATE, hence the function. No-op on
-- an unknown code.
create or replace function increment_discount_usage(p_code text)
returns void
language sql
as $$
  update discounts set used_count = used_count + 1 where code = p_code;
$$;

-- Only the service-role connection may bump counters — keep the anon /
-- authenticated PostgREST roles out, consistent with the RLS posture below.
revoke execute on function increment_discount_usage(text) from public;
revoke execute on function increment_discount_usage(text) from anon;

create table if not exists admins (
  uid text primary key,
  created_at timestamptz not null default now()
);

create table if not exists audit_log (
  id          uuid primary key default gen_random_uuid(),
  event       text not null,
  summary     text not null default '',
  actor_uid   text,
  actor_email text,
  details     jsonb not null default '{}'::jsonb,
  source      text not null default 'server',
  created     timestamptz not null default now()
);

-- Serves the keyset page: order by created desc, id desc.
create index if not exists audit_log_created_id_desc_idx
  on audit_log (created desc, id desc);
create index if not exists audit_log_actor_idx on audit_log (actor_uid);

create table if not exists newsletter_signups (
  email   text primary key,
  source  text not null default '',
  locale  text not null default '',
  created timestamptz not null default now()
);

create index if not exists newsletter_signups_created_desc_idx
  on newsletter_signups (created desc);

alter table products enable row level security;
alter table product_categories enable row level security;
alter table featured_products enable row level security;
alter table carts enable row level security;
alter table quotes enable row level security;
alter table customer_quotes enable row level security;
alter table orders enable row level security;
alter table customer_orders enable row level security;
alter table customers enable row level security;
alter table customer_addresses enable row level security;
alter table discounts enable row level security;
alter table admins enable row level security;
alter table audit_log enable row level security;
alter table newsletter_signups enable row level security;

COMMERCE_BACKEND=supabase npm run seed