-- Registro Clínico Neuro v0.19
-- Reparación idempotente de sincronización.
-- Puede ejecutarse completa aunque certificados o agenda ya existan.
-- No borra pacientes, historias, citas, certificados ni perfiles.

begin;

-- Certificados médicos guardados, corregibles y sincronizables.
-- El contenido conserva la instantánea del paciente y del profesional al emitir.

create table if not exists public.medical_certificates (
  workspace_id uuid not null references public.workspaces(id) on delete cascade,
  id text not null,
  patient_id text not null,
  issued_at date not null,
  data jsonb not null,
  revision integer not null default 1 check (revision >= 1),
  archived boolean not null default false,
  created_by uuid not null references auth.users(id),
  updated_by uuid not null references auth.users(id),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  primary key (workspace_id, id),
  foreign key (workspace_id, patient_id)
    references public.patients(workspace_id, id) on delete cascade
);

create index if not exists medical_certificates_patient_idx
  on public.medical_certificates(workspace_id, patient_id, issued_at desc)
  where not archived;

create table if not exists public.medical_certificate_versions (
  version_id uuid primary key default extensions.gen_random_uuid(),
  workspace_id uuid not null,
  certificate_id text not null,
  patient_id text not null,
  revision integer not null,
  data jsonb not null,
  saved_by uuid not null references auth.users(id),
  saved_at timestamptz not null default now(),
  action text not null default 'correction',
  foreign key (workspace_id, certificate_id)
    references public.medical_certificates(workspace_id, id) on delete cascade
);

create index if not exists medical_certificate_versions_lookup_idx
  on public.medical_certificate_versions(
    workspace_id,
    certificate_id,
    revision desc
  );

alter table public.medical_certificates enable row level security;
alter table public.medical_certificate_versions enable row level security;

drop policy if exists medical_certificates_select_member on public.medical_certificates;

create policy medical_certificates_select_member
  on public.medical_certificates
  for select using (public.is_workspace_member(workspace_id));

drop policy if exists medical_certificate_versions_select_member on public.medical_certificate_versions;

create policy medical_certificate_versions_select_member
  on public.medical_certificate_versions
  for select using (public.is_workspace_member(workspace_id));

create or replace function public.save_medical_certificate(
  target_workspace uuid,
  certificate_id text,
  target_patient_id text,
  expected_revision integer,
  certificate_data jsonb
)
returns public.medical_certificates
language plpgsql
security definer
set search_path = public
as $$
declare
  current_record public.medical_certificates;
  saved_record public.medical_certificates;
  parsed_issued_at date;
begin
  if auth.uid() is null or not public.can_write_workspace(target_workspace) then
    raise exception 'workspace_access_denied' using errcode = '42501';
  end if;

  if not exists (
    select 1
    from public.patients
    where workspace_id = target_workspace
      and id = target_patient_id
      and not archived
  ) then
    raise exception 'patient_not_found' using errcode = '23503';
  end if;

  parsed_issued_at :=
    (certificate_data ->> 'issuedAt')::timestamptz::date;

  select * into current_record
  from public.medical_certificates
  where workspace_id = target_workspace and id = certificate_id
  for update;

  if not found then
    if expected_revision <> 0 then
      raise exception 'revision_conflict' using errcode = '40001';
    end if;
    insert into public.medical_certificates(
      workspace_id,
      id,
      patient_id,
      issued_at,
      data,
      revision,
      created_by,
      updated_by
    ) values (
      target_workspace,
      certificate_id,
      target_patient_id,
      parsed_issued_at,
      certificate_data,
      1,
      auth.uid(),
      auth.uid()
    ) returning * into saved_record;
  else
    if current_record.revision <> expected_revision then
      raise exception 'revision_conflict' using errcode = '40001';
    end if;
    insert into public.medical_certificate_versions(
      workspace_id,
      certificate_id,
      patient_id,
      revision,
      data,
      saved_by
    ) values (
      target_workspace,
      certificate_id,
      current_record.patient_id,
      current_record.revision,
      current_record.data,
      auth.uid()
    );
    update public.medical_certificates
    set patient_id = target_patient_id,
        issued_at = parsed_issued_at,
        data = certificate_data,
        revision = current_record.revision + 1,
        updated_by = auth.uid(),
        updated_at = now()
    where workspace_id = target_workspace and id = certificate_id
    returning * into saved_record;
  end if;

  insert into public.audit_events(
    workspace_id,
    actor_user_id,
    entity_type,
    entity_id,
    action,
    revision
  ) values (
    target_workspace,
    auth.uid(),
    'medical_certificate',
    certificate_id,
    'upsert',
    saved_record.revision
  );

  return saved_record;
