<?php
// Initialize Meal Types and Time Slots Database Tables
header('Content-Type: text/html; charset=utf-8');

define('DB_HOST', 'localhost');
define('DB_USER', 'root');
define('DB_PASS', '');
define('DB_NAME', 'rest_table');

// Create connection
$conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);

// Check connection
if ($conn->connect_error) {
    die('<div style="color: red; font-family: Arial; padding: 20px;">Connection failed: ' . $conn->connect_error . '</div>');
}

$conn->set_charset("utf8");

echo '<div style="font-family: Arial; padding: 20px; background: #f5f5f5;">';
echo '<h2>Initializing Meal Types & Time Slots Tables...</h2>';

// Create meal_types table
$sql = "CREATE TABLE IF NOT EXISTS meal_types (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    icon VARCHAR(50) DEFAULT 'fas fa-utensils',
    color_code VARCHAR(7) DEFAULT '#d4a574',
    status VARCHAR(20) DEFAULT 'active',
    display_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";

if ($conn->query($sql)) {
    echo '<p style="color: green;"><strong>✓ meal_types table created successfully</strong></p>';
} else {
    echo '<p style="color: red;"><strong>✗ Error creating meal_types table: ' . $conn->error . '</strong></p>';
}

// Create time_slots table
$sql = "CREATE TABLE IF NOT EXISTS time_slots (
    id INT PRIMARY KEY AUTO_INCREMENT,
    meal_type_id INT NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    slot_name VARCHAR(100),
    description TEXT,
    max_capacity INT DEFAULT 100,
    status VARCHAR(20) DEFAULT 'active',
    display_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_slot (meal_type_id, start_time, end_time),
    FOREIGN KEY (meal_type_id) REFERENCES meal_types(id) ON DELETE CASCADE
)";

if ($conn->query($sql)) {
    echo '<p style="color: green;"><strong>✓ time_slots table created successfully</strong></p>';
} else {
    echo '<p style="color: red;"><strong>✗ Error creating time_slots table: ' . $conn->error . '</strong></p>';
}

// Insert default meal types
$sql = "INSERT IGNORE INTO meal_types (name, description, icon, display_order) VALUES 
('Lunch', 'Lunch service timings', 'fas fa-sun', 1),
('Dinner', 'Dinner service timings', 'fas fa-moon', 2)";

if ($conn->query($sql)) {
    echo '<p style="color: green;"><strong>✓ Default meal types inserted</strong></p>';
} else {
    echo '<p style="color: red;"><strong>✗ Error inserting meal types: ' . $conn->error . '</strong></p>';
}

// Insert default time slots for Lunch
$sql = "INSERT IGNORE INTO time_slots (meal_type_id, start_time, end_time, slot_name, display_order) 
SELECT id, '12:00:00', '13:00:00', '12:00 PM - 1:00 PM', 1 FROM meal_types WHERE name = 'Lunch'";
$conn->query($sql);

$sql = "INSERT IGNORE INTO time_slots (meal_type_id, start_time, end_time, slot_name, display_order) 
SELECT id, '13:00:00', '14:00:00', '1:00 PM - 2:00 PM', 2 FROM meal_types WHERE name = 'Lunch'";
$conn->query($sql);

$sql = "INSERT IGNORE INTO time_slots (meal_type_id, start_time, end_time, slot_name, display_order) 
SELECT id, '14:00:00', '15:00:00', '2:00 PM - 3:00 PM', 3 FROM meal_types WHERE name = 'Lunch'";
$conn->query($sql);

// Insert default time slots for Dinner
$sql = "INSERT IGNORE INTO time_slots (meal_type_id, start_time, end_time, slot_name, display_order) 
SELECT id, '18:00:00', '19:00:00', '6:00 PM - 7:00 PM', 1 FROM meal_types WHERE name = 'Dinner'";
$conn->query($sql);

$sql = "INSERT IGNORE INTO time_slots (meal_type_id, start_time, end_time, slot_name, display_order) 
SELECT id, '19:00:00', '20:00:00', '7:00 PM - 8:00 PM', 2 FROM meal_types WHERE name = 'Dinner'";
$conn->query($sql);

$sql = "INSERT IGNORE INTO time_slots (meal_type_id, start_time, end_time, slot_name, display_order) 
SELECT id, '20:00:00', '21:00:00', '8:00 PM - 9:00 PM', 3 FROM meal_types WHERE name = 'Dinner'";
$conn->query($sql);

echo '<p style="color: green;"><strong>✓ Default time slots inserted</strong></p>';

// Verify the tables exist and show counts
$result = $conn->query("SELECT COUNT(*) as count FROM meal_types");
$row = $result->fetch_assoc();
echo '<p><strong>Meal Types Count: ' . $row['count'] . '</strong></p>';

$result = $conn->query("SELECT COUNT(*) as count FROM time_slots");
$row = $result->fetch_assoc();
echo '<p><strong>Time Slots Count: ' . $row['count'] . '</strong></p>';

$conn->close();

echo '<hr style="margin: 20px 0;">';
echo '<p style="background: #d4edda; padding: 15px; border-radius: 4px; color: #155724;"><strong>✓ All tables initialized successfully! You can now use the meal types and time slots system.</strong></p>';
echo '<p style="margin-top: 15px;"><a href="javascript:history.back()" style="padding: 10px 20px; background: #d4a574; color: white; text-decoration: none; border-radius: 4px;">Go Back</a></p>';
echo '</div>';
?>
