<?php
require_once __DIR__ . '/config/database.php';

echo "<h2>Weapons Inventory Table Structure Debug</h2>";

// Check if table exists
$tableCheck = Database::fetchOne("SHOW TABLES LIKE 'weapons_inventory'");
if (!$tableCheck) {
    echo "<p style='color:red'>❌ weapons_inventory table does not exist!</p>";
    exit;
}

// Get all columns
$columns = Database::fetchAll("SHOW COLUMNS FROM weapons_inventory");
echo "<h3>Current Columns in weapons_inventory:</h3>";
echo "<table border='1' cellpadding='5' cellspacing='0'>";
echo "<tr><th>Column Name</th><th>Type</th><th>Null</th><th>Default</th></tr>";
foreach ($columns as $col) {
    echo "<tr>";
    echo "<td>" . $col['Field'] . "</td>";
    echo "<td>" . $col['Type'] . "</td>";
    echo "<td>" . $col['Null'] . "</td>";
    echo "<td>" . ($col['Default'] ?? 'NULL') . "</td>";
    echo "</tr>";
}
echo "</table>";

// Check missing columns
$requiredColumns = [
    'custodian_phone', 'custodian_zone_id', 'custodian_command_id',
    'lost_by_name', 'lost_by_rank', 'lost_by_nis', 'lost_by_phone',
    'lost_zone_id', 'lost_command_id', 'lost_zone', 'lost_command'
];

$existingColumns = array_column($columns, 'Field');
$missingColumns = array_diff($requiredColumns, $existingColumns);

if (!empty($missingColumns)) {
    echo "<h3 style='color:red'>❌ Missing Columns:</h3>";
    echo "<ul>";
    foreach ($missingColumns as $col) {
        echo "<li>" . $col . "</li>";
    }
    echo "</ul>";
    
    echo "<h3>Run this SQL to add missing columns:</h3>";
    echo "<pre>";
    foreach ($missingColumns as $col) {
        switch($col) {
            case 'custodian_phone':
                echo "ALTER TABLE weapons_inventory ADD COLUMN custodian_phone VARCHAR(20) NULL AFTER custodian_nis;\n";
                break;
            case 'custodian_zone_id':
                echo "ALTER TABLE weapons_inventory ADD COLUMN custodian_zone_id INT NULL AFTER custodian_phone;\n";
                break;
            case 'custodian_command_id':
                echo "ALTER TABLE weapons_inventory ADD COLUMN custodian_command_id INT NULL AFTER custodian_zone_id;\n";
                break;
            case 'lost_by_name':
                echo "ALTER TABLE weapons_inventory ADD COLUMN lost_by_name VARCHAR(255) NULL AFTER custodian_command_id;\n";
                break;
            case 'lost_by_rank':
                echo "ALTER TABLE weapons_inventory ADD COLUMN lost_by_rank VARCHAR(50) NULL AFTER lost_by_name;\n";
                break;
            case 'lost_by_nis':
                echo "ALTER TABLE weapons_inventory ADD COLUMN lost_by_nis VARCHAR(50) NULL AFTER lost_by_rank;\n";
                break;
            case 'lost_by_phone':
                echo "ALTER TABLE weapons_inventory ADD COLUMN lost_by_phone VARCHAR(20) NULL AFTER lost_by_nis;\n";
                break;
            case 'lost_zone_id':
                echo "ALTER TABLE weapons_inventory ADD COLUMN lost_zone_id INT NULL AFTER lost_by_phone;\n";
                break;
            case 'lost_command_id':
                echo "ALTER TABLE weapons_inventory ADD COLUMN lost_command_id INT NULL AFTER lost_zone_id;\n";
                break;
            case 'lost_zone':
                echo "ALTER TABLE weapons_inventory ADD COLUMN lost_zone VARCHAR(100) NULL AFTER lost_command_id;\n";
                break;
            case 'lost_command':
                echo "ALTER TABLE weapons_inventory ADD COLUMN lost_command VARCHAR(100) NULL AFTER lost_zone;\n";
                break;
        }
    }
    echo "</pre>";
} else {
    echo "<p style='color:green'>✅ All required columns exist!</p>";
}

// Test insert with minimal data
echo "<h3>Testing Minimal Insert:</h3>";
$testData = [
    'weapon_id' => 'TEST_' . time(),
    'make_model' => 'Test Model',
    'serial_no' => 'TEST_SN_' . time(),
    'source' => 'Test Source',
    'condition' => 'Serviceable',
    'current_location' => 'Armoury',
    'created_by' => 1
];

try {
    $result = Database::insert('weapons_inventory', $testData);
    if ($result) {
        echo "<p style='color:green'>✅ Test insert successful! ID: " . $result . "</p>";
        // Clean up test record
        Database::delete('weapons_inventory', 'id = ?', [$result]);
        echo "<p>✅ Test record deleted</p>";
    } else {
        echo "<p style='color:red'>❌ Test insert failed</p>";
    }
} catch (Exception $e) {
    echo "<p style='color:red'>❌ Test insert error: " . $e->getMessage() . "</p>";
}
?>