--1
SET SERVEROUTPUT ON;
DECLARE

    v_nbJ NUMBER;

BEGIN

    SELECT COUNT(idJoueur) INTO v_nbJ
    FROM Joueurs j
    WHERE j.idClub='C1';

    DBMS_OUTPUT.PUT_LINE('Nb joueurs club 1' || v_nbJ);

END;


--2
ACCEPT v_nbC PROMPT 'Entrez identifiant du club'
DECLARE

    v_nbJ NUMBER;

BEGIN

    SELECT COUNT(idJoueur) INTO v_nbJ
    FROM Joueurs j
    WHERE j.idClub='&v_nbC';

    DBMS_OUTPUT.PUT_LINE('Nb joueurs club  ' || TO_CHAR('&v_nbC') || ' : ' || v_nbJ);

END;

--3
ACCEPT v_idC PROMPT 'Entrez identifiant du club'
DECLARE

    v_nbJ NUMBER;
    v_nbC NUMBER;

BEGIN

    SELECT COUNT(*) INTO v_nbC
    FROM Clubs
    WHERE idClub='&v_idC';


    IF  v_nbC= 1 THEN
        SELECT COUNT(idJoueur) INTO v_nbJ
        FROM Joueurs j
        WHERE j.idClub='&v_idC';
        DBMS_OUTPUT.PUT_LINE('Nb joueurs club &v_idC' || ' : ' || v_nbJ);
    ELSE
        DBMS_OUTPUT.PUT_LINE('Le club &v_idC ne se trouve pas dans la base.');
    END IF;


END;

--4
ACCEPT v_idC PROMPT 'Entrez identifiant du club'
DECLARE

    v_nbJ NUMBER;
    v_Club CHAR(10);
BEGIN
    SELECT idClub INTO v_Club
    FROM Clubs
    WHERE idClub='&v_idC';

    SELECT COUNT(idJoueur) INTO v_nbJ
    FROM Joueurs j
    WHERE j.idClub='&v_idC';

    DBMS_OUTPUT.PUT_LINE('Nb joueurs club &v_idC' || ' : ' || v_nbJ);

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('Le club &v_idC ne se trouve pas dans la base.');



END;

--5
DECLARE
    r_tour Tournois%ROWTYPE;
BEGIN
    SELECT * INTO r_tour
    FROM Tournois
    WHERE idTounoi='T1';

    DBMS_OUTPUT.PUT_LINE('id : ' || r_tour.idTournoi || ' | nom : ' || r_tour.nomTournoi || ' | lieu : ' ||
     r_tour.lieuTournoi || ' | nombre de ronde : ' || r_tour.nbRondesTournoi);

END;

--6
CREATE OR REPLACE FUNCTION nbJoueursParClub(p_idClub IN Clubs.idClub%TYPE) RETURN NUMBER IS
    v_res NUMBER;
BEGIN
    SELECT COUNT(idJoueur) INTO v_res
    FROM Joueurs j
    JOIN Clubs c ON c.idClub=j.idClub
    WHERE c.idClub=p_idClub;

    IF v_res = 0 THEN
        RETURN NULL;
    ELSE
        RETURN v_res;
    END IF;
END;


--
--
--


--13
CREATE OR REPLACE FUNCTION categorieJoueur(p_dateNaissance IN Joueurs.dateNaissanceJoueur%TYPE, p_sexe IN Joueurs.sexeJoueur%TYPE) RETURN VARCHAR IS

BEGIN
    IF p_dateNaissance >= '31/12/1994' THEN
        RETURN ('Jun' || p_sexe);
    ELSE
        IF p_dateNaissance >= '1/1/1960' THEN
            RETURN ('Sen'||p_sexe);

        ELSE
            RETURN ('Vet'||p_sexe);
        END IF;
    END IF;
END;

--14
CREATE OR REPLACE PROCEDURE affichageResultatsRonde(p_idTounoi IN Parties.idTournoi%TYPE, p_numRonde IN Parties.numRonde%TYPE) IS

BEGIN

    FOR v_row IN (SELECT jb.nomJoueur AS nomB, jb.prenomJoueur AS preB, categorieJoueur(jb.dateNaissanceJoueur, jb.sexeJoueur) AS catB, jb.eloJoueur AS eloB, p.resultatPartie AS res, jn.nomJoueur AS nomN, jn.prenomJoueur AS preN, categorieJoueur(jn.dateNaissanceJoueur, jn.sexeJoueur) AS catN, jn.eloJoueur AS eloN
        FROM Parties p
        JOIN Joueurs jb ON p.idJoueurBlancs=jb.idJoueur
        JOIN Joueurs jn ON p.idJoueurNoirs=jn.idJoueur
        WHERE p.idTournoi=p_idTounoi
        AND p.numRonde=p_numRonde
        )
    LOOP
        DBMS_OUTPUT.PUT_LINE(v_row.nomB || ' ' || v_row.preB || ' ' || v_row.catB || ' ' || v_row.eloB || ' ' || v_row.res || ' ' || v_row.nomN || ' ' || v_row.preN || ' ' || v_row.catN || ' ' || v_row.eloN);
    END LOOP;
