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 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 `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

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_load_id INT

BEGIN
    DECLARE total_bills INT;
    DECLARE bills_with_max_status INT;
    DECLARE max_status INT;
    DECLARE new_status INT;
    
    -- Get total distinct bills for this load
    SELECT COUNT(DISTINCT bill_id) INTO total_bills
    FROM delivery_tracking_tbl
    WHERE load_id = p_load_id;
    
    -- Get the maximum status ID for this load
    SELECT MAX(status_id) INTO max_status
    FROM delivery_tracking_tbl
    WHERE load_id = p_load_id;
    
    -- Count how many bills have the max status
    SELECT COUNT(DISTINCT bill_id) INTO bills_with_max_status
    FROM delivery_tracking_tbl
    WHERE load_id = p_load_id AND status_id = max_status;
    
    -- Determine the new status
    IF bills_with_max_status = total_bills THEN
        -- All bills have the same status (max_status)
        SET new_status = max_status;
    ELSE
        -- Not all bills have the same status, use (max_status - 1)
        SET new_status = max_status - 1;
        
        -- Ensure status doesn't go below minimum (assuming 1 is the lowest)
        IF new_status < 1 THEN
            SET new_status = 1;
        END IF;
    END IF;
    
    -- Update the status in bill_loads_tbl
    UPDATE bill_loads_tbl
    SET status_id = new_status
    WHERE load_id = p_load_id;
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

  IN in_year  INT,
  IN in_month INT

BEGIN
  SELECT
    s.id                   AS `رقم المندوب`,
    s.name                 AS `اسم المندوب`,
    c.client_id            AS `رقم العميل`,
    c.client_name          AS `اسم العميل`,
    COUNT(cp.id)           AS `عدد الصور`
  FROM
    branch_tbl           AS b
    JOIN salesmen        AS s  ON s.branch_id     = b.branch_id
    JOIN client_salesmen AS cs ON cs.salesman_id  = s.id
    JOIN client_info_tbl AS c  ON c.client_id     = cs.client_id
    LEFT JOIN client_photos AS cp
      ON cp.client_id = c.client_id
     AND YEAR(cp.created_at)  = in_year
     AND MONTH(cp.created_at) = in_month
  WHERE
    -- ثابت عند الإنشاء:
    b.branch_name = 'فرع عدن'
  GROUP BY
    s.id, s.name,
    c.client_id, c.client_name
  ORDER BY
    s.id,
    c.client_id;
END

    IN from_date DATE,
    IN to_date DATE

BEGIN
    SELECT 
        eop.`ApplicantID`, 
        eop.`EmployeeName`, 
        eop.`JobName`, 
        b.`branch_name`, 
        ecp.`status`,
        CONCAT('From ', from_date, ' To ', to_date) AS DateRange
    FROM 
        employees_on_probation eop
    LEFT JOIN 
        branch_tbl b ON eop.BranchID = b.branch_id
    LEFT JOIN 
        employees_completing_procedures ecp ON eop.ApplicantID = ecp.ApplicantID
    WHERE 
        eop.EmploymentDate BETWEEN from_date AND to_date;
END

BEGIN
  -- Set the path for the CSV file.
  -- Ensure 'C:/Shared/' is
  -- configured in your MySQL's 'secure_file_priv' variable
  -- and that the MySQL user has write permissions to this directory.
  SET @path = CONCAT(
    'C:/Shared/Inventory_',
    DATE_FORMAT(NOW(), '%Y%m%d_%H%i'),
    '.csv'
  );

  -- Construct the SQL query to export data to the CSV file
  SET @sql = CONCAT(
    "SELECT * FROM inventory_two_months_view ",
    "INTO OUTFILE '", @path, "' ",
    "FIELDS TERMINATED BY ',' ",
    "OPTIONALLY ENCLOSED BY '\"' ",
    "ESCAPED BY '' ",
    "LINES TERMINATED BY '\r\n'"
  );

  -- Prepare, execute, and deallocate the dynamic SQL statement
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END

BEGIN
    -- Truncate the summary table
    TRUNCATE TABLE inventory_summary;

    -- Insert data from the view logic
    INSERT INTO inventory_summary (
        `تاريخ الجرد`,
        `تاريخ التحديث`,
        `رقم العميل`,
        `اسم العميل`,
        `رقم المندوب`,
        `اسم المندوب`,
        `اسم الفرع`,
        `رقم الصنف`,
        `الصنف`,
        `الوحدة`,
        `الكمية المدخلة`,
        `تاريخ الانتهاء`,
        `النوع`,
        `فترة الجرد`
    )
    SELECT 
        cp.created_at AS `تاريخ الجرد`,
        cp.updated_at AS `تاريخ التحديث`,
        c.client_id AS `رقم العميل`,
        c.client_name AS `اسم العميل`,
        s.id AS `رقم المندوب`,
        s.name AS `اسم المندوب`,
        b.branch_name AS `اسم الفرع`,
        p.product_id AS `رقم الصنف`,
        p.name AS `الصنف`,
        u.unit_name AS `الوحدة`,
        pq.quantity AS `الكمية المدخلة`,
        CASE 
            WHEN p.name LIKE '%ارز%' THEN NULL 
            ELSE pq.expiry_date 
        END AS `تاريخ الانتهاء`,
        pst.pro_sub_type AS `النوع`,
        TO_DAYS(LAST_DAY(CURDATE())) - TO_DAYS(DATE_FORMAT(CURDATE(),'%Y-%m-01')) AS `فترة الجرد`
    FROM 
        inventory_management.client_products cp
    JOIN inventory_management.client_info_tbl c ON cp.client_id = c.client_id
    JOIN inventory_management.salesmen s ON cp.salesman_id = s.id
    JOIN inventory_management.branch_tbl b ON s.branch_id = b.branch_id
    JOIN inventory_management.products p ON cp.product_id = p.product_id
    JOIN inventory_management.product_sub_type_tbl pst ON p.sub_type_id = pst.pro_sub_type_id
    JOIN inventory_management.product_quantities pq ON pq.client_product_id = cp.id
    JOIN inventory_management.product_unit_info_tbl pu 
        ON pu.product_id = cp.product_id AND pu.unit_id = p.unit_id
    JOIN inventory_management.product_unit_tbl u ON pu.unit_id = u.unit_id
    WHERE 
        pq.updated_at BETWEEN DATE_FORMAT(CURDATE(),'%Y-%m-01') AND LAST_DAY(CURDATE())
        AND s.name NOT LIKE '%موقفون%'
    ORDER BY 
        b.branch_name, c.client_id, p.product_id, pq.expiry_date;
END

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

BEGIN
    IF quarter_param = 'ALL' THEN
        -- Count for all quarters of the specified year
        SELECT 
            CONCAT('Q1-', year_param) AS Quarter,
            COUNT(*) AS EmployeeCount
        FROM employees_on_probation
        WHERE MONTH(EmploymentDate) BETWEEN 1 AND 3
        AND YEAR(EmploymentDate) = year_param
        
        UNION ALL
        
        SELECT 
            CONCAT('Q2-', year_param) AS Quarter,
            COUNT(*) AS EmployeeCount
        FROM employees_on_probation
        WHERE MONTH(EmploymentDate) BETWEEN 4 AND 6
        AND YEAR(EmploymentDate) = year_param
        
        UNION ALL
        
        SELECT 
            CONCAT('Q3-', year_param) AS Quarter,
            COUNT(*) AS EmployeeCount
        FROM employees_on_probation
        WHERE MONTH(EmploymentDate) BETWEEN 7 AND 9
        AND YEAR(EmploymentDate) = year_param
        
        UNION ALL
        
        SELECT 
            CONCAT('Q4-', year_param) AS Quarter,
            COUNT(*) AS EmployeeCount
        FROM employees_on_probation
        WHERE MONTH(EmploymentDate) BETWEEN 10 AND 12
        AND YEAR(EmploymentDate) = year_param;
    ELSE
        -- Count for specific quarter
        SELECT 
            CONCAT(quarter_param, '-', year_param) AS Quarter,
            COUNT(*) AS EmployeeCount
        FROM employees_on_probation
        WHERE 
            CASE quarter_param
                WHEN 'Q1' THEN MONTH(EmploymentDate) BETWEEN 1 AND 3
                WHEN 'Q2' THEN MONTH(EmploymentDate) BETWEEN 4 AND 6
                WHEN 'Q3' THEN MONTH(EmploymentDate) BETWEEN 7 AND 9
                WHEN 'Q4' THEN MONTH(EmploymentDate) BETWEEN 10 AND 12
            END
        AND YEAR(EmploymentDate) = year_param;
    END IF;
END

BEGIN
    REPLACE INTO mv_customer_aging (party_id, party_name, outstanding_amount, bucket_30, bucket_60, bucket_90_plus)
    SELECT
        p.id,
        p.name,
        SUM(i.total_amount - i.paid_amount) AS outstanding,
        SUM(CASE WHEN DATEDIFF(CURDATE(), i.due_date) BETWEEN 1 AND 30 THEN (i.total_amount - i.paid_amount) ELSE 0 END),
        SUM(CASE WHEN DATEDIFF(CURDATE(), i.due_date) BETWEEN 31 AND 60 THEN (i.total_amount - i.paid_amount) ELSE 0 END),
        SUM(CASE WHEN DATEDIFF(CURDATE(), i.due_date) > 60 THEN (i.total_amount - i.paid_amount) ELSE 0 END)
    FROM parties p
    JOIN invoices i ON p.id = i.party_id
    WHERE p.party_type = 'customer'
      AND i.status IN ('issued', 'partially_paid', 'overdue')
      AND (i.total_amount - i.paid_amount) > 0
    GROUP BY p.id, p.name;

    REPLACE INTO mv_inventory_valuation (warehouse_id, product_id, variant_id, batch_id, quantity, unit_cost, total_value, costing_method)
    SELECT
        b.warehouse_id,
        b.product_id,
        b.variant_id,
        b.id,
        b.quantity,
        b.purchase_price,
        (b.quantity * b.purchase_price),
        b.costing_method
    FROM batches b
    WHERE b.quantity > 0;
END

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

BEGIN
    -- For specific quarter selection
    IF quarter_param != 'ALL' THEN
        SELECT 
            eop.`ApplicantID`, 
            eop.`EmployeeName`, 
            eop.`JobName`, 
            b.`branch_name`, 
            ecp.`status`,
            CONCAT(quarter_param, '-', year_param) AS Quarter
        FROM 
            employees_on_probation eop
        LEFT JOIN 
            branch_tbl b ON eop.BranchID = b.branch_id
        LEFT JOIN 
            employees_completing_procedures ecp ON eop.ApplicantID = ecp.ApplicantID
        WHERE 
            CASE quarter_param
                WHEN 'Q1' THEN MONTH(eop.EmploymentDate) BETWEEN 1 AND 3
                WHEN 'Q2' THEN MONTH(eop.EmploymentDate) BETWEEN 4 AND 6
                WHEN 'Q3' THEN MONTH(eop.EmploymentDate) BETWEEN 7 AND 9
                WHEN 'Q4' THEN MONTH(eop.EmploymentDate) BETWEEN 10 AND 12
            END
        AND YEAR(eop.EmploymentDate) = year_param;
    
    -- For all quarters
    ELSE
        -- Q1 Results
        SELECT 
            eop.`ApplicantID`, 
            eop.`EmployeeName`, 
            eop.`JobName`, 
            b.`branch_name`, 
            ecp.`status`,
            CONCAT('Q1', '-', year_param) AS Quarter
        FROM 
            employees_on_probation eop
        LEFT JOIN 
            branch_tbl b ON eop.BranchID = b.branch_id
        LEFT JOIN 
            employees_completing_procedures ecp ON eop.ApplicantID = ecp.ApplicantID
        WHERE 
            MONTH(eop.EmploymentDate) BETWEEN 1 AND 3
        AND YEAR(eop.EmploymentDate) = year_param
        
        UNION ALL
        
        -- Q2 Results
        SELECT 
            eop.`ApplicantID`, 
            eop.`EmployeeName`, 
            eop.`JobName`, 
            b.`branch_name`, 
            ecp.`status`,
            CONCAT('Q2', '-', year_param) AS Quarter
        FROM 
            employees_on_probation eop
        LEFT JOIN 
            branch_tbl b ON eop.BranchID = b.branch_id
        LEFT JOIN 
            employees_completing_procedures ecp ON eop.ApplicantID = ecp.ApplicantID
        WHERE 
            MONTH(eop.EmploymentDate) BETWEEN 4 AND 6
        AND YEAR(eop.EmploymentDate) = year_param
        
        UNION ALL
        
        -- Q3 Results
        SELECT 
            eop.`ApplicantID`, 
            eop.`EmployeeName`, 
            eop.`JobName`, 
            b.`branch_name`, 
            ecp.`status`,
            CONCAT('Q3', '-', year_param) AS Quarter
        FROM 
            employees_on_probation eop
        LEFT JOIN 
            branch_tbl b ON eop.BranchID = b.branch_id
        LEFT JOIN 
            employees_completing_procedures ecp ON eop.ApplicantID = ecp.ApplicantID
        WHERE 
            MONTH(eop.EmploymentDate) BETWEEN 7 AND 9
        AND YEAR(eop.EmploymentDate) = year_param
        
        UNION ALL
        
        -- Q4 Results
        SELECT 
            eop.`ApplicantID`, 
            eop.`EmployeeName`, 
            eop.`JobName`, 
            b.`branch_name`, 
            ecp.`status`,
            CONCAT('Q4', '-', year_param) AS Quarter
        FROM 
            employees_on_probation eop
        LEFT JOIN 
            branch_tbl b ON eop.BranchID = b.branch_id
        LEFT JOIN 
            employees_completing_procedures ecp ON eop.ApplicantID = ecp.ApplicantID
        WHERE 
            MONTH(eop.EmploymentDate) BETWEEN 10 AND 12
        AND YEAR(eop.EmploymentDate) = year_param;
    END IF;
END

    IN from_date DATE,
    IN to_date DATE

BEGIN
    -- Create temporary table to hold results
    DROP TEMPORARY TABLE IF EXISTS temp_results;
    CREATE TEMPORARY TABLE temp_results (
        DateRange VARCHAR(50),
        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)
    );

    -- Insert data based on provided date range
    INSERT INTO temp_results
    SELECT 
        CONCAT('From ', from_date, ' To ', to_date) AS DateRange,
        (SELECT COUNT(*) FROM applicants 
         WHERE FirstInterviewDate BETWEEN from_date AND to_date) AS FirstInterview_Total,
        (SELECT COUNT(*) FROM applicants 
         WHERE FirstInterviewResult = 2 
           AND FirstInterviewDate BETWEEN from_date AND to_date) AS FirstInterview_Passed,
        (SELECT COUNT(*) FROM applicants 
         WHERE FirstInterviewResult = 1 
           AND FirstInterviewDate BETWEEN from_date AND to_date) AS FirstInterview_Failed,
        (SELECT COUNT(*) FROM applicants 
         WHERE FirstInterviewResult = 3 
           AND FirstInterviewDate BETWEEN from_date AND to_date) AS FirstInterview_Pending,
        (SELECT COUNT(*) FROM applicants 
         WHERE FinalInterviewResult IN (1, 2) 
           AND FinalInterviewDate BETWEEN from_date AND to_date) AS FinalInterview_Total,
        (SELECT COUNT(*) FROM applicants 
         WHERE FinalInterviewResult = 2 
           AND FinalInterviewDate BETWEEN from_date AND to_date) AS FinalInterview_Passed,
        (SELECT COUNT(*) FROM applicants 
         WHERE FinalInterviewResult = 1 
           AND FinalInterviewDate BETWEEN from_date AND to_date) AS FinalInterview_Failed,
        ROUND(
            IFNULL(
                (SELECT COUNT(*) FROM applicants 
                 WHERE FinalInterviewResult = 2 
                   AND FinalInterviewDate BETWEEN from_date AND to_date)
                /
                NULLIF((SELECT COUNT(*) FROM applicants 
                        WHERE FirstInterviewDate BETWEEN from_date AND to_date), 0),
                0
            ) * 100, 2
        ) AS ConversionRate_Percentage;

    -- Return the results
    SELECT * FROM temp_results;

    -- Clean up
    DROP TEMPORARY TABLE IF EXISTS temp_results;
END

BEGIN
    -- This procedure provides a summary of salesman photo activity across all branches
    -- for the current year, including total clients, clients with photos,
    -- photo percentage, and monthly photo counts.

    SELECT
        s.id AS `رقم المندوب`,
        s.name AS `اسم المندوب`,
        b.branch_name AS `اسم الفرع`,
        -- Derive Salesman Type based on saleman_type_name
        CASE
            WHEN st.saleman_type_name LIKE '%جملة%' THEN 'جملة'
            WHEN st.saleman_type_name LIKE '%تجزئة%' THEN 'تجزئة'
            ELSE COALESCE(st.saleman_type_name, 'غير محدد') -- Handle potential NULLs for type
        END AS `نوع المندوب`,
        -- Total Clients assigned to the salesman
        COUNT(DISTINCT cs.client_id) AS `إجمالي عدد العملاء`,
        -- Clients with at least one photo for the current year
        COUNT(DISTINCT cp.client_id) AS `العملاء الذين لديهم ظهور`,
        -- Photo Percentage (handle division by zero to avoid errors)
        ROUND(
            (COUNT(DISTINCT cp.client_id) * 100.0) /
            NULLIF(COUNT(DISTINCT cs.client_id), 0),
            2
        ) AS `نسبة الظهور`,
        -- Hardcoded month columns to ensure all 12 months are always present
        -- Each column counts photos taken in that specific month for the current year
        SUM(CASE WHEN MONTH(cp.created_at) = 1 THEN 1 ELSE 0 END) AS `يناير`,
        SUM(CASE WHEN MONTH(cp.created_at) = 2 THEN 1 ELSE 0 END) AS `فبراير`,
        SUM(CASE WHEN MONTH(cp.created_at) = 3 THEN 1 ELSE 0 END) AS `مارس`,
        SUM(CASE WHEN MONTH(cp.created_at) = 4 THEN 1 ELSE 0 END) AS `أبريل`,
        SUM(CASE WHEN MONTH(cp.created_at) = 5 THEN 1 ELSE 0 END) AS `مايو`,
        SUM(CASE WHEN MONTH(cp.created_at) = 6 THEN 1 ELSE 0 END) AS `يونيو`,
        SUM(CASE WHEN MONTH(cp.created_at) = 7 THEN 1 ELSE 0 END) AS `يوليو`,
        SUM(CASE WHEN MONTH(cp.created_at) = 8 THEN 1 ELSE 0 END) AS `أغسطس`,
        SUM(CASE WHEN MONTH(cp.created_at) = 9 THEN 1 ELSE 0 END) AS `سبتمبر`,
        SUM(CASE WHEN MONTH(cp.created_at) = 10 THEN 1 ELSE 0 END) AS `أكتوبر`,
        SUM(CASE WHEN MONTH(cp.created_at) = 11 THEN 1 ELSE 0 END) AS `نوفمبر`,
        SUM(CASE WHEN MONTH(cp.created_at) = 12 THEN 1 ELSE 0 END) AS `ديسمبر`
    FROM
        salesmen AS s
    -- Join to get branch name
    JOIN
        branch_tbl AS b ON s.branch_id = b.branch_id
    -- LEFT JOIN to get salesman type (in case a salesman has no defined type)
    LEFT JOIN
        salman_type_tbl AS st ON s.sal_type_id = st.saleman_main_type_id
    -- Join to get assigned clients for each salesman
    JOIN
        client_salesmen AS cs ON cs.salesman_id = s.id
    JOIN
        client_info_tbl AS ci ON ci.client_id = cs.client_id
    -- LEFT JOIN to get client photos. Filter for the current year directly in the JOIN condition
    -- This ensures that even salesmen with no photos are included initially,
    -- but their photo counts will be 0.
    LEFT JOIN
        client_photos AS cp ON cp.client_id = ci.client_id
                            AND YEAR(cp.created_at) = YEAR(CURDATE())
    WHERE
        -- Apply filters to exclude inactive or 'stopped' salesmen
        s.name NOT LIKE '%موقفون%'
        AND s.activity_is_active = 'نعم'
        AND s.device_id_2 IS NOT NULL
    GROUP BY
        s.id, s.name, b.branch_name, st.saleman_type_name
    -- Only show salesmen who have taken at least one photo this year
    HAVING
        `العملاء الذين لديهم ظهور` > 0
    ORDER BY
        b.branch_name ASC, s.id;

END

IN p_order_id BIGINT

BEGIN
    DECLARE v_warehouse BIGINT;
    DECLARE v_party_id BIGINT;
    DECLARE v_currency CHAR(3);
    DECLARE v_total DECIMAL(18,2) DEFAULT 0;
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_line_id, v_product_id, v_variant_id, v_batch_id BIGINT;
    DECLARE v_qty, v_price DECIMAL(18,4);
    
    DECLARE cur CURSOR FOR
        SELECT id, product_id, variant_id, batch_id, quantity, unit_price
        FROM sales_order_lines
        WHERE sales_order_id = p_order_id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    SELECT warehouse_id, party_id, currency_code, total_amount
    INTO v_warehouse, v_party_id, v_currency, v_total
    FROM sales_orders WHERE id = p_order_id;

    START TRANSACTION;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_line_id, v_product_id, v_variant_id, v_batch_id, v_qty, v_price;
        IF done THEN LEAVE read_loop; END IF;

        UPDATE batches
        SET quantity = quantity - v_qty
        WHERE id = v_batch_id AND warehouse_id = v_warehouse;

        INSERT INTO inventory_transactions (
            transaction_type, product_id, variant_id, batch_id,
            warehouse_id, quantity_change, reference_type, reference_id
        ) VALUES (
            'sale', v_product_id, v_variant_id, v_batch_id,
            v_warehouse, -v_qty, 'sales_order', p_order_id
        );
    END LOOP;
    CLOSE cur;

    UPDATE sales_orders
    SET status = 'shipped', shipped_at = NOW()
    WHERE id = p_order_id;

    INSERT INTO invoices (sales_order_id, party_id, currency_code, total_amount, status, due_date)
    VALUES (p_order_id, v_party_id, v_currency, v_total, 'issued', DATE_ADD(NOW(), INTERVAL 30 DAY));

    COMMIT;
END

    IN p_product_id BIGINT,
    IN p_variant_id BIGINT,
    IN p_from_warehouse BIGINT,
    IN p_to_warehouse BIGINT,
    IN p_quantity DECIMAL(15,4),
    IN p_notes TEXT

BEGIN
    DECLARE v_batch_id BIGINT;
    DECLARE v_location_id BIGINT;

    SELECT id, location_id INTO v_batch_id, v_location_id
    FROM batches
    WHERE product_id = p_product_id
      AND (variant_id = p_variant_id OR (p_variant_id IS NULL AND variant_id IS NULL))
      AND warehouse_id = p_from_warehouse
      AND quantity >= p_quantity
    ORDER BY created_at ASC
    LIMIT 1;

    IF v_batch_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient stock in source warehouse';
    END IF;

    START TRANSACTION;

    UPDATE batches SET quantity = quantity - p_quantity WHERE id = v_batch_id;

    INSERT INTO batches (
        batch_number, product_id, variant_id, warehouse_id, location_id,
        purchase_price, currency_code, quantity, manufacturing_date, expiry_date, costing_method
    )
    SELECT
        batch_number, product_id, variant_id, p_to_warehouse, NULL,
        purchase_price, currency_code, p_quantity, manufacturing_date, expiry_date, costing_method
    FROM batches WHERE id = v_batch_id
    ON DUPLICATE KEY UPDATE quantity = quantity + p_quantity;

    SET @dest_batch_id = LAST_INSERT_ID();
    IF ROW_COUNT() = 0 THEN
        SELECT id INTO @dest_batch_id
        FROM batches
        WHERE batch_number = (SELECT batch_number FROM batches WHERE id = v_batch_id)
          AND warehouse_id = p_to_warehouse
          AND product_id = p_product_id
          AND (variant_id = p_variant_id OR (p_variant_id IS NULL AND variant_id IS NULL));
    END IF;

    INSERT INTO inventory_transactions (
        transaction_type, product_id, variant_id, batch_id,
        warehouse_id, from_warehouse_id, to_warehouse_id,
        quantity_change, reference_type, notes
    ) VALUES
    ('transfer_out', p_product_id, p_variant_id, v_batch_id,
        p_from_warehouse, p_from_warehouse, p_to_warehouse,
        -p_quantity, 'manual_transfer', p_notes),
    ('transfer_in', p_product_id, p_variant_id, @dest_batch_id,
        p_to_warehouse, p_from_warehouse, p_to_warehouse,
        p_quantity, 'manual_transfer', p_notes);

    COMMIT;
END

BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tbl_name VARCHAR(64);
    DECLARE next_month INT;
    DECLARE drop_month INT;

    DECLARE table_cursor CURSOR FOR
        SELECT 'inventory_transactions'
        UNION SELECT 'sales_orders'
        UNION SELECT 'purchase_orders';

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    SET next_month = YEAR(DATE_ADD(NOW(), INTERVAL 2 MONTH)) * 100 + MONTH(DATE_ADD(NOW(), INTERVAL 2 MONTH));
    SET drop_month = YEAR(DATE_SUB(NOW(), INTERVAL 15 MONTH)) * 100 + MONTH(DATE_SUB(NOW(), INTERVAL 15 MONTH));

    OPEN table_cursor;
    tbl_loop: LOOP
        FETCH table_cursor INTO tbl_name;
        IF done THEN LEAVE tbl_loop; END IF;

        SET @sql_add = CONCAT(
            'ALTER TABLE ', tbl_name, ' REORGANIZE PARTITION p_future INTO (',
            'PARTITION p', LPAD(next_month, 6, '0'), ' VALUES LESS THAN (', next_month + 1, '), ',
            'PARTITION p_future VALUES LESS THAN MAXVALUE);'
        );
        PREPARE stmt FROM @sql_add;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        SET @sql_check = CONCAT(
            'SELECT COUNT(*) INTO @exists FROM information_schema.partitions ',
            'WHERE table_schema = DATABASE() AND table_name = ? AND partition_name = ?'
        );
        PREPARE stmt_check FROM @sql_check;
        EXECUTE stmt_check USING tbl_name, CONCAT('p', LPAD(drop_month, 6, '0'));
        DEALLOCATE PREPARE stmt_check;

        IF @exists > 0 THEN
            SET @sql_drop = CONCAT('ALTER TABLE ', tbl_name, ' DROP PARTITION p', LPAD(drop_month, 6, '0'));
            PREPARE stmt_drop FROM @sql_drop;
            EXECUTE stmt_drop;
            DEALLOCATE PREPARE stmt_drop;
        END IF;
    END LOOP;
    CLOSE table_cursor;
END

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 `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 `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

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