-- ============================================
-- Shadi Hall Booking System - SERVER VERSION
-- Is file ko cPanel ke phpMyAdmin me import karein
-- Database pehle cPanel se banana hai, phir is file ko us database me import karein
-- ============================================

SET FOREIGN_KEY_CHECKS=0;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

-- Users table (Super Admin, Vendor, User)
CREATE TABLE IF NOT EXISTS `users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `email` VARCHAR(100) UNIQUE NOT NULL,
    `password` VARCHAR(255) NOT NULL,
    `phone` VARCHAR(20),
    `role` ENUM('admin','vendor','user') NOT NULL DEFAULT 'user',
    `status` ENUM('active','pending','blocked') DEFAULT 'active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Shadi Halls table
CREATE TABLE IF NOT EXISTS `halls` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `vendor_id` INT NOT NULL,
    `hall_name` VARCHAR(150) NOT NULL,
    `description` TEXT,
    `location` VARCHAR(255),
    `capacity` INT,
    `price_per_day` DECIMAL(10,2),
    `main_image` VARCHAR(255),
    `video` VARCHAR(255),
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`vendor_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Hall Gallery
CREATE TABLE IF NOT EXISTS `hall_gallery` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `hall_id` INT NOT NULL,
    `image` VARCHAR(255) NOT NULL,
    FOREIGN KEY (`hall_id`) REFERENCES `halls`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dishes Menu
CREATE TABLE IF NOT EXISTS `dishes` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `hall_id` INT NOT NULL,
    `dish_name` VARCHAR(150) NOT NULL,
    `description` TEXT,
    `price` DECIMAL(10,2) NOT NULL,
    `category` VARCHAR(50),
    `image` VARCHAR(255),
    FOREIGN KEY (`hall_id`) REFERENCES `halls`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Available Dates
CREATE TABLE IF NOT EXISTS `hall_dates` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `hall_id` INT NOT NULL,
    `available_date` DATE NOT NULL,
    `status` ENUM('available','booked') DEFAULT 'available',
    FOREIGN KEY (`hall_id`) REFERENCES `halls`(`id`) ON DELETE CASCADE,
    UNIQUE KEY `unique_hall_date` (`hall_id`, `available_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bookings
CREATE TABLE IF NOT EXISTS `bookings` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `hall_id` INT NOT NULL,
    `booking_date` DATE NOT NULL,
    `guests` INT,
    `total_amount` DECIMAL(10,2),
    `status` ENUM('pending','confirmed','cancelled') DEFAULT 'pending',
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`hall_id`) REFERENCES `halls`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Messages
CREATE TABLE IF NOT EXISTS `messages` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `sender_id` INT NOT NULL,
    `receiver_id` INT NOT NULL,
    `subject` VARCHAR(200),
    `message` TEXT NOT NULL,
    `is_read` TINYINT DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`sender_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`receiver_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Default Super Admin 
-- Email: admin@shadihall.com | Password: admin123
-- IMPORTANT: Login ke baad foran password change karein!
-- ============================================
INSERT INTO `users` (`name`, `email`, `password`, `phone`, `role`, `status`) VALUES
('Super Admin', 'admin@shadihall.com', '$2y$10$n5VbpnTaWJc8PffYXn9NmO1/pkWMoJ/bpSCl323USPv8n8zsZXr4S', '03001234567', 'admin', 'active');

SET FOREIGN_KEY_CHECKS=1;
