-- Hotel Booking Database Schema
-- Database: hotel_booking_db
-- This file contains all tables needed for the Almaris Hotel Booking System

-- =====================================================
-- Create Database
-- =====================================================
CREATE DATABASE IF NOT EXISTS hotel_booking_db;
USE hotel_booking_db;

-- =====================================================
-- Table: users (Guests and Admin Users)
-- =====================================================
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(20),
    password VARCHAR(255) NOT NULL,
    user_type ENUM('guest', 'admin', 'staff') DEFAULT 'guest',
    profile_picture VARCHAR(255),
    address VARCHAR(255),
    city VARCHAR(50),
    country VARCHAR(50),
    postal_code VARCHAR(20),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_login DATETIME,
    INDEX idx_email (email),
    INDEX idx_user_type (user_type)
);

-- =====================================================
-- Table: rooms (Hotel Rooms)
-- =====================================================
CREATE TABLE IF NOT EXISTS rooms (
    id INT AUTO_INCREMENT PRIMARY KEY,
    room_number VARCHAR(10) UNIQUE NOT NULL,
    room_type VARCHAR(50) NOT NULL,
    description TEXT,
    max_guests INT NOT NULL,
    price_per_night DECIMAL(10, 2) NOT NULL,
    size_sqft INT,
    amenities JSON,
    is_available BOOLEAN DEFAULT TRUE,
    floor_number INT,
    view_type VARCHAR(50),
    image_url VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_room_type (room_type),
    INDEX idx_available (is_available)
);

-- =====================================================
-- Table: reservations (Room Bookings)
-- =====================================================
CREATE TABLE IF NOT EXISTS reservations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    room_id INT NOT NULL,
    check_in_date DATE NOT NULL,
    check_out_date DATE NOT NULL,
    number_of_guests INT NOT NULL,
    number_of_rooms INT DEFAULT 1,
    total_price DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'confirmed', 'checked_in', 'checked_out', 'cancelled') DEFAULT 'pending',
    special_requests TEXT,
    payment_status ENUM('unpaid', 'partial', 'paid') DEFAULT 'unpaid',
    payment_method VARCHAR(50),
    transaction_id VARCHAR(100),
    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,
    FOREIGN KEY (room_id) REFERENCES rooms(id),
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_check_in (check_in_date)
);

-- =====================================================
-- Table: facilities (Hotel Amenities/Services)
-- =====================================================
CREATE TABLE IF NOT EXISTS facilities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    icon VARCHAR(255),
    image_url VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_name (name)
);

-- =====================================================
-- Table: service_reservations (Facility/Service Bookings)
-- =====================================================
CREATE TABLE IF NOT EXISTS service_reservations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    facility_id INT NOT NULL,
    service_start_date DATE NOT NULL,
    service_end_date DATE NOT NULL,
    number_of_guests INT NOT NULL DEFAULT 1,
    service_price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    total_price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    payment_status ENUM('unpaid', 'partial', 'paid') DEFAULT 'unpaid',
    status ENUM('pending', 'confirmed', 'completed', 'cancelled') DEFAULT 'pending',
    special_requests TEXT,
    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,
    FOREIGN KEY (facility_id) REFERENCES facilities(id),
    INDEX idx_service_user (user_id),
    INDEX idx_service_facility (facility_id),
    INDEX idx_service_dates (service_start_date, service_end_date)
);

-- =====================================================
-- Table: news (Blog/News Articles)
-- =====================================================
CREATE TABLE IF NOT EXISTS news (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    content LONGTEXT NOT NULL,
    summary VARCHAR(500),
    author_id INT,
    featured_image VARCHAR(255),
    status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
    views INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    published_at DATETIME,
    FOREIGN KEY (author_id) REFERENCES users(id),
    INDEX idx_slug (slug),
    INDEX idx_status (status)
);

-- =====================================================
-- Table: gallery (Image Gallery)
-- =====================================================
CREATE TABLE IF NOT EXISTS gallery (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    image_url VARCHAR(255) NOT NULL,
    category VARCHAR(100),
    sort_order INT DEFAULT 0,
    is_featured BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_category (category)
);

