-------------------------------------------------------------------------------
-- corr_pl_curseurs.sql 
-------------------------------------------------------------------------------
-- authentification :

create or replace procedure authentification(
    l_idc client.idc%type,
    le_nom client.nom%type)
is
    cursor c is
        select *
	    from client
	    where idc = l_idc
	      and nom = le_nom;
    le_client c%rowtype;
    message varchar2(50);
begin
    open c;
    fetch c into le_client;
    if c%found then
        message := 'bienvenue '||le_client.nom||', '||le_client.age
	           ||' ans, avoir '||le_client.avoir;
    else
        message := 'desole, erreur identifiant/nom';
    end if;
    dbms_output.put_line(message);
    close c;
end;
/

select * from client;
exec authentification(2, 'Rita');
exec authentification(1, 'Rita');

-------------------------------------------------------------------------------
-- consulter informations client :

-- forme curseurs generale :

create or replace procedure consulter_sejours1(
    l_idc client.idc%type)
is
    cursor c is
        select ids, idv, jour
            from sejour
            where sejour.idc = l_idc;
    l_ids sejour.ids%type;
    l_idv sejour.idv%type;
    le_jour sejour.jour%type;
begin
    open c;
    fetch c into l_ids, l_idv, le_jour;
    while c%found loop
        dbms_output.put_line('sejour '||l_ids||', '||l_idv||', '||le_jour);
        fetch c into l_ids, l_idv, le_jour;
    end loop;
    dbms_output.put_line('fin affichage');
end;
/

select * from sejour;
select * from village;
exec consulter_sejours1(1);
exec consulter_sejours1(5);

-- forme curseurs plus elegante :

create or replace procedure consulter_sejours2(
    l_idc client.idc%type)
is
    cursor c is
        select ids, idv, jour
            from sejour
            where sejour.idc = l_idc;
    l_ids sejour.ids%type;
    l_idv sejour.idv%type;
    le_jour sejour.jour%type;
begin
    open c;
    loop
        fetch c into l_ids, l_idv, le_jour;
        exit when c%notfound;
        dbms_output.put_line('sejour '||l_ids||', '||l_idv||', '||le_jour);
    end loop;
    dbms_output.put_line('fin affichage');
end;
/

select * from sejour;
select * from village;
exec consulter_sejours2(1);
exec consulter_sejours2(5);

-- forme curseur simplifiee :

create or replace procedure consulter_sejours3(
    l_idc client.idc%type)
is
begin
    for x in (
        select ids, idv, jour
            from sejour
            where sejour.idc = l_idc)
    loop
        dbms_output.put_line('sejour '||x.ids||', '||x.idv||', '||x.jour);
    end loop;
    dbms_output.put_line('fin affichage');
end;
/

select * from sejour;
select * from village;
exec consulter_sejours3(1);
exec consulter_sejours3(5);

-- forme curseur avec variable rowtype curseur (rowtype sejour pas adapte) :

create or replace procedure consulter_sejours4(
    l_idc client.idc%type)
is
    cursor c is
        select ids, idv, jour
            from sejour
            where sejour.idc = l_idc;
    le_client c%rowtype;
begin
    open c;
    fetch c into le_client;
    while c%found loop
        dbms_output.put_line(
	    'sejour '||le_client.ids||', '||le_client.idv||', '
	    ||le_client.jour);
        fetch c into le_client;
    end loop;
    dbms_output.put_line('fin affichage');
end;
/

select * from sejour;
select * from village;
exec consulter_sejours4(1);
exec consulter_sejours4(5);

-- rem : consulter_informations avec for-x-in ; autres formes ci-dessus

create or replace procedure consulter_informations(
    l_idc client.idc%type)
is
begin
    for x in (
        select ids, idv, jour
            from sejour
            where sejour.idc = l_idc)
    loop
        dbms_output.put_line('sejour '||x.ids||', '||x.idv||', '||x.jour);
    end loop;
    dbms_output.put_line('fin sejours');
    for x in (
        select distinct village.idv, ville, activite, prix, capacite
            from village, sejour
            where sejour.idc = l_idc
              and village.idv = sejour.idv)
    loop
        dbms_output.put_line(
	    'village '||x.idv||', a '||x.ville||', '||x.activite||', '
	    ||x.prix||' euros/jour, '||x.capacite||' lits');
    end loop;
    dbms_output.put_line('fin villages');
end;
/

select * from sejour;
select * from village;
exec consulter_informations(1);
exec consulter_informations(5);

-------------------------------------------------------------------------------
-- traitement 2 :

create or replace procedure traitement2(
    l_idc client.idc%type,
    la_ville village.ville%type,
    le_jour sejour.jour%type,
    l_idv out village.idv%type,
    l_ids out sejour.ids%type,
    l_activite out village.activite%type)   
is
    cursor c is
        select idv, prix, activite 
            from village 
            where ville = la_ville
            order by prix desc;
    le_prix village.prix%type;
begin
    open c;
    fetch c into l_idv, le_prix, l_activite;
    if c%found then
        l_ids := seq_sejour.nextval; 
        insert into sejour 
            values(l_ids, l_idc, l_idv, le_jour);
        update client 
            set avoir = avoir - le_prix
            where idc = l_idc;
    else
        l_idv := -1;
	l_ids := -1;
	l_activite := 'neant';
    end if;
