case study 2 railway gsits gourav

create database cs2;
use cs2;

-- TrainList table

CREATE TABLE TrainList (
  train_number INT PRIMARY KEY,
  train_name VARCHAR(255) NOT NULL,
  source VARCHAR(255) NOT NULL,
  destination VARCHAR(255) NOT NULL,
  ac_fair DECIMAL(10,2) NOT NULL,
  general_fair DECIMAL(10,2) NOT NULL,
  weekdays VARCHAR(255)
);


-- Train_Status table

CREATE TABLE Train_Status (
  train_number INT,
  train_date DATE NOT NULL,
  total_ac_seats INT NOT NULL,
  total_general_seats INT NOT NULL,
  ac_seats_booked INT NOT NULL DEFAULT 0,
  general_seats_booked INT NOT NULL DEFAULT 0,
  PRIMARY KEY (train_number, train_date),
  FOREIGN KEY (train_number) REFERENCES TrainList(train_number)
);


-- Passenger table

CREATE TABLE Passenger (
  ticket_id INT PRIMARY KEY AUTO_INCREMENT,
  train_number INT NOT NULL,
  booking_date DATE NOT NULL,
  name VARCHAR(255) NOT NULL,
  age INT,
  sex VARCHAR(10),
  address TEXT,
  status VARCHAR(20) NOT NULL,
  ticket_category VARCHAR(10) NOT NULL,
  FOREIGN KEY (train_number) REFERENCES TrainList(train_number)
);

-- inserting data ramesh boi
INSERT INTO TrainList (train_number, train_name, source, destination, ac_fair, general_fair, weekdays)
VALUES
(1001, 'Gourav U Exp', 'Indore', 'Delhi', 1500.00, 1000.00, 'Monday, Wednesday, Friday'),
(1002, 'Gourav U Ps', 'Delhi', 'Prayagraj', 2000.00, 1200.00, 'Tuesday, Thursday, Saturday'),
(1003, 'Rajdhani Express', 'Kolkata', 'Chennai', 1800.00, 1100.00, 'Daily'),
(1004, 'Kalyan Exp', 'Chennai', 'Bangalore', 1700.00, 1050.00, 'Monday, Friday'),
(1005, 'Maharaja exp', 'Jaipur', 'Jodhpur', 50000.00, 20000.00, 'Wednesday, Saturday');

INSERT INTO Train_Status (train_number, train_date,
 total_ac_seats, total_general_seats, ac_seats_booked,
 general_seats_booked)
VALUES
(1001, '2024-05-05', 50, 100, 10, 20),
(1001, '2024-05-06', 50, 100, 15, 30),
(1001, '2024-05-07', 50, 100, 20, 40),
(1002, '2024-05-05', 60, 120, 5, 10),
(1002, '2024-05-06', 60, 120, 10, 20),
(1003, '2024-05-05', 40, 80, 8, 15),
(1003, '2024-05-06', 40, 80, 12, 25),
(1004, '2024-05-05', 45, 90, 6, 12),
(1004, '2024-05-06', 45, 90, 8, 16),
(1005, '2024-05-05', 100, 40, 90, 23),
(1005, '2024-05-06', 101, 40, 95, 35);

INSERT INTO Passenger (train_number, booking_date, name, age, sex, address, status, ticket_category)
VALUES
(1001, '2024-05-05', 'Gourav Upadhyay', 21, 'Male', '123 Bandra, Mumbai', 'Confirmed', 'AC'),
(1001, '2024-05-05', 'Priya Singh', 25, 'Female', '456 Kirti Nagar, Delhi', 'Confirmed', 'General'),
(1002, '2024-05-05', 'Ajay Kumar', 40, 'Male', '789 New Friends Colony, Delhi', 'Confirmed', 'AC'),
(1002, '2024-05-05', 'Neha Gupta', 35, 'Female', '987 Ballygunge, Kolkata', 'Confirmed', 'General'),
(1003, '2024-05-05', 'Ankit Patel', 28, 'Male', '654 Bhawanipur, Kolkata', 'Confirmed', 'AC'),
(1003, '2024-05-05', 'Riya Jain', 22, 'Female', '321 Anna Nagar, Chennai', 'Confirmed', 'General'),
(1004, '2024-05-05', 'Vivek Reddy', 45, 'Male', '234 Madhavaram, Chennai', 'Confirmed', 'AC'),
(1004, '2024-05-05', 'Shreya Mishra', 20, 'Female', '567 Walnut Street, Bangalore', 'Confirmed', 'General'),
(1005, '2024-05-05', 'Amit Kumar', 33, 'Male', '345 Jayanagar, Bangalore', 'Confirmed', 'AC'),
(1005, '2024-05-05', 'Pooja Sharma', 27, 'Female', '876 Banjara Hills, Hyderabad', 'Confirmed', 'General');


