206 lines
7.2 KiB
SQL
206 lines
7.2 KiB
SQL
-- afg_cars Database Schema
|
|
-- Optimized for University Project Demonstration
|
|
|
|
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
|
|
START TRANSACTION;
|
|
SET time_zone = "+00:00";
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `users`
|
|
--
|
|
|
|
CREATE TABLE IF NOT EXISTS `users` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`full_name` varchar(100) NOT NULL,
|
|
`email` varchar(100) NOT NULL,
|
|
`password` varchar(255) NOT NULL,
|
|
`role` enum('admin','user') NOT NULL DEFAULT 'user',
|
|
`avatar` varchar(255) DEFAULT 'default_user.png',
|
|
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `email` (`email`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
--
|
|
-- Dumping data for table `users`
|
|
--
|
|
|
|
INSERT INTO `users` (`full_name`, `email`, `password`, `role`) VALUES
|
|
('System Administrator', 'admin@gmail.com', '$2y$10$6mUq8K0vS.p8E8m1E8m1E8m1E8m1E8m1E8m1E8m1E8m1E8m1E8m1E', 'admin'),
|
|
('Demo User', 'user@gmail.com', '$2y$10$6mUq8K0vS.p8E8m1E8m1E8m1E8m1E8m1E8m1E8m1E8m1E8m1E8m1E', 'user');
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `cars`
|
|
--
|
|
|
|
CREATE TABLE IF NOT EXISTS `cars` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`user_id` int(11) NOT NULL,
|
|
`title` varchar(255) NOT NULL,
|
|
`brand` varchar(50) NOT NULL,
|
|
`model` varchar(50) NOT NULL,
|
|
`year` int(4) NOT NULL,
|
|
`price` decimal(15,2) NOT NULL,
|
|
`city` enum('Kabul','Herat','Mazar-i-Sharif','Kandahar') NOT NULL,
|
|
`condition` enum('New','Used','Certified Pre-Owned') NOT NULL DEFAULT 'Used',
|
|
`fuel_type` enum('Gasoline','Diesel','Hybrid','Electric') NOT NULL DEFAULT 'Gasoline',
|
|
`transmission` enum('Manual','Automatic') NOT NULL DEFAULT 'Automatic',
|
|
`mileage` int(11) NOT NULL,
|
|
`description` text NOT NULL,
|
|
`status` enum('Available','SOLD','Pending') NOT NULL DEFAULT 'Available',
|
|
`is_hot_deal` tinyint(1) NOT NULL DEFAULT 0,
|
|
`is_deleted` tinyint(1) NOT NULL DEFAULT 0,
|
|
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
|
|
PRIMARY KEY (`id`),
|
|
KEY `user_id` (`user_id`),
|
|
KEY `status` (`status`),
|
|
KEY `brand` (`brand`),
|
|
KEY `is_deleted` (`is_deleted`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
--
|
|
-- Dumping data for table `cars`
|
|
--
|
|
|
|
INSERT INTO `cars` (`user_id`, `title`, `brand`, `model`, `year`, `price`, `city`, `mileage`, `description`, `status`, `is_hot_deal`) VALUES
|
|
(1, 'Toyota Land Cruiser 2024 VXR', 'Toyota', 'Land Cruiser', 2024, 125000.00, 'Kabul', 0, 'Brand new Toyota Land Cruiser 2024 VXR. Fully loaded with premium features.', 'Available', 1),
|
|
(1, 'Mercedes-Benz G-Class 2023 AMG', 'Mercedes', 'G-Class', 2023, 185000.00, 'Kabul', 500, 'Luxurious Mercedes G-Class 63 AMG. Perfect condition, barely driven.', 'Available', 0),
|
|
(1, 'BMW X7 xDrive40i 2022', 'BMW', 'X7', 2022, 95000.00, 'Herat', 15000, 'Well-maintained BMW X7. Silver metallic finish. Full service history.', 'Available', 0),
|
|
(1, 'Lexus LX 600 2024 Ultra Luxury', 'Lexus', 'LX 600', 2024, 150000.00, 'Kandahar', 100, 'Top of the line Lexus LX 600. Ultra luxury trim. 2024 model.', 'Available', 1),
|
|
(1, 'Hyundai Tucson 2021 Sport', 'Hyundai', 'Tucson', 2021, 32000.00, 'Mazar-i-Sharif', 45000, 'Reliable family SUV. Hyundai Tucson 2021. Great fuel economy.', 'SOLD', 0);
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `car_images`
|
|
--
|
|
|
|
CREATE TABLE IF NOT EXISTS `car_images` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`car_id` int(11) NOT NULL,
|
|
`image_path` varchar(255) NOT NULL,
|
|
`is_main` tinyint(1) NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (`id`),
|
|
KEY `car_id` (`car_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `favorites`
|
|
--
|
|
|
|
CREATE TABLE IF NOT EXISTS `favorites` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`user_id` int(11) NOT NULL,
|
|
`car_id` int(11) NOT NULL,
|
|
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
|
|
PRIMARY KEY (`id`),
|
|
KEY `user_id` (`user_id`),
|
|
KEY `car_id` (`car_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `reviews`
|
|
--
|
|
|
|
CREATE TABLE IF NOT EXISTS `reviews` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`user_id` int(11) NOT NULL,
|
|
`car_id` int(11) NOT NULL,
|
|
`rating` int(1) NOT NULL CHECK (`rating` >= 1 AND `rating` <= 5),
|
|
`comment` text DEFAULT NULL,
|
|
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
|
|
PRIMARY KEY (`id`),
|
|
KEY `user_id` (`user_id`),
|
|
KEY `car_id` (`car_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `purchases`
|
|
--
|
|
|
|
CREATE TABLE IF NOT EXISTS `purchases` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`user_id` int(11) NOT NULL,
|
|
`car_id` int(11) NOT NULL,
|
|
`bank_name` varchar(100) NOT NULL,
|
|
`transaction_id` varchar(100) NOT NULL,
|
|
`amount` decimal(15,2) NOT NULL,
|
|
`purchase_date` timestamp NOT NULL DEFAULT current_timestamp(),
|
|
PRIMARY KEY (`id`),
|
|
KEY `user_id` (`user_id`),
|
|
KEY `car_id` (`car_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `contact_messages`
|
|
--
|
|
|
|
CREATE TABLE IF NOT EXISTS `contact_messages` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`name` varchar(100) NOT NULL,
|
|
`email` varchar(100) NOT NULL,
|
|
`subject` varchar(255) DEFAULT NULL,
|
|
`message` text NOT NULL,
|
|
`is_read` tinyint(1) NOT NULL DEFAULT 0,
|
|
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `notifications`
|
|
--
|
|
|
|
CREATE TABLE IF NOT EXISTS `notifications` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`user_id` int(11) NOT NULL,
|
|
`title` varchar(255) NOT NULL,
|
|
`message` text NOT NULL,
|
|
`is_read` tinyint(1) NOT NULL DEFAULT 0,
|
|
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
|
|
PRIMARY KEY (`id`),
|
|
KEY `user_id` (`user_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Constraints for dumped tables
|
|
--
|
|
|
|
ALTER TABLE `cars`
|
|
ADD CONSTRAINT `cars_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;
|
|
|
|
ALTER TABLE `car_images`
|
|
ADD CONSTRAINT `car_images_ibfk_1` FOREIGN KEY (`car_id`) REFERENCES `cars` (`id`) ON DELETE CASCADE;
|
|
|
|
ALTER TABLE `favorites`
|
|
ADD CONSTRAINT `favorites_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
|
|
ADD CONSTRAINT `favorites_ibfk_2` FOREIGN KEY (`car_id`) REFERENCES `cars` (`id`) ON DELETE CASCADE;
|
|
|
|
ALTER TABLE `reviews`
|
|
ADD CONSTRAINT `reviews_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
|
|
ADD CONSTRAINT `reviews_ibfk_2` FOREIGN KEY (`car_id`) REFERENCES `cars` (`id`) ON DELETE CASCADE;
|
|
|
|
ALTER TABLE `purchases`
|
|
ADD CONSTRAINT `purchases_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
|
|
ADD CONSTRAINT `purchases_ibfk_2` FOREIGN KEY (`car_id`) REFERENCES `cars` (`id`) ON DELETE CASCADE;
|
|
|
|
ALTER TABLE `notifications`
|
|
ADD CONSTRAINT `notifications_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;
|
|
|
|
COMMIT;
|