CREATE DEFINER=`xxxxxx`@`%` PROCEDURE `my_sp`(IN duration INT, IN period VARCHAR(5), IN type_cat VARCHAR(8), IN size_class_list VARCHAR(1000), IN zones_list VARCHAR(1000), IN laden_flag_list VARCHAR(255))
BEGIN
    DECLARE time_frame INT;
    SET time_frame = IFNULL(duration, 6);
    SELECT t0.zone as zone_id, COUNT(DISTINCT t0.vessel_imo) AS vessel_count,
        CASE
            WHEN period = 'month' THEN DATE_FORMAT(t0.creation_date, "%Y%m")
            ELSE YEARWEEK(t0.creation_date)
        END AS period,
        CASE
            WHEN type_cat = 'tanker' THEN 'Tanker'
            ELSE 'Dry Bulk'
        END AS vessel_type, t2.size_class_id FROM vessel_position t0
    INNER JOIN vessel_types t1 ON t1.vessel_imo = t0.vessel_imo
    INNER JOIN vessel_size_class t2 ON t2.vessel_imo = t0.vessel_imo
    WHERE t0.creation_date >= DATE_SUB(CURDATE(), INTERVAL time_frame MONTH)
    AND
    (
        CASE
            WHEN type_cat = 'tanker' THEN t1.type_id = 2 OR t1.type_id = 3 OR t1.type_id = 4
            ELSE t1.type_id = 1
        END
    )
    AND (zones_list IS NULL OR FIND_IN_SET(t0.zone, zones_list))
    AND (size_class_list IS NULL OR FIND_IN_SET(t2.size_class_id, size_class_list))
    GROUP BY t0.zone,
        CASE
            WHEN period = 'month' THEN DATE_FORMAT(t0.creation_date, "%Y%m")
            ELSE YEARWEEK(t0.creation_date, 1)
        END, t2.size_class_id, vessel_type
    ORDER BY t0.zone, t2.size_class_id, t0.creation_date, COUNT(t0.vessel_imo);
END