/*
 * DAIS 2013/2014
 * cv-3_task-1_1.sql
 */
CREATE OR REPLACE PROCEDURE SkoreKolo(p_nazevLiga Liga.nazev%TYPE, p_nazevRocnik Rocnik.nazev%TYPE, p_poradiKola Zapas.poradi_kola%TYPE)
AS
  v_idLiga Liga.idLiga%TYPE;
  v_idRocnik Rocnik.idRocnik%TYPE;
  v_sumSkoreDomaci NUMBER;
  v_sumSkoreHoste NUMBER;
BEGIN
  SELECT idLiga INTO v_idLiga FROM Liga WHERE nazev = p_nazevLiga;
  SELECT idRocnik INTO v_idRocnik FROM Rocnik WHERE idLiga = v_idLiga AND nazev = p_nazevRocnik;

  SELECT SUM(skore_domaci), SUM(skore_hoste) INTO v_sumSkoreDomaci, v_sumSkoreHoste  FROM Zapas WHERE idRocnik = v_idRocnik AND poradi_kola = p_poradiKola;
  INSERT INTO SkoreKoloTab(idLiga, idRocnik, poradi_kola, skore_domaci, skore_hoste) VALUES(v_idLiga, v_idRocnik, p_poradiKola, v_sumSkoreDomaci, v_sumSkoreHoste);

  DBMS_OUTPUT.PUT_LINE(v_idLiga || ', ' || v_idRocnik || ', ' || p_poradiKola || ': ' || v_sumSkoreDomaci || ':' || v_sumSkoreHoste);
END;

EXECUTE SkoreKolo('Extraliga ledního hokeje', '2012/2013', 52);

SELECT * FROM SkoreKoloTab;

CREATE TABLE SkoreKoloTab(
  idLiga NUMBER,
  idRocnik NUMBER,
  poradi_kola NUMBER,
  skore_domaci NUMBER NOT NULL,
  skore_hoste NUMBER NOT NULL,
  PRIMARY KEY(idLiga, idRocnik, poradi_kola));

DROP TABLE SkoreKoloTab;

/*
 * DAIS 2013/2014
 * cv-3_task-1_2.sql
 */
CREATE OR REPLACE TRIGGER aktualizujBodyHrace BEFORE INSERT ON Bodovani
  FOR EACH ROW
BEGIN
  /* Uloha je jednoducha, zadani by mohlo byt obohaceno o kontrolu
     zda hrac je hracem daneho tymu apod. */
  UPDATE Hrac SET BODY = BODY + 1 WHERE idHrac = :NEW.idHrac;
END;

DROP TRIGGER aktualizujBodyHrace;

DECLARE
  v_idZapas Zapas.idZapas%TYPE;
BEGIN
  SELECT idZapas INTO v_idZapas FROM Zapas WHERE  poradi_kola=52 AND domaci=13 AND hoste=9;
  INSERT INTO Bodovani VALUES(v_idZapas, 4, 'a');
  DBMS_OUTPUT.PUT_LINE(v_idZapas);
END;

SELECT * FROM Hrac WHERE idHrac=4;
SELECT * FROM Bodovani WHERE idZapas=5 AND idHrac=4;

/*
 * DAIS 2013/2014
 * cv-3_task-2_1.sql
 */
CREATE OR REPLACE PROCEDURE PrintNejlepsiHrac
(p_idRocnik IN rocnik.idRocnik%TYPE, p_idTymu IN Tym.idTym%TYPE)
AS
    v_nazevLigy liga.nazev%TYPE;
    v_nazevTymu tym.nazev%TYPE;
    v_nazevRocniku rocnik.nazev%TYPE;
    v_idhrac1 hrac.idhrac%TYPE;
    v_post VARCHAR(15);
    v_hrac hrac%ROWTYPE;
    v_goly NUMBER;

    TYPE array_t IS varray(4) OF VARCHAR2(2);
    ARRAY array_t := array_t('l', 'p', 'c', 'd');
