CREATE DEFINER=CURRENT_USER PROCEDURE `GetChefSessionsArchive`(IN i_chef INT, i_date DATE)
BEGIN
	DECLARE d_podId INT;
	DECLARE d_relatedFieldId INT; -- aggiungere funzione per recuperare d_relatedFieldId dei corsi dallo slug
	DECLARE d_session INT; -- aggiungere funzione per recuperare termId della categoria dallo slug (se necessario)
    DECLARE d_lessonId INT;
	SET d_podId = GETPODTYPEID('slci_course_session');
    SET d_lessonId = GETPODTYPEID('slci_lesson_session');
    
	SET d_relatedFieldId = 0;
	SET d_session = 0;
      
	SELECT cs.*,tax.*, lesson.lesson_date
	FROM inno_lci_2_pods_slci_course_session cs,
		(SELECT pr.* -- pr.item_id
			FROM inno_lci_2_podsrel pr
			WHERE pr.pod_id = d_podId
				-- AND  pr.item_id = 2
				-- AND pr.related_pod_id = 10924
			AND pr.related_field_id = 0
		) tax,
		(SELECT l.id lesson_id, l.slci_lesson_session_date lesson_date, rl.related_item_id lesson_course -- pr.item_id
			FROM 
				inno_lci_2_pods_slci_lesson_session l, 
				(
					SELECT rl.item_id, rl.related_item_id
					FROM inno_lci_2_podsrel rl
					WHERE 
						rl.related_pod_id = d_podId
						AND rl.pod_id = d_lessonId
				) rl
			WHERE 
				l.slci_lesson_session_selled_seats < l.slci_lesson_session_available_seats
				AND l.id = rl.item_id
			GROUP BY l.id
			ORDER BY l.slci_lesson_session_date ASC
		 ) lesson
	WHERE 
		cs.slci_course_session_chef_id = i_chef
		AND cs.id = tax.item_id
        AND cs.slci_course_session_session_end_date < i_date
		AND lesson.lesson_course = cs.id;
END