  
    -- Forge Pressure Company Hub — database setup
-- Paste this whole file into Supabase SQL Editor and click Run.

-- ============ Helper checks ============
create or replace function public.is_company()
returns boolean language sql stable as $$
  select lower(coalesce(auth.jwt() ->> 'email', '')) like '%@forgepressure.equipment'
$$;

-- ============ Tables ============
create table public.settings (
  id int primary key default 1 check (id = 1),
  allowance_days numeric not null default 25,
  approver_emails text[] not null default array['giles@forgepressure.equipment']
);
insert into public.settings (id) values (1);

create or replace function public.is_approver()
returns boolean language sql stable as $$
  select exists (
    select 1 from public.settings s
    where lower(coalesce(auth.jwt() ->> 'email','')) in
      (select lower(e) from unnest(s.approver_emails) as e)
  )
$$;

create table public.profiles (
  id uuid primary key references auth.users on delete cascade,
  email text not null,
  name text not null default ''
);

create table public.leave_requests (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references public.profiles (id),
  start_date date not null,
  end_date date not null,
  days numeric not null check (days > 0),
  type text not null default 'Annual leave',
  notes text not null default '',
  status text not null default 'pending'
    check (status in ('pending','approved','declined','cancelled')),
  decided_by uuid references public.profiles (id),
  decided_at timestamptz,
  decision_note text not null default '',
  created_at timestamptz not null default now()
);

create table public.documents (
  id uuid primary key default gen_random_uuid(),
  title text not null,
  category text not null default 'Other',
  status text not null default 'draft'
    check (status in ('draft','approved','archived')),
  review_date date,
  versions jsonb not null default '[]',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

-- ============ Security rules (row level security) ============
alter table public.settings enable row level security;
alter table public.profiles enable row level security;
alter table public.leave_requests enable row level security;
alter table public.documents enable row level security;

-- Settings: everyone in the company can read; only approvers can change.
create policy "company reads settings" on public.settings
  for select using (public.is_company());
create policy "approvers update settings" on public.settings
  for update using (public.is_company() and public.is_approver());

-- Profiles: everyone in the company can see names; you manage only your own row.
create policy "company reads profiles" on public.profiles
  for select using (public.is_company());
create policy "own profile insert" on public.profiles
  for insert with check (public.is_company() and id = auth.uid());
create policy "own profile update" on public.profiles
  for update using (public.is_company() and id = auth.uid());

-- Leave: everyone in the company can see requests; you create only your own;
-- approvers can decide anything, and you can update (cancel) your own.
create policy "company reads leave" on public.leave_requests
  for select using (public.is_company());
create policy "own leave insert" on public.leave_requests
  for insert with check (public.is_company() and user_id = auth.uid());
create policy "leave updates" on public.leave_requests
  for update using (public.is_company() and (public.is_approver() or user_id = auth.uid()));

-- Documents: everyone in the company can read and add; approvers can delete.
create policy "company reads documents" on public.documents
  for select using (public.is_company());
create policy "company adds documents" on public.documents
  for insert with check (public.is_company());
create policy "company updates documents" on public.documents
  for update using (public.is_company());
create policy "approvers delete documents" on public.documents
  for delete using (public.is_company() and public.is_approver());

-- ============ File storage rules ============
-- (Bucket "documents" itself is created in the Storage screen — see the guide.)
create policy "company reads files" on storage.objects
  for select using (bucket_id = 'documents' and public.is_company());
create policy "company uploads files" on storage.objects
  for insert with check (bucket_id = 'documents' and public.is_company());
create policy "approvers delete files" on storage.objects
  for delete using (bucket_id = 'documents' and public.is_approver());
  
  
  
Forge Pressure Equipment

Location

Contact
