BEGIN
    SELECT b.branch_name, IFNULL(SUM(cp.total_quantity), 0) AS branch_inventory
    FROM branch_tbl b
    LEFT JOIN client_info_tbl c ON c.branch_id = b.branch_id
    LEFT JOIN client_products cp ON cp.client_id = c.client_id
    GROUP BY b.branch_id, b.branch_name;
END

IN `threshold` DOUBLE

BEGIN
    SELECT p.product_id, p.name, cp.total_quantity
    FROM products p
    JOIN client_products cp ON p.product_id = cp.product_id
    WHERE cp.total_quantity < threshold;
END

BEGIN
    SELECT 
        cp.product_id,
        p.name AS product_name,
        SUM(cp.total_quantity) AS total_quantity
    FROM client_products cp
    JOIN products p 
        ON cp.product_id = p.product_id
    GROUP BY cp.product_id, p.name
    ORDER BY total_quantity DESC;
END

IN `sal_id` INT

BEGIN
    SELECT client_info_tbl.client_name, client_info_tbl.client_id
    FROM client_info_tbl
    JOIN client_saleman_rel_tbl ON client_info_tbl.client_id = client_saleman_rel_tbl.client_id
    WHERE client_saleman_rel_tbl.salesman_id = sal_id;
END

InputValue INT, KeyValue INT

int(11)

BEGIN
    RETURN InputValue ^ KeyValue;
END

IN `p_year` INT, IN `p_month` INT, IN `p_salesman_id` INT

BEGIN
    DECLARE period_start DATE;
    DECLARE period_end DATE;

    -- Calculate start and end dates of the period
    SET period_start = STR_TO_DATE(CONCAT(p_year, '-', p_month, '-01'), '%Y-%m-%d');
    SET period_end = LAST_DAY(period_start);

    -- Fetch only data related to the given salesman
    SELECT
        cp.created_at AS `تاريخ الجرد`,
        cp.updated_at AS `تاريخ التحديث`, 
        c.client_id AS `رقم العميل`, 
        c.client_name AS `اسم العميل`, 
        s.id AS `رقم المندوب`, 
        s.name AS `اسم المندوب`,
        s.last_online_login AS `تاريخ أخر تسجيل دخول`,
        b.branch_name AS `اسم الفرع`,
        p.product_id AS `رقم الصنف`, 
        p.name AS `الصنف`, 
        pq.quantity AS `الكمية المدخلة`, 
        CASE 
            WHEN p.name LIKE '%أرز%' THEN NULL 
            ELSE pq.expiry_date 
        END AS `تاريخ الانتهاء`, 
        pst.pro_sub_type AS `النوع`, 
        DATEDIFF(period_end, period_start) AS `فترة الجرد`
    FROM client_products cp
    JOIN client_info_tbl c ON cp.client_id = c.client_id
    JOIN salesmen s ON cp.salesman_id = s.id
    JOIN branch_tbl b ON s.branch_id = b.branch_id
    JOIN products p ON cp.product_id = p.product_id
    JOIN product_sub_type_tbl pst ON p.sub_type_id = pst.pro_sub_type_id
    JOIN product_quantities pq ON pq.client_product_id = cp.id
    JOIN product_unit_info_tbl pu ON pu.product_id = cp.product_id 
    JOIN product_unit_tbl u ON pu.unit_id = u.unit_id 
    WHERE pq.updated_at BETWEEN period_start AND period_end
      AND s.id = p_salesman_id -- ✅ Fetch data for only the specified salesman
      AND s.name NOT LIKE '%موقفون%'  -- Exclude salesmen with "موقفون" in their name
    ORDER BY b.branch_name, c.client_id, p.product_id, pq.expiry_date;

END

IN `input_username` VARCHAR(255), IN `input_password` VARCHAR(255)

BEGIN
    SELECT salesman_id
    FROM salesmen_login
    WHERE sale_user = input_username AND sale_pass = input_password;
END

IN `input_year` INT, IN `input_month` INT, IN `input_salesman_id` INT

BEGIN
    -- Select only the requested salesman's inventory percentage
    SELECT 
        (SELECT COUNT(DISTINCT cp.client_id) 
         FROM client_products cp
         WHERE cp.salesman_id = input_salesman_id
         AND YEAR(cp.created_at) = input_year
         AND MONTH(cp.created_at) = input_month) * 100.0 /
        NULLIF(
            (SELECT COUNT(DISTINCT cs.client_id) 
             FROM client_salesmen cs
             WHERE cs.salesman_id = input_salesman_id),
            0
        ) AS `نسبة الجرد`;