-- Booking procedure
DELIMITER //
CREATE PROCEDURE BookTicket (
    IN gu_train_number INT,
    IN gu_train_date DATE,
    IN gu_category VARCHAR(10),
    IN gu_name VARCHAR(255),
    IN gu_age INT,
    IN gu_sex VARCHAR(10),
    IN gu_address TEXT,
    OUT gu_ticket_id INT,
    OUT gu_booking_status VARCHAR(50)
)
BEGIN
    DECLARE total_seats INT;
    DECLARE total_booked_seats INT;
    -- Retrieve total and booked seats based on train number and date
    IF gu_category = 'AC' THEN
        SELECT total_ac_seats, ac_seats_booked INTO total_seats, total_booked_seats
        FROM Train_Status
        WHERE train_number = gu_train_number AND train_date = gu_train_date;
    ELSE
        SELECT total_general_seats, general_seats_booked INTO total_seats, total_booked_seats
        FROM Train_Status
        WHERE train_number = gu_train_number AND train_date = gu_train_date;
    END IF;

    -- CHECKIGN DOES SEAST AVAILABLE OR NOT
    IF total_booked_seats < total_seats THEN
        -- Insert passenger details into Passenger table
        INSERT INTO Passenger (train_number, booking_date, name, age, sex, address, status, ticket_category)
        VALUES (gu_train_number, gu_train_date, gu_name, gu_age, gu_sex, gu_address, 'Confirmed', gu_category);
        -- Retrieve the auto-generated ticket ID
        SELECT LAST_INSERT_ID() INTO gu_ticket_id;
        SET gu_booking_status = 'Booking Successful';



    ELSE
        SET gu_ticket_id = NULL;
        SET gu_booking_status = 'No seats available for the selected category';
    END IF;
END //

CALL BookTicket(
    1005,
    '2024-05-05',
    'AC',
    'Rohit Upadhyay',
    21,
    'Male',
    '13 Palan Nagar near 60 feet road Indore',
    @ticket_id,
    @booking_status
)

-- CACELLING PROCEDURE
DELIMITER //
CREATE PROCEDURE CancelTicket4(IN P_Ticket_ID INT)
BEGIN
    DECLARE C_Train_No INT;
    DECLARE C_Train_Date DATE;
    DECLARE C_Category VARCHAR(20);

    SELECT train_number, booking_date, ticket_category INTO C_Train_No, C_Train_Date, C_Category
    FROM passenger WHERE ticket_id = P_Ticket_ID;

    DELETE FROM passenger WHERE ticket_id = P_Ticket_ID;

    IF EXISTS (
        SELECT 1 FROM passenger
        WHERE train_number = C_Train_No AND booking_date = C_Train_Date AND ticket_category = C_Category AND status = 'Waiting'
    ) THEN
        UPDATE passenger SET status = 'Confirmed'
        WHERE train_number = C_Train_No AND booking_date = C_Train_Date AND ticket_category = C_Category AND status = 'Waiting' LIMIT 1;
    ELSE
        IF C_Category = 'AC' THEN
            UPDATE train_status
            SET ac_seats_booked = ac_seats_booked - 1
            WHERE train_number = C_Train_No AND train_date = C_Train_Date;
        ELSE
            UPDATE train_status
            SET general_seats_booked = general_seats_booked - 1
            WHERE train_number = C_Train_No AND train_date = C_Train_Date;
        END IF;
    END IF;

    SELECT 'Ticket Deleted Successfully' AS delete_status;
END //










EDITED THE THING GIVEN BY KAUSHAL

delimiter //
CREATE PROCEDURE G_BookTicket(
    IN P_Train_No VARCHAR(10),
    IN P_Train_Date DATE,
    IN P_Category VARCHAR(20),
    IN P_Name VARCHAR(30),
    IN P_Age INT,
    IN P_Sex CHAR(1),
    IN P_Address VARCHAR(30)
)
BEGIN
    DECLARE Total_seats INT;
    DECLARE Booked_seats INT;

    IF P_Category = 'AC' THEN
        SELECT total_ac_seats, ac_seats_booked INTO Total_seats, Booked_seats
        FROM train_status WHERE train_number = P_Train_No AND train_date = P_Train_Date;

    ELSE
        SELECT total_general_seats, general_seats_booked INTO Total_seats, Booked_seats
        FROM train_status
        WHERE train_number = P_Train_No AND train_date = P_Train_Date;
    END IF;

    IF Total_seats > Booked_seats THEN
        INSERT INTO passenger (train_number, booking_date, name, age, sex, address, status, ticket_category)
        VALUES (P_Train_No, P_Train_Date, P_Name, P_Age, P_Sex, P_Address, 'Confirmed', P_Category);

        SELECT 'Ticket Confirmed' AS message;

        IF P_Category = 'AC' THEN
            UPDATE train_status SET ac_seats_booked = ac_seats_booked + 1
            WHERE train_number = P_Train_No AND train_date = P_Train_Date;
        ELSE
            UPDATE train_status SET general_seats_booked = general_seats_booked + 1
            WHERE train_number = P_Train_No AND train_date = P_Train_Date;
        END IF;

    ELSE
        INSERT INTO passenger (train_number, booking_date, name, age, sex, address, status, ticket_category)
        VALUES (P_Train_No, P_Train_Date, P_Name, P_Age, P_Sex, P_Address, 'Waiting', P_Category);

        SELECT 'Sorry, Ticket not confirmed' AS message;
    END IF;
END;