CREATE OR REPLACE FUNCTION checkAndInsertUser(
    p_username IN USERS_TABLE.USERNAME%TYPE,
    p_email IN USERS_TABLE.EMAIL%TYPE,
    p_password IN USERS_TABLE.PASSWORD%TYPE,
    p_balance IN USERS_TABLE.BALANCE%TYPE
) RETURN USERS_TABLE%ROWTYPE IS
    v_user users_table%ROWTYPE;
BEGIN
    -- Ellenőrizzük, hogy a felhasználó regisztrált-e
    SELECT * INTO v_user FROM users_table WHERE EMAIL = p_email;

    -- Ha a felhasználó megtalálható az adatbázisban, visszaadjuk
    RETURN v_user;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- Ha a felhasználót nem találjuk meg, beszúrjuk az új felhasználót
        BEGIN
            INSERT INTO users_table (USERNAME, EMAIL, PASSWORD, BALANCE)
            VALUES (p_username, p_email, p_password, p_balance);
            COMMIT;

            -- Újra lekérdezzük az újonnan beszúrt felhasználót és visszaadjuk
            SELECT * INTO v_user FROM users_table WHERE EMAIL = p_email;
            RETURN v_user;
        EXCEPTION
            WHEN OTHERS THEN
                -- Ha valamilyen egyéb hiba történik, visszaadjuk a null-t
                RETURN NULL;
        END;
    WHEN OTHERS THEN
        -- Ha valamilyen egyéb hiba történik, visszaadjuk a null-t
        RETURN NULL;
END;
/

-- DECLARE
--     v_result USERS_TABLE%ROWTYPE;
-- BEGIN
--     v_result := checkAndInsertUser('teszt_user', 'teszt_email', 'teszt_jelszo', 100);
--     IF v_result.EMAIL IS NOT NULL THEN
--         DBMS_OUTPUT.PUT_LINE('Felhasználó neve: ' || v_result.USERNAME);
--     ELSE
--         DBMS_OUTPUT.PUT_LINE('Hiba történt a felhasználó ellenőrzése vagy beszúrása közben.');
--     END IF;
-- END;
-- /


create or replace function checkBalance(p_user_id NUMBER, schedule_id NUMBER, p_amount NUMBER) return number is
    v_balance users_table.balance%type;
    v_price   schedule.price%type;
begin
    select balance into v_balance from users_table where id = p_user_id;
    select price into v_price from schedule where id = schedule_id;

    if v_balance >= v_price * p_amount then
        return -1;
    else
        return ROUND(v_balance / v_price);
    end if;
end;



create or replace function checkSeats(p_schedule_id NUMBER, amount NUMBER) return number is
    v_max_tickets  number;
    v_sold_tickets number;
begin
    select max_tickets into v_max_tickets from schedule where id = p_schedule_id;
    select count(*) into v_sold_tickets from tickets where schedule_id = p_schedule_id;
    if v_max_tickets - (amount + v_sold_tickets) >= 0 then
        return 0;
    else
        return -1;
    end if;

end;

create or replace function buyTicket(p_user_id NUMBER, schedule_id NUMBER, p_amount NUMBER, v_you_can_buy OUT number,
                                     v_available_tickets OUT number) return number is
begin
    v_you_can_buy := checkBalance(p_user_id, schedule_id, p_amount);
    if v_you_can_buy = -1 then
        v_available_tickets := checkSeats(schedule_id, p_amount);
        if v_available_tickets = 0 then
            update users_table
            set balance = balance - p_amount * (select price from schedule where id = schedule_id)
            where id = p_user_id;
            for i in 1..p_amount
                loop
                    insert into tickets (user_id, schedule_id) values (p_user_id, schedule_id);
                end loop;
            commit;
            return 1;
        else
            DBMS_OUTPUT.PUT_LINE('Nincs elég szabad hely a megadott mennyiségű jegy megvásárlásához.');
            return 0;
        end if;

    else
        DBMS_OUTPUT.PUT_LINE('Nincs elég egyenlege a megadott mennyiségű jegy megvásárlásához. Elegendő jegy: ' ||
                             v_you_can_buy);
        return -1;
    end if;
end;
/



create or replace function GETSCHEDULEBYPLAYTYPE_AND_DATE(p_type VARCHAR2, p_date DATE, schedule_id NUMBER)
    return SYS_REFCURSOR is
    v_cursor SYS_REFCURSOR;
begin
    open v_cursor for
        select SCHEDULE.ID
        from schedule
                 join plays on SCHEDULE.PLAY_ID = plays.id
        where PLAY_TYPE = p_type
          and TRUNC(PLAY_DATE) = p_date
          and SCHEDULE.ID != schedule_id;
    return v_cursor;