END

BEGIN
    SELECT 
        DATE_FORMAT(created_at, '%M') AS month, 
        SUM(total_quantity) AS inventory_total
    FROM client_products
    GROUP BY month 
    ORDER BY MONTH(created_at);
END

InputValue INT, KeyValue INT

int(11)

BEGIN
    RETURN InputValue ^ KeyValue;
END

    IN quarter_param VARCHAR(2),  -- Can be 'Q1', 'Q2', 'Q3', 'Q4', or 'ALL'
    IN year_param INT             -- The year to filter by

BEGIN
    -- Create temporary table to hold results
    DROP TEMPORARY TABLE IF EXISTS temp_results;
    CREATE TEMPORARY TABLE temp_results (
        Quarter VARCHAR(10),
        FirstInterview_Total INT,
        FirstInterview_Passed INT,
        FirstInterview_Failed INT,
        FirstInterview_Pending INT,
        FinalInterview_Total INT,
        FinalInterview_Passed INT,
        FinalInterview_Failed INT,
        ConversionRate_Percentage DECIMAL(5,2)
    );
    
    -- Q1 Data (if requested or ALL)
    IF quarter_param IN ('Q1', 'ALL') THEN
        INSERT INTO temp_results
        SELECT 
            CONCAT('Q1-', year_param) AS Quarter,
            (SELECT COUNT(*) FROM applicants 
             WHERE MONTH(FirstInterviewDate) BETWEEN 1 AND 3 
             AND YEAR(FirstInterviewDate) = year_param) AS FirstInterview_Total,
            (SELECT COUNT(*) FROM applicants 
             WHERE FirstInterviewResult = 2 
             AND MONTH(FirstInterviewDate) BETWEEN 1 AND 3
             AND YEAR(FirstInterviewDate) = year_param) AS FirstInterview_Passed,
            (SELECT COUNT(*) FROM applicants 
             WHERE FirstInterviewResult = 1 
             AND MONTH(FirstInterviewDate) BETWEEN 1 AND 3
             AND YEAR(FirstInterviewDate) = year_param) AS FirstInterview_Failed,
            (SELECT COUNT(*) FROM applicants 
             WHERE FirstInterviewResult = 3 
             AND MONTH(FirstInterviewDate) BETWEEN 1 AND 3
             AND YEAR(FirstInterviewDate) = year_param) AS FirstInterview_Pending,
            (SELECT COUNT(*) FROM applicants 
             WHERE FinalInterviewResult IN (1, 2) 
             AND MONTH(FinalInterviewDate) BETWEEN 1 AND 3
             AND YEAR(FinalInterviewDate) = year_param) AS FinalInterview_Total,
            (SELECT COUNT(*) FROM applicants 
             WHERE FinalInterviewResult = 2 
             AND MONTH(FinalInterviewDate) BETWEEN 1 AND 3
             AND YEAR(FinalInterviewDate) = year_param) AS FinalInterview_Passed,
            (SELECT COUNT(*) FROM applicants 
             WHERE FinalInterviewResult = 1 
             AND MONTH(FinalInterviewDate) BETWEEN 1 AND 3
             AND YEAR(FinalInterviewDate) = year_param) AS FinalInterview_Failed,
            (SELECT ROUND(
                (SELECT COUNT(*) FROM applicants 
                 WHERE FinalInterviewResult = 2 
                 AND MONTH(FinalInterviewDate) BETWEEN 1 AND 3
                 AND YEAR(FinalInterviewDate) = year_param) / 
                NULLIF((SELECT COUNT(*) FROM applicants 
                       WHERE MONTH(FirstInterviewDate) BETWEEN 1 AND 3
                       AND YEAR(FirstInterviewDate) = year_param), 0) * 100, 2
            )) AS ConversionRate_Percentage;
    END IF;
    
    -- Q2 Data (if requested or ALL)
    IF quarter_param IN ('Q2', 'ALL') THEN
        INSERT INTO temp_results
        SELECT 
            CONCAT('Q2-', year_param) AS Quarter,
            (SELECT COUNT(*) FROM applicants 
             WHERE MONTH(FirstInterviewDate) BETWEEN 4 AND 6 
             AND YEAR(FirstInterviewDate) = year_param) AS FirstInterview_Total,
            (SELECT COUNT(*) FROM applicants 
             WHERE FirstInterviewResult = 2 
             AND MONTH(FirstInterviewDate) BETWEEN 4 AND 6
             AND YEAR(FirstInterviewDate) = year_param) AS FirstInterview_Passed,
            (SELECT COUNT(*) FROM applicants 
             WHERE FirstInterviewResult = 1 
             AND MONTH(FirstInterviewDate) BETWEEN 4 AND 6
             AND YEAR(FirstInterviewDate) = year_param) AS FirstInterview_Failed,
            (SELECT COUNT(*) FROM applicants 
             WHERE FirstInterviewResult = 3 
             AND MONTH(FirstInterviewDate) BETWEEN 4 AND 6
             AND YEAR(FirstInterviewDate) = year_param) AS FirstInterview_Pending,
            (SELECT COUNT(*) FROM applicants 
             WHERE FinalInterviewResult IN (1, 2) 
             AND MONTH(FinalInterviewDate) BETWEEN 4 AND 6
             AND YEAR(FinalInterviewDate) = year_param) AS FinalInterview_Total,
            (SELECT COUNT(*) FROM applicants 
             WHERE FinalInterviewResult = 2 
             AND MONTH(FinalInterviewDate) BETWEEN 4 AND 6
             AND YEAR(FinalInterviewDate) = year_param) AS FinalInterview_Passed,
            (SELECT COUNT(*) FROM applicants 
             WHERE FinalInterviewResult = 1 
             AND MONTH(FinalInterviewDate) BETWEEN 4 AND 6
             AND YEAR(FinalInterviewDate) = year_param) AS FinalInterview_Failed,
            (SELECT ROUND(
                (SELECT COUNT(*) FROM applicants 
                 WHERE FinalInterviewResult = 2 
                 AND MONTH(FinalInterviewDate) BETWEEN 4 AND 6
                 AND YEAR(FinalInterviewDate) = year_param) / 
                NULLIF((SELECT COUNT(*) FROM applicants 
                       WHERE MONTH(FirstInterviewDate) BETWEEN 4 AND 6
                       AND YEAR(FirstInterviewDate) = year_param), 0) * 100, 2
            )) AS ConversionRate_Percentage;
    END IF;
    
    -- Q3 Data (if requested or ALL)
    IF quarter_param IN ('Q3', 'ALL') THEN
        INSERT INTO temp_results
        SELECT 
            CONCAT('Q3-', year_param) AS Quarter,
            (SELECT COUNT(*) FROM applicants 
             WHERE MONTH(FirstInterviewDate) BETWEEN 7 AND 9 
             AND YEAR(FirstInterviewDate) = year_param) AS FirstInterview_Total,
            (SELECT COUNT(*) FROM applicants 
             WHERE FirstInterviewResult = 2 
             AND MONTH(FirstInterviewDate) BETWEEN 7 AND 9
             AND YEAR(FirstInterviewDate) = year_param) AS FirstInterview_Passed,
            (SELECT COUNT(*) FROM applicants 
             WHERE FirstInterviewResult = 1 
             AND MONTH(FirstInterviewDate) BETWEEN 7 AND 9
             AND YEAR(FirstInterviewDate) = year_param) AS FirstInterview_Failed,
            (SELECT COUNT(*) FROM applicants 
             WHERE FirstInterviewResult = 3 
             AND MONTH(FirstInterviewDate) BETWEEN 7 AND 9
             AND YEAR(FirstInterviewDate) = year_param) AS FirstInterview_Pending,
            (SELECT COUNT(*) FROM applicants 
             WHERE FinalInterviewResult IN (1, 2) 
             AND MONTH(FinalInterviewDate) BETWEEN 7 AND 9
             AND YEAR(FinalInterviewDate) = year_param) AS FinalInterview_Total,
            (SELECT COUNT(*) FROM applicants 
             WHERE FinalInterviewResult = 2 
             AND MONTH(FinalInterviewDate) BETWEEN 7 AND 9
             AND YEAR(FinalInterviewDate) = year_param) AS FinalInterview_Passed,
            (SELECT COUNT(*) FROM applicants 
             WHERE FinalInterviewResult = 1 
             AND MONTH(FinalInterviewDate) BETWEEN 7 AND 9
             AND YEAR(FinalInterviewDate) = year_param) AS FinalInterview_Failed,
            (SELECT ROUND(
                (SELECT COUNT(*) FROM applicants 
                 WHERE FinalInterviewResult = 2 
                 AND MONTH(FinalInterviewDate) BETWEEN 7 AND 9
                 AND YEAR(FinalInterviewDate) = year_param) / 
                NULLIF((SELECT COUNT(*) FROM applicants 
                       WHERE MONTH(FirstInterviewDate) BETWEEN 7 AND 9
                       AND YEAR(FirstInterviewDate) = year_param), 0) * 100, 2
            )) AS ConversionRate_Percentage;
    END IF;
    
    -- Q4 Data (if requested or ALL)
    IF quarter_param IN ('Q4', 'ALL') THEN
        INSERT INTO temp_results
        SELECT 
            CONCAT('Q4-', year_param) AS Quarter,
            (SELECT COUNT(*) FROM applicants 
             WHERE MONTH(FirstInterviewDate) BETWEEN 10 AND 12 
             AND YEAR(FirstInterviewDate) = year_param) AS FirstInterview_Total,
            (SELECT COUNT(*) FROM applicants 
             WHERE FirstInterviewResult = 2 
             AND MONTH(FirstInterviewDate) BETWEEN 10 AND 12
             AND YEAR(FirstInterviewDate) = year_param) AS FirstInterview_Passed,
            (SELECT COUNT(*) FROM applicants 
             WHERE FirstInterviewResult = 1 
             AND MONTH(FirstInterviewDate) BETWEEN 10 AND 12
             AND YEAR(FirstInterviewDate) = year_param) AS FirstInterview_Failed,
            (SELECT COUNT(*) FROM applicants 
             WHERE FirstInterviewResult = 3 
             AND MONTH(FirstInterviewDate) BETWEEN 10 AND 12
             AND YEAR(FirstInterviewDate) = year_param) AS FirstInterview_Pending,
            (SELECT COUNT(*) FROM applicants 
             WHERE FinalInterviewResult IN (1, 2) 
             AND MONTH(FinalInterviewDate) BETWEEN 10 AND 12
             AND YEAR(FinalInterviewDate) = year_param) AS FinalInterview_Total,
            (SELECT COUNT(*) FROM applicants 
             WHERE FinalInterviewResult = 2 
             AND MONTH(FinalInterviewDate) BETWEEN 10 AND 12
             AND YEAR(FinalInterviewDate) = year_param) AS FinalInterview_Passed,
            (SELECT COUNT(*) FROM applicants 
             WHERE FinalInterviewResult = 1 
             AND MONTH(FinalInterviewDate) BETWEEN 10 AND 12
             AND YEAR(FinalInterviewDate) = year_param) AS FinalInterview_Failed,
            (SELECT ROUND(
                (SELECT COUNT(*) FROM applicants 
                 WHERE FinalInterviewResult = 2 
                 AND MONTH(FinalInterviewDate) BETWEEN 10 AND 12
                 AND YEAR(FinalInterviewDate) = year_param) / 
                NULLIF((SELECT COUNT(*) FROM applicants 
                       WHERE MONTH(FirstInterviewDate) BETWEEN 10 AND 12
                       AND YEAR(FirstInterviewDate) = year_param), 0) * 100, 2
            )) AS ConversionRate_Percentage;
    END IF;
    
    -- Return the results
    SELECT * FROM temp_results;
    
    -- Clean up
    DROP TEMPORARY TABLE IF EXISTS temp_results;
