<?php
// Database configuration
$host = "localhost";
$username = "root";
$password = "";
$database = "shopnest_db";

// Create connection
$conn = mysqli_connect($host, $username, $password);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

echo "<h2>ShopNest Database Setup</h2>";
echo "<div style='background: #f8f9fa; padding: 20px; border-radius: 10px;'>";

// Create database if not exists
$sql = "CREATE DATABASE IF NOT EXISTS $database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci";
if (mysqli_query($conn, $sql)) {
    echo "✓ Database created successfully<br>";
} else {
    echo "✗ Error creating database: " . mysqli_error($conn) . "<br>";
}

// Select database
mysqli_select_db($conn, $database);

// ============================================
// 1. USERS TABLE
// ============================================
$sql = "CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(20),
    address TEXT,
    password VARCHAR(255) NOT NULL,
    avatar VARCHAR(255),
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    email_verified BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

if (mysqli_query($conn, $sql)) {
    echo "✓ Users table created successfully<br>";
} else {
    echo "✗ Error creating users table: " . mysqli_error($conn) . "<br>";
}

// ============================================
// 2. ADMIN TABLE
// ============================================
$sql = "CREATE TABLE IF NOT EXISTS admin (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100),
    full_name VARCHAR(100),
    role ENUM('super_admin', 'admin', 'moderator') DEFAULT 'admin',
    permissions TEXT,
    last_login TIMESTAMP NULL,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

if (mysqli_query($conn, $sql)) {
    echo "✓ Admin table created successfully<br>";
    
    // Insert default admin if not exists (password: admin123)
    $check_admin = mysqli_query($conn, "SELECT * FROM admin WHERE username='admin'");
    if (mysqli_num_rows($check_admin) == 0) {
        $hashed_password = password_hash('admin123', PASSWORD_DEFAULT);
        mysqli_query($conn, "INSERT INTO admin (username, password, email, full_name, role) VALUES ('admin', '$hashed_password', 'admin@shopnest.com', 'Super Admin', 'super_admin')");
        echo "✓ Default admin created (username: admin, password: admin123)<br>";
    }
} else {
    echo "✗ Error creating admin table: " . mysqli_error($conn) . "<br>";
}

