/*
-- 1) Crear schema si no existe (no falla si ya existe)
CREATE SCHEMA IF NOT EXISTS demo;

-- 2) Apuntar el search_path al tenant
SET search_path TO demo;
*/
/* 02_relations_fk.sql
   Ejecutar luego del 01.
*/

-- Empresa
ALTER TABLE empresa_config
  ADD CONSTRAINT fk_empresa_config_empresa
  FOREIGN KEY (empresa_id) REFERENCES empresa(id) ON DELETE CASCADE;

-- Roles / permisos
ALTER TABLE rol_permiso
  ADD CONSTRAINT fk_rol_permiso_rol
  FOREIGN KEY (rol_id) REFERENCES rol(id) ON DELETE CASCADE;

ALTER TABLE rol_permiso
  ADD CONSTRAINT fk_rol_permiso_permiso
  FOREIGN KEY (permiso_id) REFERENCES permiso(id) ON DELETE CASCADE;

ALTER TABLE usuario
  ADD CONSTRAINT fk_usuario_rol
  FOREIGN KEY (rol_id) REFERENCES rol(id) ON DELETE RESTRICT;

-- Sucursal y usuarios
ALTER TABLE sucursal
  ADD CONSTRAINT fk_sucursal_empresa
  FOREIGN KEY (empresa_id) REFERENCES empresa(id) ON DELETE RESTRICT;

ALTER TABLE sucursal
  ADD CONSTRAINT fk_sucursal_responsable
  FOREIGN KEY (responsable_usuario_id) REFERENCES usuario(id) ON DELETE SET NULL;

ALTER TABLE usuario_sucursal
  ADD CONSTRAINT fk_usuario_sucursal_usuario
  FOREIGN KEY (usuario_id) REFERENCES usuario(id) ON DELETE CASCADE;

ALTER TABLE usuario_sucursal
  ADD CONSTRAINT fk_usuario_sucursal_sucursal
  FOREIGN KEY (sucursal_id) REFERENCES sucursal(id) ON DELETE CASCADE;

ALTER TABLE user_session
  ADD CONSTRAINT fk_user_session_usuario
  FOREIGN KEY (usuario_id) REFERENCES usuario(id) ON DELETE CASCADE;

-- Series / impresión
ALTER TABLE serie_documento
  ADD CONSTRAINT fk_serie_sucursal
  FOREIGN KEY (sucursal_id) REFERENCES sucursal(id) ON DELETE CASCADE;

ALTER TABLE serie_documento
  ADD CONSTRAINT fk_serie_tipo_doc
  FOREIGN KEY (tipo_documento_id) REFERENCES tipo_documento(id) ON DELETE RESTRICT;

ALTER TABLE printer_profile
  ADD CONSTRAINT fk_printer_empresa
  FOREIGN KEY (empresa_id) REFERENCES empresa(id) ON DELETE CASCADE;

ALTER TABLE sucursal_printer
  ADD CONSTRAINT fk_sucprinter_sucursal
  FOREIGN KEY (sucursal_id) REFERENCES sucursal(id) ON DELETE CASCADE;

ALTER TABLE sucursal_printer
  ADD CONSTRAINT fk_sucprinter_tipo_doc
  FOREIGN KEY (tipo_documento_id) REFERENCES tipo_documento(id) ON DELETE RESTRICT;

ALTER TABLE sucursal_printer
  ADD CONSTRAINT fk_sucprinter_profile
  FOREIGN KEY (printer_profile_id) REFERENCES printer_profile(id) ON DELETE RESTRICT;

-- Clientes / proveedores
ALTER TABLE cliente
  ADD CONSTRAINT fk_cliente_empresa
  FOREIGN KEY (empresa_id) REFERENCES empresa(id) ON DELETE RESTRICT;

ALTER TABLE proveedor
  ADD CONSTRAINT fk_proveedor_empresa
  FOREIGN KEY (empresa_id) REFERENCES empresa(id) ON DELETE RESTRICT;

-- Productos
ALTER TABLE categoria_producto
  ADD CONSTRAINT fk_categoria_empresa
  FOREIGN KEY (empresa_id) REFERENCES empresa(id) ON DELETE RESTRICT;

ALTER TABLE producto
  ADD CONSTRAINT fk_producto_empresa
  FOREIGN KEY (empresa_id) REFERENCES empresa(id) ON DELETE RESTRICT;

ALTER TABLE producto
  ADD CONSTRAINT fk_producto_categoria
  FOREIGN KEY (categoria_id) REFERENCES categoria_producto(id) ON DELETE SET NULL;