END

IN `p_salesman_id` INT

BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE v_id INT;
  DECLARE v_client_id INT;
  DECLARE v_product_id VARCHAR(255);
  DECLARE v_salesman_id INT;
  DECLARE v_total_quantity DECIMAL(10,2);
  DECLARE v_json LONGTEXT;
  DECLARE new_client_product_id INT;

  -- Cursor to fetch rows for specific salesman
  DECLARE cur CURSOR FOR
    SELECT id, client_id, product_id, salesman_id, total_quantity, quantities_with_expiry
    FROM prevented_client_products
    WHERE salesman_id = p_salesman_id;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;

  read_loop: LOOP
    FETCH cur INTO v_id, v_client_id, v_product_id, v_salesman_id, v_total_quantity, v_json;
    IF done THEN
      LEAVE read_loop;
    END IF;

    -- Insert into client_products
    INSERT INTO client_products (
      client_id, salesman_id, product_id, total_quantity, expiration_date, created_at, updated_at
    )
    VALUES (
      v_client_id, v_salesman_id, v_product_id, v_total_quantity, NULL, NOW(), NOW()
    );

    SET new_client_product_id = LAST_INSERT_ID();

    -- Handle JSON quantities
    SET @json_data = v_json;
    SET @i = 0;
    WHILE JSON_LENGTH(@json_data) > @i DO
      SET @qty = JSON_UNQUOTE(JSON_EXTRACT(@json_data, CONCAT('$[', @i, '].quantity')));
      SET @expiry = JSON_UNQUOTE(JSON_EXTRACT(@json_data, CONCAT('$[', @i, '].expiry_date')));

      INSERT INTO product_quantities (
        client_product_id, quantity, expiry_date, created_at, updated_at
      )
      VALUES (
        new_client_product_id, @qty, STR_TO_DATE(@expiry, '%Y-%m-%d'), NOW(), NOW()
      );

      SET @i = @i + 1;
    END WHILE;

    -- Delete from source table
    DELETE FROM prevented_client_products WHERE id = v_id;
  END LOOP;

  CLOSE cur;