// ============================================
// 3. CATEGORIES TABLE
// ============================================
$sql = "CREATE TABLE IF NOT EXISTS categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_name VARCHAR(100) NOT NULL,
    slug VARCHAR(120) UNIQUE NOT NULL,
    description TEXT,
    parent_id INT DEFAULT 0,
    images VARCHAR(255),
    display_order INT DEFAULT 0,
    status ENUM('active', 'inactive') DEFAULT 'active',
    meta_title VARCHAR(200),
    meta_description TEXT,
    meta_keywords TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_parent (parent_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

if (mysqli_query($conn, $sql)) {
    echo "✓ Categories table created successfully<br>";
    
    // Insert sample categories if empty
    $check_categories = mysqli_query($conn, "SELECT * FROM categories");
    if (mysqli_num_rows($check_categories) == 0) {
        $categories = [
            "('Traditional Wear', 'traditional-wear', 'Ethnic and traditional clothing for all occasions', 0, NULL, 1, 'active', 'Traditional Wear', 'Rent traditional clothes online', 'traditional, ethnic, clothing')",
            "('Western Wear', 'western-wear', 'Casual and formal western clothing', 0, NULL, 2, 'active', 'Western Wear', 'Rent western clothes online', 'western, casual, formal')",
            "('Party Wear', 'party-wear', 'Elegant dresses for parties and events', 0, NULL, 3, 'active', 'Party Wear', 'Rent party dresses online', 'party, dress, elegant')",
            "('Wedding Wear', 'wedding-wear', 'Bridal and wedding guest outfits', 0, NULL, 4, 'active', 'Wedding Wear', 'Rent wedding outfits online', 'wedding, bridal, groom')",
            "('Casual Wear', 'casual-wear', 'Everyday comfortable clothing', 0, NULL, 5, 'active', 'Casual Wear', 'Rent casual clothes online', 'casual, comfortable, daily')",
            "('Designer Wear', 'designer-wear', 'Premium designer clothing collections', 0, NULL, 6, 'active', 'Designer Wear', 'Rent designer clothes online', 'designer, premium, luxury')",
            "('Accessories', 'accessories', 'Jewelry, bags, and fashion accessories', 0, NULL, 7, 'active', 'Accessories', 'Rent fashion accessories online', 'accessories, jewelry, bags')",
            "('Kids Wear', 'kids-wear', 'Clothing for children', 0, NULL, 8, 'active', 'Kids Wear', 'Rent kids clothes online', 'kids, children, clothing')"
        ];
        
        foreach ($categories as $category) {
            mysqli_query($conn, "INSERT INTO categories (category_name, slug, description, parent_id, images, display_order, status, meta_title, meta_description, meta_keywords) VALUES $category");
        }
        echo "✓ Sample categories inserted<br>";
    }
} else {
    echo "✗ Error creating categories table: " . mysqli_error($conn) . "<br>";
}

// ============================================
// 4. PRODUCTS TABLE (Dynamic Rental Products)
// ============================================
$sql = "CREATE TABLE IF NOT EXISTS products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    slug VARCHAR(220) UNIQUE NOT NULL,
    description TEXT,
    short_description TEXT,
    original_price DECIMAL(10,2) NOT NULL,
    discount_percent DECIMAL(5,2) DEFAULT 0,
    price DECIMAL(10,2) NOT NULL,
    category_id INT,
    subcategory_id INT DEFAULT 0,
    brand VARCHAR(100),
    size VARCHAR(50),
    color VARCHAR(50),
    material VARCHAR(100),
    weight DECIMAL(5,2),
    dimensions VARCHAR(50),
    manufacturer VARCHAR(100),
    rental_days INT DEFAULT 3,
    security_deposit DECIMAL(10,2) DEFAULT 0,
    images VARCHAR(255) NOT NULL,
    gallery_images TEXT,
    stock INT DEFAULT 1,
    min_stock INT DEFAULT 1,
    sku VARCHAR(100) UNIQUE,
    barcode VARCHAR(100),
    featured BOOLEAN DEFAULT FALSE,
    trending BOOLEAN DEFAULT FALSE,
    new_arrival BOOLEAN DEFAULT FALSE,
    status ENUM('active', 'inactive', 'out_of_stock') DEFAULT 'active',
    condition ENUM('new', 'like_new', 'good', 'fair') DEFAULT 'good',
    cleaning_fee DECIMAL(10,2) DEFAULT 0,
    delivery_time VARCHAR(50),
    pickup_time VARCHAR(50),
    tags TEXT,
    meta_title VARCHAR(200),
    meta_description TEXT,
    meta_keywords TEXT,
    views INT DEFAULT 0,
    rating DECIMAL(3,2) DEFAULT 0,
    total_ratings INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_category (category_id),
    INDEX idx_status (status),
    INDEX idx_featured (featured),
    INDEX idx_price (price),
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

if (mysqli_query($conn, $sql)) {
    echo "✓ Products table created successfully<br>";
    
    // Generate sample products if empty
    $check_products = mysqli_query($conn, "SELECT * FROM products");
    if (mysqli_num_rows($check_products) == 0) {
        $products = [
            "('Red Silk Saree', 'red-silk-saree', 'Beautiful traditional red silk saree with zari work', 'Elegant red silk saree perfect for weddings', 5000.00, 40, 3000.00, 1, 0, 'Sabyasachi', 'Free Size', 'Red', 'Silk', 0.5, '5.5x1.5x0.1', 'Sabyasachi', 7, 2000.00, 'saree1.jpg', '[\"saree2.jpg\",\"saree3.jpg\"]', 5, 1, 'SKU001', '123456789', 1, 1, 1, 'active', 'like_new', 200.00, '2-3 days', '2-3 days', 'saree,silk,traditional', 'Red Silk Saree', 'Rent beautiful red silk saree online', 'saree, silk, traditional', 0, 4.5, 10)",
            "('Designer Lehenga', 'designer-lehenga', 'Heavy embroidered lehenga choli for weddings', 'Premium designer lehenga for special occasions', 8000.00, 37.5, 5000.00, 1, 0, 'Manish Malhotra', 'M', 'Gold', 'Silk', 2.5, '20x15x5', 'Manish Malhotra', 7, 3000.00, 'lehenga1.jpg', '[\"lehenga2.jpg\",\"lehenga3.jpg\"]', 3, 1, 'SKU002', '123456790', 1, 1, 1, 'active', 'new', 300.00, '3-4 days', '3-4 days', 'lehenga,designer,wedding', 'Designer Lehenga', 'Rent designer lehenga online', 'lehenga, designer, wedding', 0, 4.8, 15)",
            "('Evening Gown', 'evening-gown', 'Elegant black evening gown for parties', 'Stunning black evening gown for formal events', 6000.00, 33.33, 4000.00, 3, 0, 'Zara', 'L', 'Black', 'Velvet', 1.2, '30x20x3', 'Zara', 5, 2500.00, 'gown1.jpg', '[\"gown2.jpg\",\"gown3.jpg\"]', 4, 1, 'SKU003', '123456791', 0, 1, 0, 'active', 'good', 250.00, '2-3 days', '2-3 days', 'gown,evening,party', 'Evening Gown', 'Rent evening gown online', 'gown, evening, party', 0, 4.2, 8)",
            "('Sherwani Set', 'sherwani-set', 'Traditional sherwani for grooms', 'Elegant sherwani set for wedding ceremonies', 10000.00, 40, 6000.00, 4, 0, 'Manyavar', 'XL', 'Cream', 'Silk', 3.0, '25x20x6', 'Manyavar', 7, 4000.00, 'sherwani1.jpg', '[\"sherwani2.jpg\",\"sherwani3.jpg\"]', 2, 1, 'SKU004', '123456792', 1, 0, 1, 'active', 'like_new', 400.00, '3-5 days', '3-5 days', 'sherwani,groom,wedding', 'Sherwani Set', 'Rent sherwani online', 'sherwani, groom, wedding', 0, 4.7, 12)",
            "('Cocktail Dress', 'cocktail-dress', 'Beautiful cocktail dress for parties', 'Chic cocktail dress for semi-formal events', 4500.00, 33.33, 3000.00, 3, 0, 'H&M', 'S', 'Navy Blue', 'Chiffon', 0.8, '25x18x2', 'H&M', 4, 2000.00, '', '[\"dress2.jpg\",\"dress3.jpg\"]', 6, 1, 'SKU005', '123456793', 0, 1, 1, 'active', 'new', 150.00, '1-2 days', '1-2 days', 'dress,cocktail,party', 'Cocktail Dress', 'Rent cocktail dress online', 'dress, cocktail, party', 0, 4.3, 7)"
        ];
        
        foreach ($products as $product) {
            mysqli_query($conn, "INSERT INTO products (name, slug, description, short_description, original_price, discount_percent, price, category_id, subcategory_id, brand, size, color, material, weight, dimensions, manufacturer, rental_days, security_deposit, images, gallery_images, stock, min_stock, sku, barcode, featured, trending, new_arrival, status, condition, cleaning_fee, delivery_time, pickup_time, tags, meta_title, meta_description, meta_keywords, views, rating, total_ratings) VALUES $product");
        }
        echo "✓ Sample products inserted<br>";
    }
} else {
    echo "✗ Error creating products table: " . mysqli_error($conn) . "<br>";
}

// ============================================
// 5. ORDERS TABLE
// ============================================
$sql = "CREATE TABLE IF NOT EXISTS orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_number VARCHAR(50) UNIQUE NOT NULL,
    user_id INT NOT NULL,
    total_price DECIMAL(10,2) NOT NULL,
    subtotal DECIMAL(10,2) NOT NULL,
    tax_amount DECIMAL(10,2) DEFAULT 0,
    delivery_charge DECIMAL(10,2) DEFAULT 0,
    cleaning_fee DECIMAL(10,2) DEFAULT 0,
    security_deposit DECIMAL(10,2) DEFAULT 0,
    discount_amount DECIMAL(10,2) DEFAULT 0,
    coupon_code VARCHAR(50),
    payment_method ENUM('cod', 'razorpay', 'paypal', 'stripe', 'bank_transfer') DEFAULT 'cod',
    payment_status ENUM('pending', 'paid', 'failed', 'refunded') DEFAULT 'pending',
    payment_id VARCHAR(100),
    transaction_id VARCHAR(100),
    status ENUM('pending', 'confirmed', 'processing', 'shipped', 'delivered', 'cancelled', 'returned', 'completed') DEFAULT 'pending',
    shipping_address TEXT NOT NULL,
    billing_address TEXT,
    customer_notes TEXT,
    admin_notes TEXT,
    rental_start_date DATE,
    rental_end_date DATE,
    rental_days INT DEFAULT 3,
    delivery_date DATE,
    pickup_date DATE,
    tracking_number VARCHAR(100),
    shipping_carrier VARCHAR(100),
    estimated_delivery DATE,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_status (status),
    INDEX idx_order_number (order_number),
    INDEX idx_created (created_at),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

if (mysqli_query($conn, $sql)) {
    echo "✓ Orders table created successfully<br>";
} else {
    echo "✗ Error creating orders table: " . mysqli_error($conn) . "<br>";
}

// ============================================
// 6. ORDER ITEMS TABLE
// ============================================
$sql = "CREATE TABLE IF NOT EXISTS order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    product_name VARCHAR(200) NOT NULL,
    product_image VARCHAR(255),
    original_price DECIMAL(10,2) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    total_price DECIMAL(10,2) NOT NULL,
    rental_days INT DEFAULT 3,
    security_deposit DECIMAL(10,2) DEFAULT 0,
    cleaning_fee DECIMAL(10,2) DEFAULT 0,
    size VARCHAR(50),
    color VARCHAR(50),
    status ENUM('pending', 'shipped', 'delivered', 'returned', 'cancelled') DEFAULT 'pending',
    return_date DATE,
    return_condition ENUM('excellent', 'good', 'damaged', 'lost') DEFAULT NULL,
    return_notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL,
    INDEX idx_order (order_id),
    INDEX idx_product (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

if (mysqli_query($conn, $sql)) {
    echo "✓ Order items table created successfully<br>";
} else {
    echo "✗ Error creating order items table: " . mysqli_error($conn) . "<br>";
}

// ============================================
// 7. ORDER HISTORY TABLE
// ============================================
$sql = "CREATE TABLE IF NOT EXISTS order_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    status VARCHAR(50) NOT NULL,
    notes TEXT,
    updated_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    INDEX idx_order (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

if (mysqli_query($conn, $sql)) {
    echo "✓ Order history table created successfully<br>";
} else {
    echo "✗ Error creating order history table: " . mysqli_error($conn) . "<br>";
}

// ============================================
// 8. REVIEWS & RATINGS TABLE
// ============================================
$sql = "CREATE TABLE IF NOT EXISTS reviews (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    user_id INT NOT NULL,
    order_id INT,
    rating INT NOT NULL CHECK (rating >= 1 AND rating <= 5),
    title VARCHAR(200),
    comment TEXT,
    images TEXT,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    helpful_count INT DEFAULT 0,
    not_helpful_count INT DEFAULT 0,
    admin_reply TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_product (product_id),
    INDEX idx_user (user_id),
    INDEX idx_status (status),
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

if (mysqli_query($conn, $sql)) {
    echo "✓ Reviews table created successfully<br>";
} else {
    echo "✗ Error creating reviews table: " . mysqli_error($conn) . "<br>";
}

// ============================================
// 9. WISHLIST TABLE
// ============================================
$sql = "CREATE TABLE IF NOT EXISTS wishlist (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    product_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_wishlist (user_id, product_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

if (mysqli_query($conn, $sql)) {
    echo "✓ Wishlist table created successfully<br>";
} else {
    echo "✗ Error creating wishlist table: " . mysqli_error($conn) . "<br>";
}

// ============================================
// 10. CART TABLE
// ============================================
$sql = "CREATE TABLE IF NOT EXISTS cart (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    rental_days INT DEFAULT 3,
    rental_start_date DATE,
    rental_end_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_cart (user_id, product_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

if (mysqli_query($conn, $sql)) {
    echo "✓ Cart table created successfully<br>";
} else {
    echo "✗ Error creating cart table: " . mysqli_error($conn) . "<br>";
}

// ============================================
// 11. COUPONS TABLE
// ============================================
$sql = "CREATE TABLE IF NOT EXISTS coupons (
    id INT PRIMARY KEY AUTO_INCREMENT,
    code VARCHAR(50) UNIQUE NOT NULL,
    type ENUM('percentage', 'fixed', 'free_shipping') DEFAULT 'percentage',
    value DECIMAL(10,2) NOT NULL,
    min_order_amount DECIMAL(10,2) DEFAULT 0,
    max_discount_amount DECIMAL(10,2),
    usage_limit INT DEFAULT NULL,
    usage_count INT DEFAULT 0,
    per_user_limit INT DEFAULT 1,
    start_date DATE,
    end_date DATE,
    status ENUM('active', 'inactive', 'expired') DEFAULT 'active',
    applicable_categories TEXT,
    excluded_categories TEXT,
    applicable_products TEXT,
    excluded_products TEXT,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_code (code),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

if (mysqli_query($conn, $sql)) {
    echo "✓ Coupons table created successfully<br>";
} else {
    echo "✗ Error creating coupons table: " . mysqli_error($conn) . "<br>";
}

// ============================================
// 12. SETTINGS TABLE
// ============================================
$sql = "CREATE TABLE IF NOT EXISTS settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    setting_group VARCHAR(50) DEFAULT 'general',
    setting_type ENUM('text', 'textarea', 'number', 'email', 'url', 'select', 'checkbox', 'radio', 'color', 'file') DEFAULT 'text',
    options TEXT,
    label VARCHAR(200),
    description TEXT,
    display_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_group (setting_group),
    INDEX idx_key (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

if (mysqli_query($conn, $sql)) {
    echo "✓ Settings table created successfully<br>";
    
    // Insert default settings
    $default_settings = [
        // General Settings
        "('site_name', 'shopnest', 'general', 'text', NULL, 'Site Name', 'Name of your website', 1)",
        "('site_email', 'admin@shopnest.com', 'general', 'email', NULL, 'Site Email', 'Default email for notifications', 2)",
        "('site_phone', '+91 98765 43210', 'general', 'text', NULL, 'Site Phone', 'Contact phone number', 3)",
        "('site_address', '123 Fashion Street, Style City', 'general', 'textarea', NULL, 'Site Address', 'Physical address of your business', 4)",
        "('site_description', 'Premium clothing rental service', 'general', 'textarea', NULL, 'Site Description', 'Brief description of your website', 5)",
        "('site_logo', 'logo.png', 'general', 'file', NULL, 'Site Logo', 'Website logo image', 6)",
        "('site_favicon', 'favicon.ico', 'general', 'file', NULL, 'Site Favicon', 'Browser favicon', 7)",
        "('currency', '₹', 'general', 'text', NULL, 'Currency', 'Currency symbol', 8)",
        "('currency_code', 'INR', 'general', 'text', NULL, 'Currency Code', 'Currency code (INR, USD, etc.)', 9)",
        "('timezone', 'Asia/Kolkata', 'general', 'select', '[\"Asia/Kolkata\",\"UTC\",\"America/New_York\",\"Europe/London\"]', 'Timezone', 'Website timezone', 10)",
        
        // Payment Settings
        "('razorpay_key_id', '', 'payment', 'text', NULL, 'Razorpay Key ID', 'Razorpay API Key ID', 1)",
        "('razorpay_key_secret', '', 'payment', 'text', NULL, 'Razorpay Key Secret', 'Razorpay API Key Secret', 2)",
        "('paypal_client_id', '', 'payment', 'text', NULL, 'PayPal Client ID', 'PayPal API Client ID', 3)",
        "('paypal_secret', '', 'payment', 'text', NULL, 'PayPal Secret', 'PayPal API Secret', 4)",
        "('stripe_publishable_key', '', 'payment', 'text', NULL, 'Stripe Publishable Key', 'Stripe Publishable Key', 5)",
        "('stripe_secret_key', '', 'payment', 'text', NULL, 'Stripe Secret Key', 'Stripe Secret Key', 6)",
        "('cod_enabled', '1', 'payment', 'checkbox', NULL, 'Cash on Delivery', 'Enable Cash on Delivery', 7)",
        "('razorpay_enabled', '1', 'payment', 'checkbox', NULL, 'Razorpay', 'Enable Razorpay payments', 8)",
        
        // Shipping Settings
        "('delivery_charge', '99', 'shipping', 'number', NULL, 'Delivery Charge', 'Standard delivery charge', 1)",
        "('free_delivery_minimum', '999', 'shipping', 'number', NULL, 'Free Delivery Minimum', 'Minimum order amount for free delivery', 2)",
        "('processing_time', '1', 'shipping', 'number', NULL, 'Processing Time (Days)', 'Order processing time in days', 3)",
        "('delivery_time_min', '2', 'shipping', 'number', NULL, 'Min Delivery Time (Days)', 'Minimum delivery time in days', 4)",
        "('delivery_time_max', '4', 'shipping', 'number', NULL, 'Max Delivery Time (Days)', 'Maximum delivery time in days', 5)",
        "('pickup_enabled', '1', 'shipping', 'checkbox', NULL, 'Pickup Service', 'Enable pickup service', 6)",
        
        // Tax Settings
        "('tax_percentage', '18', 'tax', 'number', NULL, 'Tax Percentage (%)', 'GST/TAX percentage', 1)",
        "('tax_name', 'GST', 'tax', 'text', NULL, 'Tax Name', 'Name of tax (GST, VAT, etc.)', 2)",
        "('tax_inclusive', '0', 'tax', 'checkbox', NULL, 'Tax Inclusive Pricing', 'Include tax in product prices', 3)",
        
        // Rental Settings
        "('min_rental_days', '3', 'rental', 'number', NULL, 'Minimum Rental Days', 'Minimum rental period', 1)",
        "('max_rental_days', '30', 'rental', 'number', NULL, 'Maximum Rental Days', 'Maximum rental period', 2)",
        "('late_return_fee', '500', 'rental', 'number', NULL, 'Late Return Fee (Per Day)', 'Fee for late returns per day', 3)",
        "('damage_fee_percentage', '50', 'rental', 'number', NULL, 'Damage Fee Percentage', 'Percentage of security deposit charged for damage', 4)",
        "('cleaning_fee_default', '200', 'rental', 'number', NULL, 'Default Cleaning Fee', 'Default cleaning fee for products', 5)",
        
        // Email Settings
        "('smtp_host', 'smtp.gmail.com', 'email', 'text', NULL, 'SMTP Host', 'SMTP server host', 1)",
        "('smtp_port', '587', 'email', 'text', NULL, 'SMTP Port', 'SMTP server port', 2)",
        "('smtp_username', '', 'email', 'email', NULL, 'SMTP Username', 'SMTP username/email', 3)",
        "('smtp_password', '', 'email', 'text', NULL, 'SMTP Password', 'SMTP password', 4)",
        "('smtp_encryption', 'tls', 'email', 'select', '[\"tls\",\"ssl\",\"none\"]', 'SMTP Encryption', 'SMTP encryption method', 5)",
        "('email_from_name', 'shopnest', 'email', 'text', NULL, 'From Name', 'Sender name for emails', 6)",
        "('email_from_address', 'noreply@shopnest.com', 'email', 'email', NULL, 'From Address', 'Sender email address', 7)",
        
        // Social Media
        "('facebook_url', 'https://facebook.com/shopnest', 'social', 'url', NULL, 'Facebook URL', 'Facebook page URL', 1)",
        "('instagram_url', 'https://instagram.com/shopnest', 'social', 'url', NULL, 'Instagram URL', 'Instagram profile URL', 2)",
        "('twitter_url', 'https://twitter.com/shopnest', 'social', 'url', NULL, 'Twitter URL', 'Twitter profile URL', 3)",
        "('pinterest_url', 'https://pinterest.com/shopnest', 'social', 'url', NULL, 'Pinterest URL', 'Pinterest profile URL', 4)",
        "('youtube_url', '', 'social', 'url', NULL, 'YouTube URL', 'YouTube channel URL', 5)",
        
        // SEO Settings
        "('meta_title', 'shopnest - Rent Designer Clothes Online', 'seo', 'text', NULL, 'Meta Title', 'Default meta title', 1)",
        "('meta_description', 'Rent premium designer clothes for every occasion. Save up to 90% compared to buying.', 'seo', 'textarea', NULL, 'Meta Description', 'Default meta description', 2)",
        "('meta_keywords', 'rent clothes, designer wear, traditional wear, party wear, wedding wear', 'seo', 'textarea', NULL, 'Meta Keywords', 'Default meta keywords', 3)",
        "('google_analytics', '', 'seo', 'textarea', NULL, 'Google Analytics Code', 'Google Analytics tracking code', 4)",
        "('facebook_pixel', '', 'seo', 'textarea', NULL, 'Facebook Pixel', 'Facebook Pixel code', 5)"
    ];
    
    foreach ($default_settings as $setting) {
        $key = explode(",", $setting)[0];
        $key = str_replace(["('", "'"], "", $key);
        
        $check = mysqli_query($conn, "SELECT * FROM settings WHERE setting_key = '$key'");
        if (mysqli_num_rows($check) == 0) {
            mysqli_query($conn, "INSERT INTO settings (setting_key, setting_value, setting_group, setting_type, options, label, description, display_order) VALUES $setting");
        }
    }
    echo "✓ Default settings inserted<br>";
} else {
    echo "✗ Error creating settings table: " . mysqli_error($conn) . "<br>";
}

// ============================================
// 13. PAGES TABLE
// ============================================
$sql = "CREATE TABLE IF NOT EXISTS pages (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    slug VARCHAR(220) UNIQUE NOT NULL,
    content LONGTEXT,
    excerpt TEXT,
    featured_image VARCHAR(255),
    template VARCHAR(50) DEFAULT 'default',
    parent_id INT DEFAULT 0,
    menu_order INT DEFAULT 0,
    status ENUM('published', 'draft', 'private') DEFAULT 'published',
    visibility ENUM('public', 'private', 'password') DEFAULT 'public',
    password VARCHAR(100),
    meta_title VARCHAR(200),
    meta_description TEXT,
    meta_keywords TEXT,
    author_id INT,
    published_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_slug (slug),
    INDEX idx_status (status),
    INDEX idx_parent (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

if (mysqli_query($conn, $sql)) {
    echo "✓ Pages table created successfully<br>";
    
    // Insert default pages
    $default_pages = [
        "('About Us', 'about-us', '<h2>About ShopNest</h2><p>ShopNest is a premium clothing rental service that allows you to wear designer outfits without the cost of buying them.</p>', 'Learn about shopnest', NULL, 'default', 0, 1, 'published', 'public', NULL, 'About Us - shopnest', 'Learn about shopnest clothing rental service', 'about, shopnest, clothing rental', 1, NOW())",
        "('Contact Us', 'contact-us', '<h2>Contact Us</h2><p>Get in touch with our team for any queries or support.</p>', 'Contact shopnest team', NULL, 'contact', 0, 2, 'published', 'public', NULL, 'Contact Us - shopnest', 'Contact shopnest clothing rental service', 'contact, shopnest, support', 1, NOW())",
        "('Privacy Policy', 'privacy-policy', '<h2>Privacy Policy</h2><p>Our privacy policy outlines how we handle your data.</p>', 'Privacy policy information', NULL, 'default', 0, 3, 'published', 'public', NULL, 'Privacy Policy - shopnest', 'Privacy policy for shopnest', 'privacy, policy, shopnest', 1, NOW())",
        "('Terms & Conditions', 'terms-conditions', '<h2>Terms & Conditions</h2><p>Terms and conditions for using our service.</p>', 'Terms and conditions', NULL, 'default', 0, 4, 'published', 'public', NULL, 'Terms & Conditions - shopnest', 'Terms and conditions for shopnest', 'terms, conditions, shopnest', 1, NOW())",
        "('FAQ', 'faq', '<h2>Frequently Asked Questions</h2><p>Common questions about our rental service.</p>', 'Frequently asked questions', NULL, 'faq', 0, 5, 'published', 'public', NULL, 'FAQ - shopnest', 'Frequently asked questions about shopnest', 'faq, questions, help', 1, NOW())"
    ];
    
    foreach ($default_pages as $page) {
        $slug = explode(",", $page)[1];
        $slug = str_replace(["'", " "], "", $slug);
        
        $check = mysqli_query($conn, "SELECT * FROM pages WHERE slug = '$slug'");
        if (mysqli_num_rows($check) == 0) {
            mysqli_query($conn, "INSERT INTO pages (title, slug, content, excerpt, featured_image, template, parent_id, menu_order, status, visibility, password, meta_title, meta_description, meta_keywords, author_id, published_at) VALUES $page");
        }
    }
    echo "✓ Default pages inserted<br>";
} else {
    echo "✗ Error creating pages table: " . mysqli_error($conn) . "<br>";
}

// ============================================
// 14. MENUS TABLE
// ============================================
$sql = "CREATE TABLE IF NOT EXISTS menus (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    location VARCHAR(50),
    description TEXT,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_slug (slug),
    INDEX idx_location (location)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

if (mysqli_query($conn, $sql)) {
    echo "✓ Menus table created successfully<br>";
} else {
    echo "✗ Error creating menus table: " . mysqli_error($conn) . "<br>";
}

// ============================================
// 15. MENU ITEMS TABLE
// ============================================
$sql = "CREATE TABLE IF NOT EXISTS menu_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    menu_id INT NOT NULL,
    parent_id INT DEFAULT 0,
    title VARCHAR(100) NOT NULL,
    url VARCHAR(255) NOT NULL,
    target ENUM('_self', '_blank') DEFAULT '_self',
    icon_class VARCHAR(50),
    css_class VARCHAR(100),
    menu_order INT DEFAULT 0,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (menu_id) REFERENCES menus(id) ON DELETE CASCADE,
    INDEX idx_menu (menu_id),
    INDEX idx_parent (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

if (mysqli_query($conn, $sql)) {
    echo "✓ Menu items table created successfully<br>";
} else {
    echo "✗ Error creating menu items table: " . mysqli_error($conn) . "<br>";
}

// ============================================
// 16. BLOG POSTS TABLE
// ============================================
$sql = "CREATE TABLE IF NOT EXISTS blog_posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    slug VARCHAR(220) UNIQUE NOT NULL,
    content LONGTEXT,
    excerpt TEXT,
    featured_image VARCHAR(255),
    author_id INT,
    category_id INT,
    tags TEXT,
    status ENUM('published', 'draft', 'private') DEFAULT 'draft',
    comment_status ENUM('open', 'closed') DEFAULT 'open',
    views INT DEFAULT 0,
    comment_count INT DEFAULT 0,
    meta_title VARCHAR(200),
    meta_description TEXT,
    meta_keywords TEXT,
    published_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_slug (slug),
    INDEX idx_status (status),
    INDEX idx_category (category_id),
    INDEX idx_author (author_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

if (mysqli_query($conn, $sql)) {
    echo "✓ Blog posts table created successfully<br>";
} else {
    echo "✗ Error creating blog posts table: " . mysqli_error($conn) . "<br>";
}

// ============================================
// 17. BLOG CATEGORIES TABLE
// ============================================
$sql = "CREATE TABLE IF NOT EXISTS blog_categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(120) UNIQUE NOT NULL,
    description TEXT,
    parent_id INT DEFAULT 0,
    count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_slug (slug),
    INDEX idx_parent (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

if (mysqli_query($conn, $sql)) {
    echo "✓ Blog categories table created successfully<br>";
} else {
    echo "✗ Error creating blog categories table: " . mysqli_error($conn) . "<br>";
}

// ============================================
// 18. NOTIFICATIONS TABLE
// ============================================
$sql = "CREATE TABLE IF NOT EXISTS notifications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    title VARCHAR(200) NOT NULL,
    message TEXT NOT NULL,
    type ENUM('info', 'success', 'warning', 'error', 'order', 'system') DEFAULT 'info',
    icon VARCHAR(50),
    url VARCHAR(255),
    read_status BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_read (read_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

if (mysqli_query($conn, $sql)) {
    echo "✓ Notifications table created successfully<br>";
} else {
    echo "✗ Error creating notifications table: " . mysqli_error($conn) . "<br>";
}

// ============================================
// 19. ACTIVITY LOGS TABLE
// ============================================
$sql = "CREATE TABLE IF NOT EXISTS activity_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    user_type ENUM('admin', 'customer') DEFAULT 'customer',
    action VARCHAR(100) NOT NULL,
    description TEXT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    reference_id INT,
    reference_type VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_action (action),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

if (mysqli_query($conn, $sql)) {
    echo "✓ Activity logs table created successfully<br>";
} else {
    echo "✗ Error creating activity logs table: " . mysqli_error($conn) . "<br>";
}

// ============================================
// 20. SUBSCRIBERS TABLE
// ============================================
$sql = "CREATE TABLE IF NOT EXISTS subscribers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) UNIQUE NOT NULL,
    name VARCHAR(100),
    status ENUM('active', 'inactive', 'unsubscribed') DEFAULT 'active',
    subscribed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    unsubscribed_at TIMESTAMP NULL,
    INDEX idx_email (email),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

if (mysqli_query($conn, $sql)) {
    echo "✓ Subscribers table created successfully<br>";
} else {
    echo "✗ Error creating subscribers table: " . mysqli_error($conn) . "<br>";
}

echo "</div>";
echo "<br><h2 style='color: green;'>✅ Database setup completed successfully!</h2>";
echo "<p>All tables have been created with proper relationships and sample data.</p>";
echo "<div style='margin-top: 20px;'>";
echo "<a href='../index.php' class='btn btn-primary'>Go to Homepage</a> ";
echo "<a href='../admin/login.php' class='btn btn-success'>Go to Admin Panel</a>";
echo "</div>";

mysqli_close($conn);
?>

<style>
    body {
        font-family: Arial, sans-serif;
        padding: 20px;
        background: #f0f2f5;
    }
    h2 {
        color: #2c3e50;
        margin-bottom: 20px;
    }
    h3 {
        color: #3498db;
        margin-top: 20px;
        padding-bottom: 10px;
        border-bottom: 2px solid #eee;
    }
    .btn {
        display: inline-block;
        padding: 10px 20px;
        background: #3498db;
        color: white;
        text-decoration: none;
        border-radius: 5px;
        margin-right: 10px;
        border: none;
        cursor: pointer;
    }
    .btn-primary { background: #3498db; }
    .btn-success { background: #2ecc71; }
    .btn:hover {
        opacity: 0.9;
        transform: translateY(-2px);
    }
    ✓ {
        color: #27ae60;
        font-weight: bold;
    }
    ✗ {
        color: #e74c3c;
        font-weight: bold;
    }
</style>