create database flight_management;

use flight_management;
create table flights ( flightid int primary key auto_increment, airline varchar
(50) not null,
source varchar(55) not null , destination varchar(55) not null,
status enum ('scheduled','departed','arrived','cancelled') default 'scheduled',
price decimal(10,2) check (price > 0) );

create table passengers (

passengerid int not null auto_increment primary key, fullname varchar(100) not
null,
email varchar(100) unique not null, phonenumber varchar(15) not null,
passportnumber varchar(20) unique not null);

create table bookings( bookingid int auto_increment primary key , passengerid int
unique ,
flightid int , bookingdate timestamp default current_timestamp,
seatnumber varchar(10) not null, status enum('confirmed', 'cancelled') ,
foreign key (passengerid) references passengers ( passengerid),
foreign key (flightid) references flights (flightid));

create table crew( crewid int primary key auto_increment, fullname varchar(100) not
null,
role enum('pilot','co-pilot','flight attendant') not null, flightid int,
foreign key (flightid) references flights (flightid) );

create table payments( paymentid int primary key auto_increment, bookingid int,
amount decimal(10,2) not null check (amount>0),
paymentmethod enum('Credit card',' Debit card','UPI', 'Netbanking') not null,
paymentdate timestamp default current_timestamp,
status enum('successful','failed','pending') default 'pending',
foreign key (bookingid ) references bookings(bookingid));

alter table flights add (departuretime datetime , arrivaltime datetime );

alter table flights drop column departuretime;
alter table flights drop column arrivaltime;
alter table flights add (departuretime datetime , arrivaltime datetime );