end;
$$;

revoke all on function public.save_medical_certificate(
  uuid,
  text,
  text,
  integer,
  jsonb
) from public;

grant execute on function public.save_medical_certificate(
  uuid,
  text,
  text,
  integer,
  jsonb
) to authenticated;

do $$
begin
  if not exists (
    select 1
    from pg_publication_tables
    where pubname = 'supabase_realtime'
      and schemaname = 'public'
      and tablename = 'medical_certificates'
  ) then
    alter publication supabase_realtime
      add table public.medical_certificates;
  end if;
end;
$$;


-- Agenda médica independiente y corrección robusta del nombre del consultorio.

create table if not exists public.medical_appointments (
  workspace_id uuid not null references public.workspaces(id) on delete cascade,
  id text not null,
  patient_id text,
  scheduled_at timestamptz not null,
  data jsonb not null,
  revision integer not null default 1 check (revision >= 1),
  archived boolean not null default false,
  created_by uuid not null references auth.users(id),
  updated_by uuid not null references auth.users(id),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  primary key (workspace_id, id),
  foreign key (workspace_id, patient_id)
    references public.patients(workspace_id, id)
    on delete set null (patient_id)
);

create index if not exists medical_appointments_date_idx
  on public.medical_appointments(workspace_id, scheduled_at)
  where not archived;

create table if not exists public.medical_appointment_versions (
  version_id uuid primary key default extensions.gen_random_uuid(),
  workspace_id uuid not null,
  appointment_id text not null,
  revision integer not null,
  data jsonb not null,
  saved_by uuid not null references auth.users(id),
  saved_at timestamptz not null default now(),
  action text not null default 'correction',
  foreign key (workspace_id, appointment_id)
    references public.medical_appointments(workspace_id, id) on delete cascade
);

create index if not exists medical_appointment_versions_lookup_idx
  on public.medical_appointment_versions(
    workspace_id,
    appointment_id,
    revision desc
  );

alter table public.medical_appointments enable row level security;
alter table public.medical_appointment_versions enable row level security;

drop policy if exists medical_appointments_select_member on public.medical_appointments;

create policy medical_appointments_select_member
  on public.medical_appointments
  for select using (public.is_workspace_member(workspace_id));

drop policy if exists medical_appointment_versions_select_member on public.medical_appointment_versions;

create policy medical_appointment_versions_select_member
  on public.medical_appointment_versions
  for select using (public.is_workspace_member(workspace_id));

create or replace function public.save_medical_appointment(
  target_workspace uuid,
  appointment_id text,
  expected_revision integer,
  appointment_data jsonb
)
returns public.medical_appointments
language plpgsql
security definer
set search_path = public
as $$
declare
  current_record public.medical_appointments;
  saved_record public.medical_appointments;
  parsed_patient_id text;
  parsed_scheduled_at timestamptz;
