<?php
require_once 'config.php';

// Function to execute a query and return result
function executeQuery($query, $description = '') {
    global $conn;
    if ($conn->query($query) === TRUE) {
        return "<div style='color: green; margin: 10px 0;'>✓ " . ($description ? $description : "Query executed") . " successfully</div>";
    } else {
        return "<div style='color: red; margin: 10px 0;'>✗ Error " . ($description ? "in " . $description : "") . ": " . $conn->error . "</div>";
    }
}

// Check if tables already exist
$result = $conn->query("SHOW TABLES LIKE 'customers'");
$table_exists = $result && $result->num_rows > 0;

?>
<!DOCTYPE html>
<html>
<head>
    <title>Database Setup - Zemaraim</title>
    <style>
        body {
            font-family: Arial, sans-serif;
            max-width: 800px;
            margin: 50px auto;
            padding: 20px;
            background: #f8f9fa;
        }
        h1 {
            color: #667eea;
            text-align: center;
        }
        .container {
            background: white;
            padding: 30px;
            border-radius: 8px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.1);
        }
        .status {
            padding: 20px;
            margin: 20px 0;
            border-radius: 5px;
        }
        .success {
            background: rgba(46, 213, 115, 0.1);
            color: #2ed573;
            border: 1px solid rgba(46, 213, 115, 0.3);
        }
        .info {
            background: rgba(102, 126, 234, 0.1);
            color: #667eea;
            border: 1px solid rgba(102, 126, 234, 0.3);
        }
        .warning {
            background: rgba(255, 165, 2, 0.1);
            color: #ffa502;
            border: 1px solid rgba(255, 165, 2, 0.3);
        }
        button {
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            color: white;
            border: none;
            padding: 10px 20px;
            border-radius: 5px;
            cursor: pointer;
            font-size: 14px;
            transition: all 0.3s ease;
        }
        button:hover {
            transform: translateY(-2px);
            box-shadow: 0 5px 15px rgba(102, 126, 234, 0.3);
        }
        button:disabled {
            opacity: 0.5;
            cursor: not-allowed;
            transform: none;
        }
        .output {
            background: #f5f5f5;
            padding: 15px;
            border-radius: 5px;
            margin: 20px 0;
            border-left: 4px solid #667eea;
        }
    </style>
