-- =====================================================
-- SQL: SISTEMA DE ALERTAS DE STOCK BAJO
-- Tablas para gestionar alertas y notificaciones
-- =====================================================

-- Tabla de configuración de alertas
CREATE TABLE IF NOT EXISTS alertas_configuracion (
    id_config INT AUTO_INCREMENT PRIMARY KEY,
    alertas_activas TINYINT(1) DEFAULT 1 COMMENT 'Habilitar/deshabilitar alertas',
    notificar_email TINYINT(1) DEFAULT 1 COMMENT 'Enviar alertas por email',
    notificar_whatsapp TINYINT(1) DEFAULT 0 COMMENT 'Enviar alertas por WhatsApp',
    frecuencia_verificacion VARCHAR(20) DEFAULT 'diario' COMMENT 'diario, semanal, tiempo_real',
    hora_verificacion TIME DEFAULT '08:00:00' COMMENT 'Hora para verificación diaria',
    umbral_critico INT DEFAULT 5 COMMENT 'Stock crítico (rojo)',
    umbral_bajo INT DEFAULT 10 COMMENT 'Stock bajo (amarillo)',
    email_destino VARCHAR(500) COMMENT 'Emails separados por coma',
    telefono_destino VARCHAR(500) COMMENT 'Teléfonos separados por coma',
    whatsapp_api_url VARCHAR(255) COMMENT 'URL de API WhatsApp',
    whatsapp_api_token VARCHAR(255) COMMENT 'Token de API WhatsApp',
    smtp_host VARCHAR(100) DEFAULT 'smtp.gmail.com',
    smtp_port INT DEFAULT 587,
    smtp_user VARCHAR(100),
    smtp_pass VARCHAR(255),
    smtp_from_name VARCHAR(100) DEFAULT 'Sistema DIGITALSHOP',
    ultima_verificacion DATETIME,
    fecha_creacion DATETIME DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de historial de alertas enviadas
CREATE TABLE IF NOT EXISTS alertas_historial (
    id_alerta INT AUTO_INCREMENT PRIMARY KEY,
    tipo_alerta ENUM('STOCK_BAJO', 'STOCK_CRITICO', 'SIN_STOCK') NOT NULL,
    id_producto INT NOT NULL,
    id_almacen INT,
    stock_actual DECIMAL(10,2) NOT NULL,
    stock_minimo DECIMAL(10,2) NOT NULL,
    mensaje TEXT,
    notificado_email TINYINT(1) DEFAULT 0,
    notificado_whatsapp TINYINT(1) DEFAULT 0,
    email_enviado_a VARCHAR(500),
    whatsapp_enviado_a VARCHAR(500),
    leida TINYINT(1) DEFAULT 0,
    leida_por INT,
    fecha_lectura DATETIME,
    fecha_creacion DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_producto) REFERENCES productos(id_producto) ON DELETE CASCADE,
    FOREIGN KEY (id_almacen) REFERENCES almacenes(id_almacen) ON DELETE SET NULL,
    INDEX idx_tipo_alerta (tipo_alerta),
    INDEX idx_fecha (fecha_creacion),
    INDEX idx_leida (leida)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de suscriptores a alertas
CREATE TABLE IF NOT EXISTS alertas_suscriptores (
    id_suscriptor INT AUTO_INCREMENT PRIMARY KEY,
    id_usuario INT,
    nombre VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    telefono VARCHAR(20),
    recibir_email TINYINT(1) DEFAULT 1,
    recibir_whatsapp TINYINT(1) DEFAULT 0,
    tipos_alerta VARCHAR(100) DEFAULT 'STOCK_BAJO,STOCK_CRITICO,SIN_STOCK',
    activo TINYINT(1) DEFAULT 1,
    fecha_creacion DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_usuario) REFERENCES usuarios(id_usuario) ON DELETE SET NULL,
    INDEX idx_activo (activo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de productos excluidos de alertas
CREATE TABLE IF NOT EXISTS alertas_productos_excluidos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_producto INT NOT NULL,
    motivo VARCHAR(255),
    excluido_por INT,
    fecha_exclusion DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_producto) REFERENCES productos(id_producto) ON DELETE CASCADE,
    FOREIGN KEY (excluido_por) REFERENCES usuarios(id_usuario) ON DELETE SET NULL,
    UNIQUE KEY unique_producto (id_producto)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insertar configuración por defecto
INSERT INTO alertas_configuracion (
    alertas_activas, 
    notificar_email, 
    notificar_whatsapp,
    frecuencia_verificacion,
    umbral_critico,
    umbral_bajo
) VALUES (1, 1, 0, 'diario', 5, 10)
ON DUPLICATE KEY UPDATE id_config = id_config;

-- Vista para productos con stock bajo
CREATE OR REPLACE VIEW v_productos_stock_bajo AS
SELECT 
    p.id_producto,
    p.codigo,
    p.nombre AS producto_nombre,
    p.stock_minimo,
    c.nombre AS categoria,
    COALESCE(
        (SELECT SUM(k.cantidad) 
         FROM kardex k 
         WHERE k.id_producto = p.id_producto),
        0
    ) AS stock_actual,
    CASE 
        WHEN COALESCE((SELECT SUM(k.cantidad) FROM kardex k WHERE k.id_producto = p.id_producto), 0) <= 0 THEN 'SIN_STOCK'
        WHEN COALESCE((SELECT SUM(k.cantidad) FROM kardex k WHERE k.id_producto = p.id_producto), 0) <= (SELECT umbral_critico FROM alertas_configuracion LIMIT 1) THEN 'STOCK_CRITICO'
        WHEN COALESCE((SELECT SUM(k.cantidad) FROM kardex k WHERE k.id_producto = p.id_producto), 0) <= p.stock_minimo THEN 'STOCK_BAJO'
        ELSE 'NORMAL'
    END AS nivel_alerta,
    p.precio_compra,
    p.precio_venta
FROM productos p
LEFT JOIN categorias c ON p.id_categoria = c.id_categoria
WHERE p.estado = 1
AND p.id_producto NOT IN (SELECT id_producto FROM alertas_productos_excluidos)
HAVING nivel_alerta != 'NORMAL'
ORDER BY 
    CASE nivel_alerta
        WHEN 'SIN_STOCK' THEN 1
        WHEN 'STOCK_CRITICO' THEN 2
        WHEN 'STOCK_BAJO' THEN 3
    END,
    stock_actual ASC;

-- Índices adicionales para optimizar consultas
CREATE INDEX IF NOT EXISTS idx_productos_stock_minimo ON productos(stock_minimo);
CREATE INDEX IF NOT EXISTS idx_alertas_hist_producto ON alertas_historial(id_producto, fecha_creacion);
