
CREATE TABLE `adjuntos` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `cliente_id` bigint unsigned NOT NULL,
  `entidad` tinyint unsigned NOT NULL,
  `entidad_id` bigint unsigned NOT NULL,
  `nombre_archivo` varchar(160) COLLATE utf8mb4_spanish_ci NOT NULL,
  `mime_type` varchar(80) COLLATE utf8mb4_spanish_ci DEFAULT NULL,
  `ruta_storage` varchar(260) COLLATE utf8mb4_spanish_ci NOT NULL,
  `tamano_bytes` bigint unsigned DEFAULT NULL,
  `subido_por_id` bigint unsigned DEFAULT NULL,
  `subido_en` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_adj` (`cliente_id`,`entidad`,`entidad_id`),
  KEY `fk_adj_usuario` (`subido_por_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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
CREATE TABLE `anticipos_aplicaciones` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `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,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_ant_doc` (`anticipo_id`,`documento_id`),
  KEY `idx_antapl_doc` (`cliente_id`,`documento_id`),
  KEY `fk_antapl_documento` (`documento_id`),
  CONSTRAINT `fk_antapl_anticipo` FOREIGN KEY (`anticipo_id`) REFERENCES `anticipos_proveedor` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_antapl_cliente` FOREIGN KEY (`cliente_id`) REFERENCES `clientes` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_antapl_documento` FOREIGN KEY (`documento_id`) REFERENCES `documentos_cxp` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`christian`@`%`*/ /*!50003 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 */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`christian`@`%`*/ /*!50003 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 ;
CREATE TABLE `anticipos_proveedor` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `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) COLLATE utf8mb4_spanish_ci DEFAULT NULL,
  `observaciones` varchar(300) COLLATE utf8mb4_spanish_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_ant_prov` (`cliente_id`,`proveedor_id`),
  KEY `fk_ant_prov` (`proveedor_id`),
  KEY `fk_ant_moneda` (`moneda_id`),
  CONSTRAINT `fk_ant_cliente` FOREIGN KEY (`cliente_id`) REFERENCES `clientes` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ant_moneda` FOREIGN KEY (`moneda_id`) REFERENCES `monedas` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_ant_prov` FOREIGN KEY (`proveedor_id`) REFERENCES `proveedores` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
CREATE TABLE `auditoria_eventos` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `cliente_id` bigint unsigned NOT NULL,
  `sitio_id` bigint unsigned DEFAULT NULL,
  `entidad` varchar(60) COLLATE utf8mb4_spanish_ci NOT NULL,
  `entidad_id` bigint unsigned NOT NULL,
  `accion` varchar(30) COLLATE utf8mb4_spanish_ci NOT NULL,
  `usuario_id` bigint unsigned DEFAULT NULL,
  `datos` json DEFAULT NULL,
  `creado_en` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_aud1` (`cliente_id`,`entidad`,`entidad_id`),
  CONSTRAINT `fk_aud_cliente` FOREIGN KEY (`cliente_id`) REFERENCES `clientes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
CREATE TABLE `bancos` (
  `id` smallint unsigned NOT NULL AUTO_INCREMENT,
  `cliente_id` bigint unsigned NOT NULL,
  `nombre` varchar(100) COLLATE utf8mb4_spanish_ci NOT NULL,
  `activo` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  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 AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
CREATE TABLE `centros_costo` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `cliente_id` bigint unsigned NOT NULL,
  `codigo` varchar(20) COLLATE utf8mb4_spanish_ci NOT NULL,
  `nombre` varchar(120) COLLATE utf8mb4_spanish_ci NOT NULL,
  `activo` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  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 AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
CREATE TABLE `clasificaciones_proveedor` (
  `id` tinyint unsigned NOT NULL AUTO_INCREMENT,
  `cliente_id` bigint unsigned NOT NULL,
  `codigo` varchar(20) COLLATE utf8mb4_spanish_ci NOT NULL,
  `nombre` varchar(80) COLLATE utf8mb4_spanish_ci NOT NULL,
  `es_extranjero` tinyint(1) NOT NULL DEFAULT '0',
  `sujeto_retencion_isr_definitiva` tinyint(1) NOT NULL DEFAULT '0',
  `aplica_retencion_no_residente` tinyint(1) NOT NULL DEFAULT '0',
  `activo` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_claprov` (`cliente_id`,`codigo`),
  CONSTRAINT `fk_claprov_cliente` FOREIGN KEY (`cliente_id`) REFERENCES `clientes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
CREATE TABLE `cliente_erp_conexiones` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `cliente_id` bigint unsigned NOT NULL,
  `host` varchar(120) COLLATE utf8mb4_unicode_ci NOT NULL,
  `port` smallint unsigned NOT NULL DEFAULT '3306',
  `database_name` varchar(120) COLLATE utf8mb4_unicode_ci NOT NULL,
  `username` varchar(120) COLLATE utf8mb4_unicode_ci NOT NULL,
  `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `tabla_proveedores` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'maepro',
  `tabla_facturas` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `fecha_inicio_sync_facturas` date DEFAULT 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,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_CLIENTE_ERP_CONEXION_CLIENTE` (`cliente_id`),
  KEY `IDX_CLIENTE_ERP_CONEXION_ACTIVO` (`activo`),
  CONSTRAINT `FK_CLIENTE_ERP_CONEXION_CLIENTE` FOREIGN KEY (`cliente_id`) REFERENCES `clientes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `clientes` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `nombre` varchar(120) COLLATE utf8mb4_spanish_ci NOT NULL,
  `nombre_comercial` varchar(120) COLLATE utf8mb4_spanish_ci DEFAULT NULL,
  `nit` varchar(20) COLLATE utf8mb4_spanish_ci DEFAULT NULL,
  `pais_iso2` char(2) COLLATE utf8mb4_spanish_ci DEFAULT NULL,
  `moneda_base_id` smallint unsigned DEFAULT NULL,
  `timezone` varchar(64) COLLATE utf8mb4_spanish_ci NOT NULL DEFAULT 'America/Guatemala',
  `dominio` varchar(120) COLLATE utf8mb4_spanish_ci DEFAULT 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,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_cliente_nombre` (`nombre`),
  UNIQUE KEY `uk_cliente_dominio` (`dominio`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
CREATE TABLE `condiciones_pago` (
  `id` smallint unsigned NOT NULL AUTO_INCREMENT,
  `cliente_id` bigint unsigned NOT NULL,
  `nombre` varchar(80) COLLATE utf8mb4_spanish_ci NOT NULL,
  `dias` smallint unsigned NOT NULL DEFAULT '0',
  `activo` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  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 AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
CREATE TABLE `cuentas_bancarias` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `cliente_id` bigint unsigned NOT NULL,
  `banco_id` smallint unsigned NOT NULL,
  `empresa_id` bigint unsigned DEFAULT NULL,
  `numero` varchar(34) COLLATE utf8mb4_spanish_ci NOT NULL,
  `moneda_id` smallint unsigned NOT NULL,
  `descripcion` varchar(120) COLLATE utf8mb4_spanish_ci DEFAULT NULL,
  `activa` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_cta` (`cliente_id`,`numero`),
  KEY `idx_cta_banco` (`banco_id`),
  KEY `fk_cta_moneda` (`moneda_id`),
  KEY `fk_cta_empresa` (`empresa_id`),
  CONSTRAINT `fk_cta_banco` FOREIGN KEY (`banco_id`) REFERENCES `bancos` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_cta_cliente` FOREIGN KEY (`cliente_id`) REFERENCES `clientes` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_cta_empresa` FOREIGN KEY (`empresa_id`) REFERENCES `empresas` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_cta_moneda` FOREIGN KEY (`moneda_id`) REFERENCES `monedas` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
CREATE TABLE `cuentas_contables` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `cliente_id` bigint unsigned NOT NULL,
  `codigo` varchar(20) COLLATE utf8mb4_spanish_ci NOT NULL,
  `nombre` varchar(160) COLLATE utf8mb4_spanish_ci NOT NULL,
  `nivel` tinyint unsigned DEFAULT NULL,
  `tipo` tinyint unsigned DEFAULT NULL,
  `cuenta_padre_id` bigint unsigned DEFAULT NULL,
  `activo` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_cta_cliente_codigo` (`cliente_id`,`codigo`),
  KEY `fk_cc_padre` (`cuenta_padre_id`),
  CONSTRAINT `fk_cc_padre` FOREIGN KEY (`cuenta_padre_id`) REFERENCES `cuentas_contables` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_ctaclie_cliente` FOREIGN KEY (`cliente_id`) REFERENCES `clientes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
CREATE TABLE `doctrine_migration_versions` (
  `version` varchar(191) COLLATE utf8mb3_unicode_ci NOT NULL,
  `executed_at` datetime DEFAULT NULL,
  `execution_time` int DEFAULT NULL,
  PRIMARY KEY (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
CREATE TABLE `documentos_cxp` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `cliente_id` bigint unsigned NOT NULL,
  `sitio_id` bigint unsigned NOT NULL,
  `empresa_id` bigint unsigned DEFAULT NULL,
  `sucursal_id` bigint unsigned DEFAULT NULL,
  `sucursal_iva_codigo` varchar(10) COLLATE utf8mb4_spanish_ci DEFAULT NULL,
  `proveedor_id` bigint unsigned NOT NULL,
  `tipo_documento_id` smallint unsigned NOT NULL,
  `condicion_pago_id` smallint unsigned DEFAULT NULL,
  `moneda_id` smallint unsigned NOT NULL,
  `tipo_iva_id` tinyint unsigned DEFAULT NULL,
  `tipo_cambio` decimal(18,6) NOT NULL DEFAULT '1.000000',
  `serie` varchar(20) COLLATE utf8mb4_spanish_ci DEFAULT NULL,
  `numero` varchar(30) COLLATE utf8mb4_spanish_ci NOT NULL,
  `origen_sistema` varchar(20) COLLATE utf8mb4_spanish_ci NOT NULL DEFAULT 'CXP',
  `origen_id_externo` varchar(80) COLLATE utf8mb4_spanish_ci DEFAULT NULL,
  `referencia` varchar(60) COLLATE utf8mb4_spanish_ci DEFAULT NULL,
  `fecha_emision` date NOT NULL,
  `fecha_recepcion` date NOT NULL,
  `fecha_vencimiento` date NOT NULL,
  `cuenta_provision_id` bigint unsigned DEFAULT NULL,
  `estado` tinyint unsigned NOT NULL DEFAULT '1',
  `subtotal` decimal(18,2) NOT NULL DEFAULT '0.00',
  `base_gravada_bienes` decimal(18,2) NOT NULL DEFAULT '0.00',
  `base_gravada_servicios` decimal(18,2) NOT NULL DEFAULT '0.00',
  `exento_bienes` decimal(18,2) NOT NULL DEFAULT '0.00',
  `exento_servicios` decimal(18,2) NOT NULL DEFAULT '0.00',
  `otros_no_gravados` 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',
  `retencion_codigo_sat` varchar(80) COLLATE utf8mb4_spanish_ci DEFAULT NULL,
  `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) COLLATE utf8mb4_spanish_ci DEFAULT NULL,
  `creado_por_id` bigint unsigned DEFAULT NULL,
  `creado_en` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `actualizado_en` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `anio` smallint unsigned GENERATED ALWAYS AS (year(`fecha_emision`)) STORED,
  `mes` tinyint unsigned GENERATED ALWAYS AS (month(`fecha_emision`)) STORED,
  `dias_credito` smallint unsigned GENERATED ALWAYS AS ((to_days(`fecha_vencimiento`) - to_days(`fecha_emision`))) STORED,
  `documento_padre_id` bigint unsigned DEFAULT NULL,
  `idp_total` decimal(18,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_doc_unico` (`cliente_id`,`proveedor_id`,`tipo_documento_id`,`serie`,`numero`),
  UNIQUE KEY `UNIQ_DOCUMENTOS_ORIGEN_EXTERNO` (`cliente_id`,`origen_sistema`,`origen_id_externo`),
  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`),
  KEY `fk_doc_sitio` (`sitio_id`),
  KEY `fk_doc_empresa` (`empresa_id`),
  KEY `fk_doc_sucursal` (`sucursal_id`),
  KEY `fk_doc_prov` (`proveedor_id`),
  KEY `fk_doc_tipodoc` (`tipo_documento_id`),
  KEY `fk_doc_condpago` (`condicion_pago_id`),
  KEY `fk_doc_moneda` (`moneda_id`),
  KEY `fk_doc_tiva` (`tipo_iva_id`),
  KEY `fk_doc_cta_prov` (`cuenta_provision_id`),
  KEY `IDX_DOC_PADRE` (`documento_padre_id`),
  KEY `IDX_DOCUMENTOS_ORIGEN_SISTEMA` (`origen_sistema`),
  CONSTRAINT `fk_doc_cliente` FOREIGN KEY (`cliente_id`) REFERENCES `clientes` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_doc_condpago` FOREIGN KEY (`condicion_pago_id`) REFERENCES `condiciones_pago` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_doc_cta_prov` FOREIGN KEY (`cuenta_provision_id`) REFERENCES `cuentas_contables` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_doc_empresa` FOREIGN KEY (`empresa_id`) REFERENCES `empresas` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_doc_moneda` FOREIGN KEY (`moneda_id`) REFERENCES `monedas` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `FK_DOC_PADRE` FOREIGN KEY (`documento_padre_id`) REFERENCES `documentos_cxp` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_doc_prov` FOREIGN KEY (`proveedor_id`) REFERENCES `proveedores` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_doc_sitio` FOREIGN KEY (`sitio_id`) REFERENCES `sitios` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_doc_sucursal` FOREIGN KEY (`sucursal_id`) REFERENCES `sucursales` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_doc_tipodoc` FOREIGN KEY (`tipo_documento_id`) REFERENCES `tipos_documento` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_doc_tiva` FOREIGN KEY (`tipo_iva_id`) REFERENCES `tipos_iva` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`christian`@`%`*/ /*!50003 TRIGGER `trg_doc_cxp_bi` BEFORE INSERT ON `documentos_cxp` FOR EACH ROW BEGIN
  -- Inicializa el saldo antes de insertar, sin tocar la tabla
  SET NEW.saldo_pendiente = NEW.total_neto;

  -- (Opcional) si no mandas estado, deja 2 = Registrado
  IF NEW.estado IS NULL OR NEW.estado = 0 THEN
    SET NEW.estado = 2;
  END IF;
END */;;
DELIMITER ;
CREATE TABLE `documentos_cxp_combustible` (
  `documento_id` bigint unsigned NOT NULL,
  `placa` varchar(15) COLLATE utf8mb4_spanish_ci DEFAULT NULL,
  `kilometraje` int unsigned DEFAULT NULL,
  `galones` decimal(12,4) DEFAULT NULL,
  `precio_galon` decimal(18,6) DEFAULT NULL,
  PRIMARY KEY (`documento_id`),
  CONSTRAINT `fk_comb_doc` FOREIGN KEY (`documento_id`) REFERENCES `documentos_cxp` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
CREATE TABLE `documentos_cxp_det` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `cliente_id` bigint unsigned NOT NULL,
  `documento_id` bigint unsigned NOT NULL,
  `linea` int unsigned NOT NULL,
  `descripcion` varchar(200) COLLATE utf8mb4_spanish_ci 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 DEFAULT NULL,
  `impuesto_monto` decimal(18,2) NOT NULL DEFAULT '0.00',
  `retencion_id` smallint unsigned DEFAULT NULL,
  `retencion_monto` decimal(18,2) NOT NULL DEFAULT '0.00',
  `centro_costo_id` bigint unsigned DEFAULT NULL,
  `proyecto_id` bigint unsigned DEFAULT NULL,
  `total_linea` decimal(18,2) NOT NULL DEFAULT '0.00',
  `idp_galones` decimal(18,4) DEFAULT NULL,
  `idp_monto` decimal(18,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`),
  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`),
  KEY `fk_det_imp` (`impuesto_id`),
  KEY `fk_det_ret` (`retencion_id`),
  KEY `fk_det_cc` (`centro_costo_id`),
  KEY `fk_det_proy` (`proyecto_id`),
  CONSTRAINT `fk_det_cc` FOREIGN KEY (`centro_costo_id`) REFERENCES `centros_costo` (`id`) ON DELETE SET NULL,
  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_proy` FOREIGN KEY (`proyecto_id`) REFERENCES `proyectos` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_det_ret` FOREIGN KEY (`retencion_id`) REFERENCES `retenciones` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
CREATE TABLE `documentos_cxp_retenciones` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `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',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_doc_ret` (`documento_id`,`retencion_id`),
  KEY `idx_ret_doc` (`cliente_id`,`documento_id`),
  KEY `fk_docret_ret` (`retencion_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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
CREATE TABLE `empresas` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `cliente_id` bigint unsigned NOT NULL,
  `sitio_id` bigint unsigned NOT NULL,
  `razon_social` varchar(160) COLLATE utf8mb4_spanish_ci NOT NULL,
  `nit` varchar(20) COLLATE utf8mb4_spanish_ci DEFAULT NULL,
  `direccion` varchar(200) COLLATE utf8mb4_spanish_ci DEFAULT NULL,
  `telefono` varchar(30) COLLATE utf8mb4_spanish_ci DEFAULT NULL,
  `email` varchar(120) COLLATE utf8mb4_spanish_ci DEFAULT 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,
  PRIMARY KEY (`id`),
  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 AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
CREATE TABLE `idp_tasas` (
  `id` tinyint unsigned NOT NULL AUTO_INCREMENT,
  `tipo` varchar(12) COLLATE utf8mb4_spanish_ci NOT NULL,
  `tasa_por_galon` decimal(10,4) DEFAULT NULL,
  `vigente_desde` date DEFAULT NULL,
  `vigente_hasta` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_idp` (`tipo`,`vigente_desde`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
CREATE TABLE `impuestos` (
  `id` smallint unsigned NOT NULL AUTO_INCREMENT,
  `cliente_id` bigint unsigned NOT NULL,
  `nombre` varchar(80) COLLATE utf8mb4_spanish_ci NOT NULL,
  `porcentaje` decimal(5,2) NOT NULL,
  `codigo_sat` varchar(30) COLLATE utf8mb4_spanish_ci DEFAULT NULL,
  `activo` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  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 AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
CREATE TABLE `monedas` (
  `id` smallint unsigned NOT NULL AUTO_INCREMENT,
  `codigo` char(3) COLLATE utf8mb4_spanish_ci NOT NULL,
  `nombre` varchar(40) COLLATE utf8mb4_spanish_ci NOT NULL,
  `simbolo` varchar(6) COLLATE utf8mb4_spanish_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_moneda_codigo` (`codigo`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
CREATE TABLE `oc_documentos` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `cliente_id` bigint unsigned NOT NULL,
  `orden_id` bigint unsigned NOT NULL,
  `documento_id` bigint unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_rel` (`orden_id`,`documento_id`),
  KEY `idx_rel_cliente` (`cliente_id`),
  KEY `fk_ocr_documento` (`documento_id`),
  CONSTRAINT `fk_ocr_cliente` FOREIGN KEY (`cliente_id`) REFERENCES `clientes` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ocr_documento` FOREIGN KEY (`documento_id`) REFERENCES `documentos_cxp` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ocr_orden` FOREIGN KEY (`orden_id`) REFERENCES `ordenes_compra` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
CREATE TABLE `ordenes_compra` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `cliente_id` bigint unsigned NOT NULL,
  `sitio_id` bigint unsigned NOT NULL,
  `empresa_id` bigint unsigned DEFAULT 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) COLLATE utf8mb4_spanish_ci 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) COLLATE utf8mb4_spanish_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_oc` (`cliente_id`,`numero`),
  KEY `idx_oc_prov` (`cliente_id`,`proveedor_id`,`fecha`),
  KEY `fk_oc_sitio` (`sitio_id`),
  KEY `fk_oc_empresa` (`empresa_id`),
  KEY `fk_oc_prov` (`proveedor_id`),
  KEY `fk_oc_moneda` (`moneda_id`),
  CONSTRAINT `fk_oc_cliente` FOREIGN KEY (`cliente_id`) REFERENCES `clientes` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_oc_empresa` FOREIGN KEY (`empresa_id`) REFERENCES `empresas` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_oc_moneda` FOREIGN KEY (`moneda_id`) REFERENCES `monedas` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_oc_prov` FOREIGN KEY (`proveedor_id`) REFERENCES `proveedores` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_oc_sitio` FOREIGN KEY (`sitio_id`) REFERENCES `sitios` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
CREATE TABLE `ordenes_compra_det` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `cliente_id` bigint unsigned NOT NULL,
  `orden_id` bigint unsigned NOT NULL,
  `linea` int unsigned NOT NULL,
  `descripcion` varchar(200) COLLATE utf8mb4_spanish_ci 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 DEFAULT NULL,
  `impuesto_monto` decimal(18,2) NOT NULL DEFAULT '0.00',
  `total_linea` decimal(18,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_ocdet` (`orden_id`,`linea`),
  KEY `idx_ocdet_orden` (`cliente_id`,`orden_id`),
  KEY `fk_ocdet_imp` (`impuesto_id`),
  CONSTRAINT `fk_ocdet_cliente` FOREIGN KEY (`cliente_id`) REFERENCES `clientes` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ocdet_imp` FOREIGN KEY (`impuesto_id`) REFERENCES `impuestos` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_ocdet_orden` FOREIGN KEY (`orden_id`) REFERENCES `ordenes_compra` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
CREATE TABLE `pagos` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `cliente_id` bigint unsigned NOT NULL,
  `sitio_id` bigint unsigned NOT NULL,
  `empresa_id` bigint unsigned DEFAULT NULL,
  `proveedor_id` bigint unsigned NOT NULL,
  `cuenta_bancaria_id` bigint unsigned DEFAULT NULL,
  `cuenta_auxiliar_id` bigint unsigned DEFAULT 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) COLLATE utf8mb4_spanish_ci DEFAULT NULL,
  `total_pagado` decimal(18,2) NOT NULL DEFAULT '0.00',
  `estado` tinyint unsigned NOT NULL DEFAULT '1',
  `observaciones` varchar(300) COLLATE utf8mb4_spanish_ci DEFAULT NULL,
  `creado_por_id` bigint unsigned DEFAULT NULL,
  `creado_en` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `actualizado_en` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `moneda_origen_id` smallint unsigned DEFAULT NULL,
  `monto_origen` decimal(18,2) DEFAULT '0.00',
  `tipo_cambio_usado` decimal(18,6) DEFAULT '1.000000',
  `tipo_cambio_compra` decimal(18,6) DEFAULT '1.000000',
  `tipo_cambio_venta` decimal(18,6) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_pago_cliente` (`cliente_id`,`fecha_pago`),
  KEY `idx_pago_prov` (`cliente_id`,`proveedor_id`,`fecha_pago`),
  KEY `fk_pago_sitio` (`sitio_id`),
  KEY `fk_pago_empresa` (`empresa_id`),
  KEY `fk_pago_prov` (`proveedor_id`),
  KEY `fk_pago_cta` (`cuenta_bancaria_id`),
  KEY `fk_pago_moneda` (`moneda_id`),
  KEY `fk_pago_cta_aux` (`cuenta_auxiliar_id`),
  KEY `IDX_PAGOS_MONEDA_ORIGEN` (`moneda_origen_id`),
  CONSTRAINT `fk_pago_cliente` FOREIGN KEY (`cliente_id`) REFERENCES `clientes` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_pago_cta` FOREIGN KEY (`cuenta_bancaria_id`) REFERENCES `cuentas_bancarias` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_pago_cta_aux` FOREIGN KEY (`cuenta_auxiliar_id`) REFERENCES `cuentas_contables` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_pago_empresa` FOREIGN KEY (`empresa_id`) REFERENCES `empresas` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_pago_moneda` FOREIGN KEY (`moneda_id`) REFERENCES `monedas` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_pago_prov` FOREIGN KEY (`proveedor_id`) REFERENCES `proveedores` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_pago_sitio` FOREIGN KEY (`sitio_id`) REFERENCES `sitios` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `FK_PAGOS_MONEDA_ORIGEN` FOREIGN KEY (`moneda_origen_id`) REFERENCES `monedas` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
CREATE TABLE `pagos_aplicaciones` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `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,
  `dif_cambiario` decimal(18,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_pago_doc` (`pago_id`,`documento_id`),
  KEY `idx_apl_doc` (`cliente_id`,`documento_id`),
  KEY `fk_apl_documento` (`documento_id`),
  CONSTRAINT `fk_apl_cliente` FOREIGN KEY (`cliente_id`) REFERENCES `clientes` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_apl_documento` FOREIGN KEY (`documento_id`) REFERENCES `documentos_cxp` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_apl_pago` FOREIGN KEY (`pago_id`) REFERENCES `pagos` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`christian`@`%`*/ /*!50003 TRIGGER `trg_pago_apl_ai` AFTER INSERT ON `pagos_aplicaciones` FOR EACH ROW BEGIN
  -- Resta del saldo del documento al aplicar pago
  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 */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`christian`@`%`*/ /*!50003 TRIGGER `trg_pago_apl_ad` AFTER DELETE ON `pagos_aplicaciones` FOR EACH ROW BEGIN
  -- Reversa saldo si se elimina una aplicación
  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 */;;
DELIMITER ;
CREATE TABLE `paises` (
  `id` smallint unsigned NOT NULL AUTO_INCREMENT,
  `codigo` varchar(3) COLLATE utf8mb4_spanish_ci NOT NULL,
  `codigo_iso2` varchar(2) COLLATE utf8mb4_spanish_ci DEFAULT NULL,
  `nombre` varchar(100) COLLATE utf8mb4_spanish_ci NOT NULL,
  `activo` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_PAISES_CODIGO` (`codigo`)
) ENGINE=InnoDB AUTO_INCREMENT=64 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
CREATE TABLE `proveedores` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `cliente_id` bigint unsigned NOT NULL,
  `codigo` varchar(20) COLLATE utf8mb4_spanish_ci DEFAULT NULL,
  `origen_sistema` varchar(20) COLLATE utf8mb4_spanish_ci NOT NULL DEFAULT 'CXP',
  `origen_id_externo` varchar(64) COLLATE utf8mb4_spanish_ci DEFAULT NULL,
  `nit` varchar(20) COLLATE utf8mb4_spanish_ci NOT NULL,
  `razon_social` varchar(160) COLLATE utf8mb4_spanish_ci NOT NULL,
  `comercial` text COLLATE utf8mb4_spanish_ci,
  `contacto` varchar(120) COLLATE utf8mb4_spanish_ci DEFAULT NULL,
  `telefono` varchar(30) COLLATE utf8mb4_spanish_ci DEFAULT NULL,
  `email` varchar(120) COLLATE utf8mb4_spanish_ci DEFAULT NULL,
  `direccion` varchar(200) COLLATE utf8mb4_spanish_ci DEFAULT NULL,
  `departamento` varchar(80) COLLATE utf8mb4_spanish_ci DEFAULT NULL,
  `municipio` varchar(80) COLLATE utf8mb4_spanish_ci DEFAULT NULL,
  `pais_iso2` char(2) COLLATE utf8mb4_spanish_ci DEFAULT NULL,
  `es_extranjero` tinyint(1) NOT NULL DEFAULT '0',
  `clasificacion` tinyint unsigned DEFAULT NULL,
  `condicion_pago_id` smallint unsigned DEFAULT NULL,
  `impuesto_id` smallint unsigned DEFAULT NULL,
  `retencion_isr_id` smallint unsigned DEFAULT NULL,
  `retencion_iva_id` smallint unsigned DEFAULT NULL,
  `aplica_ret_isr` tinyint(1) NOT NULL DEFAULT '0',
  `aplica_ret_iva` tinyint(1) NOT NULL DEFAULT '0',
  `aplica_idp` tinyint(1) NOT NULL DEFAULT '0',
  `saldo_inicial` decimal(18,2) NOT NULL DEFAULT '0.00',
  `limite_credito` decimal(18,2) DEFAULT 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,
  `regimen_fiscal_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_prov_nit` (`cliente_id`,`nit`),
  UNIQUE KEY `UNIQ_PROVEEDORES_ORIGEN_EXTERNO` (`cliente_id`,`origen_sistema`,`origen_id_externo`),
  KEY `idx_prov_cliente` (`cliente_id`),
  KEY `idx_prov_busqueda` (`cliente_id`,`razon_social`,`codigo`),
  KEY `fk_prov_condpago` (`condicion_pago_id`),
  KEY `fk_prov_clasificacion` (`clasificacion`),
  KEY `fk_prov_impuesto` (`impuesto_id`),
  KEY `fk_prov_ret_isr` (`retencion_isr_id`),
  KEY `fk_prov_ret_iva` (`retencion_iva_id`),
  KEY `IDX_864FA8F18DC7FA53` (`regimen_fiscal_id`),
  KEY `IDX_PROVEEDORES_ORIGEN_SISTEMA` (`origen_sistema`),
  CONSTRAINT `FK_864FA8F18DC7FA53` FOREIGN KEY (`regimen_fiscal_id`) REFERENCES `regimenes_fiscales` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_prov_clasificacion` FOREIGN KEY (`clasificacion`) REFERENCES `clasificaciones_proveedor` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_prov_cliente` FOREIGN KEY (`cliente_id`) REFERENCES `clientes` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_prov_condpago` FOREIGN KEY (`condicion_pago_id`) REFERENCES `condiciones_pago` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_prov_impuesto` FOREIGN KEY (`impuesto_id`) REFERENCES `impuestos` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_prov_ret_isr` FOREIGN KEY (`retencion_isr_id`) REFERENCES `retenciones` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_prov_ret_iva` FOREIGN KEY (`retencion_iva_id`) REFERENCES `retenciones` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
CREATE TABLE `proyectos` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `cliente_id` bigint unsigned NOT NULL,
  `codigo` varchar(20) COLLATE utf8mb4_spanish_ci NOT NULL,
  `nombre` varchar(160) COLLATE utf8mb4_spanish_ci NOT NULL,
  `activo` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  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 AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
CREATE TABLE `regimen_fiscal_configs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `regimen_fiscal_id` int NOT NULL,
  `cliente_id` bigint unsigned NOT NULL,
  `impuesto_id` smallint unsigned DEFAULT NULL,
  `retencion_isr_id` smallint unsigned DEFAULT NULL,
  `retencion_iva_id` smallint unsigned DEFAULT NULL,
  `idp_tasa_id` tinyint unsigned DEFAULT NULL,
  `aplica_ret_isr` tinyint(1) NOT NULL DEFAULT '0',
  `aplica_ret_iva` tinyint(1) NOT NULL DEFAULT '0',
  `aplica_idp` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_CFG_REGIMEN_CLIENTE` (`regimen_fiscal_id`,`cliente_id`),
  KEY `IDX_CFG_REGIMEN` (`regimen_fiscal_id`),
  KEY `IDX_CFG_CLIENTE` (`cliente_id`),
  KEY `IDX_CFG_IMPUESTO` (`impuesto_id`),
  KEY `IDX_CFG_RET_ISR` (`retencion_isr_id`),
  KEY `IDX_CFG_RET_IVA` (`retencion_iva_id`),
  KEY `IDX_CFG_IDP_TASA` (`idp_tasa_id`),
  CONSTRAINT `FK_CFG_CLIENTE` FOREIGN KEY (`cliente_id`) REFERENCES `clientes` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_CFG_IDP_TASA` FOREIGN KEY (`idp_tasa_id`) REFERENCES `idp_tasas` (`id`) ON DELETE SET NULL,
  CONSTRAINT `FK_CFG_IMPUESTO` FOREIGN KEY (`impuesto_id`) REFERENCES `impuestos` (`id`) ON DELETE SET NULL,
  CONSTRAINT `FK_CFG_REGIMEN` FOREIGN KEY (`regimen_fiscal_id`) REFERENCES `regimenes_fiscales` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_CFG_RET_ISR` FOREIGN KEY (`retencion_isr_id`) REFERENCES `retenciones` (`id`) ON DELETE SET NULL,
  CONSTRAINT `FK_CFG_RET_IVA` FOREIGN KEY (`retencion_iva_id`) REFERENCES `retenciones` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `regimenes_fiscales` (
  `id` int NOT NULL AUTO_INCREMENT,
  `codigo` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `nombre` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `descripcion` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `tipo` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'LOCAL',
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_ECC9C0AB20332D99` (`codigo`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `retenciones` (
  `id` smallint unsigned NOT NULL AUTO_INCREMENT,
  `cliente_id` bigint unsigned NOT NULL,
  `nombre` varchar(80) COLLATE utf8mb4_spanish_ci NOT NULL,
  `tipo` tinyint unsigned NOT NULL,
  `porcentaje` decimal(6,3) NOT NULL,
  `base_sobre` tinyint unsigned NOT NULL DEFAULT '1',
  `aplica_no_residente` tinyint(1) NOT NULL DEFAULT '0',
  `activo` tinyint(1) NOT NULL DEFAULT '1',
  `es_escalonado` tinyint(1) NOT NULL DEFAULT '0',
  `limite_escala1` decimal(18,2) DEFAULT NULL,
  `porcentaje_escala2` decimal(6,3) DEFAULT NULL,
  PRIMARY KEY (`id`),
  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 AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
CREATE TABLE `roles` (
  `id` smallint unsigned NOT NULL AUTO_INCREMENT,
  `codigo` varchar(40) COLLATE utf8mb4_spanish_ci NOT NULL,
  `nombre` varchar(80) COLLATE utf8mb4_spanish_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_rol_codigo` (`codigo`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
CREATE TABLE `sitios` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `cliente_id` bigint unsigned NOT NULL,
  `nombre` varchar(120) COLLATE utf8mb4_spanish_ci NOT NULL,
  `moneda_base_id` smallint unsigned DEFAULT NULL,
  `timezone` varchar(64) COLLATE utf8mb4_spanish_ci 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,
  PRIMARY KEY (`id`),
  KEY `idx_sitio_cliente` (`cliente_id`),
  CONSTRAINT `fk_sitio_cliente` FOREIGN KEY (`cliente_id`) REFERENCES `clientes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
CREATE TABLE `sucursales` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `cliente_id` bigint unsigned NOT NULL,
  `sitio_id` bigint unsigned NOT NULL,
  `empresa_id` bigint unsigned NOT NULL,
  `nombre` varchar(160) COLLATE utf8mb4_spanish_ci NOT NULL,
  `codigo` varchar(30) COLLATE utf8mb4_spanish_ci DEFAULT NULL,
  `direccion` varchar(200) COLLATE utf8mb4_spanish_ci DEFAULT 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,
  PRIMARY KEY (`id`),
  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_empresa` FOREIGN KEY (`empresa_id`) REFERENCES `empresas` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_sucursal_sitio` FOREIGN KEY (`sitio_id`) REFERENCES `sitios` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
CREATE TABLE `tipos_cambio` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `moneda_id` smallint unsigned NOT NULL,
  `fecha` date NOT NULL,
  `tc_venta` decimal(18,6) NOT NULL,
  `tc_compra` decimal(18,6) NOT NULL,
  PRIMARY KEY (`id`),
  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 AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
CREATE TABLE `tipos_documento` (
  `id` smallint unsigned NOT NULL AUTO_INCREMENT,
  `cliente_id` bigint unsigned NOT NULL,
  `codigo` varchar(20) COLLATE utf8mb4_spanish_ci NOT NULL,
  `codigo_libro_compras` varchar(8) COLLATE utf8mb4_spanish_ci DEFAULT NULL,
  `nombre` varchar(80) COLLATE utf8mb4_spanish_ci NOT NULL,
  `clase_mov` enum('CARGO','ABONO') COLLATE utf8mb4_spanish_ci NOT NULL DEFAULT 'CARGO',
  `clase` tinyint unsigned NOT NULL,
  `afecta_saldo` tinyint(1) NOT NULL DEFAULT '1',
  `afecta_libro_iva` tinyint(1) NOT NULL DEFAULT '1',
  `es_extranjero` tinyint(1) NOT NULL DEFAULT '0',
  `requiere_aplicacion` tinyint(1) NOT NULL DEFAULT '0',
  `activo` tinyint(1) NOT NULL DEFAULT '1',
  `retiene_iva` tinyint(1) NOT NULL DEFAULT '0',
  `retiene_isr` tinyint(1) NOT NULL DEFAULT '0',
  `es_factura_especial` tinyint(1) NOT NULL DEFAULT '0',
  `porcentaje_ret_iva` decimal(5,2) DEFAULT NULL,
  `porcentaje_ret_isr` decimal(5,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  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 AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
CREATE TABLE `tipos_iva` (
  `id` tinyint unsigned NOT NULL AUTO_INCREMENT,
  `codigo` varchar(20) COLLATE utf8mb4_spanish_ci NOT NULL,
  `nombre` varchar(60) COLLATE utf8mb4_spanish_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_tiva` (`codigo`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
CREATE TABLE `usuarios` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(120) COLLATE utf8mb4_spanish_ci NOT NULL,
  `password_hash` varchar(255) COLLATE utf8mb4_spanish_ci NOT NULL,
  `nombre` varchar(120) COLLATE utf8mb4_spanish_ci NOT NULL,
  `activo` tinyint(1) NOT NULL DEFAULT '1',
  `superadmin` tinyint(1) NOT NULL DEFAULT '0',
  `ultimo_login_en` datetime DEFAULT NULL,
  `creado_en` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `actualizado_en` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `accesos` longtext COLLATE utf8mb4_spanish_ci,
  `foto` varchar(255) COLLATE utf8mb4_spanish_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_usuario_email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
CREATE TABLE `usuarios_sitios_roles` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `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,
  PRIMARY KEY (`id`),
  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`),
  KEY `fk_usrrol_cliente` (`cliente_id`),
  KEY `fk_usrrol_sitio` (`sitio_id`),
  CONSTRAINT `fk_usrrol_rol` FOREIGN KEY (`rol_id`) REFERENCES `roles` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_usrrol_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
/*!50001 DROP VIEW IF EXISTS `vw_antiguedad_saldos`*/;
/*!50001 CREATE VIEW `vw_antiguedad_saldos` AS SELECT 
 1 AS `cliente_id`,
 1 AS `proveedor_id`,
 1 AS `razon_social`,
 1 AS `documento_id`,
 1 AS `serie`,
 1 AS `numero`,
 1 AS `fecha_emision`,
 1 AS `fecha_vencimiento`,
 1 AS `moneda_id`,
 1 AS `saldo_pendiente`,
 1 AS `no_vencido`,
 1 AS `dias_1_30`,
 1 AS `dias_31_60`,
 1 AS `dias_61_90`,
 1 AS `dias_91_mas`*/;
/*!50001 DROP VIEW IF EXISTS `vw_estado_cuenta`*/;
/*!50001 CREATE VIEW `vw_estado_cuenta` AS SELECT 
 1 AS `cliente_id`,
 1 AS `proveedor_id`,
 1 AS `razon_social`,
 1 AS `documento_id`,
 1 AS `serie`,
 1 AS `numero`,
 1 AS `fecha_emision`,
 1 AS `fecha_vencimiento`,
 1 AS `total_neto`,
 1 AS `saldo_pendiente`,
 1 AS `estado`*/;
/*!50001 DROP VIEW IF EXISTS `vw_libro_compras`*/;
/*!50001 CREATE VIEW `vw_libro_compras` AS SELECT 
 1 AS `id`,
 1 AS `cliente_id`,
 1 AS `sitio_id`,
 1 AS `proveedor_id`,
 1 AS `tipo_documento_id`,
 1 AS `moneda_id`,
 1 AS `fecha_emision`,
 1 AS `tipo_documento_codigo`,
 1 AS `tipo_documento_nombre`,
 1 AS `serie`,
 1 AS `numero`,
 1 AS `proveedor_nit`,
 1 AS `proveedor_nombre`,
 1 AS `compra_neta`,
 1 AS `iva`,
 1 AS `idp`,
 1 AS `retencion_total`,
 1 AS `total_bruto`,
 1 AS `total_neto`,
 1 AS `tipo_cambio`,
 1 AS `estado`*/;
/*!50001 DROP VIEW IF EXISTS `vw_resumen_proveedores`*/;
/*!50001 CREATE VIEW `vw_resumen_proveedores` AS SELECT 
 1 AS `cliente_id`,
 1 AS `proveedor_id`,
 1 AS `razon_social`,
 1 AS `total_adeudado`,
 1 AS `saldo_pendiente`*/;
/*!50001 DROP VIEW IF EXISTS `vw_antiguedad_saldos`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`christian`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `vw_antiguedad_saldos` AS select `d`.`cliente_id` AS `cliente_id`,`d`.`proveedor_id` AS `proveedor_id`,`p`.`razon_social` AS `razon_social`,`d`.`id` AS `documento_id`,`d`.`serie` AS `serie`,`d`.`numero` AS `numero`,`d`.`fecha_emision` AS `fecha_emision`,`d`.`fecha_vencimiento` AS `fecha_vencimiento`,`d`.`moneda_id` AS `moneda_id`,`d`.`saldo_pendiente` AS `saldo_pendiente`,(case when (`d`.`saldo_pendiente` <= 0) then 0 when ((to_days(curdate()) - to_days(`d`.`fecha_vencimiento`)) <= 0) then `d`.`saldo_pendiente` else 0 end) AS `no_vencido`,(case when ((to_days(curdate()) - to_days(`d`.`fecha_vencimiento`)) between 1 and 30) then `d`.`saldo_pendiente` else 0 end) AS `dias_1_30`,(case when ((to_days(curdate()) - to_days(`d`.`fecha_vencimiento`)) between 31 and 60) then `d`.`saldo_pendiente` else 0 end) AS `dias_31_60`,(case when ((to_days(curdate()) - to_days(`d`.`fecha_vencimiento`)) between 61 and 90) then `d`.`saldo_pendiente` else 0 end) AS `dias_61_90`,(case when ((to_days(curdate()) - to_days(`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`))) */;
/*!50001 DROP VIEW IF EXISTS `vw_estado_cuenta`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`christian`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `vw_estado_cuenta` AS select `d`.`cliente_id` AS `cliente_id`,`d`.`proveedor_id` AS `proveedor_id`,`p`.`razon_social` AS `razon_social`,`d`.`id` AS `documento_id`,`d`.`serie` AS `serie`,`d`.`numero` AS `numero`,`d`.`fecha_emision` AS `fecha_emision`,`d`.`fecha_vencimiento` AS `fecha_vencimiento`,`d`.`total_neto` AS `total_neto`,`d`.`saldo_pendiente` AS `saldo_pendiente`,`d`.`estado` AS `estado` from (`documentos_cxp` `d` join `proveedores` `p` on((`p`.`id` = `d`.`proveedor_id`))) */;
/*!50001 DROP VIEW IF EXISTS `vw_libro_compras`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`christian`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `vw_libro_compras` AS select `d`.`id` AS `id`,`d`.`cliente_id` AS `cliente_id`,`d`.`sitio_id` AS `sitio_id`,`d`.`proveedor_id` AS `proveedor_id`,`d`.`tipo_documento_id` AS `tipo_documento_id`,`d`.`moneda_id` AS `moneda_id`,`d`.`fecha_emision` AS `fecha_emision`,`td`.`codigo` AS `tipo_documento_codigo`,`td`.`nombre` AS `tipo_documento_nombre`,`d`.`serie` AS `serie`,`d`.`numero` AS `numero`,`p`.`nit` AS `proveedor_nit`,`p`.`razon_social` AS `proveedor_nombre`,`d`.`subtotal` AS `compra_neta`,`d`.`impuesto_total` AS `iva`,`d`.`idp_total` AS `idp`,`d`.`retencion_total` AS `retencion_total`,`d`.`total_bruto` AS `total_bruto`,`d`.`total_neto` AS `total_neto`,`d`.`tipo_cambio` AS `tipo_cambio`,`d`.`estado` AS `estado` from ((`documentos_cxp` `d` join `proveedores` `p` on((`d`.`proveedor_id` = `p`.`id`))) join `tipos_documento` `td` on((`d`.`tipo_documento_id` = `td`.`id`))) where (`d`.`estado` <> 0) */;
/*!50001 DROP VIEW IF EXISTS `vw_resumen_proveedores`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`christian`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `vw_resumen_proveedores` AS select `d`.`cliente_id` AS `cliente_id`,`d`.`proveedor_id` AS `proveedor_id`,`p`.`razon_social` AS `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` */;