insert into flights (airline,source, destination , departuretime, arrivaltime,

status , price)
values
('indigo','chennai','delhi','2025-03-13 09:30:00','2023-03-13 12:10:40',
'scheduled' , 13000.00),
('Airindia','hyderabad','kochi','2025-03-13 10:30:00','2023-03-13 01:19:20',
'departed' , 10000.50),
('airasiaindia','indoor','mohali','2025-03-13 09:00:00','2023-03-13 2:10:40',
'arrived' , 15000.23),
('vistara','bangalor','kolkata','2025-03-13 09:30:00','2023-03-13
12:01:40','arrived',16000.00);

insert into passengers( fullname, email, phonenumber, passportnumber) values

('rahul sharma', 'rahul@gamil.com', 8907654477, 'A12345678'),
('abhi roy', 'abhi09@gamil.com', 9007657889, 'B76435789'),
('rohit kapoor', 'rohitkapoor@gamil.com',9087563456, 'C12337878'),
('amana rathor', 'amanarathoroff80@gamil.com', 7543975446, 'D87543678');

select*from flights;
insert into bookings(seatnumber,status) values
('10A','confirmed'),('11B','confirmed'),('12C','confirmed'),('13A','cancelled');
 select*from bookings;
select*from passengers;

update bookings set flightid = case

when bookingid=5 then 5 when bookingid=6 then 6
when bookingid=7 then 7 when bookingid=8 then 8
else flightid
end;

update bookings set passengerid = case

when bookingid=5 then 5 when bookingid=6 then 6
when bookingid=7 then 7 when bookingid=8 then 8
else passengerid
end;

select*from bookings;

insert into crew ( fullname , role, flightid) values

('aman bajwa','pilot',5),('karan mahta','co-pilot',5),('anita sharma','flight
attendant',5),
('sandeep singh','pilot',6),('surya rathor','co-pilot',6),('meera reddy','flight
attendant',6);

insert into payments ( bookingid,amount, paymentmethod , status) values

(5,13000,'UPI','successful'),(6,10000.50,'Netbanking', 'successful'),
(7,15000.23,'Credit card', 'successful'),(8,13000,'UPI','pending');

insert into flights ( airline, source, destination , departuretime, arrivaltime,

status , price)
values
('indigo','chennai','delhi','2025-03-13 09:30:00','2023-03-13 12:10:40',
'scheduled' , 7000.00),
('Airindia','hyderabad','kochi','2025-03-13 10:30:00','2023-03-13 01:19:20',
'departed' , 10000.00),
('airasiaindia','indoor','mohali','2025-03-13 09:00:00','2023-03-13 2:10:40',
'arrived' , 5000.00);

alter table bookings add (source varchar(40), destination varchar(40) ,

depaturetime datetime, arrivaltime datetime );

update flights
set source = case
when flightid= 5 then 'chennai' when flightid= 6 then 'chennai'
when flightid= 7 then 'chennai' when flightid= 8 then 'chennai'
when flightid= 9 then 'chennai' when flightid= 10 then 'chennai'
when flightid= 11 then 'chennai' else source
end;

update flights
set departuretime = case
when flightid= 5 then'2025-03-23 09:30:00' when flightid= 6 then '2025-03-23
09:50:00'
when flightid= 7 then '2025-03-23 10:30:00' when flightid= 8 then '2025-03-23
11:30:00'
when flightid= 9 then '2025-03-23 12:05:00' when flightid= 10 then '2025-03-23
13:15:00'
when flightid= 11 then '2025-03-23 14:30:00' else departuretime
end;
insert into crew ( fullname , role, flightid) values
('maan singh','pilot',7),('hema bajwa','co-pilot',7),('saitha','flight
attendant',7),
('shivay rathor','pilot',8),('manbir singh','co-pilot',8),('keerthi','flight
attendant',8),
('shiva shankar','pilot',9),('harmanpreet','co-pilot',9),('mohan','flight
attendant',9),
('janani','pilot',10),('nithish gosh','co-pilot',10),('isha negi','flight
attendant',10),
('shan','pilot',11),('rithivik gosh','co-pilot',11),('josh','flight attendant',11);

insert into passengers ( fullname, email, phonenumber, passportnumber) values

('hema', 'hemai908@gmail.com', 9776567889 , 'A455966'),
('meenu','meenu98@gmail.com' , 9764567888 ,'G876567'),
('kavitha','kavi@gmail.com', 7980089656, 'S787889'),
('abhishek', 'abhishek@gmail.com', 9776567889,'A45566'),
('prithi','prithi@gmail.com' , 9769456798 ,'D78567'),
('karthi','karthi@gmail.com', 7982344526, 'P347889'),
('rio','rio98@gmail.com' , 9345686578 ,'R896567'),
('keerthi','keerthi@gmail.com', 7875489699, 'E727889');

update bookings
set passengerid = case
when bookingid=9 then 17 when bookingid=10 then 18
when bookingid=11 then 19 when bookingid=12 then 20
else passengerid end,
flightid = case
when bookingid=9 then 7 when bookingid=10 then 8
when bookingid=11 then 8 when bookingid=12 then 7
else flightid end;

alter table bookings change column depaturetime departuretime datetime;

alter table bookings change column arrivaltime arrivaltime datetime;
update bookings b
join flights f on b.flightid = f.flightid
set
b.source = f.source,
b.destination = f.destination,
b.arrivaltime = f.arrivaltime,
b.departuretime = f.departuretime;

insert into payments( bookingid,amount, paymentmethod , status) values

(9,7000,'UPI','successful'),(10,10000.00,'Netbanking', 'successful'),
(11,7000.23,'Credit card', 'successful'),(12,5000,'UPI','pending');

select* from flights;

select bookingid, paymentmethod from payments;

select airline, min(price)as min_price,

max(price) as max_price from flights group by airline;
select status, count(*) as total_flights from flights group by status;

select b.bookingid, p.fullname as passenger_name, f.airline, f.source,

f.destination, b.status
from bookings b
inner join passengers p on b.passengerid = p.passengerid
inner join flights f on b.flightid = f.flightid;

select f.flightid, f.airline, f.source, f.destination, b.bookingid, b.status

from flights f
left join bookings b on f.flightid = b.flightid;

select f.airline, sum(p.amount) as totalrevenue from flights f

join bookings b on f.flightid = b.flightid
join payments p on b.bookingid = p.bookingid
where p.status = 'successful'
group by f.airline;

select f.airline , c.fullname as crewmember, c.role from crew c

join flights f on c.flightid = f.flightid
order by f.airline, c.role;

select* from crew;

select FullName, Email from Passengers

where Passengerid in (
select Passengerid from Bookings
where Bookingid not in (select Bookingid from Payments where Status =
'successful'));

select distinct Airline , status from Flights

where Airline not in (
select distinct Airline from Flights
where Status <> 'scheduled');

create view passengerbookings as select

p.passengerID, p.fullName as passengerName, p.email, b.bookingid,
f.airline, f.source, f.destination, f.departureTime, f.arrivalTime,
b.status as bookingstatus
from bookings b
join passengers p on b.passengerid = p.passengerid
join flights f on b.flightid = f.flightid;

select * from passengerbookings where bookingstatus = 'confirmed';

CREATE USER 'admin_user'@'localhost' IDENTIFIED BY 'AdminPass123';

GRANT ALL PRIVILEGES ON flight_management.* TO 'admin_user'@'localhost';
ALTER USER 'admin_user'@'localhost' IDENTIFIED BY 'flightmanegementadmin';

SELECT * FROM passengers WHERE REGEXP_LIKE(phonenumber, '^97');

SELECT passportnumber, REGEXP_REPLACE(passportnumber, '[0-9]', 'X') AS
masked_passport
FROM passengers;
SELECT email, REGEXP_INSTR(email, '@') AS at_position FROM passengers;
SELECT email, REGEXP_SUBSTR(email, '^[^@]+') AS username FROM passengers;

DELIMITER //
CREATE PROCEDURE GetFlightsByStatus(IN flight_status
ENUM('scheduled','departed','arrived','cancelled'))
BEGIN
SELECT * FROM flights WHERE status = flight_status;
END //
DELIMITER ;
CALL GetFlightsByStatus('arrived');

DELIMITER //
CREATE TRIGGER update_payment_on_booking_cancel
AFTER UPDATE ON bookings
FOR EACH ROW
BEGIN
IF NEW.status = 'cancelled' THEN
UPDATE payments SET status = 'failed' WHERE bookingid = NEW.bookingid;
END IF;
END //
DELIMITER ;
UPDATE flights SET status = 'cancelled' WHERE flightid = 2;

CREATE TABLE flight_archive (

flightid INT, airline VARCHAR(50), source VARCHAR(55), destination VARCHAR(55),

departuretime DATETIME, arrivaltime DATETIME, status ENUM('scheduled',

'departed', 'arrived', 'cancelled'),
price DECIMAL(10,2)
);
DELIMITER //
CREATE TRIGGER archive_deleted_flight
BEFORE DELETE ON flights
FOR EACH ROW
BEGIN
INSERT INTO flight_archive
VALUES (OLD.flightid, OLD.airline, OLD.source, OLD.destination,
OLD.departuretime, OLD.arrivaltime, OLD.status, OLD.price);
END //
DELIMITER ;
DELETE FROM flights WHERE flightid = 3;
SELECT * FROM flight_archive; -- Check if the deleted flight is archived

SELECT * FROM flights WHERE status = 'scheduled' ORDER BY departuretime ASC;

SELECT * FROM flights WHERE price BETWEEN 5000 AND 15000;
SELECT flightid, COUNT(*) AS total_bookings FROM bookings GROUP BY flightid;