<?php
// Script to analyze and update themes table
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

// Start session at the beginning to avoid errors
if (session_status() === PHP_SESSION_NONE) {
    session_start();
}

// Set session variables to simulate admin login
if (!isset($_SESSION['user_id'])) {
    $_SESSION['user_id'] = 1;
    $_SESSION['username'] = 'admin';
    $_SESSION['role'] = 'admin';
}

// Database connection
$config = [
    'db_host' => 'localhost',
    'db_user' => 'nsaorg_mobileapp',
    'db_pass' => '.com/iisADMIN123',
    'db_name' => 'nsaorg_mobileapp'
];

// Connect to database
$conn = new mysqli(
    $config['db_host'],
    $config['db_user'],
    $config['db_pass'],
    $config['db_name']
);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

echo "<h1>Theme Table Analysis</h1>";

// Check if themes table exists
$themes_exists = false;
$result = $conn->query("SHOW TABLES LIKE 'themes'");
if ($result && $result->num_rows > 0) {
    $themes_exists = true;
    echo "<p style='color:green'>✓ The 'themes' table exists.</p>";
} else {
    echo "<p style='color:red'>✗ The 'themes' table does not exist.</p>";
}

// If themes table exists, get its structure
if ($themes_exists) {
    echo "<h2>Table Structure:</h2>";
    echo "<table border='1' cellpadding='5'>";
    echo "<tr><th>Field</th><th>Type</th><th>Null</th><th>Key</th><th>Default</th><th>Extra</th></tr>";
    
    $structure_result = $conn->query("DESCRIBE themes");
    if ($structure_result) {
        while ($row = $structure_result->fetch_assoc()) {
            echo "<tr>";
            echo "<td>" . htmlspecialchars($row['Field']) . "</td>";
            echo "<td>" . htmlspecialchars($row['Type']) . "</td>";
            echo "<td>" . htmlspecialchars($row['Null']) . "</td>";
            echo "<td>" . htmlspecialchars($row['Key']) . "</td>";
            echo "<td>" . htmlspecialchars($row['Default'] ?? 'NULL') . "</td>";
            echo "<td>" . htmlspecialchars($row['Extra']) . "</td>";
            echo "</tr>";
        }
    }
    echo "</table>";
    
    // Get the theme data
    echo "<h2>Current Theme Data:</h2>";
    echo "<table border='1' cellpadding='5'>";
    echo "<tr><th>Theme ID</th><th>Theme Name</th><th>Theme Type</th></tr>";
    
    $data_result = $conn->query("SELECT * FROM themes");
    if ($data_result) {
        if ($data_result->num_rows > 0) {
            while ($row = $data_result->fetch_assoc()) {
                echo "<tr>";
                echo "<td>" . htmlspecialchars($row['theme_id']) . "</td>";
                echo "<td>" . htmlspecialchars($row['theme_name']) . "</td>";
                echo "<td>" . htmlspecialchars($row['theme_type']) . "</td>";
                echo "</tr>";
            }
        } else {
            echo "<tr><td colspan='3'>No themes found in the table.</td></tr>";
        }
    }
    echo "</table>";
}

// Check if national_indicators has a theme_id column
echo "<h2>National Indicators Structure:</h2>";

$theme_id_exists = false;
$result = $conn->query("SHOW COLUMNS FROM national_indicators LIKE 'theme_id'");
if ($result && $result->num_rows > 0) {
    $theme_id_exists = true;
    echo "<p style='color:green'>✓ The 'theme_id' column exists in the national_indicators table.</p>";
} else {
    echo "<p style='color:red'>✗ The 'theme_id' column does not exist in the national_indicators table.</p>";
    
    // Add the column if it doesn't exist
    echo "<h3>Adding theme_id column:</h3>";
    if ($conn->query("ALTER TABLE national_indicators ADD COLUMN theme_id INT(11) DEFAULT NULL")) {
        echo "<p style='color:green'>✓ Successfully added 'theme_id' column to national_indicators table.</p>";
    } else {
        echo "<p style='color:red'>✗ Failed to add 'theme_id' column: " . $conn->error . "</p>";
    }
}

// Show national_indicators columns
echo "<table border='1' cellpadding='5'>";
echo "<tr><th>Field</th><th>Type</th><th>Null</th><th>Key</th><th>Default</th><th>Extra</th></tr>";

$structure_result = $conn->query("DESCRIBE national_indicators");
if ($structure_result) {
    while ($row = $structure_result->fetch_assoc()) {
        echo "<tr>";
        echo "<td>" . htmlspecialchars($row['Field']) . "</td>";
        echo "<td>" . htmlspecialchars($row['Type']) . "</td>";
        echo "<td>" . htmlspecialchars($row['Null']) . "</td>";
        echo "<td>" . htmlspecialchars($row['Key']) . "</td>";
        echo "<td>" . htmlspecialchars($row['Default'] ?? 'NULL') . "</td>";
        echo "<td>" . htmlspecialchars($row['Extra']) . "</td>";
        echo "</tr>";
    }
}
echo "</table>";

// Close connection
$conn->close();
?>

<div style="margin-top: 20px; text-align: center;">
    <a href="indicators.php" style="display: inline-block; padding: 10px 20px; background-color: #1f3889; color: white; text-decoration: none; border-radius: 5px;">
        Go to Indicators Page
    </a>
</div>