group_assignment.sql

create database assignment;
use assignment;
create table `user`(
user_id int primary key auto_increment,
`name` varchar(50) null,
contact_number varchar(10) null,
check( contact_number regexp '^98[0-9]{8}$' or contact_number regexp '^97[0-9]{8}$'), 
#restriction in phone number
preferences varchar(50) null,
email varchar(50) null, check( email like '%@gmail.com') #email should end with @gmail.com
);
create table theatre(
theatre_id int primary key auto_increment,
location varchar(50) null,
no_of_screens int null 
);
create table screen(
screen_id int primary key auto_increment,
theatre_id int, foreign key (theatre_id) references theatre (theatre_id),
total_seat int null
);
 create table movie(
 movie_id int primary key auto_increment,
 title varchar(50) null,
 production_company varchar(50) null,
 genre varchar(100) null, #for flexibility we use varchar instead of enum.
 duration date null,
 `language` varchar(50) null,
 rating int null
 );
 create table `show`(
 show_id int primary key auto_increment,
 movie_id int, foreign key (movie_id) references movie( movie_id),
 ticket_price int null,
 screen_id int, foreign key (screen_id) references screen (screen_id),
 show_time time null
 );
 create table booking(
 booking_id int primary key auto_increment,
 `status` varchar(50) null,
 booking_date date null,
 card_number varchar(50) null,
 booking_time time null,
 change_reason varchar(100) null,
 ticket_count int null,
 payment_status enum('online', 'cash on delivery', 'half online- half cash')null,
 user_id int, foreign key (user_id) references user (user_id),
 show_id int, foreign key (show_id) references `show`( show_id)
 );
 create table seat_class(
 class_id int primary key auto_increment,
 class_name varchar(50) null,
 base_price int null,
 price_multiplier decimal null
 );
 
 create table seat_class_booking(
 booking_id int, class_id int,
 foreign key (class_id) references seat_class (class_id),
 foreign key (booking_id) references booking (booking_id),
 primary key (class_id, booking_id)
 );
 DELIMITER //

create trigger movie_show
after insert on movie
for each row
begin
  insert into `show`(movie_id) values (new.movie_id);
end;
//

DELIMITER ;

 DELIMITER //
 create trigger auto_price_multiplier
 before insert on seat_class
 for each row begin
 if new.class_name = 'Economy' then
    set new.price_multiplier = new.base_price;
  elseif new.class_name = 'Silver' then
    set new.price_multiplier = new.base_price * 1.2;
  elseif new.class_name = 'Gold' then
    set new.price_multiplier = new.base_price *1.5;
end if;
end;
   //

DELIMITER ;


 delimiter //
 create trigger show_screen
 after insert on screen for each row begin
     insert into `show` (screen_id) values (new.screen_id);
end;
	
 //
 delimiter ;
 
 delimiter //
 create trigger Theatre_screen
 after insert on theatre for each row begin
    insert into screen(theatre_id) values (new.theatre_id);
end;
 //
 
 delimiter ;




insert into theatre(location, no_of_screens) values
('Kathmandu', 5),
('Pokhara', 4),
('Biratnagar', 3),
('Lalitpur', 6),
('Bharatpur', 4),
('Dharan', 3),
('Butwal', 2),
('Janakpur', 3),
('Hetauda', 4),
( 'Nepalgunj', 2),
( 'Birgunj', 5),
( 'Itahari', 3),
( 'Bhaktapur', 4),
( 'Dhangadhi', 3),
( 'Tulsipur', 2),
( 'Gorkha', 1),
( 'Rupandehi', 4),
( 'Kavre', 3),
( 'Sindhuli', 2),
( 'Nuwakot', 3),
( 'Chitwan', 5),
( 'Ramechhap', 1),
( 'Lamjung', 3),
( 'Surkhet', 2),
( 'Kaski', 4),
( 'Sunsari', 3),
( 'Rautahat', 2),
( 'Banke', 3),
( 'Saptari', 2),
( 'Makwanpur', 4);

set SQL_safe_updates = 0;

set @sid := 0;

