-- Inventory Management System Tables
-- Glado POS System

-- Table for inventory change logs
CREATE TABLE IF NOT EXISTS inventory_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    user_id INT NOT NULL,
    old_quantity INT NOT NULL,
    new_quantity INT NOT NULL,
    operation ENUM('set', 'add', 'subtract', 'count_adjustment') NOT NULL,
    reason TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_product_date (product_id, created_at),
    INDEX idx_user_date (user_id, created_at)
);

-- Table for supplier orders
CREATE TABLE IF NOT EXISTS supplier_orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    franchise_id INT NOT NULL,
    supplier_contact VARCHAR(255) NOT NULL,
    contact_method ENUM('whatsapp', 'email') NOT NULL,
    products_data JSON NOT NULL,
    notes TEXT,
    status ENUM('sent', 'confirmed', 'delivered', 'cancelled') DEFAULT 'sent',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (franchise_id) REFERENCES franchises(id) ON DELETE CASCADE,
    INDEX idx_franchise_date (franchise_id, created_at),
    INDEX idx_status (status)
);

-- Table for stock count records
CREATE TABLE IF NOT EXISTS stock_counts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    franchise_id INT NOT NULL,
    total_products INT NOT NULL,
    total_discrepancies INT NOT NULL,
    inconsistencies_data JSON,
    notes TEXT,
    total_value_adjustment DECIMAL(10,2) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (franchise_id) REFERENCES franchises(id) ON DELETE CASCADE,
    INDEX idx_franchise_date (franchise_id, created_at)
);

-- Table for inventory alert configuration
CREATE TABLE IF NOT EXISTS inventory_alert_config (
    id INT PRIMARY KEY AUTO_INCREMENT,
    franchise_id INT NOT NULL,
    config_data JSON NOT NULL,
    updated_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (franchise_id) REFERENCES franchises(id) ON DELETE CASCADE,
    FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_franchise (franchise_id)
);

-- Table for suppliers information
CREATE TABLE IF NOT EXISTS suppliers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    franchise_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    contact_person VARCHAR(255),
    phone VARCHAR(20),
    email VARCHAR(255),
    address TEXT,
    payment_terms VARCHAR(100),
    notes TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (franchise_id) REFERENCES franchises(id) ON DELETE CASCADE,
    INDEX idx_franchise_active (franchise_id, is_active)
);

-- Table for product-supplier relationships
CREATE TABLE IF NOT EXISTS product_suppliers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    supplier_id INT NOT NULL,
    supplier_product_code VARCHAR(100),
    unit_cost DECIMAL(10,2),
    min_order_quantity INT DEFAULT 1,
    lead_time_days INT DEFAULT 7,
    is_preferred BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE CASCADE,
    UNIQUE KEY unique_product_supplier (product_id, supplier_id),
    INDEX idx_supplier (supplier_id),
    INDEX idx_preferred (is_preferred)
);

-- Table for purchase orders
CREATE TABLE IF NOT EXISTS purchase_orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    franchise_id INT NOT NULL,
    supplier_id INT NOT NULL,
    order_number VARCHAR(50) NOT NULL,
    user_id INT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
    status ENUM('draft', 'sent', 'confirmed', 'received', 'cancelled') DEFAULT 'draft',
    order_date DATE NOT NULL,
    expected_delivery_date DATE,
    actual_delivery_date DATE,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (franchise_id) REFERENCES franchises(id) ON DELETE CASCADE,
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_order_number (franchise_id, order_number),
    INDEX idx_franchise_status (franchise_id, status),
    INDEX idx_supplier_date (supplier_id, order_date)
);

-- Table for purchase order items
CREATE TABLE IF NOT EXISTS purchase_order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    purchase_order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_cost DECIMAL(10,2) NOT NULL,
    total_cost DECIMAL(10,2) NOT NULL,
    received_quantity INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (purchase_order_id) REFERENCES purchase_orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    INDEX idx_purchase_order (purchase_order_id),
    INDEX idx_product (product_id)
);

-- Table for inventory alerts history
CREATE TABLE IF NOT EXISTS inventory_alerts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    franchise_id INT NOT NULL,
    alert_type ENUM('low_stock', 'expiration', 'out_of_stock') NOT NULL,
    product_id INT NOT NULL,
    message TEXT NOT NULL,
    notification_method ENUM('whatsapp', 'email', 'both') NOT NULL,
    recipients JSON NOT NULL,
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('sent', 'failed', 'pending') DEFAULT 'pending',
    FOREIGN KEY (franchise_id) REFERENCES franchises(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    INDEX idx_franchise_type (franchise_id, alert_type),
    INDEX idx_product_date (product_id, sent_at),
    INDEX idx_status (status)
);

-- Add indexes to existing products table for better performance
ALTER TABLE products 
ADD INDEX IF NOT EXISTS idx_franchise_stock (franchise_id, stock_quantity),
ADD INDEX IF NOT EXISTS idx_franchise_expiration (franchise_id, expiration_date),
ADD INDEX IF NOT EXISTS idx_low_stock (stock_quantity, min_stock_level),
ADD INDEX IF NOT EXISTS idx_active_products (franchise_id, is_active);

-- Add indexes to existing categories table
ALTER TABLE categories 
ADD INDEX IF NOT EXISTS idx_franchise_active (franchise_id, is_active);

-- Create view for low stock products
CREATE OR REPLACE VIEW low_stock_products AS
SELECT 
    p.*,
    c.name as category_name,
    (p.min_stock_level - p.stock_quantity) as shortage_quantity,
    CASE 
        WHEN p.stock_quantity = 0 THEN 'out_of_stock'
        WHEN p.stock_quantity <= p.min_stock_level * 0.5 THEN 'critical'
        ELSE 'low'
    END as urgency_level
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
WHERE p.is_active = 1 
AND p.stock_quantity <= p.min_stock_level;

