<?php
/**
 * Menu Database Initialization
 * Creates tables for menu filters, subcategories, and menu items
 */

require_once 'config.php';

try {
    // Create menu_filters table
    $sql = "CREATE TABLE IF NOT EXISTS `menu_filters` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `filter_name` VARCHAR(100) NOT NULL UNIQUE,
        `description` TEXT,
        `display_order` INT DEFAULT 0,
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX(`display_order`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    
    $conn->query($sql);

    // Create menu_subcategories table
    $sql = "CREATE TABLE IF NOT EXISTS `menu_subcategories` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `filter_id` INT NOT NULL,
        `subcategory_name` VARCHAR(100) NOT NULL,
        `display_order` INT DEFAULT 0,
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY `unique_subcategory` (`filter_id`, `subcategory_name`),
        FOREIGN KEY (`filter_id`) REFERENCES `menu_filters`(`id`) ON DELETE CASCADE,
        INDEX(`filter_id`),
        INDEX(`display_order`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    
    $conn->query($sql);

    // Create menu_items table
    $sql = "CREATE TABLE IF NOT EXISTS `menu_items` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `subcategory_id` INT NOT NULL,
        `item_name` VARCHAR(150) NOT NULL,
        `description` TEXT,
        `price` DECIMAL(10, 2) NOT NULL,
        `image_path` VARCHAR(255),
        `display_order` INT DEFAULT 0,
        `is_available` BOOLEAN DEFAULT TRUE,
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (`subcategory_id`) REFERENCES `menu_subcategories`(`id`) ON DELETE CASCADE,
        INDEX(`subcategory_id`),
        INDEX(`display_order`),
        INDEX(`is_available`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    
    $conn->query($sql);

    // Insert default subcategories if they don't exist
    // These will serve as templates - actual subcategories will be created per filter
    
    echo json_encode([
        'success' => true,
        'message' => 'Menu tables created successfully',
        'tables' => ['menu_filters', 'menu_subcategories', 'menu_items']
    ]);

} catch (Exception $e) {
    echo json_encode([
        'success' => false,
        'message' => 'Error creating tables: ' . $e->getMessage()
    ]);
}

$conn->close();
?>
