-- Flight & Hotel Reservation System Database Schema

CREATE DATABASE IF NOT EXISTS `devamsproteamco_booking`;
USE `devamsproteamco_booking`;

-- 1. Departments Master
CREATE TABLE IF NOT EXISTS `departments` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 2. Users Master (RBAC)
CREATE TABLE IF NOT EXISTS `users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `email` VARCHAR(100) NOT NULL UNIQUE,
    `password` VARCHAR(255) NOT NULL,
    `role` ENUM('admin', 'agent', 'corporate', 'customer') NOT NULL DEFAULT 'customer',
    `department_id` INT NULL,
    `status` ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`department_id`) REFERENCES `departments`(`id`) ON DELETE SET NULL
);

-- 3. Agents Extensions
CREATE TABLE IF NOT EXISTS `agents` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `agency_name` VARCHAR(150),
    `credit_limit` DECIMAL(10,2) DEFAULT 0.00,
    `commission_rate` DECIMAL(5,2) DEFAULT 0.00,
    `status` ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
);

-- 4. Markup Rules
CREATE TABLE IF NOT EXISTS `markup_rules` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `type` ENUM('flight', 'hotel') NOT NULL,
    `target_role` ENUM('admin', 'agent', 'corporate', 'customer') NULL,
    `markup_type` ENUM('percentage', 'fixed') NOT NULL,
    `value` DECIMAL(10,2) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 5. Flights Inventory
CREATE TABLE IF NOT EXISTS `flights` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `airline` VARCHAR(100) NOT NULL,
    `flight_number` VARCHAR(20) NOT NULL,
    `source` VARCHAR(100) NOT NULL,
    `destination` VARCHAR(100) NOT NULL,
    `departure_time` DATETIME NOT NULL,
    `arrival_time` DATETIME NOT NULL,
    `base_price` DECIMAL(10,2) NOT NULL,
    `seats_available` INT NOT NULL DEFAULT 60,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 6. Hotels Inventory
CREATE TABLE IF NOT EXISTS `hotels` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(150) NOT NULL,
    `city` VARCHAR(100) NOT NULL,
    `star_rating` INT DEFAULT 3,
    `base_price_per_night` DECIMAL(10,2) NOT NULL,
    `description` TEXT,
    `image_url` VARCHAR(255),
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 7. Bookings
CREATE TABLE IF NOT EXISTS `bookings` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `booking_type` ENUM('flight', 'hotel') NOT NULL,
    `item_id` INT NOT NULL,  -- references flight_id or hotel_id
    `status` ENUM('confirmed', 'cancelled', 'pending') NOT NULL DEFAULT 'pending',
    `total_amount` DECIMAL(10,2) NOT NULL,
    `booking_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
);

-- 8. Passengers (for flight bookings)
CREATE TABLE IF NOT EXISTS `passengers` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `booking_id` INT NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `age` INT NOT NULL,
    `gender` ENUM('M', 'F', 'O') NOT NULL,
    `seat_number` VARCHAR(10),
    FOREIGN KEY (`booking_id`) REFERENCES `bookings`(`id`) ON DELETE CASCADE
);

-- Seeding some dummy data

INSERT INTO `departments` (`name`) VALUES ('Sales'), ('Operations'), ('Support');

INSERT INTO `users` (`name`, `email`, `password`, `role`, `status`) 
VALUES 
('Super Admin', 'admin@example.com', '$2y$10$wYm2tW1mGIfT1Z/XJIn6kOw.aU.z6vL/y3V9d5D37N7Cg.JmXU1uO', 'admin', 'active'), -- password is 'password'
('Agent Demo', 'agent@example.com', '$2y$10$wYm2tW1mGIfT1Z/XJIn6kOw.aU.z6vL/y3V9d5D37N7Cg.JmXU1uO', 'agent', 'active'),
('Customer Demo', 'customer@example.com', '$2y$10$wYm2tW1mGIfT1Z/XJIn6kOw.aU.z6vL/y3V9d5D37N7Cg.JmXU1uO', 'customer', 'active');

INSERT INTO `agents` (`user_id`, `agency_name`, `status`) VALUES (2, 'Fast Travel LLC', 'approved');

INSERT INTO `flights` (`airline`, `flight_number`, `source`, `destination`, `departure_time`, `arrival_time`, `base_price`) 
VALUES 
('IndiGo', '6E-101', 'Delhi', 'Mumbai', DATE_ADD(NOW(), INTERVAL 1 DAY), DATE_ADD(NOW(), INTERVAL '1 2' DAY_HOUR), 4500.00),
('Air India', 'AI-302', 'Mumbai', 'Bangalore', DATE_ADD(NOW(), INTERVAL 2 DAY), DATE_ADD(NOW(), INTERVAL '2 2' DAY_HOUR), 5500.00);

INSERT INTO `hotels` (`name`, `city`, `star_rating`, `base_price_per_night`) 
VALUES 
('Taj Mahal Palace', 'Mumbai', 5, 12000.00),
('Oberoi', 'Delhi', 5, 10500.00),
('Budget Inn', 'Bangalore', 3, 2500.00);