update screen
set 
  screen_id = (@sid := @sid + 1),
  total_seat = case theatre_id
      WHEN 1 THEN 120
      WHEN 2 THEN 115
      WHEN 3 THEN 110
      WHEN 4 THEN 105
      WHEN 5 THEN 100
      WHEN 6 THEN 95
      WHEN 7 THEN 90
      WHEN 8 THEN 85
      WHEN 9 THEN 80
      WHEN 10 THEN 75
      WHEN 11 THEN 70
      WHEN 12 THEN 65
      WHEN 13 THEN 60
      WHEN 14 THEN 55
      WHEN 15 THEN 50
      WHEN 16 THEN 45
      WHEN 17 THEN 40
      WHEN 18 THEN 35
      WHEN 19 THEN 30
      WHEN 20 THEN 25
      WHEN 21 THEN 20
      WHEN 22 THEN 18
      WHEN 23 THEN 16
      WHEN 24 THEN 14
      WHEN 25 THEN 12
      WHEN 26 THEN 10
      WHEN 27 THEN 8
      WHEN 28 THEN 6
      WHEN 29 THEN 4
      WHEN 30 THEN 2
      ELSE 60 
  end
order by theatre_id;

alter table movie
modify column duration int;


insert into movie (title, production_company, genre, duration, `language`, rating) values
('The Last Kingdom', 'Kathmandu Films', 'Action', 125, 'Nepali', 4),
('Mero Maya', 'Everest Studios', 'Romance', 110, 'Nepali', 5),
('Ghost in Janakpur', 'Terai Motion Pictures', 'Horror', 95, 'Nepali', 3),
('Kathaa Kathmandu', 'NepalTalkies', 'Drama', 120, 'Nepali', 4),
('Beyond the Clouds', 'SkyView Films', 'Adventure', 130, 'English', 5),
('Silent Hills', 'HorrorHouse', 'Horror', 90, 'Nepali', 2),
('Sano Sapana', 'Sajha Cinema', 'Romance', 100, 'Nepali', 4),
('The Himalayan Trek', 'TravelDocs', 'Documentary', 85, 'English', 5),
('Rajdrohi', 'Bhairahawa Films', 'Thriller', 115, 'Nepali', 4),
('Code 404', 'TechLine Pictures', 'Sci-Fi', 105, 'English', 5),
('Bir Gorkhali', 'Evergreen Studio', 'Historical', 140, 'Nepali', 4),
('The Comedian', 'Funny House', 'Comedy', 90, 'English', 3),
('Pahilo Prem', 'DreamWorld', 'Romance', 110, 'Nepali', 4),
('Dark Clouds', 'Mystery Inc.', 'Thriller', 100, 'English', 3),
('Basanta ko Din', 'Koshi Films', 'Drama', 95, 'Nepali', 4),
('Alien Bhitra', 'OuterWorlds', 'Sci-Fi', 125, 'English', 5),
('Ma Ta Hero', 'Jumla Pictures', 'Action', 110, 'Nepali', 4),
('Love Reloaded', 'Cupid Studios', 'Romance', 100, 'English', 3),
('Operation Everest', 'High Altitude Films', 'Adventure', 135, 'English', 5),
('Chiya Guff', 'Cafe Productions', 'Comedy', 85, 'Nepali', 4),
('Digital Dukha', 'Code Cinema', 'Tech-Drama', 100, 'Nepali', 4),
('Bhoot FM', 'Haunted Studios', 'Horror', 90, 'Nepali', 2),
('Rato Tika', 'Mithila Films', 'Romantic Drama', 105, 'Nepali', 5),
('Silent Kathmandu', 'Noir Films', 'Mystery', 95, 'Nepali', 3),
('Nirasha', 'Hope Studios', 'Tragedy', 110, 'Nepali', 4),
('The Hacker', 'CyberVision', 'Thriller', 115, 'English', 4),
('Kanchi Returns', 'Tharu Films', 'Romance Comedy', 100, 'Nepali', 4),
('Love in Pokhara', 'City Romance', 'Romance', 110, 'Nepali', 5),
('Lost in Mustang', 'Trekking Tales', 'Adventure', 120, 'English', 4),
('Bhukamp', 'Disaster Docs', 'Documentary', 90, 'Nepali', 5);

