-- Hotel Management System Database Schema
-- Created: 2026-06-16

CREATE DATABASE IF NOT EXISTS hotel_management CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE hotel_management;

-- Users Table (for login system)
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    role ENUM('admin', 'manager', 'receptionist', 'staff') DEFAULT 'staff',
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Hotels Table
CREATE TABLE hotels (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    address TEXT,
    phone VARCHAR(20),
    email VARCHAR(100),
    gst_number VARCHAR(50),
    logo VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Rooms Table
CREATE TABLE rooms (
    id INT AUTO_INCREMENT PRIMARY KEY,
    hotel_id INT,
    room_number VARCHAR(20) NOT NULL,
    room_type ENUM('single', 'double', 'suite', 'deluxe', 'family') DEFAULT 'single',
    price_per_night DECIMAL(10,2) NOT NULL,
    capacity INT DEFAULT 2,
    description TEXT,
    amenities TEXT,
    status ENUM('available', 'occupied', 'maintenance', 'cleaning') DEFAULT 'available',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE
);

-- Guests Table
CREATE TABLE guests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20) NOT NULL,
    address TEXT,
    id_proof_type VARCHAR(50),
    id_proof_number VARCHAR(100),
    nationality VARCHAR(50) DEFAULT 'Indian',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Bookings Table
CREATE TABLE bookings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    guest_id INT NOT NULL,
    room_id INT NOT NULL,
    check_in DATE NOT NULL,
    check_out DATE NOT NULL,
    adults INT DEFAULT 1,
    children INT DEFAULT 0,
    total_amount DECIMAL(12,2) NOT NULL,
    paid_amount DECIMAL(12,2) DEFAULT 0,
    payment_status ENUM('pending', 'partial', 'paid', 'refunded') DEFAULT 'pending',
    booking_status ENUM('confirmed', 'checked_in', 'checked_out', 'cancelled') DEFAULT 'confirmed',
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (guest_id) REFERENCES guests(id),
    FOREIGN KEY (room_id) REFERENCES rooms(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

-- Billing Table
CREATE TABLE bills (
    id INT AUTO_INCREMENT PRIMARY KEY,
    booking_id INT NOT NULL,
    bill_number VARCHAR(50) UNIQUE NOT NULL,
    subtotal DECIMAL(12,2) NOT NULL,
    gst_rate DECIMAL(5,2) DEFAULT 18.00,
    gst_amount DECIMAL(12,2) NOT NULL,
    cgst_amount DECIMAL(12,2) NOT NULL,
    sgst_amount DECIMAL(12,2) NOT NULL,
    discount DECIMAL(12,2) DEFAULT 0,
    total_amount DECIMAL(12,2) NOT NULL,
    paid_amount DECIMAL(12,2) DEFAULT 0,
    payment_method ENUM('cash', 'card', 'upi', 'netbanking', 'wallet') DEFAULT 'cash',
    transaction_id VARCHAR(100),
    status ENUM('unpaid', 'partial', 'paid', 'cancelled') DEFAULT 'unpaid',
    bill_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (booking_id) REFERENCES bookings(id)
);

-- Bill Items Table
CREATE TABLE bill_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    bill_id INT NOT NULL,
    description VARCHAR(255) NOT NULL,
    quantity INT DEFAULT 1,
    unit_price DECIMAL(10,2) NOT NULL,
    total_price DECIMAL(10,2) NOT NULL,
    item_type ENUM('room', 'food', 'service', 'tax', 'other') DEFAULT 'other',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (bill_id) REFERENCES bills(id) ON DELETE CASCADE
);

-- Food Categories Table
CREATE TABLE food_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Food Items Table
CREATE TABLE food_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    cost_price DECIMAL(10,2) DEFAULT 0,
    image VARCHAR(255),
    is_veg BOOLEAN DEFAULT TRUE,
    status ENUM('available', 'unavailable') DEFAULT 'available',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES food_categories(id)
);