ALTER TABLE producto
  ADD CONSTRAINT fk_producto_unidad
  FOREIGN KEY (unidad_id) REFERENCES unidad_medida(id) ON DELETE RESTRICT;

ALTER TABLE producto_principio_activo
  ADD CONSTRAINT fk_prod_pa_producto
  FOREIGN KEY (producto_id) REFERENCES producto(id) ON DELETE CASCADE;

ALTER TABLE producto_principio_activo
  ADD CONSTRAINT fk_prod_pa_principio
  FOREIGN KEY (principio_activo_id) REFERENCES principio_activo(id) ON DELETE RESTRICT;

ALTER TABLE producto_presentacion
  ADD CONSTRAINT fk_presentacion_producto
  FOREIGN KEY (producto_id) REFERENCES producto(id) ON DELETE CASCADE;

ALTER TABLE producto_presentacion
  ADD CONSTRAINT fk_presentacion_unidad
  FOREIGN KEY (unidad_id) REFERENCES unidad_medida(id) ON DELETE RESTRICT;

-- Ubicación / lotes / stock
ALTER TABLE ubicacion
  ADD CONSTRAINT fk_ubicacion_sucursal
  FOREIGN KEY (sucursal_id) REFERENCES sucursal(id) ON DELETE CASCADE;

ALTER TABLE lote
  ADD CONSTRAINT fk_lote_producto
  FOREIGN KEY (producto_id) REFERENCES producto(id) ON DELETE CASCADE;

ALTER TABLE lote
  ADD CONSTRAINT fk_lote_proveedor
  FOREIGN KEY (proveedor_id) REFERENCES proveedor(id) ON DELETE RESTRICT;

ALTER TABLE stock_lote_ubicacion
  ADD CONSTRAINT fk_stock_sucursal
  FOREIGN KEY (sucursal_id) REFERENCES sucursal(id) ON DELETE CASCADE;

ALTER TABLE stock_lote_ubicacion
  ADD CONSTRAINT fk_stock_ubicacion
  FOREIGN KEY (ubicacion_id) REFERENCES ubicacion(id) ON DELETE RESTRICT;

ALTER TABLE stock_lote_ubicacion
  ADD CONSTRAINT fk_stock_lote
  FOREIGN KEY (lote_id) REFERENCES lote(id) ON DELETE CASCADE;

ALTER TABLE mov_inventario
  ADD CONSTRAINT fk_movinv_empresa
  FOREIGN KEY (empresa_id) REFERENCES empresa(id) ON DELETE RESTRICT;

ALTER TABLE mov_inventario
  ADD CONSTRAINT fk_movinv_sucursal
  FOREIGN KEY (sucursal_id) REFERENCES sucursal(id) ON DELETE RESTRICT;

ALTER TABLE mov_inventario
  ADD CONSTRAINT fk_movinv_producto
  FOREIGN KEY (producto_id) REFERENCES producto(id) ON DELETE RESTRICT;

ALTER TABLE mov_inventario
  ADD CONSTRAINT fk_movinv_lote
  FOREIGN KEY (lote_id) REFERENCES lote(id) ON DELETE SET NULL;

ALTER TABLE mov_inventario
  ADD CONSTRAINT fk_movinv_ubicacion
  FOREIGN KEY (ubicacion_id) REFERENCES ubicacion(id) ON DELETE SET NULL;

-- Listas de precio
ALTER TABLE lista_precio
  ADD CONSTRAINT fk_lista_empresa
  FOREIGN KEY (empresa_id) REFERENCES empresa(id) ON DELETE CASCADE;

ALTER TABLE lista_precio_item
  ADD CONSTRAINT fk_lpi_lista
  FOREIGN KEY (lista_id) REFERENCES lista_precio(id) ON DELETE CASCADE;

ALTER TABLE lista_precio_item
  ADD CONSTRAINT fk_lpi_producto
  FOREIGN KEY (producto_id) REFERENCES producto(id) ON DELETE CASCADE;

ALTER TABLE sucursal_lista_precio
  ADD CONSTRAINT fk_slp_sucursal
  FOREIGN KEY (sucursal_id) REFERENCES sucursal(id) ON DELETE CASCADE;

ALTER TABLE sucursal_lista_precio
  ADD CONSTRAINT fk_slp_lista
  FOREIGN KEY (lista_id) REFERENCES lista_precio(id) ON DELETE RESTRICT;

-- Caja
ALTER TABLE caja
  ADD CONSTRAINT fk_caja_sucursal
  FOREIGN KEY (sucursal_id) REFERENCES sucursal(id) ON DELETE CASCADE;

