khi

--- Problem 6 Extract all customers departing from home

select c.customer_id,
CONCAT(c.first_name,' ',c.last_name) as full_name,
p.town_name FROM customers as c
        join airports as air on air.town_id  = c.home_town_id
        join flights as f on f.origin_airport_id = air.airport_id
        join tickets as t on t.flight_id = f.flight_id
        join towns as p on p.town_id = c.home_town_id
        group by customer_id
        order by customer_id;

---- PROBLEM 11 EXTRACT all airports and count passengers

select air.airport_id,air.airport_name ,
COUNT(ti.customer_id) as passengers
from airports as air
        join flights as fi on fi.origin_airport_id = air.airport_id
        join tickets as ti on ti.flight_id = fi.flight_id
        where fi.`status` = 'Departing'
        group by air.airport_id,air.airport_name
        having COUNT(ti.customer_id) > 0
        order by air.airport_id;


--- Problem 1 Review Registering procedure

create procedure usp_submit_review (customer_id INT ,review_content VARCHAR(255),review_grade INT ,airline_name_n VARCHAR(50))
begin
    declare airlin_id int;

    select airline_id into airlin_id from airlines  where airline_name = airline_name_n ;

    if(airlin_id IS NULL) then
     signal sqlstate'45000' set message_text = 'Airline does not exist.';
     else
        insert into customer_reviews(customer_id,review_content,review_grade,airline_id)
        values (customer_id,review_content,review_grade,airlin_id);
    end if;

end

--- Problem 2 Ticket purchase procedure


create procedure usp_purchase_ticket(customer_id_n INT ,flight_id INT ,a_ticket_price DECIMAL,class ENUM('First','Second','Third')
,seat VARCHAR(5))
begin
    declare customer_balance decimal;

    select balance into customer_balance from customer_bank_accounts  where customer_id = customer_id_n;

    if((customer_balance - a_ticket_price) < 0) then
        signal sqlstate '45000' set message_text = 'Insufficient bank account balance for ticket purchase.';
    else
        insert into tickets (price,class,seat,flight_id,customer_id)
        values (a_ticket_price,class,seat,flight_id,customer_id_n);
        update customer_bank_accounts as cba
            set cba.balance = cba.balance - a_ticket_price
        where customer_id = customer_id_n;
    end if;
end