tinker_tickets/create_ticket_api.php

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

error_reporting(E_ALL);
ini_set('display_errors', 1);
file_put_contents('debug.log', print_r($_POST, true) . "\n" . file_get_contents('php://input') . "\n", FILE_APPEND);


// Load environment variables with error check
$envFile = __DIR__ . '/.env';
if (!file_exists($envFile)) {
    echo json_encode([
        'success' => false,
        'error' => 'Configuration file not found'
    ]);
    exit;
}

$envVars = parse_ini_file($envFile);
if (!$envVars) {
    echo json_encode([
        'success' => false,
        'error' => 'Invalid configuration file'
    ]);
    exit;
}

// Database connection with detailed error handling
$conn = new mysqli(
    $envVars['DB_HOST'],
    $envVars['DB_USER'],
    $envVars['DB_PASS'],
    $envVars['DB_NAME']
);

if ($conn->connect_error) {
    echo json_encode([
        'success' => false,
        'error' => 'Database connection failed: ' . $conn->connect_error
    ]);
    exit;
}

// Create tickets table with hash column if not exists
$createTableSQL = "CREATE TABLE IF NOT EXISTS tickets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ticket_id VARCHAR(9) NOT NULL,
    title VARCHAR(255) NOT NULL,
    hash VARCHAR(64) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_hash (hash)
)";

$conn->query($createTableSQL);

// Parse input regardless of content-type header
$rawInput = file_get_contents('php://input');
$data = json_decode($rawInput, true);

// Generate hash from stable components
function generateTicketHash($data) {
    // Extract device name if present (matches /dev/sdX pattern)
    preg_match('/\/dev\/sd[a-z]/', $data['title'], $deviceMatches);
    $isDriveTicket = !empty($deviceMatches);
    
    // Extract hostname from title [hostname][tags]...
    preg_match('/\[([\w\d-]+)\]/', $data['title'], $hostMatches);
    $hostname = $hostMatches[1] ?? '';

    // Extract SMART attribute types without their values
    preg_match_all('/Warning ([^:]+)/', $data['title'], $smartMatches);
    $smartAttributes = $smartMatches[1] ?? [];

    // Build stable components with only static data
    $stableComponents = [
        'hostname' => $hostname,
        'smart_attributes' => $smartAttributes,
        'environment_tags' => array_filter(
            explode('][', $data['title']),
            fn($tag) => in_array($tag, ['production', 'development', 'staging', 'single-node'])
        )
    ];

    // Only include device info for drive-specific tickets
    if ($isDriveTicket) {
        $stableComponents['device'] = $deviceMatches[0];
    }

    // Sort arrays for consistent hashing
    sort($stableComponents['smart_attributes']);
    sort($stableComponents['environment_tags']);
    
    return hash('sha256', json_encode($stableComponents, JSON_UNESCAPED_SLASHES));
}

// Check for duplicate tickets
$ticketHash = generateTicketHash($data);
$checkDuplicateSQL = "SELECT ticket_id FROM tickets WHERE hash = ? AND created_at > DATE_SUB(NOW(), INTERVAL 24 HOUR)";
$checkStmt = $conn->prepare($checkDuplicateSQL);
$checkStmt->bind_param("s", $ticketHash);
$checkStmt->execute();
$result = $checkStmt->get_result();

if ($result->num_rows > 0) {
    $existingTicket = $result->fetch_assoc();
    echo json_encode([
        'success' => false,
        'error' => 'Duplicate ticket',
        'existing_ticket_id' => $existingTicket['ticket_id']
    ]);
    exit;
}

// Force JSON content type for all incoming requests
header('Content-Type: application/json');

if (!$data) {
    // Try parsing as URL-encoded data
    parse_str($rawInput, $data);
}

// Generate ticket ID (9-digit format with leading zeros)
$ticket_id = sprintf('%09d', mt_rand(1, 999999999));

// Prepare insert query
$sql = "INSERT INTO tickets (ticket_id, title, description, status, priority, category, type, hash) 
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)";

$stmt = $conn->prepare($sql);
// First, store all values in variables
$title = $data['title'];
$description = $data['description'];
$status = $data['status'] ?? 'Open';
$priority = $data['priority'] ?? '4';
$category = $data['category'] ?? 'General';
$type = $data['type'] ?? 'Issue';

// Then use the variables in bind_param
$stmt->bind_param(
    "ssssssss",
    $ticket_id,
    $title,
    $description,
    $status,
    $priority,
    $category,
    $type,
    $ticketHash
);

if ($stmt->execute()) {
    echo json_encode([
        'success' => true,
        'ticket_id' => $ticket_id,
        'message' => 'Ticket created successfully'
    ]);
} else {
    echo json_encode([
        'success' => false,
        'error' => $conn->error
    ]);
}

$stmt->close();
$conn->close();

// Discord webhook
$discord_webhook_url = $envVars['DISCORD_WEBHOOK_URL'];

// Map priorities to Discord colors (decimal format)
$priorityColors = [
    "1" => 16736589, // --priority-1: #ff4d4d
    "2" => 16753958, // --priority-2: #ffa726
    "3" => 4363509,  // --priority-3: #42a5f5
    "4" => 6736490   // --priority-4: #66bb6a
];

$discord_data = [
    "content" => "",
    "embeds" => [[
        "title" => "New Ticket Created: #" . $ticket_id,
        "description" => $title,
        "url" => "http://t.lotusguild.org/ticket/" . $ticket_id,
        "color" => $priorityColors[$priority],
        "fields" => [
            ["name" => "Priority", "value" => $priority, "inline" => true],
            ["name" => "Category", "value" => $category, "inline" => true],
            ["name" => "Type", "value" => $type, "inline" => true]
        ]
    ]]
];

$ch = curl_init($discord_webhook_url);
curl_setopt($ch, CURLOPT_HTTPHEADER, ['Content-Type: application/json']);
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($discord_data));
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_exec($ch);
curl_close($ch);