ALTER TABLE arqueo_caja
  ADD CONSTRAINT fk_arqueo_caja
  FOREIGN KEY (caja_id) REFERENCES caja(id) ON DELETE RESTRICT;

ALTER TABLE arqueo_caja
  ADD CONSTRAINT fk_arqueo_usuario
  FOREIGN KEY (usuario_id) REFERENCES usuario(id) ON DELETE RESTRICT;

ALTER TABLE mov_caja
  ADD CONSTRAINT fk_movcaja_arqueo
  FOREIGN KEY (arqueo_id) REFERENCES arqueo_caja(id) ON DELETE CASCADE;

ALTER TABLE mov_caja
  ADD CONSTRAINT fk_movcaja_mediopago
  FOREIGN KEY (medio_pago_id) REFERENCES medio_pago(id) ON DELETE RESTRICT;

-- Cotizaciones
ALTER TABLE cotizacion
  ADD CONSTRAINT fk_cot_empresa
  FOREIGN KEY (empresa_id) REFERENCES empresa(id) ON DELETE RESTRICT;

ALTER TABLE cotizacion
  ADD CONSTRAINT fk_cot_sucursal
  FOREIGN KEY (sucursal_id) REFERENCES sucursal(id) ON DELETE RESTRICT;

ALTER TABLE cotizacion
  ADD CONSTRAINT fk_cot_cliente
  FOREIGN KEY (cliente_id) REFERENCES cliente(id) ON DELETE SET NULL;

ALTER TABLE cotizacion
  ADD CONSTRAINT fk_cot_usuario
  FOREIGN KEY (usuario_id) REFERENCES usuario(id) ON DELETE RESTRICT;

ALTER TABLE cotizacion_detalle
  ADD CONSTRAINT fk_cotdet_cot
  FOREIGN KEY (cotizacion_id) REFERENCES cotizacion(id) ON DELETE CASCADE;

ALTER TABLE cotizacion_detalle
  ADD CONSTRAINT fk_cotdet_prod
  FOREIGN KEY (producto_id) REFERENCES producto(id) ON DELETE RESTRICT;

ALTER TABLE cotizacion_detalle
  ADD CONSTRAINT fk_cotdet_pres
  FOREIGN KEY (presentacion_id) REFERENCES producto_presentacion(id) ON DELETE RESTRICT;

-- Ventas
ALTER TABLE venta
  ADD CONSTRAINT fk_venta_empresa
  FOREIGN KEY (empresa_id) REFERENCES empresa(id) ON DELETE RESTRICT;

ALTER TABLE venta
  ADD CONSTRAINT fk_venta_sucursal
  FOREIGN KEY (sucursal_id) REFERENCES sucursal(id) ON DELETE RESTRICT;

ALTER TABLE venta
  ADD CONSTRAINT fk_venta_cliente
  FOREIGN KEY (cliente_id) REFERENCES cliente(id) ON DELETE SET NULL;

ALTER TABLE venta
  ADD CONSTRAINT fk_venta_usuario
  FOREIGN KEY (usuario_id) REFERENCES usuario(id) ON DELETE RESTRICT;

ALTER TABLE venta
  ADD CONSTRAINT fk_venta_arqueo
  FOREIGN KEY (arqueo_id) REFERENCES arqueo_caja(id) ON DELETE SET NULL;

ALTER TABLE venta
  ADD CONSTRAINT fk_venta_tipodoc
  FOREIGN KEY (tipo_documento_id) REFERENCES tipo_documento(id) ON DELETE RESTRICT;

ALTER TABLE venta
  ADD CONSTRAINT fk_venta_serie
  FOREIGN KEY (serie_documento_id) REFERENCES serie_documento(id) ON DELETE RESTRICT;

ALTER TABLE venta_detalle
  ADD CONSTRAINT fk_vdet_venta
  FOREIGN KEY (venta_id) REFERENCES venta(id) ON DELETE CASCADE;

ALTER TABLE venta_detalle
  ADD CONSTRAINT fk_vdet_producto
  FOREIGN KEY (producto_id) REFERENCES producto(id) ON DELETE RESTRICT;

ALTER TABLE venta_detalle
  ADD CONSTRAINT fk_vdet_presentacion
  FOREIGN KEY (presentacion_id) REFERENCES producto_presentacion(id) ON DELETE RESTRICT;

ALTER TABLE venta_detalle
  ADD CONSTRAINT fk_vdet_lote
  FOREIGN KEY (lote_id) REFERENCES lote(id) ON DELETE SET NULL;

