#!/usr/bin/env bash
# Extrait les stats compétition depuis la prod (à lancer en local).
set -euo pipefail
ROOT="$(cd "$(dirname "${BASH_SOURCE[0]}")/.." && pwd)"
# shellcheck disable=SC1090
source "$ROOT/.env.deploy"
REMOTE="${DEPLOY_SSH_USER}@${DEPLOY_SSH_HOST}"
RPATH="${DEPLOY_REMOTE_PATH%/}"
SSH=(ssh -o BatchMode=yes -o StrictHostKeyChecking=accept-new -i "${DEPLOY_SSH_KEY}")

run_sql() {
  "${SSH[@]}" "${REMOTE}" "cd '${RPATH}' && APP_ENV=prod php bin/console dbal:run-sql --no-ansi --quiet \"${1}\" 2>/dev/null"
}

echo "===META==="
run_sql "SELECT COUNT(*) AS teams FROM team"
run_sql "SELECT COUNT(DISTINCT tm.id) AS players FROM team_member tm JOIN user u ON u.id=tm.player_id WHERE LOWER(u.email) != 'admin@lotopotofoot.fr'"
run_sql "SELECT COUNT(*) AS matches_played FROM \`match\` WHERE score_domicile IS NOT NULL AND score_exterieur IS NOT NULL"

echo "===MATCHES==="
run_sql "SELECT m.id, cd.nom AS home, ce.nom AS away, CONCAT(m.score_domicile,'-',m.score_exterieur) AS score, DATE_FORMAT(m.date_heure,'%d/%m %H:%i') AS dt, COALESCE(m.phase,'') AS phase FROM \`match\` m JOIN country cd ON cd.id=m.pays_domicile_id JOIN country ce ON ce.id=m.pays_exterieur_id WHERE m.score_domicile IS NOT NULL ORDER BY m.date_heure ASC, m.id ASC"

echo "===RANKING==="
run_sql "SELECT t.name, s.position, ROUND(s.total_points) AS pts, s.scores_exacts AS ex, s.bons_resultats AS br, s.resultats_faux AS rf, s.prises_risque AS bb, s.prises_risque_reussies AS bbs FROM team_ranking_snapshot s JOIN team t ON t.id=s.team_id WHERE s.match_ref_id = (SELECT s2.match_ref_id FROM team_ranking_snapshot s2 JOIN \`match\` m ON m.id=s2.match_ref_id ORDER BY m.date_heure DESC, m.id DESC LIMIT 1) ORDER BY s.position ASC"

echo "===TOP_PLAYERS==="
run_sql "SELECT tm.nickname, t.name AS team, ROUND(COALESCE(SUM(p.points),0)) AS pts, COUNT(p.id) AS pronos, SUM(CASE WHEN p.points_base=30 THEN 1 ELSE 0 END) AS exacts, SUM(CASE WHEN COALESCE(p.points,0)<=0 THEN 1 ELSE 0 END) AS ratés FROM pronostic p JOIN user u ON u.id=p.joueur_id JOIN team_member tm ON tm.player_id=u.id JOIN team t ON t.id=tm.team_id JOIN \`match\` m ON m.id=p.match_id WHERE m.score_domicile IS NOT NULL AND LOWER(u.email)!='admin@lotopotofoot.fr' GROUP BY u.id, tm.nickname, t.name ORDER BY pts DESC LIMIT 12"

echo "===BOTTOM_PLAYERS==="
run_sql "SELECT tm.nickname, t.name AS team, ROUND(COALESCE(SUM(p.points),0)) AS pts, COUNT(p.id) AS pronos FROM pronostic p JOIN user u ON u.id=p.joueur_id JOIN team_member tm ON tm.player_id=u.id JOIN team t ON t.id=tm.team_id JOIN \`match\` m ON m.id=p.match_id WHERE m.score_domicile IS NOT NULL AND LOWER(u.email)!='admin@lotopotofoot.fr' GROUP BY u.id, tm.nickname, t.name HAVING pronos>=2 ORDER BY pts ASC LIMIT 8"

