58 lines
1.8 KiB
SQL
58 lines
1.8 KiB
SQL
-- Create the customers table
|
|
CREATE TABLE IF NOT EXISTS `customers` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`name` VARCHAR(255) NOT NULL,
|
|
`email` VARCHAR(255) NOT NULL UNIQUE,
|
|
`phone` VARCHAR(50),
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Create the vehicles table
|
|
CREATE TABLE IF NOT EXISTS `vehicles` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`name` VARCHAR(255) NOT NULL,
|
|
`type` VARCHAR(100),
|
|
`price_per_day` DECIMAL(10, 2) NOT NULL,
|
|
`image_url` VARCHAR(255),
|
|
`description` TEXT,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Create the bookings table for vehicle rentals
|
|
CREATE TABLE IF NOT EXISTS `bookings` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`customer_id` INT NOT NULL,
|
|
`vehicle_id` INT NOT NULL,
|
|
`start_date` DATE NOT NULL,
|
|
`end_date` DATE NOT NULL,
|
|
`total_price` DECIMAL(10, 2) NOT NULL,
|
|
`status` VARCHAR(50) DEFAULT 'pending',
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`customer_id`) REFERENCES `customers`(`id`),
|
|
FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles`(`id`)
|
|
);
|
|
|
|
-- Create the tour_packages table
|
|
CREATE TABLE IF NOT EXISTS `tour_packages` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`name` VARCHAR(255) NOT NULL,
|
|
`description` TEXT,
|
|
`price` DECIMAL(10, 2) NOT NULL,
|
|
`image_url` VARCHAR(255),
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Create the tour_bookings table
|
|
CREATE TABLE IF NOT EXISTS `tour_bookings` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`customer_id` INT NOT NULL,
|
|
`package_id` INT NOT NULL,
|
|
`booking_date` DATE NOT NULL,
|
|
`num_people` INT NOT NULL,
|
|
`total_price` DECIMAL(10, 2) NOT NULL,
|
|
`status` VARCHAR(50) DEFAULT 'pending',
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`customer_id`) REFERENCES `customers`(`id`),
|
|
FOREIGN KEY (`package_id`) REFERENCES `tour_packages`(`id`)
|
|
);
|