-- Ajustes recomendados
SET NAMES utf8mb4;
SET time_zone = '+00:00';
SET sql_mode = 'STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

-- Crear base de datos y usarla
CREATE DATABASE IF NOT EXISTS `mbinvcxp`
  DEFAULT CHARACTER SET utf8mb4
  COLLATE utf8mb4_spanish_ci;
USE `mbinvcxp`;

-- =========================
-- CATALOGOS / MULTI-TENANT
-- =========================
CREATE TABLE clientes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nombre VARCHAR(120) NOT NULL,
  nombre_comercial VARCHAR(120) NULL,
  nit VARCHAR(20) NULL,
  pais_iso2 CHAR(2) NULL,
  moneda_base_id SMALLINT UNSIGNED NULL,
  timezone VARCHAR(64) NOT NULL DEFAULT 'America/Guatemala',
  dominio VARCHAR(120) NULL,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uk_cliente_nombre (nombre),
  UNIQUE KEY uk_cliente_dominio (dominio)
) ENGINE=InnoDB;

CREATE TABLE sitios (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id BIGINT UNSIGNED NOT NULL,
  nombre VARCHAR(120) NOT NULL,
  moneda_base_id SMALLINT UNSIGNED NULL,
  timezone VARCHAR(64) NOT NULL DEFAULT 'America/Guatemala',
  activo TINYINT(1) NOT NULL DEFAULT 1,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_sitio_cliente (cliente_id),
  CONSTRAINT fk_sitio_cliente FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE empresas (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id BIGINT UNSIGNED NOT NULL,
  sitio_id BIGINT UNSIGNED NOT NULL,
  razon_social VARCHAR(160) NOT NULL,
  nit VARCHAR(20) NULL,
  direccion VARCHAR(200) NULL,
  telefono VARCHAR(30) NULL,
  email VARCHAR(120) NULL,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uk_empresa (cliente_id, sitio_id, razon_social),
  KEY idx_empresa_cliente (cliente_id),
  KEY idx_empresa_sitio (sitio_id),
  CONSTRAINT fk_empresa_cliente FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE,
  CONSTRAINT fk_empresa_sitio FOREIGN KEY (sitio_id) REFERENCES sitios(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE sucursales (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id BIGINT UNSIGNED NOT NULL,
  sitio_id BIGINT UNSIGNED NOT NULL,
  empresa_id BIGINT UNSIGNED NOT NULL,
  nombre VARCHAR(160) NOT NULL,
  codigo VARCHAR(30) NULL,
  direccion VARCHAR(200) NULL,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uk_sucursal (cliente_id, sitio_id, empresa_id, nombre),
  KEY idx_sucursal_cliente (cliente_id),
  KEY idx_sucursal_sitio (sitio_id),
  KEY idx_sucursal_empresa (empresa_id),
  CONSTRAINT fk_sucursal_cliente FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE,
  CONSTRAINT fk_sucursal_sitio FOREIGN KEY (sitio_id) REFERENCES sitios(id) ON DELETE CASCADE,
  CONSTRAINT fk_sucursal_empresa FOREIGN KEY (empresa_id) REFERENCES empresas(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE usuarios (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(120) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  nombre VARCHAR(120) NOT NULL,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  superadmin TINYINT(1) NOT NULL DEFAULT 0,
  ultimo_login_en DATETIME NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uk_usuario_email (email)
) ENGINE=InnoDB;

CREATE TABLE roles (
  id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  codigo VARCHAR(40) NOT NULL,
  nombre VARCHAR(80) NOT NULL,
  UNIQUE KEY uk_rol_codigo (codigo)
) ENGINE=InnoDB;

CREATE TABLE usuarios_sitios_roles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  usuario_id BIGINT UNSIGNED NOT NULL,
  cliente_id BIGINT UNSIGNED NOT NULL,
  sitio_id BIGINT UNSIGNED NOT NULL,
  rol_id SMALLINT UNSIGNED NOT NULL,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_usr_sitio_rol (usuario_id, sitio_id, rol_id),
  KEY idx_usr_cliente (usuario_id, cliente_id),
  KEY idx_usr_rol (rol_id),
  CONSTRAINT fk_usrrol_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE,
  CONSTRAINT fk_usrrol_cliente FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE,
  CONSTRAINT fk_usrrol_sitio FOREIGN KEY (sitio_id) REFERENCES sitios(id) ON DELETE CASCADE,
  CONSTRAINT fk_usrrol_rol FOREIGN KEY (rol_id) REFERENCES roles(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE monedas (
  id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  codigo CHAR(3) NOT NULL,
  nombre VARCHAR(40) NOT NULL,
  simbolo VARCHAR(6) NULL,
  UNIQUE KEY uk_moneda_codigo (codigo)
) ENGINE=InnoDB;

CREATE TABLE tipos_cambio (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  moneda_id SMALLINT UNSIGNED NOT NULL,
  fecha DATE NOT NULL,
  tc_venta DECIMAL(18,6) NOT NULL,
  tc_compra DECIMAL(18,6) NOT NULL,
  UNIQUE KEY uk_tc (moneda_id, fecha),
  KEY idx_tc_moneda (moneda_id, fecha),
  CONSTRAINT fk_tc_moneda FOREIGN KEY (moneda_id) REFERENCES monedas(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE impuestos (
  id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id BIGINT UNSIGNED NOT NULL,
  nombre VARCHAR(80) NOT NULL,
  porcentaje DECIMAL(5,2) NOT NULL,
  codigo_sat VARCHAR(30) NULL,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY uk_imp (cliente_id, nombre),
  KEY idx_imp_cliente (cliente_id),
  CONSTRAINT fk_imp_cliente FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE retenciones (
  id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id BIGINT UNSIGNED NOT NULL,
  nombre VARCHAR(80) NOT NULL,
  tipo TINYINT UNSIGNED NOT NULL,
  porcentaje DECIMAL(6,3) NOT NULL,
  base_sobre TINYINT UNSIGNED NOT NULL DEFAULT 1,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY uk_ret (cliente_id, nombre),
  KEY idx_ret_cliente (cliente_id),
  CONSTRAINT fk_ret_cliente FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE centros_costo (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id BIGINT UNSIGNED NOT NULL,
  codigo VARCHAR(20) NOT NULL,
  nombre VARCHAR(120) NOT NULL,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY uk_cc (cliente_id, codigo),
  KEY idx_cc_cliente (cliente_id),
  CONSTRAINT fk_cc_cliente FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE proyectos (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id BIGINT UNSIGNED NOT NULL,
  codigo VARCHAR(20) NOT NULL,
  nombre VARCHAR(160) NOT NULL,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY uk_proy (cliente_id, codigo),
  KEY idx_proy_cliente (cliente_id),
  CONSTRAINT fk_proy_cliente FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE tipos_documento (
  id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id BIGINT UNSIGNED NOT NULL,
  codigo VARCHAR(20) NOT NULL,
  nombre VARCHAR(80) NOT NULL,
  clase TINYINT UNSIGNED NOT NULL,
  afecta_saldo TINYINT(1) NOT NULL DEFAULT 1,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY uk_tipodoc (cliente_id, codigo),
  KEY idx_tipodoc_cliente (cliente_id),
  CONSTRAINT fk_tipodoc_cliente FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE condiciones_pago (
  id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id BIGINT UNSIGNED NOT NULL,
  nombre VARCHAR(80) NOT NULL,
  dias SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY uk_condpago (cliente_id, nombre),
  KEY idx_condpago_cliente (cliente_id),
  CONSTRAINT fk_condpago_cliente FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE bancos (
  id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id BIGINT UNSIGNED NOT NULL,
  nombre VARCHAR(100) NOT NULL,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY uk_banco (cliente_id, nombre),
  KEY idx_banco_cliente (cliente_id),
  CONSTRAINT fk_banco_cliente FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE cuentas_bancarias (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id BIGINT UNSIGNED NOT NULL,
  banco_id SMALLINT UNSIGNED NOT NULL,
  empresa_id BIGINT UNSIGNED NULL,
  numero VARCHAR(34) NOT NULL,
  moneda_id SMALLINT UNSIGNED NOT NULL,
  descripcion VARCHAR(120) NULL,
  activa TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY uk_cta (cliente_id, numero),
  KEY idx_cta_banco (banco_id),
  CONSTRAINT fk_cta_cliente FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE,
  CONSTRAINT fk_cta_banco FOREIGN KEY (banco_id) REFERENCES bancos(id) ON DELETE RESTRICT,
  CONSTRAINT fk_cta_moneda FOREIGN KEY (moneda_id) REFERENCES monedas(id) ON DELETE RESTRICT,
  CONSTRAINT fk_cta_empresa FOREIGN KEY (empresa_id) REFERENCES empresas(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE proveedores (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id BIGINT UNSIGNED NOT NULL,
  codigo VARCHAR(20) NULL,
  nit VARCHAR(20) NOT NULL,
  razon_social VARCHAR(160) NOT NULL,
  contacto VARCHAR(120) NULL,
  telefono VARCHAR(30) NULL,
  email VARCHAR(120) NULL,
  direccion VARCHAR(200) NULL,
  pais_iso2 CHAR(2) NULL,
  clasificacion TINYINT UNSIGNED NOT NULL DEFAULT 1,
  regimen_fiscal VARCHAR(80) NULL,
  aplica_ret_isr TINYINT(1) NOT NULL DEFAULT 0,
  aplica_ret_iva TINYINT(1) NOT NULL DEFAULT 0,
  saldo_inicial DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uk_prov_nit (cliente_id, nit),
  KEY idx_prov_cliente (cliente_id),
  KEY idx_prov_busqueda (cliente_id, razon_social, codigo),
  CONSTRAINT fk_prov_cliente FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =========================
-- DOCUMENTOS CXP
-- =========================
CREATE TABLE documentos_cxp (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id BIGINT UNSIGNED NOT NULL,
  sitio_id BIGINT UNSIGNED NOT NULL,
  empresa_id BIGINT UNSIGNED NULL,
  sucursal_id BIGINT UNSIGNED NULL,
  proveedor_id BIGINT UNSIGNED NOT NULL,
  tipo_documento_id SMALLINT UNSIGNED NOT NULL,
  condicion_pago_id SMALLINT UNSIGNED NULL,
  moneda_id SMALLINT UNSIGNED NOT NULL,
  tipo_cambio DECIMAL(18,6) NOT NULL DEFAULT 1.000000,
  serie VARCHAR(20) NULL,
  numero VARCHAR(30) NOT NULL,
  referencia VARCHAR(60) NULL,
  fecha_emision DATE NOT NULL,
  fecha_recepcion DATE NOT NULL,
  fecha_vencimiento DATE NOT NULL,
  estado TINYINT UNSIGNED NOT NULL DEFAULT 1,
  subtotal DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  impuesto_total DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  retencion_total DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  otros_cargos DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  total_bruto DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  total_neto DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  saldo_pendiente DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  observaciones VARCHAR(300) NULL,
  creado_por_id BIGINT UNSIGNED NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uk_doc_unico (cliente_id, proveedor_id, tipo_documento_id, serie, numero),
  KEY idx_doc_cliente (cliente_id, fecha_emision),
  KEY idx_doc_proveedor (cliente_id, proveedor_id, fecha_vencimiento),
  KEY idx_doc_estado (cliente_id, estado, fecha_vencimiento),
  KEY idx_doc_saldo (cliente_id, saldo_pendiente),
  CONSTRAINT fk_doc_cliente FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE,
  CONSTRAINT fk_doc_sitio FOREIGN KEY (sitio_id) REFERENCES sitios(id) ON DELETE RESTRICT,
  CONSTRAINT fk_doc_empresa FOREIGN KEY (empresa_id) REFERENCES empresas(id) ON DELETE SET NULL,
  CONSTRAINT fk_doc_sucursal FOREIGN KEY (sucursal_id) REFERENCES sucursales(id) ON DELETE SET NULL,
  CONSTRAINT fk_doc_prov FOREIGN KEY (proveedor_id) REFERENCES proveedores(id) ON DELETE RESTRICT,
  CONSTRAINT fk_doc_tipodoc FOREIGN KEY (tipo_documento_id) REFERENCES tipos_documento(id) ON DELETE RESTRICT,
  CONSTRAINT fk_doc_condpago FOREIGN KEY (condicion_pago_id) REFERENCES condiciones_pago(id) ON DELETE SET NULL,
  CONSTRAINT fk_doc_moneda FOREIGN KEY (moneda_id) REFERENCES monedas(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE documentos_cxp_det (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id BIGINT UNSIGNED NOT NULL,
  documento_id BIGINT UNSIGNED NOT NULL,
  linea INT UNSIGNED NOT NULL,
  descripcion VARCHAR(200) NOT NULL,
  cantidad DECIMAL(18,6) NOT NULL DEFAULT 1.000000,
  precio_unitario DECIMAL(18,6) NOT NULL DEFAULT 0.000000,
  descuento_monto DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  impuesto_id SMALLINT UNSIGNED NULL,
  impuesto_monto DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  retencion_id SMALLINT UNSIGNED NULL,
  retencion_monto DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  centro_costo_id BIGINT UNSIGNED NULL,
  proyecto_id BIGINT UNSIGNED NULL,
  total_linea DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  UNIQUE KEY uk_doc_det_linea (documento_id, linea),
  KEY idx_det_doc (cliente_id, documento_id),
  KEY idx_det_cc (cliente_id, centro_costo_id),
  KEY idx_det_proy (cliente_id, proyecto_id),
  CONSTRAINT fk_det_cliente FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE,
  CONSTRAINT fk_det_documento FOREIGN KEY (documento_id) REFERENCES documentos_cxp(id) ON DELETE CASCADE,
  CONSTRAINT fk_det_imp FOREIGN KEY (impuesto_id) REFERENCES impuestos(id) ON DELETE SET NULL,
  CONSTRAINT fk_det_ret FOREIGN KEY (retencion_id) REFERENCES retenciones(id) ON DELETE SET NULL,
  CONSTRAINT fk_det_cc FOREIGN KEY (centro_costo_id) REFERENCES centros_costo(id) ON DELETE SET NULL,
  CONSTRAINT fk_det_proy FOREIGN KEY (proyecto_id) REFERENCES proyectos(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE documentos_cxp_retenciones (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id BIGINT UNSIGNED NOT NULL,
  documento_id BIGINT UNSIGNED NOT NULL,
  retencion_id SMALLINT UNSIGNED NOT NULL,
  base_monto DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  porcentaje DECIMAL(6,3) NOT NULL,
  monto DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  UNIQUE KEY uk_doc_ret (documento_id, retencion_id),
  KEY idx_ret_doc (cliente_id, documento_id),
  CONSTRAINT fk_docret_cliente FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE,
  CONSTRAINT fk_docret_documento FOREIGN KEY (documento_id) REFERENCES documentos_cxp(id) ON DELETE CASCADE,
  CONSTRAINT fk_docret_ret FOREIGN KEY (retencion_id) REFERENCES retenciones(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE documentos_cxp_combustible (
  documento_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  placa VARCHAR(15) NULL,
  kilometraje INT UNSIGNED NULL,
  galones DECIMAL(12,4) NULL,
  precio_galon DECIMAL(18,6) NULL,
  CONSTRAINT fk_comb_doc FOREIGN KEY (documento_id) REFERENCES documentos_cxp(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE adjuntos (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id BIGINT UNSIGNED NOT NULL,
  entidad TINYINT UNSIGNED NOT NULL,
  entidad_id BIGINT UNSIGNED NOT NULL,
  nombre_archivo VARCHAR(160) NOT NULL,
  mime_type VARCHAR(80) NULL,
  ruta_storage VARCHAR(260) NOT NULL,
  tamano_bytes BIGINT UNSIGNED NULL,
  subido_por_id BIGINT UNSIGNED NULL,
  subido_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_adj (cliente_id, entidad, entidad_id),
  CONSTRAINT fk_adj_cliente FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE,
  CONSTRAINT fk_adj_usuario FOREIGN KEY (subido_por_id) REFERENCES usuarios(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- =========================
-- PAGOS / ANTICIPOS
-- =========================
CREATE TABLE pagos (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id BIGINT UNSIGNED NOT NULL,
  sitio_id BIGINT UNSIGNED NOT NULL,
  empresa_id BIGINT UNSIGNED NULL,
  proveedor_id BIGINT UNSIGNED NOT NULL,
  cuenta_bancaria_id BIGINT UNSIGNED NULL,
  moneda_id SMALLINT UNSIGNED NOT NULL,
  tipo_cambio DECIMAL(18,6) NOT NULL DEFAULT 1.000000,
  fecha_pago DATE NOT NULL,
  forma_pago TINYINT UNSIGNED NOT NULL,
  referencia VARCHAR(60) NULL,
  total_pagado DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  estado TINYINT UNSIGNED NOT NULL DEFAULT 1,
  observaciones VARCHAR(300) NULL,
  creado_por_id BIGINT UNSIGNED NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_pago_cliente (cliente_id, fecha_pago),
  KEY idx_pago_prov (cliente_id, proveedor_id, fecha_pago),
  CONSTRAINT fk_pago_cliente FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE,
  CONSTRAINT fk_pago_sitio FOREIGN KEY (sitio_id) REFERENCES sitios(id) ON DELETE RESTRICT,
  CONSTRAINT fk_pago_empresa FOREIGN KEY (empresa_id) REFERENCES empresas(id) ON DELETE SET NULL,
  CONSTRAINT fk_pago_prov FOREIGN KEY (proveedor_id) REFERENCES proveedores(id) ON DELETE RESTRICT,
  CONSTRAINT fk_pago_cta FOREIGN KEY (cuenta_bancaria_id) REFERENCES cuentas_bancarias(id) ON DELETE SET NULL,
  CONSTRAINT fk_pago_moneda FOREIGN KEY (moneda_id) REFERENCES monedas(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE pagos_aplicaciones (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id BIGINT UNSIGNED NOT NULL,
  pago_id BIGINT UNSIGNED NOT NULL,
  documento_id BIGINT UNSIGNED NOT NULL,
  monto_aplicado DECIMAL(18,2) NOT NULL,
  UNIQUE KEY uk_pago_doc (pago_id, documento_id),
  KEY idx_apl_doc (cliente_id, documento_id),
  CONSTRAINT fk_apl_cliente FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE,
  CONSTRAINT fk_apl_pago FOREIGN KEY (pago_id) REFERENCES pagos(id) ON DELETE CASCADE,
  CONSTRAINT fk_apl_documento FOREIGN KEY (documento_id) REFERENCES documentos_cxp(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE anticipos_proveedor (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id BIGINT UNSIGNED NOT NULL,
  proveedor_id BIGINT UNSIGNED NOT NULL,
  moneda_id SMALLINT UNSIGNED NOT NULL,
  tipo_cambio DECIMAL(18,6) NOT NULL DEFAULT 1.000000,
  fecha DATE NOT NULL,
  monto DECIMAL(18,2) NOT NULL,
  saldo_disponible DECIMAL(18,2) NOT NULL,
  referencia VARCHAR(60) NULL,
  observaciones VARCHAR(300) NULL,
  KEY idx_ant_prov (cliente_id, proveedor_id),
  CONSTRAINT fk_ant_cliente FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE,
  CONSTRAINT fk_ant_prov FOREIGN KEY (proveedor_id) REFERENCES proveedores(id) ON DELETE RESTRICT,
  CONSTRAINT fk_ant_moneda FOREIGN KEY (moneda_id) REFERENCES monedas(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE anticipos_aplicaciones (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id BIGINT UNSIGNED NOT NULL,
  anticipo_id BIGINT UNSIGNED NOT NULL,
  documento_id BIGINT UNSIGNED NOT NULL,
  monto_aplicado DECIMAL(18,2) NOT NULL,
  UNIQUE KEY uk_ant_doc (anticipo_id, documento_id),
  KEY idx_antapl_doc (cliente_id, documento_id),
  CONSTRAINT fk_antapl_cliente FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE,
  CONSTRAINT fk_antapl_anticipo FOREIGN KEY (anticipo_id) REFERENCES anticipos_proveedor(id) ON DELETE CASCADE,
  CONSTRAINT fk_antapl_documento FOREIGN KEY (documento_id) REFERENCES documentos_cxp(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =========================
-- ORDENES DE COMPRA
-- =========================
CREATE TABLE ordenes_compra (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id BIGINT UNSIGNED NOT NULL,
  sitio_id BIGINT UNSIGNED NOT NULL,
  empresa_id BIGINT UNSIGNED NULL,
  proveedor_id BIGINT UNSIGNED NOT NULL,
  moneda_id SMALLINT UNSIGNED NOT NULL,
  tipo_cambio DECIMAL(18,6) NOT NULL DEFAULT 1.000000,
  numero VARCHAR(30) NOT NULL,
  fecha DATE NOT NULL,
  estado TINYINT UNSIGNED NOT NULL DEFAULT 1,
  subtotal DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  impuesto_total DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  total DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  observaciones VARCHAR(300) NULL,
  UNIQUE KEY uk_oc (cliente_id, numero),
  KEY idx_oc_prov (cliente_id, proveedor_id, fecha),
  CONSTRAINT fk_oc_cliente FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE,
  CONSTRAINT fk_oc_sitio FOREIGN KEY (sitio_id) REFERENCES sitios(id) ON DELETE RESTRICT,
  CONSTRAINT fk_oc_empresa FOREIGN KEY (empresa_id) REFERENCES empresas(id) ON DELETE SET NULL,
  CONSTRAINT fk_oc_prov FOREIGN KEY (proveedor_id) REFERENCES proveedores(id) ON DELETE RESTRICT,
  CONSTRAINT fk_oc_moneda FOREIGN KEY (moneda_id) REFERENCES monedas(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE ordenes_compra_det (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id BIGINT UNSIGNED NOT NULL,
  orden_id BIGINT UNSIGNED NOT NULL,
  linea INT UNSIGNED NOT NULL,
  descripcion VARCHAR(200) NOT NULL,
  cantidad DECIMAL(18,6) NOT NULL DEFAULT 1.000000,
  precio_unitario DECIMAL(18,6) NOT NULL DEFAULT 0.000000,
  impuesto_id SMALLINT UNSIGNED NULL,
  impuesto_monto DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  total_linea DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  UNIQUE KEY uk_ocdet (orden_id, linea),
  KEY idx_ocdet_orden (cliente_id, orden_id),
  CONSTRAINT fk_ocdet_cliente FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE,
  CONSTRAINT fk_ocdet_orden FOREIGN KEY (orden_id) REFERENCES ordenes_compra(id) ON DELETE CASCADE,
  CONSTRAINT fk_ocdet_imp FOREIGN KEY (impuesto_id) REFERENCES impuestos(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE oc_documentos (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id BIGINT UNSIGNED NOT NULL,
  orden_id BIGINT UNSIGNED NOT NULL,
  documento_id BIGINT UNSIGNED NOT NULL,
  UNIQUE KEY uk_rel (orden_id, documento_id),
  KEY idx_rel_cliente (cliente_id),
  CONSTRAINT fk_ocr_cliente FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE,
  CONSTRAINT fk_ocr_orden FOREIGN KEY (orden_id) REFERENCES ordenes_compra(id) ON DELETE CASCADE,
  CONSTRAINT fk_ocr_documento FOREIGN KEY (documento_id) REFERENCES documentos_cxp(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =========================
-- VISTAS
-- =========================
CREATE OR REPLACE VIEW vw_antiguedad_saldos AS
SELECT
  d.cliente_id,
  d.proveedor_id,
  p.razon_social,
  d.id AS documento_id,
  d.serie, d.numero,
  d.fecha_emision, d.fecha_vencimiento,
  d.moneda_id, d.saldo_pendiente,
  CASE WHEN d.saldo_pendiente <= 0 THEN 0
       WHEN DATEDIFF(CURDATE(), d.fecha_vencimiento) <= 0  THEN d.saldo_pendiente ELSE 0 END AS no_vencido,
  CASE WHEN DATEDIFF(CURDATE(), d.fecha_vencimiento) BETWEEN 1 AND 30 THEN d.saldo_pendiente ELSE 0 END AS dias_1_30,
  CASE WHEN DATEDIFF(CURDATE(), d.fecha_vencimiento) BETWEEN 31 AND 60 THEN d.saldo_pendiente ELSE 0 END AS dias_31_60,
  CASE WHEN DATEDIFF(CURDATE(), d.fecha_vencimiento) BETWEEN 61 AND 90 THEN d.saldo_pendiente ELSE 0 END AS dias_61_90,
  CASE WHEN DATEDIFF(CURDATE(), d.fecha_vencimiento) > 90 THEN d.saldo_pendiente ELSE 0 END AS dias_91_mas
FROM documentos_cxp d
JOIN proveedores p ON p.id = d.proveedor_id;

CREATE OR REPLACE VIEW vw_estado_cuenta AS
SELECT
  d.cliente_id, d.proveedor_id, p.razon_social,
  d.id AS documento_id, d.serie, d.numero, d.fecha_emision, d.fecha_vencimiento,
  d.total_neto, d.saldo_pendiente, d.estado
FROM documentos_cxp d
JOIN proveedores p ON p.id = d.proveedor_id;

CREATE OR REPLACE VIEW vw_resumen_proveedores AS
SELECT
  d.cliente_id, d.proveedor_id, p.razon_social,
  SUM(CASE WHEN d.estado IN (2,3) THEN d.total_neto ELSE 0 END) AS total_adeudado,
  SUM(d.saldo_pendiente) AS saldo_pendiente
FROM documentos_cxp d
JOIN proveedores p ON p.id = d.proveedor_id
GROUP BY d.cliente_id, d.proveedor_id, p.razon_social;

-- =========================
-- TRIGGERS
-- =========================
DELIMITER $$

CREATE TRIGGER trg_doc_cxp_ai AFTER INSERT ON documentos_cxp
FOR EACH ROW
BEGIN
  UPDATE documentos_cxp SET saldo_pendiente = NEW.total_neto WHERE id = NEW.id;
END $$

CREATE TRIGGER trg_pago_apl_ai AFTER INSERT ON pagos_aplicaciones
FOR EACH ROW
BEGIN
  UPDATE documentos_cxp
     SET saldo_pendiente = GREATEST(0, saldo_pendiente - NEW.monto_aplicado),
         estado = CASE WHEN GREATEST(0, saldo_pendiente - NEW.monto_aplicado) = 0 THEN 4 ELSE 3 END
   WHERE id = NEW.documento_id;
END $$

CREATE TRIGGER trg_pago_apl_ad AFTER DELETE ON pagos_aplicaciones
FOR EACH ROW
BEGIN
  UPDATE documentos_cxp
     SET saldo_pendiente = saldo_pendiente + OLD.monto_aplicado,
         estado = CASE WHEN (saldo_pendiente + OLD.monto_aplicado) = total_neto THEN 2 ELSE 3 END
   WHERE id = OLD.documento_id;
END $$

CREATE TRIGGER trg_ant_apl_ai AFTER INSERT ON anticipos_aplicaciones
FOR EACH ROW
BEGIN
  UPDATE documentos_cxp
     SET saldo_pendiente = GREATEST(0, saldo_pendiente - NEW.monto_aplicado),
         estado = CASE WHEN GREATEST(0, saldo_pendiente - NEW.monto_aplicado) = 0 THEN 4 ELSE 3 END
   WHERE id = NEW.documento_id;

  UPDATE anticipos_proveedor
     SET saldo_disponible = GREATEST(0, saldo_disponible - NEW.monto_aplicado)
   WHERE id = NEW.anticipo_id;
END $$

CREATE TRIGGER trg_ant_apl_ad AFTER DELETE ON anticipos_aplicaciones
FOR EACH ROW
BEGIN
  UPDATE documentos_cxp
     SET saldo_pendiente = saldo_pendiente + OLD.monto_aplicado,
         estado = CASE WHEN (saldo_pendiente + OLD.monto_aplicado) = total_neto THEN 2 ELSE 3 END
   WHERE id = OLD.documento_id;

  UPDATE anticipos_proveedor
     SET saldo_disponible = saldo_disponible + OLD.monto_aplicado
   WHERE id = OLD.anticipo_id;
END $$

DELIMITER ;

-- =========================
-- SEED MINIMO
-- =========================
INSERT INTO monedas (codigo, nombre, simbolo) VALUES
  ('GTQ','Quetzal','Q'),
  ('USD','Dólar','$')
ON DUPLICATE KEY UPDATE nombre=VALUES(nombre), simbolo=VALUES(simbolo);

INSERT INTO roles (codigo, nombre) VALUES
  ('SUPERADMIN','Superadministrador'),
  ('ADMIN_SITIO','Administrador de Sitio'),
  ('OPERADOR','Operador'),
  ('LECTURA','Consulta')
ON DUPLICATE KEY UPDATE nombre=VALUES(nombre);

