-- ============================================================
-- SalonPro X - Stored Procedures
-- ============================================================

USE `salonprox_db`;

DELIMITER $$

-- Get daily sales report
CREATE PROCEDURE `sp_daily_sales`(IN p_date DATE, IN p_branch_id INT UNSIGNED)
BEGIN
    SELECT
        COUNT(DISTINCT i.id) AS invoice_count,
        COALESCE(SUM(i.subtotal), 0) AS subtotal,
        COALESCE(SUM(i.tax_total), 0) AS tax_total,
        COALESCE(SUM(i.discount_total), 0) AS discount_total,
        COALESCE(SUM(i.grand_total), 0) AS grand_total,
        COALESCE(SUM(i.paid_amount), 0) AS paid_amount,
        COALESCE(SUM(i.balance_amount), 0) AS balance_amount,
        COUNT(DISTINCT i.customer_id) AS customer_count
    FROM `invoices` i
    WHERE DATE(i.created_at) = p_date
    AND (p_branch_id IS NULL OR i.branch_id = p_branch_id);
END$$

-- Get monthly sales report
CREATE PROCEDURE `sp_monthly_sales`(IN p_year INT, IN p_month INT, IN p_branch_id INT UNSIGNED)
BEGIN
    SELECT
        COUNT(DISTINCT i.id) AS invoice_count,
        COALESCE(SUM(i.subtotal), 0) AS subtotal,
        COALESCE(SUM(i.tax_total), 0) AS tax_total,
        COALESCE(SUM(i.discount_total), 0) AS discount_total,
        COALESCE(SUM(i.grand_total), 0) AS grand_total,
        COALESCE(SUM(i.paid_amount), 0) AS paid_amount,
        COUNT(DISTINCT i.customer_id) AS customer_count
    FROM `invoices` i
    WHERE YEAR(i.created_at) = p_year AND MONTH(i.created_at) = p_month
    AND (p_branch_id IS NULL OR i.branch_id = p_branch_id);
END$$

-- Get top services by revenue
CREATE PROCEDURE `sp_top_services`(IN p_start_date DATE, IN p_end_date DATE, IN p_limit INT)
BEGIN
    SELECT
        s.id,
        s.name,
        s.price,
        COUNT(asi.id) AS booking_count,
        COALESCE(SUM(asi.price), 0) AS total_revenue
    FROM `services` s
    LEFT JOIN `appointment_services` asi ON asi.service_id = s.id
    LEFT JOIN `appointments` a ON a.id = asi.appointment_id
    WHERE (a.start_datetime BETWEEN p_start_date AND p_end_date OR a.start_datetime IS NULL)
    AND a.status NOT IN ('cancelled', 'no_show')
    GROUP BY s.id, s.name, s.price
    ORDER BY total_revenue DESC
    LIMIT p_limit;
END$$

-- Get top employees by revenue
CREATE PROCEDURE `sp_top_employees`(IN p_start_date DATE, IN p_end_date DATE, IN p_limit INT)
BEGIN
    SELECT
        e.id,
        e.first_name,
        e.last_name,
        e.job_title,
        COUNT(asi.id) AS service_count,
        COALESCE(SUM(asi.price), 0) AS total_revenue,
        COALESCE(SUM(ec.commission_amount), 0) AS total_commission
    FROM `employees` e
    LEFT JOIN `appointment_services` asi ON asi.employee_id = e.id
    LEFT JOIN `appointments` a ON a.id = asi.appointment_id
    LEFT JOIN `employee_commissions` ec ON ec.employee_id = e.id AND ec.invoice_id IN (
        SELECT i.id FROM `invoices` i WHERE DATE(i.created_at) BETWEEN p_start_date AND p_end_date
    )
    WHERE (a.start_datetime BETWEEN p_start_date AND p_end_date OR a.start_datetime IS NULL)
    AND a.status NOT IN ('cancelled', 'no_show')
    GROUP BY e.id, e.first_name, e.last_name, e.job_title
    ORDER BY total_revenue DESC
    LIMIT p_limit;
END$$

-- Get top customers by spending
CREATE PROCEDURE `sp_top_customers`(IN p_start_date DATE, IN p_end_date DATE, IN p_limit INT)
BEGIN
    SELECT
        c.id,
        c.first_name,
        c.last_name,
        c.email,
        c.phone,
        c.total_visits,
        COALESCE(SUM(i.grand_total), 0) AS total_spent,
        MAX(i.created_at) AS last_purchase
    FROM `customers` c
    LEFT JOIN `invoices` i ON i.customer_id = c.id
        AND DATE(i.created_at) BETWEEN p_start_date AND p_end_date
    GROUP BY c.id, c.first_name, c.last_name, c.email, c.phone, c.total_visits
    ORDER BY total_spent DESC
    LIMIT p_limit;
