<?php
header("Content-Type: application/json");
require_once __DIR__ . "/../auth/require-auth.php";
require_once __DIR__ . "/../services/db.php";

$conn = db();

if (!$conn) {
    http_response_code(500);
    echo json_encode(["error" => "Database connection failed"]);
    exit;
}

$data = json_decode(file_get_contents("php://input"), true);

$projectId = isset($data['project_id']) ? (int)$data['project_id'] : null;
$name = trim($data['name'] ?? '');

if (!$projectId || !$name) {
    http_response_code(400);
    echo json_encode([
        "error" => "Missing parameters",
        "message" => "project_id and section name are required."
    ]);
    exit;
}

// 1. Try inserting with sort_order
$sqlSort = "SELECT ISNULL(MAX(sort_order), -1) + 1 AS next_order FROM sections WHERE project_id = ?";
$stmtSort = sqlsrv_query($conn, $sqlSort, [$projectId]);
$nextOrder = 0;
if ($stmtSort !== false && $sortRow = sqlsrv_fetch_array($stmtSort, SQLSRV_FETCH_ASSOC)) {
    $nextOrder = $sortRow["next_order"];
}

$sql = "INSERT INTO sections (project_id, name, sort_order) VALUES (?, ?, ?)";
$stmt = sqlsrv_query($conn, $sql, [$projectId, $name, $nextOrder]);

// 2. Fallback if sort_order doesn't exist in sections table schema
if ($stmt === false) {
    $sqlFallback = "INSERT INTO sections (project_id, name) VALUES (?, ?)";
    $stmt = sqlsrv_query($conn, $sqlFallback, [$projectId, $name]);
}

if ($stmt === false) {
    $errors = sqlsrv_errors();
    http_response_code(500);
    echo json_encode([
        "error" => "SQL INSERT failed",
        "message" => $errors[0]['message'] ?? 'Failed to insert section',
        "sqlsrv_errors" => $errors
    ]);
    exit;
}

// 3. Fetch newly inserted section ID
$resId = sqlsrv_query($conn, "SELECT SCOPE_IDENTITY() AS id");
$idRow = sqlsrv_fetch_array($resId, SQLSRV_FETCH_ASSOC);
$newSectionId = $idRow['id'] ?? null;

echo json_encode([
    "success" => true,
    "message" => "Section created successfully.",
    "section" => [
        "id" => $newSectionId,
        "project_id" => $projectId,
        "name" => $name,
        "sort_order" => $nextOrder
    ]
]);