<?php
// Database update script to add team member columns
require_once 'config/database.php';

echo "<!DOCTYPE html>
<html lang='en'>
<head>
    <meta charset='UTF-8'>
    <meta name='viewport' content='width=device-width, initial-scale=1.0'>
    <title>Database Update - Team Members</title>
    <link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css'>
    <style>
        body { padding: 2rem; background: #f8f9fa; }
        .container { max-width: 800px; }
        .alert { margin-bottom: 1rem; }
        .btn { background: #2c2b7c; border-color: #2c2b7c; }
        .btn:hover { background: #2c2b7c; border-color: #2c2b7c; }
    </style>
</head>
<body>
    <div class='container'>
        <div class='card'>
            <div class='card-header bg-primary text-white'>
                <h3><i class='fas fa-database me-2'></i>Database Update - Team Members</h3>
            </div>
            <div class='card-body'>";

try {
    // Check if columns already exist
    $check_sql = "SHOW COLUMNS FROM proposals LIKE 'member1_name'";
    $result = $conn->query($check_sql);
    
    if ($result->num_rows > 0) {
        echo "<div class='alert alert-info'>
                <i class='fas fa-info-circle me-2'></i>
                <strong>Info:</strong> Team member columns already exist in the proposals table.
              </div>";
    } else {
        // Add team member columns
        $alter_sql = "ALTER TABLE proposals 
                     ADD COLUMN member1_name VARCHAR(255) NULL,
                     ADD COLUMN member1_programme VARCHAR(255) NULL,
                     ADD COLUMN member1_level VARCHAR(50) NULL,
                     ADD COLUMN member1_email VARCHAR(255) NULL,
                     ADD COLUMN member2_name VARCHAR(255) NULL,
                     ADD COLUMN member2_programme VARCHAR(255) NULL,
                     ADD COLUMN member2_level VARCHAR(50) NULL,
                     ADD COLUMN member2_email VARCHAR(255) NULL";
        
        if ($conn->query($alter_sql) === TRUE) {
            echo "<div class='alert alert-success'>
                    <i class='fas fa-check-circle me-2'></i>
                    <strong>Success!</strong> Team member columns have been successfully added to the proposals table.
                  </div>";
            
            echo "<div class='alert alert-info'>
                    <i class='fas fa-list me-2'></i>
                    <strong>Columns Added:</strong>
                    <ul class='mb-0 mt-2'>
                        <li>member1_name (VARCHAR 255)</li>
                        <li>member1_programme (VARCHAR 255)</li>
                        <li>member1_level (VARCHAR 50)</li>
                        <li>member1_email (VARCHAR 255)</li>
                        <li>member2_name (VARCHAR 255)</li>
                        <li>member2_programme (VARCHAR 255)</li>
                        <li>member2_level (VARCHAR 50)</li>
                        <li>member2_email (VARCHAR 255)</li>
                    </ul>
                  </div>";
        } else {
            echo "<div class='alert alert-danger'>
                    <i class='fas fa-exclamation-triangle me-2'></i>
                    <strong>Error:</strong> " . $conn->error . "
                  </div>";
        }
    }
    
    // Show current table structure
    echo "<div class='alert alert-secondary'>
            <i class='fas fa-table me-2'></i>
            <strong>Current Proposals Table Structure:</strong>
          </div>";
    
    $structure_sql = "DESCRIBE proposals";
    $structure_result = $conn->query($structure_sql);
    
    if ($structure_result->num_rows > 0) {
        echo "<div class='table-responsive'>
                <table class='table table-striped table-sm'>
                    <thead class='table-dark'>
                        <tr>
                            <th>Field</th>
                            <th>Type</th>
                            <th>Null</th>
                            <th>Key</th>
                            <th>Default</th>
                        </tr>
                    </thead>
                    <tbody>";
        
        while ($row = $structure_result->fetch_assoc()) {
            $highlight = (strpos($row['Field'], 'member') !== false) ? 'table-success' : '';
            echo "<tr class='$highlight'>
                    <td><strong>" . $row['Field'] . "</strong></td>
                    <td>" . $row['Type'] . "</td>
                    <td>" . $row['Null'] . "</td>
                    <td>" . $row['Key'] . "</td>
                    <td>" . $row['Default'] . "</td>
                  </tr>";
        }
        
        echo "</tbody></table></div>";
    }
    
} catch (Exception $e) {
    echo "<div class='alert alert-danger'>
            <i class='fas fa-exclamation-triangle me-2'></i>
            <strong>Error:</strong> " . $e->getMessage() . "
          </div>";
}

echo "<div class='text-center mt-4'>
        <a href='submit_proposal.php' class='btn btn-primary me-2'>
            <i class='fas fa-arrow-left me-1'></i>Go to Submit Proposal
        </a>
        <a href='index.php' class='btn btn-secondary'>
            <i class='fas fa-home me-1'></i>Go to Homepage
        </a>
      </div>
    </div>
  </div>
</div>
</body>
</html>";
?>


