<?php
/**
 * Database Setup Utility
 * Ensures all tables have the correct structure
 */
require_once 'config.php';

echo "<!DOCTYPE html>
<html>
<head>
    <title>Database Setup</title>
    <style>
        body { font-family: Arial; margin: 40px; background: #f5f5f5; }
        .container { max-width: 800px; margin: 0 auto; background: white; padding: 30px; border-radius: 10px; }
        h1 { color: #333; border-bottom: 3px solid #667eea; padding-bottom: 10px; }
        .success { background: #d4edda; color: #155724; padding: 12px; margin: 10px 0; border-radius: 5px; }
        .error { background: #f8d7da; color: #721c24; padding: 12px; margin: 10px 0; border-radius: 5px; }
        .warning { background: #fff3cd; color: #856404; padding: 12px; margin: 10px 0; border-radius: 5px; }
        .info { background: #d1ecf1; color: #0c5460; padding: 12px; margin: 10px 0; border-radius: 5px; }
        button { background: #667eea; color: white; padding: 12px 24px; border: none; border-radius: 5px; cursor: pointer; font-size: 16px; }
        button:hover { background: #764ba2; }
        code { background: #f4f4f4; padding: 2px 6px; border-radius: 3px; }
    </style>
</head>
<body>
<div class='container'>
    <h1>🔧 Database Structure Setup</h1>";

// Check if form submitted
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'setup') {
    echo "<h2>Setting up database tables...</h2>";
    
    // Ensure appointments table has required columns
    $columns_to_add = [
        'appointments' => ['user_id', 'appointment_date'],
        'inquiries' => ['customer_id', 'property_id', 'assigned_to', 'broker_id', 'status', 'message']
    ];
    
    foreach ($columns_to_add as $table => $columns) {
        echo "<h3>Updating table: <code>$table</code></h3>";
        foreach ($columns as $column) {
            $result = $conn->query("SHOW COLUMNS FROM $table LIKE '$column'");
            
            if ($result && $result->num_rows === 0) {
                // Column doesn't exist, add it
                $type = 'VARCHAR(50)';
                if (in_array($column, ['id', 'user_id', 'customer_id', 'property_id', 'assigned_to', 'broker_id'])) {
                    $type = 'INT';
                } elseif ($column === 'message') {
                    $type = 'TEXT';
                } elseif ($column === 'appointment_date') {
                    $type = 'DATE';
                } elseif ($column === 'created_at' || $column === 'updated_at') {
                    $type = 'TIMESTAMP DEFAULT CURRENT_TIMESTAMP';
                }
                
                $alter_sql = "ALTER TABLE $table ADD COLUMN $column $type";
                if ($conn->query($alter_sql)) {
                    echo "<div class='success'>✓ Added column <code>$column</code></div>";
                } else {
                    echo "<div class='error'>✗ Failed to add column <code>$column</code>: " . $conn->error . "</div>";
                }
            } else {
                echo "<div class='info'>ℹ Column <code>$column</code> already exists</div>";
            }
        }
    }
    
    echo "<div class='success'><strong>✅ Database setup complete!</strong></div>";
    echo "<p>You can now test the customer dashboard:</p>";
    echo "<ul>
        <li><a href='login.php'>Go to Customer Login</a></li>
        <li><a href='check-tables.php'>View Table Structure</a></li>
    </ul>";
    
} else {
    // Show setup form
    echo "<h2>Ready to Setup?</h2>";
    echo "<p>This utility will ensure all database tables have the correct structure for the customer dashboard.</p>";
    
    echo "<h3>What will be done:</h3>";
    echo "<ul>";
    echo "<li>Add missing columns to <code>appointments</code> table</li>";
    echo "<li>Add missing columns to <code>inquiries</code> table</li>";
    echo "</ul>";
    
    echo "<h3>Columns to be added:</h3>";
    echo "<div style='background: #f4f4f4; padding: 15px; border-radius: 5px; margin-bottom: 20px;'>";
    echo "<strong>appointments:</strong><br>";
    echo "• <code>user_id</code> (INT)<br>";
    echo "• <code>appointment_date</code> (DATE)<br><br>";
    echo "<strong>inquiries:</strong><br>";
    echo "• <code>customer_id</code> (INT)<br>";
    echo "• <code>property_id</code> (INT)<br>";
    echo "• <code>assigned_to</code> (INT)<br>";
    echo "• <code>broker_id</code> (INT)<br>";
    echo "• <code>status</code> (VARCHAR)<br>";
    echo "• <code>message</code> (TEXT)<br>";
    echo "</div>";
    
    echo "<form method='POST'>
        <input type='hidden' name='action' value='setup'>
        <button type='submit'>🔧 Setup Database Now</button>
    </form>";
    
    echo "<div style='margin-top: 30px; padding-top: 30px; border-top: 1px solid #ddd;'>";
    echo "<p><strong>Note:</strong> You can also check the current table structure:</p>";
    echo "<p><a href='check-tables.php' style='color: #667eea; text-decoration: none;'>📋 View Current Table Structure</a></p>";
    echo "</div>";
}

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