select title, 
CONCAT(FLOOR(duration / 60), ' hr ', duration % 60, ' min') as duration from movie;

update `show` s
join movie m on s.movie_id = m.movie_id
join screen sc on s.screen_id = sc.screen_id
set
s.ticket_price = case s.show_id
WHEN 1 THEN 150
WHEN 2 THEN 160
WHEN 3 THEN 170
WHEN 4 THEN 180
WHEN 5 THEN 190
WHEN 6 THEN 200
WHEN 7 THEN 210
WHEN 8 THEN 220
WHEN 9 THEN 230
WHEN 10 THEN 240
WHEN 11 THEN 250
WHEN 12 THEN 260
WHEN 13 THEN 270
WHEN 14 THEN 280
WHEN 15 THEN 290
WHEN 16 THEN 300
WHEN 17 THEN 310
WHEN 18 THEN 320
WHEN 19 THEN 330
WHEN 20 THEN 340
WHEN 21 THEN 350
WHEN 22 THEN 360
WHEN 23 THEN 370
WHEN 24 THEN 380
WHEN 25 THEN 390
WHEN 26 THEN 400
WHEN 27 THEN 410
WHEN 28 THEN 420
WHEN 29 THEN 430
WHEN 30 THEN 440
else 600
end, 
s.show_time = case s.show_id
	WHEN 1 THEN '18:00:00'
    WHEN 2 THEN '19:00:00'
    WHEN 3 THEN '20:00:00'
    WHEN 4 THEN '21:00:00'
    WHEN 5 THEN '22:00:00'
    WHEN 6 THEN '23:00:00'
    WHEN 7 THEN '18:30:00'
    WHEN 8 THEN '19:30:00'
    WHEN 9 THEN '20:30:00'
    WHEN 10 THEN '21:30:00'
    WHEN 11 THEN '22:30:00'
    WHEN 12 THEN '23:30:00'
    WHEN 13 THEN '18:15:00'
    WHEN 14 THEN '19:15:00'
    WHEN 15 THEN '20:15:00'
    WHEN 16 THEN '21:15:00'
    WHEN 17 THEN '22:15:00'
    WHEN 18 THEN '23:15:00'
    WHEN 19 THEN '18:45:00'
    WHEN 20 THEN '19:45:00'
    WHEN 21 THEN '20:45:00'
    WHEN 22 THEN '21:45:00'
    WHEN 23 THEN '22:45:00'
    WHEN 24 THEN '23:45:00'
    WHEN 25 THEN '18:05:00'
    WHEN 26 THEN '19:05:00'
    WHEN 27 THEN '20:05:00'
    WHEN 28 THEN '21:05:00'
    WHEN 29 THEN '22:05:00'
    WHEN 30 THEN '23:05:00'
    else '18:00:00'
end
where s.movie_id is not null and s.screen_id is not null;

delimiter //

create procedure AddBookingFull(
    in p_name varchar(50),
	in p_contact_number varchar(10),
	in p_preferences varchar(50),
	in p_email varchar(50),

     in p_movie_id int, 
     in p_screen_id int, 
     in p_ticket_price int,
     in p_show_time time,

     in p_status varchar(50),
     in p_booking_date date,
     in p_card_number varchar(50),
     in p_booking_time time,
     in p_change_reason varchar(100),
     in p_ticket_count int,
     in p_payment_status enum('online', 'cash on delivery', 'half online- half cash')
)
begin
    declare last_user_id int;
    declare last_show_id int;

    insert into `user`(`name`, contact_number, preferences, email)
    values (p_name, p_contact_number, p_preferences, p_email);

    set last_user_id = LAST_INSERT_ID();

    insert into `show`(movie_id, ticket_price, screen_id, show_time)
    values (p_movie_id, p_ticket_price, p_screen_id, p_show_time);

    set last_show_id = LAST_INSERT_ID();

    insert into booking(`status`, booking_date, card_number, booking_time, change_reason, ticket_count, payment_status, user_id, show_id)
    values (p_status, p_booking_date, p_card_number, p_booking_time, p_change_reason, p_ticket_count, p_payment_status, last_user_id, last_show_id);