END

    IN from_date DATE,
    IN to_date DATE

BEGIN
    -- Return count of employees hired within the specified date range
    SELECT 
        CONCAT('From ', from_date, ' To ', to_date) AS DateRange,
        COUNT(*) AS EmployeeCount
    FROM employees_on_probation
    WHERE EmploymentDate BETWEEN from_date AND to_date;
END

	IN `warehouse_param` INT

BEGIN
    -- 🟢 Fetch system quantity from block_inventory for the given warehouse
    SELECT 
        bi.product_id, 
        p.name AS product_name, 
        p.helper_name, 
        p.continuity,
        bi.warehouse_id, 
        bi.quantity AS system_quantity,
        0 AS actual_quantity,  -- 🟢 Always default to 0 (calculated in Flutter)
        -bi.quantity AS difference  -- 🟢 Difference calculated locally
    FROM block_inventory bi
    JOIN products p ON p.product_id = bi.product_id
    WHERE bi.warehouse_id = warehouse_param
    ORDER BY bi.warehouse_id, p.product_id;
END

BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE v_id INT;
  DECLARE v_client_id INT;
  DECLARE v_product_id VARCHAR(255);
  DECLARE v_salesman_id INT;
  DECLARE v_total_quantity DECIMAL(10,2);
  DECLARE v_json LONGTEXT;

  -- Cursor to loop through prevented data
  DECLARE cur CURSOR FOR
    SELECT id, client_id, product_id, salesman_id, total_quantity, quantities_with_expiry
    FROM prevented_client_products;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;

  read_loop: LOOP
    FETCH cur INTO v_id, v_client_id, v_product_id, v_salesman_id, v_total_quantity, v_json;
    IF done THEN
      LEAVE read_loop;
    END IF;

    -- Insert into client_products
    INSERT INTO client_products (
      client_id, salesman_id, product_id, total_quantity, expiration_date, created_at, updated_at
    )
    VALUES (
      v_client_id, v_salesman_id, v_product_id, v_total_quantity, NULL, NOW(), NOW()
    );

    SET @client_product_id = LAST_INSERT_ID();

    -- Extract and insert each quantity with expiry from JSON
    SET @json_data = v_json;
    SET @i = 0;
    WHILE JSON_LENGTH(@json_data) > @i DO
      SET @qty = JSON_UNQUOTE(JSON_EXTRACT(@json_data, CONCAT('$[', @i, '].quantity')));
      SET @expiry = JSON_UNQUOTE(JSON_EXTRACT(@json_data, CONCAT('$[', @i, '].expiry_date')));

      INSERT INTO product_quantities (
        client_product_id, quantity, expiry_date, created_at, updated_at
      )
      VALUES (
        @client_product_id, @qty, STR_TO_DATE(@expiry, '%Y-%m-%d'), NOW(), NOW()
      );

      SET @i = @i + 1;
    END WHILE;

  END LOOP;

  CLOSE cur;