</head>
<body>
    <div class="container">
        <h1>🏠 Zemaraim Database Setup</h1>

        <?php if (!$table_exists): ?>
            <div class="status info">
                <strong>Database Status:</strong> Tables not found. Click the button below to create them.
            </div>

            <form method="POST">
                <input type="hidden" name="setup" value="true">
                <button type="submit" onclick="return confirm('This will create all necessary database tables. Continue?');">Create Database Tables</button>
            </form>

        <?php else: ?>
            <div class="status success">
                <strong>✓ Database Tables Already Exist</strong><br>
                All tables have been initialized. Your database is ready to use.
            </div>

            <?php
            // Display table information
            $tables_query = $conn->query("SHOW TABLES");
            if ($tables_query) {
                echo "<h3>Existing Tables:</h3>";
                echo "<ul>";
                while ($row = $tables_query->fetch_row()) {
                    echo "<li><strong>" . $row[0] . "</strong></li>";
                }
                echo "</ul>";
            }
            ?>

            <div style="margin-top: 30px; text-align: center;">
                <p><strong>You can now:</strong></p>
                <ul style="list-style: none; padding: 0;">
                    <li>✓ <a href="index.php" style="color: #667eea; text-decoration: none;">Go to Home Page</a></li>
                    <li>✓ <a href="register.php" style="color: #667eea; text-decoration: none;">Create Customer Account</a></li>
                    <li>✓ <a href="login.php" style="color: #667eea; text-decoration: none;">Login to Account</a></li>
                </ul>
            </div>
        <?php endif; ?>

        <?php
        // Handle setup request
        if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['setup'])) {
            echo "<div class='output'>";
            echo "<h3>Creating Database Tables...</h3>";

            // Drop existing tables if they exist (careful - this deletes data!)
            // Comment out these lines if you want to preserve existing data

            // Create customers table
            $customers_table = "CREATE TABLE IF NOT EXISTS customers (
                id INT PRIMARY KEY AUTO_INCREMENT,
                firstname VARCHAR(100) NOT NULL,
                lastname VARCHAR(100) NOT NULL,
                email VARCHAR(100) UNIQUE NOT NULL,
                phone VARCHAR(20),
                password VARCHAR(255) NOT NULL,
                role VARCHAR(20) DEFAULT 'customer',
                status VARCHAR(20) DEFAULT 'active',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                INDEX idx_email (email),
                INDEX idx_role (role)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8";
            echo executeQuery($customers_table, "Creating customers table");

            // Create brokers table
            $brokers_table = "CREATE TABLE IF NOT EXISTS brokers (
                id INT PRIMARY KEY AUTO_INCREMENT,
                name VARCHAR(100) NOT NULL,
                email VARCHAR(100) UNIQUE NOT NULL,
                phone VARCHAR(20),
                password VARCHAR(255) NOT NULL,
                company VARCHAR(100),
                license_number VARCHAR(50),
                role VARCHAR(20) DEFAULT 'broker',
                status VARCHAR(20) DEFAULT 'active',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                INDEX idx_email (email),
                INDEX idx_role (role)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8";
            echo executeQuery($brokers_table, "Creating brokers table");

            // Create staff table
            $staff_table = "CREATE TABLE IF NOT EXISTS staff (
                id INT PRIMARY KEY AUTO_INCREMENT,
                name VARCHAR(100) NOT NULL,
                email VARCHAR(100) UNIQUE NOT NULL,
                phone VARCHAR(20),
                password VARCHAR(255) NOT NULL,
                position VARCHAR(100),
                role VARCHAR(20) DEFAULT 'staff',
                status VARCHAR(20) DEFAULT 'active',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                INDEX idx_email (email),
                INDEX idx_role (role)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8";
            echo executeQuery($staff_table, "Creating staff table");

            // Create properties table
            $properties_table = "CREATE TABLE IF NOT EXISTS properties (
                id INT PRIMARY KEY AUTO_INCREMENT,
                title VARCHAR(255) NOT NULL,
                description LONGTEXT,
                price DECIMAL(12, 2) NOT NULL,
                type VARCHAR(50),
                status VARCHAR(50) DEFAULT 'available',
                address VARCHAR(255) NOT NULL,
                city VARCHAR(100),
                state VARCHAR(100),
                zip_code VARCHAR(20),
                bedrooms INT,
                bathrooms INT,
                garage INT,
                area DECIMAL(10, 2),
                broker_id INT,
                image_url VARCHAR(255),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                INDEX idx_broker_id (broker_id),
                INDEX idx_city (city),
                INDEX idx_price (price),
                FOREIGN KEY (broker_id) REFERENCES brokers(id) ON DELETE SET NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8";
            echo executeQuery($properties_table, "Creating properties table");

            // Create newsletter_subscribers table
            $newsletter_table = "CREATE TABLE IF NOT EXISTS newsletter_subscribers (
                id INT PRIMARY KEY AUTO_INCREMENT,
                email VARCHAR(100) UNIQUE NOT NULL,
                subscribed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_email (email)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8";
            echo executeQuery($newsletter_table, "Creating newsletter_subscribers table");

            // Create appointments table
            $appointments_table = "CREATE TABLE IF NOT EXISTS appointments (
                id INT PRIMARY KEY AUTO_INCREMENT,
                customer_id INT NOT NULL,
                property_id INT NOT NULL,
                broker_id INT,
                appointment_date DATETIME NOT NULL,
                message LONGTEXT,
                status VARCHAR(50) DEFAULT 'pending',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
                FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE,
                FOREIGN KEY (broker_id) REFERENCES brokers(id) ON DELETE SET NULL,
                INDEX idx_customer_id (customer_id),
                INDEX idx_property_id (property_id),
                INDEX idx_status (status)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8";
            echo executeQuery($appointments_table, "Creating appointments table");

            // Create reviews table
            $reviews_table = "CREATE TABLE IF NOT EXISTS reviews (
                id INT PRIMARY KEY AUTO_INCREMENT,
                customer_id INT NOT NULL,
                broker_id INT,
                property_id INT,
                rating INT CHECK (rating >= 1 AND rating <= 5),
                comment LONGTEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
                FOREIGN KEY (broker_id) REFERENCES brokers(id) ON DELETE CASCADE,
                FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE,
                INDEX idx_broker_id (broker_id),
                INDEX idx_rating (rating)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8";
            echo executeQuery($reviews_table, "Creating reviews table");

            // Create blog table
            $blog_table = "CREATE TABLE IF NOT EXISTS blog_posts (
                id INT PRIMARY KEY AUTO_INCREMENT,
                title VARCHAR(255) NOT NULL,
                slug VARCHAR(255) UNIQUE NOT NULL,
                content LONGTEXT NOT NULL,
                excerpt VARCHAR(255),
                featured_image VARCHAR(255),
                author_id INT,
                status VARCHAR(50) DEFAULT 'published',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                INDEX idx_slug (slug),
                INDEX idx_author_id (author_id),
                INDEX idx_status (status)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8";
            echo executeQuery($blog_table, "Creating blog_posts table");

            echo "</div>";

            echo "<div class='status success'>";
            echo "<strong>✓ Database Setup Complete!</strong><br>";
            echo "All tables have been created successfully. You can now use the application.";
            echo "</div>";

            // Add JavaScript to refresh page after 3 seconds
            echo "<script>
                setTimeout(function() {
                    location.reload();
                }, 3000);
            </script>";
        }
        ?>

        <div style="margin-top: 40px; text-align: center; color: #999; font-size: 12px; border-top: 1px solid #e0e0e0; padding-top: 20px;">
            <p>Zemaraim Database Setup v1.0</p>
            <p>Database: <strong><?php echo DB_NAME; ?></strong> | Host: <strong><?php echo DB_HOST; ?></strong></p>
        </div>
    </div>
</body>
</html>