BEGIN
    SELECT nazev INTO v_nazevRocniku FROM rocnik WHERE idRocnik = p_idRocnik;
    SELECT nazev INTO v_nazevLigy FROM liga WHERE idLiga IN
    (SELECT idLiga FROM Rocnik WHERE idRocnik = p_idRocnik);
    SELECT nazev INTO v_nazevTymu FROM tym  WHERE idTym = p_idTymu;

    DBMS_OUTPUT.put_line('Název ročníku ' || v_nazevRocniku);
    DBMS_OUTPUT.put_line('Název ligy ' || v_nazevLigy);
    DBMS_OUTPUT.put_line('Název tymu ' || v_nazevTymu);

    FOR i IN 1..ARRAY.COUNT LOOP
    BEGIN

        CASE ARRAY(i)
            WHEN 'l' THEN v_post := 'Levé křídlo';
            WHEN 'p' THEN v_post := 'Pravé křídlo';
            WHEN 'c' THEN v_post := 'Centr';
            WHEN 'd' THEN v_post := 'Obránce';
        END CASE;

    SELECT idHrac, COUNT(*) INTO v_idhrac1,v_goly FROM bodovani
    WHERE idZapas IN
      (SELECT idZapas FROM Zapas WHERE idRocnik = p_idRocnik AND (domaci = p_idTymu OR hoste = p_idTymu) AND typ = 'g')
    AND idHrac IN
      (SELECT idhrac FROM tymhrac WHERE idtym = p_idTymu AND idhrac IN (SELECT idhrac FROM hrac WHERE post = ARRAY(i) ))
    AND ROWNUM = 1
    GROUP BY idHrac
    ORDER BY COUNT(typ) ;

    SELECT * INTO v_hrac FROM hrac WHERE idhrac = v_idhrac1;

    DBMS_OUTPUT.put_line(v_post || ': ' || v_hrac.jmeno || ' ' ||v_hrac.prijmeni ||  ' ,' ||  v_goly );

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
        BEGIN
            DBMS_OUTPUT.PUT_LINE(v_post || ': '|| 'NENALEZEN');
            CONTINUE;
        END;
    END;
    END LOOP;

END;

/*
 * DAIS 2013/2014
 * cv-3_task-2_2.sql
 */
CREATE OR REPLACE PROCEDURE VlozZapas(p_nazev_ligy Liga.nazev%TYPE, p_nazev_rocnik Rocnik.nazev%TYPE, p_poradi_kola Zapas.poradi_kola%TYPE,
                                      p_domaci Zapas.domaci1%TYPE, p_hoste Zapas.hoste1%TYPE, p_datum Zapas.datum%TYPE, p_skore_domaci Zapas.skore_domaci%TYPE,
                                      p_skore_hoste Zapas.skore_hoste%TYPE, p_navsteva Zapas.navsteva%TYPE)
AS
  v_id_liga Liga.idLiga%TYPE;
  v_id_rocnik Rocnik.idRocnik%TYPE;
BEGIN
  SELECT idLiga INTO v_id_liga FROM Liga WHERE nazev = p_nazev_ligy;
  SELECT idRocnik INTO v_id_rocnik FROM Rocnik WHERE nazev = p_nazev_rocnik AND idLiga = v_id_liga;
  INSERT INTO Zapas(idRocnik, poradi_kola, domaci1, hoste1, datum, skore_domaci, skore_hoste, navsteva)
  VALUES(v_id_rocnik, p_poradi_kola, p_domaci, p_hoste, p_datum, p_skore_domaci, p_skore_hoste, p_navsteva);

  IF p_skore_domaci > p_skore_hoste THEN
    UPDATE Ucastnik SET BODY = BODY + 2 WHERE idRocnik = v_id_rocnik AND idTym = p_domaci;
  ELSE IF p_skore_domaci < p_skore_hoste THEN
    UPDATE Ucastnik SET BODY = BODY + 2 WHERE idRocnik = v_id_rocnik AND idTym = p_hoste;
  ELSE
    UPDATE Ucastnik SET BODY = BODY + 1 WHERE idRocnik = v_id_rocnik AND idTym = p_domaci;
    UPDATE Ucastnik SET BODY = BODY + 1 WHERE idRocnik = v_id_rocnik AND idTym = p_hoste;
  END IF;
  COMMIT;
