<?php
/**
 * Test Data Setup for Staff Dashboard
 * Creates sample properties, inquiries, and appointments
 */
require_once 'config.php';

echo "<!DOCTYPE html>
<html>
<head>
    <title>Test Data Setup</title>
    <style>
        body { font-family: 'Poppins', sans-serif; margin: 40px; background: #f5f7fa; }
        .container { max-width: 900px; margin: 0 auto; background: white; padding: 30px; border-radius: 10px; box-shadow: 0 2px 10px rgba(0,0,0,0.1); }
        h1 { color: #667eea; border-bottom: 3px solid #667eea; padding-bottom: 10px; }
        .success { background: #d4edda; color: #155724; padding: 15px; margin: 10px 0; border-radius: 5px; border-left: 4px solid #28a745; }
        .error { background: #f8d7da; color: #721c24; padding: 15px; margin: 10px 0; border-radius: 5px; border-left: 4px solid #dc3545; }
        .info { background: #d1ecf1; color: #0c5460; padding: 15px; margin: 10px 0; border-radius: 5px; border-left: 4px solid #17a2b8; }
        .warning { background: #fff3cd; color: #856404; padding: 15px; margin: 10px 0; border-radius: 5px; border-left: 4px solid #ffc107; }
        button { background: #667eea; color: white; padding: 12px 24px; border: none; border-radius: 5px; cursor: pointer; font-size: 16px; margin-right: 10px; }
        button:hover { background: #764ba2; }
        .stats { display: grid; grid-template-columns: repeat(auto-fit, minmax(200px, 1fr)); gap: 20px; margin: 20px 0; }
        .stat-card { background: #f8f9fa; padding: 20px; border-radius: 8px; border-left: 4px solid #667eea; }
        .stat-card h3 { margin: 0 0 10px 0; color: #667eea; }
        .stat-card .number { font-size: 28px; font-weight: bold; color: #333; }
        ul { margin: 10px 0; padding-left: 20px; }
        code { background: #f4f4f4; padding: 2px 6px; border-radius: 3px; }
    </style>
</head>
<body>
<div class='container'>
    <h1>🔧 Setup Test Data for Staff Dashboard</h1>";

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['setup'])) {
    echo "<h2>Creating Test Data...</h2>";
    
    try {
        // Ensure response column exists in inquiries
        $result = $conn->query("SHOW COLUMNS FROM inquiries LIKE 'response'");
        if (!$result || $result->num_rows === 0) {
            $conn->query("ALTER TABLE inquiries ADD COLUMN response TEXT AFTER message");
            echo "<div class='info'>ℹ Added 'response' column to inquiries table</div>";
        }
        
        // Ensure appointment_time column exists
        $result = $conn->query("SHOW COLUMNS FROM appointments LIKE 'appointment_time'");
        if (!$result || $result->num_rows === 0) {
            $conn->query("ALTER TABLE appointments ADD COLUMN appointment_time TIME AFTER appointment_date");
            echo "<div class='info'>ℹ Added 'appointment_time' column to appointments table</div>";
        }
        
        // Create test staff user if not exists
        $stmt = $conn->prepare("SELECT id FROM users WHERE email = ? AND role = 'staff' LIMIT 1");
        $staff_email = 'staff@Zemaraim.com';
        $stmt->bind_param('s', $staff_email);
        $stmt->execute();
        $result = $stmt->get_result();
        
        if ($result->num_rows === 0) {
            $hashed_password = password_hash('staff123', PASSWORD_BCRYPT);
            $stmt = $conn->prepare("INSERT INTO users (full_name, email, password, phone, role, status) VALUES (?, ?, ?, ?, ?, ?)");
            $staff_name = 'Mike Staff';
            $phone = '555-0001';
            $role = 'staff';
            $status = 'active';
            $stmt->bind_param('ssssss', $staff_name, $staff_email, $hashed_password, $phone, $role, $status);
            $stmt->execute();
            echo "<div class='success'>✅ Created staff user: staff@Zemaraim.com / password: staff123</div>";
            $stmt->close();
        } else {
            echo "<div class='info'>ℹ Staff user already exists</div>";
        }
        
        // Create test customer users in CUSTOMERS table
        $customers = [
            ['name' => 'John Doe', 'email' => 'john@example.com', 'phone' => '555-1001'],
            ['name' => 'Jane Smith', 'email' => 'jane@example.com', 'phone' => '555-1002'],
            ['name' => 'Bob Wilson', 'email' => 'bob@example.com', 'phone' => '555-1003'],
            ['name' => 'Alice Brown', 'email' => 'alice@example.com', 'phone' => '555-1004'],
        ];
        
        $customer_ids = [];
        foreach ($customers as $customer) {
            $stmt = $conn->prepare("SELECT id FROM customers WHERE email = ? LIMIT 1");
            $stmt->bind_param('s', $customer['email']);
            $stmt->execute();
            $result = $stmt->get_result();
            
            if ($result->num_rows === 0) {
                $hashed_pass = password_hash('password123', PASSWORD_BCRYPT);
                $parts = explode(' ', $customer['name'], 2);
                $firstname = $parts[0];
                $lastname = $parts[1] ?? '';
                $stmt2 = $conn->prepare("INSERT INTO customers (firstname, lastname, email, password, phone, role) VALUES (?, ?, ?, ?, ?, ?)");
                $role = 'customer';
                $stmt2->bind_param('ssssss', $firstname, $lastname, $customer['email'], $hashed_pass, $customer['phone'], $role);
                $stmt2->execute();
                $customer_ids[] = $conn->insert_id;
                echo "<div class='success'>✅ Created customer: {$customer['name']}</div>";
                $stmt2->close();
            } else {
                $row = $result->fetch_assoc();
                $customer_ids[] = $row['id'];
                echo "<div class='info'>ℹ Customer already exists: {$customer['name']}</div>";
            }
            $stmt->close();
        }
        
        // Create test properties
        echo "<h3>Creating Properties...</h3>";
        $properties = [
            ['title' => 'Luxury Penthouse Downtown', 'city' => 'New York', 'price' => 2500000, 'bedrooms' => 4, 'bathrooms' => 3, 'type' => 'Apartment', 'desc' => 'Stunning penthouse with panoramic city views and modern amenities.'],
            ['title' => 'Beachfront Villa', 'city' => 'Miami', 'price' => 1800000, 'bedrooms' => 5, 'bathrooms' => 4, 'type' => 'House', 'desc' => 'Beautiful beachfront property with private beach access.'],
            ['title' => 'Mountain Cabin', 'city' => 'Denver', 'price' => 650000, 'bedrooms' => 3, 'bathrooms' => 2, 'type' => 'House', 'desc' => 'Cozy mountain retreat with scenic views.'],
            ['title' => 'Urban Condo', 'city' => 'Los Angeles', 'price' => 950000, 'bedrooms' => 3, 'bathrooms' => 2, 'type' => 'Apartment', 'desc' => 'Modern condo in vibrant downtown location.'],
            ['title' => 'Country Estate', 'city' => 'Texas', 'price' => 1200000, 'bedrooms' => 6, 'bathrooms' => 4, 'type' => 'House', 'desc' => 'Spacious estate on 10 acres with beautiful gardens.'],
        ];
        
        $property_ids = [];
        foreach ($properties as $prop) {
            $stmt = $conn->prepare("SELECT id FROM properties WHERE title = ? LIMIT 1");
            $stmt->bind_param('s', $prop['title']);
            $stmt->execute();
            $result = $stmt->get_result();
            
            if ($result->num_rows === 0) {
                $stmt2 = $conn->prepare("INSERT INTO properties (title, city, price, bedrooms, bathrooms, type, description, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
                $status = 'active';
                $stmt2->bind_param('ssiiiiss', $prop['title'], $prop['city'], $prop['price'], $prop['bedrooms'], $prop['bathrooms'], $prop['type'], $prop['desc'], $status);
                $stmt2->execute();
                $property_ids[] = $conn->insert_id;
                echo "<div class='success'>✅ Created property: {$prop['title']}</div>";
                $stmt2->close();
            } else {
                $row = $result->fetch_assoc();
                $property_ids[] = $row['id'];
                echo "<div class='info'>ℹ Property already exists: {$prop['title']}</div>";
            }
            $stmt->close();
        }
        
        // Create test inquiries
        echo "<h3>Creating Inquiries...</h3>";
        $inquiries_created = 0;
        $messages = [
            'Is this property still available? When can I schedule a viewing?',
            'What are the property taxes and HOA fees?',
            'Can you provide more information about the neighborhood?',
            'Are there any recent renovations done to this property?',
            'What is the condition of the roof and HVAC system?',
        ];
        
        foreach ($customer_ids as $customer_id) {
            foreach (array_slice($property_ids, 0, 2) as $property_id) {
                $stmt = $conn->prepare("SELECT id FROM inquiries WHERE customer_id = ? AND property_id = ? LIMIT 1");
                $stmt->bind_param('ii', $customer_id, $property_id);
                $stmt->execute();
                $result = $stmt->get_result();
                
                if ($result->num_rows === 0) {
                    $message = $messages[array_rand($messages)];
                    $status = 'pending';
                    $stmt2 = $conn->prepare("INSERT INTO inquiries (customer_id, property_id, message, status, created_at) VALUES (?, ?, ?, ?, NOW())");
                    $stmt2->bind_param('iiss', $customer_id, $property_id, $message, $status);
                    $stmt2->execute();
                    $inquiries_created++;
                    $stmt2->close();
                }
                $stmt->close();
            }
        }
        echo "<div class='success'>✅ Created {$inquiries_created} test inquiries</div>";
        
        // Create test appointments
        echo "<h3>Creating Appointments...</h3>";
        $appointments_created = 0;
        $dates = [
            date('Y-m-d', strtotime('+1 day')),
            date('Y-m-d', strtotime('+3 days')),
            date('Y-m-d', strtotime('+5 days')),
            date('Y-m-d', strtotime('+7 days')),
        ];
        
        $times = ['10:00', '14:00', '15:30', '16:45'];
        
        foreach ($customer_ids as $customer_id) {
            foreach (array_slice($property_ids, 0, 2) as $property_id) {
                $stmt = $conn->prepare("SELECT id FROM appointments WHERE user_id = ? AND property_id = ? AND status = 'pending' LIMIT 1");
                $stmt->bind_param('ii', $customer_id, $property_id);
                $stmt->execute();
                $result = $stmt->get_result();
                
                if ($result->num_rows === 0) {
                    $appointment_date = $dates[array_rand($dates)];
                    $appointment_time = $times[array_rand($times)];
                    $status = 'pending';
                    $message = 'Looking forward to viewing this property. Can we schedule for morning?';
                    
                    $stmt2 = $conn->prepare("INSERT INTO appointments (user_id, property_id, appointment_date, appointment_time, message, status, created_at) VALUES (?, ?, ?, ?, ?, ?, NOW())");
                    $stmt2->bind_param('iissss', $customer_id, $property_id, $appointment_date, $appointment_time, $message, $status);
                    $stmt2->execute();
                    $appointments_created++;
                    $stmt2->close();
                }
                $stmt->close();
            }
        }
        echo "<div class='success'>✅ Created {$appointments_created} test appointments</div>";
        
        // Update API calls to use full_name from users table instead of user_name
        $conn->query("CREATE TABLE IF NOT EXISTS api_cache (
            id INT AUTO_INCREMENT PRIMARY KEY,
            cache_key VARCHAR(100) UNIQUE,
            cache_value JSON,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            expires_at TIMESTAMP NULL
        )");
        
        // Get statistics
        $stats = [];
        
        $stmt = $conn->prepare("SELECT COUNT(*) as count FROM inquiries WHERE status = 'pending'");
        $stmt->execute();
        $result = $stmt->get_result();
        $stats['pending_inquiries'] = $result->fetch_assoc()['count'];
        $stmt->close();
        
        $stmt = $conn->prepare("SELECT COUNT(*) as count FROM appointments WHERE status IN ('pending', 'scheduled') AND appointment_date >= CURDATE()");
        $stmt->execute();
        $result = $stmt->get_result();
        $stats['upcoming_visits'] = $result->fetch_assoc()['count'];
        $stmt->close();
        
        $stmt = $conn->prepare("SELECT COUNT(*) as count FROM properties WHERE status = 'active'");
        $stmt->execute();
        $result = $stmt->get_result();
        $stats['total_properties'] = $result->fetch_assoc()['count'];
        $stmt->close();
        
        $stmt = $conn->prepare("SELECT COUNT(*) as count FROM users WHERE role = 'customer'");
        $stmt->execute();
        $result = $stmt->get_result();
        $stats['total_customers'] = $result->fetch_assoc()['count'];
        $stmt->close();
        
        echo "<h2>✅ Test Data Setup Complete!</h2>";
        echo "<div class='stats'>
            <div class='stat-card'>
                <h3>💬 Pending Inquiries</h3>
                <div class='number'>{$stats['pending_inquiries']}</div>
            </div>
            <div class='stat-card'>
                <h3>📅 Upcoming Visits</h3>
                <div class='number'>{$stats['upcoming_visits']}</div>
            </div>
            <div class='stat-card'>
                <h3>🏠 Properties</h3>
                <div class='number'>{$stats['total_properties']}</div>
            </div>
            <div class='stat-card'>
                <h3>👥 Customers</h3>
                <div class='number'>{$stats['total_customers']}</div>
            </div>
        </div>";
        
        echo "<h2>🔑 Login Credentials:</h2>";
        echo "<div class='info'>
            <strong>Staff Dashboard:</strong><br>
            📧 Email: <code>staff@Zemaraim.com</code><br>
            🔐 Password: <code>staff123</code><br>
        </div>";
        
        echo "<div class='info'>
            <strong>Customer Accounts (for reference):</strong><br>";
        foreach ($customers as $customer) {
            echo "📧 {$customer['email']} - Password: <code>password123</code><br>";
        }
        echo "</div>";
        
        echo "<h2>Next Steps:</h2>";
        echo "<div class='success'>
            <ol>
                <li>Go to <strong><a href='staff-login.php'>Staff Login</a></strong></li>
                <li>Login with: staff@Zemaraim.com / staff123</li>
                <li>Navigate to <strong><a href='dashboards/staff-dashboard.php'>Staff Dashboard</a></strong></li>
                <li>You should now see pending inquiries, appointments, and properties!</li>
            </ol>
        </div>";
        
    } catch (Exception $e) {
        echo "<div class='error'>❌ Error: " . $e->getMessage() . "</div>";
    }
    
} else {
    // Show setup form
    echo "<h2>Create Test Data</h2>";
    echo "<div class='info'>
        This utility will create:
        <ul>
            <li>✅ 1 Staff user account (staff@Zemaraim.com)</li>
            <li>✅ 4 Customer user accounts</li>
            <li>✅ 5 Sample properties</li>
            <li>✅ Multiple test inquiries (pending approval)</li>
            <li>✅ Multiple test appointments (pending schedules)</li>
        </ul>
    </div>";
    
    echo "<div class='warning'>
        <strong>⚠️ Important:</strong>
        <ul>
            <li>This will create <code>Zemaraim_db</code> database automatically</li>
            <li>All required tables will be created</li>
            <li>Existing data will NOT be overwritten</li>
            <li>You can run this multiple times safely</li>
        </ul>
    </div>";
    
    echo "<form method='POST'>
        <button type='submit' name='setup' value='1'>Create Test Data & Users</button>
    </form>";
}

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