-- ============================================================ -- ERP TINSA — Esquema consolidado de `tinsa_db` -- Generado: 2026-10-26 · MariaDB 10.4 · InnoDB / utf8mb4 -- ------------------------------------------------------------ -- Estado del esquema TRAS aplicar todas las migraciones de -- database/migrations/ hasta 2026-10-26_05_asignar_tareas. -- -- Solo estructura: tablas, la función fn_stock_delta y los -- triggers. Los catálogos del sistema (módulos, pestañas, -- permisos, catálogos del MH) los cargan las migraciones y -- database/seeds/. Ver database/README.md. -- ============================================================ /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Current Database: `tinsa_db` -- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `tinsa_db` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */; USE `tinsa_db`; -- -- Table structure for table `acceso` -- DROP TABLE IF EXISTS `acceso`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `acceso` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_usuario` int(10) unsigned NOT NULL, `fecha_inicio` timestamp NOT NULL DEFAULT current_timestamp(), `ultima_actividad` timestamp NOT NULL DEFAULT current_timestamp(), `fecha_fin` timestamp NULL DEFAULT NULL, `ip` varchar(45) DEFAULT NULL, `user_agent` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `fk_acceso_usuario` (`id_usuario`), CONSTRAINT `fk_acceso_usuario` FOREIGN KEY (`id_usuario`) REFERENCES `usuarios` (`id_usuario`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `bitacora` -- DROP TABLE IF EXISTS `bitacora`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `bitacora` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_usuario` int(10) unsigned DEFAULT NULL, `ruta_pestana` varchar(150) NOT NULL, `metodo` varchar(6) NOT NULL, `uri` varchar(255) NOT NULL, `id_registro` varchar(40) DEFAULT NULL COMMENT 'primer parámetro {id} de la ruta', `accion` varchar(500) DEFAULT NULL COMMENT 'mensaje de la respuesta', `exito` tinyint(1) DEFAULT NULL, `codigo_http` smallint(5) unsigned DEFAULT NULL, `datos` mediumtext DEFAULT NULL COMMENT 'JSON de lo enviado, sin contraseñas', `ip` varchar(45) DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `idx_bitacora_pestana` (`ruta_pestana`,`created_at`), KEY `idx_bitacora_usuario` (`id_usuario`,`created_at`), CONSTRAINT `fk_bitacora_usuario` FOREIGN KEY (`id_usuario`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `bitacora_cambio` -- DROP TABLE IF EXISTS `bitacora_cambio`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `bitacora_cambio` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_bitacora` int(10) unsigned NOT NULL, `tabla` varchar(64) NOT NULL, `id_fila` varchar(40) DEFAULT NULL, `operacion` enum('crear','editar','eliminar') NOT NULL, `campo` varchar(64) NOT NULL, `antes` text DEFAULT NULL, `despues` text DEFAULT NULL, PRIMARY KEY (`id`), KEY `fk_bitacora_cambio` (`id_bitacora`), CONSTRAINT `fk_bitacora_cambio` FOREIGN KEY (`id_bitacora`) REFERENCES `bitacora` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `bodega` -- DROP TABLE IF EXISTS `bodega`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `bodega` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `nombre` varchar(150) NOT NULL, `id_sucursal` int(10) unsigned NOT NULL, `id_tipo_bodega` int(10) unsigned NOT NULL, `direccion` text DEFAULT NULL, `descripcion` text DEFAULT NULL, `capacidad_maxima` decimal(12,2) DEFAULT NULL, `latitud` decimal(10,7) DEFAULT NULL, `longitud` decimal(10,7) DEFAULT NULL, `imagen` varchar(255) DEFAULT NULL, `estado` tinyint(1) NOT NULL DEFAULT 1, `usuario_modifico` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), KEY `fk_bodega_sucursal` (`id_sucursal`), KEY `fk_bodega_tipo` (`id_tipo_bodega`), KEY `fk_bodega_usuario_modifico` (`usuario_modifico`), CONSTRAINT `fk_bodega_sucursal` FOREIGN KEY (`id_sucursal`) REFERENCES `sucursal` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_bodega_tipo` FOREIGN KEY (`id_tipo_bodega`) REFERENCES `tipo_bodega` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_bodega_usuario_modifico` FOREIGN KEY (`usuario_modifico`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `caja_chica` -- DROP TABLE IF EXISTS `caja_chica`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `caja_chica` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `nombre` varchar(120) NOT NULL, `id_sucursal` int(10) unsigned DEFAULT NULL, `id_custodio` int(10) unsigned NOT NULL, `monto_fondo` decimal(12,2) NOT NULL, `limite_por_gasto` decimal(12,2) DEFAULT NULL COMMENT 'monto máximo de un gasto (null = sin límite)', `umbral_reposicion` tinyint(3) unsigned NOT NULL DEFAULT 25 COMMENT '% del fondo bajo el cual se sugiere reponer', `activo` tinyint(1) NOT NULL DEFAULT 1, `usuario_modifico` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_caja_chica_nombre` (`nombre`), KEY `fk_caja_sucursal` (`id_sucursal`), KEY `fk_caja_custodio` (`id_custodio`), KEY `fk_caja_usuario` (`usuario_modifico`), CONSTRAINT `fk_caja_custodio` FOREIGN KEY (`id_custodio`) REFERENCES `usuarios` (`id_usuario`) ON UPDATE CASCADE, CONSTRAINT `fk_caja_sucursal` FOREIGN KEY (`id_sucursal`) REFERENCES `sucursal` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_caja_usuario` FOREIGN KEY (`usuario_modifico`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `chk_caja_fondo` CHECK (`monto_fondo` > 0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `caja_chica_arqueo` -- DROP TABLE IF EXISTS `caja_chica_arqueo`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `caja_chica_arqueo` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_caja` int(10) unsigned NOT NULL, `fecha` datetime NOT NULL DEFAULT current_timestamp(), `saldo_sistema` decimal(12,2) NOT NULL, `efectivo_contado` decimal(12,2) NOT NULL, `diferencia` decimal(12,2) NOT NULL, `denominaciones` text DEFAULT NULL COMMENT 'JSON {denominación: cantidad}', `observaciones` varchar(500) DEFAULT NULL, `id_usuario` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`id`), KEY `fk_cc_arqueo_caja` (`id_caja`), KEY `fk_cc_arqueo_usuario` (`id_usuario`), CONSTRAINT `fk_cc_arqueo_caja` FOREIGN KEY (`id_caja`) REFERENCES `caja_chica` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_cc_arqueo_usuario` FOREIGN KEY (`id_usuario`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `caja_chica_categoria` -- DROP TABLE IF EXISTS `caja_chica_categoria`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `caja_chica_categoria` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `nombre` varchar(80) NOT NULL, `activo` tinyint(1) NOT NULL DEFAULT 1, PRIMARY KEY (`id`), UNIQUE KEY `uq_cc_categoria` (`nombre`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `caja_chica_fondo_historial` -- DROP TABLE IF EXISTS `caja_chica_fondo_historial`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `caja_chica_fondo_historial` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_caja` int(10) unsigned NOT NULL, `evento` varchar(30) NOT NULL, `monto_antes` decimal(12,2) DEFAULT NULL, `monto_despues` decimal(12,2) DEFAULT NULL, `detalle` varchar(255) DEFAULT NULL, `id_usuario` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `fk_cc_fondo_caja` (`id_caja`), KEY `fk_cc_fondo_usuario` (`id_usuario`), CONSTRAINT `fk_cc_fondo_caja` FOREIGN KEY (`id_caja`) REFERENCES `caja_chica` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_cc_fondo_usuario` FOREIGN KEY (`id_usuario`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `caja_chica_gasto` -- DROP TABLE IF EXISTS `caja_chica_gasto`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `caja_chica_gasto` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_caja` int(10) unsigned NOT NULL, `id_reposicion` int(10) unsigned DEFAULT NULL, `fecha` date NOT NULL, `monto` decimal(12,2) NOT NULL, `id_categoria` int(10) unsigned DEFAULT NULL, `descripcion` varchar(255) NOT NULL, `proveedor` varchar(150) DEFAULT NULL, `tipo_comprobante` enum('factura','ccf','ticket','recibo','sujeto_excluido','sin_comprobante') NOT NULL DEFAULT 'factura', `numero_comprobante` varchar(60) DEFAULT NULL, `id_factura` int(10) unsigned DEFAULT NULL COMMENT 'factura de sujeto excluido (14) emitida en Facturación', `id_cotizacion_venta` int(10) unsigned DEFAULT NULL COMMENT 'proyecto al que se carga', `archivo` varchar(80) DEFAULT NULL, `archivo_nombre` varchar(255) DEFAULT NULL, `estado` enum('registrado','en_revision','aprobado','repuesto','rechazado') NOT NULL DEFAULT 'registrado', `motivo_rechazo` varchar(255) DEFAULT NULL, `id_usuario` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), KEY `fk_cc_gasto_caja` (`id_caja`), KEY `fk_cc_gasto_repo` (`id_reposicion`), KEY `fk_cc_gasto_categoria` (`id_categoria`), KEY `fk_cc_gasto_factura` (`id_factura`), KEY `fk_cc_gasto_proyecto` (`id_cotizacion_venta`), KEY `fk_cc_gasto_usuario` (`id_usuario`), CONSTRAINT `fk_cc_gasto_caja` FOREIGN KEY (`id_caja`) REFERENCES `caja_chica` (`id`) ON UPDATE CASCADE, CONSTRAINT `fk_cc_gasto_categoria` FOREIGN KEY (`id_categoria`) REFERENCES `caja_chica_categoria` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_cc_gasto_factura` FOREIGN KEY (`id_factura`) REFERENCES `factura` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_cc_gasto_proyecto` FOREIGN KEY (`id_cotizacion_venta`) REFERENCES `cotizacion_venta` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_cc_gasto_repo` FOREIGN KEY (`id_reposicion`) REFERENCES `caja_chica_reposicion` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_cc_gasto_usuario` FOREIGN KEY (`id_usuario`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `chk_cc_gasto_monto` CHECK (`monto` > 0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `caja_chica_reposicion` -- DROP TABLE IF EXISTS `caja_chica_reposicion`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `caja_chica_reposicion` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_caja` int(10) unsigned NOT NULL, `numero` varchar(20) NOT NULL, `estado` enum('pendiente','aprobada','rechazada','pagada') NOT NULL DEFAULT 'pendiente', `total_gastos` decimal(12,2) NOT NULL DEFAULT 0.00, `total_aprobado` decimal(12,2) NOT NULL DEFAULT 0.00, `observaciones` varchar(500) DEFAULT NULL, `id_usuario_solicito` int(10) unsigned DEFAULT NULL, `fecha_solicitud` datetime NOT NULL DEFAULT current_timestamp(), `id_usuario_aprobo` int(10) unsigned DEFAULT NULL, `fecha_aprobacion` datetime DEFAULT NULL, `comentario_aprobacion` varchar(500) DEFAULT NULL, `forma_reembolso` enum('efectivo','cheque','transferencia') DEFAULT NULL, `referencia_reembolso` varchar(100) DEFAULT NULL, `id_usuario_pago` int(10) unsigned DEFAULT NULL, `fecha_pago` datetime DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uq_cc_repo_numero` (`numero`), KEY `fk_cc_repo_caja` (`id_caja`), KEY `fk_cc_repo_solicito` (`id_usuario_solicito`), KEY `fk_cc_repo_aprobo` (`id_usuario_aprobo`), KEY `fk_cc_repo_pago` (`id_usuario_pago`), CONSTRAINT `fk_cc_repo_aprobo` FOREIGN KEY (`id_usuario_aprobo`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_cc_repo_caja` FOREIGN KEY (`id_caja`) REFERENCES `caja_chica` (`id`) ON UPDATE CASCADE, CONSTRAINT `fk_cc_repo_pago` FOREIGN KEY (`id_usuario_pago`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_cc_repo_solicito` FOREIGN KEY (`id_usuario_solicito`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `catalogo_producto` -- DROP TABLE IF EXISTS `catalogo_producto`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `catalogo_producto` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `codigo` varchar(50) NOT NULL, `id_subcategoria` int(10) unsigned NOT NULL, `id_medida` int(10) unsigned NOT NULL, `sueldo_categoria` decimal(10,2) DEFAULT NULL, `descripcion` text DEFAULT NULL, `caracteristicas_campos` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`caracteristicas_campos`)), `estado` tinyint(1) NOT NULL DEFAULT 1, `usuario_modifico` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `codigo` (`codigo`), KEY `fk_catalogo_subcategoria` (`id_subcategoria`), KEY `fk_catalogo_medida` (`id_medida`), KEY `fk_catalogo_producto_usuario_modifico` (`usuario_modifico`), CONSTRAINT `fk_catalogo_medida` FOREIGN KEY (`id_medida`) REFERENCES `medida` (`id`) ON UPDATE CASCADE, CONSTRAINT `fk_catalogo_producto_usuario_modifico` FOREIGN KEY (`usuario_modifico`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_catalogo_subcategoria` FOREIGN KEY (`id_subcategoria`) REFERENCES `subcategoria` (`id`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `categoria` -- DROP TABLE IF EXISTS `categoria`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `categoria` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `nombre` varchar(100) NOT NULL, `estado` tinyint(1) NOT NULL DEFAULT 1, `usuario_modifico` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `codigo` varchar(50) NOT NULL, PRIMARY KEY (`id`), KEY `fk_categoria_usuario_modifico` (`usuario_modifico`), CONSTRAINT `fk_categoria_usuario_modifico` FOREIGN KEY (`usuario_modifico`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `cliente` -- DROP TABLE IF EXISTS `cliente`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cliente` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `tipo_cliente` enum('natural','juridico') NOT NULL, `nombres` varchar(150) DEFAULT NULL, `apellidos` varchar(150) DEFAULT NULL, `dui` varchar(15) DEFAULT NULL, `razon_social` varchar(150) DEFAULT NULL, `nombre_comercial` varchar(150) DEFAULT NULL, `tipo_empresa` enum('publica','privada') DEFAULT NULL, `nrc` varchar(20) DEFAULT NULL, `nit` varchar(20) DEFAULT NULL, `tipo_contribuyente` enum('pequeno','mediano','grande') NOT NULL DEFAULT 'pequeno', `giro` varchar(150) DEFAULT NULL, `cod_actividad` varchar(6) DEFAULT NULL, `correo` varchar(150) DEFAULT NULL, `telefono` varchar(30) DEFAULT NULL, `direccion` text DEFAULT NULL, `cod_departamento` char(2) DEFAULT NULL, `cod_municipio` char(2) DEFAULT NULL, `cod_distrito` char(2) DEFAULT NULL COMMENT 'CAT-008', `imagen` varchar(255) DEFAULT NULL, `estado` tinyint(1) NOT NULL DEFAULT 1, `nombre` varchar(300) GENERATED ALWAYS AS (case when `tipo_cliente` = 'natural' then trim(concat(coalesce(`nombres`,''),' ',coalesce(`apellidos`,''))) else `razon_social` end) STORED, `usuario_modifico` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), KEY `idx_cliente_tipo` (`tipo_cliente`), KEY `idx_cliente_nombre` (`nombre`), KEY `fk_cliente_usuario_modifico` (`usuario_modifico`), CONSTRAINT `fk_cliente_usuario_modifico` FOREIGN KEY (`usuario_modifico`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `cliente_contacto` -- DROP TABLE IF EXISTS `cliente_contacto`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cliente_contacto` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_cliente` int(10) unsigned NOT NULL, `nombre` varchar(150) NOT NULL, `cargo` varchar(100) DEFAULT NULL, `correo` varchar(150) DEFAULT NULL, `telefono` varchar(30) DEFAULT NULL, `nota` text DEFAULT NULL, `activo` tinyint(1) NOT NULL DEFAULT 1, `usuario_modifico` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), KEY `idx_cliente_contacto_cliente` (`id_cliente`,`activo`), KEY `fk_cliente_contacto_usuario_modifico` (`usuario_modifico`), CONSTRAINT `fk_cliente_contacto_cliente` FOREIGN KEY (`id_cliente`) REFERENCES `cliente` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_cliente_contacto_usuario_modifico` FOREIGN KEY (`usuario_modifico`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `cliente_usuario` -- DROP TABLE IF EXISTS `cliente_usuario`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cliente_usuario` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_cliente` int(10) unsigned NOT NULL, `id_usuario` int(10) unsigned NOT NULL, `es_principal` tinyint(1) NOT NULL DEFAULT 0, `usuario_asigno` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_cliente_usuario` (`id_cliente`,`id_usuario`), KEY `idx_cliente_usuario_usuario` (`id_usuario`), KEY `fk_cliente_usuario_asigno` (`usuario_asigno`), CONSTRAINT `fk_cliente_usuario_asigno` FOREIGN KEY (`usuario_asigno`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_cliente_usuario_cliente` FOREIGN KEY (`id_cliente`) REFERENCES `cliente` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_cliente_usuario_usuario` FOREIGN KEY (`id_usuario`) REFERENCES `usuarios` (`id_usuario`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `compra_dte` -- DROP TABLE IF EXISTS `compra_dte`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `compra_dte` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `codigo_generacion` char(36) NOT NULL, `numero_control` varchar(31) NOT NULL, `tipo_dte` char(2) NOT NULL, `fecha_emision` date NOT NULL, `id_proveedor` int(10) unsigned NOT NULL, `emisor_nombre` varchar(250) NOT NULL, `emisor_nit` varchar(20) DEFAULT NULL, `emisor_nrc` varchar(20) DEFAULT NULL, `modo` enum('orden','directa','nota_credito') NOT NULL, `id_orden` int(10) unsigned DEFAULT NULL, `id_recepcion` int(10) unsigned DEFAULT NULL, `id_compra_relacionada` int(10) unsigned DEFAULT NULL COMMENT 'nota de crédito: la compra que modifica', `efecto_nota` enum('rebaja_pago','solo_registro') DEFAULT NULL, `monto_rebajado` decimal(14,2) NOT NULL DEFAULT 0.00 COMMENT 'lo que la nota rebajó del pago pendiente', `id_bodega` int(10) unsigned DEFAULT NULL, `total_gravada` decimal(14,2) NOT NULL DEFAULT 0.00, `iva` decimal(14,2) NOT NULL DEFAULT 0.00, `total_pagar` decimal(14,2) NOT NULL DEFAULT 0.00, `json_original` longtext NOT NULL, `observaciones` varchar(500) DEFAULT NULL, `id_usuario` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_compra_dte_codigo` (`codigo_generacion`), KEY `fk_compra_dte_proveedor` (`id_proveedor`), KEY `fk_compra_dte_orden` (`id_orden`), KEY `fk_compra_dte_recepcion` (`id_recepcion`), KEY `fk_compra_dte_bodega` (`id_bodega`), KEY `fk_compra_dte_usuario` (`id_usuario`), KEY `fk_compra_dte_relacionada` (`id_compra_relacionada`), CONSTRAINT `fk_compra_dte_bodega` FOREIGN KEY (`id_bodega`) REFERENCES `bodega` (`id`) ON UPDATE CASCADE, CONSTRAINT `fk_compra_dte_orden` FOREIGN KEY (`id_orden`) REFERENCES `orden_compra` (`id`) ON UPDATE CASCADE, CONSTRAINT `fk_compra_dte_proveedor` FOREIGN KEY (`id_proveedor`) REFERENCES `proveedor` (`id`) ON UPDATE CASCADE, CONSTRAINT `fk_compra_dte_recepcion` FOREIGN KEY (`id_recepcion`) REFERENCES `orden_compra_recepcion` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_compra_dte_relacionada` FOREIGN KEY (`id_compra_relacionada`) REFERENCES `compra_dte` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_compra_dte_usuario` FOREIGN KEY (`id_usuario`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `compra_dte_item` -- DROP TABLE IF EXISTS `compra_dte_item`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `compra_dte_item` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_compra` int(10) unsigned NOT NULL, `num_item` smallint(5) unsigned NOT NULL, `codigo` varchar(60) DEFAULT NULL, `descripcion` varchar(1000) NOT NULL, `cantidad` decimal(14,4) NOT NULL, `precio_unitario` decimal(14,6) NOT NULL DEFAULT 0.000000, `descuento` decimal(14,2) NOT NULL DEFAULT 0.00, `monto` decimal(14,2) NOT NULL DEFAULT 0.00, `omitido` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'servicio, flete u otra línea que no entra a bodega', `id_producto` int(10) unsigned DEFAULT NULL, `id_orden_item` int(10) unsigned DEFAULT NULL, `factor` decimal(12,4) NOT NULL DEFAULT 1.0000 COMMENT 'unidades de inventario por unidad del DTE', `cantidad_ingresada` decimal(14,4) NOT NULL DEFAULT 0.0000 COMMENT 'en unidades de inventario', `costo_unitario` decimal(14,6) NOT NULL DEFAULT 0.000000 COMMENT 'por unidad de inventario', PRIMARY KEY (`id`), KEY `fk_compra_dte_item_compra` (`id_compra`), KEY `fk_compra_dte_item_producto` (`id_producto`), KEY `fk_compra_dte_item_oc` (`id_orden_item`), CONSTRAINT `fk_compra_dte_item_compra` FOREIGN KEY (`id_compra`) REFERENCES `compra_dte` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_compra_dte_item_oc` FOREIGN KEY (`id_orden_item`) REFERENCES `orden_compra_item` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_compra_dte_item_producto` FOREIGN KEY (`id_producto`) REFERENCES `producto` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `configuracion_sistema` -- DROP TABLE IF EXISTS `configuracion_sistema`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `configuracion_sistema` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `nombre_sistema` varchar(150) NOT NULL DEFAULT 'ERP - TINSA', `logo` varchar(255) DEFAULT NULL, `usuario_modifico` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `color_primario` varchar(7) NOT NULL DEFAULT '#0D2482', `color_primario_oscuro` varchar(7) NOT NULL DEFAULT '#0D2482', `color_secundario` varchar(7) NOT NULL DEFAULT '#00897b', `color_secundario_oscuro` varchar(7) NOT NULL DEFAULT '#00897b', PRIMARY KEY (`id`), KEY `fk_configuracion_sistema_usuario_modifico` (`usuario_modifico`), CONSTRAINT `fk_configuracion_sistema_usuario_modifico` FOREIGN KEY (`usuario_modifico`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `conteo_inventario` -- DROP TABLE IF EXISTS `conteo_inventario`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `conteo_inventario` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `codigo` varchar(20) DEFAULT NULL, `id_bodega` int(10) unsigned NOT NULL, `descripcion` varchar(200) DEFAULT NULL, `estado` enum('abierto','cerrado','anulado') NOT NULL DEFAULT 'abierto', `id_usuario` int(10) unsigned DEFAULT NULL, `id_usuario_cierre` int(10) unsigned DEFAULT NULL, `fecha_cierre` datetime DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_conteo_codigo` (`codigo`), KEY `fk_conteo_bodega` (`id_bodega`), KEY `fk_conteo_usuario` (`id_usuario`), KEY `fk_conteo_usuario_cierre` (`id_usuario_cierre`), CONSTRAINT `fk_conteo_bodega` FOREIGN KEY (`id_bodega`) REFERENCES `bodega` (`id`) ON UPDATE CASCADE, CONSTRAINT `fk_conteo_usuario` FOREIGN KEY (`id_usuario`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_conteo_usuario_cierre` FOREIGN KEY (`id_usuario_cierre`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `conteo_marbete` -- DROP TABLE IF EXISTS `conteo_marbete`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `conteo_marbete` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_conteo` int(10) unsigned NOT NULL, `numero` int(10) unsigned NOT NULL, `id_producto` int(10) unsigned NOT NULL, `id_ubicacion_bodega` int(10) unsigned DEFAULT NULL, `cantidad_sistema` decimal(12,4) NOT NULL DEFAULT 0.0000, `cantidad_contada` decimal(12,4) DEFAULT NULL, `cantidad_recuento` decimal(12,4) DEFAULT NULL, `estado` enum('pendiente','contado','anulado') NOT NULL DEFAULT 'pendiente', `id_usuario_conteo` int(10) unsigned DEFAULT NULL, `fecha_conteo` datetime DEFAULT NULL, `cantidad_sistema_cierre` decimal(12,4) DEFAULT NULL, `cantidad_ajuste` decimal(12,4) DEFAULT NULL, `id_movimiento` int(10) unsigned DEFAULT NULL, `adicional` tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), UNIQUE KEY `uq_marbete_numero` (`id_conteo`,`numero`), UNIQUE KEY `uq_marbete_producto` (`id_conteo`,`id_producto`), KEY `fk_marbete_producto` (`id_producto`), KEY `fk_marbete_ubicacion` (`id_ubicacion_bodega`), KEY `fk_marbete_usuario` (`id_usuario_conteo`), KEY `fk_marbete_movimiento` (`id_movimiento`), CONSTRAINT `fk_marbete_conteo` FOREIGN KEY (`id_conteo`) REFERENCES `conteo_inventario` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_marbete_movimiento` FOREIGN KEY (`id_movimiento`) REFERENCES `movimiento_stock` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_marbete_producto` FOREIGN KEY (`id_producto`) REFERENCES `producto` (`id`) ON UPDATE CASCADE, CONSTRAINT `fk_marbete_ubicacion` FOREIGN KEY (`id_ubicacion_bodega`) REFERENCES `ubicacion_bodega` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_marbete_usuario` FOREIGN KEY (`id_usuario_conteo`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `chk_marbete_cant_no_neg` CHECK ((`cantidad_contada` is null or `cantidad_contada` >= 0) and (`cantidad_recuento` is null or `cantidad_recuento` >= 0)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `cotizacion_compra` -- DROP TABLE IF EXISTS `cotizacion_compra`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cotizacion_compra` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `numero` varchar(20) NOT NULL, `id_solicitud` int(10) unsigned NOT NULL, `id_departamento` int(10) unsigned DEFAULT NULL, `descripcion` text DEFAULT NULL, `monto_aprobado` decimal(12,2) DEFAULT NULL, `estado` enum('borrador','enviada','cerrada','cancelada') NOT NULL DEFAULT 'borrador', `id_usuario_creo` int(10) unsigned NOT NULL, `fecha_envio` datetime DEFAULT NULL, `fecha_cierre` datetime DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_cotizacion_compra_numero` (`numero`), KEY `fk_cc_solicitud` (`id_solicitud`), KEY `fk_cc_departamento` (`id_departamento`), KEY `fk_cc_usuario_creo` (`id_usuario_creo`), CONSTRAINT `fk_cc_departamento` FOREIGN KEY (`id_departamento`) REFERENCES `departamento` (`id`) ON DELETE SET NULL, CONSTRAINT `fk_cc_solicitud` FOREIGN KEY (`id_solicitud`) REFERENCES `solicitud_compra` (`id`), CONSTRAINT `fk_cc_usuario_creo` FOREIGN KEY (`id_usuario_creo`) REFERENCES `usuarios` (`id_usuario`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `cotizacion_compra_item` -- DROP TABLE IF EXISTS `cotizacion_compra_item`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cotizacion_compra_item` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_cotizacion` int(10) unsigned NOT NULL, `id_solicitud_item` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uq_cci` (`id_cotizacion`,`id_solicitud_item`), KEY `fk_cci_item` (`id_solicitud_item`), CONSTRAINT `fk_cci_cotizacion` FOREIGN KEY (`id_cotizacion`) REFERENCES `cotizacion_compra` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_cci_item` FOREIGN KEY (`id_solicitud_item`) REFERENCES `solicitud_compra_item` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `cotizacion_compra_proveedor` -- DROP TABLE IF EXISTS `cotizacion_compra_proveedor`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cotizacion_compra_proveedor` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_cotizacion` int(10) unsigned NOT NULL, `id_proveedor` int(10) unsigned NOT NULL, `estado` enum('invitado','recibida') NOT NULL DEFAULT 'invitado', `tiempo_entrega` varchar(100) DEFAULT NULL, `validez` varchar(100) DEFAULT NULL, `condiciones_pago` varchar(150) DEFAULT NULL, `observaciones` text DEFAULT NULL, `archivo` varchar(255) DEFAULT NULL, `total` decimal(12,2) NOT NULL DEFAULT 0.00, `es_seleccionada` tinyint(1) NOT NULL DEFAULT 0, `fecha_recibida` datetime DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_ccp` (`id_cotizacion`,`id_proveedor`), KEY `fk_ccp_proveedor` (`id_proveedor`), CONSTRAINT `fk_ccp_cotizacion` FOREIGN KEY (`id_cotizacion`) REFERENCES `cotizacion_compra` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_ccp_proveedor` FOREIGN KEY (`id_proveedor`) REFERENCES `proveedor` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `cotizacion_compra_proveedor_item` -- DROP TABLE IF EXISTS `cotizacion_compra_proveedor_item`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cotizacion_compra_proveedor_item` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_cotizacion_proveedor` int(10) unsigned NOT NULL, `id_solicitud_item` int(10) unsigned NOT NULL, `precio_unitario` decimal(12,2) NOT NULL DEFAULT 0.00, `descuento_pct` decimal(5,2) NOT NULL DEFAULT 0.00, `impuesto_pct` decimal(5,2) NOT NULL DEFAULT 0.00, `subtotal` decimal(12,2) NOT NULL DEFAULT 0.00, PRIMARY KEY (`id`), UNIQUE KEY `uq_ccpi` (`id_cotizacion_proveedor`,`id_solicitud_item`), KEY `fk_ccpi_item` (`id_solicitud_item`), CONSTRAINT `fk_ccpi_item` FOREIGN KEY (`id_solicitud_item`) REFERENCES `solicitud_compra_item` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_ccpi_oferta` FOREIGN KEY (`id_cotizacion_proveedor`) REFERENCES `cotizacion_compra_proveedor` (`id`) ON DELETE CASCADE, CONSTRAINT `chk_ccpi_pcts` CHECK (`descuento_pct` between 0 and 100 and `impuesto_pct` between 0 and 100) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `cotizacion_venta` -- DROP TABLE IF EXISTS `cotizacion_venta`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cotizacion_venta` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `numero` varchar(50) NOT NULL, `id_cliente` int(10) unsigned NOT NULL, `id_cliente_contacto` int(10) unsigned DEFAULT NULL, `solicitante_nombre` varchar(150) DEFAULT NULL, `solicitante_correo` varchar(150) DEFAULT NULL, `solicitante_telefono` varchar(30) DEFAULT NULL, `id_usuario_vendedor` int(10) unsigned NOT NULL, `id_usuario_cotizador` int(10) unsigned DEFAULT NULL, `estado` enum('borrador','pendiente_presupuesto','presupuestada','enviada','aprobada','rechazada','cancelada') NOT NULL DEFAULT 'borrador', `fecha` datetime NOT NULL DEFAULT current_timestamp(), `fecha_necesidad` date DEFAULT NULL, `nombre_proyecto` varchar(200) DEFAULT NULL, `descripcion_proyecto` text DEFAULT NULL, `lugar_entrega` varchar(200) DEFAULT NULL, `direccion_entrega` text DEFAULT NULL, `tiempo_entrega` varchar(100) DEFAULT NULL, `tiempo_instalacion` varchar(100) DEFAULT NULL, `garantia` varchar(150) DEFAULT NULL, `validez_oferta_dias` smallint(5) unsigned DEFAULT NULL, `condiciones_pago` text DEFAULT NULL, `tipo_margen` enum('global','por_item') NOT NULL DEFAULT 'global', `margen_global` decimal(5,2) DEFAULT NULL, `observaciones` text DEFAULT NULL, `subtotal` decimal(12,2) NOT NULL DEFAULT 0.00, `total` decimal(12,2) NOT NULL DEFAULT 0.00, `costo_total` decimal(12,2) NOT NULL DEFAULT 0.00, `usuario_modifico` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_cotizacion_venta_numero` (`numero`), KEY `idx_cv_cliente` (`id_cliente`), KEY `idx_cv_estado` (`estado`), KEY `fk_cotizacion_venta_usuario_modifico` (`usuario_modifico`), KEY `fk_cotizacion_venta_usuario_vendedor` (`id_usuario_vendedor`), KEY `fk_cotizacion_venta_usuario_cotizador` (`id_usuario_cotizador`), KEY `fk_cotizacion_venta_contacto` (`id_cliente_contacto`), CONSTRAINT `fk_cotizacion_venta_contacto` FOREIGN KEY (`id_cliente_contacto`) REFERENCES `cliente_contacto` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_cotizacion_venta_usuario_cotizador` FOREIGN KEY (`id_usuario_cotizador`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_cotizacion_venta_usuario_modifico` FOREIGN KEY (`usuario_modifico`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_cotizacion_venta_usuario_vendedor` FOREIGN KEY (`id_usuario_vendedor`) REFERENCES `usuarios` (`id_usuario`) ON UPDATE CASCADE, CONSTRAINT `fk_cv_cliente` FOREIGN KEY (`id_cliente`) REFERENCES `cliente` (`id`), CONSTRAINT `chk_cv_margen_global` CHECK (`margen_global` is null or `margen_global` >= 0 and `margen_global` < 100) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `cotizacion_venta_adjunto` -- DROP TABLE IF EXISTS `cotizacion_venta_adjunto`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cotizacion_venta_adjunto` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_cotizacion_venta` int(10) unsigned NOT NULL, `tipo` enum('imagen','documento','cad','otro') NOT NULL DEFAULT 'documento', `nombre_original` varchar(255) NOT NULL, `archivo` varchar(255) NOT NULL, `mime` varchar(120) DEFAULT NULL, `tamano` int(10) unsigned NOT NULL DEFAULT 0, `usuario_subio` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `idx_cva_cotizacion` (`id_cotizacion_venta`), KEY `fk_cva_usuario` (`usuario_subio`), CONSTRAINT `fk_cva_cotizacion` FOREIGN KEY (`id_cotizacion_venta`) REFERENCES `cotizacion_venta` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_cva_usuario` FOREIGN KEY (`usuario_subio`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `cotizacion_venta_historial` -- DROP TABLE IF EXISTS `cotizacion_venta_historial`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cotizacion_venta_historial` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_cotizacion_venta` int(10) unsigned NOT NULL, `tipo` varchar(40) NOT NULL, `descripcion` varchar(255) NOT NULL, `cambios` text DEFAULT NULL, `id_usuario` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `idx_cvh_cotizacion` (`id_cotizacion_venta`), KEY `fk_cvh_usuario` (`id_usuario`), CONSTRAINT `fk_cvh_cotizacion` FOREIGN KEY (`id_cotizacion_venta`) REFERENCES `cotizacion_venta` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_cvh_usuario` FOREIGN KEY (`id_usuario`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `cotizacion_venta_item` -- DROP TABLE IF EXISTS `cotizacion_venta_item`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cotizacion_venta_item` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_cotizacion_venta` int(10) unsigned NOT NULL, `descripcion` varchar(255) NOT NULL, `cantidad` decimal(12,4) NOT NULL DEFAULT 1.0000, `unidad` varchar(30) DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `idx_cvi_cotizacion` (`id_cotizacion_venta`), CONSTRAINT `fk_cvi_cotizacion` FOREIGN KEY (`id_cotizacion_venta`) REFERENCES `cotizacion_venta` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `cotizacion_venta_material` -- DROP TABLE IF EXISTS `cotizacion_venta_material`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cotizacion_venta_material` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_oferta` int(10) unsigned NOT NULL, `id_item` int(10) unsigned NOT NULL, `origen` enum('paquete','extra') NOT NULL DEFAULT 'extra', `id_producto` int(10) unsigned DEFAULT NULL, `id_paquete_producto` int(10) unsigned DEFAULT NULL, `descripcion` varchar(255) DEFAULT NULL, `unidad` varchar(30) DEFAULT NULL, `cantidad` decimal(12,4) NOT NULL DEFAULT 0.0000, `costo_unitario` decimal(10,2) NOT NULL DEFAULT 0.00, `precio_unitario` decimal(10,2) NOT NULL DEFAULT 0.00, `margen` decimal(5,2) DEFAULT NULL, `subtotal_costo` decimal(12,2) NOT NULL DEFAULT 0.00, `subtotal_venta` decimal(12,2) NOT NULL DEFAULT 0.00, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `idx_cvm_producto` (`id_producto`), KEY `idx_cvm_oferta` (`id_oferta`), KEY `idx_cvm_paquete_producto` (`id_paquete_producto`), KEY `idx_cvm_item` (`id_item`), CONSTRAINT `fk_cvm_item` FOREIGN KEY (`id_item`) REFERENCES `cotizacion_venta_oferta_item` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_cvm_oferta` FOREIGN KEY (`id_oferta`) REFERENCES `cotizacion_venta_oferta` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_cvm_paquete_producto` FOREIGN KEY (`id_paquete_producto`) REFERENCES `paquete_producto` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_cvm_producto` FOREIGN KEY (`id_producto`) REFERENCES `producto` (`id`), CONSTRAINT `chk_cvm_origen` CHECK (`id_producto` is not null or `descripcion` is not null) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `cotizacion_venta_oferta` -- DROP TABLE IF EXISTS `cotizacion_venta_oferta`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cotizacion_venta_oferta` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_cotizacion_venta` int(10) unsigned NOT NULL, `numero` varchar(50) NOT NULL, `nombre` varchar(150) DEFAULT NULL, `nota` text DEFAULT NULL, `estado` enum('borrador','lista','seleccionada','descartada') NOT NULL DEFAULT 'borrador', `tipo_margen` enum('global','por_item') NOT NULL DEFAULT 'global', `margen_global` decimal(5,2) DEFAULT NULL, `subtotal` decimal(12,2) NOT NULL DEFAULT 0.00, `iva` decimal(12,2) NOT NULL DEFAULT 0.00, `total` decimal(12,2) NOT NULL DEFAULT 0.00, `costo_total` decimal(12,2) NOT NULL DEFAULT 0.00, `id_usuario_creo` int(10) unsigned DEFAULT NULL, `fecha_lista` datetime DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_cvo_numero` (`numero`), KEY `idx_cvo_cotizacion` (`id_cotizacion_venta`), KEY `fk_cvo_usuario` (`id_usuario_creo`), CONSTRAINT `fk_cvo_cotizacion` FOREIGN KEY (`id_cotizacion_venta`) REFERENCES `cotizacion_venta` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_cvo_usuario` FOREIGN KEY (`id_usuario_creo`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `chk_cvo_margen_global` CHECK (`margen_global` is null or `margen_global` >= 0 and `margen_global` < 100) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `cotizacion_venta_oferta_documento` -- DROP TABLE IF EXISTS `cotizacion_venta_oferta_documento`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cotizacion_venta_oferta_documento` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_oferta` int(10) unsigned NOT NULL, `id_cotizacion_venta` int(10) unsigned NOT NULL, `tipo` enum('imagen','documento','cad','otro') NOT NULL DEFAULT 'documento', `nombre_original` varchar(255) NOT NULL, `archivo` varchar(255) NOT NULL, `mime` varchar(120) DEFAULT NULL, `tamano` int(10) unsigned NOT NULL DEFAULT 0, `descripcion` varchar(255) DEFAULT NULL, `usuario_subio` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `idx_cvod_oferta` (`id_oferta`), KEY `fk_cvod_cotizacion` (`id_cotizacion_venta`), KEY `fk_cvod_usuario` (`usuario_subio`), CONSTRAINT `fk_cvod_cotizacion` FOREIGN KEY (`id_cotizacion_venta`) REFERENCES `cotizacion_venta` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_cvod_oferta` FOREIGN KEY (`id_oferta`) REFERENCES `cotizacion_venta_oferta` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_cvod_usuario` FOREIGN KEY (`usuario_subio`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `cotizacion_venta_oferta_item` -- DROP TABLE IF EXISTS `cotizacion_venta_oferta_item`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cotizacion_venta_oferta_item` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_oferta` int(10) unsigned NOT NULL, `titulo` varchar(200) NOT NULL, `descripcion` text DEFAULT NULL, `cantidad` decimal(12,4) NOT NULL DEFAULT 1.0000, `unidad` varchar(30) DEFAULT NULL, `incluye_instalacion` tinyint(1) DEFAULT NULL, `id_paquete` int(10) unsigned DEFAULT NULL, `paquete_parametros` text DEFAULT NULL, `imprevisto_tipo` enum('valor','porcentaje') DEFAULT NULL COMMENT 'monto fijo o % sobre el costo de los materiales', `imprevisto_valor` decimal(12,2) DEFAULT NULL COMMENT 'el monto o el porcentaje escrito', `imprevisto_monto` decimal(12,2) NOT NULL DEFAULT 0.00 COMMENT 'imprevisto en dinero (ya sumado en costo_total)', `costo_total` decimal(12,2) NOT NULL DEFAULT 0.00, `margen` decimal(6,2) DEFAULT NULL, `precio` decimal(12,2) NOT NULL DEFAULT 0.00, `orden` int(11) NOT NULL DEFAULT 0, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `idx_cvoi_oferta` (`id_oferta`), KEY `fk_cvoi_paquete` (`id_paquete`), CONSTRAINT `fk_cvoi_oferta` FOREIGN KEY (`id_oferta`) REFERENCES `cotizacion_venta_oferta` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_cvoi_paquete` FOREIGN KEY (`id_paquete`) REFERENCES `paquete` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `chk_item_imprevisto` CHECK (`imprevisto_valor` is null or `imprevisto_valor` >= 0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `cuenta_fondos` -- DROP TABLE IF EXISTS `cuenta_fondos`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cuenta_fondos` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `nombre` varchar(120) NOT NULL, `tipo` enum('efectivo','banco') NOT NULL, `banco` varchar(80) DEFAULT NULL, `numero_cuenta` varchar(40) DEFAULT NULL, `tipo_cuenta` enum('corriente','ahorro') DEFAULT NULL, `id_sucursal` int(10) unsigned DEFAULT NULL, `saldo_inicial` decimal(14,2) NOT NULL DEFAULT 0.00 COMMENT 'saldo a la fecha_saldo_inicial, antes de los movimientos', `fecha_saldo_inicial` date NOT NULL, `activo` tinyint(1) NOT NULL DEFAULT 1, `usuario_modifico` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_cuenta_fondos_nombre` (`nombre`), KEY `fk_cuenta_fondos_sucursal` (`id_sucursal`), KEY `fk_cuenta_fondos_usuario` (`usuario_modifico`), CONSTRAINT `fk_cuenta_fondos_sucursal` FOREIGN KEY (`id_sucursal`) REFERENCES `sucursal` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_cuenta_fondos_usuario` FOREIGN KEY (`usuario_modifico`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Caja general y cuentas bancarias'; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `cuenta_movimiento` -- DROP TABLE IF EXISTS `cuenta_movimiento`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cuenta_movimiento` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_cuenta` int(10) unsigned NOT NULL, `fecha` date NOT NULL, `tipo` enum('cobro','reposicion','pago_proveedor','pago_mano_obra','transferencia','ingreso','egreso') NOT NULL, `monto` decimal(14,2) NOT NULL COMMENT 'con signo: + entra, − sale', `concepto` varchar(255) NOT NULL, `referencia` varchar(100) DEFAULT NULL, `id_factura_pago` int(10) unsigned DEFAULT NULL, `id_reposicion` int(10) unsigned DEFAULT NULL, `id_recepcion` int(10) unsigned DEFAULT NULL COMMENT 'recepción de orden de compra pagada', `id_pago_mano_obra` int(10) unsigned DEFAULT NULL, `id_transferencia` int(10) unsigned DEFAULT NULL COMMENT 'mismo valor en las dos filas de una transferencia', `id_usuario` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_movimiento_pago` (`id_factura_pago`), UNIQUE KEY `uq_movimiento_recepcion` (`id_recepcion`), UNIQUE KEY `uq_movimiento_pago_mo` (`id_pago_mano_obra`), KEY `idx_movimiento_cuenta_fecha` (`id_cuenta`,`fecha`), KEY `idx_movimiento_transferencia` (`id_transferencia`), KEY `fk_movimiento_reposicion` (`id_reposicion`), KEY `fk_movimiento_usuario` (`id_usuario`), CONSTRAINT `fk_movimiento_cuenta` FOREIGN KEY (`id_cuenta`) REFERENCES `cuenta_fondos` (`id`) ON UPDATE CASCADE, CONSTRAINT `fk_movimiento_pago` FOREIGN KEY (`id_factura_pago`) REFERENCES `factura_pago` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_movimiento_pago_mo` FOREIGN KEY (`id_pago_mano_obra`) REFERENCES `pago_mano_obra` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_movimiento_recepcion` FOREIGN KEY (`id_recepcion`) REFERENCES `orden_compra_recepcion` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_movimiento_reposicion` FOREIGN KEY (`id_reposicion`) REFERENCES `caja_chica_reposicion` (`id`) ON UPDATE CASCADE, CONSTRAINT `fk_movimiento_usuario` FOREIGN KEY (`id_usuario`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `chk_movimiento_monto` CHECK (`monto` <> 0), CONSTRAINT `chk_movimiento_signo` CHECK (`tipo` in ('cobro','ingreso') and `monto` > 0 or `tipo` in ('reposicion','pago_proveedor','pago_mano_obra','egreso') and `monto` < 0 or `tipo` = 'transferencia') ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Libro de cada cuenta de fondos: el saldo es saldo_inicial + SUM(monto)'; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `departamento` -- DROP TABLE IF EXISTS `departamento`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `departamento` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `nombre` varchar(100) NOT NULL, `estado` tinyint(1) NOT NULL DEFAULT 1, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_departamento_nombre` (`nombre`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `dte_contingencia` -- DROP TABLE IF EXISTS `dte_contingencia`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `dte_contingencia` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `codigo_generacion` char(36) NOT NULL, `ambiente` char(2) NOT NULL DEFAULT '00', `tipo` tinyint(3) unsigned NOT NULL COMMENT 'CAT-005: 1 MH no disponible, 2 sistema del emisor, 3 internet del emisor, 4 energía, 5 otro', `motivo` varchar(500) DEFAULT NULL, `fecha_inicio` datetime NOT NULL, `fecha_fin` datetime NOT NULL, `estado` enum('pendiente','recibido','rechazado') NOT NULL DEFAULT 'pendiente', `sello_recepcion` varchar(100) DEFAULT NULL, `observaciones_mh` text DEFAULT NULL, `json_firmado` mediumtext DEFAULT NULL, `respuesta_mh` text DEFAULT NULL, `fecha_transmision` datetime DEFAULT NULL, `id_usuario` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `codigo_generacion` (`codigo_generacion`), KEY `fk_dte_contingencia_usuario` (`id_usuario`), CONSTRAINT `fk_dte_contingencia_usuario` FOREIGN KEY (`id_usuario`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `existencia_bodega` -- DROP TABLE IF EXISTS `existencia_bodega`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `existencia_bodega` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_bodega` int(10) unsigned NOT NULL, `id_producto` int(10) unsigned NOT NULL, `id_ubicacion_bodega` int(10) unsigned DEFAULT NULL, `stock_actual` decimal(12,4) NOT NULL DEFAULT 0.0000, `stock_minimo` decimal(12,4) NOT NULL DEFAULT 0.0000, `actualizado_en` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_existencia` (`id_bodega`,`id_producto`,`id_ubicacion_bodega`), KEY `fk_existencia_producto` (`id_producto`), KEY `fk_existencia_ubicacion` (`id_ubicacion_bodega`), CONSTRAINT `fk_existencia_bodega` FOREIGN KEY (`id_bodega`) REFERENCES `bodega` (`id`) ON UPDATE CASCADE, CONSTRAINT `fk_existencia_producto` FOREIGN KEY (`id_producto`) REFERENCES `producto` (`id`) ON UPDATE CASCADE, CONSTRAINT `fk_existencia_ubicacion` FOREIGN KEY (`id_ubicacion_bodega`) REFERENCES `ubicacion_bodega` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `chk_exist_stock_no_neg` CHECK (`stock_actual` >= 0), CONSTRAINT `chk_exist_min_no_neg` CHECK (`stock_minimo` >= 0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `factura` -- DROP TABLE IF EXISTS `factura`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `factura` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `tipo_dte` char(2) NOT NULL, `id_punto_venta` int(10) unsigned DEFAULT NULL, `correlativo` int(10) unsigned NOT NULL, `numero_control` varchar(31) NOT NULL, `codigo_generacion` char(36) NOT NULL, `fecha_emision` datetime NOT NULL DEFAULT current_timestamp(), `id_cotizacion_venta` int(10) unsigned DEFAULT NULL COMMENT 'proyecto (solicitud)', `id_oferta` int(10) unsigned DEFAULT NULL, `id_documento_relacionado` int(10) unsigned DEFAULT NULL COMMENT 'nota de crédito/débito: el crédito fiscal al que modifica', `rel_externo_generacion` tinyint(1) DEFAULT NULL COMMENT 'nota sobre documento externo: 1 físico, 2 electrónico', `rel_externo_numero` varchar(36) DEFAULT NULL COMMENT 'código de generación (electrónico) o número (físico)', `rel_externo_fecha` date DEFAULT NULL, `rel_externo_monto` decimal(14,2) DEFAULT NULL COMMENT 'monto sin IVA del crédito fiscal externo', `id_cliente` int(10) unsigned DEFAULT NULL, `id_proveedor` int(10) unsigned DEFAULT NULL COMMENT 'comprobante de retención: proveedor receptor', `receptor_nombre` varchar(250) NOT NULL, `receptor_tipo_doc` varchar(10) DEFAULT NULL COMMENT 'NIT, DUI, PASAPORTE, OTRO', `receptor_num_doc` varchar(30) DEFAULT NULL, `receptor_nrc` varchar(20) DEFAULT NULL, `receptor_giro` varchar(250) DEFAULT NULL, `receptor_cod_actividad` varchar(6) DEFAULT NULL, `receptor_direccion` varchar(250) DEFAULT NULL, `receptor_cod_departamento` char(2) DEFAULT NULL, `receptor_cod_municipio` char(2) DEFAULT NULL, `receptor_cod_distrito` char(2) DEFAULT NULL COMMENT 'CAT-008', `receptor_correo` varchar(120) DEFAULT NULL, `receptor_telefono` varchar(30) DEFAULT NULL, `receptor_pais` varchar(80) DEFAULT NULL, `exp_recinto` varchar(4) DEFAULT NULL COMMENT 'CAT-027', `exp_regimen` varchar(13) DEFAULT NULL COMMENT 'CAT-028', `exp_tipo_regimen` varchar(5) DEFAULT NULL COMMENT 'CAT-033', `receptor_grande` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'gran contribuyente (retención 1 %)', `condicion_operacion` enum('contado','credito','otro') NOT NULL DEFAULT 'contado', `plazo_dias` smallint(5) unsigned DEFAULT NULL, `fecha_vencimiento` date DEFAULT NULL, `estado_pago` enum('no_aplica','pendiente','parcial','pagado') NOT NULL DEFAULT 'no_aplica', `monto_pagado` decimal(14,2) NOT NULL DEFAULT 0.00, `monto_acreditado` decimal(14,2) NOT NULL DEFAULT 0.00 COMMENT 'crédito fiscal: suma de sus notas de crédito vigentes', `modo_cobro` varchar(20) DEFAULT NULL COMMENT 'items, porcentaje, saldo, libre', `gravado` decimal(14,2) NOT NULL DEFAULT 0.00 COMMENT 'suma de líneas (01: con IVA)', `descuento` decimal(14,2) NOT NULL DEFAULT 0.00, `iva` decimal(14,2) NOT NULL DEFAULT 0.00 COMMENT '01: IVA incluido (informativo)', `retencion_iva` decimal(14,2) NOT NULL DEFAULT 0.00, `percepcion_iva` decimal(14,2) NOT NULL DEFAULT 0.00, `retencion_renta` decimal(14,2) NOT NULL DEFAULT 0.00, `total` decimal(14,2) NOT NULL DEFAULT 0.00 COMMENT 'monto total de la operación', `total_pagar` decimal(14,2) NOT NULL DEFAULT 0.00, `monto_base` decimal(14,2) NOT NULL DEFAULT 0.00 COMMENT 'lo que avanza la facturación del proyecto, sin IVA (0 en 04 y 14)', `observaciones` text DEFAULT NULL, `estado` enum('emitida','anulada') NOT NULL DEFAULT 'emitida', `estado_fiscal` enum('pendiente','procesado','rechazado') NOT NULL DEFAULT 'pendiente', `ambiente` char(2) NOT NULL DEFAULT '00', `sello_recepcion` varchar(100) DEFAULT NULL, `fecha_procesamiento` datetime DEFAULT NULL, `observaciones_mh` text DEFAULT NULL, `json_firmado` mediumtext DEFAULT NULL, `respuesta_mh` text DEFAULT NULL, `fecha_transmision` datetime DEFAULT NULL, `intentos_transmision` smallint(5) unsigned NOT NULL DEFAULT 0, `falla_transmision` enum('internet','hacienda') DEFAULT NULL COMMENT 'por qué no se pudo transmitir (sin respuesta)', `correo_enviado` datetime DEFAULT NULL, `correo_destinos` varchar(500) DEFAULT NULL, `id_contingencia` int(10) unsigned DEFAULT NULL, `motivo_anulacion` varchar(255) DEFAULT NULL, `fecha_anulacion` datetime DEFAULT NULL, `invalidacion_tipo` tinyint(3) unsigned DEFAULT NULL, `id_factura_reemplazo` int(10) unsigned DEFAULT NULL, `invalidacion_solicita` varchar(160) DEFAULT NULL, `invalidacion_codigo` char(36) DEFAULT NULL, `invalidacion_sello` varchar(100) DEFAULT NULL, `invalidacion_json` mediumtext DEFAULT NULL, `invalidacion_respuesta` text DEFAULT NULL, `invalidacion_fecha` datetime DEFAULT NULL, `id_usuario_emitio` int(10) unsigned DEFAULT NULL, `id_usuario_anulo` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_factura_control` (`numero_control`), UNIQUE KEY `uq_factura_codigo` (`codigo_generacion`), UNIQUE KEY `uq_factura_punto_tipo_corr` (`id_punto_venta`,`tipo_dte`,`correlativo`), KEY `fk_factura_solicitud` (`id_cotizacion_venta`), KEY `fk_factura_oferta` (`id_oferta`), KEY `fk_factura_cliente` (`id_cliente`), KEY `fk_factura_emitio` (`id_usuario_emitio`), KEY `fk_factura_anulo` (`id_usuario_anulo`), KEY `fk_factura_punto` (`id_punto_venta`), KEY `idx_factura_fecha` (`fecha_emision`), KEY `fk_factura_contingencia` (`id_contingencia`), KEY `fk_factura_reemplazo` (`id_factura_reemplazo`), KEY `fk_factura_relacionado` (`id_documento_relacionado`), KEY `fk_factura_proveedor` (`id_proveedor`), CONSTRAINT `fk_factura_anulo` FOREIGN KEY (`id_usuario_anulo`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_factura_cliente` FOREIGN KEY (`id_cliente`) REFERENCES `cliente` (`id`) ON UPDATE CASCADE, CONSTRAINT `fk_factura_contingencia` FOREIGN KEY (`id_contingencia`) REFERENCES `dte_contingencia` (`id`) ON DELETE SET NULL, CONSTRAINT `fk_factura_emitio` FOREIGN KEY (`id_usuario_emitio`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_factura_oferta` FOREIGN KEY (`id_oferta`) REFERENCES `cotizacion_venta_oferta` (`id`) ON UPDATE CASCADE, CONSTRAINT `fk_factura_proveedor` FOREIGN KEY (`id_proveedor`) REFERENCES `proveedor` (`id`) ON UPDATE CASCADE, CONSTRAINT `fk_factura_punto` FOREIGN KEY (`id_punto_venta`) REFERENCES `facturacion_punto_venta` (`id`) ON UPDATE CASCADE, CONSTRAINT `fk_factura_reemplazo` FOREIGN KEY (`id_factura_reemplazo`) REFERENCES `factura` (`id`) ON DELETE SET NULL, CONSTRAINT `fk_factura_relacionado` FOREIGN KEY (`id_documento_relacionado`) REFERENCES `factura` (`id`) ON UPDATE CASCADE, CONSTRAINT `fk_factura_solicitud` FOREIGN KEY (`id_cotizacion_venta`) REFERENCES `cotizacion_venta` (`id`) ON UPDATE CASCADE, CONSTRAINT `chk_factura_tipo` CHECK (`tipo_dte` in ('01','03','04','05','06','07','11','14')), CONSTRAINT `chk_factura_rel_externo` CHECK (`rel_externo_generacion` is null or `rel_externo_generacion` in (1,2)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `factura_historial` -- DROP TABLE IF EXISTS `factura_historial`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `factura_historial` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_factura` int(10) unsigned NOT NULL, `evento` varchar(40) NOT NULL, `detalle` varchar(500) DEFAULT NULL, `id_usuario` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `fk_factura_hist_factura` (`id_factura`), KEY `fk_factura_hist_usuario` (`id_usuario`), CONSTRAINT `fk_factura_hist_factura` FOREIGN KEY (`id_factura`) REFERENCES `factura` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_factura_hist_usuario` FOREIGN KEY (`id_usuario`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `factura_linea` -- DROP TABLE IF EXISTS `factura_linea`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `factura_linea` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_factura` int(10) unsigned NOT NULL, `num_item` smallint(5) unsigned NOT NULL, `tipo_item` tinyint(3) unsigned NOT NULL DEFAULT 1 COMMENT '1 bienes, 2 servicios, 3 ambos', `id_item_oferta` int(10) unsigned DEFAULT NULL, `id_producto` int(10) unsigned DEFAULT NULL, `descripcion` varchar(1000) NOT NULL, `cantidad` decimal(14,4) NOT NULL DEFAULT 1.0000, `unidad` varchar(30) DEFAULT NULL, `uni_medida` smallint(5) unsigned NOT NULL DEFAULT 59 COMMENT 'CAT-014', `precio_unitario` decimal(14,4) NOT NULL DEFAULT 0.0000 COMMENT '01: con IVA; demás: sin IVA', `descuento` decimal(14,2) NOT NULL DEFAULT 0.00, `monto` decimal(14,2) NOT NULL DEFAULT 0.00, PRIMARY KEY (`id`), KEY `fk_factura_linea_factura` (`id_factura`), KEY `fk_factura_linea_item` (`id_item_oferta`), KEY `fk_factura_linea_producto` (`id_producto`), CONSTRAINT `fk_factura_linea_factura` FOREIGN KEY (`id_factura`) REFERENCES `factura` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_factura_linea_item` FOREIGN KEY (`id_item_oferta`) REFERENCES `cotizacion_venta_oferta_item` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_factura_linea_producto` FOREIGN KEY (`id_producto`) REFERENCES `producto` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `factura_pago` -- DROP TABLE IF EXISTS `factura_pago`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `factura_pago` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_factura` int(10) unsigned NOT NULL, `fecha` date NOT NULL, `monto` decimal(14,2) NOT NULL, `forma_pago` enum('efectivo','transferencia','cheque','tarjeta','otro') NOT NULL DEFAULT 'transferencia', `referencia` varchar(100) DEFAULT NULL, `nota` varchar(255) DEFAULT NULL, `id_usuario` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `fk_factura_pago_factura` (`id_factura`), KEY `fk_factura_pago_usuario` (`id_usuario`), CONSTRAINT `fk_factura_pago_factura` FOREIGN KEY (`id_factura`) REFERENCES `factura` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_factura_pago_usuario` FOREIGN KEY (`id_usuario`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `chk_factura_pago_monto` CHECK (`monto` > 0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `factura_retencion` -- DROP TABLE IF EXISTS `factura_retencion`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `factura_retencion` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_factura` int(10) unsigned NOT NULL COMMENT 'el comprobante de retención (tipo 07)', `num_item` smallint(5) unsigned NOT NULL, `tipo_dte` char(2) NOT NULL COMMENT 'documento del proveedor: 01 factura, 03 crédito fiscal', `tipo_generacion` tinyint(1) NOT NULL COMMENT '1 físico, 2 electrónico (DTE)', `num_documento` varchar(36) NOT NULL COMMENT 'código de generación (electrónico) o número del documento físico', `fecha_emision` date NOT NULL, `monto_sujeto` decimal(14,2) NOT NULL COMMENT 'monto gravado sujeto a retención (sin IVA)', `codigo_retencion` char(2) NOT NULL DEFAULT '22' COMMENT 'CAT-006: 22 = retención IVA 1 %', `iva_retenido` decimal(14,2) NOT NULL, `descripcion` varchar(1000) NOT NULL, `id_compra_dte` int(10) unsigned DEFAULT NULL, `id_recepcion` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_factura_retencion_factura` (`id_factura`), KEY `idx_factura_retencion_compra` (`id_compra_dte`), KEY `idx_factura_retencion_recepcion` (`id_recepcion`), CONSTRAINT `fk_factura_retencion_compra` FOREIGN KEY (`id_compra_dte`) REFERENCES `compra_dte` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_factura_retencion_factura` FOREIGN KEY (`id_factura`) REFERENCES `factura` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_factura_retencion_recepcion` FOREIGN KEY (`id_recepcion`) REFERENCES `orden_compra_recepcion` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `chk_factura_retencion_tipo` CHECK (`tipo_dte` in ('01','03')), CONSTRAINT `chk_factura_retencion_generacion` CHECK (`tipo_generacion` in (1,2)), CONSTRAINT `chk_factura_retencion_montos` CHECK (`monto_sujeto` > 0 and `iva_retenido` > 0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `facturacion_correlativo` -- DROP TABLE IF EXISTS `facturacion_correlativo`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `facturacion_correlativo` ( `id_punto_venta` int(10) unsigned NOT NULL, `tipo_dte` char(2) NOT NULL, `siguiente` int(10) unsigned NOT NULL DEFAULT 1, PRIMARY KEY (`id_punto_venta`,`tipo_dte`), CONSTRAINT `fk_fact_corr_punto` FOREIGN KEY (`id_punto_venta`) REFERENCES `facturacion_punto_venta` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `facturacion_emisor` -- DROP TABLE IF EXISTS `facturacion_emisor`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `facturacion_emisor` ( `id` tinyint(3) unsigned NOT NULL, `nombre` varchar(250) NOT NULL DEFAULT '', `nombre_comercial` varchar(250) DEFAULT NULL, `nit` varchar(20) NOT NULL DEFAULT '', `nrc` varchar(20) NOT NULL DEFAULT '', `cod_actividad` varchar(10) DEFAULT NULL, `desc_actividad` varchar(250) DEFAULT NULL, `tipo_contribuyente` enum('pequeno','mediano','grande') NOT NULL DEFAULT 'mediano', `agente_retencion` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'designado agente de retención de IVA (los grandes contribuyentes lo son siempre)', `direccion` varchar(250) DEFAULT NULL, `cod_departamento` char(2) DEFAULT NULL, `cod_municipio` char(2) DEFAULT NULL, `cod_distrito` char(2) DEFAULT NULL COMMENT 'CAT-008', `telefono` varchar(30) DEFAULT NULL, `correo` varchar(120) DEFAULT NULL, `responsable_nombre` varchar(100) DEFAULT NULL, `responsable_tipo_doc` char(2) NOT NULL DEFAULT '13', `responsable_num_doc` varchar(25) DEFAULT NULL, `ambiente` char(2) NOT NULL DEFAULT '00' COMMENT '00 pruebas, 01 producción (DTE)', `version_dte` enum('1.2','2.0') NOT NULL DEFAULT '1.2' COMMENT 'normativa con la que se arman los DTE', `tipos_habilitados` varchar(40) NOT NULL DEFAULT '01,03,04,11,14', `dte_habilitado` tinyint(1) NOT NULL DEFAULT 0, `api_usuario` varchar(30) DEFAULT NULL, `api_password` varchar(255) DEFAULT NULL, `firmador_url` varchar(250) NOT NULL DEFAULT 'http://localhost:8113/firmardocumento/', `clave_privada` varchar(255) DEFAULT NULL COMMENT 'contraseña de la llave privada del certificado', `cert_pruebas` varchar(120) DEFAULT NULL, `cert_produccion` varchar(120) DEFAULT NULL, `transmitir_al_emitir` tinyint(1) NOT NULL DEFAULT 1, `contingencia_automatica` tinyint(1) NOT NULL DEFAULT 1, `libro_tipo_operacion` tinyint(3) unsigned NOT NULL DEFAULT 1 COMMENT 'F-07 ventas: tipo de operación (Renta)', `libro_tipo_ingreso` tinyint(3) unsigned NOT NULL DEFAULT 2 COMMENT 'F-07 ventas: tipo de ingreso (Renta)', `libro_compra_operacion` tinyint(3) unsigned NOT NULL DEFAULT 1 COMMENT 'F-07 compras: tipo de operación', `libro_compra_clasificacion` tinyint(3) unsigned NOT NULL DEFAULT 1 COMMENT 'F-07 compras: 1 costo, 2 gasto', `libro_compra_sector` tinyint(3) unsigned NOT NULL DEFAULT 4 COMMENT 'F-07 compras: sector', `libro_compra_tipo_gasto` tinyint(3) unsigned NOT NULL DEFAULT 5 COMMENT 'F-07 compras: tipo de costo/gasto', `token_valor` text DEFAULT NULL, `token_ambiente` char(2) DEFAULT NULL, `token_obtenido` datetime DEFAULT NULL, `url_api_pruebas` varchar(250) NOT NULL DEFAULT 'https://apitest.dtes.mh.gob.sv', `url_api_produccion` varchar(250) NOT NULL DEFAULT 'https://api.dtes.mh.gob.sv', `smtp_host` varchar(120) DEFAULT NULL, `smtp_puerto` smallint(5) unsigned NOT NULL DEFAULT 587, `smtp_seguridad` enum('tls','ssl','ninguna') NOT NULL DEFAULT 'tls', `smtp_usuario` varchar(120) DEFAULT NULL, `smtp_password` varchar(255) DEFAULT NULL, `correo_remitente` varchar(120) DEFAULT NULL, `nombre_remitente` varchar(120) DEFAULT NULL, `correo_copia` varchar(255) DEFAULT NULL, `enviar_correo_al_procesar` tinyint(1) NOT NULL DEFAULT 0, `usuario_modifico` int(10) unsigned DEFAULT NULL, `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), KEY `fk_fact_emisor_usuario` (`usuario_modifico`), CONSTRAINT `fk_fact_emisor_usuario` FOREIGN KEY (`usuario_modifico`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `facturacion_establecimiento` -- DROP TABLE IF EXISTS `facturacion_establecimiento`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `facturacion_establecimiento` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `codigo` char(4) NOT NULL COMMENT 'código asignado por Hacienda (codEstableMH)', `nombre` varchar(120) NOT NULL, `tipo` char(2) NOT NULL DEFAULT '02' COMMENT '01 sucursal, 02 casa matriz, 04 bodega, 07 predio, 20 otro', `direccion` varchar(250) DEFAULT NULL, `cod_departamento` char(2) DEFAULT NULL, `cod_municipio` char(2) DEFAULT NULL, `telefono` varchar(30) DEFAULT NULL, `activo` tinyint(1) NOT NULL DEFAULT 1, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_fact_estab_codigo` (`codigo`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `facturacion_plantilla_correo` -- DROP TABLE IF EXISTS `facturacion_plantilla_correo`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `facturacion_plantilla_correo` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `nombre` varchar(100) NOT NULL, `tipos` varchar(60) DEFAULT NULL COMMENT 'tipos DTE separados por coma; NULL = predeterminada', `asunto` varchar(250) NOT NULL, `encabezado` mediumtext DEFAULT NULL COMMENT 'HTML del encabezado (logo, nombre…)', `cuerpo` mediumtext NOT NULL COMMENT 'HTML limpio (PlantillaCorreo::limpiar) con variables {…}', `pie` mediumtext DEFAULT NULL COMMENT 'HTML del pie (datos de la empresa…)', `color_fondo` char(7) NOT NULL DEFAULT '#f2f4f7' COMMENT 'fondo alrededor del correo', `color_encabezado` char(7) NOT NULL DEFAULT '#ffffff', `color_pie` char(7) NOT NULL DEFAULT '#f8f9fb', `usuario_modifico` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_plantilla_correo_nombre` (`nombre`), KEY `fk_plantilla_correo_usuario` (`usuario_modifico`), CONSTRAINT `fk_plantilla_correo_usuario` FOREIGN KEY (`usuario_modifico`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `facturacion_punto_venta` -- DROP TABLE IF EXISTS `facturacion_punto_venta`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `facturacion_punto_venta` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_establecimiento` int(10) unsigned NOT NULL, `codigo` char(4) NOT NULL COMMENT 'codPuntoVentaMH', `nombre` varchar(120) NOT NULL, `activo` tinyint(1) NOT NULL DEFAULT 1, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_fact_punto` (`id_establecimiento`,`codigo`), CONSTRAINT `fk_fact_punto_estab` FOREIGN KEY (`id_establecimiento`) REFERENCES `facturacion_establecimiento` (`id`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `garantia_item` -- DROP TABLE IF EXISTS `garantia_item`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `garantia_item` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_recepcion_item` int(10) unsigned NOT NULL, `id_orden` int(10) unsigned NOT NULL, `id_producto` int(10) unsigned DEFAULT NULL, `descripcion` varchar(200) DEFAULT NULL, `numero_serie` varchar(120) DEFAULT NULL, `meses_garantia` int(10) unsigned DEFAULT NULL, `fecha_inicio` date NOT NULL, `fecha_fin` date DEFAULT NULL, `garante` enum('proveedor','fabricante','otro') NOT NULL DEFAULT 'proveedor', `garante_nombre` varchar(150) DEFAULT NULL, `cobertura` text DEFAULT NULL, `numero_documento` varchar(120) DEFAULT NULL, `observaciones` text DEFAULT NULL, `estado` enum('activa','reclamada','anulada') NOT NULL DEFAULT 'activa', `id_usuario` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), KEY `fk_gi_recepcion_item` (`id_recepcion_item`), KEY `fk_gi_orden` (`id_orden`), KEY `fk_gi_producto` (`id_producto`), KEY `fk_gi_usuario` (`id_usuario`), KEY `idx_gi_serie` (`numero_serie`), KEY `idx_gi_vencimiento` (`estado`,`fecha_fin`), CONSTRAINT `fk_gi_orden` FOREIGN KEY (`id_orden`) REFERENCES `orden_compra` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_gi_producto` FOREIGN KEY (`id_producto`) REFERENCES `producto` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_gi_recepcion_item` FOREIGN KEY (`id_recepcion_item`) REFERENCES `orden_compra_recepcion_item` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_gi_usuario` FOREIGN KEY (`id_usuario`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `kpi_meta` -- DROP TABLE IF EXISTS `kpi_meta`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `kpi_meta` ( `clave` varchar(40) NOT NULL COMMENT 'clave del KPI en Kpi::DEFINICIONES', `meta` decimal(5,2) NOT NULL COMMENT 'porcentaje objetivo (0-100)', `usuario_modifico` int(10) unsigned DEFAULT NULL, `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`clave`), KEY `fk_kpi_meta_usuario` (`usuario_modifico`), CONSTRAINT `fk_kpi_meta_usuario` FOREIGN KEY (`usuario_modifico`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `chk_kpi_meta_rango` CHECK (`meta` between 0 and 100) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `marca` -- DROP TABLE IF EXISTS `marca`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `marca` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `nombre` varchar(100) NOT NULL, `descripcion` text DEFAULT NULL, `sitio_web` varchar(255) DEFAULT NULL, `logo` varchar(255) DEFAULT NULL, `estado` tinyint(1) NOT NULL DEFAULT 1, `usuario_modifico` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), KEY `fk_marca_usuario_modifico` (`usuario_modifico`), CONSTRAINT `fk_marca_usuario_modifico` FOREIGN KEY (`usuario_modifico`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `medida` -- DROP TABLE IF EXISTS `medida`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `medida` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `nombre` varchar(50) NOT NULL, `cod_mh` smallint(5) unsigned NOT NULL DEFAULT 59 COMMENT 'CAT-014', `usuario_modifico` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), KEY `fk_medida_usuario_modifico` (`usuario_modifico`), CONSTRAINT `fk_medida_usuario_modifico` FOREIGN KEY (`usuario_modifico`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `mh_catalogo` -- DROP TABLE IF EXISTS `mh_catalogo`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `mh_catalogo` ( `catalogo` varchar(8) NOT NULL, `codigo` varchar(10) NOT NULL, `padre` varchar(10) NOT NULL DEFAULT '' COMMENT 'CAT-013: código del departamento', `valor` varchar(300) NOT NULL, PRIMARY KEY (`catalogo`,`padre`,`codigo`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `modulos` -- DROP TABLE IF EXISTS `modulos`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `modulos` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `nombre` varchar(100) NOT NULL, `icono` varchar(100) DEFAULT NULL, `orden` int(11) NOT NULL DEFAULT 0, `estado` tinyint(1) NOT NULL DEFAULT 1, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `movimiento_stock` -- DROP TABLE IF EXISTS `movimiento_stock`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `movimiento_stock` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_producto` int(10) unsigned NOT NULL, `id_bodega` int(10) unsigned NOT NULL, `tipo` enum('entrada','salida','traslado','ajuste','carga_inicial') NOT NULL, `cantidad` decimal(12,4) NOT NULL, `id_referencia` int(10) unsigned DEFAULT NULL, `tipo_referencia` varchar(50) DEFAULT NULL, `motivo` varchar(255) DEFAULT NULL, `fecha` datetime NOT NULL DEFAULT current_timestamp(), `id_usuario` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `fk_movimiento_producto` (`id_producto`), KEY `fk_movimiento_bodega` (`id_bodega`), KEY `fk_movimiento_stock_usuario` (`id_usuario`), CONSTRAINT `fk_movimiento_bodega` FOREIGN KEY (`id_bodega`) REFERENCES `bodega` (`id`) ON UPDATE CASCADE, CONSTRAINT `fk_movimiento_producto` FOREIGN KEY (`id_producto`) REFERENCES `producto` (`id`) ON UPDATE CASCADE, CONSTRAINT `fk_movimiento_stock_usuario` FOREIGN KEY (`id_usuario`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `chk_mov_cantidad_pos` CHECK (`cantidad` > 0), CONSTRAINT `chk_mov_ajuste_dir` CHECK (`tipo` <> 'ajuste' or `tipo_referencia` in ('aumento','disminucion')), CONSTRAINT `chk_mov_traslado_dir` CHECK (`tipo` <> 'traslado' or `tipo_referencia` in ('origen','destino')) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_unicode_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `trg_mov_stock_ai` AFTER INSERT ON `movimiento_stock` FOR EACH ROW BEGIN DECLARE v_delta DECIMAL(12,4); DECLARE v_exist_id INT UNSIGNED DEFAULT NULL; DECLARE v_actual DECIMAL(12,4) DEFAULT 0; DECLARE v_ubic INT UNSIGNED DEFAULT NULL; SET v_delta = fn_stock_delta(NEW.tipo, NEW.tipo_referencia, NEW.cantidad); SELECT id, stock_actual INTO v_exist_id, v_actual FROM existencia_bodega WHERE id_bodega = NEW.id_bodega AND id_producto = NEW.id_producto ORDER BY id LIMIT 1; IF v_exist_id IS NULL THEN IF v_delta < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Stock insuficiente: no hay existencia previa para este producto/bodega'; END IF; SELECT id INTO v_ubic FROM ubicacion_bodega WHERE id_bodega = NEW.id_bodega AND es_predeterminada = 1 ORDER BY id LIMIT 1; INSERT INTO existencia_bodega (id_bodega, id_producto, id_ubicacion_bodega, stock_actual, stock_minimo) VALUES (NEW.id_bodega, NEW.id_producto, v_ubic, v_delta, 0); ELSE IF v_actual + v_delta < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Stock insuficiente: el movimiento dejaría existencia_bodega en negativo'; END IF; UPDATE existencia_bodega SET stock_actual = stock_actual + v_delta WHERE id = v_exist_id; END IF; END */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER trg_mov_stock_bu BEFORE UPDATE ON movimiento_stock FOR EACH ROW BEGIN IF NEW.id_producto <> OLD.id_producto OR NEW.id_bodega <> OLD.id_bodega OR NEW.tipo <> OLD.tipo OR NEW.cantidad <> OLD.cantidad OR NOT (NEW.tipo_referencia <=> OLD.tipo_referencia) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'movimiento_stock es inmutable: use un asiento inverso para corregir'; END IF; END */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER trg_mov_stock_ad AFTER DELETE ON movimiento_stock FOR EACH ROW BEGIN UPDATE existencia_bodega SET stock_actual = stock_actual - fn_stock_delta(OLD.tipo, OLD.tipo_referencia, OLD.cantidad) WHERE id_bodega = OLD.id_bodega AND id_producto = OLD.id_producto ORDER BY id LIMIT 1; END */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; -- -- Table structure for table `notificacion` -- DROP TABLE IF EXISTS `notificacion`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `notificacion` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_usuario_destino` int(10) unsigned NOT NULL, `tipo` varchar(50) NOT NULL, `titulo` varchar(150) NOT NULL, `mensaje` varchar(255) DEFAULT NULL, `ruta` varchar(255) DEFAULT NULL, `leida` tinyint(1) NOT NULL DEFAULT 0, `fecha_leida` datetime DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `idx_notif_usuario` (`id_usuario_destino`,`leida`), CONSTRAINT `fk_notif_usuario` FOREIGN KEY (`id_usuario_destino`) REFERENCES `usuarios` (`id_usuario`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `orden_compra` -- DROP TABLE IF EXISTS `orden_compra`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `orden_compra` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `numero` varchar(30) NOT NULL, `origen` enum('cotizacion','directa') NOT NULL DEFAULT 'cotizacion', `id_cotizacion` int(10) unsigned DEFAULT NULL, `id_cotizacion_proveedor` int(10) unsigned DEFAULT NULL, `id_solicitud` int(10) unsigned DEFAULT NULL, `id_proveedor` int(10) unsigned NOT NULL, `id_sucursal` int(10) unsigned DEFAULT NULL, `id_bodega_destino` int(10) unsigned DEFAULT NULL, `id_departamento` int(10) unsigned DEFAULT NULL, `id_usuario_creo` int(10) unsigned NOT NULL, `id_usuario_aprobo` int(10) unsigned DEFAULT NULL, `prioridad` enum('baja','normal','alta') NOT NULL DEFAULT 'normal', `estado` enum('borrador','pendiente_aprobacion','aprobada','rechazada','con_observaciones','enviada','confirmada','recibida_parcial','recibida','cancelada','cerrada') NOT NULL DEFAULT 'borrador', `forma_pago` enum('contado','credito') NOT NULL DEFAULT 'credito', `dias_credito` int(10) unsigned NOT NULL DEFAULT 0, `condicion_pago` varchar(150) DEFAULT NULL, `tiempo_entrega` varchar(100) DEFAULT NULL, `observaciones` text DEFAULT NULL, `subtotal` decimal(12,2) NOT NULL DEFAULT 0.00, `descuento` decimal(12,2) NOT NULL DEFAULT 0.00, `impuesto` decimal(12,2) NOT NULL DEFAULT 0.00, `total` decimal(12,2) NOT NULL DEFAULT 0.00, `fecha_creacion` date DEFAULT NULL, `fecha_aprobacion` datetime DEFAULT NULL, `fecha_envio` datetime DEFAULT NULL, `fecha_recepcion` datetime DEFAULT NULL, `fecha_cierre` datetime DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_orden_compra_numero` (`numero`), UNIQUE KEY `uq_orden_compra_cotizacion` (`id_cotizacion`), KEY `fk_orden_compra_solicitud` (`id_solicitud`), KEY `fk_orden_compra_proveedor` (`id_proveedor`), KEY `fk_orden_compra_sucursal` (`id_sucursal`), KEY `fk_orden_compra_bodega` (`id_bodega_destino`), KEY `fk_orden_compra_departamento` (`id_departamento`), KEY `fk_orden_compra_usuario_creo` (`id_usuario_creo`), KEY `idx_orden_compra_estado` (`estado`), KEY `fk_orden_compra_cotizacion_proveedor` (`id_cotizacion_proveedor`), KEY `fk_orden_compra_usuario_aprobo` (`id_usuario_aprobo`), CONSTRAINT `fk_orden_compra_bodega` FOREIGN KEY (`id_bodega_destino`) REFERENCES `bodega` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_orden_compra_cotizacion` FOREIGN KEY (`id_cotizacion`) REFERENCES `cotizacion_compra` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_orden_compra_cotizacion_proveedor` FOREIGN KEY (`id_cotizacion_proveedor`) REFERENCES `cotizacion_compra_proveedor` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_orden_compra_departamento` FOREIGN KEY (`id_departamento`) REFERENCES `departamento` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_orden_compra_proveedor` FOREIGN KEY (`id_proveedor`) REFERENCES `proveedor` (`id`) ON UPDATE CASCADE, CONSTRAINT `fk_orden_compra_solicitud` FOREIGN KEY (`id_solicitud`) REFERENCES `solicitud_compra` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_orden_compra_sucursal` FOREIGN KEY (`id_sucursal`) REFERENCES `sucursal` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_orden_compra_usuario_aprobo` FOREIGN KEY (`id_usuario_aprobo`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_orden_compra_usuario_creo` FOREIGN KEY (`id_usuario_creo`) REFERENCES `usuarios` (`id_usuario`) ON UPDATE CASCADE, CONSTRAINT `chk_orden_compra_origen_cotizacion` CHECK (`origen` <> 'cotizacion' or `id_cotizacion` is not null) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `orden_compra_devolucion` -- DROP TABLE IF EXISTS `orden_compra_devolucion`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `orden_compra_devolucion` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `numero` varchar(30) NOT NULL, `id_orden` int(10) unsigned NOT NULL, `id_recepcion` int(10) unsigned DEFAULT NULL, `fecha` datetime NOT NULL DEFAULT current_timestamp(), `id_bodega` int(10) unsigned NOT NULL, `id_usuario` int(10) unsigned DEFAULT NULL, `motivo` enum('defectuoso','equivocado','exceso','danado_transito','otro') NOT NULL DEFAULT 'defectuoso', `observaciones` text DEFAULT NULL, `numero_nota_credito` varchar(80) DEFAULT NULL, `monto` decimal(12,2) NOT NULL DEFAULT 0.00, `efecto_pago` enum('descuenta_pago','saldo_a_favor','no_aplica') NOT NULL DEFAULT 'no_aplica', `saldo_a_favor` decimal(12,2) NOT NULL DEFAULT 0.00, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_orden_compra_devolucion_numero` (`numero`), KEY `fk_ocd_orden` (`id_orden`), KEY `fk_ocd_recepcion` (`id_recepcion`), KEY `fk_ocd_bodega` (`id_bodega`), KEY `fk_ocd_usuario` (`id_usuario`), CONSTRAINT `fk_ocd_bodega` FOREIGN KEY (`id_bodega`) REFERENCES `bodega` (`id`) ON UPDATE CASCADE, CONSTRAINT `fk_ocd_orden` FOREIGN KEY (`id_orden`) REFERENCES `orden_compra` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_ocd_recepcion` FOREIGN KEY (`id_recepcion`) REFERENCES `orden_compra_recepcion` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_ocd_usuario` FOREIGN KEY (`id_usuario`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `orden_compra_devolucion_item` -- DROP TABLE IF EXISTS `orden_compra_devolucion_item`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `orden_compra_devolucion_item` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_devolucion` int(10) unsigned NOT NULL, `id_orden_item` int(10) unsigned NOT NULL, `cantidad` decimal(12,4) NOT NULL, PRIMARY KEY (`id`), KEY `fk_ocdi_devolucion` (`id_devolucion`), KEY `fk_ocdi_orden_item` (`id_orden_item`), CONSTRAINT `fk_ocdi_devolucion` FOREIGN KEY (`id_devolucion`) REFERENCES `orden_compra_devolucion` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_ocdi_orden_item` FOREIGN KEY (`id_orden_item`) REFERENCES `orden_compra_item` (`id`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `orden_compra_historial` -- DROP TABLE IF EXISTS `orden_compra_historial`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `orden_compra_historial` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_orden` int(10) unsigned NOT NULL, `estado_anterior` varchar(30) DEFAULT NULL, `estado_nuevo` varchar(30) NOT NULL, `observaciones` text DEFAULT NULL, `id_usuario` int(10) unsigned NOT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `fk_orden_compra_historial_orden` (`id_orden`), KEY `fk_orden_compra_historial_usuario` (`id_usuario`), CONSTRAINT `fk_orden_compra_historial_orden` FOREIGN KEY (`id_orden`) REFERENCES `orden_compra` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_orden_compra_historial_usuario` FOREIGN KEY (`id_usuario`) REFERENCES `usuarios` (`id_usuario`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `orden_compra_item` -- DROP TABLE IF EXISTS `orden_compra_item`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `orden_compra_item` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_orden` int(10) unsigned NOT NULL, `id_solicitud_item` int(10) unsigned DEFAULT NULL, `id_producto` int(10) unsigned DEFAULT NULL, `descripcion` varchar(255) NOT NULL, `cantidad` decimal(12,4) NOT NULL, `unidad_medida` varchar(50) DEFAULT NULL, `precio_unitario` decimal(12,4) NOT NULL DEFAULT 0.0000, `descuento_pct` decimal(5,2) NOT NULL DEFAULT 0.00, `impuesto_pct` decimal(5,2) NOT NULL DEFAULT 0.00, `subtotal` decimal(12,2) NOT NULL DEFAULT 0.00, `cantidad_recibida` decimal(12,4) NOT NULL DEFAULT 0.0000, `cantidad_devuelta` decimal(12,4) NOT NULL DEFAULT 0.0000, PRIMARY KEY (`id`), KEY `fk_orden_compra_item_orden` (`id_orden`), KEY `fk_orden_compra_item_solicitud_item` (`id_solicitud_item`), KEY `fk_orden_compra_item_producto` (`id_producto`), CONSTRAINT `fk_orden_compra_item_orden` FOREIGN KEY (`id_orden`) REFERENCES `orden_compra` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_orden_compra_item_producto` FOREIGN KEY (`id_producto`) REFERENCES `producto` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_orden_compra_item_solicitud_item` FOREIGN KEY (`id_solicitud_item`) REFERENCES `solicitud_compra_item` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `orden_compra_recepcion` -- DROP TABLE IF EXISTS `orden_compra_recepcion`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `orden_compra_recepcion` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_orden` int(10) unsigned NOT NULL, `fecha` datetime NOT NULL DEFAULT current_timestamp(), `id_bodega` int(10) unsigned NOT NULL, `id_usuario` int(10) unsigned DEFAULT NULL, `numero_factura` varchar(80) DEFAULT NULL, `observaciones` text DEFAULT NULL, `monto` decimal(12,2) NOT NULL DEFAULT 0.00, `iva_retenido` decimal(14,2) NOT NULL DEFAULT 0.00 COMMENT 'IVA retenido con comprobantes vigentes; se paga monto − iva_retenido', `estado_pago` enum('no_aplica','pendiente','pagada') NOT NULL DEFAULT 'no_aplica', `fecha_pago_estimada` date DEFAULT NULL, `fecha_pago` datetime DEFAULT NULL, `referencia_pago` varchar(80) DEFAULT NULL, `id_usuario_pago` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `fk_orden_compra_recepcion_orden` (`id_orden`), KEY `fk_orden_compra_recepcion_bodega` (`id_bodega`), KEY `fk_orden_compra_recepcion_usuario` (`id_usuario`), KEY `fk_orden_compra_recepcion_usuario_pago` (`id_usuario_pago`), KEY `idx_orden_compra_recepcion_pago` (`estado_pago`,`fecha_pago_estimada`), CONSTRAINT `fk_orden_compra_recepcion_bodega` FOREIGN KEY (`id_bodega`) REFERENCES `bodega` (`id`) ON UPDATE CASCADE, CONSTRAINT `fk_orden_compra_recepcion_orden` FOREIGN KEY (`id_orden`) REFERENCES `orden_compra` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_orden_compra_recepcion_usuario` FOREIGN KEY (`id_usuario`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_orden_compra_recepcion_usuario_pago` FOREIGN KEY (`id_usuario_pago`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `orden_compra_recepcion_item` -- DROP TABLE IF EXISTS `orden_compra_recepcion_item`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `orden_compra_recepcion_item` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_recepcion` int(10) unsigned NOT NULL, `id_orden_item` int(10) unsigned NOT NULL, `cantidad` decimal(12,4) NOT NULL, PRIMARY KEY (`id`), KEY `fk_orden_compra_recepcion_item_recepcion` (`id_recepcion`), KEY `fk_orden_compra_recepcion_item_orden_item` (`id_orden_item`), CONSTRAINT `fk_orden_compra_recepcion_item_orden_item` FOREIGN KEY (`id_orden_item`) REFERENCES `orden_compra_item` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_orden_compra_recepcion_item_recepcion` FOREIGN KEY (`id_recepcion`) REFERENCES `orden_compra_recepcion` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `orden_salida` -- DROP TABLE IF EXISTS `orden_salida`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `orden_salida` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_bodega` int(10) unsigned NOT NULL, `id_cotizacion_venta` int(10) unsigned DEFAULT NULL, `id_oferta` int(10) unsigned DEFAULT NULL, `numero` varchar(50) NOT NULL, `fecha` datetime NOT NULL DEFAULT current_timestamp(), `fecha_refer` date DEFAULT NULL, `referencia_documento` varchar(100) DEFAULT NULL, `observaciones` text DEFAULT NULL, `estado` enum('borrador','aprobada','despachada','cancelada') NOT NULL DEFAULT 'borrador', `fecha_despacho` datetime DEFAULT NULL, `id_usuario_creo` int(10) unsigned DEFAULT NULL, `id_usuario_aprobo` int(10) unsigned DEFAULT NULL, `id_usuario_despacho` int(10) unsigned DEFAULT NULL, `usuario_modifico` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `numero` (`numero`), KEY `fk_orden_salida_bodega` (`id_bodega`), KEY `fk_orden_salida_usuario_creo` (`id_usuario_creo`), KEY `fk_orden_salida_usuario_aprobo` (`id_usuario_aprobo`), KEY `fk_orden_salida_usuario_despacho` (`id_usuario_despacho`), KEY `fk_orden_salida_usuario_modifico` (`usuario_modifico`), KEY `fk_orden_salida_solicitud` (`id_cotizacion_venta`), KEY `fk_orden_salida_oferta` (`id_oferta`), CONSTRAINT `fk_orden_salida_bodega` FOREIGN KEY (`id_bodega`) REFERENCES `bodega` (`id`) ON UPDATE CASCADE, CONSTRAINT `fk_orden_salida_oferta` FOREIGN KEY (`id_oferta`) REFERENCES `cotizacion_venta_oferta` (`id`) ON UPDATE CASCADE, CONSTRAINT `fk_orden_salida_solicitud` FOREIGN KEY (`id_cotizacion_venta`) REFERENCES `cotizacion_venta` (`id`) ON UPDATE CASCADE, CONSTRAINT `fk_orden_salida_usuario_aprobo` FOREIGN KEY (`id_usuario_aprobo`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_orden_salida_usuario_creo` FOREIGN KEY (`id_usuario_creo`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_orden_salida_usuario_despacho` FOREIGN KEY (`id_usuario_despacho`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_orden_salida_usuario_modifico` FOREIGN KEY (`usuario_modifico`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `orden_salida_detalle` -- DROP TABLE IF EXISTS `orden_salida_detalle`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `orden_salida_detalle` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_orden_salida` int(10) unsigned NOT NULL, `id_paquete_producto` int(10) unsigned DEFAULT NULL, `id_producto` int(10) unsigned NOT NULL, `cantidad_solicitada` decimal(12,4) NOT NULL DEFAULT 0.0000, `cantidad_despachada` decimal(12,4) NOT NULL DEFAULT 0.0000, `observaciones` text DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), KEY `fk_detalle_salida_orden` (`id_orden_salida`), KEY `fk_detalle_salida_producto` (`id_producto`), KEY `fk_detalle_salida_concepto` (`id_paquete_producto`), CONSTRAINT `fk_detalle_salida_concepto` FOREIGN KEY (`id_paquete_producto`) REFERENCES `paquete_producto` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_detalle_salida_orden` FOREIGN KEY (`id_orden_salida`) REFERENCES `orden_salida` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_detalle_salida_producto` FOREIGN KEY (`id_producto`) REFERENCES `producto` (`id`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `orden_traslado` -- DROP TABLE IF EXISTS `orden_traslado`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `orden_traslado` ( `id_bodega_origen` int(10) unsigned NOT NULL, `id_bodega_destino` int(10) unsigned NOT NULL, `enviado` tinyint(1) NOT NULL DEFAULT 0, `estado` enum('pendiente','despachada','recibida','cancelada') NOT NULL DEFAULT 'pendiente', `fecha_solicitud` date DEFAULT NULL, `fecha_despacho` date DEFAULT NULL, `fecha_recepcion` date DEFAULT NULL, `id_usuario_creo` int(10) unsigned DEFAULT NULL, `id_usuario_aprobo` int(10) unsigned DEFAULT NULL, `id_pasaje_despacho` int(10) unsigned DEFAULT NULL, `despacho_a_cliente` tinyint(1) NOT NULL DEFAULT 0, `observaciones` text DEFAULT NULL, `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), KEY `fk_traslado_bodega_origen` (`id_bodega_origen`), KEY `fk_traslado_bodega_destino` (`id_bodega_destino`), KEY `fk_orden_traslado_usuario_creo` (`id_usuario_creo`), KEY `fk_orden_traslado_usuario_aprobo` (`id_usuario_aprobo`), CONSTRAINT `fk_orden_traslado_usuario_aprobo` FOREIGN KEY (`id_usuario_aprobo`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_orden_traslado_usuario_creo` FOREIGN KEY (`id_usuario_creo`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_traslado_bodega_destino` FOREIGN KEY (`id_bodega_destino`) REFERENCES `bodega` (`id`) ON UPDATE CASCADE, CONSTRAINT `fk_traslado_bodega_origen` FOREIGN KEY (`id_bodega_origen`) REFERENCES `bodega` (`id`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `orden_traslado_detalle` -- DROP TABLE IF EXISTS `orden_traslado_detalle`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `orden_traslado_detalle` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_orden_traslado` int(10) unsigned NOT NULL, `id_producto` int(10) unsigned NOT NULL, `cantidad_solicitada` decimal(12,4) NOT NULL DEFAULT 0.0000, `cantidad_despachada` decimal(12,4) NOT NULL DEFAULT 0.0000, `cantidad_recibida` decimal(12,4) NOT NULL DEFAULT 0.0000, `observaciones` text DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), KEY `fk_detalle_traslado_orden` (`id_orden_traslado`), KEY `fk_detalle_traslado_producto` (`id_producto`), CONSTRAINT `fk_detalle_traslado_orden` FOREIGN KEY (`id_orden_traslado`) REFERENCES `orden_traslado` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_detalle_traslado_producto` FOREIGN KEY (`id_producto`) REFERENCES `producto` (`id`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `pacto_mano_obra` -- DROP TABLE IF EXISTS `pacto_mano_obra`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `pacto_mano_obra` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `numero` varchar(20) NOT NULL COMMENT 'PMO-AAAA-NNNN', `id_proyecto` int(10) unsigned NOT NULL, `id_personal` int(10) unsigned NOT NULL, `fecha` date NOT NULL, `condiciones` text DEFAULT NULL COMMENT 'lo acordado: forma de trabajo, plazos, materiales a cargo…', `estado` enum('vigente','anulado') NOT NULL DEFAULT 'vigente', `motivo_anulacion` varchar(255) DEFAULT NULL, `id_usuario_creo` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_pacto_numero` (`numero`), KEY `fk_pacto_proyecto` (`id_proyecto`), KEY `fk_pacto_personal` (`id_personal`), KEY `fk_pacto_usuario` (`id_usuario_creo`), CONSTRAINT `fk_pacto_personal` FOREIGN KEY (`id_personal`) REFERENCES `personal_obra` (`id`) ON UPDATE CASCADE, CONSTRAINT `fk_pacto_proyecto` FOREIGN KEY (`id_proyecto`) REFERENCES `proyecto_supervision` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_pacto_usuario` FOREIGN KEY (`id_usuario_creo`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `pago_mano_obra` -- DROP TABLE IF EXISTS `pago_mano_obra`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `pago_mano_obra` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `numero` varchar(20) NOT NULL COMMENT 'PMO-P-AAAA-NNNN', `id_pacto` int(10) unsigned NOT NULL, `monto` decimal(12,2) NOT NULL, `estado` enum('pendiente','pagado') NOT NULL DEFAULT 'pendiente', `observaciones` varchar(255) DEFAULT NULL, `id_usuario_solicito` int(10) unsigned DEFAULT NULL, `fecha_solicitud` datetime NOT NULL DEFAULT current_timestamp(), `fecha_pago` date DEFAULT NULL, `referencia` varchar(100) DEFAULT NULL, `id_usuario_pago` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uq_pago_mano_obra_numero` (`numero`), KEY `fk_pago_mo_pacto` (`id_pacto`), KEY `idx_pago_mo_estado` (`estado`), KEY `fk_pago_mo_solicito` (`id_usuario_solicito`), KEY `fk_pago_mo_pago` (`id_usuario_pago`), CONSTRAINT `fk_pago_mo_pacto` FOREIGN KEY (`id_pacto`) REFERENCES `pacto_mano_obra` (`id`) ON UPDATE CASCADE, CONSTRAINT `fk_pago_mo_pago` FOREIGN KEY (`id_usuario_pago`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_pago_mo_solicito` FOREIGN KEY (`id_usuario_solicito`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `chk_pago_mo_monto` CHECK (`monto` > 0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `paquete` -- DROP TABLE IF EXISTS `paquete`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `paquete` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `nombre` varchar(150) NOT NULL, `descripcion` text DEFAULT NULL, `tipo` enum('automatizado','manual') NOT NULL DEFAULT 'automatizado', `activo` tinyint(1) NOT NULL DEFAULT 1, `usuario_modifico` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), KEY `fk_pq_usuario` (`usuario_modifico`), CONSTRAINT `fk_pq_usuario` FOREIGN KEY (`usuario_modifico`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `paquete_formula` -- DROP TABLE IF EXISTS `paquete_formula`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `paquete_formula` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_paquete` int(10) unsigned NOT NULL, `nombre` varchar(100) NOT NULL, `formula` text NOT NULL, `comentario` varchar(500) DEFAULT NULL, `orden` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), KEY `idx_pqf_paquete` (`id_paquete`), CONSTRAINT `fk_pqf_paquete` FOREIGN KEY (`id_paquete`) REFERENCES `paquete` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `paquete_material` -- DROP TABLE IF EXISTS `paquete_material`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `paquete_material` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_paquete` int(10) unsigned NOT NULL, `id_paquete_producto` int(10) unsigned NOT NULL, `clave` varchar(80) NOT NULL, `formula_cantidad` text NOT NULL, `precio_override` decimal(10,2) DEFAULT NULL, `precio_manual` tinyint(1) NOT NULL DEFAULT 0, `cantidad_editable` tinyint(1) NOT NULL DEFAULT 0, `comentario` varchar(500) DEFAULT NULL, `orden` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), KEY `idx_pqm_paquete` (`id_paquete`), KEY `fk_pqm_producto` (`id_paquete_producto`), CONSTRAINT `fk_pqm_paquete` FOREIGN KEY (`id_paquete`) REFERENCES `paquete` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_pqm_producto` FOREIGN KEY (`id_paquete_producto`) REFERENCES `paquete_producto` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `paquete_producto` -- DROP TABLE IF EXISTS `paquete_producto`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `paquete_producto` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `nombre` varchar(200) NOT NULL, `precio` decimal(10,2) NOT NULL DEFAULT 0.00, `unidad` varchar(30) DEFAULT NULL, `es_mano_obra` tinyint(1) NOT NULL DEFAULT 0, `es_transporte` tinyint(1) NOT NULL DEFAULT 0, `id_catalogo` int(10) unsigned DEFAULT NULL, `id_producto` int(10) unsigned DEFAULT NULL, `factor_inventario` decimal(12,4) NOT NULL DEFAULT 1.0000, `activo` tinyint(1) NOT NULL DEFAULT 1, `usuario_modifico` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), KEY `fk_pqp_producto` (`id_producto`), KEY `fk_pqp_usuario` (`usuario_modifico`), KEY `fk_pqp_catalogo` (`id_catalogo`), CONSTRAINT `fk_pqp_catalogo` FOREIGN KEY (`id_catalogo`) REFERENCES `catalogo_producto` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_pqp_producto` FOREIGN KEY (`id_producto`) REFERENCES `producto` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_pqp_usuario` FOREIGN KEY (`usuario_modifico`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `chk_pqp_factor` CHECK (`factor_inventario` > 0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `paquete_variable` -- DROP TABLE IF EXISTS `paquete_variable`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `paquete_variable` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_paquete` int(10) unsigned NOT NULL, `nombre` varchar(80) NOT NULL, `etiqueta` varchar(120) NOT NULL, `tipo` enum('numero','texto','selector','lista') NOT NULL DEFAULT 'numero', `unidad` varchar(30) DEFAULT NULL, `opciones` text DEFAULT NULL, `valor_default` varchar(100) DEFAULT NULL, `requerido` tinyint(1) NOT NULL DEFAULT 1, `en_titulo` tinyint(1) NOT NULL DEFAULT 0, `comentario` varchar(500) DEFAULT NULL, `orden` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), KEY `idx_pqv_paquete` (`id_paquete`), CONSTRAINT `fk_pqv_paquete` FOREIGN KEY (`id_paquete`) REFERENCES `paquete` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `permisos` -- DROP TABLE IF EXISTS `permisos`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `permisos` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_usuario` int(10) unsigned NOT NULL, `id_pestana` int(10) unsigned NOT NULL, `puede_leer` tinyint(1) NOT NULL DEFAULT 0, `puede_escribir` tinyint(1) NOT NULL DEFAULT 0, `puede_eliminar` tinyint(1) NOT NULL DEFAULT 0, `puede_ver_historial` tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), UNIQUE KEY `uq_usuario_pestana` (`id_usuario`,`id_pestana`), KEY `id_pestana` (`id_pestana`), CONSTRAINT `permisos_ibfk_1` FOREIGN KEY (`id_usuario`) REFERENCES `usuarios` (`id_usuario`), CONSTRAINT `permisos_ibfk_2` FOREIGN KEY (`id_pestana`) REFERENCES `pestanas` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `permisos_roles` -- DROP TABLE IF EXISTS `permisos_roles`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `permisos_roles` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_rol` int(10) unsigned NOT NULL, `id_pestana` int(10) unsigned NOT NULL, `puede_leer` tinyint(1) NOT NULL DEFAULT 0, `puede_escribir` tinyint(1) NOT NULL DEFAULT 0, `puede_eliminar` tinyint(1) NOT NULL DEFAULT 0, `puede_ver_historial` tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), UNIQUE KEY `uq_rol_pestana` (`id_rol`,`id_pestana`), KEY `id_pestana` (`id_pestana`), CONSTRAINT `permisos_roles_ibfk_1` FOREIGN KEY (`id_rol`) REFERENCES `roles` (`id`), CONSTRAINT `permisos_roles_ibfk_2` FOREIGN KEY (`id_pestana`) REFERENCES `pestanas` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `personal_obra` -- DROP TABLE IF EXISTS `personal_obra`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `personal_obra` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `nombre` varchar(150) NOT NULL, `tipo` enum('interno','externo') NOT NULL COMMENT 'interno = empleado; externo = contratista', `documento` varchar(30) DEFAULT NULL COMMENT 'DUI o NIT', `telefono` varchar(30) DEFAULT NULL, `oficio` varchar(80) DEFAULT NULL, `notas` varchar(255) DEFAULT NULL, `activo` tinyint(1) NOT NULL DEFAULT 1, `usuario_modifico` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), KEY `idx_personal_obra_nombre` (`nombre`), KEY `fk_personal_obra_usuario` (`usuario_modifico`), CONSTRAINT `fk_personal_obra_usuario` FOREIGN KEY (`usuario_modifico`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Personal de mano de obra (interno o externo) para los pactos'; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `pestanas` -- DROP TABLE IF EXISTS `pestanas`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `pestanas` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_modulo` int(10) unsigned NOT NULL, `nombre` varchar(100) NOT NULL, `ruta` varchar(150) NOT NULL, `icono` varchar(100) DEFAULT NULL, `orden` int(11) NOT NULL DEFAULT 0, `estado` tinyint(1) NOT NULL DEFAULT 1, PRIMARY KEY (`id`), UNIQUE KEY `ruta` (`ruta`), KEY `id_modulo` (`id_modulo`), CONSTRAINT `pestanas_ibfk_1` FOREIGN KEY (`id_modulo`) REFERENCES `modulos` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `plantilla_cotizacion` -- DROP TABLE IF EXISTS `plantilla_cotizacion`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `plantilla_cotizacion` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `nombre` varchar(120) NOT NULL, `config` text NOT NULL, `estilo_tabla` enum('clasica','moderna') NOT NULL DEFAULT 'clasica', `predeterminada` tinyint(1) NOT NULL DEFAULT 0, `activo` tinyint(1) NOT NULL DEFAULT 1, `usuario_modifico` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_plantilla_cotizacion_nombre` (`nombre`), KEY `fk_plc_usuario` (`usuario_modifico`), CONSTRAINT `fk_plc_usuario` FOREIGN KEY (`usuario_modifico`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `producto` -- DROP TABLE IF EXISTS `producto`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `producto` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `codigo` varchar(50) NOT NULL, `id_catalogo` int(10) unsigned NOT NULL, `id_marca` int(10) unsigned DEFAULT NULL, `id_proveedor` int(10) unsigned DEFAULT NULL, `id_referencia` varchar(100) DEFAULT NULL, `descripcion` text DEFAULT NULL, `caracteristicas` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`caracteristicas`)), `codigo_barras` varchar(100) DEFAULT NULL, `precio` decimal(10,2) NOT NULL DEFAULT 0.00, `precio_costo` decimal(10,2) NOT NULL DEFAULT 0.00, `estado` tinyint(1) NOT NULL DEFAULT 1, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `codigo` (`codigo`), KEY `fk_producto_catalogo` (`id_catalogo`), KEY `fk_producto_marca` (`id_marca`), KEY `fk_producto_proveedor` (`id_proveedor`), CONSTRAINT `fk_producto_catalogo` FOREIGN KEY (`id_catalogo`) REFERENCES `catalogo_producto` (`id`) ON UPDATE CASCADE, CONSTRAINT `fk_producto_marca` FOREIGN KEY (`id_marca`) REFERENCES `marca` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_producto_proveedor` FOREIGN KEY (`id_proveedor`) REFERENCES `proveedor` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `chk_producto_precios_no_neg` CHECK (`precio` >= 0 and `precio_costo` >= 0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `proveedor` -- DROP TABLE IF EXISTS `proveedor`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `proveedor` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `nombre` varchar(150) NOT NULL, `nit` varchar(30) DEFAULT NULL, `nrc` varchar(20) DEFAULT NULL, `tipo_contribuyente` enum('pequeno','mediano','grande') NOT NULL DEFAULT 'mediano', `cod_actividad` varchar(6) DEFAULT NULL COMMENT 'CAT-019', `contacto` varchar(150) DEFAULT NULL, `direccion` text DEFAULT NULL, `cod_departamento` char(2) DEFAULT NULL COMMENT 'CAT-012', `cod_municipio` char(2) DEFAULT NULL COMMENT 'CAT-013', `cod_distrito` char(2) DEFAULT NULL COMMENT 'CAT-008', `telefono` varchar(30) DEFAULT NULL, `correo` varchar(150) DEFAULT NULL, `estado` tinyint(1) NOT NULL DEFAULT 1, `usuario_modifico` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), KEY `fk_proveedor_usuario_modifico` (`usuario_modifico`), CONSTRAINT `fk_proveedor_usuario_modifico` FOREIGN KEY (`usuario_modifico`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `proveedor_marca` -- DROP TABLE IF EXISTS `proveedor_marca`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `proveedor_marca` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_proveedor` int(10) unsigned NOT NULL, `id_marca` int(10) unsigned NOT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_proveedor_marca` (`id_proveedor`,`id_marca`), KEY `fk_provmarca_marca` (`id_marca`), CONSTRAINT `fk_provmarca_marca` FOREIGN KEY (`id_marca`) REFERENCES `marca` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_provmarca_proveedor` FOREIGN KEY (`id_proveedor`) REFERENCES `proveedor` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `proveedor_producto_equivalencia` -- DROP TABLE IF EXISTS `proveedor_producto_equivalencia`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `proveedor_producto_equivalencia` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_proveedor` int(10) unsigned NOT NULL, `clave` varchar(255) NOT NULL COMMENT 'c:CODIGO o d:descripción normalizada', `id_producto` int(10) unsigned DEFAULT NULL COMMENT 'null = la línea se omite (servicio, flete)', `factor` decimal(12,4) NOT NULL DEFAULT 1.0000, `usos` int(10) unsigned NOT NULL DEFAULT 1, `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_equivalencia` (`id_proveedor`,`clave`), KEY `fk_equiv_producto` (`id_producto`), CONSTRAINT `fk_equiv_producto` FOREIGN KEY (`id_producto`) REFERENCES `producto` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_equiv_proveedor` FOREIGN KEY (`id_proveedor`) REFERENCES `proveedor` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `chk_equiv_factor` CHECK (`factor` > 0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `proyecto_ampliacion` -- DROP TABLE IF EXISTS `proyecto_ampliacion`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `proyecto_ampliacion` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_proyecto` int(10) unsigned NOT NULL, `monto_solicitado` decimal(12,2) NOT NULL COMMENT 'nuevo tope que se pide', `motivo` varchar(500) NOT NULL, `estado` enum('pendiente','aprobada','rechazada') NOT NULL DEFAULT 'pendiente', `monto_anterior` decimal(12,2) DEFAULT NULL, `monto_nuevo` decimal(12,2) DEFAULT NULL COMMENT 'el que quedó (puede diferir del solicitado)', `comentario` varchar(500) DEFAULT NULL, `id_usuario_solicito` int(10) unsigned DEFAULT NULL, `fecha_solicitud` datetime NOT NULL DEFAULT current_timestamp(), `id_usuario_resolvio` int(10) unsigned DEFAULT NULL, `fecha_resolucion` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `fk_ampliacion_proyecto` (`id_proyecto`), KEY `fk_ampliacion_solicito` (`id_usuario_solicito`), KEY `fk_ampliacion_resolvio` (`id_usuario_resolvio`), CONSTRAINT `fk_ampliacion_proyecto` FOREIGN KEY (`id_proyecto`) REFERENCES `proyecto_supervision` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_ampliacion_resolvio` FOREIGN KEY (`id_usuario_resolvio`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_ampliacion_solicito` FOREIGN KEY (`id_usuario_solicito`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `chk_ampliacion_monto` CHECK (`monto_solicitado` > 0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `proyecto_supervision` -- DROP TABLE IF EXISTS `proyecto_supervision`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `proyecto_supervision` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_cotizacion_venta` int(10) unsigned NOT NULL, `id_oferta` int(10) unsigned NOT NULL COMMENT 'oferta seleccionada al iniciar', `id_responsable` int(10) unsigned DEFAULT NULL COMMENT 'supervisor asignado: solo él y el supervisor general ven el proyecto', `id_usuario_asigno` int(10) unsigned DEFAULT NULL, `fecha_asignacion` datetime DEFAULT NULL, `presupuesto_mo_inicial` decimal(12,2) NOT NULL DEFAULT 0.00 COMMENT 'mano de obra de la oferta al iniciar', `presupuesto_mo` decimal(12,2) NOT NULL DEFAULT 0.00 COMMENT 'tope vigente (cambia solo con una ampliación aprobada)', `estado` enum('planificacion','en_ejecucion','finalizado') NOT NULL DEFAULT 'planificacion', `fecha_inicio` date DEFAULT NULL, `fecha_fin_estimada` date DEFAULT NULL, `fecha_finalizado` datetime DEFAULT NULL, `observaciones` text DEFAULT NULL, `id_usuario_creo` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_proyecto_supervision_cv` (`id_cotizacion_venta`), KEY `fk_proyecto_supervision_oferta` (`id_oferta`), KEY `fk_proyecto_supervision_responsable` (`id_responsable`), KEY `fk_proyecto_supervision_creo` (`id_usuario_creo`), KEY `fk_proyecto_supervision_asigno` (`id_usuario_asigno`), CONSTRAINT `fk_proyecto_supervision_asigno` FOREIGN KEY (`id_usuario_asigno`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_proyecto_supervision_creo` FOREIGN KEY (`id_usuario_creo`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_proyecto_supervision_cv` FOREIGN KEY (`id_cotizacion_venta`) REFERENCES `cotizacion_venta` (`id`) ON UPDATE CASCADE, CONSTRAINT `fk_proyecto_supervision_oferta` FOREIGN KEY (`id_oferta`) REFERENCES `cotizacion_venta_oferta` (`id`) ON UPDATE CASCADE, CONSTRAINT `fk_proyecto_supervision_responsable` FOREIGN KEY (`id_responsable`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `chk_proyecto_supervision_ppto` CHECK (`presupuesto_mo` >= 0 and `presupuesto_mo_inicial` >= 0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `proyecto_tarea` -- DROP TABLE IF EXISTS `proyecto_tarea`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `proyecto_tarea` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_proyecto` int(10) unsigned NOT NULL, `id_oferta_item` int(10) unsigned DEFAULT NULL COMMENT 'ítem de la oferta del que salió', `id_usuario_asignado` int(10) unsigned DEFAULT NULL COMMENT 'usuario del sistema que hace la tarea (si no es de un pacto)', `titulo` varchar(200) NOT NULL, `descripcion` text DEFAULT NULL, `orden` int(11) NOT NULL DEFAULT 0, `fecha_inicio` date DEFAULT NULL, `fecha_fin` date DEFAULT NULL, `estado` enum('pendiente','en_proceso','finalizada') NOT NULL DEFAULT 'pendiente', `fecha_finalizada` datetime DEFAULT NULL, `id_usuario_finalizo` int(10) unsigned DEFAULT NULL, `id_pacto` int(10) unsigned DEFAULT NULL COMMENT 'pacto que la cubre', `monto_pacto` decimal(12,2) DEFAULT NULL COMMENT 'valor de la tarea en el pacto', `id_pago` int(10) unsigned DEFAULT NULL COMMENT 'pago de mano de obra que la incluye', `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), KEY `fk_tarea_proyecto` (`id_proyecto`), KEY `fk_tarea_item` (`id_oferta_item`), KEY `fk_tarea_pacto` (`id_pacto`), KEY `fk_tarea_pago` (`id_pago`), KEY `fk_tarea_finalizo` (`id_usuario_finalizo`), KEY `fk_tarea_usuario_asignado` (`id_usuario_asignado`), CONSTRAINT `fk_tarea_finalizo` FOREIGN KEY (`id_usuario_finalizo`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_tarea_item` FOREIGN KEY (`id_oferta_item`) REFERENCES `cotizacion_venta_oferta_item` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_tarea_pacto` FOREIGN KEY (`id_pacto`) REFERENCES `pacto_mano_obra` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_tarea_pago` FOREIGN KEY (`id_pago`) REFERENCES `pago_mano_obra` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_tarea_proyecto` FOREIGN KEY (`id_proyecto`) REFERENCES `proyecto_supervision` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_tarea_usuario_asignado` FOREIGN KEY (`id_usuario_asignado`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `chk_tarea_fechas` CHECK (`fecha_fin` is null or `fecha_inicio` is null or `fecha_fin` >= `fecha_inicio`), CONSTRAINT `chk_tarea_monto` CHECK (`monto_pacto` is null or `monto_pacto` >= 0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `proyecto_tarea_archivo` -- DROP TABLE IF EXISTS `proyecto_tarea_archivo`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `proyecto_tarea_archivo` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_comentario` int(10) unsigned NOT NULL, `id_tarea` int(10) unsigned NOT NULL, `tipo` enum('imagen','documento','cad','otro') NOT NULL DEFAULT 'documento', `nombre_original` varchar(255) NOT NULL, `archivo` varchar(255) NOT NULL COMMENT 'nombre guardado en storage/supervision/{proyecto}/', `mime` varchar(120) DEFAULT NULL, `tamano` int(10) unsigned NOT NULL DEFAULT 0, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `fk_tarea_archivo_comentario` (`id_comentario`), KEY `fk_tarea_archivo_tarea` (`id_tarea`), CONSTRAINT `fk_tarea_archivo_comentario` FOREIGN KEY (`id_comentario`) REFERENCES `proyecto_tarea_comentario` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_tarea_archivo_tarea` FOREIGN KEY (`id_tarea`) REFERENCES `proyecto_tarea` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Fotografías y archivos que corroboran una tarea'; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `proyecto_tarea_comentario` -- DROP TABLE IF EXISTS `proyecto_tarea_comentario`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `proyecto_tarea_comentario` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_tarea` int(10) unsigned NOT NULL, `id_usuario` int(10) unsigned DEFAULT NULL, `texto` text DEFAULT NULL COMMENT 'puede ir vacío si solo se suben archivos', `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `fk_tarea_comentario_tarea` (`id_tarea`), KEY `fk_tarea_comentario_usuario` (`id_usuario`), CONSTRAINT `fk_tarea_comentario_tarea` FOREIGN KEY (`id_tarea`) REFERENCES `proyecto_tarea` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_tarea_comentario_usuario` FOREIGN KEY (`id_usuario`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Seguimiento de cada tarea: comentarios con evidencias'; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `rol_permiso_especial` -- DROP TABLE IF EXISTS `rol_permiso_especial`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `rol_permiso_especial` ( `id_rol` int(10) unsigned NOT NULL, `clave` varchar(50) NOT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id_rol`,`clave`), CONSTRAINT `fk_rpe_rol` FOREIGN KEY (`id_rol`) REFERENCES `roles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `roles` -- DROP TABLE IF EXISTS `roles`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `roles` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `nombre` varchar(100) NOT NULL, `descripcion` varchar(255) DEFAULT NULL, `estado` tinyint(1) NOT NULL DEFAULT 1, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `nombre` (`nombre`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `solicitud_compra` -- DROP TABLE IF EXISTS `solicitud_compra`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `solicitud_compra` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `numero` varchar(20) NOT NULL, `titulo` varchar(150) NOT NULL, `descripcion` text NOT NULL, `tipo` enum('nueva_compra','reposicion','proyecto') NOT NULL DEFAULT 'nueva_compra', `proyecto_referencia` varchar(150) DEFAULT NULL, `estado` enum('borrador','enviada','en_aprobacion','aprobada','no_aprobada','con_observaciones','en_compras','comprada','en_bodega') NOT NULL DEFAULT 'borrador', `observaciones` text DEFAULT NULL, `id_usuario_solicita` int(10) unsigned NOT NULL, `id_usuario_resuelve` int(10) unsigned DEFAULT NULL, `fecha_envio` datetime DEFAULT NULL, `fecha_resolucion` datetime DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_solicitud_compra_numero` (`numero`), KEY `fk_sc_usuario_solicita` (`id_usuario_solicita`), KEY `fk_sc_usuario_resuelve` (`id_usuario_resuelve`), CONSTRAINT `fk_sc_usuario_resuelve` FOREIGN KEY (`id_usuario_resuelve`) REFERENCES `usuarios` (`id_usuario`), CONSTRAINT `fk_sc_usuario_solicita` FOREIGN KEY (`id_usuario_solicita`) REFERENCES `usuarios` (`id_usuario`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `solicitud_compra_historial` -- DROP TABLE IF EXISTS `solicitud_compra_historial`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `solicitud_compra_historial` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_solicitud` int(10) unsigned NOT NULL, `estado_anterior` varchar(30) DEFAULT NULL, `estado_nuevo` varchar(30) NOT NULL, `observaciones` text DEFAULT NULL, `id_usuario` int(10) unsigned NOT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `fk_sch_solicitud` (`id_solicitud`), KEY `fk_sch_usuario` (`id_usuario`), CONSTRAINT `fk_sch_solicitud` FOREIGN KEY (`id_solicitud`) REFERENCES `solicitud_compra` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_sch_usuario` FOREIGN KEY (`id_usuario`) REFERENCES `usuarios` (`id_usuario`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `solicitud_compra_item` -- DROP TABLE IF EXISTS `solicitud_compra_item`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `solicitud_compra_item` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_solicitud` int(10) unsigned NOT NULL, `descripcion` varchar(255) NOT NULL, `id_producto` int(10) unsigned DEFAULT NULL, `cantidad` decimal(12,2) NOT NULL, `unidad_medida` varchar(50) DEFAULT NULL, `referencia` text DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), KEY `fk_sci_solicitud` (`id_solicitud`), KEY `fk_sci_producto` (`id_producto`), CONSTRAINT `fk_sci_producto` FOREIGN KEY (`id_producto`) REFERENCES `producto` (`id`), CONSTRAINT `fk_sci_solicitud` FOREIGN KEY (`id_solicitud`) REFERENCES `solicitud_compra` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `subcategoria` -- DROP TABLE IF EXISTS `subcategoria`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `subcategoria` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_categoria` int(10) unsigned NOT NULL, `nombre` varchar(100) NOT NULL, `descripcion` text DEFAULT NULL, `estado` tinyint(1) NOT NULL DEFAULT 1, `usuario_modifico` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), KEY `fk_subcategoria_categoria` (`id_categoria`), KEY `fk_subcategoria_usuario_modifico` (`usuario_modifico`), CONSTRAINT `fk_subcategoria_categoria` FOREIGN KEY (`id_categoria`) REFERENCES `categoria` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_subcategoria_usuario_modifico` FOREIGN KEY (`usuario_modifico`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `sucursal` -- DROP TABLE IF EXISTS `sucursal`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `sucursal` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `nombre` varchar(150) NOT NULL, `direccion` text DEFAULT NULL, `descripcion` text DEFAULT NULL, `latitud` decimal(10,7) DEFAULT NULL, `longitud` decimal(10,7) DEFAULT NULL, `estado` tinyint(1) NOT NULL DEFAULT 1, `usuario_modifico` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), KEY `fk_sucursal_usuario_modifico` (`usuario_modifico`), CONSTRAINT `fk_sucursal_usuario_modifico` FOREIGN KEY (`usuario_modifico`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `tipo_bodega` -- DROP TABLE IF EXISTS `tipo_bodega`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `tipo_bodega` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `nombre` varchar(100) NOT NULL, `descripcion` text DEFAULT NULL, `estado` tinyint(1) NOT NULL DEFAULT 1, `usuario_modifico` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), KEY `fk_tipo_bodega_usuario_modifico` (`usuario_modifico`), CONSTRAINT `fk_tipo_bodega_usuario_modifico` FOREIGN KEY (`usuario_modifico`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `traslado_bodega` -- DROP TABLE IF EXISTS `traslado_bodega`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `traslado_bodega` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `numero` varchar(30) NOT NULL, `id_bodega_origen` int(10) unsigned NOT NULL, `id_bodega_destino` int(10) unsigned NOT NULL, `id_usuario_solicito` int(10) unsigned NOT NULL, `id_usuario_aprobo` int(10) unsigned DEFAULT NULL, `id_usuario_envio` int(10) unsigned DEFAULT NULL, `id_usuario_recibio` int(10) unsigned DEFAULT NULL, `estado` enum('borrador','pendiente_aprobacion','aprobado','rechazado','enviado','recibido','cancelado') NOT NULL DEFAULT 'borrador', `motivo` text DEFAULT NULL, `observaciones_rechazo` text DEFAULT NULL, `observaciones_envio` text DEFAULT NULL, `observaciones_recepcion` text DEFAULT NULL, `fecha_envio_aprobacion` datetime DEFAULT NULL, `fecha_aprobacion` datetime DEFAULT NULL, `fecha_envio` datetime DEFAULT NULL, `fecha_recepcion` datetime DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_traslado_numero` (`numero`), KEY `fk_tb_bodega_origen` (`id_bodega_origen`), KEY `fk_tb_bodega_destino` (`id_bodega_destino`), KEY `fk_tb_usuario_solicito` (`id_usuario_solicito`), KEY `fk_tb_usuario_aprobo` (`id_usuario_aprobo`), KEY `fk_tb_usuario_envio` (`id_usuario_envio`), KEY `fk_tb_usuario_recibio` (`id_usuario_recibio`), CONSTRAINT `fk_tb_bodega_destino` FOREIGN KEY (`id_bodega_destino`) REFERENCES `bodega` (`id`) ON UPDATE CASCADE, CONSTRAINT `fk_tb_bodega_origen` FOREIGN KEY (`id_bodega_origen`) REFERENCES `bodega` (`id`) ON UPDATE CASCADE, CONSTRAINT `fk_tb_usuario_aprobo` FOREIGN KEY (`id_usuario_aprobo`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_tb_usuario_envio` FOREIGN KEY (`id_usuario_envio`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_tb_usuario_recibio` FOREIGN KEY (`id_usuario_recibio`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_tb_usuario_solicito` FOREIGN KEY (`id_usuario_solicito`) REFERENCES `usuarios` (`id_usuario`) ON UPDATE CASCADE, CONSTRAINT `chk_tb_bodegas_distintas` CHECK (`id_bodega_origen` <> `id_bodega_destino`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `traslado_bodega_historial` -- DROP TABLE IF EXISTS `traslado_bodega_historial`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `traslado_bodega_historial` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_traslado` int(10) unsigned NOT NULL, `estado_anterior` varchar(30) DEFAULT NULL, `estado_nuevo` varchar(30) NOT NULL, `observaciones` text DEFAULT NULL, `id_usuario` int(10) unsigned NOT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `fk_tbh_traslado` (`id_traslado`), KEY `fk_tbh_usuario` (`id_usuario`), CONSTRAINT `fk_tbh_traslado` FOREIGN KEY (`id_traslado`) REFERENCES `traslado_bodega` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_tbh_usuario` FOREIGN KEY (`id_usuario`) REFERENCES `usuarios` (`id_usuario`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `traslado_bodega_item` -- DROP TABLE IF EXISTS `traslado_bodega_item`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `traslado_bodega_item` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_traslado` int(10) unsigned NOT NULL, `id_producto` int(10) unsigned NOT NULL, `cantidad_solicitada` decimal(12,4) NOT NULL, `cantidad_enviada` decimal(12,4) NOT NULL DEFAULT 0.0000, PRIMARY KEY (`id`), UNIQUE KEY `uq_tbi_traslado_producto` (`id_traslado`,`id_producto`), KEY `fk_tbi_producto` (`id_producto`), CONSTRAINT `fk_tbi_producto` FOREIGN KEY (`id_producto`) REFERENCES `producto` (`id`) ON UPDATE CASCADE, CONSTRAINT `fk_tbi_traslado` FOREIGN KEY (`id_traslado`) REFERENCES `traslado_bodega` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `chk_tbi_solicitada_pos` CHECK (`cantidad_solicitada` > 0), CONSTRAINT `chk_tbi_enviada_no_neg` CHECK (`cantidad_enviada` >= 0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `ubicacion_bodega` -- DROP TABLE IF EXISTS `ubicacion_bodega`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `ubicacion_bodega` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_bodega` int(10) unsigned NOT NULL, `nombre` varchar(150) NOT NULL, `descripcion` text DEFAULT NULL, `estado` tinyint(1) NOT NULL DEFAULT 1, `es_predeterminada` tinyint(1) NOT NULL DEFAULT 0, `usuario_modifico` int(10) unsigned DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), KEY `fk_ubicacion_bodega` (`id_bodega`), KEY `fk_ubicacion_bodega_usuario_modifico` (`usuario_modifico`), CONSTRAINT `fk_ubicacion_bodega` FOREIGN KEY (`id_bodega`) REFERENCES `bodega` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_ubicacion_bodega_usuario_modifico` FOREIGN KEY (`usuario_modifico`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `usuario_bodega` -- DROP TABLE IF EXISTS `usuario_bodega`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `usuario_bodega` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_usuario` int(10) unsigned NOT NULL, `id_bodega` int(10) unsigned NOT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_usuario_bodega` (`id_usuario`,`id_bodega`), KEY `fk_ub_bodega` (`id_bodega`), CONSTRAINT `fk_ub_bodega` FOREIGN KEY (`id_bodega`) REFERENCES `bodega` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_ub_usuario` FOREIGN KEY (`id_usuario`) REFERENCES `usuarios` (`id_usuario`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `usuario_permiso_especial` -- DROP TABLE IF EXISTS `usuario_permiso_especial`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `usuario_permiso_especial` ( `id_usuario` int(10) unsigned NOT NULL, `clave` varchar(50) NOT NULL, `activo` tinyint(1) NOT NULL, `usuario_modifico` int(10) unsigned DEFAULT NULL, `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id_usuario`,`clave`), KEY `fk_upe_modifico` (`usuario_modifico`), CONSTRAINT `fk_upe_modifico` FOREIGN KEY (`usuario_modifico`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_upe_usuario` FOREIGN KEY (`id_usuario`) REFERENCES `usuarios` (`id_usuario`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `usuario_roles` -- DROP TABLE IF EXISTS `usuario_roles`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `usuario_roles` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_usuario` int(10) unsigned NOT NULL, `id_rol` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uq_usuario_rol` (`id_usuario`,`id_rol`), KEY `id_rol` (`id_rol`), CONSTRAINT `usuario_roles_ibfk_1` FOREIGN KEY (`id_usuario`) REFERENCES `usuarios` (`id_usuario`), CONSTRAINT `usuario_roles_ibfk_2` FOREIGN KEY (`id_rol`) REFERENCES `roles` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `usuarios` -- DROP TABLE IF EXISTS `usuarios`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `usuarios` ( `id_usuario` int(10) unsigned NOT NULL AUTO_INCREMENT, `nombre` varchar(100) NOT NULL, `usuario` varchar(60) NOT NULL, `correo` varchar(150) NOT NULL, `id_departamento` int(10) unsigned DEFAULT NULL, `cargo` varchar(100) DEFAULT NULL, `id_sucursal` int(10) unsigned DEFAULT NULL, `password` varchar(255) NOT NULL, `activo` tinyint(1) NOT NULL DEFAULT 1, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `notif_stock_bajo` tinyint(1) NOT NULL DEFAULT 1, `notif_movimientos` tinyint(1) NOT NULL DEFAULT 1, `notif_resumen_semanal` tinyint(1) NOT NULL DEFAULT 0, `notif_login_nuevo` tinyint(1) NOT NULL DEFAULT 1, PRIMARY KEY (`id_usuario`), UNIQUE KEY `correo` (`correo`), UNIQUE KEY `uq_usuarios_usuario` (`usuario`), KEY `fk_usuario_departamento` (`id_departamento`), KEY `fk_usuario_sucursal` (`id_sucursal`), CONSTRAINT `fk_usuario_departamento` FOREIGN KEY (`id_departamento`) REFERENCES `departamento` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_usuario_sucursal` FOREIGN KEY (`id_sucursal`) REFERENCES `sucursal` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping routines for database 'tinsa_db' -- /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP FUNCTION IF EXISTS `fn_stock_delta` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_unicode_ci */ ; DELIMITER ;; CREATE DEFINER=`root`@`localhost` FUNCTION `fn_stock_delta`(`p_tipo` VARCHAR(20), `p_tipo_ref` VARCHAR(50), `p_cant` DECIMAL(12,4)) RETURNS decimal(12,4) NO SQL DETERMINISTIC BEGIN RETURN CASE WHEN p_tipo IN ('entrada','carga_inicial') THEN p_cant WHEN p_tipo = 'salida' THEN -p_cant WHEN p_tipo = 'ajuste' AND p_tipo_ref = 'aumento' THEN p_cant WHEN p_tipo = 'ajuste' AND p_tipo_ref = 'disminucion' THEN -p_cant WHEN p_tipo = 'traslado' AND p_tipo_ref = 'destino' THEN p_cant WHEN p_tipo = 'traslado' AND p_tipo_ref = 'origen' THEN -p_cant ELSE 0 END; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed