CREATE DATABASE IF NOT EXISTS event_management;
USE event_management;

-- Admins table
CREATE TABLE admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    password VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    role ENUM('super_admin', 'admin') DEFAULT 'admin',
    status TINYINT DEFAULT 1,
    last_login DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Categories table
CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    description TEXT NULL,
    image VARCHAR(255) NULL,
    icon VARCHAR(100) NULL,
    sort_order INT DEFAULT 0,
    status TINYINT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Packages table
CREATE TABLE packages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT NOT NULL,
    name VARCHAR(200) NOT NULL,
    slug VARCHAR(200) NOT NULL UNIQUE,
    short_description TEXT NULL,
    description TEXT NULL,
    price DECIMAL(12,2) NOT NULL DEFAULT 0,
    original_price DECIMAL(12,2) NULL,
    whats_included TEXT NULL,
    optional_extras TEXT NULL,
    image VARCHAR(255) NULL,
    is_featured TINYINT DEFAULT 0,
    is_popular TINYINT DEFAULT 0,
    status TINYINT DEFAULT 1,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Package images
CREATE TABLE package_images (
    id INT AUTO_INCREMENT PRIMARY KEY,
    package_id INT NOT NULL,
    image VARCHAR(255) NOT NULL,
    sort_order INT DEFAULT 0,
    FOREIGN KEY (package_id) REFERENCES packages(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Customers table
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(20) NOT NULL,
    password VARCHAR(255) NOT NULL,
    address TEXT NULL,
    district VARCHAR(100) NULL,
    profile_image VARCHAR(255) NULL,
    status TINYINT DEFAULT 1,
    email_verified TINYINT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Bookings table
CREATE TABLE bookings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    booking_id VARCHAR(20) NOT NULL UNIQUE,
    customer_id INT NOT NULL,
    package_id INT NOT NULL,
    category_id INT NOT NULL,
    event_date DATE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    venue VARCHAR(255) NOT NULL,
    address TEXT NULL,
    district VARCHAR(100) NULL,
    color_theme VARCHAR(100) NULL,
    special_notes TEXT NULL,
    inspiration_image VARCHAR(255) NULL,
    package_price DECIMAL(12,2) DEFAULT 0.00,
    transport_cost DECIMAL(12,2) DEFAULT 0.00,
    distance DECIMAL(8,2) DEFAULT 0.00,
    latitude DECIMAL(10,8) NULL,
    longitude DECIMAL(11,8) NULL,
    total_price DECIMAL(12,2) NOT NULL DEFAULT 0,
    advance_amount DECIMAL(12,2) DEFAULT 0,
    balance_amount DECIMAL(12,2) DEFAULT 0,
    payment_status ENUM('pending', 'advance_paid', 'full_paid', 'refunded') DEFAULT 'pending',
    status ENUM('pending', 'approved', 'waiting_payment', 'confirmed', 'completed', 'cancelled') DEFAULT 'pending',
    admin_notes TEXT NULL,
    assigned_team VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    FOREIGN KEY (package_id) REFERENCES packages(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Booking status history
CREATE TABLE booking_status (
    id INT AUTO_INCREMENT PRIMARY KEY,
    booking_id INT NOT NULL,
    status VARCHAR(50) NOT NULL,
    notes TEXT NULL,
    created_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Availability calendar
CREATE TABLE availability (
    id INT AUTO_INCREMENT PRIMARY KEY,
    date DATE NOT NULL,
    status ENUM('available', 'blocked', 'holiday', 'booked') DEFAULT 'available',
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_date (date)
) ENGINE=InnoDB;

-- Time slots
CREATE TABLE time_slots (
    id INT AUTO_INCREMENT PRIMARY KEY,
    availability_id INT NULL,
    slot_name VARCHAR(50) NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    is_custom TINYINT DEFAULT 0,
    status ENUM('available', 'booked', 'blocked') DEFAULT 'available',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (availability_id) REFERENCES availability(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Payments table
CREATE TABLE payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    booking_id INT NOT NULL,
    customer_id INT NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    payment_type ENUM('advance', 'full', 'remaining') DEFAULT 'advance',
    payment_method VARCHAR(100) NULL,
    transaction_id VARCHAR(100) NULL,
    receipt_image VARCHAR(255) NULL,
    notes TEXT NULL,
    status ENUM('pending', 'verified', 'rejected') DEFAULT 'pending',
    verified_by INT NULL,
    verified_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Rental categories
CREATE TABLE rental_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    description TEXT NULL,
    image VARCHAR(255) NULL,
    sort_order INT DEFAULT 0,
    status TINYINT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Rental items
CREATE TABLE rental_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT NOT NULL,
    name VARCHAR(200) NOT NULL,
    slug VARCHAR(200) NOT NULL UNIQUE,
    description TEXT NULL,
    price DECIMAL(12,2) NOT NULL DEFAULT 0,
    image VARCHAR(255) NULL,
    quantity INT DEFAULT 1,
    available_quantity INT DEFAULT 1,
    features TEXT NULL,
    status TINYINT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES rental_categories(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Rental inquiries
CREATE TABLE rental_inquiries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NULL,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    items TEXT NOT NULL,
    event_date DATE NULL,
    notes TEXT NULL,
    status ENUM('pending', 'contacted', 'closed') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Gallery
CREATE TABLE gallery (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT NULL,
    title VARCHAR(200) NULL,
    image VARCHAR(255) NOT NULL,
    description TEXT NULL,
    is_featured TINYINT DEFAULT 0,
    sort_order INT DEFAULT 0,
    status TINYINT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Contact messages
CREATE TABLE contact_messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NULL,
    subject VARCHAR(200) NULL,
    message TEXT NOT NULL,
    is_read TINYINT DEFAULT 0,
    replied TINYINT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Notifications
CREATE TABLE notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    user_type ENUM('admin', 'customer') DEFAULT 'admin',
    title VARCHAR(200) NOT NULL,
    message TEXT NOT NULL,
    link VARCHAR(255) NULL,
    is_read TINYINT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Settings
CREATE TABLE settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Social links
CREATE TABLE social_links (
    id INT AUTO_INCREMENT PRIMARY KEY,
    platform VARCHAR(50) NOT NULL,
    url VARCHAR(255) NOT NULL,
    icon VARCHAR(100) NULL,
    sort_order INT DEFAULT 0,
    status TINYINT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Testimonials
CREATE TABLE testimonials (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    customer_image VARCHAR(255) NULL,
    rating INT DEFAULT 5,
    content TEXT NOT NULL,
    event_type VARCHAR(100) NULL,
    status TINYINT DEFAULT 1,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Invoices
CREATE TABLE invoices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_no VARCHAR(30) NOT NULL UNIQUE,
    booking_id INT NOT NULL,
    customer_id INT NOT NULL,
    total_amount DECIMAL(12,2) NOT NULL,
    advance_paid DECIMAL(12,2) DEFAULT 0,
    balance DECIMAL(12,2) DEFAULT 0,
    payment_status VARCHAR(50) DEFAULT 'pending',
    invoice_date DATE NOT NULL,
    due_date DATE NULL,
    notes TEXT NULL,
    status ENUM('active', 'paid', 'cancelled') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Activity logs
CREATE TABLE activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    user_type VARCHAR(50) NULL,
    action VARCHAR(100) NOT NULL,
    description TEXT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Insert default admin
INSERT INTO admins (username, email, password, full_name, role) VALUES
('admin', 'admin@eventdecor.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Super Admin', 'super_admin');

-- Insert default settings
INSERT INTO settings (setting_key, setting_value) VALUES
('site_name', 'DreamCraft Events'),
('site_logo', ''),
('site_description', 'Creating unforgettable celebrations with premium event decorations'),
('contact_email', 'info@dreamcraftevents.com'),
('contact_phone', '+94 77 123 4567'),
('whatsapp_number', '+94771234567'),
('address', '123 Event Street, Colombo, Sri Lanka'),
('business_hours', 'Mon-Sat: 9:00 AM - 6:00 PM'),
('facebook_url', '#'),
('instagram_url', '#'),
('tiktok_url', '#'),
('bank_name', 'Sample Bank'),
('bank_account_name', 'DreamCraft Events'),
('bank_account_no', '1234567890'),
('bank_branch', 'Colombo Main Branch'),
('advance_percentage', '50'),
('currency', 'LKR'),
('office_latitude', '6.9271'),
('office_longitude', '79.8612'),
('transport_cost_per_km', '100.00'),
('footer_text', '© 2026 DreamCraft Events. All rights reserved.');

-- Insert sample categories
INSERT INTO categories (name, slug, description, icon, sort_order) VALUES
('Birthday', 'birthday', 'Make your birthday unforgettable with our premium decorations', 'bi-gift', 1),
('Wedding', 'wedding', 'Elegant wedding decorations for your special day', 'bi-heart', 2),
('Anniversary', 'anniversary', 'Celebrate your love story with beautiful decorations', 'bi-balloon-heart', 3),
('Engagement', 'engagement', 'Start your journey with a beautifully decorated engagement', 'bi-gem', 4),
('Baby Shower', 'baby-shower', 'Welcome your little one with adorable decorations', 'bi-baby', 5),
('Gender Reveal', 'gender-reveal', 'Reveal the big surprise in style', 'bi-question-circle', 6),
('Baptism', 'baptism', 'Beautiful decorations for this sacred occasion', 'bi-droplet', 7),
('Homecoming', 'homecoming', 'Welcome home with stunning decorations', 'bi-house-heart', 8),
('Corporate', 'corporate', 'Professional decorations for corporate events', 'di-building', 9),
('Other', 'other', 'Custom decorations for any special event', 'bi-stars', 10);

-- Insert sample packages
INSERT INTO packages (category_id, name, slug, short_description, description, price, original_price, whats_included, is_featured, is_popular, sort_order) VALUES
(1, 'Silver Backdrop', 'silver-backdrop', 'Classic birthday setup with elegant silver backdrop', 'Complete birthday decoration package featuring a stunning silver balloon backdrop, happy birthday neon sign, and elegant table setup.', 45000, 55000, '7x7 Balloon Backdrop\nHappy Birthday Neon Sign\n3 Cake Pedestals\nTable Cloth\n50 Balloons', 1, 0, 1),
(1, 'Gold Luxury', 'gold-luxury', 'Premium gold-themed birthday decoration', 'Luxurious gold themed birthday decoration with organic balloon arch, personalized acrylic board, and premium table decorations.', 75000, 85000, 'Organic Balloon Arch\nPersonalized Acrylic Board\nWelcome Board\nPremium Table Decorations\nGold Centerpieces\n100 Balloons', 1, 1, 2),
(1, 'Platinum Dream', 'platinum-dream', 'Ultimate birthday decoration experience', 'The ultimate birthday experience with full venue decoration, custom backdrop, floral arrangements, and premium lighting.', 120000, 140000, 'Custom Themed Backdrop\nFloral Arrangements\nPremium Lighting\nPhoto Booth Setup\nWelcome Board\nCustomized Decor\n200+ Balloons\nFull Venue Decoration', 1, 0, 3);

-- Insert rental categories
INSERT INTO rental_categories (name, slug, description, sort_order) VALUES
('Backdrops', 'backdrops', 'Elegant backdrops for all occasions', 1),
('Flower Stands', 'flower-stands', 'Beautiful flower stand arrangements', 2),
('Cake Stands', 'cake-stands', 'Elegant cake display stands', 3),
('LED Lights', 'led-lights', 'Premium LED lighting solutions', 4),
('Artificial Flowers', 'artificial-flowers', 'High quality artificial flower arrangements', 5),
('Tables', 'tables', 'Event tables for all occasions', 6),
('Chairs', 'chairs', 'Comfortable seating options', 7),
('Accessories', 'accessories', 'Event accessories and decor items', 8);

-- Insert sample testimonials
INSERT INTO testimonials (customer_name, rating, content, event_type) VALUES
('Sarah Johnson', 5, 'Absolutely amazing! DreamCraft made my birthday party look like a fairy tale. The attention to detail was incredible.', 'Birthday'),
('Michael & Priya', 5, 'Our wedding was perfect thanks to DreamCraft Events. The gold luxury theme was beyond our expectations.', 'Wedding'),
('Amanda Perera', 5, 'The baby shower decorations were adorable! Everyone loved the setup. Highly recommend!', 'Baby Shower'),
('David Chen', 4, 'Professional corporate event setup. Very impressed with their punctuality and quality of work.', 'Corporate');