<?php
/**
 * Setup Script: Add status column to schools table
 * Run this once to add the status tracking functionality
 */

require_once 'config.php';

echo "<!DOCTYPE html>
<html>
<head>
    <title>Database Setup - School Status Column</title>
    <style>
        body {
            font-family: Arial, sans-serif;
            max-width: 800px;
            margin: 50px auto;
            padding: 20px;
            background: #f5f5f5;
        }
        .container {
            background: white;
            padding: 30px;
            border-radius: 10px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.1);
        }
        .success {
            color: #22c55e;
            padding: 15px;
            background: #f0fdf4;
            border-left: 4px solid #22c55e;
            margin: 10px 0;
            border-radius: 4px;
        }
        .error {
            color: #ef4444;
            padding: 15px;
            background: #fef2f2;
            border-left: 4px solid #ef4444;
            margin: 10px 0;
            border-radius: 4px;
        }
        .info {
            color: #3b82f6;
            padding: 15px;
            background: #eff6ff;
            border-left: 4px solid #3b82f6;
            margin: 10px 0;
            border-radius: 4px;
        }
        h1 { color: #1f2937; }
        code {
            background: #f3f4f6;
            padding: 2px 6px;
            border-radius: 4px;
            font-family: 'Courier New', monospace;
        }
    </style>
</head>
<body>
    <div class='container'>
        <h1>🔧 Database Setup: School Status Column</h1>";

if (!isset($conn) || !$conn instanceof mysqli) {
    echo "<div class='error'><strong>Error:</strong> Database connection failed. Check your config.php file.</div>";
    echo "</div></body></html>";
    exit;
}

// Check if status column already exists
$check_column = "SHOW COLUMNS FROM schools LIKE 'status'";
$result = $conn->query($check_column);

if ($result && $result->num_rows > 0) {
    echo "<div class='info'><strong>ℹ️ Info:</strong> The <code>status</code> column already exists in the <code>schools</code> table. No action needed.</div>";
} else {
    // Add status column
    $sql = "ALTER TABLE schools ADD COLUMN status VARCHAR(20) DEFAULT 'active' AFTER subscription_expiry_date";
    
    if ($conn->query($sql)) {
        echo "<div class='success'><strong>✅ Success:</strong> Successfully added <code>status</code> column to <code>schools</code> table.</div>";
        
        // Update all existing schools to 'active' status
        $update_sql = "UPDATE schools SET status = 'active' WHERE status IS NULL OR status = ''";
        if ($conn->query($update_sql)) {
            echo "<div class='success'><strong>✅ Success:</strong> All existing schools set to 'active' status.</div>";
        }
        
        echo "<div class='info'>
                <strong>📋 Column Details:</strong>
                <ul>
                    <li><strong>Column Name:</strong> status</li>
                    <li><strong>Type:</strong> VARCHAR(20)</li>
                    <li><strong>Default Value:</strong> 'active'</li>
                    <li><strong>Possible Values:</strong> 'active', 'suspended'</li>
                </ul>
              </div>";
    } else {
        echo "<div class='error'><strong>❌ Error:</strong> Failed to add status column: " . $conn->error . "</div>";
    }
}

echo "<div class='info'>
        <strong>🎯 Next Steps:</strong>
        <ul>
            <li>The subscription timer now automatically suspends schools when their subscription expires</li>
            <li>Suspended schools will be redirected to the suspension notice page</li>
            <li>Super admins can manually activate/deactivate schools using the status column</li>
        </ul>
      </div>";

echo "<div class='info' style='margin-top: 20px;'>
        <strong>⚠️ Important:</strong> You can safely delete this file (<code>setup_school_status.php</code>) after running it once.
      </div>";

$conn->close();

echo "</div></body></html>";
?>