-- =========================================================
-- TABLERO CDC - Acceso autorizado por Supabase
-- Ejecutar en Supabase SQL Editor ANTES de desplegar el ZIP.
--
-- Objetivo:
-- - El acceso a la app ya no depende de una lista fija en código.
-- - Los usuarios permitidos viven en public.tablero_cdc_allowed_users.
-- - Para agregar/quitar acceso luego, se actualiza esta tabla en Supabase.
-- - También centraliza permisos especiales usados por la app:
--   * can_manage_internal_pricing
--   * can_delete_projects
--   * can_control_pricing_summary
-- =========================================================

begin;

create table if not exists public.tablero_cdc_allowed_users (
  email text primary key,
  full_name text,
  role text not null default 'user',
  is_active boolean not null default true,
  can_delete_projects boolean not null default false,
  can_manage_internal_pricing boolean not null default false,
  can_control_pricing_summary boolean not null default false,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

comment on table public.tablero_cdc_allowed_users is
  'Lista central de usuarios autorizados para entrar al Tablero CDC.';

comment on column public.tablero_cdc_allowed_users.role is
  'Rol lógico de la app. director_creativo habilita comportamiento especial de Director Creativo.';

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

drop trigger if exists trg_tablero_cdc_allowed_users_updated_at on public.tablero_cdc_allowed_users;

create trigger trg_tablero_cdc_allowed_users_updated_at
before insert or update on public.tablero_cdc_allowed_users
for each row
execute function public.set_tablero_cdc_allowed_users_updated_at();

-- Usuarios autorizados iniciales.
-- Nota: Re-ejecutar este SQL mantiene estos usuarios activos y actualiza sus permisos base.
insert into public.tablero_cdc_allowed_users (
  email,
  full_name,
  role,
  is_active,
  can_delete_projects,
  can_manage_internal_pricing,
  can_control_pricing_summary
)
values
  (
    'iaracena@gomezleemarketing.com',
    'Isaac Daniel Aracena Toribio',
    'admin_it',
    true,
    true,
    true,
    true
  ),
  (
    'jgomez@gomezleemarketing.com',
    'José Leopoldo Gomez',
    'leadership',
    true,
    false,
    false,
    true
  ),
  (
    'gmarrero@gomezleemarketing.com',
    'Gerardo Marrero',
    'director_creativo',
    true,
    true,
    true,
    true
  ),
  (
    'areyes@gomezleemarketing.com',
    'Alicia Thaía Reyes',
    'user',
    true,
    false,
    false,
    false
  ),
  (
    'boliva@gomezleemarketing.com',
    'Bonnie Oliva',
    'user',
    true,
    false,
    false,
    false
  ),
  (
    'obrisceno@gomezleemarketing.com',
    'Oscar Brisceño',
    'user',
    true,
    false,
    false,
    false
  ),
  (
    'lavila01@gomezlee.marketing',
    'Lady Avila Faura',
    'user',
    true,
    false,
    false,
    false
  ),
  (
    'rmarcano@gomezleemarketing.com',
    'Ramon Marcano',
    'user',
    true,
    false,
    false,
    false
  ),
  (
    'lmatos@gomezleemarketing.com',
    'Luis Matos',
    'it',
    true,
    false,
    false,
    false
  ),
  (
    'mgomez@gomezleemarketing.com',
    'Máximo Gomez',
    'leadership',
    true,
    false,
    false,
    false
  )
on conflict (email)
do update set
  full_name = excluded.full_name,
  role = excluded.role,
  is_active = excluded.is_active,
  can_delete_projects = excluded.can_delete_projects,
  can_manage_internal_pricing = excluded.can_manage_internal_pricing,
  can_control_pricing_summary = excluded.can_control_pricing_summary,
  updated_at = now();

alter table public.tablero_cdc_allowed_users enable row level security;

drop policy if exists "tablero_cdc_allowed_users_select_own_active" on public.tablero_cdc_allowed_users;
drop policy if exists "tablero_cdc_allowed_users_no_insert_from_app" on public.tablero_cdc_allowed_users;
drop policy if exists "tablero_cdc_allowed_users_no_update_from_app" on public.tablero_cdc_allowed_users;
drop policy if exists "tablero_cdc_allowed_users_no_delete_from_app" on public.tablero_cdc_allowed_users;

-- La app solo puede leer la fila activa del usuario autenticado.
-- La administración real se hace desde Supabase SQL Editor.
create policy "tablero_cdc_allowed_users_select_own_active"
  on public.tablero_cdc_allowed_users
  for select
  to authenticated
  using (
    is_active = true
    and lower(email) = lower(coalesce(auth.jwt() ->> 'email', ''))
  );

create policy "tablero_cdc_allowed_users_no_insert_from_app"
  on public.tablero_cdc_allowed_users
  for insert
  to authenticated
  with check (false);

create policy "tablero_cdc_allowed_users_no_update_from_app"
  on public.tablero_cdc_allowed_users
  for update
  to authenticated
  using (false)
  with check (false);

create policy "tablero_cdc_allowed_users_no_delete_from_app"
  on public.tablero_cdc_allowed_users
  for delete
  to authenticated
  using (false);

grant select on public.tablero_cdc_allowed_users to authenticated;

-- Funciones auxiliares para políticas/RPC.
create or replace function public.tablero_cdc_current_user_is_allowed()
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select exists (
    select 1
    from public.tablero_cdc_allowed_users u
    where u.email = lower(coalesce(auth.jwt() ->> 'email', ''))
      and u.is_active = true
  );
$$;

create or replace function public.tablero_cdc_current_user_can_control_pricing_summary()
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select exists (
    select 1
    from public.tablero_cdc_allowed_users u
    where u.email = lower(coalesce(auth.jwt() ->> 'email', ''))
      and u.is_active = true
      and u.can_control_pricing_summary = true
  );
$$;

grant execute on function public.tablero_cdc_current_user_is_allowed() to authenticated;
grant execute on function public.tablero_cdc_current_user_can_control_pricing_summary() to authenticated;

-- =========================================================
-- Total Tarifado: configuración pública/privada usando tabla de accesos
-- =========================================================

create table if not exists public.tablero_cdc_app_settings (
  key text primary key,
  value jsonb not null default '{}'::jsonb,
  updated_by uuid references auth.users(id),
  updated_at timestamptz not null default now()
);

insert into public.tablero_cdc_app_settings (key, value)
values ('pricing_summary_public', jsonb_build_object('enabled', false))
on conflict (key) do nothing;

alter table public.tablero_cdc_app_settings enable row level security;

drop policy if exists "tablero_cdc_app_settings_select_authenticated" on public.tablero_cdc_app_settings;
drop policy if exists "tablero_cdc_app_settings_select_allowed_users" on public.tablero_cdc_app_settings;
drop policy if exists "tablero_cdc_app_settings_insert_summary_controllers" on public.tablero_cdc_app_settings;
drop policy if exists "tablero_cdc_app_settings_update_summary_controllers" on public.tablero_cdc_app_settings;
drop policy if exists "tablero_cdc_app_settings_delete_none" on public.tablero_cdc_app_settings;

create policy "tablero_cdc_app_settings_select_allowed_users"
  on public.tablero_cdc_app_settings
  for select
  to authenticated
  using (public.tablero_cdc_current_user_is_allowed());

create policy "tablero_cdc_app_settings_insert_summary_controllers"
  on public.tablero_cdc_app_settings
  for insert
  to authenticated
  with check (
    key = 'pricing_summary_public'
    and public.tablero_cdc_current_user_can_control_pricing_summary()
  );

create policy "tablero_cdc_app_settings_update_summary_controllers"
  on public.tablero_cdc_app_settings
  for update
  to authenticated
  using (
    key = 'pricing_summary_public'
    and public.tablero_cdc_current_user_can_control_pricing_summary()
  )
  with check (
    key = 'pricing_summary_public'
    and public.tablero_cdc_current_user_can_control_pricing_summary()
  );

create policy "tablero_cdc_app_settings_delete_none"
  on public.tablero_cdc_app_settings
  for delete
  to authenticated
  using (false);

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

drop trigger if exists trg_tablero_cdc_app_settings_updated_at on public.tablero_cdc_app_settings;

create trigger trg_tablero_cdc_app_settings_updated_at
before insert or update on public.tablero_cdc_app_settings
for each row
execute function public.set_tablero_cdc_app_settings_updated_at();

grant select on public.tablero_cdc_app_settings to authenticated;

create or replace function public.tablero_cdc_set_pricing_summary_public(p_enabled boolean)
returns jsonb
language plpgsql
security definer
set search_path = public
as $$
declare
  v_enabled boolean := coalesce(p_enabled, false);
begin
  if not public.tablero_cdc_current_user_can_control_pricing_summary() then
    raise exception 'No tienes permiso para cambiar la visibilidad del total tarifado.';
  end if;

  insert into public.tablero_cdc_app_settings (key, value, updated_by, updated_at)
  values (
    'pricing_summary_public',
    jsonb_build_object('enabled', v_enabled),
    auth.uid(),
    now()
  )
  on conflict (key)
  do update set
    value = excluded.value,
    updated_by = auth.uid(),
    updated_at = now();

  return jsonb_build_object('enabled', v_enabled);
end;
$$;

grant execute on function public.tablero_cdc_set_pricing_summary_public(boolean) to authenticated;

-- Reemplaza la RPC de resumen para respetar:
-- 1) que el usuario esté autorizado para la app;
-- 2) que sea controlador del Total Tarifado o que el total esté público.
create or replace function public.tablero_cdc_get_pricing_summary(
  p_tab text default 'todos',
  p_search text default '',
  p_country text default '',
  p_brand text default '',
  p_client text default '',
  p_cm text default ''
)
returns jsonb
language sql
stable
security invoker
set search_path = public
as $$
  with visibility as (
    select coalesce((value ->> 'enabled')::boolean, false) as is_public
    from public.tablero_cdc_app_settings
    where key = 'pricing_summary_public'
  ),
  allowed as (
    select
      public.tablero_cdc_current_user_is_allowed()
      and (
        public.tablero_cdc_current_user_can_control_pricing_summary()
        or coalesce((select is_public from visibility), false)
      ) as can_view_pricing
  ),
  params as (
    select
      lower(coalesce(nullif(trim(p_tab), ''), 'todos')) as tab,
      trim(coalesce(p_search, '')) as search_text,
      trim(coalesce(p_country, '')) as country_filter,
      trim(coalesce(p_brand, '')) as brand_filter,
      trim(coalesce(p_client, '')) as client_filter,
      trim(coalesce(p_cm, '')) as cm_filter
  ),
  base_filtered as (
    select p.*
    from public.tablero_cdc_projects p
    cross join params prm
    where
      case
        when prm.tab in ('abiertos', 'activos', 'active', 'open') then
          coalesce(nullif(trim(p.status), ''), 'Activo') = 'Activo'
        when prm.tab in ('cerrados', 'closed') then
          coalesce(nullif(trim(p.status), ''), 'Activo') <> 'Activo'
        else
          true
      end
      and (
        prm.search_text = ''
        or coalesce(p.title, '') ilike '%' || prm.search_text || '%'
        or coalesce(p.client, '') ilike '%' || prm.search_text || '%'
        or coalesce(p.brand, '') ilike '%' || prm.search_text || '%'
        or coalesce(p.country, '') ilike '%' || prm.search_text || '%'
        or coalesce(p.requested_by, '') ilike '%' || prm.search_text || '%'
        or coalesce(p.country_manager, '') ilike '%' || prm.search_text || '%'
        or coalesce(p.description, '') ilike '%' || prm.search_text || '%'
      )
  ),
  filtered as (
    select bf.*
    from base_filtered bf
    cross join params prm
    where
      (prm.country_filter = '' or coalesce(bf.country, '') ilike '%' || prm.country_filter || '%')
      and (prm.brand_filter = '' or coalesce(bf.brand, '') ilike '%' || prm.brand_filter || '%')
      and (prm.client_filter = '' or coalesce(bf.client, '') ilike '%' || prm.client_filter || '%')
      and (prm.cm_filter = '' or coalesce(bf.country_manager, '') ilike '%' || prm.cm_filter || '%')
  ),
  totals as (
    select
      count(*)::integer as project_count,
      coalesce(
        sum(
          greatest(
            coalesce(nullif(trim(internal_amount::text), '')::numeric, 0),
            0
          )
        ),
        0
      )::numeric as total_amount
    from filtered
  )
  select case
    when (select can_view_pricing from allowed) then
      jsonb_build_object(
        'total_amount', (select total_amount from totals),
        'project_count', (select project_count from totals)
      )
    else
      jsonb_build_object(
        'total_amount', 0,
        'project_count', 0
      )
  end;
$$;

grant execute on function public.tablero_cdc_get_pricing_summary(text, text, text, text, text, text) to authenticated;

-- Intenta habilitar realtime para que el cambio de visible/oculto se refleje sin recargar.
do $$
begin
  alter publication supabase_realtime add table public.tablero_cdc_app_settings;
exception
  when duplicate_object then null;
  when undefined_object then null;
end $$;

commit;

select
  'Acceso Tablero CDC listo' as status,
  count(*) filter (where is_active) as usuarios_activos,
  count(*) filter (where can_control_pricing_summary and is_active) as controlan_total_tarifado,
  count(*) filter (where can_manage_internal_pricing and is_active) as ven_interno_cargado,
  count(*) filter (where can_delete_projects and is_active) as pueden_eliminar
from public.tablero_cdc_allowed_users;