END;


/*
 * DAIS 2013/2014
 * cv-3_task-3_1.sql
 */
CREATE OR REPLACE PROCEDURE VypisRocniky
AS
  CURSOR vypisrocniky_cursor IS SELECT Liga.NAZEV AS liga_nazev, rocnik.nazev AS rocnik_nazev, idRocnik, zacatek, konec
                                FROM rocnik join liga ON Rocnik.idLiga = liga.idLiga
                                WHERE zacatek <= CURRENT_DATE AND TRUNC(CURRENT_DATE) <= konec;
  poradi NUMBER;
BEGIN
  FOR rocnik_radek IN vypisrocniky_cursor LOOP
    poradi := 1;
    DBMS_OUTPUT.put_line(rocnik_radek.liga_nazev || ':' || rocnik_radek.rocnik_nazev || ' od: ' || rocnik_radek.zacatek || ' do: ' || rocnik_radek.konec );
    DBMS_OUTPUT.put_line('Poradí Název Pocet bodu');
    FOR ucastnik_radek IN (SELECT nazev, BODY FROM ucastnik join tym ON ucastnik.idTym = tym.idTym WHERE ucastnik.idrocnik = rocnik_radek.idRocnik ORDER BY ucastnik.BODY DESC) LOOP
        DBMS_OUTPUT.put_line(poradi || '. ' || ucastnik_radek.nazev || ' ' || ucastnik_radek.BODY);
        poradi := poradi + 1;
    END LOOP;
  END LOOP;
END VypisRocniky;

/*
 * DAIS 2013/2014
 * cv-3_task-3_2.sql
 */
CREATE OR REPLACE PROCEDURE VlozZapas(p_nazev_ligy Liga.nazev%TYPE, p_nazev_rocnik Rocnik.nazev%TYPE, p_poradi_kola Zapas.poradi_kola%TYPE,
                                      p_domaci Zapas.domaci1%TYPE, p_hoste Zapas.hoste1%TYPE, p_datum Zapas.datum%TYPE, p_skore_domaci Zapas.skore_domaci%TYPE,
                                      p_skore_hoste Zapas.skore_hoste%TYPE, p_navsteva Zapas.navsteva%TYPE)
AS
  v_id_liga Liga.idLiga%TYPE;
  v_id_rocnik Rocnik.idRocnik%TYPE;
BEGIN
  SELECT idLiga INTO v_id_liga FROM Liga WHERE nazev = p_nazev_ligy;
  SELECT idRocnik INTO v_id_rocnik FROM Rocnik WHERE nazev = p_nazev_rocnik AND idLiga = v_id_liga;
  INSERT INTO Zapas(idRocnik, poradi_kola, domaci1, hoste1, datum, skore_domaci, skore_hoste, navsteva)
  VALUES(v_id_rocnik, p_poradi_kola, p_domaci, p_hoste, p_datum, p_skore_domaci, p_skore_hoste, p_navsteva);

  IF p_skore_domaci > p_skore_hoste THEN
    UPDATE Ucastnik SET BODY = BODY + 2 WHERE idRocnik = v_id_rocnik AND idTym = p_domaci;
  ELSE IF p_skore_domaci < p_skore_hoste THEN
    UPDATE Ucastnik SET BODY = BODY + 2 WHERE idRocnik = v_id_rocnik AND idTym = p_hoste;
  ELSE
    UPDATE Ucastnik SET BODY = BODY + 1 WHERE idRocnik = v_id_rocnik AND idTym = p_domaci;
    UPDATE Ucastnik SET BODY = BODY + 1 WHERE idRocnik = v_id_rocnik AND idTym = p_hoste;
  END IF;
  COMMIT;
END;