Purchase captured in seconds, points pooled across branches

-- 0002_functions_rls.sql — helper functions, loyalty engine, RLS policies
-- See stages/02_design/output/design.md §4 and §5.

-- ---------------------------------------------------------------------------
-- Identity helpers. SECURITY DEFINER (owned by postgres) so they read profiles
-- without triggering RLS recursion.
-- ---------------------------------------------------------------------------
create or replace function public.current_branch_id()
returns uuid language sql stable security definer set search_path = public as $$
  select branch_id from public.profiles where id = auth.uid();
$$;

create or replace function public.is_admin()
returns boolean language sql stable security definer set search_path = public as $$
  select exists (
    select 1 from public.profiles where id = auth.uid() and role = 'admin'
  );
$$;

-- ---------------------------------------------------------------------------
-- Loyalty engine
-- ---------------------------------------------------------------------------
-- Earn: 1 point per PKR 100 (floor). Set on the row, then mirror into ledger.
create or replace function public.set_purchase_points()
returns trigger language plpgsql security definer set search_path = public as $$
begin
  new.points_earned := floor(new.total_amount / 100);
  return new;
end $$;

create or replace function public.ledger_earn_on_purchase()
returns trigger language plpgsql security definer set search_path = public as $$
begin
  if new.points_earned > 0 then
    insert into public.loyalty_ledger (customer_id, type, points, purchase_id, branch_id)
    values (new.customer_id, 'earn', new.points_earned, new.id, new.branch_id);
  end if;
  return new;
end $$;

drop trigger if exists trg_set_purchase_points on public.purchases;
create trigger trg_set_purchase_points
  before insert on public.purchases
  for each row execute function public.set_purchase_points();

drop trigger if exists trg_ledger_earn on public.purchases;
create trigger trg_ledger_earn
  after insert on public.purchases
  for each row execute function public.ledger_earn_on_purchase();

-- Redeem: 1 point = PKR 1, minimum balance 100 to redeem. Locks the customer's
-- ledger rows so the balance can never go negative under concurrent use.
create or replace function public.redeem_points(p_customer uuid, p_points int)
returns int language plpgsql security definer set search_path = public as $$
declare
  v_balance int;
  v_branch  uuid;
begin
  if p_points <= 0 then
    raise exception 'Points to redeem must be positive';
  end if;

  -- serialize concurrent redemptions for this customer
  perform 1 from public.loyalty_ledger where customer_id = p_customer for update;

  select coalesce(sum(case when type = 'earn' then points else -points end), 0)
    into v_balance
  from public.loyalty_ledger where customer_id = p_customer;

  if v_balance < 100 then
    raise exception 'Minimum 100 points required to redeem (balance: %)', v_balance;
  end if;
  if p_points > v_balance then
    raise exception 'Insufficient points (balance: %, requested: %)', v_balance, p_points;
  end if;

  v_branch := public.current_branch_id();
  if v_branch is null then
    raise exception 'No branch context for redemption';
  end if;

# Alchemist Pharmacy — Customer Database & Loyalty: How It Works

A plain-language guide to what this system is and how every page works. For
setup/deploy steps see [`README.md`](README.md).

---

## 1. What this is

A small, fast web app that lets **pharmacy counter staff record who bought
something, how much they spent, and at which branch** — and runs a **loyalty
points** program on top of that. It sits *alongside* the branches' existing POS
billing software (which we can't access), capturing just the customer + sale
info the pharmacy wants to keep and reward.

**Why it exists**
- Keep a reliable record of customers and their spending across all branches.
- Reward repeat customers with loyalty points that work at *any* branch.
- Give head office real reporting: who's buying, how much, and where.
- Build the purchase history that a future subscriptions product can use.

**What it deliberately does *not* store:** the actual medicines/items on a
receipt. Only the **total amount** is recorded — never line items or drug names.
This keeps the system out of sensitive "health data" territory on purpose.

---

## 2. The two kinds of user

| Role | Who | What they can do |
|------|-----|------------------|
| **Branch** | Counter staff at one branch (one shared login per branch) | Look up/enroll customers, record sales for **their own** branch, redeem points, see **their own** branch's sales |
| **Admin (Head Office)** | The owner / management | Everything a branch can, **plus** see every branch's sales, an all-customers report, and manage branches + branch logins |

Every branch has a unique ID. When a branch is logged in, every sale it records
is automatically stamped with that branch — staff never pick it from a list, so
it can't be mis-attributed.

---

## 3. The technology (in one paragraph)

It's a **Next.js** app hosted on **Vercel** with a **Supabase** (Postgres)
database. The important design choice: **all the security rules live in the
database itself** (Postgres "Row-Level Security"), not just in the app. So even
if the app had a bug, one branch still could not read another branch's sales.
The app is built to be **very lightweight** for slow internet — pages are mostly
plain HTML with almost no JavaScript.

---