END

IN `beneficiary_ID_one` INT

BEGIN
    SELECT DISTINCT 
        beneficiaries.beneficiary_ID AS 'رقم المستفيد',
        XOREncrypt(`beneficiaries`.`card_charity`, 734214160) AS 'رقم الكرت المشفر',  -- XOR encryption function
        beneficiaries.card_charity AS 'رقم الكرت',
        beneficiaries.beneficiary_name AS 'اسم المستفيد',
        beneficiaries.recipient_name AS 'اسم المستلم',
        merchants.merchant_address AS 'العنوان'
    FROM beneficiaries
    INNER JOIN merchants ON beneficiaries.merchant_id = merchants.merchant_id
    WHERE beneficiaries.beneficiary_ID = beneficiary_ID_one;
END

	IN `p_from_date` DATE,
	IN `p_to_date` DATE

BEGIN
    DECLARE period_start DATE;
    DECLARE period_end DATE;

    -- استخدام التواريخ المدخلة
    SET period_start = p_from_date;
    SET period_end = p_to_date;

    -- حذف الجدول المؤقت لتجنب التكرار
    DROP TEMPORARY TABLE IF EXISTS client_product_percentage;

    -- إنشاء الجدول المؤقت
    CREATE TEMPORARY TABLE client_product_percentage (
        salesman_id INT,
        salesman_name VARCHAR(255),
        branch_id INT,
        branch_name VARCHAR(255),
        total_clients_related INT,
        clients_managed_by_salesman INT,
        final_percentage DECIMAL(10,2)
    );

    -- إدخال البيانات
    INSERT INTO client_product_percentage (
        salesman_id, 
        salesman_name, 
        branch_id, 
        branch_name, 
        total_clients_related, 
        clients_managed_by_salesman, 
        final_percentage
    )
    SELECT 
        s.id,
        s.name,
        b.branch_id,
        b.branch_name,

        -- إجمالي العملاء المرتبطين
        (SELECT COUNT(DISTINCT cs.client_id) 
         FROM client_salesmen cs
         WHERE cs.salesman_id = s.id),

        -- العملاء المجرودين خلال الفترة
        (SELECT COUNT(DISTINCT cp.client_id) 
         FROM client_products cp
         WHERE cp.salesman_id = s.id
         AND cp.created_at >= period_start
         AND cp.created_at < DATE_ADD(period_end, INTERVAL 1 DAY)),

        -- نسبة الجرد
        (SELECT 
            (COUNT(DISTINCT cp.client_id) * 100.0) /
            NULLIF(
                (SELECT COUNT(DISTINCT cs.client_id) 
                 FROM client_salesmen cs
                 WHERE cs.salesman_id = s.id),
                0
            )
         FROM client_products cp
         WHERE cp.salesman_id = s.id
         AND cp.created_at >= period_start
         AND cp.created_at < DATE_ADD(period_end, INTERVAL 1 DAY)
        )

    FROM 
        salesmen s
    JOIN branch_tbl b ON s.branch_id = b.branch_id
    WHERE s.is_active = 'نعم';

    -- عرض النتائج
    SELECT 
        salesman_id AS `رقم المندوب`,
        salesman_name AS `اسم المندوب`,
        branch_id AS `رقم الفرع`,
        branch_name AS `اسم الفرع`,
        total_clients_related AS `إجمالي العملاء`,
        clients_managed_by_salesman AS `إجمالي العملاء المجرودين`,
        final_percentage AS `نسبة الجرد`
    FROM client_product_percentage 
    WHERE 
        salesman_name NOT LIKE '%موقفون%' 
        AND salesman_name NOT LIKE '%حلويات%' 
        AND (
            salesman_name LIKE '%جملة%' 
            OR salesman_name LIKE '%سوبرات%' 
            OR salesman_name LIKE '%حلويات%' 
            OR salesman_name LIKE '%مطاعم%' 
            OR salesman_name LIKE '%مدير%'
            OR salesman_name LIKE '%كبار التجزئة%'
        );

    -- حذف الجدول المؤقت
    DROP TEMPORARY TABLE IF EXISTS client_product_percentage;