END;

--15
CREATE OR REPLACE PROCEDURE affichageInfosJoueur(p_idJoueur IN Joueurs.idJoueur%TYPE) IS
    v_player Joueurs%ROWTYPE;
BEGIN
    SELECT *
    INTO v_player
    FROM Joueurs
    WHERE idJoueur=p_idJoueur;

    DBMS_OUTPUT.PUT_LINE('Identifiant joueur : ' || v_player.idJoueur);
    DBMS_OUTPUT.PUT_LINE('Nom joueur : ' || v_player.nomJoueur);
    DBMS_OUTPUT.PUT_LINE('Prénom joueur : ' || v_player.prenomJoueur);
    DBMS_OUTPUT.PUT_LINE('Elo joueur : ' || v_player.eloJoueur);
    DBMS_OUTPUT.PUT_LINE('Catégorie joueur : ' || categorieJoueur(v_player.dateNaissanceJoueur, v_player.sexeJoueur));
    DBMS_OUTPUT.PUT_LINE('Club joueur : ' || v_player.idClub);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Le joueur ' || p_idJoueur || ' n éxiste pas.');
END;

--16
CREATE OR REPLACE FUNCTION resultatEnPoint(p_couleur IN VARCHAR, p_resultat IN Tournois.idTournoi%TYPE) RETURN NUMBER IS

BEGIN
    IF p_resultat='1-0' THEN
        IF p_couleur='B' THEN
            RETURN 1;
        ELSIF p_couleur='N' THEN
            RETURN 0;
        END IF;
    ELSIF p_resultat='0-1' THEN
        IF p_couleur='B' THEN
            RETURN 0;
        ELSIF p_couleur='N' THEN
            RETURN 1;
        END IF;
    ELSIF p_resultat='1/2' THEN
        RETURN 0.5;
    END IF;
END;

--17
CREATE OR REPLACE FUNCTION couleurJoueurRonde( p_idJoueur IN Joueurs.idJoueur%TYPE, p_idTournoi IN Tournois.idTournoi%TYPE, p_numRonde IN Parties.numRonde%TYPE ) RETURN VARCHAR IS
    v_nbB NUMBER;
    v_nbN NUMBER;
BEGIN
    SELECT COUNT(idJoueurBlancs)
    INTO  v_nbB
    FROM Parties
    WHERE idJoueurBlancs = p_idJoueur
    AND idTournoi = p_idTournoi
    AND numRonde = p_numRonde;

    SELECT COUNT(idJoueurNoirs)
    INTO  v_nbN
    FROM Parties
    WHERE idJoueurNoirs = p_idJoueur
    AND idTournoi = p_idTournoi
    AND numRonde = p_numRonde;

    IF v_nbB = 1 THEN
        RETURN 'B';
    ELSIF v_nbN = 1 THEN
        RETURN 'N';
    ELSE
        RETURN NULL;
    END IF;
END;


--18
CREATE OR REPLACE FUNCTION adversaireJoueurRonde( p_idJoueur IN Joueurs.idJoueur%TYPE, p_idTournoi IN Tournois.idTournoi%TYPE, p_numRonde IN Parties.numRonde%TYPE ) RETURN Joueurs.idJoueur%TYPE IS
    v_jB Joueurs.idJoueur%TYPE;
    v_jN Joueurs.idJoueur%TYPE;
BEGIN
    SELECT idJoueurBlancs, idJoueurNoirs
    INTO v_jB, v_jN
    FROM Parties
    WHERE (idJoueurBlancs = p_idJoueur
    OR idJoueurNoirs = p_idJoueur)
    AND idTournoi = p_idTournoi
    AND numRonde = p_numRonde;

    IF v_jB = p_idJoueur THEN
        RETURN v_jN;
    ELSIF v_jN = p_idJoueur THEN
        RETURN v_jB;
    ELSE
        RETURN NULL;
    END IF;
END;

--19
CREATE OR REPLACE FUNCTION resultatJoueurRonde( p_idJoueur IN Joueurs.idJoueur%TYPE, p_idTournoi IN Tournois.idTournoi%TYPE, p_numRonde IN Parties.numRonde%TYPE ) RETURN Joueurs.idJoueur%TYPE IS
    v_res Parties.resultatPartie%TYPE;
    v_col VARCHAR(1);