-- Food Orders Table
CREATE TABLE food_orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    booking_id INT,
    guest_id INT,
    room_id INT,
    order_type ENUM('dine_in', 'room_service', 'takeaway') DEFAULT 'room_service',
    total_amount DECIMAL(12,2) NOT NULL,
    gst_amount DECIMAL(12,2) DEFAULT 0,
    discount DECIMAL(10,2) DEFAULT 0,
    final_amount DECIMAL(12,2) NOT NULL,
    payment_status ENUM('pending', 'paid') DEFAULT 'pending',
    order_status ENUM('placed', 'preparing', 'ready', 'delivered', 'cancelled') DEFAULT 'placed',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (booking_id) REFERENCES bookings(id),
    FOREIGN KEY (guest_id) REFERENCES guests(id),
    FOREIGN KEY (room_id) REFERENCES rooms(id)
);

-- Food Order Items Table
CREATE TABLE food_order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    food_item_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    total_price DECIMAL(10,2) NOT NULL,
    special_instructions TEXT,
    FOREIGN KEY (order_id) REFERENCES food_orders(id) ON DELETE CASCADE,
    FOREIGN KEY (food_item_id) REFERENCES food_items(id)
);

-- Staff Table
CREATE TABLE staff (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id VARCHAR(50) UNIQUE NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20) NOT NULL,
    address TEXT,
    designation VARCHAR(100) NOT NULL,
    department ENUM('front_office', 'housekeeping', 'kitchen', 'maintenance', 'management', 'security', 'other') DEFAULT 'other',
    join_date DATE NOT NULL,
    salary DECIMAL(12,2) NOT NULL,
    bank_account VARCHAR(50),
    bank_name VARCHAR(100),
    ifsc_code VARCHAR(20),
    emergency_contact VARCHAR(20),
    status ENUM('active', 'inactive', 'on_leave', 'terminated') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Staff Attendance Table
CREATE TABLE staff_attendance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    staff_id INT NOT NULL,
    date DATE NOT NULL,
    status ENUM('present', 'absent', 'half_day', 'leave', 'holiday') DEFAULT 'present',
    check_in TIME,
    check_out TIME,
    overtime_hours DECIMAL(4,2) DEFAULT 0,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (staff_id) REFERENCES staff(id)
);

-- Salary Payments Table
CREATE TABLE salary_payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    staff_id INT NOT NULL,
    month INT NOT NULL,
    year INT NOT NULL,
    basic_salary DECIMAL(12,2) NOT NULL,
    hra DECIMAL(12,2) DEFAULT 0,
    da DECIMAL(12,2) DEFAULT 0,
    ta DECIMAL(12,2) DEFAULT 0,
    overtime_amount DECIMAL(12,2) DEFAULT 0,
    bonus DECIMAL(12,2) DEFAULT 0,
    deductions DECIMAL(12,2) DEFAULT 0,
    advance_deduction DECIMAL(12,2) DEFAULT 0,
    pf_amount DECIMAL(12,2) DEFAULT 0,
    esi_amount DECIMAL(12,2) DEFAULT 0,
    tds_amount DECIMAL(12,2) DEFAULT 0,
    gross_salary DECIMAL(12,2) NOT NULL,
    net_salary DECIMAL(12,2) NOT NULL,
    payment_date DATE,
    payment_method ENUM('cash', 'bank_transfer', 'cheque') DEFAULT 'bank_transfer',
    transaction_id VARCHAR(100),
    status ENUM('pending', 'paid', 'failed') DEFAULT 'pending',
    notification_sent BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (staff_id) REFERENCES staff(id)
);

-- Expenses Table
CREATE TABLE expenses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category VARCHAR(100) NOT NULL,
    description TEXT,
    amount DECIMAL(12,2) NOT NULL,
    expense_date DATE NOT NULL,
    payment_method ENUM('cash', 'card', 'upi', 'bank_transfer', 'cheque') DEFAULT 'cash',
    bill_number VARCHAR(100),
    vendor_name VARCHAR(200),
    gst_amount DECIMAL(12,2) DEFAULT 0,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id)
);

