create extension if not exists pgcrypto;

create table if not exists public.franchises (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  code text not null unique,
  next_report_number integer not null default 1 check (next_report_number > 0),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

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')),
  franchise_id uuid references public.franchises(id) on delete set null,
  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 add column if not exists franchise_id uuid references public.franchises(id) on delete set null;

create table if not exists public.app_user_franchises (
  id uuid primary key default gen_random_uuid(),
  email text not null references public.app_user_roles(email) on delete cascade,
  franchise_id uuid not null references public.franchises(id) on delete cascade,
  created_at timestamptz not null default now(),
  unique (email, franchise_id)
);

insert into public.app_user_franchises (email, franchise_id)
select email, franchise_id
from public.app_user_roles
where franchise_id is not null
on conflict (email, franchise_id) do nothing;

alter table public.franchises enable row level security;
alter table public.app_user_roles enable row level security;
alter table public.app_user_franchises 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.current_franchise_id()
returns uuid
language sql
stable
security definer
set search_path = public
as $$
  select franchise_id
  from (
    select aur.franchise_id
    from public.app_user_roles aur
    where lower(aur.email) = lower(auth.jwt() ->> 'email')
      and aur.franchise_id is not null
    union
    select auf.franchise_id
    from public.app_user_franchises auf
    where lower(auf.email) = lower(auth.jwt() ->> 'email')
  ) assigned
  limit 1
$$;

create or replace function public.current_franchise_ids()
returns uuid[]
language sql
stable
security definer
set search_path = public
as $$
  select coalesce(array_agg(distinct franchise_id), array[]::uuid[])
  from (
    select aur.franchise_id
    from public.app_user_roles aur
    where lower(aur.email) = lower(auth.jwt() ->> 'email')
      and aur.franchise_id is not null
    union
    select auf.franchise_id
    from public.app_user_franchises auf
    where lower(auf.email) = lower(auth.jwt() ->> 'email')
  ) assigned
$$;

create or replace function public.can_access_franchise(target_franchise_id uuid)
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select coalesce(
    public.is_super_user()
    or target_franchise_id = any(public.current_franchise_ids()),
    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)
$$;

create or replace function public.upsert_franchise(
  franchise_name text,
  franchise_code text,
  next_number integer default 1
)
returns table (
  id uuid,
  name text,
  code text,
  next_report_number integer
)
language plpgsql
security definer
set search_path = public
as $$
begin
  if not public.is_super_user() then
    raise exception 'Solo un super user puede crear franquicias.';
  end if;

  franchise_code := regexp_replace(upper(coalesce(franchise_code, '')), '[^A-Z0-9]', '', 'g');
  franchise_name := nullif(trim(coalesce(franchise_name, '')), '');
  next_number := greatest(coalesce(next_number, 1), 1);

  if franchise_name is null or franchise_code = '' then
    raise exception 'Completa nombre y letra/sigla de la franquicia.';
  end if;

  return query
  insert into public.franchises as f (name, code, next_report_number)
  values (franchise_name, franchise_code, next_number)
  on conflict on constraint franchises_code_key do update
  set name = excluded.name,
      next_report_number = excluded.next_report_number,
      updated_at = now()
  returning f.id, f.name, f.code, f.next_report_number;
end;
$$;

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 assigned franchises or super users can read all" on public.franchises;
create policy "Users can read assigned franchises or super users can read all"
on public.franchises for select
using (public.can_access_franchise(id));

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

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

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

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());

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

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

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

drop policy if exists "Only super users can delete franchise assignments" on public.app_user_franchises;
create policy "Only super users can delete franchise assignments"
on public.app_user_franchises 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,
  franchise_id uuid references public.franchises(id) on delete set null,
  report_sequence integer,
  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_type text,
  system_power numeric(10, 2),
  panel_count integer not null default 0,
  panel_nominal_power numeric(10, 2),
  panel_serials text[] not null default '{}',
  inverter_count integer not null default 0,
  inverter_serials text[] not null default '{}',
  inverter_brands text[] not null default '{}',
  inverter_powers numeric[] not null default '{}',
  has_batteries text not null default 'no',
  battery_count integer not null default 0,
  battery_type text,
  batteries_same_brand text,
  battery_brand text,
  battery_brands text[] not null default '{}',
  batteries_same_capacity text,
  battery_capacity numeric(10, 2),
  battery_serials text[] not null default '{}',
  battery_capacities numeric[] not null default '{}',
  dc_breaker_count integer not null default 0,
  dc_breaker_ratings numeric[] not null default '{}',
  dc_spd_count integer not null default 0,
  ac_main_breaker_poles integer,
  ac_main_breaker_current numeric(10, 2),
  ac_differential_installed text,
  ac_differential_poles integer,
  ac_differential_current numeric(10, 2),
  ac_differential_sensitivity numeric(10, 2),
  ac_spd_installed text,
  ground_resistance_ohm numeric(10, 2),
  ground_measurement_method text,
  ground_calibration_number text,
  ground_calibration_date date,
  ground_temperature numeric(10, 2),
  ground_time time,
  ground_humidity numeric(10, 2),
  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 add column if not exists franchise_id uuid references public.franchises(id) on delete set null;