BEGIN

    v_col := couleurJoueurRonde(p_idJoueur, p_idTournoi, p_numRonde);


    SELECT resultatPartie
    INTO v_res
    FROM Parties
    WHERE idTournoi = p_idTournoi
    AND numRonde = p_numRonde
    AND (idJoueurBlancs = p_idJoueur
    OR idJoueurNoirs = p_idJoueur);

    RETURN (resultatEnPoint(v_col, v_res));
END;

--20
CREATE OR REPLACE PROCEDURE affichagePartiesJoueurTournoi( p_idJoueur IN Joueurs.idJoueur%TYPE, p_idTournoi IN Tournois.idTournoi%TYPE) IS
    v_partie Parties%ROWTYPE;
    v_advP Joueurs.prenomJoueur%TYPE;
    v_advN Joueurs.nomJoueur%TYPE;
    v_advE Joueurs.eloJoueur%TYPE;
    v_advC VARCHAR(4);
    v_col VARCHAR(1);
    v_res NUMBER;
    v_ronde Parties.numRonde%TYPE;
BEGIN
    FOR v_partie IN (SELECT * FROM Parties WHERE idTournoi = p_idTournoi AND (idJoueurBlancs = p_idJoueur OR idJoueurNoirs = p_idJoueur)) LOOP
        IF couleurJoueurRonde(p_idJoueur, v_partie.idTournoi, v_partie.numRonde)='B' THEN
            v_col := 'B';
            v_res := resultatJoueurRonde(p_idJoueur, p_idTournoi, v_partie.numRonde);
            SELECT nomJoueur, prenomJoueur, eloJoueur, categorieJoueur(dateNaissanceJoueur, sexeJoueur)
            INTO v_advN, v_advP, v_advE, v_advC
            FROM Joueurs
            WHERE idJoueur = v_partie.idJoueurNoirs;
        ELSE
            v_col := 'N';
            v_res := resultatJoueurRonde(p_idJoueur, p_idTournoi, v_partie.numRonde);
            SELECT nomJoueur, prenomJoueur, eloJoueur, categorieJoueur(dateNaissanceJoueur, sexeJoueur)
            INTO v_advN, v_advP, v_advE, v_advC
            FROM Joueurs
            WHERE idJoueur = v_partie.idJoueurBlancs;
        END IF;
            v_ronde := v_partie.numRonde;
            DBMS_OUTPUT.PUT_LINE('R'||v_ronde||' '||v_col||' '||v_advN||' '||v_advP||' '||v_advC||' '||v_advE||' '||v_res);
    END LOOP;
END;

--21
CREATE OR REPLACE FUNCTION nbPointsJoueurTournoi(p_idJoueur IN Joueurs.idJoueur%TYPE, p_idTournoi IN Tournois.idTournoi%TYPE) RETURN NUMBER IS
    v_res NUMBER;
BEGIN
    v_res := 0;
    FOR v_partie IN (SELECT * FROM Parties WHERE idTournoi = p_idTournoi AND (idJoueurBlancs = p_idJoueur OR idJoueurNoirs = p_idJoueur))
    LOOP
        v_res := v_res + resultatJoueurRonde(p_idJoueur, p_idTournoi, v_partie.numRonde);
    END LOOP;
    RETURN v_res;
END;


--22
CREATE OR REPLACE FUNCTION cumulatifJoueurTournoi(p_idJoueur IN Joueurs.idJoueur%TYPE, p_idTournoi IN Tournois.idTournoi%TYPE) RETURN NUMBER IS
    v_res NUMBER;
    v_tot NUMBER;
BEGIN
    v_res := 0;
    v_tot := 0;
    FOR v_partie IN (SELECT * FROM Parties WHERE idTournoi = p_idTournoi AND (idJoueurBlancs = p_idJoueur OR idJoueurNoirs = p_idJoueur))
    LOOP
        v_res := v_res + resultatJoueurRonde(p_idJoueur, p_idTournoi, v_partie.numRonde);
        v_tot := v_tot + v_res;
    END LOOP;
    RETURN v_tot;
END;

--23
CREATE OR REPLACE FUNCTION performanceEloJoueurTournoi(p_idJoueur IN Joueurs.idJoueur%TYPE, p_idTournoi IN Tournois.idTournoi%TYPE) RETURN NUMBER IS
    v_totEloAdv NUMBER;
    v_cmp NUMBER;
    v_eloAdv Joueurs.EloJoueur%TYPE;