end;

    create or replace function GETSCHEDULEBYPLAYTYPE_ORDERED(p_type VARCHAR2, schedule_id NUMBER)
    return SYS_REFCURSOR is
    v_cursor SYS_REFCURSOR;
begin
    open v_cursor for
        select SCHEDULE.ID
        from schedule
                 join plays on SCHEDULE.PLAY_ID = plays.id
        where PLAY_TYPE = p_type
          and SCHEDULE.ID != schedule_id
        ORDER BY SCHEDULE.PRICE ;

    return v_cursor;
end;

create or replace function getScheduleWithLowestPrice(p_cursor IN SYS_REFCURSOR) return NUMBER is
    v_schedule_id     NUMBER;
    v_price           NUMBER;
    v_min_price       NUMBER;
    v_min_schedule_id NUMBER;
begin

    select MAX(price) into v_min_price from schedule;
    LOOP
        FETCH p_cursor INTO v_schedule_id;
        EXIT WHEN p_cursor%NOTFOUND;
        select price into v_price from schedule where id = v_schedule_id;
        if v_price < v_min_price then
            v_min_price := v_price;
            v_min_schedule_id := v_schedule_id;
        end if;
    END LOOP;
    return v_min_schedule_id;
end;



create or replace function get_Schedule_By_TheatreAndPlay(p_theatre_id Number, p_play_id NUMBER, schedule_id NUMBER)
    return SYS_REFCURSOR is
    v_cursor SYS_REFCURSOR;
begin
    open v_cursor for
        select SCHEDULE.ID
        from schedule
                 join plays on SCHEDULE.PLAY_ID = plays.id
        where PLAY_ID = p_play_id
          and THEATRE_ID = p_theatre_id
          and SCHEDULE.ID != schedule_id;
    return v_cursor;
end;





-- jegy vasarlas.
-- Ha nincs elegendo hely, akkor a tipus es datum szerint,
-- keres mas eloadasokat, a legolcsobbikbol megveszi az osszeset.

-- A procedura kiirja a megvasarolt jegyek arat.
-- PLSQL

CREATE OR REPLACE PROCEDURE main(p_username VARCHAR2,
                                 p_password VARCHAR2,
                                 p_email VARCHAR2,
                                 p_balance NUMBER,
                                 p_schedule_id NUMBER,
                                 p_amount NUMBER) IS
    v_user                 users_table%ROWTYPE;
    v_you_can_buy          NUMBER;
    v_available_tickets    NUMBER;
    v_purchase_status      NUMBER;
    v_schedules_cursor     SYS_REFCURSOR;
    v_play_type            VARCHAR2(100); -- Specify appropriate size
    v_play_time            DATE;
    v_cheapest_schedule_id NUMBER;
    v_price                NUMBER;
BEGIN
    v_user := CHECKANDINSERTUSER(p_username, p_email, p_password, p_balance);
    DBMS_OUTPUT.PUT_LINE('Felhasznalo neve: ' || v_user.username);

    v_purchase_status := BUYTICKET(v_user.id,
                                   p_schedule_id,
                                   p_amount,
                                   v_you_can_buy,
                                   v_available_tickets);

    IF v_purchase_status = 1 THEN
        SELECT price INTO v_price FROM schedule WHERE id = p_schedule_id;
        DBMS_OUTPUT.PUT_LINE('Sikeresen megvasarolt jegyek ara: ' || p_amount * v_price);

    ELSIF v_purchase_status = 0 THEN
        SELECT play_type, TRUNC(PLAY_DATE)
        INTO v_play_type, v_play_time
        FROM schedule
                 JOIN PLAYS ON SCHEDULE.PLAY_ID = PLAYS.ID
        WHERE SCHEDULE.ID = p_schedule_id;

        v_schedules_cursor := GETSCHEDULEBYPLAYTYPE_AND_DATE(v_play_type, v_play_time, p_schedule_id);
        v_cheapest_schedule_id := GETSCHEDULEWITHLOWESTPRICE(v_schedules_cursor);

        DBMS_OUTPUT.PUT_LINE('Probaljuk megvasarolni a legolcsobb jegyeket...');

        v_purchase_status := BUYTICKET(v_user.id,
                                       v_cheapest_schedule_id,
                                       p_amount,
                                       v_you_can_buy,
                                       v_available_tickets);

        IF v_purchase_status = 1 THEN
            SELECT price INTO v_price FROM schedule WHERE id = v_cheapest_schedule_id;
            DBMS_OUTPUT.PUT_LINE('Sikeresen megvasarolt jegyek ara: ' || p_amount * v_price);
        END IF;
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Hiba tortent: ' || SQLERRM);
END;
/

