-- =====================================================================
-- Right Way Fire Pump Design & BOQ ERP — Supabase Schema
-- Run this whole file once in the Supabase SQL editor.
-- =====================================================================

-- ---------- EXTENSIONS ----------
create extension if not exists "uuid-ossp";
create extension if not exists "pgcrypto"; -- needed to hash the default admin password below

-- ---------- TABLES ----------

create table if not exists profiles (
  id uuid primary key references auth.users(id) on delete cascade,
  full_name text,
  company text,
  phone text,
  role text not null default 'user', -- 'user' | 'admin'
  created_at timestamptz not null default now()
);

create table if not exists projects (
  id uuid primary key default uuid_generate_v4(),
  user_id uuid not null references auth.users(id) on delete cascade,
  project_name text not null,
  client_name text,
  building_area float not null,          -- sq ft (normalized)
  area_unit text not null default 'sqft', -- 'sqft' | 'sqm' (original input unit, for display)
  floors int not null default 1,
  rooms int default 0,
  halls int default 0,
  hazard_class text,                     -- 'light' | 'ordinary_1' | 'ordinary_2' | 'extra_1' | 'extra_2'
  selected_systems jsonb not null default '{}'::jsonb, -- {hoseReel:true, fireHose:true, sprinkler:true, riser:'wet'}
  calc_results jsonb,                    -- full snapshot of calcEngine output
  currency text not null default 'PKR',
  status text not null default 'draft',  -- draft | pending_payment | verified
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists materials_library (
  id uuid primary key default uuid_generate_v4(),
  category text not null,      -- 'pipe' | 'fitting' | 'equipment'
  subtype text,                -- e.g. 'elbow_90', 'tee_equal', 'reducer_conc'
  name text not null,
  size text,                   -- e.g. 2" or 4"x2"
  schedule text default 'SCH40',
  unit text not null default 'nos', -- nos | meter | ft | kg
  nfpa_ref text,
  price float not null default 0,
  currency text not null default 'PKR',
  is_system boolean not null default false, -- true = pre-seeded, cannot be deleted by users
  created_by uuid references auth.users(id),
  created_at timestamptz not null default now(),
  unique (name, size, schedule, created_by)
);

create table if not exists project_boq (
  id uuid primary key default uuid_generate_v4(),
  project_id uuid not null references projects(id) on delete cascade,
  material_id uuid references materials_library(id),
  description text,           -- denormalized snapshot (name/size) in case library item changes later
  size text,
  unit text,
  quantity float not null default 0,
  unit_price float not null default 0,
  total_price float generated always as (quantity * unit_price) stored,
  sort_order int default 0,
  created_at timestamptz not null default now()
);

create table if not exists drawings (
  id uuid primary key default uuid_generate_v4(),
  project_id uuid not null references projects(id) on delete cascade,
  file_url text,
  file_type text, -- 'pdf' | 'jpg'
  canvas_data jsonb, -- shapes, markers, lines, calibration {pxPerUnit, unit}
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists payments (
  id uuid primary key default uuid_generate_v4(),
  project_id uuid not null references projects(id) on delete cascade,
  order_id text not null unique,
  amount float not null,
  currency text not null default 'PKR',
  payment_method text not null default 'manual_slip', -- manual_slip | online_sim
  slip_url text,
  is_verified boolean not null default false,
  verified_by uuid references auth.users(id),
  verified_at timestamptz,
  created_at timestamptz not null default now()
);

-- ---------- INDEXES ----------
create index if not exists idx_projects_user on projects(user_id);
create index if not exists idx_boq_project on project_boq(project_id);
create index if not exists idx_drawings_project on drawings(project_id);
create index if not exists idx_payments_project on payments(project_id);
create index if not exists idx_materials_category on materials_library(category);

-- ---------- updated_at TRIGGER ----------
create or replace function set_updated_at() returns trigger as $$
begin
  new.updated_at = now();
  return new;
end;
$$ language plpgsql;

drop trigger if exists trg_projects_updated on projects;
create trigger trg_projects_updated before update on projects
  for each row execute function set_updated_at();

drop trigger if exists trg_drawings_updated on drawings;
create trigger trg_drawings_updated before update on drawings
  for each row execute function set_updated_at();

-- ---------- ROW LEVEL SECURITY ----------
alter table profiles enable row level security;
alter table projects enable row level security;
alter table materials_library enable row level security;
alter table project_boq enable row level security;
alter table drawings enable row level security;
alter table payments enable row level security;

-- Helper: is the current user an admin?
create or replace function is_admin() returns boolean as $$
  select exists (
    select 1 from profiles where id = auth.uid() and role = 'admin'
  );
$$ language sql stable security definer;

-- profiles: user can see/update only their own row; admin can see all
create policy "profiles_select_own_or_admin" on profiles
  for select using (id = auth.uid() or is_admin());
create policy "profiles_update_own" on profiles
  for update using (id = auth.uid());
create policy "profiles_insert_own" on profiles
  for insert with check (id = auth.uid());

-- projects: strictly owner-only (+ admin read for verification workflows)
create policy "projects_select_own_or_admin" on projects
  for select using (user_id = auth.uid() or is_admin());
create policy "projects_insert_own" on projects
  for insert with check (user_id = auth.uid());
create policy "projects_update_own_or_admin" on projects
  for update using (user_id = auth.uid() or is_admin());
create policy "projects_delete_own" on projects
  for delete using (user_id = auth.uid());

-- materials_library: everyone (authenticated) can read; users can only manage their own custom entries; system rows are read-only
create policy "materials_select_all" on materials_library
  for select using (auth.role() = 'authenticated');
create policy "materials_insert_own" on materials_library
  for insert with check (created_by = auth.uid() and is_system = false);
create policy "materials_update_own_or_admin" on materials_library
  for update using ((created_by = auth.uid() and is_system = false) or is_admin());
create policy "materials_delete_own_or_admin" on materials_library
  for delete using ((created_by = auth.uid() and is_system = false) or is_admin());

-- project_boq: access follows the parent project's owner
create policy "boq_select_via_project" on project_boq
  for select using (
    exists (select 1 from projects p where p.id = project_id and (p.user_id = auth.uid() or is_admin()))
  );
create policy "boq_modify_via_project" on project_boq
  for all using (
    exists (select 1 from projects p where p.id = project_id and p.user_id = auth.uid())
  ) with check (
    exists (select 1 from projects p where p.id = project_id and p.user_id = auth.uid())
  );

-- drawings: same pattern
create policy "drawings_select_via_project" on drawings
  for select using (
    exists (select 1 from projects p where p.id = project_id and (p.user_id = auth.uid() or is_admin()))
  );
create policy "drawings_modify_via_project" on drawings
  for all using (
    exists (select 1 from projects p where p.id = project_id and p.user_id = auth.uid())
  ) with check (
    exists (select 1 from projects p where p.id = project_id and p.user_id = auth.uid())
  );

-- payments: owner can insert/view own; only admin can verify (update is_verified)
create policy "payments_select_via_project" on payments
  for select using (
    exists (select 1 from projects p where p.id = project_id and (p.user_id = auth.uid() or is_admin()))
  );
create policy "payments_insert_via_project" on payments
  for insert with check (
    exists (select 1 from projects p where p.id = project_id and p.user_id = auth.uid())
  );
create policy "payments_update_admin_only" on payments
  for update using (is_admin());

-- ---------- STORAGE BUCKETS (run once; ignore error if bucket exists) ----------
-- Create these manually in Supabase Studio > Storage if this insert fails due to permissions:
--   drawings  (public: false)
--   slips     (public: false)
insert into storage.buckets (id, name, public) values ('drawings', 'drawings', false) on conflict (id) do nothing;
insert into storage.buckets (id, name, public) values ('slips', 'slips', false) on conflict (id) do nothing;

create policy "drawings_bucket_owner" on storage.objects for all
  using (bucket_id = 'drawings' and auth.uid()::text = (storage.foldername(name))[1])
  with check (bucket_id = 'drawings' and auth.uid()::text = (storage.foldername(name))[1]);

create policy "slips_bucket_owner" on storage.objects for all
  using (bucket_id = 'slips' and auth.uid()::text = (storage.foldername(name))[1])
  with check (bucket_id = 'slips' and auth.uid()::text = (storage.foldername(name))[1]);



