-- Synthèse
SELECT count(distinct fp.id) as nb_participant
     , 100 * count(distinct fp.id) / (SELECT sum(JSON_EXTRACT(`values`, '$.maxParticipant')) AS maxParticipant
FROM fostt_timeslots ft
	inner join fostt_options_timeslots fot on fot.timeslot_id = ft.id
                                          and option_id = 6
WHERE event_id = fp.event_id) as pct
     , count(*) as nb_inscriptions
     , 100 * count(*) / (SELECT sum(JSON_EXTRACT(`values`, '$.maxParticipant')) AS maxParticipant
FROM fostt_timeslots ft
	inner join fostt_options_timeslots fot on fot.timeslot_id = ft.id
                                          and option_id = 6
WHERE event_id = fp.event_id) as pct2
FROM `fostt_participants` fp
	inner join fostt_participants_timeslots fpt on fp.id = fpt.participant_id
WHERE event_id = 184;

-- Synthèse par jour
SELECT dayNumber
	 , count(distinct fp.id) as nb_participant
FROM `fostt_participants` fp
	inner join fostt_participants_timeslots fpt on fp.id = fpt.participant_id
	inner join fostt_timeslots ft on ft.id = fpt.timeslot_id
WHERE fp.event_id = 184
GROUP BY dayNumber;

-- Joueurs / inscriptions du jour
SELECT count(distinct fp.id) as nb_participant, count(*) as nb_inscriptions
FROM `fostt_participants` fp
	inner join fostt_participants_timeslots fpt on fp.id = fpt.participant_id
WHERE event_id = 184;

-- Nb max participant
SELECT sum(JSON_EXTRACT(`values`, '$.maxParticipant')) AS maxParticipant
FROM fostt_timeslots ft
	inner join fostt_options_timeslots fot on fot.timeslot_id = ft.id
                                          and option_id = 6
WHERE event_id = 184;

-- Nb paiement en ligne
SELECT count(distinct fp.id) as nb_participant -- JSON_EXTRACT(`values`, '$.amount') AS maxParticipant
FROM fostt_participants fp
	inner join fostt_options_participants fop on fop.participant_id = fp.id
                                          and option_id = 3
WHERE event_id = 184;

-- Nb paiement en ligne par type
SELECT 
    jt.type,
    SUM(jt.amount) AS montant_total,
	MIN(jt.amount) AS min_total,
	MAX(jt.amount) AS max_total,
	count(DISTINCT fp.id) as nb_participant
FROM fostt_participants fp
INNER JOIN fostt_options_participants fop 
    ON fop.participant_id = fp.id 
    AND option_id = 3
CROSS JOIN JSON_TABLE(
    `values`, 
    '$.paiements[*]' COLUMNS (
        amount DOUBLE PATH '$.amount',
        type VARCHAR(50) PATH '$.type'
    )
) AS jt
WHERE event_id = 184
GROUP BY jt.type;

-- Nb paiement en ligne par type et par jour
SELECT 
    jt.type,
    jt.date, 
    SUM(jt.amount) AS montant_total,
	count(1) as nb_participant
FROM fostt_participants fp
INNER JOIN fostt_options_participants fop 
    ON fop.participant_id = fp.id 
    AND option_id = 3
CROSS JOIN JSON_TABLE(
    `values`, 
    '$.paiements[*]' COLUMNS (
        amount DOUBLE PATH '$.amount',
        type VARCHAR(50) PATH '$.type',
        date date PATH '$.date'
    )
) AS jt
WHERE event_id = 184
GROUP BY jt.type, jt.date
ORDER BY jt.type, jt.date;