-- =====================================================================
-- 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]);

-- =====================================================================
-- Note: Material library SEED DATA is inserted from the app on first run
-- (src/materialLibrary.js -> seedIfEmpty()) so it stays in sync with the
-- calculation engine's naming conventions. See that file for the full list.
-- =====================================================================

-- =====================================================================
-- DEFAULT ADMIN ACCOUNT (TESTING ONLY — REMOVE/CHANGE BEFORE GOING LIVE)
--
-- Login (in the app): username "admin" / password "78611"
-- Real backend email: admin@rightwayint.com  (auto-confirmed, no email
--   verification needed). The app's login screen auto-appends
--   "@rightwayint.com" if you just type "admin" with no "@" — that's a
--   convenience wired into src/app.js, not a Supabase feature.
--
-- IMPORTANT: this inserts directly into Supabase's internal auth.users /
-- auth.identities tables, which is an UNSUPPORTED, undocumented method
-- (Supabase's official way is Dashboard > Authentication > Add User, or
-- signing up through the app). It works on current Supabase Postgres
-- versions but could break on a future Supabase upgrade. Treat this as
-- a throwaway testing account — change the password (or delete this
-- user and create a real one) before this app is used by anyone else.
-- =====================================================================
do $$
declare
  new_user_id uuid := gen_random_uuid();
begin
  -- Skip entirely if this admin already exists (safe to re-run the file)
  if not exists (select 1 from auth.users where email = 'admin@rightwayint.com') then

    insert into auth.users (
      instance_id, id, aud, role, email, encrypted_password,
      email_confirmed_at, confirmation_token, recovery_token,
      email_change_token_new, email_change,
      raw_app_meta_data, raw_user_meta_data,
      is_super_admin, created_at, updated_at
    ) values (
      '00000000-0000-0000-0000-000000000000',
      new_user_id,
      'authenticated',
      'authenticated',
      'admin@rightwayint.com',
      crypt('78611', gen_salt('bf')),
      now(), '', '', '', '',
      '{"provider":"email","providers":["email"]}',
      '{"full_name":"Admin"}',
      false, now(), now()
    );

    insert into auth.identities (
      id, user_id, provider_id, identity_data, provider, last_sign_in_at, created_at, updated_at
    ) values (
      gen_random_uuid(), new_user_id, new_user_id::text,
      jsonb_build_object('sub', new_user_id::text, 'email', 'admin@rightwayint.com'),
      'email', now(), now(), now()
    );

    insert into profiles (id, full_name, role)
    values (new_user_id, 'Admin', 'admin')
    on conflict (id) do update set role = 'admin';

  else
    -- Already exists (e.g. file re-run) — just make sure it's still admin.
    insert into profiles (id, full_name, role)
    select id, 'Admin', 'admin' from auth.users where email = 'admin@rightwayint.com'
    on conflict (id) do update set role = 'admin';
  end if;
end $$;