end;
//

delimiter ;

call AddBookingFull('Anish Gurung', '9812345678', 'Action', 'anish.gurung@gmail.com', 5, 10, 320, '20:15:00', 'confirmed', '2025-07-01', '1234567890123456', '18:30:00', 'Evening preferred', 3, 'online');
call AddBookingFull('Rita', '9820086818', 'Adventure', 'rita1@gmail.com', 16, 29, 261, '14:30:00', 'pending', '2025-07-01', '4812465091068649', '11:00:00', 'Want front row seats', 1, 'online');
call AddBookingFull('Suman', '9869639738', 'Romance', 'suman2@gmail.com', 28, 23, 448, '18:30:00', 'pending', '2025-07-02', '4837958834178748', '12:45:00', 'Celebrating anniversary', 5, 'half online- half cash');
call AddBookingFull('Mina', '9816998454', 'Comedy', 'mina3@gmail.com', 13, 10, 353, '11:30:00', 'pending', '2025-07-03', '4245202162443672', '17:30:00', 'Morning shows are best', 2, 'online');
call AddBookingFull('Ramesh', '9834465932', 'Horror', 'ramesh4@gmail.com', 27, 18, 355, '15:45:00', 'cancelled', '2025-07-04', '4851254843021329', '17:45:00', 'Changed mind after booking', 1, 'half online- half cash');
call AddBookingFull('Sita', '9825045737', 'Sci-Fi', 'sita5@gmail.com', 9, 17, 225, '17:00:00', 'confirmed', '2025-07-05', '4842011191110029', '14:45:00', 'Watching with cousins', 5, 'online');
call AddBookingFull('Anil', '9847756242', 'Drama', 'anil6@gmail.com', 5, 22, 399, '13:00:00', 'confirmed', '2025-07-06', '4867459056156785', '10:30:00', 'For film studies assignment', 2, 'cash on delivery');
call AddBookingFull('Sabina', '9806525486', 'Romance', 'sabina7@gmail.com', 3, 9, 350, '12:30:00', 'pending', '2025-07-07', '4812789675632108', '13:30:00', 'Special date outing', 3, 'online');
call AddBookingFull('Kiran', '9876543210', 'Thriller', 'kiran8@gmail.com', 19, 20, 430, '20:00:00', 'confirmed', '2025-07-08', '4857452364023488', '15:30:00', 'Big fan of lead actor', 1, 'half online- half cash');
call AddBookingFull('Nisha', '9811111111', 'Horror', 'nisha9@gmail.com', 6, 13, 250, '21:00:00', 'cancelled', '2025-07-09', '4864532088654098', '19:30:00', 'Too scared to watch', 4, 'cash on delivery');
call AddBookingFull('Deepak', '9822222222', 'Action', 'deepak10@gmail.com', 1, 3, 275, '16:30:00', 'pending', '2025-07-10', '4823567456483191', '16:00:00', 'Just got free from work', 2, 'online');
call AddBookingFull('Anju', '9833333333', 'Drama', 'anju11@gmail.com', 15, 15, 370, '19:15:00', 'confirmed', '2025-07-11', '4785464578462382', '12:30:00', 'Evening escape plan', 3, 'cash on delivery');
call AddBookingFull('Raj', '9844444444', 'Romance', 'raj12@gmail.com', 2, 6, 310, '13:45:00', 'cancelled', '2025-07-12', '4709328476543950', '11:45:00', 'Plan changed last minute', 1, 'half online- half cash');
call AddBookingFull('Sujita', '9855555555', 'Action', 'sujita13@gmail.com', 12, 7, 345, '15:00:00', 'confirmed', '2025-07-13', '4798235739241873', '09:30:00', 'Excited about the sequel', 4, 'online');
call AddBookingFull('Pramesh', '9866666666', 'Horror', 'pramesh14@gmail.com', 7, 11, 289, '11:15:00', 'pending', '2025-07-14', '4719375837503484', '11:15:00', 'Watched trailer, looks scary', 2, 'cash on delivery');
call AddBookingFull('Rekha', '9877777777', 'Drama', 'rekha15@gmail.com', 21, 19, 410, '18:00:00', 'confirmed', '2025-07-15', '4783975812349801', '13:15:00', 'Afternoon break plan', 5, 'half online- half cash');
call AddBookingFull('Bijay', '9888888888', 'Action', 'bijay16@gmail.com', 24, 14, 280, '22:00:00', 'pending', '2025-07-16', '4720850439208471', '20:00:00', 'Friend group outing', 1, 'online');
call AddBookingFull('Sarita', '9899999999', 'Romance', 'sarita17@gmail.com', 8, 4, 365, '17:30:00', 'cancelled', '2025-07-17', '4852095749320862', '15:15:00', 'Partner not available', 2, 'cash on delivery');
call AddBookingFull('Manoj', '9801234567', 'Comedy', 'manoj18@gmail.com', 10, 8, 295, '13:30:00', 'confirmed', '2025-07-18', '4861209482038742', '10:30:00', 'Need to laugh after long week', 3, 'online');
call AddBookingFull('Sabita', '9812345678', 'Action', 'sabita19@gmail.com', 18, 25, 400, '12:00:00', 'pending', '2025-07-19', '4812390583902483', '11:00:00', 'Pre-birthday celebration', 1, 'half online- half cash');
call AddBookingFull('Raju', '9823456789', 'Horror', 'raju20@gmail.com', 11, 5, 312, '10:45:00', 'cancelled', '2025-07-20', '4849023845092309', '12:30:00', 'Accidental booking', 2, 'online');
call AddBookingFull('Pooja', '9834567890', 'Romance', 'pooja21@gmail.com', 26, 16, 426, '16:00:00', 'confirmed', '2025-07-21', '4832748502384052', '13:30:00', 'Girl gang movie night', 4, 'cash on delivery');
call AddBookingFull('Sunil', '9845678901', 'Drama', 'sunil22@gmail.com', 14, 12, 305, '11:45:00', 'pending', '2025-07-22', '4851029384029384', '10:15:00', 'Morning energy boost', 1, 'half online- half cash');
call AddBookingFull('Kavita', '9856789012', 'Action', 'kavita23@gmail.com', 17, 21, 390, '17:45:00', 'cancelled', '2025-07-23', '4862039485092847', '16:00:00', 'Movie time clashed', 2, 'online');
call AddBookingFull('Dipesh', '9867890123', 'Sci-Fi', 'dipesh24@gmail.com', 22, 30, 435, '23:00:00', 'confirmed', '2025-07-24', '4820485029384756', '21:15:00', 'Watching with tech group', 3, 'cash on delivery');
call AddBookingFull('Sneha', '9878901234', 'Adventure', 'sneha25@gmail.com', 20, 1, 322, '13:15:00', 'pending', '2025-07-25', '4789384750293847', '12:30:00', 'Cousin visiting', 1, 'half online- half cash');
call AddBookingFull('Pratik', '9889012345', 'Comedy', 'pratik26@gmail.com', 25, 26, 450, '19:00:00', 'cancelled', '2025-07-26', '4860203948502938', '17:45:00', 'Feeling under the weather', 5, 'online');
call AddBookingFull('Sneha', '9890123456', 'Romantic', 'sneha27@gmail.com', 23, 24, 417, '22:30:00', 'confirmed', '2025-07-27', '4837502938475092', '21:00:00', 'Date night plan', 2, 'cash on delivery');
call AddBookingFull('Krishna', '9802345678', 'Mystery', 'krishna28@gmail.com', 4, 28, 278, '15:30:00', 'pending', '2025-07-28', '4850283450238402', '14:00:00', 'Fan of mystery thrillers', 3, 'half online- half cash');