END

IN `p_start_date` DATETIME, IN `p_end_date` DATETIME

BEGIN
    SELECT 
        cn.id AS `رقم الملاحظة`,
        cn.client_id AS `رقم العميل`,
        c.client_name AS `اسم العميل`,
        cn.salesman_id AS `رقم المندوب`,
        s.name AS `اسم المندوب`,
        cn.note AS `الملاحظة`,
        cn.created_at AS `تاريخ الإنشاء`
    FROM client_notes cn
    JOIN client_info_tbl c 
        ON cn.client_id = c.client_id
    JOIN salesmen s
        ON cn.salesman_id = s.id
    WHERE cn.created_at BETWEEN p_start_date AND p_end_date
    ORDER BY cn.created_at DESC;
END

IN `p_start_date` DATETIME, IN `p_end_date` DATETIME

BEGIN
    SELECT 
        cp.product_id AS `رقم المنتج`,
        p.name AS `اسم المنتج`,
        SUM(cp.total_quantity) AS `اجمالي الكمية`
    FROM client_products cp
    JOIN products p 
        ON cp.product_id = p.product_id
    -- Filter by date range (using cp.created_at as an example)
    WHERE cp.created_at BETWEEN p_start_date AND p_end_date
    GROUP BY cp.product_id, p.name
    ORDER BY total_quantity DESC;