-- =====================================================
-- Table: special_offers (Promotional Offers)
-- =====================================================
CREATE TABLE IF NOT EXISTS special_offers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    discount_type ENUM('percentage', 'fixed_amount') NOT NULL,
    discount_value DECIMAL(10, 2) NOT NULL,
    applicable_rooms JSON,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    max_redemptions INT,
    redemptions_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_active (is_active),
    INDEX idx_dates (start_date, end_date)
);

-- =====================================================
-- Table: payments (Payment Records)
-- =====================================================
CREATE TABLE IF NOT EXISTS payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    reservation_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    payment_method VARCHAR(50) NOT NULL,
    transaction_id VARCHAR(100) UNIQUE,
    status ENUM('pending', 'completed', 'failed', 'refunded') DEFAULT 'pending',
    payment_date DATETIME,
    refund_date DATETIME,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (reservation_id) REFERENCES reservations(id),
    INDEX idx_status (status),
    INDEX idx_transaction (transaction_id)
);

-- =====================================================
-- Table: reviews (Guest Reviews and Ratings)
-- =====================================================
CREATE TABLE IF NOT EXISTS reviews (
    id INT AUTO_INCREMENT PRIMARY KEY,
    reservation_id INT NOT NULL,
    user_id INT NOT NULL,
    rating INT NOT NULL COMMENT '1-5 stars',
    comment TEXT,
    room_cleanliness INT,
    staff_service INT,
    amenities_satisfaction INT,
    overall_experience INT,
    is_verified BOOLEAN DEFAULT FALSE,
    is_published BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (reservation_id) REFERENCES reservations(id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_rating (rating)
);

-- =====================================================
-- Table: contact_messages (Contact Form Messages)
-- =====================================================
CREATE TABLE IF NOT EXISTS contact_messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    subject VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    status ENUM('new', 'read', 'responded') DEFAULT 'new',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_created (created_at)
);

-- =====================================================
-- Sample Data Insertion
-- =====================================================

-- Insert Sample Rooms
INSERT INTO rooms (room_number, room_type, description, max_guests, price_per_night, size_sqft, floor_number, view_type, image_url) VALUES
('101', 'Standard Room', 'Comfortable room with modern amenities', 2, 129.00, 280, 1, 'City View', 'images/room/1.webp'),
('102', 'Deluxe Room', 'Spacious room with premium bedding and refined details', 2, 149.00, 320, 1, 'City View', 'images/room/2.webp'),
('103', 'Premier Room', 'Stylish room with upgraded amenities', 2, 159.00, 350, 1, 'Ocean View', 'images/room/3.webp'),
('104', 'Family Suite', 'Perfect for group stays with generous layout', 4, 179.00, 450, 1, 'City View', 'images/room/4.webp'),
('105', 'Luxury Suite', 'Premium comfort with curated design', 4, 199.00, 500, 1, 'Ocean View', 'images/room/5.webp'),
('106', 'Presidential Suite', 'Top-tier luxury with spacious living', 4, 249.00, 600, 2, 'Panoramic', 'images/room/6.webp');

-- Insert Sample Facilities
INSERT INTO facilities (name, description, icon, image_url) VALUES
('Restaurant', 'Award-winning dining with all-day service', 'restaurant-svgrepo-com.svg', 'images/svg/restaurant-svgrepo-com.svg'),
('Swimming Pool', 'Spacious pool area with panoramic views', 'swimming-pool-svgrepo-com.svg', 'images/svg/swimming-pool-svgrepo-com.svg'),
('Fitness Center', 'Modern equipment with professional support', 'fitness-gym-svgrepo-com.svg', 'images/svg/fitness-gym-svgrepo-com.svg'),
('Spa & Massage', 'Rejuvenating treatments to unwind', 'flower-lotus-thin-svgrepo-com.svg', 'images/svg/flower-lotus-thin-svgrepo-com.svg'),
('Meeting Rooms', 'Business-ready spaces with fast connectivity', 'meeting-explain-svgrepo-com.svg', 'images/svg/meeting-explain-svgrepo-com.svg'),
('Laundry Service', 'Same-day care for your convenience', 'laundry-machine-svgrepo-com.svg', 'images/svg/laundry-machine-svgrepo-com.svg');