-- Income Table
CREATE TABLE income (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category VARCHAR(100) NOT NULL,
    description TEXT,
    amount DECIMAL(12,2) NOT NULL,
    income_date DATE NOT NULL,
    payment_method ENUM('cash', 'card', 'upi', 'bank_transfer', 'wallet') DEFAULT 'cash',
    reference_number VARCHAR(100),
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id)
);

-- GST Reports Table
CREATE TABLE gst_reports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    report_period VARCHAR(50) NOT NULL,
    report_type ENUM('monthly', 'quarterly', 'yearly') NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    total_sales DECIMAL(12,2) NOT NULL,
    total_purchases DECIMAL(12,2) NOT NULL,
    cgst_collected DECIMAL(12,2) DEFAULT 0,
    sgst_collected DECIMAL(12,2) DEFAULT 0,
    igst_collected DECIMAL(12,2) DEFAULT 0,
    cgst_paid DECIMAL(12,2) DEFAULT 0,
    sgst_paid DECIMAL(12,2) DEFAULT 0,
    igst_paid DECIMAL(12,2) DEFAULT 0,
    net_gst_payable DECIMAL(12,2) DEFAULT 0,
    status ENUM('draft', 'filed', 'paid') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Notifications Table
CREATE TABLE notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    staff_id INT,
    type ENUM('salary', 'booking', 'order', 'bill', 'system', 'payment') NOT NULL,
    title VARCHAR(200) NOT NULL,
    message TEXT NOT NULL,
    is_read BOOLEAN DEFAULT FALSE,
    sent_via ENUM('app', 'sms', 'email') DEFAULT 'app',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (staff_id) REFERENCES staff(id)
);

-- Payment Gateway Logs
CREATE TABLE payment_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    reference_id INT,
    reference_type ENUM('booking', 'bill', 'salary', 'subscription') NOT NULL,
    gateway VARCHAR(50) NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    currency VARCHAR(10) DEFAULT 'INR',
    status ENUM('initiated', 'success', 'failed', 'refunded') DEFAULT 'initiated',
    gateway_response TEXT,
    transaction_id VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Monthly Subscriptions
CREATE TABLE subscriptions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    plan_name VARCHAR(100) NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    status ENUM('active', 'expired', 'cancelled') DEFAULT 'active',
    payment_status ENUM('pending', 'paid', 'failed') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Insert Default Admin User (password: admin123)