ALTER TABLE venta_detalle
  ADD CONSTRAINT fk_vdet_ubicacion
  FOREIGN KEY (ubicacion_id) REFERENCES ubicacion(id) ON DELETE SET NULL;

ALTER TABLE venta_pago
  ADD CONSTRAINT fk_vp_venta
  FOREIGN KEY (venta_id) REFERENCES venta(id) ON DELETE CASCADE;

ALTER TABLE venta_pago
  ADD CONSTRAINT fk_vp_mp
  FOREIGN KEY (medio_pago_id) REFERENCES medio_pago(id) ON DELETE RESTRICT;

ALTER TABLE venta_controlado
  ADD CONSTRAINT fk_vc_venta
  FOREIGN KEY (venta_id) REFERENCES venta(id) ON DELETE CASCADE;

-- Compras
ALTER TABLE solicitud_compra
  ADD CONSTRAINT fk_sc_empresa
  FOREIGN KEY (empresa_id) REFERENCES empresa(id) ON DELETE RESTRICT;

ALTER TABLE solicitud_compra
  ADD CONSTRAINT fk_sc_sucursal
  FOREIGN KEY (sucursal_id) REFERENCES sucursal(id) ON DELETE RESTRICT;

ALTER TABLE solicitud_compra
  ADD CONSTRAINT fk_sc_usuario
  FOREIGN KEY (usuario_id) REFERENCES usuario(id) ON DELETE RESTRICT;

ALTER TABLE solicitud_compra_detalle
  ADD CONSTRAINT fk_scd_sol
  FOREIGN KEY (solicitud_id) REFERENCES solicitud_compra(id) ON DELETE CASCADE;

ALTER TABLE solicitud_compra_detalle
  ADD CONSTRAINT fk_scd_prod
  FOREIGN KEY (producto_id) REFERENCES producto(id) ON DELETE RESTRICT;

ALTER TABLE compra
  ADD CONSTRAINT fk_comp_empresa
  FOREIGN KEY (empresa_id) REFERENCES empresa(id) ON DELETE RESTRICT;

ALTER TABLE compra
  ADD CONSTRAINT fk_comp_sucursal
  FOREIGN KEY (sucursal_id) REFERENCES sucursal(id) ON DELETE RESTRICT;

ALTER TABLE compra
  ADD CONSTRAINT fk_comp_prov
  FOREIGN KEY (proveedor_id) REFERENCES proveedor(id) ON DELETE RESTRICT;

ALTER TABLE compra
  ADD CONSTRAINT fk_comp_usuario
  FOREIGN KEY (usuario_id) REFERENCES usuario(id) ON DELETE RESTRICT;

ALTER TABLE compra_detalle
  ADD CONSTRAINT fk_cd_compra
  FOREIGN KEY (compra_id) REFERENCES compra(id) ON DELETE CASCADE;

ALTER TABLE compra_detalle
  ADD CONSTRAINT fk_cd_producto
  FOREIGN KEY (producto_id) REFERENCES producto(id) ON DELETE RESTRICT;

ALTER TABLE compra_detalle
  ADD CONSTRAINT fk_cd_lote
  FOREIGN KEY (lote_id) REFERENCES lote(id) ON DELETE SET NULL;

ALTER TABLE compra_detalle
  ADD CONSTRAINT fk_cd_ubicacion
  FOREIGN KEY (ubicacion_id) REFERENCES ubicacion(id) ON DELETE SET NULL;

-- Puntos
ALTER TABLE puntos_regla
  ADD CONSTRAINT fk_pr_empresa
  FOREIGN KEY (empresa_id) REFERENCES empresa(id) ON DELETE RESTRICT;

ALTER TABLE puntos_ledger
  ADD CONSTRAINT fk_pl_cliente
  FOREIGN KEY (cliente_id) REFERENCES cliente(id) ON DELETE CASCADE;

-- Alertas
ALTER TABLE alerta
  ADD CONSTRAINT fk_alerta_empresa
  FOREIGN KEY (empresa_id) REFERENCES empresa(id) ON DELETE CASCADE;

ALTER TABLE alerta
  ADD CONSTRAINT fk_alerta_sucursal
  FOREIGN KEY (sucursal_id) REFERENCES sucursal(id) ON DELETE SET NULL;

ALTER TABLE alerta_destinatario
  ADD CONSTRAINT fk_ad_alerta
  FOREIGN KEY (alerta_id) REFERENCES alerta(id) ON DELETE CASCADE;

ALTER TABLE alerta_destinatario
  ADD CONSTRAINT fk_ad_usuario
  FOREIGN KEY (usuario_id) REFERENCES usuario(id) ON DELETE CASCADE;