-- Adds explicit receipt linkage and a transaction-safe overpayment guard.
-- Existing payments keep recibo_id NULL; no historical payment is reassigned.
-- No foreign key is intentional: linked payment rows retain their receipt ID
-- and snapshots if a receipt is later deleted by the existing Recibos workflow.

ALTER TABLE public.pagos
  ADD COLUMN IF NOT EXISTS recibo_id bigint,
  ADD COLUMN IF NOT EXISTS recibo_numero_snapshot text,
  ADD COLUMN IF NOT EXISTS recibo_numero_guia_snapshot text,
  ADD COLUMN IF NOT EXISTS recibo_cliente_snapshot text,
  ADD COLUMN IF NOT EXISTS recibo_monto_snapshot numeric;

CREATE INDEX IF NOT EXISTS pagos_recibo_id_fecha_idx
  ON public.pagos (recibo_id, fecha DESC);

-- Preserve legacy ERP behavior for rows that omit receipt linkage, while
-- requiring server-side registration (service_role) for HCE-linked payments.
ALTER TABLE public.pagos ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS pagos_hce_linked_insert_guard ON public.pagos;

CREATE POLICY pagos_hce_linked_insert_guard
  ON public.pagos
  AS RESTRICTIVE
  FOR INSERT
  TO anon, authenticated
  WITH CHECK (recibo_id IS NULL);

-- The legacy ERP operates without a Supabase Auth session (anon role).
-- It keeps full access to historical payments (recibo_id IS NULL) but is
-- blocked from reading, modifying or deleting HCE-linked payments.
DROP POLICY IF EXISTS pagos_legacy_select ON public.pagos;
DROP POLICY IF EXISTS pagos_legacy_insert ON public.pagos;
DROP POLICY IF EXISTS pagos_legacy_update ON public.pagos;
DROP POLICY IF EXISTS pagos_legacy_delete ON public.pagos;

CREATE POLICY pagos_legacy_select
  ON public.pagos
  FOR SELECT
  TO anon
  USING (recibo_id IS NULL);

CREATE POLICY pagos_legacy_insert
  ON public.pagos
  FOR INSERT
  TO anon
  WITH CHECK (recibo_id IS NULL);

CREATE POLICY pagos_legacy_update
  ON public.pagos
  FOR UPDATE
  TO anon
  USING (recibo_id IS NULL)
  WITH CHECK (recibo_id IS NULL);

CREATE POLICY pagos_legacy_delete
  ON public.pagos
  FOR DELETE
  TO anon
  USING (recibo_id IS NULL);

-- The new ERP authenticates users (authenticated role) and needs to read
-- all payment rows to display receipt balances and historical payments.
-- Linked-receipt inserts are still blocked by the restrictive policy above,
-- so HCE-linked payments can only be created server-side via service_role.
DROP POLICY IF EXISTS pagos_hce_select ON public.pagos;

CREATE POLICY pagos_hce_select
  ON public.pagos
  FOR SELECT
  TO authenticated
  USING (true);

CREATE OR REPLACE FUNCTION public.hce_guardar_abono_por_recibo()
RETURNS trigger
LANGUAGE plpgsql
VOLATILE
SECURITY DEFINER
SET search_path = pg_catalog
SET row_security = off
AS $function$
DECLARE
  recibo_actual public.hce_recibos%ROWTYPE;
  total_abonado numeric;
BEGIN
  IF TG_OP = 'DELETE' THEN
    IF OLD.recibo_id IS NOT NULL THEN
      RAISE EXCEPTION 'Los abonos vinculados a recibos no se pueden eliminar'
        USING ERRCODE = '23514';
    END IF;
    RETURN OLD;
  END IF;

  IF TG_OP = 'UPDATE' THEN
    IF OLD.recibo_id IS NOT NULL THEN
      RAISE EXCEPTION 'Los abonos vinculados a recibos son inmutables'
        USING ERRCODE = '23514';
    END IF;
    IF NEW.recibo_id IS NOT NULL THEN
      RAISE EXCEPTION 'Un pago histórico no se puede vincular posteriormente a un recibo'
        USING ERRCODE = '23514';
    END IF;
    RETURN NEW;
  END IF;

  IF NEW.recibo_id IS NULL THEN
    RETURN NEW;
  END IF;

  IF NEW.monto IS NULL OR NEW.monto <= 0 THEN
    RAISE EXCEPTION 'El importe del abono debe ser mayor que cero'
      USING ERRCODE = '23514';
  END IF;

  SELECT r.*
    INTO recibo_actual
    FROM public.hce_recibos AS r
   WHERE r.id = NEW.recibo_id
   FOR UPDATE;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'No existe el recibo indicado'
      USING ERRCODE = '23503';
  END IF;

  IF recibo_actual.monto IS NULL OR recibo_actual.monto <= 0 THEN
    RAISE EXCEPTION 'El recibo no tiene un importe válido para recibir abonos'
      USING ERRCODE = '23514';
  END IF;

  SELECT COALESCE(pg_catalog.sum(p.monto), 0)
    INTO total_abonado
    FROM public.pagos AS p
   WHERE p.recibo_id = NEW.recibo_id;

  IF total_abonado + NEW.monto > recibo_actual.monto THEN
    RAISE EXCEPTION 'El abono supera el saldo pendiente del recibo'
      USING ERRCODE = '23514';
  END IF;

  NEW.recibo_numero_snapshot := recibo_actual.numero;
  NEW.recibo_numero_guia_snapshot := recibo_actual.numero_guia;
  NEW.recibo_cliente_snapshot := recibo_actual.cliente;
  NEW.recibo_monto_snapshot := recibo_actual.monto;

  RETURN NEW;
END;
$function$;

ALTER FUNCTION public.hce_guardar_abono_por_recibo() OWNER TO postgres;

DROP TRIGGER IF EXISTS pagos_guardar_abono_por_recibo ON public.pagos;

CREATE TRIGGER pagos_guardar_abono_por_recibo
BEFORE INSERT OR UPDATE OR DELETE
ON public.pagos
FOR EACH ROW
EXECUTE FUNCTION public.hce_guardar_abono_por_recibo();

REVOKE ALL ON FUNCTION public.hce_guardar_abono_por_recibo()
  FROM PUBLIC;

-- Todos los roles que disparan el trigger necesitan EXECUTE.
-- La función es SECURITY DEFINER (owner postgres), por lo que la lógica de
-- saldo y snapshots se ejecuta con privilegios elevados sin depender del rol.
GRANT EXECUTE ON FUNCTION public.hce_guardar_abono_por_recibo()
  TO anon, authenticated, service_role;