CREATE TABLE `users` (
    `id` int PRIMARY KEY,
    `name` varchar(255),
    `gender` varchar(255),
    `mobile` varchar(255) UNIQUE,
    `dob` date,
    `profile_pic` varchar(255),
    `created_at` datetime,
    `updated_at` datetime
);

CREATE TABLE `employeees` (
    `id` int PRIMARY KEY,
    `name` varchar(255),
    `email` varchar(255) UNIQUE,
    `status` tinyint,
    `created_at` datetime,
    `updated_at` datetime
);

CREATE TABLE `otp` (
    `id` int PRIMARY KEY,
    `mobile` varchar(255),
    `sent_at` datetime,
    `last_sent_at` datetime,
    `attempts` int,
    `is_success` bool
);

CREATE TABLE `categories` (
    `id` int PRIMARY KEY,
    `name` varchar(255) UNIQUE,
    `pic` varchar(255),
    `status` tinyint,
    `order` int,
    `created_at` datetime,
    `updated_at` datetime
);

CREATE TABLE `services` (
    `id` int PRIMARY KEY,
    `category_id` int,
    `name` varchar(255) UNIQUE,
    `status` tinyint,
    `order` int,
    `time_in_mins` int,
    `rating` int,
    `price` int,
    `description` text,
    `created_at` datetime,
    `updated_at` datetime
);

CREATE TABLE `media` (
    `id` int PRIMARY KEY,
    `model_type` varchar(255),
    `model_id` int,
    `path` varchar(255),
    `created_at` datetime,
    `updated_at` datetime
);

CREATE TABLE `notifications` (
    `id` int PRIMARY KEY,
    `user_id` int,
    `notification_type` varchar(255),
    `title` varchar(255),
    `data` json,
    `read_at` datetime,
    `created_at` datetime,
    `updated_at` datetime
);

CREATE TABLE `wishlist` (
    `id` int PRIMARY KEY,
    `user_id` int,
    `service_id` int,
    `created_at` datetime,
    `updated_at` datetime
);

CREATE TABLE `reviews` (
    `id` int PRIMARY KEY,
    `user_id` int,
    `service_id` int,
    `rating` float,
    `review` varchar(255),
    `created_at` datetime,
    `updated_at` datetime
);

CREATE TABLE `faqs` (
    `id` int PRIMARY KEY,
    `service_id` int,
    `question` varchar(255),
    `answer` varchar(255),
    `order` int,
    `created_at` datetime,
    `updated_at` datetime
);

CREATE TABLE `carts` (
    `id` int PRIMARY KEY,
    `user_id` int,
    `service_id` int,
    `booking_time` datetime,
    `quantity` int,
    `created_at` datetime,
    `updated_at` datetime
);

CREATE TABLE `beauty_experts` (
    `id` int PRIMARY KEY,
    `name` varchar(255),
    `mobile` varchar(255) UNIQUE,
    `status` varchar(255),
    `created_at` datetime,
    `updated_at` datetime
);

CREATE TABLE `beauty_expert_service` (`beauty_expert_id` int, `service_id` int);

CREATE TABLE `riders` (
    `id` int PRIMARY KEY,
    `name` varchar(255),
    `mobile` varchar(255) UNIQUE,
    `status` varchar(255),
    `created_at` datetime,
    `updated_at` datetime
);

CREATE TABLE `orders` (
    `id` int PRIMARY KEY,
    `user_id` int,
    `address_id` int,
    `quantity` int,
    `status` varchar(255),
    `payment_status` varchar(255),
    `created_at` datetime,
    `updated_at` datetime
);

CREATE TABLE `bookings` (
    `id` int PRIMARY KEY,
    `user_id` int,
    `order_id` int,
    `booking_number` varchar(255) UNIQUE,
    `service_id` int,
    `booking_time` datetime,
    `quantity` int,
    `status` varchar(255),
    `beauty_expert_id` int,
    `rider_id` int,
    `created_at` datetime,
    `updated_at` datetime
);

CREATE TABLE `addresses` (
    `id` int PRIMARY KEY,
    `user_id` int,
    `name` varchar(255),
    `address` varchar(255),
    `created_at` datetime,
    `updated_at` datetime
);

CREATE TABLE `payments` (
    `id` int PRIMARY KEY,
    `user_id` int,
    `order_id` int,
    `amount` float,
    `method` varchar(255),
    `status` varchar(255),
    `created_at` datetime,
    `updated_at` datetime
);

CREATE TABLE `banners` (
    `id` int PRIMARY KEY,
    `name` varchar(255) UNIQUE,
    `status` tinyint,
    `order` int,
    `created_at` datetime,
    `updated_at` datetime
);

ALTER TABLE
    `notifications`
ADD
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

ALTER TABLE
    `wishlist`
ADD
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

ALTER TABLE
    `wishlist`
ADD
    FOREIGN KEY (`service_id`) REFERENCES `services` (`id`);

ALTER TABLE
    `services`
ADD
    FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`);

ALTER TABLE
    `reviews`
ADD
    FOREIGN KEY (`service_id`) REFERENCES `services` (`id`);

ALTER TABLE
    `reviews`
ADD
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

ALTER TABLE
    `carts`
ADD
    FOREIGN KEY (`service_id`) REFERENCES `services` (`id`);

ALTER TABLE
    `carts`
ADD
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

ALTER TABLE
    `bookings`
ADD
    FOREIGN KEY (`service_id`) REFERENCES `services` (`id`);

ALTER TABLE
    `bookings`
ADD
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

ALTER TABLE
    `bookings`
ADD
    FOREIGN KEY (`beauty_expert_id`) REFERENCES `beauty_experts` (`id`);

ALTER TABLE
    `bookings`
ADD
    FOREIGN KEY (`rider_id`) REFERENCES `riders` (`id`);

ALTER TABLE
    `orders`
ADD
    FOREIGN KEY (`address_id`) REFERENCES `addresses` (`id`);

ALTER TABLE
    `orders`
ADD
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

ALTER TABLE
    `payments`
ADD
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

ALTER TABLE
    `payments`
ADD
    FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`);

ALTER TABLE
    `beauty_expert_service`
ADD
    FOREIGN KEY (`beauty_expert_id`) REFERENCES `beauty_experts` (`id`);

ALTER TABLE
    `beauty_expert_service`
ADD
    FOREIGN KEY (`service_id`) REFERENCES `services` (`id`);

ALTER TABLE
    `faqs`
ADD
    FOREIGN KEY (`service_id`) REFERENCES `services` (`id`);
