Inventario.sql

-- =====================================================
-- SCRIPT DE CREACIÓN DE BASE DE DATOS - EMPRESA INDUSTRIAL
-- Sistema de gestión de materias primas, productos y ventas por lotes
-- MariaDB/MySQL compatible
-- =====================================================

-- Crear la base de datos
CREATE DATABASE IF NOT EXISTS empresa_industrial;
USE empresa_industrial;

-- =====================================================
-- TABLA: PROVEEDORES
-- =====================================================
CREATE TABLE PROVEEDORES (
    proveedor_id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    contacto_principal VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- =====================================================
-- TABLA: MATERIAS_PRIMAS
-- =====================================================
CREATE TABLE MATERIAS_PRIMAS (
    materia_prima_id INT AUTO_INCREMENT PRIMARY KEY,
    codigo VARCHAR(50) NOT NULL UNIQUE,
    nombre VARCHAR(100) NOT NULL,
    precio_unitario DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    categoria VARCHAR(50),
    stock_minimo_dinamico DECIMAL(12,2) DEFAULT NULL,
    stock_maximo_dinamico DECIMAL(12,2) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- =====================================================
-- TABLA: ALMACENES
-- =====================================================
CREATE TABLE ALMACENES (
    almacen_id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    ubicacion VARCHAR(200),
    capacidad_maxima DECIMAL(12,2) DEFAULT 0.00,
    capacidad_actual DECIMAL(12,2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- =====================================================
-- TABLA: PRODUCTOS
-- =====================================================
CREATE TABLE PRODUCTOS (
    producto_id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    precio_venta DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    categoria VARCHAR(50),
    stock_minimo_dinamico DECIMAL(12,2) DEFAULT NULL,
    stock_maximo_dinamico DECIMAL(12,2) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- =====================================================
-- TABLA: CLIENTES
-- =====================================================
CREATE TABLE CLIENTES (
    cliente_id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    contacto_principal VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- =====================================================
-- TABLA: LOTES_MATERIAS
-- =====================================================
CREATE TABLE LOTES_MATERIAS (
    lote_materia_id INT AUTO_INCREMENT PRIMARY KEY,
    materia_prima_id INT NOT NULL,
    almacen_id INT NOT NULL,
    cantidad_disponible DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    fecha_compra DATE NOT NULL,
    fecha_caducidad DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (materia_prima_id) REFERENCES MATERIAS_PRIMAS(materia_prima_id) ON DELETE CASCADE,
    FOREIGN KEY (almacen_id) REFERENCES ALMACENES(almacen_id) ON DELETE CASCADE
);

-- =====================================================
-- TABLA: LOTES_PRODUCTOS
-- =====================================================
CREATE TABLE LOTES_PRODUCTOS (
    lote_producto_id INT AUTO_INCREMENT PRIMARY KEY,
    producto_id INT NOT NULL,
    almacen_id INT NOT NULL,
    cantidad_disponible DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    fecha_produccion DATE NOT NULL,
    fecha_caducidad DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (producto_id) REFERENCES PRODUCTOS(producto_id) ON DELETE CASCADE,
    FOREIGN KEY (almacen_id) REFERENCES ALMACENES(almacen_id) ON DELETE CASCADE
);

-- =====================================================
-- TABLA: COMPRAS
-- =====================================================
CREATE TABLE COMPRAS (
    compra_id INT AUTO_INCREMENT PRIMARY KEY,
    proveedor_id INT NOT NULL,
    lote_materia_id INT NOT NULL,
    fecha_compra DATE NOT NULL,
    subtotal DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (proveedor_id) REFERENCES PROVEEDORES(proveedor_id) ON DELETE CASCADE,
    FOREIGN KEY (lote_materia_id) REFERENCES LOTES_MATERIAS(lote_materia_id) ON DELETE CASCADE
);

-- =====================================================
-- TABLA: VENTAS
-- =====================================================
CREATE TABLE VENTAS (
    venta_id INT AUTO_INCREMENT PRIMARY KEY,
    cliente_id INT NOT NULL,
    lote_producto_id INT NOT NULL,
    fecha_venta DATE NOT NULL,
    cantidad_vendida DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total_venta DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (cliente_id) REFERENCES CLIENTES(cliente_id) ON DELETE CASCADE,
    FOREIGN KEY (lote_producto_id) REFERENCES LOTES_PRODUCTOS(lote_producto_id) ON DELETE CASCADE
);

-- =====================================================
-- TABLA: PRODUCTO_MATERIAS (Relación de componentes)
-- =====================================================
CREATE TABLE PRODUCTO_MATERIAS (
    producto_materia_id INT AUTO_INCREMENT PRIMARY KEY,
    producto_id INT NOT NULL,
    materia_prima_id INT NOT NULL,
    cantidad_requerida DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (producto_id) REFERENCES PRODUCTOS(producto_id) ON DELETE CASCADE,
    FOREIGN KEY (materia_prima_id) REFERENCES MATERIAS_PRIMAS(materia_prima_id) ON DELETE CASCADE,
    
    -- Evitar duplicados de la misma combinación producto-materia prima
    UNIQUE KEY unique_producto_materia (producto_id, materia_prima_id)
);

-- =====================================================
-- TABLA: CONSUMO_LOTES_MATERIAS (Trazabilidad)
-- =====================================================
CREATE TABLE CONSUMO_LOTES_MATERIAS (
    consumo_id INT AUTO_INCREMENT PRIMARY KEY,
    lote_producto_id INT NOT NULL,
    lote_materia_id INT NOT NULL,
    cantidad_consumida DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (lote_producto_id) REFERENCES LOTES_PRODUCTOS(lote_producto_id) ON DELETE CASCADE,
    FOREIGN KEY (lote_materia_id) REFERENCES LOTES_MATERIAS(lote_materia_id) ON DELETE CASCADE
);

-- =====================================================
-- TABLA: MOVIMIENTOS_INVENTARIO (Auditoría)
-- =====================================================
CREATE TABLE MOVIMIENTOS_INVENTARIO (
    movimiento_id INT AUTO_INCREMENT PRIMARY KEY,
    almacen_id INT NOT NULL,
    tipo_item ENUM('MATERIA_PRIMA', 'PRODUCTO') NOT NULL,
    item_id INT NOT NULL,
    tipo_movimiento ENUM('ENTRADA', 'SALIDA', 'AJUSTE', 'TRANSFERENCIA', 'MERMA') NOT NULL,
    cantidad DECIMAL(12,2) NOT NULL,
    fecha_movimiento TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (almacen_id) REFERENCES ALMACENES(almacen_id) ON DELETE CASCADE
);

-- =====================================================
-- ÍNDICES PARA OPTIMIZAR CONSULTAS
-- =====================================================

-- Índices en LOTES_MATERIAS
CREATE INDEX idx_lotes_materias_caducidad ON LOTES_MATERIAS(fecha_caducidad);
CREATE INDEX idx_lotes_materias_materia ON LOTES_MATERIAS(materia_prima_id);
CREATE INDEX idx_lotes_materias_almacen ON LOTES_MATERIAS(almacen_id);

-- Índices en LOTES_PRODUCTOS
CREATE INDEX idx_lotes_productos_caducidad ON LOTES_PRODUCTOS(fecha_caducidad);
CREATE INDEX idx_lotes_productos_producto ON LOTES_PRODUCTOS(producto_id);
CREATE INDEX idx_lotes_productos_almacen ON LOTES_PRODUCTOS(almacen_id);

-- Índices en COMPRAS
CREATE INDEX idx_compras_fecha ON COMPRAS(fecha_compra);
CREATE INDEX idx_compras_proveedor ON COMPRAS(proveedor_id);

-- Índices en VENTAS
CREATE INDEX idx_ventas_fecha ON VENTAS(fecha_venta);
CREATE INDEX idx_ventas_cliente ON VENTAS(cliente_id);

-- Índices en MOVIMIENTOS_INVENTARIO
CREATE INDEX idx_movimientos_fecha ON MOVIMIENTOS_INVENTARIO(fecha_movimiento);
CREATE INDEX idx_movimientos_tipo ON MOVIMIENTOS_INVENTARIO(tipo_movimiento);
CREATE INDEX idx_movimientos_almacen ON MOVIMIENTOS_INVENTARIO(almacen_id);

-- =====================================================
-- DATOS DE EJEMPLO (OPCIONAL)
-- =====================================================

-- Insertar almacenes de ejemplo
INSERT INTO ALMACENES (nombre, ubicacion, capacidad_maxima) VALUES
('Almacén Principal', 'Planta Baja - Sector A', 10000.00),
('Almacén de Materias Primas', 'Planta Baja - Sector B', 5000.00),
('Almacén de Productos Terminados', 'Primer Piso', 8000.00);

-- Insertar proveedores de ejemplo
INSERT INTO PROVEEDORES (nombre, contacto_principal) VALUES
('Proveedora Industrial SA', 'Juan Pérez'),
('Materias Primas del Norte', 'María García'),
('Suministros Técnicos SRL', 'Carlos López');

-- Insertar clientes de ejemplo
INSERT INTO CLIENTES (nombre, contacto_principal) VALUES
('Distribuidora Central', 'Ana Martínez'),
('Comercial del Sur', 'Pedro Rodríguez'),
('Mayorista Express', 'Laura Sánchez');

-- =====================================================
-- SCRIPT COMPLETADO
-- =====================================================
-- La base de datos está lista para usar
-- Recuerda ajustar los tipos de datos según tus necesidades específicas