-- Create view for expiring products
CREATE OR REPLACE VIEW expiring_products AS
SELECT 
    p.*,
    c.name as category_name,
    DATEDIFF(p.expiration_date, CURDATE()) as days_to_expire,
    CASE 
        WHEN DATEDIFF(p.expiration_date, CURDATE()) <= 0 THEN 'expired'
        WHEN DATEDIFF(p.expiration_date, CURDATE()) <= 2 THEN 'critical'
        WHEN DATEDIFF(p.expiration_date, CURDATE()) <= 7 THEN 'warning'
        ELSE 'normal'
    END as expiration_status
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
WHERE p.is_active = 1 
AND p.expiration_date IS NOT NULL
AND p.expiration_date <= DATE_ADD(CURDATE(), INTERVAL 30 DAY);

-- Create view for inventory summary
CREATE OR REPLACE VIEW inventory_summary AS
SELECT 
    f.id as franchise_id,
    f.name as franchise_name,
    COUNT(p.id) as total_products,
    SUM(p.stock_quantity) as total_units,
    SUM(p.stock_quantity * p.cost) as total_cost_value,
    SUM(p.stock_quantity * p.price) as total_retail_value,
    COUNT(CASE WHEN p.stock_quantity <= p.min_stock_level THEN 1 END) as low_stock_count,
    COUNT(CASE WHEN p.stock_quantity = 0 THEN 1 END) as out_of_stock_count,
    COUNT(CASE WHEN p.expiration_date <= DATE_ADD(CURDATE(), INTERVAL 7 DAY) THEN 1 END) as expiring_soon_count
FROM franchises f
LEFT JOIN products p ON f.id = p.franchise_id AND p.is_active = 1
GROUP BY f.id, f.name;

-- Insert default alert configuration for existing franchises
INSERT IGNORE INTO inventory_alert_config (franchise_id, config_data, updated_by)
SELECT 
    f.id,
    JSON_OBJECT(
        'low_stock_enabled', true,
        'expiration_enabled', true,
        'expiration_days_ahead', 7,
        'notification_method', 'both',
        'notification_contacts', JSON_ARRAY()
    ),
    1
FROM franchises f;

-- Create stored procedure for automatic low stock alerts
DELIMITER //
CREATE PROCEDURE IF NOT EXISTS CheckLowStockAlerts()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_product_id INT;
    DECLARE v_franchise_id INT;
    DECLARE v_product_name VARCHAR(255);
    DECLARE v_stock_quantity INT;
    DECLARE v_min_stock_level INT;
    
    DECLARE cur CURSOR FOR 
        SELECT id, franchise_id, name, stock_quantity, min_stock_level
        FROM products 
        WHERE is_active = 1 
        AND stock_quantity <= min_stock_level
        AND id NOT IN (
            SELECT product_id 
            FROM inventory_alerts 
            WHERE alert_type = 'low_stock' 
            AND DATE(sent_at) = CURDATE()
        );
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO v_product_id, v_franchise_id, v_product_name, v_stock_quantity, v_min_stock_level;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- Insert alert record
        INSERT INTO inventory_alerts (
            franchise_id, 
            alert_type, 
            product_id, 
            message, 
            notification_method, 
            recipients,
            status
        ) VALUES (
            v_franchise_id,
            'low_stock',
            v_product_id,
            CONCAT('Stock bajo detectado para ', v_product_name, '. Stock actual: ', v_stock_quantity, ', Mínimo: ', v_min_stock_level),
            'both',
            JSON_ARRAY(),
            'pending'
        );
        
    END LOOP;
    
    CLOSE cur;
END //
DELIMITER ;

-- Create stored procedure for automatic expiration alerts
DELIMITER //
CREATE PROCEDURE IF NOT EXISTS CheckExpirationAlerts()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_product_id INT;
    DECLARE v_franchise_id INT;
    DECLARE v_product_name VARCHAR(255);
    DECLARE v_expiration_date DATE;
    DECLARE v_days_to_expire INT;
    
    DECLARE cur CURSOR FOR 
        SELECT id, franchise_id, name, expiration_date, DATEDIFF(expiration_date, CURDATE()) as days_to_expire
        FROM products 
        WHERE is_active = 1 
        AND expiration_date IS NOT NULL
        AND expiration_date <= DATE_ADD(CURDATE(), INTERVAL 7 DAY)
        AND id NOT IN (
            SELECT product_id 
            FROM inventory_alerts 
            WHERE alert_type = 'expiration' 
            AND DATE(sent_at) = CURDATE()
        );
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO v_product_id, v_franchise_id, v_product_name, v_expiration_date, v_days_to_expire;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- Insert alert record
        INSERT INTO inventory_alerts (
            franchise_id, 
            alert_type, 
            product_id, 
            message, 
            notification_method, 
            recipients,
            status
        ) VALUES (
            v_franchise_id,
            'expiration',
            v_product_id,
            CONCAT('Producto próximo a vencer: ', v_product_name, '. Vence en ', v_days_to_expire, ' día(s) (', v_expiration_date, ')'),
            'both',
            JSON_ARRAY(),
            'pending'
        );
        
    END LOOP;
    
    CLOSE cur;
END //
DELIMITER ;

-- Create event scheduler for automatic alerts (runs daily at 8 AM)
SET GLOBAL event_scheduler = ON;

CREATE EVENT IF NOT EXISTS daily_inventory_alerts
ON SCHEDULE EVERY 1 DAY
STARTS TIMESTAMP(CURDATE() + INTERVAL 1 DAY, '08:00:00')
DO
BEGIN
    CALL CheckLowStockAlerts();
    CALL CheckExpirationAlerts();
END;