CREATE OR REPLACE PACKAGE BODY ALIMCC
IS
    --Paramètre : Id du film à envoyer
    PROCEDURE MOVIE_COPY_GENERATOR(v_film_id IN NUMBER)
    AS
        nbrCopieDispo NUMBER;
        nbrCopieTransfert NUMBER;
        documentXML XMLTYPE;

        TYPE tabCopy IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
        copy tabCopy;

        parc NUMBER;

    BEGIN
        --Génération du nombre de copie à envoyer sur base du nombre de copies présentes sur CB
        SELECT COUNT(*) INTO nbrCopieDispo FROM COPIES WHERE MOVIE = v_film_id;

        nbrCopieTransfert := FLOOR(dbms_random.VALUE(0, nbrCopieDispo));

        IF nbrCopieTransfert > 0 THEN
            --Generation des infos du film en XML
            SELECT XMLElement(  "film",
                                XMLForest(  MOVIES.idmovie AS "id_film",
                                            MOVIES.title AS "titre",
                                            MOVIES.original_title AS "titre_original",
                                            TO_CHAR(MOVIES.release_date, 'YYYY/MM/DD') AS "date_sortie",
                                            MOVIES.STATUS AS "status",
                                            to_char(MOVIES.vote_average,'000.000') AS "note_moyenne",
                                            MOVIES.vote_count AS "nbr_note",
                                            MOVIES.runtime AS "runtime",
                                            MOVIES.certification AS "certification",
                                            --(SELECT IMAGE FROM MOVIES INNER JOIN PICTURES ON MOVIES.poster_path = PICTURES.id_picture where MOVIES.idmovie = v_film_id) AS "affiche",
                                            MOVIES.budget AS "budget",
                                            MOVIES.revenu AS "revenus",
                                            MOVIES.homepage AS "homepage",
                                            MOVIES.tagline AS "tagline",
                                            MOVIES.overview AS "overview"),
                                -- <genres> <genre> <id_genre></......>
                                XMLElement  ("genres", (SELECT XMLAgg( XMLElement(  "genre",
                                                                                    XMLForest(GENRES.id AS "id_genre", GENRES.name AS "nom")))
                                                    FROM MOVIES
                                                    INNER JOIN GENRE_MOVIES ON MOVIES.idmovie = GENRE_MOVIES.movie
                                                    INNER JOIN GENRES ON GENRE_MOVIES.id = GENRES.id
                                                    WHERE MOVIES.idmovie = v_film_id
                                                )
                                            ),

                                XMLElement  ("langues", (SELECT XMLAgg( XMLElement("langue",
                                                                                    XMLForest(LANGUAGES.code AS "id_langue", LANGUAGES.name AS "nom")))
                                                    FROM MOVIES
                                                    INNER JOIN SPOKEN_LANGUAGES ON MOVIES.idmovie = SPOKEN_LANGUAGES.movie
                                                    INNER JOIN LANGUAGES ON SPOKEN_LANGUAGES.code = LANGUAGES.code
                                                    WHERE MOVIES.idmovie = v_film_id
                                                )
                                            ),

                                XMLElement  ("listPays", (SELECT XMLAgg( XMLElement("pays",
                                                                                    XMLForest(COUNTRIES.code_country AS "id_pays", COUNTRIES.name_country AS "nom")))
                                                    FROM MOVIES
                                                    INNER JOIN PRODUCTION_COUNTRY_MOVIES ON MOVIES.idmovie = PRODUCTION_COUNTRY_MOVIES.movie
                                                    INNER JOIN COUNTRIES ON PRODUCTION_COUNTRY_MOVIES.code_country = COUNTRIES.code_country
                                                    WHERE MOVIES.idmovie = v_film_id
                                                )
                                            ),

                                XMLElement  ("acteurs", (SELECT XMLAgg( XMLElement("acteur",
                                                                                XMLForest(ARTISTES.id_artiste AS "id_acteur", ARTISTES.name_artiste AS "nom", ARTISTES.img_artiste AS "lien_photo", PERSONNAGES.name_personnage AS "nom_role")))
                                                    FROM MOVIES
                                                    INNER JOIN ACTORS_MOVIES ON MOVIES.idmovie = ACTORS_MOVIES.id_movie
                                                    INNER JOIN ARTISTES ON ACTORS_MOVIES.id_acteur = ARTISTES.id_artiste
                                                    INNER JOIN PERSONNAGES ON PERSONNAGES.id_personnage = ACTORS_MOVIES.id_acteur AND PERSONNAGES.movie = ACTORS_MOVIES.id_movie
                                                    WHERE MOVIES.idmovie = v_film_id
                                                )
                                            ),

                                XMLElement  ("listAvis", (SELECT XMLAgg( XMLElement("avis",
                                                                                XMLForest(AVIS.cote AS "note", AVIS.commentaire AS "commentaire")))
                                                    FROM MOVIES
                                                    INNER JOIN AVIS ON MOVIES.idmovie = AVIS.id_movie
                                                    WHERE MOVIES.idmovie = v_film_id
                                                )
                                            )
                            )
            INTO documentXML
            FROM MOVIES
            WHERE idmovie = v_film_id;

            --Insertion du XML généré dans la table de communication avec CC
            INSERT INTO TMPXMLMOVIE
            VALUES(documentXML);

            --generation des copies de film à envoyer sur CC
            PROCEDURE_LOG('ALIMCC - AJOUT DE ' || nbrCopieTransfert ||' COPIES DU FILM ' || v_film_id);

            SELECT ID_COPIE BULK COLLECT INTO copy
            FROM (SELECT ID_COPIE, ROWNUM FROM COPIES WHERE MOVIE = v_film_id)
            WHERE ROWNUM <= nbrCopieTransfert;

            FOR parc IN copy.FIRST..copy.LAST LOOP

                SELECT XMLElement("copie", XMLForest(   v_film_id AS "idFilm",
                                                        copy(parc) AS "numCopy"))
                INTO documentXML
                FROM DUAL;

                INSERT INTO tmpXMLCopie VALUES(documentXML); --Insertion de la copie sélectionnée dans la table de communicaiton avec CC
                DELETE FROM COPIES WHERE MOVIE = v_film_id AND ID_COPIE = copy(parc);--Suppression sur CB de la copie envoyée

            END LOOP;

        END IF;

    EXCEPTION
        WHEN OTHERS THEN PROCEDURE_LOG('procedure alimCC ERREUR : ' ||SQLERRM); ROLLBACK;
    END;



    PROCEDURE JOB
    AS
        f MOVIES%ROWTYPE;
    BEGIN
        --Boucle sur tous les films présents dans CB
        FOR f IN (SELECT * FROM MOVIES) LOOP

            ALIMCC.MOVIE_COPY_GENERATOR(f.idmovie);

        END LOOP;

        COMMIT;

        RECEPTION_FILM@CC2.DBL; --Demande à CC de lire la table remplie lors de MOVIE_COPY_GENERATOR

    EXCEPTION
        WHEN OTHERS THEN PROCEDURE_LOG('procedure alimCC JOB ERREUR : ' ||SQLERRM);
    ROLLBACK;
    END;

END;