-- ===============================================
-- Sistema de Gestión de Inventario de Utensilios
-- Script de creación de base de datos
-- ===============================================

-- Crear base de datos
CREATE DATABASE IF NOT EXISTS inventario_utensilios 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE inventario_utensilios;

-- ===============================================
-- TABLA: usuarios
-- ===============================================
CREATE TABLE usuarios (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nombre VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    rol ENUM('administrador', 'encargado', 'auditor') NOT NULL DEFAULT 'encargado',
    activo BOOLEAN DEFAULT TRUE,
    ultimo_acceso TIMESTAMP NULL,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_email (email),
    INDEX idx_rol (rol),
    INDEX idx_activo (activo)
);

-- ===============================================
-- TABLA: utensilios
-- ===============================================
CREATE TABLE utensilios (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nombre VARCHAR(100) NOT NULL,
    tipo ENUM('cuchillo', 'sarten', 'olla', 'utensilio_menor', 'electrodomestico', 'otro') NOT NULL,
    descripcion TEXT,
    cantidad_total INT NOT NULL DEFAULT 0,
    cantidad_disponible INT NOT NULL DEFAULT 0,
    cantidad_minima INT NOT NULL DEFAULT 1,
    estado ENUM('excelente', 'bueno', 'regular', 'malo', 'en_revision', 'fuera_servicio') DEFAULT 'bueno',
    ubicacion VARCHAR(100) NOT NULL,
    precio_unitario DECIMAL(10,2) DEFAULT 0.00,
    fecha_registro TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    observaciones TEXT,
    activo BOOLEAN DEFAULT TRUE,
    
    INDEX idx_nombre (nombre),
    INDEX idx_tipo (tipo),
    INDEX idx_estado (estado),
    INDEX idx_ubicacion (ubicacion),
    INDEX idx_activo (activo),
    INDEX idx_cantidad_disponible (cantidad_disponible)
);

-- ===============================================

-- ===============================================
-- TABLA: auditoria
-- ===============================================
CREATE TABLE auditoria (
    id INT PRIMARY KEY AUTO_INCREMENT,
    tabla_afectada VARCHAR(50) NOT NULL,
    accion ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
    id_registro INT NOT NULL,
    usuario_id INT,
    datos_anteriores JSON,
    datos_nuevos JSON,
    ip_address VARCHAR(45),
    user_agent TEXT,
    fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE SET NULL,
    
    INDEX idx_tabla_accion (tabla_afectada, accion),
    INDEX idx_usuario (usuario_id),
    INDEX idx_fecha (fecha),
    INDEX idx_id_registro (id_registro)
);

-- ===============================================
-- TABLA: reportes_generados
-- ===============================================
CREATE TABLE reportes_generados (
    id INT PRIMARY KEY AUTO_INCREMENT,
    tipo_reporte ENUM('semanal', 'mensual', 'personalizado') NOT NULL,
    nombre_archivo VARCHAR(255) NOT NULL,
    ruta_archivo VARCHAR(500) NOT NULL,
    parametros JSON, -- Filtros aplicados
    usuario_id INT NOT NULL,
    fecha_generacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_inicio DATE,
    fecha_fin DATE,
    total_registros INT DEFAULT 0,
    
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE,
    
    INDEX idx_tipo_reporte (tipo_reporte),
    INDEX idx_usuario (usuario_id),
    INDEX idx_fecha_generacion (fecha_generacion)
);

-- ===============================================
-- TABLA: alertas_sistema
-- ===============================================
CREATE TABLE alertas_sistema (
    id INT PRIMARY KEY AUTO_INCREMENT,
    tipo_alerta ENUM('stock_bajo', 'utensilio_dañado', 'revision_pendiente', 'inactividad') NOT NULL,
    id_utensilio INT,
    mensaje VARCHAR(255) NOT NULL,
    nivel ENUM('info', 'warning', 'critical') DEFAULT 'info',
    leida BOOLEAN DEFAULT FALSE,
    usuario_destinatario INT,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_lectura TIMESTAMP NULL,
    activa BOOLEAN DEFAULT TRUE,
    
    FOREIGN KEY (id_utensilio) REFERENCES utensilios(id) ON DELETE CASCADE,
    FOREIGN KEY (usuario_destinatario) REFERENCES usuarios(id) ON DELETE CASCADE,
    
    INDEX idx_tipo_alerta (tipo_alerta),
    INDEX idx_leida (leida),
    INDEX idx_activa (activa),
    INDEX idx_usuario_destinatario (usuario_destinatario)
);