#!/usr/bin/env bash
# S'exécute sur le serveur OVH (via stdin).
set -euo pipefail
cd /homez.1544/lotopou/lpf26

q() {
  echo ""
  echo "=== $1 ==="
  APP_ENV=prod php bin/console dbal:run-sql --no-ansi "$2"
}

q TEAM_STATS "SELECT t.name AS team, s.position AS pos, ROUND(s.total_points) AS ranking_pts, ROUND(AVG(p.score_domicile + p.score_exterieur), 2) AS avg_prono_goals, (SELECT ROUND(AVG(mg.total_goals), 2) FROM (SELECT DISTINCT m2.id, m2.score_domicile + m2.score_exterieur AS total_goals FROM pronostic p2 JOIN team_member tm2 ON tm2.player_id = p2.joueur_id AND tm2.team_id = t.id JOIN \`match\` m2 ON m2.id = p2.match_id AND m2.score_domicile IS NOT NULL) mg) AS avg_real_goals, COUNT(DISTINCT p.id) AS pronos_count FROM team t LEFT JOIN team_ranking_snapshot s ON s.team_id = t.id AND s.match_ref_id = (SELECT s2.match_ref_id FROM team_ranking_snapshot s2 JOIN \`match\` m3 ON m3.id = s2.match_ref_id ORDER BY m3.date_heure DESC, m3.id DESC LIMIT 1) JOIN team_member tm ON tm.team_id = t.id JOIN user u ON u.id = tm.player_id AND LOWER(u.email) != 'admin@lotopotofoot.fr' LEFT JOIN pronostic p ON p.joueur_id = u.id LEFT JOIN \`match\` m ON m.id = p.match_id AND m.score_domicile IS NOT NULL WHERE p.id IS NOT NULL GROUP BY t.id, t.name, s.position, s.total_points ORDER BY s.position ASC"

q TEAM_JOKERS "SELECT t.name AS team, COUNT(u.id) AS jokers_used, GROUP_CONCAT(CONCAT(j.name, ' (', ROUND(COALESCE(mp.match_pts, 0)), ' pts match)') ORDER BY u.placed_at SEPARATOR ' | ') AS detail FROM team_joker_usage u JOIN team t ON t.id = u.team_id JOIN joker j ON j.id = u.joker_id LEFT JOIN (SELECT tj.team_id, tj.match_id, ROUND(SUM(COALESCE(p.points_equipe, p.points, 0))) + COALESCE((SELECT SUM(b2.points_attribues) FROM but b2 JOIN user u2 ON u2.buteur_choisi_id = b2.buteur_id JOIN team_member tm2 ON tm2.player_id = u2.id AND tm2.team_id = tj.team_id WHERE b2.match_ref_id = tj.match_id), 0) AS match_pts FROM team_joker_usage tj JOIN team_member tm ON tm.team_id = tj.team_id JOIN pronostic p ON p.joueur_id = tm.player_id AND p.match_id = tj.match_id GROUP BY tj.team_id, tj.match_id) mp ON mp.team_id = u.team_id AND mp.match_id = u.match_id GROUP BY t.id, t.name ORDER BY jokers_used DESC, t.name ASC"

q TEAM_JOKER_EFFICACY "SELECT t.name AS team, COUNT(DISTINCT jm.match_id) AS jokers, ROUND(AVG(CASE WHEN jm.match_id IS NOT NULL THEN mp.pts END), 1) AS avg_pts_joker_match, ROUND(AVG(CASE WHEN jm.match_id IS NULL THEN mp.pts END), 1) AS avg_pts_other_match, ROUND(AVG(CASE WHEN jm.match_id IS NOT NULL THEN mp.pts END) - AVG(CASE WHEN jm.match_id IS NULL THEN mp.pts END), 1) AS delta_vs_avg FROM team t JOIN (SELECT tm.team_id, m.id AS match_id, ROUND(SUM(COALESCE(p.points_equipe, p.points, 0))) + COALESCE((SELECT SUM(b2.points_attribues) FROM but b2 JOIN user u2 ON u2.buteur_choisi_id = b2.buteur_id JOIN team_member tm2 ON tm2.player_id = u2.id AND tm2.team_id = tm.team_id WHERE b2.match_ref_id = m.id), 0) AS pts FROM team_member tm JOIN user usr ON usr.id = tm.player_id AND LOWER(usr.email) != 'admin@lotopotofoot.fr' JOIN pronostic p ON p.joueur_id = tm.player_id JOIN \`match\` m ON m.id = p.match_id AND m.score_domicile IS NOT NULL GROUP BY tm.team_id, m.id) mp ON mp.team_id = t.id LEFT JOIN (SELECT DISTINCT team_id, match_id FROM team_joker_usage) jm ON jm.team_id = mp.team_id AND jm.match_id = mp.match_id GROUP BY t.id, t.name HAVING jokers > 0 ORDER BY delta_vs_avg DESC"

q TEAM_FORGOTTEN "SELECT t.name AS team, SUM(CASE WHEN m.phase LIKE 'Group%' AND p.created_at >= m.date_heure THEN 1 ELSE 0 END) AS oublis_poules_auto, SUM(CASE WHEN m.phase LIKE 'Group%' AND p.created_at >= m.date_heure THEN ROUND(COALESCE(p.points, 0)) ELSE 0 END) AS pts_oublis_poules FROM team t JOIN team_member tm ON tm.team_id = t.id JOIN user u ON u.id = tm.player_id AND LOWER(u.email) != 'admin@lotopotofoot.fr' JOIN pronostic p ON p.joueur_id = u.id JOIN \`match\` m ON m.id = p.match_id AND m.score_domicile IS NOT NULL GROUP BY t.id, t.name ORDER BY oublis_poules_auto DESC, t.name ASC"

q TEAM_FORGOTTEN_KO "SELECT t.name AS team, COUNT(*) AS oublis_knockout FROM team_member tm JOIN team t ON t.id = tm.team_id JOIN user u ON u.id = tm.player_id AND LOWER(u.email) != 'admin@lotopotofoot.fr' JOIN \`match\` m ON m.score_domicile IS NOT NULL AND (m.phase IS NULL OR m.phase NOT LIKE 'Group%') LEFT JOIN pronostic p ON p.joueur_id = u.id AND p.match_id = m.id WHERE p.id IS NULL GROUP BY t.id, t.name HAVING oublis_knockout > 0 ORDER BY oublis_knockout DESC"

q FORGOTTEN_GLOBAL "SELECT SUM(CASE WHEN m.phase LIKE 'Group%' AND p.created_at >= m.date_heure THEN 1 ELSE 0 END) AS oublis_poules_auto, SUM(CASE WHEN m.phase LIKE 'Group%' AND p.created_at >= m.date_heure THEN ROUND(COALESCE(p.points, 0)) ELSE 0 END) AS pts_oublis_poules FROM pronostic p JOIN user u ON u.id = p.joueur_id AND LOWER(u.email) != 'admin@lotopotofoot.fr' JOIN \`match\` m ON m.id = p.match_id AND m.score_domicile IS NOT NULL"

q FORGOTTEN_KO_GLOBAL "SELECT COUNT(*) AS oublis_knockout_sans_prono FROM team_member tm JOIN user u ON u.id = tm.player_id AND LOWER(u.email) != 'admin@lotopotofoot.fr' JOIN \`match\` m ON m.score_domicile IS NOT NULL AND (m.phase IS NULL OR m.phase NOT LIKE 'Group%') LEFT JOIN pronostic p ON p.joueur_id = u.id AND p.match_id = m.id WHERE p.id IS NULL"