create extension if not exists pgcrypto;

create table if not exists public.app_user_roles (
  id uuid primary key default gen_random_uuid(),
  email text not null unique,
  username text unique,
  display_name text,
  role text not null check (role in ('super_user', 'installer')),
  created_by uuid references auth.users(id) on delete set null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

alter table public.app_user_roles add column if not exists username text unique;
alter table public.app_user_roles add column if not exists display_name text;

alter table public.app_user_roles enable row level security;

create or replace function public.current_app_role()
returns text
language sql
stable
security definer
set search_path = public
as $$
  select role
  from public.app_user_roles
  where lower(email) = lower(auth.jwt() ->> 'email')
  limit 1
$$;

create or replace function public.is_super_user()
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select coalesce(public.current_app_role() = 'super_user', false)
$$;

create or replace function public.can_make_conformes()
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select coalesce(public.current_app_role() in ('super_user', 'installer'), false)
$$;

insert into public.app_user_roles (email, role, display_name)
values ('valentinovillaverde@gmail.com', 'super_user', 'Valentino')
on conflict (email) do update set role = excluded.role, display_name = 'Valentino';

drop policy if exists "Users can read own role or super users can read all" on public.app_user_roles;
create policy "Users can read own role or super users can read all"
on public.app_user_roles for select
using (lower(email) = lower(auth.jwt() ->> 'email') or public.is_super_user());

drop policy if exists "Only super users can insert roles" on public.app_user_roles;
create policy "Only super users can insert roles"
on public.app_user_roles for insert
with check (public.is_super_user());

drop policy if exists "Only super users can update roles" on public.app_user_roles;
create policy "Only super users can update roles"
on public.app_user_roles for update
using (public.is_super_user())
with check (public.is_super_user());

drop policy if exists "Only super users can delete roles" on public.app_user_roles;
create policy "Only super users can delete roles"
on public.app_user_roles for delete
using (public.is_super_user());

create table if not exists public.installations (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  report_number text not null,
  install_date date,
  client_name text not null,
  client_id text,
  site_address text,
  client_phone text,
  installer_name text,
  system_power numeric(10, 2),
  panel_count integer not null default 0,
  panel_serials text[] not null default '{}',
  connection_type text,
  connection_notes text,
  inverter_count integer not null default 0,
  inverter_serials text[] not null default '{}',
  notes text,
  signature_data_url text,
  pdf_url text,
  source text not null default 'online',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (user_id, report_number)
);

alter table public.installations enable row level security;

drop policy if exists "Users can read own installations" on public.installations;
create policy "Users can read own installations"
on public.installations for select
using (auth.uid() = user_id or public.is_super_user());

drop policy if exists "Users can insert own installations" on public.installations;
create policy "Users can insert own installations"
on public.installations for insert
with check (auth.uid() = user_id and public.can_make_conformes());

drop policy if exists "Users can update own installations" on public.installations;
create policy "Users can update own installations"
on public.installations for update
using (auth.uid() = user_id or public.is_super_user())
with check ((auth.uid() = user_id and public.can_make_conformes()) or public.is_super_user());

drop policy if exists "Users can delete own installations" on public.installations;
create policy "Users can delete own installations"
on public.installations for delete
using (auth.uid() = user_id or public.is_super_user());

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

drop trigger if exists installations_set_updated_at on public.installations;
create trigger installations_set_updated_at
before update on public.installations
for each row
execute function public.set_updated_at();

drop trigger if exists app_user_roles_set_updated_at on public.app_user_roles;
create trigger app_user_roles_set_updated_at
before update on public.app_user_roles
for each row
execute function public.set_updated_at();