
-- Contrôle montant payé / montant dû
SELECT t.*
     , fu.email
     , (montant_paye + nb_serie) - montant_du as remboursement
FROM (
SELECT 
    fp.id,
    fp.licence,
    fp.last_name,
    fp.first_name,
    fp.user_id,
    sum(coalesce(jt.timeslotPrice, 9)) as montant_du,
        (SELECT SUM(jt.amount) 
		FROM fostt_options_participants fop 
			CROSS JOIN JSON_TABLE(
			fop.`values`, 
			'$.paiements[*]' COLUMNS (
				amount DOUBLE PATH '$.amount',
				type VARCHAR(50) PATH '$.type'
			)
		) AS jt
		WHERE fop.participant_id = fp.id 
		AND option_id = 3
		AND jt.type = 'hello'
	) AS montant_paye,
	count(DISTINCT fpt.timeslot_id) as nb_serie
FROM fostt_participants fp
INNER JOIN fostt_participants_timeslots fpt
    ON fpt.participant_id = fp.id
INNER JOIN fostt_options_timeslots ft
    ON ft.timeslot_id = fpt.timeslot_id 	
	AND ft.option_id = 3
CROSS JOIN JSON_TABLE(
    ft.`values`, 
    '$' COLUMNS (
        timeslotPrice DOUBLE PATH '$.timeslotPrice'
    )
) AS jt
WHERE event_id = 184
GROUP BY fp.id) as t
	INNER JOIN fostt_users fu ON fu.id = t.user_id
WHERE montant_du <> COALESCE(montant_paye + nb_serie, montant_du)
ORDER BY user_id;

-- Export Feuille de pointage
SELECT t.*
     , fu.email
     , (montant_paye_ligne + reduction) - montant_du as solde
     , 0 as montant_paye_tournoi
     , (montant_paye_ligne + reduction) - montant_du >= 0 AS pointé 
FROM (
SELECT 
    fp.id,
    fp.licence,
    fp.last_name,
    fp.first_name,
    fp.user_id,
    pt.nomclub,
    GROUP_CONCAT(ft.code ORDER BY ft.code SEPARATOR', ') AS code,
    COALESCE(pt.villesalle,pt.nomclub) as villesalle,
    sum(coalesce(jt.timeslotPrice, 9)) as montant_du,
    coalesce((SELECT SUM(jt.amount) 
		FROM fostt_options_participants fop 
			CROSS JOIN JSON_TABLE(
			fop.`values`, 
			'$.paiements[*]' COLUMNS (
				amount DOUBLE PATH '$.amount',
				type VARCHAR(50) PATH '$.type'
			)
		) AS jt
		WHERE fop.participant_id = fp.id 
		AND option_id = 3
		AND jt.type = 'hello'
	), 0) AS montant_paye_ligne,
    coalesce((SELECT SUM(jt.amount) 
		FROM fostt_options_participants fop 
			CROSS JOIN JSON_TABLE(
			fop.`values`, 
			'$.paiements[*]' COLUMNS (
				amount DOUBLE PATH '$.amount',
				type VARCHAR(50) PATH '$.type'
			)
		) AS jt
		WHERE fop.participant_id = fp.id 
		AND option_id = 3
		AND jt.type = 'hello-reduc'
	), 0) AS reduction
FROM fostt_participants fp
INNER JOIN fostt_participants_timeslots fpt
    ON fpt.participant_id = fp.id
INNER JOIN fostt_timeslots ft
    ON ft.id = fpt.timeslot_id
INNER JOIN fostt_options_timeslots fot
    ON fot.timeslot_id = fpt.timeslot_id 	
	AND fot.option_id = 3
CROSS JOIN JSON_TABLE(
    fot.`values`, 
    '$' COLUMNS (
        timeslotPrice DOUBLE PATH '$.timeslotPrice'
    )
) AS jt
INNER JOIN fostt_options_participants fop
    ON fp.id = fop.participant_id 	
	AND fop.option_id = 1
CROSS JOIN JSON_TABLE(
    fop.`values`, 
    '$' COLUMNS (
        nomclub VARCHAR(50) PATH '$.nomclub',
        villesalle VARCHAR(50) PATH '$.villesalle'
    )
) AS pt
WHERE fp.event_id = 184
GROUP BY fp.id) as t
	INNER JOIN fostt_users fu ON fu.id = t.user_id
ORDER BY villesalle, nomclub, last_name, first_name;

-- Contrôle réduc / série
SELECT t.*
     , fu.email
FROM (
SELECT 
    fp.id,
    fp.licence,
    fp.last_name,
    fp.first_name,
    fp.user_id,
        (SELECT SUM(jt.amount) 
		FROM fostt_options_participants fop 
			CROSS JOIN JSON_TABLE(
			fop.`values`, 
			'$.paiements[*]' COLUMNS (
				amount DOUBLE PATH '$.amount',
				type VARCHAR(50) PATH '$.type'
			)
		) AS jt
		WHERE fop.participant_id = fp.id 
		AND option_id = 3
		AND jt.type = 'hello-reduc'
	) AS montant_reduc,
	count(DISTINCT fpt.timeslot_id) as nb_serie
FROM fostt_participants fp
INNER JOIN fostt_participants_timeslots fpt
    ON fpt.participant_id = fp.id
WHERE event_id = 184
GROUP BY fp.id) as t
	INNER JOIN fostt_users fu ON fu.id = t.user_id
WHERE nb_serie <> montant_reduc
ORDER BY user_id;

-- Contrôle catégorie -- On ne doit avoir que "A"
SELECT DISTINCT JSON_EXTRACT(fop.`values`, '$.cat') AS cat, ft.code
FROM fostt_participants fp
	inner join fostt_options_participants fop on fop.participant_id = fp.id
    										 AND fop.option_id = 1
	inner join fostt_participants_timeslots fpt on fpt.participant_id = fp.id
	inner join fostt_timeslots ft on fpt.timeslot_id = ft.id
WHERE fp.event_id = 184
  AND JSON_EXTRACT(fop.`values`, '$.cat') = 'P'
ORDER BY ft.code ASC;

-- Contrôle type de licence -- On ne doit pas avoir de "P"
SELECT DISTINCT JSON_EXTRACT(fop.`values`, '$.type') AS type, ft.code
FROM fostt_participants fp
	inner join fostt_options_participants fop on fop.participant_id = fp.id
    										 AND fop.option_id = 1
	inner join fostt_participants_timeslots fpt on fpt.participant_id = fp.id
	inner join fostt_timeslots ft on fpt.timeslot_id = ft.id
WHERE fp.event_id = 184
ORDER BY ft.code ASC;