<?php
session_start();
header('Content-Type: application/json');

require_once __DIR__ . '/../config/db.php';

$response = ['success' => false, 'products' => [], 'tools' => []];

try {
    $serviceId = isset($_GET['service_id']) ? intval($_GET['service_id']) : 0;

    if (!$serviceId) {
        throw new Exception('Service ID required');
    }

    // Ensure tables exist
    $connection->query("CREATE TABLE IF NOT EXISTS service_products (
        id INT AUTO_INCREMENT PRIMARY KEY,
        service_id INT NOT NULL,
        product_name VARCHAR(255) NOT NULL,
        cost DECIMAL(10, 2),
        quantity VARCHAR(100),
        spot_x DECIMAL(5,2) NULL,
        spot_y DECIMAL(5,2) NULL,
        sort_order INT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE,
        INDEX idx_service_id (service_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    $connection->query("ALTER TABLE service_products ADD COLUMN IF NOT EXISTS spot_x DECIMAL(5,2) NULL");
    $connection->query("ALTER TABLE service_products ADD COLUMN IF NOT EXISTS spot_y DECIMAL(5,2) NULL");

    $connection->query("CREATE TABLE IF NOT EXISTS service_tools (
        id INT AUTO_INCREMENT PRIMARY KEY,
        service_id INT NOT NULL,
        tool_name VARCHAR(255) NOT NULL,
        tool_description TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE,
        INDEX idx_service_id (service_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    // Fetch products
    $products = [];
    $productStmt = $connection->prepare("SELECT id, product_name, cost, quantity, spot_x, spot_y FROM service_products WHERE service_id = ? ORDER BY sort_order ASC, id ASC");
    if ($productStmt) {
        $productStmt->bind_param('i', $serviceId);
        $productStmt->execute();
        $result = $productStmt->get_result();
        
        while ($row = $result->fetch_assoc()) {
            $products[] = [
                'id' => (int)$row['id'],
                'product_name' => $row['product_name'],
                'cost' => floatval($row['cost']),
                'quantity' => $row['quantity'],
                'spot_x' => $row['spot_x'] !== null ? (float)$row['spot_x'] : null,
                'spot_y' => $row['spot_y'] !== null ? (float)$row['spot_y'] : null
            ];
        }
        $productStmt->close();
    }

    // Fetch tools
    $tools = [];
    $toolStmt = $connection->prepare("SELECT id, tool_name, tool_description FROM service_tools WHERE service_id = ? ORDER BY id ASC");
    if ($toolStmt) {
        $toolStmt->bind_param('i', $serviceId);
        $toolStmt->execute();
        $result = $toolStmt->get_result();
        
        while ($row = $result->fetch_assoc()) {
            $tools[] = [
                'id' => (int)$row['id'],
                'tool_name' => $row['tool_name'],
                'tool_description' => $row['tool_description']
            ];
        }
        $toolStmt->close();
    }

    $response = [
        'success' => true,
        'products' => $products,
        'tools' => $tools
    ];

} catch (Exception $e) {
    http_response_code(400);
    $response['message'] = 'Error: ' . $e->getMessage();
}

echo json_encode($response);
exit;
