-- CRS Nexus v1.0 Foundation schema
-- Run in Supabase SQL Editor after you are ready to initialize the database.

create extension if not exists pgcrypto;

create table if not exists public.profiles (
  id uuid primary key references auth.users(id) on delete cascade,
  email text not null,
  full_name text,
  role text not null default 'viewer' check (role in ('admin','manager','accounting','sales','technician','viewer')),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists public.clients (
  id uuid primary key default gen_random_uuid(),
  company text not null,
  sector text,
  status text,
  priority int,
  region text,
  website text,
  notes text,
  created_by uuid references public.profiles(id),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists public.contacts (
  id uuid primary key default gen_random_uuid(),
  client_id uuid references public.clients(id) on delete cascade,
  name text,
  role_title text,
  email text,
  phone text,
  notes text,
  created_at timestamptz not null default now()
);

create table if not exists public.events (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  start_date date,
  end_date date,
  location text,
  link text,
  status text,
  notes text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists public.expenses (
  id uuid primary key default gen_random_uuid(),
  expense_date date not null,
  category text not null,
  amount numeric(12,2) not null default 0,
  note text,
  client_id uuid references public.clients(id) on delete set null,
  created_by uuid references public.profiles(id),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists public.expense_receipts (
  id uuid primary key default gen_random_uuid(),
  expense_id uuid references public.expenses(id) on delete cascade,
  r2_key text not null,
  file_name text,
  mime_type text,
  size_bytes bigint,
  created_at timestamptz not null default now()
);

create table if not exists public.fundings (
  id uuid primary key default gen_random_uuid(),
  funding_date date,
  amount numeric(12,2) not null default 0,
  kind text not null default 'funding' check (kind in ('funding','interest')),
  note text,
  created_at timestamptz not null default now()
);

create table if not exists public.tax_tasks (
  id uuid primary key default gen_random_uuid(),
  due_date date,
  area text,
  form text,
  period text,
  title text not null,
  note text,
  conditional boolean not null default false,
  done boolean not null default false,
  not_applicable boolean not null default false,
  completed_at timestamptz,
  created_at timestamptz not null default now()
);

create table if not exists public.machines (
  id uuid primary key default gen_random_uuid(),
  client_id uuid references public.clients(id) on delete cascade,
  machine_text text not null,
  serial_number text,
  install_date date,
  notes text,
  created_at timestamptz not null default now()
);

create table if not exists public.tickets (
  id uuid primary key default gen_random_uuid(),
  client_id uuid references public.clients(id) on delete set null,
  machine_id uuid references public.machines(id) on delete set null,
  serial_number text,
  problem text not null,
  priority text default 'medium',
  status text default 'open',
  opened_at timestamptz not null default now(),
  resolved_at timestamptz,
  notes text
);

create table if not exists public.inventory_items (
  id uuid primary key default gen_random_uuid(),
  part_number text,
  description text not null,
  category text,
  quantity numeric(12,2) not null default 0,
  min_quantity numeric(12,2) not null default 0,
  location text,
  supplier text,
  purchase_price numeric(12,2),
  sale_price numeric(12,2),
  lead_time text,
  notes text,
  created_at timestamptz not null default now()
);

create table if not exists public.manager_tasks (
  id uuid primary key default gen_random_uuid(),
  title text not null,
  description text,
  category text,
  due_at timestamptz,
  priority text default 'medium',
  status text default 'open',
  completed_at timestamptz,
  created_by uuid references public.profiles(id),
  created_at timestamptz not null default now()
);

create table if not exists public.documents (
  id uuid primary key default gen_random_uuid(),
  title text not null,
  module text,
  related_id uuid,
  r2_key text,
  file_name text,
  mime_type text,
  notes text,
  created_at timestamptz not null default now()
);

alter table public.profiles enable row level security;
alter table public.clients enable row level security;
alter table public.contacts enable row level security;
alter table public.events enable row level security;
alter table public.expenses enable row level security;
alter table public.expense_receipts enable row level security;
alter table public.fundings enable row level security;
alter table public.tax_tasks enable row level security;
alter table public.machines enable row level security;
alter table public.tickets enable row level security;
alter table public.inventory_items enable row level security;
alter table public.manager_tasks enable row level security;
alter table public.documents enable row level security;

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

-- Foundation policy: any authenticated CRS user can read/write for now.
-- Later we will tighten this by module and role.
do $$
declare t text;
begin
  foreach t in array array['profiles','clients','contacts','events','expenses','expense_receipts','fundings','tax_tasks','machines','tickets','inventory_items','manager_tasks','documents'] loop
    execute format('drop policy if exists crs_authenticated_all on public.%I', t);
    execute format('create policy crs_authenticated_all on public.%I for all to authenticated using (true) with check (true)', t);
  end loop;
end $$;

-- Create profile automatically when an auth user signs up.
create or replace function public.handle_new_user()
returns trigger language plpgsql security definer as $$
begin
  insert into public.profiles (id, email, full_name, role)
  values (new.id, new.email, coalesce(new.raw_user_meta_data->>'full_name',''), 'admin')
  on conflict (id) do nothing;
  return new;
end;
$$;

drop trigger if exists on_auth_user_created on auth.users;
create trigger on_auth_user_created
after insert on auth.users
for each row execute function public.handle_new_user();