delimiter //

create procedure AddFullBookingWithClass(
    in p_status varchar(50),
    in p_booking_date date,
    in p_card_number varchar(50),
    in p_booking_time time,
    in p_change_reason varchar(100),
    in p_ticket_count int,
    in p_payment_status enum('online', 'cash on delivery', 'half online- half cash'),
    in p_user_id int,
    in p_show_id int,

    in p_class_name varchar(50),
   in  p_base_price int,
    in p_price_multiplier decimal(5,2)
)
begin
    declare new_booking_id int;
    declare new_class_id int;

    insert into booking (`status`, booking_date, card_number, booking_time, change_reason, ticket_count, payment_status, user_id, show_id)
    values ( p_status, p_booking_date, p_card_number, p_booking_time, p_change_reason, p_ticket_count, p_payment_status, p_user_id, p_show_id);
    set new_booking_id = LAST_INSERT_ID();

    insert into seat_class (class_name, base_price, price_multiplier) values (p_class_name, p_base_price, p_price_multiplier);
   set new_class_id = LAST_INSERT_ID();

    insert into seat_class_booking (booking_id, class_id) values (new_booking_id, new_class_id);
end;
//

delimiter ;

call AddFullBookingWithClass('confirmed', '2025-07-01', '1234567890123456', '14:00:00', 'Prefers aisle seat', 2, 'online', 1, 3, 'Gold', 350, 1.5);
call AddFullBookingWithClass('pending', '2025-07-02', '4567890123456789', '15:00:00', 'Wants middle seat', 3, 'cash on delivery', 2, 5, 'Silver', 300, 1.2);
call AddFullBookingWithClass('cancelled', '2025-07-03', '9876543210987654', '16:30:00', 'Near exit', 1, 'half online- half cash', 3, 2, 'Economy', 250, 1.0);
call AddFullBookingWithClass('confirmed', '2025-07-05', '9988776655443322', '13:15:00', 'Birthday celebration', 2, 'online', 5, 4, 'Gold', 380, 1.4);
call AddFullBookingWithClass('pending', '2025-07-06', '1234432112344321', '12:45:00', 'Wants snacks', 3, 'cash on delivery', 6, 7, 'Business', 450, 1.6);
call AddFullBookingWithClass('cancelled', '2025-07-07', '4444555566667777', '20:30:00', 'Wants front row', 1, 'half online- half cash', 7, 6, 'Silver', 310, 1.25);
call AddFullBookingWithClass('confirmed', '2025-07-08', '6677889900112233', '21:45:00', 'Watching alone', 5, 'online', 8, 8, 'Economy', 260, 1.0);
call AddFullBookingWithClass('confirmed', '2025-07-09', '3213213213213213', '10:15:00', 'Wants last row', 2, 'cash on delivery', 9, 10, 'Gold', 340, 1.3);
call AddFullBookingWithClass('pending', '2025-07-10', '9999888877776666', '11:30:00', 'Friend recommended', 3, 'half online- half cash', 10, 3, 'Platinum', 510, 2.1);
call AddFullBookingWithClass('confirmed', '2025-07-11', '4561237894561237', '13:00:00', 'Needs legroom', 4, 'online', 1, 2, 'Silver', 320, 1.2);
call AddFullBookingWithClass('cancelled', '2025-07-12', '1111222233334444', '14:45:00', 'Family seat', 2, 'cash on delivery', 2, 9, 'Gold', 370, 1.35);
call AddFullBookingWithClass('pending', '2025-07-13', '5555666677778888', '16:00:00', 'Discount applied', 3, 'online', 3, 1, 'Business', 420, 1.7);
call AddFullBookingWithClass('confirmed', '2025-07-14', '9999000011112222', '17:30:00', 'Regular moviegoer', 1, 'online', 4, 6, 'Silver', 295, 1.15);
call AddFullBookingWithClass('pending', '2025-07-15', '8888999900001111', '19:00:00', 'Near screen', 5, 'cash on delivery', 5, 5, 'Economy', 270, 1.0);
call AddFullBookingWithClass('cancelled', '2025-07-16', '1234567812345678', '20:15:00', 'Free seat choice', 2, 'half online- half cash', 6, 4, 'Gold', 365, 1.4);
call AddFullBookingWithClass('confirmed', '2025-07-17', '8765432187654321', '21:30:00', 'Comfort is priority', 3, 'online', 7, 3, 'Business', 460, 1.75);
call AddFullBookingWithClass('pending', '2025-07-18', '2345678923456789', '10:45:00', 'Loves high screen', 4, 'cash on delivery', 8, 2, 'Silver', 310, 1.25);
call AddFullBookingWithClass('confirmed', '2025-07-19', '3456789034567890', '12:00:00', 'Back seat preferred', 2, 'online', 9, 1, 'Economy', 280, 1.05);
call AddFullBookingWithClass('cancelled', '2025-07-20', '4567890145678901', '13:30:00', 'With colleague', 3, 'half online- half cash', 10, 7, 'Platinum', 495, 2.0);
call AddFullBookingWithClass('confirmed', '2025-07-21', '5678901256789012', '14:45:00', 'Wants window seat', 5, 'online', 1, 8, 'Silver', 305, 1.2);
call AddFullBookingWithClass('pending', '2025-07-22', '6789012367890123', '16:15:00', 'Quiet seat', 2, 'cash on delivery', 2, 6, 'Gold', 390, 1.45);
call AddFullBookingWithClass('confirmed', '2025-07-23', '7890123478901234', '17:30:00', 'Back right corner', 4, 'online', 3, 5, 'Business', 470, 1.8);
call AddFullBookingWithClass('cancelled', '2025-07-24', '8901234589012345', '18:45:00', 'Light sensitivity', 1, 'half online- half cash', 4, 4, 'Platinum', 510, 2.2);
call AddFullBookingWithClass('confirmed', '2025-07-25', '9012345690123456', '20:00:00', 'Late night fan', 3, 'online', 5, 3, 'Economy', 265, 1.0);
call AddFullBookingWithClass('pending', '2025-07-26', '0123456701234567', '21:15:00', 'Mid-back seats', 2, 'cash on delivery', 6, 2, 'Silver', 315, 1.22);
call AddFullBookingWithClass('confirmed', '2025-07-27', '1234567812345678', '22:30:00', 'Corner side', 4, 'online', 7, 1, 'Gold', 355, 1.4);
call AddFullBookingWithClass('pending', '2025-07-28', '2345678923456789', '10:00:00', 'Aisle seat', 1, 'half online- half cash', 8, 5, 'Platinum', 500, 2.05);
call AddFullBookingWithClass('cancelled', '2025-07-29', '3456789034567890', '11:15:00', 'Cheap seat', 3, 'cash on delivery', 9, 6, 'Business', 435, 1.6);
call AddFullBookingWithClass('confirmed', '2025-07-30', '4567890145678901', '12:45:00', 'Birthday surprise', 2, 'online', 10, 4, 'Silver', 330, 1.3);

