create type user_role as enum ('super_admin','dealer_admin','medewerker');

create table dealers (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  kvk text,
  contact_email text,
  created_at timestamptz default now()
);

create table profiles (
  id uuid primary key references auth.users(id) on delete cascade,
  dealer_id uuid references dealers(id),
  role user_role not null default 'medewerker',
  full_name text,
  created_at timestamptz default now()
);

create table customers (
  id uuid primary key default gen_random_uuid(),
  dealer_id uuid not null references dealers(id),
  name text not null,
  email text,
  phone text,
  created_at timestamptz default now()
);

create table bikes (
  id uuid primary key default gen_random_uuid(),
  dealer_id uuid not null references dealers(id),
  customer_id uuid references customers(id),
  bike_id text unique not null,
  brand text,
  model text,
  bike_type text,
  frame_number text,
  frame_size text,
  build_year int,
  new_value numeric,
  current_value numeric,
  trade_in_value numeric,
  health_score numeric,
  status text default 'Actief',
  created_at timestamptz default now()
);

create table bike_passports (
  id uuid primary key default gen_random_uuid(),
  dealer_id uuid not null references dealers(id),
  bike_id uuid not null references bikes(id),
  passport_number text unique not null,
  version text default 'v0.1',
  pdf_url text,
  qr_url text,
  issued_at timestamptz default now()
);

create table inspections (
  id uuid primary key default gen_random_uuid(),
  dealer_id uuid not null references dealers(id),
  bike_id uuid not null references bikes(id),
  inspector_id uuid references profiles(id),
  inspection_date date default current_date,
  total_score numeric,
  safety_score numeric,
  notes text,
  created_at timestamptz default now()
);

create table inspection_items (
  id uuid primary key default gen_random_uuid(),
  inspection_id uuid not null references inspections(id) on delete cascade,
  category text not null,
  item text not null,
  condition text,
  score numeric,
  advice text
);

alter table dealers enable row level security;
alter table profiles enable row level security;
alter table customers enable row level security;
alter table bikes enable row level security;
alter table bike_passports enable row level security;
alter table inspections enable row level security;
alter table inspection_items enable row level security;

create policy "profiles own" on profiles for select using (id = auth.uid());

create policy "dealers tenant select" on dealers for select using (
  id in (select dealer_id from profiles where profiles.id = auth.uid())
  or exists (select 1 from profiles where profiles.id = auth.uid() and role = 'super_admin')
);

create policy "customers tenant all" on customers for all using (
  dealer_id in (select dealer_id from profiles where profiles.id = auth.uid())
  or exists (select 1 from profiles where profiles.id = auth.uid() and role = 'super_admin')
);

create policy "bikes tenant all" on bikes for all using (
  dealer_id in (select dealer_id from profiles where profiles.id = auth.uid())
  or exists (select 1 from profiles where profiles.id = auth.uid() and role = 'super_admin')
);

create policy "passports tenant all" on bike_passports for all using (
  dealer_id in (select dealer_id from profiles where profiles.id = auth.uid())
  or exists (select 1 from profiles where profiles.id = auth.uid() and role = 'super_admin')
);

create policy "inspections tenant all" on inspections for all using (
  dealer_id in (select dealer_id from profiles where profiles.id = auth.uid())
  or exists (select 1 from profiles where profiles.id = auth.uid() and role = 'super_admin')
);