<?php
/**
 * Initialize Managers Table and Related Tables
 * Sets up the managers database structure
 */

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

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

if ($conn->connect_error) {
    die(json_encode(['success' => false, 'message' => 'Connection failed: ' . $conn->connect_error]));
}

// Create managers table
$sql = "CREATE TABLE IF NOT EXISTS managers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    employee_id VARCHAR(50) UNIQUE,
    shift_type VARCHAR(50) DEFAULT 'day' COMMENT 'day, evening, night',
    status VARCHAR(20) DEFAULT 'active',
    last_login TIMESTAMP NULL,
    is_online BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";

if ($conn->query($sql) === TRUE) {
    echo json_encode(['success' => true, 'message' => 'managers table created']);
} else {
    echo json_encode(['success' => false, 'message' => 'Error creating managers table: ' . $conn->error]);
}

// Create servers table (staff that take orders at tables)
$sql = "CREATE TABLE IF NOT EXISTS servers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    employee_id VARCHAR(50) UNIQUE,
    phone VARCHAR(20),
    status VARCHAR(20) DEFAULT 'available' COMMENT 'available, busy, break, offline',
    shift_start TIME,
    shift_end TIME,
    max_active_orders INT NOT NULL DEFAULT 1,
    current_table_id INT,
    rating DECIMAL(3, 2) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (current_table_id) REFERENCES restaurant_tables(id) ON DELETE SET NULL
)";

if ($conn->query($sql) === TRUE) {
    echo json_encode(['success' => true, 'message' => 'servers table created']);
} else {
    echo json_encode(['success' => false, 'message' => 'Error creating servers table: ' . $conn->error]);
}

// Create table_assignments table (tracks table-server assignments)
$sql = "CREATE TABLE IF NOT EXISTS table_assignments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    table_id INT NOT NULL,
    server_id INT,
    manager_id INT,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ended_at TIMESTAMP NULL,
    status VARCHAR(50) DEFAULT 'assigned' COMMENT 'assigned, completed, cancelled',
    notes TEXT,
    FOREIGN KEY (table_id) REFERENCES restaurant_tables(id) ON DELETE CASCADE,
    FOREIGN KEY (server_id) REFERENCES servers(id) ON DELETE SET NULL,
    FOREIGN KEY (manager_id) REFERENCES managers(id) ON DELETE SET NULL
)";

if ($conn->query($sql) === TRUE) {
    echo json_encode(['success' => true, 'message' => 'table_assignments table created']);
} else {
    echo json_encode(['success' => false, 'message' => 'Error creating table_assignments: ' . $conn->error]);
}

// Create table_merges table (tracks merged tables)
$sql = "CREATE TABLE IF NOT EXISTS table_merges (
    id INT PRIMARY KEY AUTO_INCREMENT,
    primary_table_id INT NOT NULL,
    merged_table_id INT NOT NULL,
    manager_id INT,
    merged_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    unmerged_at TIMESTAMP NULL,
    status VARCHAR(50) DEFAULT 'active' COMMENT 'active, completed',
    FOREIGN KEY (primary_table_id) REFERENCES restaurant_tables(id) ON DELETE CASCADE,
    FOREIGN KEY (merged_table_id) REFERENCES restaurant_tables(id) ON DELETE CASCADE,
    FOREIGN KEY (manager_id) REFERENCES managers(id) ON DELETE SET NULL
)";

if ($conn->query($sql) === TRUE) {
    echo json_encode(['success' => true, 'message' => 'table_merges table created']);
} else {
    echo json_encode(['success' => false, 'message' => 'Error creating table_merges: ' . $conn->error]);
}

// Create billing table (POS transactions)
$sql = "CREATE TABLE IF NOT EXISTS billings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    billing_number VARCHAR(50) UNIQUE NOT NULL,
    table_id INT NOT NULL,
    manager_id INT,
    order_id INT,
    subtotal DECIMAL(10, 2) DEFAULT 0,
    tax DECIMAL(10, 2) DEFAULT 0,
    discount DECIMAL(10, 2) DEFAULT 0,
    total DECIMAL(10, 2) DEFAULT 0,
    payment_method VARCHAR(50) COMMENT 'cash, card, upi, online',
    payment_status VARCHAR(50) DEFAULT 'pending' COMMENT 'pending, completed, cancelled',
    status VARCHAR(50) DEFAULT 'open' COMMENT 'open, billed, completed',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP NULL,
    FOREIGN KEY (table_id) REFERENCES restaurant_tables(id) ON DELETE CASCADE,
    FOREIGN KEY (manager_id) REFERENCES managers(id) ON DELETE SET NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL
)";

if ($conn->query($sql) === TRUE) {
    echo json_encode(['success' => true, 'message' => 'billings table created']);
} else {
    echo json_encode(['success' => false, 'message' => 'Error creating billings: ' . $conn->error]);
}

// Create billing_items table (items in a billing)
$sql = "CREATE TABLE IF NOT EXISTS billing_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    billing_id INT NOT NULL,
    item_name VARCHAR(100) NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    unit_price DECIMAL(10, 2) NOT NULL,
    total_price DECIMAL(10, 2) NOT NULL,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (billing_id) REFERENCES billings(id) ON DELETE CASCADE
)";

if ($conn->query($sql) === TRUE) {
    echo json_encode(['success' => true, 'message' => 'billing_items table created']);
} else {
    echo json_encode(['success' => false, 'message' => 'Error creating billing_items: ' . $conn->error]);
}

// Create daily_operations table (tracks daily activities)
$sql = "CREATE TABLE IF NOT EXISTS daily_operations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    manager_id INT NOT NULL,
    operation_date DATE NOT NULL,
    shift_type VARCHAR(50),
    tables_served INT DEFAULT 0,
    total_revenue DECIMAL(12, 2) DEFAULT 0,
    total_orders INT DEFAULT 0,
    notes TEXT,
    status VARCHAR(50) DEFAULT 'open' COMMENT 'open, closed',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    closed_at TIMESTAMP NULL,
    FOREIGN KEY (manager_id) REFERENCES managers(id) ON DELETE CASCADE,
    UNIQUE KEY unique_daily_op (manager_id, operation_date)
)";

if ($conn->query($sql) === TRUE) {
    echo json_encode(['success' => true, 'message' => 'daily_operations table created']);
} else {
    echo json_encode(['success' => false, 'message' => 'Error creating daily_operations: ' . $conn->error]);
}

$conn->close();
?>
