<?php
/**
 * Resolve broker identity for mixed schemas.
 * Some databases store properties.broker_id -> users.id, others -> brokers.id.
 */

function resolveBrokerContext(mysqli $conn, int $sessionUserId): array
{
    $context = [
        'session_user_id' => $sessionUserId,
        'property_broker_id' => $sessionUserId,
        'reference_table' => 'users',
    ];

    $fk = $conn->query("
        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
    ");
    if ($fk && ($row = $fk->fetch_assoc())) {
        $ref = strtolower((string)($row['ref_table'] ?? 'users'));
        if ($ref === 'users' || $ref === 'brokers') {
            $context['reference_table'] = $ref;
        }
    }

    if ($context['reference_table'] !== 'brokers') {
        return $context;
    }

    $userStmt = $conn->prepare("
        SELECT email
        FROM users
        WHERE id = ? AND role = 'broker'
        LIMIT 1
    ");
    if (!$userStmt) {
        return $context;
    }
    $userStmt->bind_param('i', $sessionUserId);
    $userStmt->execute();
    $userRow = $userStmt->get_result()->fetch_assoc();
    $userStmt->close();

    $email = trim((string)($userRow['email'] ?? ''));
    if ($email === '') {
        return $context;
    }

    $brokerStmt = $conn->prepare("
        SELECT id
        FROM brokers
        WHERE LOWER(email) = LOWER(?)
        LIMIT 1
    ");
    if (!$brokerStmt) {
        return $context;
    }
    $brokerStmt->bind_param('s', $email);
    $brokerStmt->execute();
    $brokerRow = $brokerStmt->get_result()->fetch_assoc();
    $brokerStmt->close();

    if ($brokerRow && isset($brokerRow['id'])) {
        $context['property_broker_id'] = (int)$brokerRow['id'];
    }

    return $context;
}