begin
  if auth.uid() is null or not public.can_write_workspace(target_workspace) then
    raise exception 'workspace_access_denied' using errcode = '42501';
  end if;

  parsed_patient_id := nullif(trim(appointment_data ->> 'patientId'), '');
  parsed_scheduled_at := (appointment_data ->> 'scheduledAt')::timestamptz;

  select * into current_record
  from public.medical_appointments
  where workspace_id = target_workspace and id = appointment_id
  for update;

  if not found then
    if expected_revision <> 0 then
      raise exception 'revision_conflict' using errcode = '40001';
    end if;
    insert into public.medical_appointments(
      workspace_id,
      id,
      patient_id,
      scheduled_at,
      data,
      revision,
      created_by,
      updated_by
    ) values (
      target_workspace,
      appointment_id,
      parsed_patient_id,
      parsed_scheduled_at,
      appointment_data,
      1,
      auth.uid(),
      auth.uid()
    ) returning * into saved_record;
  else
    if current_record.revision <> expected_revision then
      raise exception 'revision_conflict' using errcode = '40001';
    end if;
    insert into public.medical_appointment_versions(
      workspace_id,
      appointment_id,
      revision,
      data,
      saved_by
    ) values (
      target_workspace,
      appointment_id,
      current_record.revision,
      current_record.data,
      auth.uid()
    );
    update public.medical_appointments
    set patient_id = parsed_patient_id,
        scheduled_at = parsed_scheduled_at,
        data = appointment_data,
        revision = current_record.revision + 1,
        updated_by = auth.uid(),
        updated_at = now()
    where workspace_id = target_workspace and id = appointment_id
    returning * into saved_record;
  end if;

  insert into public.audit_events(
    workspace_id,
    actor_user_id,
    entity_type,
    entity_id,
    action,
    revision
  ) values (
    target_workspace,
    auth.uid(),
    'medical_appointment',
    appointment_id,
    'upsert',
    saved_record.revision
  );

  return saved_record;
end;
$$;

revoke all on function public.save_medical_appointment(
  uuid,
  text,
  integer,
  jsonb
) from public;

grant execute on function public.save_medical_appointment(
  uuid,
  text,
  integer,
  jsonb
) to authenticated;

-- Se vuelve a declarar para que la corrección del consultorio quede incluida
-- también en el paquete de esta versión.
create or replace function public.rename_workspace(
  target_workspace uuid,
  new_name text
)
returns text
language plpgsql
security definer
set search_path = public
as $$
declare
  cleaned_name text := trim(new_name);
begin
  if auth.uid() is null then
    raise exception 'workspace_access_denied' using errcode = '42501';
  end if;
  if char_length(cleaned_name) < 2 or char_length(cleaned_name) > 160 then
    raise exception 'workspace_name_invalid' using errcode = '22023';
  end if;
  if not exists (
    select 1
    from public.workspace_members member
    where member.workspace_id = target_workspace
      and member.user_id = auth.uid()
      and member.active
      and member.role = 'owner'
  ) then
    raise exception 'workspace_owner_required' using errcode = '42501';
  end if;
  update public.workspaces
  set name = cleaned_name,
      updated_at = now()
  where id = target_workspace;
  if not found then
    raise exception 'workspace_not_found' using errcode = 'P0002';
  end if;
  insert into public.audit_events(
    workspace_id,
    actor_user_id,
    entity_type,
    entity_id,
    action
  ) values (
    target_workspace,
    auth.uid(),
    'workspace',
    target_workspace::text,
    'rename'
  );
  return cleaned_name;
end;
$$;

revoke all on function public.rename_workspace(uuid, text) from public;
grant execute on function public.rename_workspace(uuid, text) to authenticated;

do $$
begin
  if not exists (
    select 1
    from pg_publication_tables
    where pubname = 'supabase_realtime'
      and schemaname = 'public'
      and tablename = 'medical_appointments'
  ) then
    alter publication supabase_realtime
      add table public.medical_appointments;
  end if;
end;
$$;


-- Sincronización del perfil profesional individual.
-- Cada usuario conserva su propio membrete y datos de receta dentro del consultorio.

create or replace function public.save_professional_profile(
  target_workspace uuid,
  profile_data jsonb
)
returns public.professional_profiles
language plpgsql
security definer
set search_path = public
as $$
declare
  saved_record public.professional_profiles;