BEGIN
    v_cmp := 0;
    v_totEloAdv := 0;
    FOR v_partie IN (SELECT * FROM Parties WHERE idTournoi = p_idTournoi AND (idJoueurBlancs = p_idJoueur OR idJoueurNoirs = p_idJoueur))
    LOOP
        IF couleurJoueurRonde(p_idJoueur, v_partie.idTournoi, v_partie.numRonde)='B' THEN
            SELECT EloJoueur INTO v_eloAdv FROM Joueurs WHERE idJoueur = v_partie.idJoueurNoirs;
            v_totEloAdv := v_totEloAdv + v_eloAdv;
        ELSE
            SELECT EloJoueur INTO v_eloAdv FROM Joueurs WHERE idJoueur = v_partie.idJoueurBlancs;
            v_totEloAdv := v_totEloAdv + v_eloAdv;
        END IF;
        v_cmp := v_cmp + 1;
    END LOOP;
    RETURN ROUND(v_totEloAdv/v_cmp + (nbPointsJoueurTournoi(p_idjoueur, p_idTournoi)/v_cmp-0.5)*750);
END;

--24
CREATE OR REPLACE PROCEDURE affichageResultatJoueur(p_idJoueur IN Joueurs.idJoueur%TYPE, p_idTournoi IN Tournois.idTournoi%TYPE) IS

BEGIN
    DBMS_OUTPUT.PUT_LINE('Nombre de points : ' || nbPointsJoueurTournoi(p_idJoueur, p_idTournoi));
    DBMS_OUTPUT.PUT_LINE('Cumulatif : ' || cumulatifJoueurTournoi(p_idJoueur, p_idTournoi));
    DBMS_OUTPUT.PUT_LINE('Performance : ' || performanceEloJoueurTournoi(p_idJoueur, p_idTournoi));
END;

--25
CREATE OR REPLACE FUNCTION estInvaincu(p_idJoueur IN Joueurs.idJoueur%TYPE, p_idTournoi IN Tournois.idTournoi%TYPE) RETURN NUMBER IS

BEGIN
    FOR v_partie IN (SELECT * FROM Parties WHERE idTournoi = p_idTournoi AND (idJoueurBlancs = p_idJoueur OR idJoueurNoirs = p_idJoueur))
    LOOP
        IF resultatJoueurRonde(p_idJoueur, p_idTournoi, v_partie.numRonde) = 0 THEN
            RETURN 0;
        END IF;
    END LOOP;
    RETURN 1;
END;

--26
CREATE OR REPLACE FUNCTION classementJoueurTournoi(p_idJoueur IN Joueurs.idJoueur%TYPE, p_idTournoi IN Tournois.idTournoi%TYPE) RETURN NUMBER IS
    v_cmp NUMBER;
BEGIN
    v_cmp := 1;
    FOR v_joueur IN  (SELECT DISTINCT idJoueur, nomJoueur, nbPointsJoueurTournoi(idJoueur, idTournoi) AS pts, cumulatifJoueurTournoi(idJoueur, idTournoi) AS cum, performanceEloJoueurTournoi(idJoueur, idTournoi) AS perf
        FROM Joueurs j
        JOIN Parties p ON idJoueurBlancs=idJoueur OR idJoueurNoirs=idJoueur
        WHERE idTournoi = p_idTournoi
        ORDER BY pts DESC, cum DESC, perf DESC, nomJoueur DESC)
    LOOP
        IF v_joueur.idJoueur = p_idJoueur THEN
            RETURN v_cmp;
        END IF;
        v_cmp := v_cmp + 1;
    END LOOP;
    RETURN NULL;
END;

--27
CREATE OR REPLACE PROCEDURE affichageClasmntJoueurTounoi(p_idJoueur IN Joueurs.idJoueur%TYPE, p_idTournoi IN Tournois.idTournoi%TYPE) IS
BEGIN
    IF estInvaincu(p_idJoueur, p_idTournoi) = 0 THEN
        DBMS_OUTPUT.PUT_LINE('Classement : ' || classementJoueurTournoi(p_idJoueur, p_idTournoi));
    ELSE
        DBMS_OUTPUT.PUT_LINE('Classement : ' || classementJoueurTournoi(p_idJoueur, p_idTournoi) || ' INVAINCU');
    END IF;
END;

--28
CREATE OR REPLACE PROCEDURE affichagaFicheJoueurTournoi(p_idJoueur IN Joueurs.idJoueur%TYPE, p_idTournoi IN Tournois.idTournoi%TYPE) IS
BEGIN
    affichageInfosJoueur(p_idJoueur);
    DBMS_OUTPUT.PUT_LINE(' ');
    affichagePartiesJoueurTournoi(p_idJoueur, p_idTournoi);
    DBMS_OUTPUT.PUT_LINE(' ');
    affichageResultatJoueur(p_idJoueur, p_idTournoi);
    affichageClasmntJoueurTounoi(p_idJoueur, p_idTournoi);
END;