alter table public.installations add column if not exists report_sequence integer;
alter table public.installations add column if not exists system_type text;
alter table public.installations add column if not exists panel_nominal_power numeric(10, 2);
alter table public.installations add column if not exists inverter_brands text[] not null default '{}';
alter table public.installations add column if not exists inverter_powers numeric[] not null default '{}';
alter table public.installations add column if not exists has_batteries text not null default 'no';
alter table public.installations add column if not exists battery_count integer not null default 0;
alter table public.installations add column if not exists battery_type text;
alter table public.installations add column if not exists batteries_same_brand text;
alter table public.installations add column if not exists battery_brand text;
alter table public.installations add column if not exists battery_brands text[] not null default '{}';
alter table public.installations add column if not exists batteries_same_capacity text;
alter table public.installations add column if not exists battery_capacity numeric(10, 2);
alter table public.installations add column if not exists battery_serials text[] not null default '{}';
alter table public.installations add column if not exists battery_capacities numeric[] not null default '{}';
alter table public.installations add column if not exists dc_breaker_count integer not null default 0;
alter table public.installations add column if not exists dc_breaker_ratings numeric[] not null default '{}';
alter table public.installations add column if not exists dc_spd_count integer not null default 0;
alter table public.installations add column if not exists ac_main_breaker_poles integer;
alter table public.installations add column if not exists ac_main_breaker_current numeric(10, 2);
alter table public.installations add column if not exists ac_differential_installed text;
alter table public.installations add column if not exists ac_differential_poles integer;
alter table public.installations add column if not exists ac_differential_current numeric(10, 2);
alter table public.installations add column if not exists ac_differential_sensitivity numeric(10, 2);
alter table public.installations add column if not exists ac_spd_installed text;
alter table public.installations add column if not exists ground_resistance_ohm numeric(10, 2);
alter table public.installations add column if not exists ground_measurement_method text;
alter table public.installations add column if not exists ground_calibration_number text;
alter table public.installations add column if not exists ground_calibration_date date;
alter table public.installations add column if not exists ground_temperature numeric(10, 2);
alter table public.installations add column if not exists ground_time time;
alter table public.installations add column if not exists ground_humidity numeric(10, 2);

notify pgrst, 'reload schema';

create unique index if not exists installations_franchise_report_number_idx
on public.installations (franchise_id, report_number)
where franchise_id is not null;

alter table public.installations enable row level security;

create or replace function public.next_report_number(target_franchise_id uuid)
returns table (
  report_number text,
  report_sequence integer,
  franchise_id uuid,
  franchise_name text,
  franchise_code text
)
language plpgsql
security definer
set search_path = public
as $$
begin
  if target_franchise_id is null then
    raise exception 'Selecciona una franquicia.';
  end if;

  if not public.can_access_franchise(target_franchise_id) then
    raise exception 'No tenes acceso a esta franquicia.';
  end if;

  update public.franchises f
  set next_report_number = f.next_report_number + 1,
      updated_at = now()
  where f.id = target_franchise_id
  returning f.next_report_number - 1, f.id, f.name, f.code
  into report_sequence, franchise_id, franchise_name, franchise_code;

  if report_sequence is null then
    raise exception 'No se encontro la franquicia.';
  end if;

  report_number := franchise_code || '-' || lpad(report_sequence::text, 6, '0');
  return next;
end;
$$;

grant execute on function public.upsert_franchise(text, text, integer) to authenticated;
grant execute on function public.next_report_number(uuid) to authenticated;
grant select, insert, update, delete on public.app_user_franchises to authenticated;

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()
  and public.can_access_franchise(franchise_id)
);

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() and public.can_access_franchise(franchise_id))
  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();

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

notify pgrst, 'reload schema';