end;
/

select * from client;
select * from village;
select * from sejour;

declare
    iv village.idv%type;
    l_ids sejour.ids%type;
    a village.activite%type;
begin
    traitement2(1, 'Chatelaillon', 361, iv, l_ids, a);
    dbms_output.put_line('idv '||iv||', ids '||l_ids||', activite '||a);
    traitement2(1, 'Chatelaillon', 360, iv, l_ids, a);
    dbms_output.put_line('idv '||iv||', ids '||l_ids||', activite '||a);
end;
/
select * from sejour;

--- Version plus complète:
CREATE OR REPLACE PROCEDURE traitement2 (
    p_idc IN NUMBER,
    p_ville IN VARCHAR2,
    p_jour IN NUMBER,
    p_idv OUT NUMBER,
    p_ids OUT NUMBER,
    p_activite OUT VARCHAR2
) AS
    -- Variables locales
    CURSOR cur_villages IS
        SELECT idv, activite, prix
        FROM Village
        WHERE ville = p_ville
        ORDER BY prix DESC; -- Village avec le prix le plus élevé en premier

    v_idv Village.idv%TYPE;
    v_activite Village.activite%TYPE;
    v_prix Village.prix%TYPE;
    v_avoir Client.avoir%TYPE;

BEGIN
    -- Initialiser les sorties
    p_idv := -1;
    p_ids := -1;
    p_activite := NULL;

    -- Récupérer l'avoir du client
    SELECT avoir INTO v_avoir
    FROM Client
    WHERE idc = p_idc;

    -- Ouvrir le curseur
    OPEN cur_villages;
    FETCH cur_villages INTO v_idv, v_activite, v_prix;

    IF cur_villages%FOUND THEN
        -- Vérifier si le client peut payer
        IF v_avoir >= v_prix THEN
            -- Créer le séjour
            INSERT INTO Sejour (ids, idc, idv, jour)
            VALUES (Sejour_seq.NEXTVAL, p_idc, v_idv, p_jour)
            RETURNING ids INTO p_ids;

            -- Mettre à jour l'avoir du client
            UPDATE Client
            SET avoir = avoir - v_prix
            WHERE idc = p_idc;

            -- Mettre à jour les sorties
            p_idv := v_idv;
            p_activite := v_activite;
        END IF;
    END IF;

    -- Fermer le curseur
    CLOSE cur_villages;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_idv := -1;
        p_ids := -1;
        p_activite := NULL;

    WHEN OTHERS THEN
        RAISE;
END traitement2;
/

DECLARE
    v_idv NUMBER;
    v_ids NUMBER;
    v_activite VARCHAR2(100);
BEGIN
    traitement2(1, 'Rio', 365, v_idv, v_ids, v_activite);
    DBMS_OUTPUT.PUT_LINE('Village ID: ' || v_idv);
    DBMS_OUTPUT.PUT_LINE('Sejour ID: ' || v_ids);
    DBMS_OUTPUT.PUT_LINE('Activité: ' || NVL(v_activite, 'Néant'));
END;
/

-- Alternative

CREATE OR REPLACE PROCEDURE traitement2 (
    p_idc IN NUMBER,
    p_ville IN VARCHAR2,
    p_jour IN NUMBER,
    p_idv OUT NUMBER,
    p_ids OUT NUMBER,
    p_activite OUT VARCHAR2
) AS
    -- Variables locales
    CURSOR cur_villages IS
        SELECT idv, prix, activite
        FROM Village
        WHERE ville = p_ville
        ORDER BY prix DESC; -- Trier par prix décroissant

    v_idv Village.idv%TYPE;
    v_prix Village.prix%TYPE;
    v_activite Village.activite%TYPE;

    v_avoir Client.avoir%TYPE;
BEGIN
    -- Initialiser les sorties par défaut
    p_idv := -1;
    p_ids := -1;
    p_activite := 'neant';

    -- Récupérer l'avoir du client
    SELECT avoir INTO v_avoir
    FROM Client
    WHERE idc = p_idc;

    -- Ouvrir et traiter le curseur
    OPEN cur_villages;
    FETCH cur_villages INTO v_idv, v_prix, v_activite;

    IF cur_villages%FOUND THEN
        -- Le village existe, vérifier que le client peut payer
        IF v_avoir >= v_prix THEN
            -- Créer un nouvel identifiant pour le séjour
            p_ids := Sejour_seq.NEXTVAL;

            -- Insérer le séjour
            INSERT INTO Sejour (ids, idc, idv, jour)
            VALUES (p_ids, p_idc, v_idv, p_jour);

            -- Mettre à jour l'avoir du client
            UPDATE Client
            SET avoir = avoir - v_prix
            WHERE idc = p_idc;

            -- Mettre à jour les sorties
            p_idv := v_idv;
            p_activite := v_activite;
        END IF;
    END IF;

    -- Fermer le curseur
    CLOSE cur_villages;
END traitement2;
/


-------------------------------------------------------------------------------
