-- ============================================================================
-- TABLERO CDC - ETAPA 1 - HORAS HISTÓRICAS DEL EXCEL
-- Diseño ADITIVO y no destructivo.
--
-- Este script NO altera ni elimina datos de:
--   public.tablero_cdc_projects
--   public.tablero_cdc_project_time_entries
--   public.tablero_cdc_app_metrics_events
--
-- Crea una tabla separada para el histórico y una vista de lectura combinada.
-- ============================================================================

begin;

create table if not exists public.tablero_cdc_time_history (
  id uuid primary key default gen_random_uuid(),
  source_key text not null unique,
  source_workbook text not null,
  source_sheet text not null,
  source_row integer not null check (source_row >= 3),

  work_date date not null,
  project_name text not null,
  client text,
  country text,
  country_manager text,
  worked_by_name text not null,
  task_name text not null,
  duration_minutes integer not null check (duration_minutes > 0),

  request_date date,
  requested_date date,
  delivered_date date,
  execution_status text,
  timing_status text,
  notes text not null default '',
  source_month text,
  source_month_number smallint,
  source_year integer not null,

  -- Se deja NULL en la migración inicial salvo que exista un match determinístico
  -- validado posteriormente. No se crean proyectos falsos y no se fuerza fuzzy matching.
  matched_project_id uuid references public.tablero_cdc_projects(id) on delete set null,
  match_method text,
  match_confidence numeric(5,4),

  raw_data jsonb not null default '{}'::jsonb,
  imported_at timestamptz not null default now(),

  constraint tablero_cdc_time_history_project_not_blank
    check (length(trim(project_name)) > 0),
  constraint tablero_cdc_time_history_worker_not_blank
    check (length(trim(worked_by_name)) > 0),
  constraint tablero_cdc_time_history_task_not_blank
    check (length(trim(task_name)) > 0),
  constraint tablero_cdc_time_history_month_number_valid
    check (source_month_number is null or source_month_number between 1 and 12),
  constraint tablero_cdc_time_history_confidence_valid
    check (match_confidence is null or (match_confidence >= 0 and match_confidence <= 1))
);

comment on table public.tablero_cdc_time_history is
  'Histórico de horas proveniente de Registro horas. Se mantiene separado de los registros creados en la app para no alterar la operación, métricas ni sincronización actual.';

comment on column public.tablero_cdc_time_history.source_key is
  'Clave idempotente de origen. Para esta migración: CD_Latam_Registro_Horas_2024|Registro horas|row:<n>.';

create index if not exists tablero_cdc_time_history_work_date_idx
  on public.tablero_cdc_time_history(work_date desc);

create index if not exists tablero_cdc_time_history_worker_date_idx
  on public.tablero_cdc_time_history(worked_by_name, work_date desc);

create index if not exists tablero_cdc_time_history_project_name_idx
  on public.tablero_cdc_time_history(project_name);

create index if not exists tablero_cdc_time_history_year_idx
  on public.tablero_cdc_time_history(source_year, work_date);

create index if not exists tablero_cdc_time_history_matched_project_idx
  on public.tablero_cdc_time_history(matched_project_id, work_date desc)
  where matched_project_id is not null;

alter table public.tablero_cdc_time_history enable row level security;

drop policy if exists "tablero_cdc_time_history_select_allowed"
  on public.tablero_cdc_time_history;

create policy "tablero_cdc_time_history_select_allowed"
  on public.tablero_cdc_time_history
  for select
  to authenticated
  using (
    exists (
      select 1
      from public.tablero_cdc_allowed_users u
      where lower(trim(u.email)) = lower(trim(coalesce(auth.jwt() ->> 'email', '')))
        and u.is_active = true
    )
  );

-- El frontend solo puede LEER el histórico. La carga se hace una sola vez mediante
-- el workflow administrativo con service role almacenado de forma segura en n8n.
revoke all on public.tablero_cdc_time_history from anon;
revoke insert, update, delete on public.tablero_cdc_time_history from authenticated;
grant select on public.tablero_cdc_time_history to authenticated;

-- Vista unificada para la segunda etapa (dashboard diario por persona).
-- security_invoker mantiene las políticas RLS de las tablas subyacentes.
create or replace view public.tablero_cdc_time_combined
with (security_invoker = true)
as
select
  t.id,
  'app'::text as record_source,
  ('app:' || t.id::text) as source_key,
  null::integer as source_row,
  t.work_date,
  t.project_id,
  p.title as project_name,
  p.client,
  t.country,
  p.country_manager,
  t.created_by_name as worked_by_name,
  t.created_by_email as worked_by_email,
  t.task_name,
  t.duration_minutes,
  null::date as request_date,
  null::date as requested_date,
  null::date as delivered_date,
  null::text as execution_status,
  null::text as timing_status,
  t.notes,
  extract(month from t.work_date)::integer as source_month_number,
  extract(year from t.work_date)::integer as source_year,
  t.created_at,
  t.updated_at
from public.tablero_cdc_project_time_entries t
left join public.tablero_cdc_projects p
  on p.id = t.project_id

union all

select
  h.id,
  'excel_history'::text as record_source,
  h.source_key,
  h.source_row,
  h.work_date,
  h.matched_project_id as project_id,
  h.project_name,
  h.client,
  h.country,
  h.country_manager,
  h.worked_by_name,
  null::text as worked_by_email,
  h.task_name,
  h.duration_minutes,
  h.request_date,
  h.requested_date,
  h.delivered_date,
  h.execution_status,
  h.timing_status,
  h.notes,
  coalesce(h.source_month_number, extract(month from h.work_date)::integer) as source_month_number,
  h.source_year,
  h.imported_at as created_at,
  h.imported_at as updated_at
from public.tablero_cdc_time_history h;

comment on view public.tablero_cdc_time_combined is
  'Lectura unificada de horas actuales de la app + histórico importado del Excel. Diseñada como fuente de la segunda etapa de reportes por persona.';

grant select on public.tablero_cdc_time_combined to authenticated;

commit;

-- ============================================================================
-- VERIFICACIÓN INICIAL (ANTES DEL COMMIT DE n8n)
-- Debe mostrar 0 si esta es la primera ejecución del histórico.
-- ============================================================================
select
  count(*) as historial_registros,
  coalesce(sum(duration_minutes), 0) as historial_minutos
from public.tablero_cdc_time_history;
