<?php
require_once '../../config.php';
header('Content-Type: application/json');

$sessionRole = $_SESSION['role'] ?? ($_SESSION['user_role'] ?? '');
if (!isset($_SESSION['user_id']) || $sessionRole !== 'staff') {
    http_response_code(401);
    echo json_encode(['success' => false, 'message' => 'Unauthorized']);
    exit;
}

function ensureAssignmentWorkflowTable(mysqli $mysqli): void
{
    $mysqli->query("
        CREATE TABLE IF NOT EXISTS property_assignment_workflow (
            id INT AUTO_INCREMENT PRIMARY KEY,
            property_id INT NOT NULL UNIQUE,
            broker_id INT NULL,
            workflow_status VARCHAR(40) NOT NULL DEFAULT 'assigned',
            admin_review_status VARCHAR(20) NOT NULL DEFAULT 'pending',
            broker_confirmed_at DATETIME NULL,
            work_started_at DATETIME NULL,
            work_finished_at DATETIME NULL,
            admin_reviewed_by INT NULL,
            admin_reviewed_at DATETIME NULL,
            admin_notes VARCHAR(255) NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_broker_id (broker_id),
            INDEX idx_workflow_status (workflow_status),
            INDEX idx_admin_review_status (admin_review_status)
        )
    ");
}

function getPropertyBrokerReferenceTable(mysqli $mysqli): string
{
    $sql = "
        SELECT LOWER(REFERENCED_TABLE_NAME) AS ref_table
        FROM information_schema.KEY_COLUMN_USAGE
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = 'properties'
          AND COLUMN_NAME = 'broker_id'
          AND REFERENCED_TABLE_NAME IS NOT NULL
        LIMIT 1
    ";
    $res = $mysqli->query($sql);
    if ($res && ($row = $res->fetch_assoc())) {
        $ref = strtolower((string)($row['ref_table'] ?? ''));
        $res->free();
        if ($ref === 'brokers' || $ref === 'users') {
            return $ref;
        }
    }
    return 'users';
}

try {
    ensureAssignmentWorkflowTable($mysqli);
    $refTable = getPropertyBrokerReferenceTable($mysqli);
    $brokers = [];
    if ($refTable === 'brokers') {
        $knownBrokerEmails = [];
        $broker_result = $mysqli->query("SELECT id, name, email FROM brokers ORDER BY name ASC");
        if ($broker_result) {
            while ($row = $broker_result->fetch_assoc()) {
                $emailKey = strtolower(trim((string)($row['email'] ?? '')));
                if ($emailKey !== '') {
                    $knownBrokerEmails[$emailKey] = true;
                }
                $brokers[] = [
                    'id' => 'broker:' . (int)$row['id'],
                    'full_name' => $row['name']
                ];
            }
        }

        // Also expose active broker users not yet present in brokers table.
        $users_result = $mysqli->query("
            SELECT id, full_name, email
            FROM users
            WHERE role = 'broker' AND status = 'active'
            ORDER BY full_name ASC
        ");
        if ($users_result) {
            while ($user = $users_result->fetch_assoc()) {
                $emailKey = strtolower(trim((string)($user['email'] ?? '')));
                if ($emailKey !== '' && isset($knownBrokerEmails[$emailKey])) {
                    continue;
                }
                $brokers[] = [
                    'id' => 'user:' . (int)$user['id'],
                    'full_name' => $user['full_name']
                ];
            }
        }
    } else {
        $broker_result = $mysqli->query("
            SELECT id, full_name
            FROM users
            WHERE role = 'broker' AND status = 'active'
            ORDER BY full_name ASC
        ");
        if ($broker_result) {
            while ($row = $broker_result->fetch_assoc()) {
                $brokers[] = [
                    'id' => 'user:' . (int)$row['id'],
                    'full_name' => $row['full_name']
                ];
            }
        }
    }

    $properties = [];
    $property_query = $refTable === 'brokers'
        ? "
            SELECT
                p.id,
                p.title,
                p.city,
                p.address,
                p.status,
                p.image_url,
                p.broker_id,
                COALESCE(b.name, 'Unassigned') AS broker_name,
                aw.workflow_status,
                aw.admin_review_status,
                aw.work_finished_at,
                aw.updated_at AS workflow_updated_at
            FROM properties p
            LEFT JOIN brokers b ON b.id = p.broker_id
            LEFT JOIN property_assignment_workflow aw ON aw.property_id = p.id
            ORDER BY p.created_at DESC
            LIMIT 300
        "
        : "
            SELECT
                p.id,
                p.title,
                p.city,
                p.address,
                p.status,
                p.image_url,
                p.broker_id,
                COALESCE(u.full_name, 'Unassigned') AS broker_name,
                aw.workflow_status,
                aw.admin_review_status,
                aw.work_finished_at,
                aw.updated_at AS workflow_updated_at
            FROM properties p
            LEFT JOIN users u ON u.id = p.broker_id AND u.role = 'broker'
            LEFT JOIN property_assignment_workflow aw ON aw.property_id = p.id
            ORDER BY p.created_at DESC
            LIMIT 300
        ";
    $property_result = $mysqli->query($property_query);
    if ($property_result) {
        while ($row = $property_result->fetch_assoc()) {
            $brokerId = $row['broker_id'] !== null ? (int)$row['broker_id'] : null;
            $assignmentStatus = 'unassigned';
            if ($brokerId !== null) {
                $assignmentStatus = !empty($row['workflow_status']) ? (string)$row['workflow_status'] : 'assigned';
            }
            $properties[] = [
                'id' => (int)$row['id'],
                'title' => $row['title'],
                'city' => $row['city'],
                'address' => $row['address'],
                'status' => $row['status'],
                'image_url' => $row['image_url'],
                'broker_id' => $brokerId,
                'broker_token' => $brokerId !== null ? (($refTable === 'brokers' ? 'broker:' : 'user:') . $brokerId) : '0',
                'broker_name' => $row['broker_name'],
                'assignment_status' => $assignmentStatus,
                'assignment_review_status' => (string)($row['admin_review_status'] ?? 'pending'),
                'assignment_finished_at' => $row['work_finished_at'] ?? null,
                'assignment_updated_at' => $row['workflow_updated_at'] ?? null
            ];
        }
    }

    echo json_encode([
        'success' => true,
        'broker_reference_table' => $refTable,
        'brokers' => $brokers,
        'properties' => $properties
    ]);
} catch (Exception $e) {
    http_response_code(500);
    echo json_encode([
        'success' => false,
        'message' => 'Failed to load assignment data: ' . $e->getMessage()
    ]);
}