INSERT INTO users (username, email, password, full_name, phone, role) VALUES 
('admin', 'admin@hotel.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'System Administrator', '9876543210', 'admin');

-- Insert Sample Hotel
INSERT INTO hotels (name, address, phone, email, gst_number) VALUES 
('Royal Hotel & Resorts', '123 Main Street, City Center', '9876543210', 'info@royalhotel.com', '27AABCU9603R1ZM');

-- Insert Sample Food Categories
INSERT INTO food_categories (name, description) VALUES 
('Breakfast', 'Morning breakfast items'),
('Lunch', 'Lunch menu items'),
('Dinner', 'Dinner specialties'),
('Snacks', 'Evening snacks and beverages'),
('Desserts', 'Sweet dishes and desserts'),
('Beverages', 'Hot and cold drinks');

-- Insert Sample Rooms
INSERT INTO rooms (hotel_id, room_number, room_type, price_per_night, capacity, description, amenities) VALUES 
(1, '101', 'single', 1500.00, 1, 'Comfortable single room with city view', 'AC, TV, WiFi, Attached Bathroom'),
(1, '102', 'double', 2500.00, 2, 'Spacious double room with balcony', 'AC, TV, WiFi, Mini Fridge, Attached Bathroom'),
(1, '201', 'suite', 5000.00, 4, 'Luxury suite with living area', 'AC, TV, WiFi, Mini Bar, Jacuzzi, Living Room, Kitchenette'),
(1, '202', 'deluxe', 3500.00, 2, 'Deluxe room with premium amenities', 'AC, Smart TV, WiFi, Mini Bar, Coffee Maker, Attached Bathroom'),
(1, '301', 'family', 4000.00, 5, 'Family room with multiple beds', 'AC, TV, WiFi, Mini Fridge, Attached Bathroom, Extra Bed');

-- Insert Sample Food Items
INSERT INTO food_items (category_id, name, description, price, cost_price, is_veg) VALUES 
(1, 'Masala Dosa', 'Crispy rice crepe with potato filling', 120.00, 40.00, TRUE),
(1, 'Idli Sambar', 'Steamed rice cakes with lentil soup', 80.00, 25.00, TRUE),
(1, 'Poha', 'Flattened rice with spices and peanuts', 60.00, 20.00, TRUE),
(2, 'Veg Thali', 'Complete vegetarian meal with rice, roti, dal, sabzi', 250.00, 100.00, TRUE),
(2, 'Chicken Biryani', 'Aromatic rice with tender chicken pieces', 350.00, 150.00, FALSE),
(2, 'Paneer Butter Masala', 'Cottage cheese in rich tomato gravy', 280.00, 120.00, TRUE),
(3, 'Butter Chicken', 'Tandoori chicken in buttery tomato gravy', 400.00, 180.00, FALSE),
(3, 'Dal Makhani', 'Creamy black lentils', 220.00, 80.00, TRUE),
(4, 'Samosa', 'Crispy pastry with spiced potato filling', 30.00, 10.00, TRUE),
(4, 'Vada Pav', 'Spiced potato fritter in bun', 40.00, 15.00, TRUE),
(5, 'Gulab Jamun', 'Sweet milk solids in sugar syrup', 80.00, 30.00, TRUE),
(5, 'Rasmalai', 'Soft cheese patties in sweetened milk', 120.00, 50.00, TRUE),
(6, 'Masala Chai', 'Spiced Indian tea', 30.00, 10.00, TRUE),
(6, 'Cold Coffee', 'Iced coffee with cream', 100.00, 35.00, TRUE);

-- Insert Sample Staff
INSERT INTO staff (employee_id, full_name, email, phone, address, designation, department, join_date, salary, bank_account, bank_name, ifsc_code, emergency_contact) VALUES 
('EMP001', 'Rajesh Kumar', 'rajesh@hotel.com', '9876543211', '45 Staff Quarters, Hotel Campus', 'Front Office Manager', 'front_office', '2023-01-15', 45000.00, '12345678901', 'State Bank of India', 'SBIN0001234', '9876543212'),
('EMP002', 'Priya Sharma', 'priya@hotel.com', '9876543213', '46 Staff Quarters, Hotel Campus', 'Housekeeping Supervisor', 'housekeeping', '2023-02-01', 28000.00, '12345678902', 'HDFC Bank', 'HDFC0005678', '9876543214'),
('EMP003', 'Amit Singh', 'amit@hotel.com', '9876543215', '47 Staff Quarters, Hotel Campus', 'Head Chef', 'kitchen', '2023-01-10', 50000.00, '12345678903', 'ICICI Bank', 'ICIC0009012', '9876543216'),
('EMP004', 'Sunita Devi', 'sunita@hotel.com', '9876543217', '48 Staff Quarters, Hotel Campus', 'Receptionist', 'front_office', '2023-03-01', 22000.00, '12345678904', 'Axis Bank', 'UTIB0003456', '9876543218'),
('EMP005', 'Vikram Patel', 'vikram@hotel.com', '9876543219', '49 Staff Quarters, Hotel Campus', 'Security Guard', 'security', '2023-01-20', 18000.00, '12345678905', 'State Bank of India', 'SBIN0001234', '9876543220');