-- Base de datos: gestion_almacen
-- Motor: InnoDB
-- Charset: utf8mb4


-- ============================================================
-- 2. PROVEEDORES
-- ============================================================
CREATE TABLE `proveedores` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `nombre` VARCHAR(150) NOT NULL,
    `cif_nif` VARCHAR(20) NULL DEFAULT NULL,
    `contacto` VARCHAR(100) NULL DEFAULT NULL,
    `telefono` VARCHAR(20) NULL DEFAULT NULL,
    `email` VARCHAR(100) NULL DEFAULT NULL,
    `direccion` VARCHAR(255) NULL DEFAULT NULL,
    `observaciones` TEXT NULL DEFAULT NULL,
    `activo` TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_proveedores_cif` (`cif_nif`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 3. CLIENTES
-- ============================================================
CREATE TABLE `clientes` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `nombre` VARCHAR(150) NOT NULL,
    `cif_nif` VARCHAR(20) NULL DEFAULT NULL,
    `contacto` VARCHAR(100) NULL DEFAULT NULL,
    `telefono` VARCHAR(20) NULL DEFAULT NULL,
    `email` VARCHAR(100) NULL DEFAULT NULL,
    `direccion` VARCHAR(255) NULL DEFAULT NULL,
    `observaciones` TEXT NULL DEFAULT NULL,
    `activo` TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_clientes_cif` (`cif_nif`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 4. CATEGORIAS
-- ============================================================
CREATE TABLE `categorias` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `nombre` VARCHAR(100) NOT NULL,
    `descripcion` TEXT NULL DEFAULT NULL,
    `activo` TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 5. ARTICULOS
-- ============================================================
CREATE TABLE `articulos` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `codigo` VARCHAR(50) NOT NULL,
    `nombre` VARCHAR(150) NOT NULL,
    `descripcion` TEXT NULL DEFAULT NULL,
    `categoria_id` INT UNSIGNED NULL DEFAULT NULL,
    `stock_actual` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `stock_minimo` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `unidad_medida` VARCHAR(20) NOT NULL DEFAULT 'unidades',
    `precio_coste` DECIMAL(10,2) NULL DEFAULT NULL,
    `ubicacion` VARCHAR(100) NULL DEFAULT NULL,
    `activo` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_articulos_codigo` (`codigo`),
    KEY `fk_articulos_categoria` (`categoria_id`),
    CONSTRAINT `fk_articulos_categoria` FOREIGN KEY (`categoria_id`) REFERENCES `categorias` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 6. ALBARANES
-- ============================================================
CREATE TABLE `albaranes` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `tipo` ENUM('entrada','salida') NOT NULL,
    `numero_albaran` VARCHAR(20) NOT NULL,
    `fecha` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `usuario_id` INT UNSIGNED NOT NULL,
    `usuario_nombre` VARCHAR(100) NOT NULL,
    `proveedor_id` INT UNSIGNED NULL DEFAULT NULL,
    `cliente_id` INT UNSIGNED NULL DEFAULT NULL,
    `referencia_externa` VARCHAR(50) NULL DEFAULT NULL,
    `observaciones` TEXT NULL DEFAULT NULL,
    `created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_albaranes_numero` (`numero_albaran`),
    KEY `fk_albaranes_usuario` (`usuario_id`),
    KEY `fk_albaranes_proveedor` (`proveedor_id`),
    KEY `fk_albaranes_cliente` (`cliente_id`),
    CONSTRAINT `fk_albaranes_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT `fk_albaranes_proveedor` FOREIGN KEY (`proveedor_id`) REFERENCES `proveedores` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `fk_albaranes_cliente` FOREIGN KEY (`cliente_id`) REFERENCES `clientes` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 7. ALBARANES_LINEAS
-- ============================================================
CREATE TABLE `albaranes_lineas` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `albaran_id` INT UNSIGNED NOT NULL,
    `articulo_id` INT UNSIGNED NOT NULL,
    `cantidad` DECIMAL(10,2) NOT NULL,
    `precio_unitario` DECIMAL(10,2) NULL DEFAULT NULL,
    `observaciones` TEXT NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `fk_lineas_albaran` (`albaran_id`),
    KEY `fk_lineas_articulo` (`articulo_id`),
    CONSTRAINT `fk_lineas_albaran` FOREIGN KEY (`albaran_id`) REFERENCES `albaranes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_lineas_articulo` FOREIGN KEY (`articulo_id`) REFERENCES `articulos` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 8. MOVIMIENTOS_STOCK
-- ============================================================
CREATE TABLE `movimientos_stock` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `articulo_id` INT UNSIGNED NOT NULL,
    `tipo_movimiento` ENUM('entrada','salida','ajuste') NOT NULL,
    `cantidad` DECIMAL(10,2) NOT NULL,
    `stock_anterior` DECIMAL(10,2) NOT NULL,
    `stock_nuevo` DECIMAL(10,2) NOT NULL,
    `referencia` VARCHAR(50) NULL DEFAULT NULL,
    `usuario_id` INT UNSIGNED NOT NULL,
    `created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `fk_movimientos_articulo` (`articulo_id`),
    KEY `fk_movimientos_usuario` (`usuario_id`),
    CONSTRAINT `fk_movimientos_articulo` FOREIGN KEY (`articulo_id`) REFERENCES `articulos` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT `fk_movimientos_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 9. ALERTAS_STOCK
-- ============================================================
CREATE TABLE `alertas_stock` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `articulo_id` INT UNSIGNED NOT NULL,
    `tipo` ENUM('minimo','agotado') NOT NULL,
    `stock_actual` DECIMAL(10,2) NOT NULL,
    `stock_minimo` DECIMAL(10,2) NOT NULL,
    `leida` TINYINT(1) NOT NULL DEFAULT 0,
    `created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `fk_alertas_articulo` (`articulo_id`),
    CONSTRAINT `fk_alertas_articulo` FOREIGN KEY (`articulo_id`) REFERENCES `articulos` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Categorías de ejemplo
INSERT INTO `categorias` (`nombre`, `descripcion`) VALUES
('Electrónica', 'Componentes y dispositivos electrónicos'),
('Ferretería', 'Herramientas y materiales de construcción'),
('Papelería', 'Material de oficina y escritorio'),
('Informática', 'Equipos informáticos y periféricos');