echo "===BEST_PRONOS==="
run_sql "SELECT tm.nickname, t.name AS team, ROUND(p.points) AS pts, CONCAT(p.score_domicile,'-',p.score_exterieur) AS prono, CONCAT(m.score_domicile,'-',m.score_exterieur) AS reel, CONCAT(cd.nom,' - ',ce.nom) AS match_label FROM pronostic p JOIN user u ON u.id=p.joueur_id JOIN team_member tm ON tm.player_id=u.id JOIN team t ON t.id=tm.team_id JOIN \`match\` m ON m.id=p.match_id JOIN country cd ON cd.id=m.pays_domicile_id JOIN country ce ON ce.id=m.pays_exterieur_id WHERE m.score_domicile IS NOT NULL AND p.points IS NOT NULL ORDER BY p.points DESC LIMIT 10"

echo "===TOP_SCORES_PRONO==="
run_sql "SELECT CONCAT(p.score_domicile,'-',p.score_exterieur) AS score, COUNT(*) AS cnt FROM pronostic p JOIN \`match\` m ON m.id=p.match_id WHERE m.score_domicile IS NOT NULL GROUP BY p.score_domicile, p.score_exterieur ORDER BY cnt DESC LIMIT 8"

echo "===JOKERS==="
run_sql "SELECT j.name, j.code, COUNT(*) AS usages FROM team_joker_usage u JOIN joker j ON j.id=u.joker_id GROUP BY j.id, j.name, j.code ORDER BY usages DESC"

echo "===JOKERS_BY_TEAM==="
run_sql "SELECT t.name AS team, j.name AS joker, CONCAT(cd.nom,' - ',ce.nom) AS match_label FROM team_joker_usage u JOIN team t ON t.id=u.team_id JOIN joker j ON j.id=u.joker_id JOIN \`match\` m ON m.id=u.match_id JOIN country cd ON cd.id=m.pays_domicile_id JOIN country ce ON ce.id=m.pays_exterieur_id ORDER BY u.placed_at ASC"

echo "===BUTEURS_GOALS==="
run_sql "SELECT tm.nickname, t.name AS team, CONCAT(b.prenom,' ',b.nom) AS buteur, b2.points_attribues AS pts, b2.minute, CONCAT(cd.nom,' - ',ce.nom) AS match_label FROM but b2 JOIN buteur b ON b.id=b2.buteur_id JOIN user u ON u.buteur_choisi_id=b.id JOIN team_member tm ON tm.player_id=u.id JOIN team t ON t.id=tm.team_id JOIN \`match\` m ON m.id=b2.match_ref_id JOIN country cd ON cd.id=m.pays_domicile_id JOIN country ce ON ce.id=m.pays_exterieur_id ORDER BY b2.points_attribues DESC, b2.minute ASC LIMIT 15"

echo "===BUTEUR_PICKS==="
run_sql "SELECT CONCAT(b.prenom,' ',b.nom) AS buteur, COUNT(*) AS picks FROM user u JOIN buteur b ON b.id=u.buteur_choisi_id JOIN team_member tm ON tm.player_id=u.id WHERE LOWER(u.email)!='admin@lotopotofoot.fr' GROUP BY b.id, b.prenom, b.nom ORDER BY picks DESC LIMIT 8"

echo "===TEAM_GAP==="
run_sql "SELECT t.name AS team, ROUND(SUM(CASE WHEN sub.rn=1 THEN sub.pts ELSE 0 END)) AS joueur1_pts, ROUND(SUM(CASE WHEN sub.rn=2 THEN sub.pts ELSE 0 END)) AS joueur2_pts, ROUND(ABS(SUM(CASE WHEN sub.rn=1 THEN sub.pts ELSE 0 END)-SUM(CASE WHEN sub.rn=2 THEN sub.pts ELSE 0 END))) AS gap FROM team t JOIN (SELECT tm.team_id, tm.player_id, COALESCE(SUM(p.points),0) AS pts, ROW_NUMBER() OVER (PARTITION BY tm.team_id ORDER BY COALESCE(SUM(p.points),0) DESC) AS rn FROM team_member tm LEFT JOIN pronostic p ON p.joueur_id=tm.player_id LEFT JOIN \`match\` m ON m.id=p.match_id AND m.score_domicile IS NOT NULL GROUP BY tm.team_id, tm.player_id) sub ON sub.team_id=t.id WHERE sub.rn<=2 GROUP BY t.id, t.name HAVING COUNT(sub.player_id)>=2 ORDER BY gap DESC"