<?php
/**
 * Initialize Recipe, Ingredient, and KOT Database Tables
 * Phase 1: Database Schema Extension
 */

header('Content-Type: application/json');
require_once 'config.php';

try {
    // 1. Create ingredients table
    $ingredients_sql = "CREATE TABLE IF NOT EXISTS ingredients (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(255) NOT NULL UNIQUE,
        description TEXT,
        unit VARCHAR(50) NOT NULL COMMENT 'kg, liter, pieces, etc',
        current_stock DECIMAL(10, 2) NOT NULL DEFAULT 0,
        minimum_stock DECIMAL(10, 2) NOT NULL DEFAULT 0,
        unit_cost DECIMAL(10, 2) NOT NULL,
        supplier VARCHAR(255),
        status VARCHAR(50) DEFAULT 'active' COMMENT 'active, inactive, discontinued',
        last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_status (status),
        INDEX idx_name (name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

    if (!$conn->query($ingredients_sql)) {
        throw new Exception('Error creating ingredients table: ' . $conn->error);
    }

    // 2. Create recipes table
    $recipes_sql = "CREATE TABLE IF NOT EXISTS recipes (
        id INT PRIMARY KEY AUTO_INCREMENT,
        menu_item_id INT NOT NULL,
        name VARCHAR(255) NOT NULL,
        description TEXT,
        preparation_time INT DEFAULT 0 COMMENT 'Minutes',
        difficulty_level VARCHAR(50) DEFAULT 'medium' COMMENT 'easy, medium, hard',
        yield_quantity INT DEFAULT 1,
        yield_unit VARCHAR(50),
        chef_notes TEXT,
        status VARCHAR(50) DEFAULT 'active' COMMENT 'active, draft, archived',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON DELETE CASCADE,
        UNIQUE KEY unique_menu_item (menu_item_id),
        INDEX idx_status (status)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

    if (!$conn->query($recipes_sql)) {
        throw new Exception('Error creating recipes table: ' . $conn->error);
    }

    // 3. Create recipe_ingredients table (junction table)
    $recipe_ingredients_sql = "CREATE TABLE IF NOT EXISTS recipe_ingredients (
        id INT PRIMARY KEY AUTO_INCREMENT,
        recipe_id INT NOT NULL,
        ingredient_id INT NOT NULL,
        quantity_required DECIMAL(10, 3) NOT NULL,
        unit VARCHAR(50) NOT NULL,
        notes TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (recipe_id) REFERENCES recipes(id) ON DELETE CASCADE,
        FOREIGN KEY (ingredient_id) REFERENCES ingredients(id) ON DELETE CASCADE,
        UNIQUE KEY unique_recipe_ingredient (recipe_id, ingredient_id),
        INDEX idx_recipe (recipe_id),
        INDEX idx_ingredient (ingredient_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

    if (!$conn->query($recipe_ingredients_sql)) {
        throw new Exception('Error creating recipe_ingredients table: ' . $conn->error);
    }

    // 4. Create kot_tickets table (Kitchen Order Tickets)
    $kot_tickets_sql = "CREATE TABLE IF NOT EXISTS kot_tickets (
        id INT PRIMARY KEY AUTO_INCREMENT,
        order_id INT NOT NULL,
        ticket_number VARCHAR(50) UNIQUE NOT NULL,
        status VARCHAR(50) DEFAULT 'pending' COMMENT 'pending, acknowledged, preparing, ready, completed, cancelled',
        priority VARCHAR(50) DEFAULT 'normal' COMMENT 'low, normal, high, urgent',
        table_number VARCHAR(50),
        server_name VARCHAR(255),
        special_instructions TEXT,
        generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        acknowledged_at TIMESTAMP NULL,
        started_at TIMESTAMP NULL,
        completed_at TIMESTAMP NULL,
        FOREIGN KEY (order_id) REFERENCES dine_in_orders(id) ON DELETE CASCADE,
        INDEX idx_status (status),
        INDEX idx_priority (priority),
        INDEX idx_generated (generated_at),
        UNIQUE KEY unique_order_ticket (order_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

    if (!$conn->query($kot_tickets_sql)) {
        throw new Exception('Error creating kot_tickets table: ' . $conn->error);
    }

    // 5. Create ingredient_allocations table
    $ingredient_allocations_sql = "CREATE TABLE IF NOT EXISTS ingredient_allocations (
        id INT PRIMARY KEY AUTO_INCREMENT,
        order_id INT NOT NULL,
        item_id INT NOT NULL,
        ingredient_id INT NOT NULL,
        quantity_allocated DECIMAL(10, 3) NOT NULL,
        unit VARCHAR(50) NOT NULL,
        status VARCHAR(50) DEFAULT 'allocated' COMMENT 'allocated, used, wasted, returned',
        allocated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        used_at TIMESTAMP NULL,
        notes TEXT,
        FOREIGN KEY (order_id) REFERENCES dine_in_orders(id) ON DELETE CASCADE,
        FOREIGN KEY (item_id) REFERENCES dine_in_order_items(id) ON DELETE CASCADE,
        FOREIGN KEY (ingredient_id) REFERENCES ingredients(id) ON DELETE CASCADE,
        INDEX idx_order (order_id),
        INDEX idx_item (item_id),
        INDEX idx_status (status)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

    if (!$conn->query($ingredient_allocations_sql)) {
        throw new Exception('Error creating ingredient_allocations table: ' . $conn->error);
    }

    echo json_encode([
        'success' => true,
        'message' => 'All recipe and ingredient tables created successfully',
        'tables_created' => [
            'ingredients',
            'recipes',
            'recipe_ingredients',
            'kot_tickets',
            'ingredient_allocations'
        ]
    ]);

} catch (Exception $e) {
    http_response_code(500);
    echo json_encode([
        'success' => false,
        'message' => $e->getMessage()
    ]);
}

$conn->close();
?>
