ALTER TABLE utilizator add nr_likeuri NUMBER;

DECLARE
    TYPE rexord IS RECORD (userId utilizator.id%TYPE, nr_likeuri NUMBER);
    TYPE coll IS TABLE OF rexord;
    collectie coll;
BEGIN
    SELECT u.id ,COUNT(l.id)
    BULK COLLECT INTO collectie
    FROM utilizator u
    join postare p
    ON u.id = p.id_utilizator
    join likes l
    ON p.id = l.id_postare
    GROUP BY u.id;


    FOR i IN collectie.FIRST..collectie.LAST LOOP
        UPDATE utilizator SET nr_likeuri = i.nr_likeuri
        WHERE id = i.userId;
    END LOOP;
END;
/

CREATE OR REPLACE PROCEDURE p1 (UName utilizator.nume%TYPE) AS
    TYPE rexord IS RECORD (ID utilizator.id%TYPE, Nume utilizator.nume%TYPE);
    TYPE coll IS TABLE OF rexord;
    TYPE text_nrLike IS RECORD (text postare.text%TYPE, nrLikeuri NUMBER);
    TYPE coll1 IS TABLE OF text_nrLike;
    v_text_likeuri coll1;
    v_prieteni coll;
    v_id utilizator.id%TYPE;
BEGIN
    SELECT id
    INTO v_id
    FROM utilizator
    WHERE nume LIKE UName;

    SELECT pr.id_utilizator2, u.nume
    BULK COLLECT INTO v_prieteni
    FROM utilizator u
    join prieteni pr
    ON u.id = pr.id_utilizator2
    WHERE pr.id_utilizator1 = v_id;

    FOR i IN v_prieteni.FIRST..v_prieteni.LAST LOOP
        DBMS_OUTPUT.put_line(v_prieteni(i).Nume);

        SELECT p.text, COUNT(l.data)
        BULK COLLECT INTO v_text_likeuri
        FROM postare p
        join likes l
        ON p.id = l.id_postare
        WHERE p.id_utilizator = v_prieteni(i).ID
        GROUP BY p.text;

        FOR j IN v_text_likeuri.FIRST..v_text_likeuri.LAST LOOP
            DBMS_OUTPUT.put_line(v_text_likeuri(j).text || '  ' || v_text_likeuri(j).nrLikeuri);
        END LOOP;
    END LOOP;

END;
/

CREATE OR REPLACE TRIGGER t instead OF INSERT ON shares
    referencing NEW AS nou OLD AS vechi
DECLARE
    CURSOR c IS
    SELECT id_utilizator2 AS ID
    FROM prieteni
    WHERE id_utilizator1 = nou.id_utilizator_sursa;
    v_prieteni_ids id_utilizator2%TYPE;
    v_gasit INT := 0;
BEGIN
    FOR i IN c LOOP
        IF (nou.id_destinatie = i.ID) THEN
            v_gasit := 1;
        END IF;
    END LOOP;
    IF (v_gasit = 0) THEN
        raise_application_error(-20000,'NU');
    END IF;
    INSERT INTO shares VALUES (nou.id_utilizator_sursa, nou.id_utilizator_destinatie, nou.id_postare, nou.tip_destinatie);
END;
/

CREATE OR REPLACE TYPE lista_turisti IS TABLE OF turist.id%TYPE;
ALTER TABLE excursie add participanti lista_turisti;

DECLARE
    CURSOR exc IS
    SELECT id_excursie FROM excursie;
    v_coduri_turisti lista_turisti := lista_turisti();
    CURSOR c IS
    SELECT * FROM excursie
    WHERE participanti.COUNT < 10;
BEGIN
    FOR i IN exc LOOP
        v_coduri_turisti := lista_turisti();
        SELECT a.cod_turist
        BULK COLLECT INTO v_coduri_turisti
        FROM excursie e
        join achizitioneaza a
        ON excursie.id_excursie = achizitioneaza.cod_excursie
        WHERE e.id_excursie = i;
        UPDATE excursie SET participanti = v_coduri_turisti WHERE id_excursie = i;

    END LOOP;

    FOR i IN c LOOP
        UPDATE excursie SET este_anulata = 1 WHERE id_excursie = i.id_excursie;
    END LOOP;

END;
/


CREATE OR REPLACE PROCEDURE ex2 (cod agentie.cod_agentie%TYPE) AS
    CURSOR c IS
    SELECT * FROM excursie
    WHERE cod_agentie = cod;
    nr_excursii INT := 0;
BEGIN
    FOR i IN c LOOP
        nr_excursii := nr_excursii + 1;
        DBMS_OUTPUT(i.pret || TO_CHAR(sysdate-i.data_inceput, 'DD') || i.numar_locuri - i.participanti.COUNT);
    END LOOP;
    dmbs_output(nr_excursii);
END;
/

CREATE OR REPLACE PROCEDURE ex3 (nume agentie.nume_agentie%TYPE) AS
    cod_excursie excursie.id_excursie%TYPE;
    cod_agentie agentie.id_agentie%TYPE;
    id_turist turist.id_turist%TYPE;
BEGIN

    SELECT id_agentie
    INTO cod_agentie
    FROM agentie
    WHERE agentie.nume_agentie LIKE nume;

    SELECT id_excursie
    INTO cod_excursie
    FROM excursie
    WHERE excursie.cod_agentie = cod_agentie AND pret =
            (SELECT MAX(pret)
             FROM excursie
             WHERE excursie.cod_agentie = cod_agentie);

    SELECT t.id_turist
    INTO id_turist
    FROM turist t
    join achizitioneaza a
    ON a.cod_turist = t.id_turist
    WHERE a.cod_excursie = cod_excursie AND varsta =
    (SELECT MIN(t1.varsta)
     FROM turist t1
     join achizitioneaza a1
     ON a1.cod_cutist = t1.id_turist
     WHERE a1.cod_excursie = cod_excursie
    );
EXCEPTION
    WHEN TOO_MANY_ROWS THEN
        raise_application_error(-200001, 'Mai multe agentii cu acelasi nume');
    WHEN NO_DATA_FOUND THEN
        raise_application_error(-200002, 'Nu am gasit excursia cu pret maxim');
END;
/


CREATE OR REPLACE TRIGGER t1 before INSERT ON achizitioneaza
FOR each ROW
DECLARE
    v_exc excursie%ROWTYPE;
BEGIN
    SELECT *
    INTO v_exc
    FROM excursie
    WHERE id_excursie = :NEW.cod_excursie;
    IF (v_exc.numar_locuri = v_exc.participanti.COUNT) THEN
        raise_application_error(-20003, 'S-au epuizat locurile');
    END IF;
END;
/