END$$

-- Get profit and loss summary
CREATE PROCEDURE `sp_profit_loss`(IN p_start_date DATE, IN p_end_date DATE, IN p_branch_id INT UNSIGNED)
BEGIN
    DECLARE total_income DECIMAL(12,2);
    DECLARE total_expenses DECIMAL(12,2);
    DECLARE total_service_cost DECIMAL(12,2);
    DECLARE total_product_cost DECIMAL(12,2);
    DECLARE gross_profit DECIMAL(12,2);
    DECLARE net_profit DECIMAL(12,2);
    
    SELECT COALESCE(SUM(grand_total), 0) INTO total_income
    FROM `invoices`
    WHERE DATE(created_at) BETWEEN p_start_date AND p_end_date
    AND (p_branch_id IS NULL OR branch_id = p_branch_id);
    
    SELECT COALESCE(SUM(amount), 0) INTO total_expenses
    FROM `expenses`
    WHERE DATE BETWEEN p_start_date AND p_end_date
    AND (p_branch_id IS NULL OR branch_id = p_branch_id);
    
    SELECT COALESCE(SUM(s.cost_price * asi.quantity), 0) INTO total_service_cost
    FROM `appointment_services` asi
    JOIN `services` s ON s.id = asi.service_id
    JOIN `appointments` a ON a.id = asi.appointment_id
    WHERE DATE(a.start_datetime) BETWEEN p_start_date AND p_end_date
    AND (p_branch_id IS NULL OR a.branch_id = p_branch_id);
    
    SELECT COALESCE(SUM(p.cost_price * ii.quantity), 0) INTO total_product_cost
    FROM `invoice_items` ii
    JOIN `products` p ON p.id = ii.reference_id
    JOIN `invoices` i ON i.id = ii.invoice_id
    WHERE ii.reference_type = 'product'
    AND DATE(i.created_at) BETWEEN p_start_date AND p_end_date
    AND (p_branch_id IS NULL OR i.branch_id = p_branch_id);
    
    SET gross_profit = total_income - (total_service_cost + total_product_cost);
    SET net_profit = gross_profit - total_expenses;
    
    SELECT
        total_income,
        total_expenses,
        total_service_cost,
        total_product_cost,
        gross_profit,
        net_profit;
END$$

-- Generate invoice number
CREATE PROCEDURE `sp_generate_invoice_number`(OUT p_invoice_no VARCHAR(50))
BEGIN
    DECLARE v_year VARCHAR(4);
    DECLARE v_month VARCHAR(2);
    DECLARE v_count INT;
    
    SET v_year = DATE_FORMAT(NOW(), '%Y');
    SET v_month = DATE_FORMAT(NOW(), '%m');
    
    SELECT COUNT(*) + 1 INTO v_count
    FROM `invoices`
    WHERE `invoice_number` LIKE CONCAT('INV-', v_year, v_month, '%');
    
    SET p_invoice_no = CONCAT('INV-', v_year, v_month, LPAD(v_count, 4, '0'));
END$$

-- Get low stock products
CREATE PROCEDURE `sp_low_stock_products`(IN p_branch_id INT UNSIGNED)
BEGIN
    SELECT
        p.id,
        p.name,
        p.sku,
        p.current_stock,
        p.reorder_level,
        p.min_stock,
        p.unit_price,
        p.cost_price,
        pc.name AS category_name,
        s.name AS supplier_name
    FROM `products` p
    LEFT JOIN `product_categories` pc ON pc.id = p.category_id
    LEFT JOIN `suppliers` s ON s.id = p.supplier_id
    WHERE p.current_stock <= p.reorder_level
    AND p.track_inventory = 1
    AND p.is_active = 1
    AND (p_branch_id IS NULL OR p.branch_id = p_branch_id)
    ORDER BY p.current_stock ASC;
END$$

-- Get expiring products
CREATE PROCEDURE `sp_expiring_products`(IN p_days INT, IN p_branch_id INT UNSIGNED)
BEGIN
    SELECT
        p.id,
        p.name,
        p.sku,
        pb.batch_number,
        pb.expiry_date,
        pb.quantity,
        p.unit_price,
        p.cost_price
    FROM `product_batches` pb
    JOIN `products` p ON p.id = pb.product_id
    WHERE pb.expiry_date IS NOT NULL
    AND pb.expiry_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL p_days DAY)
    AND pb.quantity > 0
    AND (p_branch_id IS NULL OR p.branch_id = p_branch_id)
    ORDER BY pb.expiry_date ASC;
END$$

DELIMITER ;
