DROP PROCEDURE IF EXISTS `get_admin_reports`;
DELIMITER ;;
CREATE PROCEDURE `get_admin_reports` (
    IN p_session_id CHAR(64),
    IN start_date VARCHAR(10),
    IN end_date VARCHAR(10)
)
proc:BEGIN
    IF (SELECT COUNT(session_id) FROM sessions WHERE session_id = LOWER(p_session_id)) = 0 OR p_session_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'get_admin_reports: No user logged in';
        LEAVE proc;
    END IF;
    IF (SELECT COUNT(session_id) FROM sessions WHERE user_type = 0 AND session_id = LOWER(p_session_id)) = 0 THEN
        SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = 'get_admin_reports: User not a Administrator';
        LEAVE proc;
    END IF;
    IF start_date IS NOT NULL AND end_date IS NOT NULL THEN
        SELECT P.name as Product_Name, OP.product_id as Product_ID, OP.quantity as Quantity, OP.amount as Amount, P.tax as Tax, O.order_completed as order_timestamp FROM order_products OP INNER JOIN orders O on O.id = OP.order_id INNER JOIN products P on OP.product_id = P.id WHERE O.status = 3 AND (O.order_completed BETWEEN start_date AND end_date) ORDER BY O.order_completed ASC;
        LEAVE proc;
    END IF;
    
    IF start_date IS NOT NULL AND end_date IS NULL THEN
        SELECT P.name as Product_Name, OP.product_id as Product_ID, OP.quantity as Quantity, OP.amount as Amount, P.tax as Tax, O.order_completed as order_timestamp FROM order_products OP INNER JOIN orders O on O.id = OP.order_id INNER JOIN products P on OP.product_id = P.id WHERE O.status = 3 AND (O.order_completed > start_date)  ORDER BY O.order_completed ASC;
        LEAVE proc;
    END IF;

    IF start_date IS NULL AND end_date IS NOT NULL THEN
        SELECT P.name as Product_Name, OP.product_id as Product_ID, OP.quantity as Quantity, OP.amount as Amount, P.tax as Tax, O.order_completed as order_timestamp FROM order_products OP INNER JOIN orders O on O.id = OP.order_id INNER JOIN products P on OP.product_id = P.id WHERE O.status = 3 AND (O.order_completed < end_date)  ORDER BY O.order_completed ASC;
        LEAVE proc;
    END IF;
    SELECT P.name as Product_Name, OP.product_id as Product_ID, OP.quantity as Quantity, OP.amount as Amount, P.tax as Tax, O.order_completed as order_timestamp FROM order_products OP INNER JOIN orders O on O.id = OP.order_id INNER JOIN products P on OP.product_id = P.id WHERE O.status = 3 ORDER BY O.order_completed ASC;
END ;;
DELIMITER ;