<?php
/**
 * Migration Script - Add Staff Users & Permissions
 * LandSales Pro - Run this to add staff tables to existing database
 *
 * Access via: http://localhost/landsales/database/migrate_staff.php
 */

// Basic config
$host = 'localhost';
$user = 'root';
$pass = '';
$dbname = 'landsales';

echo "<h1>LandSales Pro - Staff Users Migration</h1>";
echo "<pre style='background:#f5f5f5; padding:20px; font-family:monospace;'>";

// Connect to database
$conn = new mysqli($host, $user, $pass, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

echo "Connected to MySQL successfully.\n\n";

// Check if tables already exist
$result = $conn->query("SHOW TABLES LIKE 'staff_users'");
if ($result->num_rows > 0) {
    echo "Staff tables already exist. Checking for updates...\n\n";
} else {
    echo "Creating staff tables...\n\n";

    // Staff Users
    $sql = "CREATE TABLE staff_users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        owner_id INT NOT NULL COMMENT 'The subscriber who owns this staff user',
        email VARCHAR(100) NOT NULL,
        phone VARCHAR(20),
        password VARCHAR(255) NOT NULL,
        full_name VARCHAR(100) NOT NULL,
        status ENUM('active', 'inactive') DEFAULT 'active',
        last_login TIMESTAMP NULL,
        login_count INT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE,
        INDEX idx_owner (owner_id),
        INDEX idx_email (email),
        INDEX idx_status (status),
        UNIQUE KEY idx_owner_email (owner_id, email)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

    if ($conn->query($sql)) {
        echo "  - staff_users table created\n";
    } else {
        echo "  - ERROR creating staff_users: " . $conn->error . "\n";
    }

    // Staff Permissions
    $sql = "CREATE TABLE staff_permissions (
        id INT AUTO_INCREMENT PRIMARY KEY,
        staff_id INT NOT NULL,
        permission VARCHAR(50) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (staff_id) REFERENCES staff_users(id) ON DELETE CASCADE,
        INDEX idx_staff (staff_id),
        UNIQUE KEY idx_staff_permission (staff_id, permission)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

    if ($conn->query($sql)) {
        echo "  - staff_permissions table created\n";
    } else {
        echo "  - ERROR creating staff_permissions: " . $conn->error . "\n";
    }
}

echo "\n";
echo "========================================\n";
echo "MIGRATION COMPLETE!\n";
echo "========================================\n\n";
echo "Staff users and permissions tables are ready.\n\n";
echo "DELETE THIS FILE AFTER MIGRATION FOR SECURITY!\n";
echo "</pre>";

echo "<p style='margin-top:20px;'>";
echo "<a href='../dashboard/' style='padding:10px 20px; background:#667eea; color:white; text-decoration:none; border-radius:5px;'>Go to Dashboard</a>";
echo "</p>";

$conn->close();
?>