DECLARE
    p_username    VARCHAR2(50)  := 'teszt_user';
    p_password    VARCHAR2(50)  := 'test_password';
    p_email       VARCHAR2(100) := 'teszt_email1';
    p_balance     NUMBER        := 100.00;
    p_schedule_id NUMBER        := 1; -- Replace with a valid schedule ID from your database
    p_amount      NUMBER        := 1; -- Number of tickets to buy
BEGIN
    -- Call the main procedure with the test values
    main(p_username, p_password, p_email, p_balance, p_schedule_id, p_amount);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

-- Ha nincs hely,
-- akkor az olcsobbik helyeken probalja
-- meg megvenni a jegyeket,
-- addig amig elfogy a penze vagy
-- annyi jegyet tudott venni ahanyat akart.

create or replace procedure main2(p_username varchar2,
                                  p_password varchar2,
                                  p_email varchar2,
                                  p_balance number,
                                  p_schedule_id number,
                                  p_amount number) is
    v_user                 users_table%rowtype;
    v_you_can_buy          number;
    v_purchase_status      number;
    v_schedules_cursor     sys_refcursor;
    v_play_type            varchar2(100);
    v_play_time            date;
    v_cheapest_schedule_id number;
    v_price                number;
    v_total_price          number;
    v_current_amount       number;
    v_available_tickets_in number;
    v_available_tickets_out number;
begin
    v_current_amount := p_amount;
    v_user := checkandinsertuser(p_username, p_email, p_password, p_balance);
    dbms_output.put_line('Felhasznalo neve: ' || v_user.username);
    v_purchase_status := buyticket(v_user.id, p_schedule_id, v_current_amount, v_you_can_buy, v_available_tickets_out);
    v_available_tickets_in := v_available_tickets_out;
    IF v_purchase_status = 1 THEN
        SELECT price INTO v_price FROM schedule WHERE id = p_schedule_id;
        DBMS_OUTPUT.PUT_LINE('Sikeresen megvasarolt jegyek ara: ' || v_current_amount * v_price);
    ELSIF v_purchase_status = 0 THEN

        v_purchase_status := buyticket(v_user.id, p_schedule_id, v_available_tickets_in, v_you_can_buy,
                                       v_available_tickets_out);
        v_available_tickets_in := v_available_tickets_out;
        v_current_amount := v_current_amount - v_available_tickets_out;
        if v_purchase_status = -1 then
            SELECT price INTO v_price FROM schedule WHERE id = p_schedule_id;
            DBMS_OUTPUT.PUT_LINE('Sikeresen megvasarolt jegyek ara: ' || v_available_tickets_in * v_price ||
                                 'de elfogyott a penze');
        end if;
        SELECT play_type, TRUNC(play_date)
        INTO v_play_type, v_play_time
        FROM schedule
                 JOIN plays ON schedule.play_id = plays.id
        WHERE schedule.id = p_schedule_id;

        v_current_amount := v_current_amount - v_available_tickets_in;

        v_schedules_cursor := GETSCHEDULEBYPLAYTYPE_ORDERED(v_play_type, p_schedule_id);
        LOOP
            FETCH v_schedules_cursor INTO v_cheapest_schedule_id;
            EXIT WHEN v_cheapest_schedule_id IS NULL;
            v_purchase_status :=
                    buyticket(v_user.id, v_cheapest_schedule_id, v_current_amount, v_you_can_buy, v_available_tickets_out);
            v_available_tickets_in := v_available_tickets_out;
            IF v_purchase_status = 1 THEN
                SELECT price INTO v_price FROM schedule WHERE id = v_cheapest_schedule_id;
                v_total_price := v_available_tickets_in * v_price;
                DBMS_OUTPUT.PUT_LINE('Sikeresen megvasarolt jegyek ara: ' || v_total_price);
                EXIT when 1 = 1;
            elsif v_purchase_status = 0 then
                v_purchase_status := buyticket(v_user.id, v_cheapest_schedule_id, v_available_tickets_in, v_you_can_buy,
                                               v_available_tickets_out);
                v_available_tickets_in := v_available_tickets_out;
                v_current_amount := v_current_amount - v_available_tickets_out;
            END IF;
                exit when v_current_amount = 0 or v_purchase_status = -1;
        END LOOP;
    end if;
end;

/
DECLARE
    p_username    VARCHAR2(50)  := 'teszt_user';
    p_password    VARCHAR2(50)  := 'test_password';
    p_email       VARCHAR2(100) := 'test_email';
    p_balance     NUMBER        := 100.00;
    p_schedule_id NUMBER        := 4; -- Replace with a valid schedule ID from your database
    p_amount      NUMBER        := 10; -- Number of tickets to buy
BEGIN
    -- Call the main procedure with the test values
    main2(p_username, p_password, p_email, p_balance, p_schedule_id, p_amount);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/