begin
  if auth.uid() is null or not public.can_write_workspace(target_workspace) then
    raise exception 'workspace_access_denied' using errcode = '42501';
  end if;

  insert into public.professional_profiles(
    workspace_id, user_id, data, revision
  ) values (
    target_workspace, auth.uid(), profile_data, 1
  )
  on conflict (workspace_id, user_id) do update
  set data = excluded.data,
      revision = public.professional_profiles.revision + 1,
      updated_at = now()
  returning * into saved_record;

  insert into public.audit_events(
    workspace_id, actor_user_id, entity_type, entity_id, action, revision
  ) values (
    target_workspace, auth.uid(), 'professional_profile', auth.uid()::text,
    'upsert', saved_record.revision
  );

  return saved_record;
end;
$$;

revoke all on function public.save_professional_profile(uuid, jsonb) from public;
grant execute on function public.save_professional_profile(uuid, jsonb)
  to authenticated;


-- Registro Clínico Neuro v0.18
-- Borrado recuperable de expedientes y propagación de la baja a todos los dispositivos.

create or replace function public.save_patient(
  target_workspace uuid,
  patient_id text,
  expected_revision integer,
  patient_data jsonb
)
returns public.patients
language plpgsql
security definer
set search_path = public
as $$
declare
  current_record public.patients;
  saved_record public.patients;
  wants_archive boolean := coalesce((patient_data ->> 'archived')::boolean, false);
  normalized_data jsonb := jsonb_set(
    patient_data,
    '{archived}',
    to_jsonb(coalesce((patient_data ->> 'archived')::boolean, false)),
    true
  );
begin
  if auth.uid() is null or not public.can_write_workspace(target_workspace) then
    raise exception 'workspace_access_denied' using errcode = '42501';
  end if;

  select * into current_record
  from public.patients
  where workspace_id = target_workspace and id = patient_id
  for update;

  if not found then
    if expected_revision <> 0 then
      raise exception 'revision_conflict' using errcode = '40001';
    end if;
    insert into public.patients(
      workspace_id,
      id,
      data,
      revision,
      archived,
      created_by,
      updated_by
    ) values (
      target_workspace,
      patient_id,
      normalized_data,
      1,
      wants_archive,
      auth.uid(),
      auth.uid()
    ) returning * into saved_record;
  else
    if current_record.revision <> expected_revision then
      raise exception 'revision_conflict' using errcode = '40001';
    end if;
    insert into public.patient_versions(
      workspace_id,
      patient_id,
      revision,
      data,
      saved_by,
      action
    ) values (
      target_workspace,
      patient_id,
      current_record.revision,
      current_record.data,
      auth.uid(),
      case when wants_archive then 'archive' else 'correction' end
    );
    update public.patients
    set data = normalized_data,
        archived = wants_archive,
        revision = current_record.revision + 1,
        updated_by = auth.uid(),
        updated_at = now()
    where workspace_id = target_workspace and id = patient_id
    returning * into saved_record;
  end if;

  if wants_archive then
    update public.visits as target
    set archived = true,
        updated_by = auth.uid(),
        updated_at = now()
    where target.workspace_id = target_workspace
      and target.patient_id = save_patient.patient_id;

    update public.medical_certificates as target
    set archived = true,
        updated_by = auth.uid(),
        updated_at = now()
    where target.workspace_id = target_workspace
      and target.patient_id = save_patient.patient_id;

    update public.medical_appointments as target
    set archived = true,
        updated_by = auth.uid(),
        updated_at = now()
    where target.workspace_id = target_workspace
      and target.patient_id = save_patient.patient_id;
  end if;

  insert into public.audit_events(
    workspace_id,
    actor_user_id,
    entity_type,
    entity_id,
    action,
    revision
  ) values (
    target_workspace,
    auth.uid(),
    'patient',
    patient_id,
    case when wants_archive then 'archive' else 'upsert' end,
    saved_record.revision
  );

  return saved_record;
end;
$$;

revoke all on function public.save_patient(uuid, text, integer, jsonb) from public;
grant execute on function public.save_patient(uuid, text, integer, jsonb) to authenticated;


commit;

select 'Reparación v0.19 aplicada correctamente' as resultado;