END

IN `p_offset` INT, IN `p_limit` INT, OUT `total_rows` INT

BEGIN
    SELECT COUNT(*) INTO total_rows 
    FROM salesmen;

    SELECT 
        s.id,
        s.username,
        s.name,
        s.password_hash,
        s.device_id,
        s.last_online_login,
        s.created_at,
        s.sal_sub_type_id,
        st.sal_subtype_name,
        st.activity AS subtype_activity,
        s.sal_type_id,
        mt.saleman_type_name,
        s.branch_id,
        s.branch_area,
        s.is_active
    FROM salesmen AS s
    LEFT JOIN salman_subtype_tbl AS st 
        ON s.sal_sub_type_id = st.sal_sub_type_id
    LEFT JOIN salman_type_tbl AS mt
        ON s.sal_type_id = mt.saleman_main_type_id
    LIMIT p_offset, p_limit;
END

IN `p_start_date` DATE, IN `p_end_date` DATE

BEGIN
    SELECT
        cp.created_at AS `تاريخ الجرد`,
        cp.updated_at AS `تاريخ التحديث`, 
        c.client_id AS `رقم العميل`, 
        c.client_name AS `اسم العميل`, 
        s.id AS `رقم المندوب`, 
        s.name AS `اسم المندوب`,
        s.last_online_login AS `تاريخ أخر تسجيل دخول`,
        b.branch_name AS `اسم الفرع`,
        p.product_id AS `رقم الصنف`, 
        p.name AS `الصنف`, 
        u.unit_name AS `الوحدة`, 
        p.unit_id AS `رقم الوحدة`,
        pq.quantity AS `الكمية المدخلة`,
        pq.expiry_date AS `تاريخ الانتهاء`, 
        pst.pro_sub_type AS `النوع`
    FROM client_products cp
    JOIN client_info_tbl c ON cp.client_id = c.client_id
    JOIN salesmen s ON cp.salesman_id = s.id
    JOIN branch_tbl b ON s.branch_id = b.branch_id
    JOIN products p ON cp.product_id = p.product_id
    JOIN product_sub_type_tbl pst ON p.sub_type_id = pst.pro_sub_type_id
    JOIN product_quantities pq ON pq.client_product_id = cp.id
    JOIN product_unit_info_tbl pu ON pu.product_id = cp.product_id 
    JOIN product_unit_tbl u ON pu.unit_id = u.unit_id 
    WHERE pq.updated_at BETWEEN p_start_date AND p_end_date
    ORDER BY b.branch_name, c.client_id, p.product_id, pq.expiry_date;
END

IN `p_year` INT, IN `p_month` INT