select movie_id, title, genre, rating 
from movie 
where genre 
like '%Horror%' or genre 
like '%Thriller%' or genre 
like '%Action%' or genre 
like '%Sci-Fi%' or genre 
like '%Mystery%';

select u.user_id, u.`name`, u.contact_number, u.preferences, u.email, b.booking_id, b.`status`, b.booking_date, b.card_number, b.booking_time, b.change_reason,
    b.ticket_count, b.payment_status, b.show_id 
from booking b
join `user` u 
on b.user_id = u.user_id 
where b.ticket_count > 10;

select u.user_id, u.`name`, u.contact_number, u.preferences, u.email, b.card_number, b.booking_id, b.booking_date, b.booking_time, b.ticket_count,
    b.payment_status, b.`status`, b.show_id 
from `user` u 
join booking b 
on u.user_id = b.user_id 
where b.card_number 
is not null;

select m.movie_id, m.title, SUM(s.ticket_price * b.ticket_count) 
as total_earnings 
from movie m 
join `show` s 
on m.movie_id = s.movie_id
join booking b 
on s.show_id = b.show_id 
group by m.movie_id, m.title 
order by total_earnings 
desc;

select sc.screen_id, t.theatre_id, t.location, t.no_of_screens 
from screen sc 
join theatre t 
on sc.theatre_id = t.theatre_id
order by sc.screen_id;

select u.user_id, u.`name`, u.contact_number, u.preferences, u.email, b.booking_id 
as ticket_id, b.`status`, b.booking_date, b.booking_time, b.ticket_count, b.payment_status 
from `user` u 
join booking b 
on u.user_id = b.user_id 
order by b.booking_id;