BEGIN
    DECLARE period_start DATE;
    DECLARE period_end DATE;

    -- Calculate the first day of the specified month
    SET period_start = STR_TO_DATE(CONCAT(p_year, '-', p_month, '-01'), '%Y-%m-%d');

    -- Calculate the last day of the specified month
    SET period_end = LAST_DAY(period_start);

    SELECT
        cp.updated_at AS `تاريخ الجرد`,                    -- Inventory Date
        c.client_id AS `رقم العميل`,                       -- Client Number
        c.client_name AS `اسم العميل`,                     -- Client Name
        s.id AS `رقم المندوب`,                             -- Salesman Number
        s.name AS `اسم المندوب`,                           -- Salesman Name
        p.product_id AS `رقم الصنف`,                       -- Product Number
        p.name AS `الصنف`,                                 -- Product
        u.unit_name AS `الوحدة`,                           -- Unit
        pq.quantity AS `الكمية`,                             -- Quantity (From product_quantities)
        pq.expiry_date AS `تاريخ الانتهاء`,                -- Expiration Date (From product_quantities)
        pst.pro_sub_type AS `النوع`,                        -- Type
        p.helper_name AS `المميز`,                           -- Helper Name
        DATEDIFF(period_end, period_start) AS `فترة الجرد`  -- Inventory Period
    FROM client_products cp
    JOIN client_info_tbl c ON cp.client_id = c.client_id
    JOIN salesmen s ON cp.salesman_id = s.id
    JOIN products p ON cp.product_id = p.product_id
    JOIN product_sub_type_tbl pst ON p.sub_type_id = pst.pro_sub_type_id
    JOIN product_unit_info_tbl pu ON p.product_id = pu.product_id
    JOIN product_unit_tbl u ON pu.unit_id = u.unit_id
    JOIN product_quantities pq ON pq.client_product_id = cp.id   -- Joining product_quantities
    WHERE
        pq.updated_at = (
            SELECT MAX(pq3.updated_at)
            FROM product_quantities pq3
            WHERE pq3.client_product_id = pq.client_product_id
              AND pq3.updated_at BETWEEN period_start AND period_end
        )
        AND pq.updated_at BETWEEN period_start AND period_end
    ORDER BY c.client_id, p.product_id, pq.expiry_date;

END

BEGIN
    SELECT 
         (SELECT COUNT(*) FROM client_info_tbl) AS total_clients,
         (SELECT COUNT(*) FROM products where continuity <> 'متوقف') AS total_products,
         (SELECT IFNULL(SUM(total_quantity),0) FROM client_products) AS total_inventory,
         (SELECT COUNT(*) 
             FROM client_products 
             WHERE total_quantity < 10) AS low_inventory_products;
END

IN `p_year` INT, IN `p_month` INT

BEGIN
    DECLARE period_start DATE;
    DECLARE period_end DATE;

    -- Calculate start and end dates of the period
    SET period_start = STR_TO_DATE(CONCAT(p_year, '-', p_month, '-01'), '%Y-%m-%d');
    SET period_end = LAST_DAY(period_start);

    SELECT
    	cp.created_at AS `تاريخ الجرد`,
        cp.updated_at AS `تاريخ التحديث`, 
        c.client_id AS `رقم العميل`, 
        c.client_name AS `اسم العميل`, 
        s.id AS `رقم المندوب`, 
        s.name AS `اسم المندوب`,
        s.last_online_login `تاريخ أخر تسجيل دخول`,
        b.branch_name AS `اسم الفرع`,
        p.product_id AS `رقم الصنف`, 
        p.name AS `الصنف`, 
        pq.quantity AS `الكمية المدخلة`, 
        CASE 
        		WHEN p.name LIKE '%أرز%' THEN NULL 
      		ELSE pq.expiry_date 
    		END AS `تاريخ الانتهاء`, 
        pst.pro_sub_type AS `النوع`, 
        DATEDIFF(period_end, period_start) AS `فترة الجرد`
    FROM client_products cp
    JOIN client_info_tbl c ON cp.client_id = c.client_id
    JOIN salesmen s ON cp.salesman_id = s.id
    JOIN branch_tbl b ON s.branch_id = b.branch_id
    JOIN products p ON cp.product_id = p.product_id
    JOIN product_sub_type_tbl pst ON p.sub_type_id = pst.pro_sub_type_id
    JOIN product_quantities pq ON pq.client_product_id = cp.id
    JOIN product_unit_info_tbl pu ON pu.product_id = cp.product_id 
    JOIN product_unit_tbl u ON pu.unit_id = u.unit_id 
    WHERE pq.updated_at BETWEEN period_start AND period_end
AND s.name NOT LIKE '%موقفون%'  -- Exclude salesmen whose name contains "موقفون" 
    ORDER BY b.branch_name, c.client_id, p.product_id, pq.expiry_date;

END

IN `p_month` VARCHAR(7)

BEGIN
    SELECT 
        p.product_id AS 'رقم المنتج',
        p.name AS 'اسم المنتج',
        SUM(cp.total_quantity) AS 'إجمالي الكمية'
    FROM client_products cp
    JOIN products p ON cp.product_id = p.product_id
    WHERE DATE_FORMAT(cp.created_at, '%Y-%m') = p_month
    GROUP BY p.product_id, p.name;
END