<?php
/**
 * api/explore-search.php — AJAX search for the Explore directory page
 * Returns JSON results grouped by: direct_org, direct_individual, via_submission
 * Cross-references submissions to find users whose data matches even if profile doesn't
 * Version: 1.0.0
 *
 * Changelog:
 * 1.0.0 - Initial release. Grouped search with submission cross-referencing and match reasons.
 */
header('Content-Type: application/json');
session_start();
include __DIR__ . '/../config.php';

$q = trim($_GET['q'] ?? '');
if (mb_strlen($q) < 2) {
    echo json_encode(['results' => []]);
    exit;
}

$like = '%' . $conn->real_escape_string($q) . '%';
$results = [];

// ── Helper: determine match reason ──
function get_match_reason($row, $q) {
    $q_lower = mb_strtolower($q);
    $checks = [
        'org_name'          => 'Name matches',
        'bio'               => 'Bio mentions "' . htmlspecialchars($q) . '"',
        'county'            => 'Located in matching county',
        'designation'       => 'Designation matches',
        'website'           => 'Website matches',
        'email'             => 'Email matches',
        'phone'             => 'Phone matches',
        'personal_email'    => 'Email matches',
        'personal_phone'    => 'Phone matches',
        'company_email'     => 'Email matches',
        'company_phone'     => 'Phone matches',
        'service_chains'    => 'Active in matching service chain',
        'enabling_pillars'  => 'Active in matching enabling pillar',
        'challenges_sector' => 'Works on matching sector challenge',
    ];
    foreach ($checks as $field => $reason) {
        if (!empty($row[$field]) && mb_stripos($row[$field], $q) !== false) {
            return $reason;
        }
    }
    return 'Profile matches';
}

// ── 1. Direct user matches (orgs) — max 6 ──
$sql = "SELECT id, org_name, county, slug, bio, designation, website,
               email, phone, personal_email, personal_phone, company_email, company_phone,
               service_chains, enabling_pillars, challenges_sector, user_type
        FROM users
        WHERE is_verified = 1 AND role != 'admin' AND IFNULL(is_public, 1) = 1
          AND IFNULL(user_type, 'organization') != 'individual'
          AND (org_name LIKE ? OR bio LIKE ? OR email LIKE ? OR phone LIKE ?
               OR personal_email LIKE ? OR personal_phone LIKE ?
               OR company_email LIKE ? OR company_phone LIKE ?
               OR IFNULL(website,'') LIKE ? OR IFNULL(designation,'') LIKE ?
               OR county LIKE ?
               OR IFNULL(service_chains,'') LIKE ?
               OR IFNULL(enabling_pillars,'') LIKE ?
               OR IFNULL(challenges_sector,'') LIKE ?)
        ORDER BY org_name
        LIMIT 6";

$stmt = $conn->prepare($sql);
if ($stmt) {
    $stmt->bind_param("ssssssssssssss", $like,$like,$like,$like,$like,$like,$like,$like,$like,$like,$like,$like,$like,$like);
    $stmt->execute();
    $res = $stmt->get_result();
    $direct_user_ids = [];
    while ($row = $res->fetch_assoc()) {
        $direct_user_ids[] = (int)$row['id'];
        $link = !empty($row['slug'])
            ? '/profile/' . urlencode($row['slug'])
            : '/profile.php?user_id=' . $row['id'];
        $results[] = [
            'type'    => 'direct_org',
            'id'      => (int)$row['id'],
            'title'   => htmlspecialchars($row['org_name'] ?: 'Organization'),
            'sub'     => trim(htmlspecialchars(($row['county'] ?? '') . ($row['designation'] ? ' · ' . $row['designation'] : ''))),
            'reason'  => get_match_reason($row, $q),
            'icon'    => 'fa-building',
            'color'   => 'text-blue-600',
            'url'     => $link,
        ];
    }
    $stmt->close();
}

// ── 2. Direct user matches (individuals) — max 4 ──
$sql2 = "SELECT id, org_name, county, slug, bio, designation, website,
                email, phone, personal_email, personal_phone, company_email, company_phone,
                service_chains, enabling_pillars, challenges_sector, user_type
         FROM users
         WHERE is_verified = 1 AND role != 'admin' AND IFNULL(is_public, 1) = 1
           AND user_type = 'individual'
           AND (org_name LIKE ? OR bio LIKE ? OR email LIKE ? OR phone LIKE ?
                OR personal_email LIKE ? OR personal_phone LIKE ?
                OR company_email LIKE ? OR company_phone LIKE ?
                OR IFNULL(website,'') LIKE ? OR IFNULL(designation,'') LIKE ?
                OR county LIKE ?
                OR IFNULL(service_chains,'') LIKE ?
                OR IFNULL(enabling_pillars,'') LIKE ?
                OR IFNULL(challenges_sector,'') LIKE ?)
         ORDER BY org_name
         LIMIT 4";

$stmt = $conn->prepare($sql2);
if ($stmt) {
    $stmt->bind_param("ssssssssssssss", $like,$like,$like,$like,$like,$like,$like,$like,$like,$like,$like,$like,$like,$like);
    $stmt->execute();
    $res = $stmt->get_result();
    while ($row = $res->fetch_assoc()) {
        $direct_user_ids[] = (int)$row['id'];
        $link = !empty($row['slug'])
            ? '/profile/' . urlencode($row['slug'])
            : '/profile.php?user_id=' . $row['id'];
        $results[] = [
            'type'    => 'direct_individual',
            'id'      => (int)$row['id'],
            'title'   => htmlspecialchars($row['org_name'] ?: 'Individual'),
            'sub'     => trim(htmlspecialchars(($row['county'] ?? '') . ($row['designation'] ? ' · ' . $row['designation'] : ''))),
            'reason'  => get_match_reason($row, $q),
            'icon'    => 'fa-user',
            'color'   => 'text-purple-600',
            'url'     => $link,
        ];
    }
    $stmt->close();
}

// ── 3. Via-submission cross-reference — max 4 ──
// Find users whose VERIFIED submissions match the query, but who weren't already found above
$exclude_ids = !empty($direct_user_ids) ? implode(',', array_map('intval', $direct_user_ids)) : '0';

$sql3 = "SELECT s.user_id, s.title AS sub_title, s.service_chains AS sub_chains,
                u.id, u.org_name, u.county, u.slug, u.user_type
         FROM submissions s
         JOIN users u ON s.user_id = u.id
         WHERE s.is_verified = 1
           AND u.is_verified = 1 AND u.role != 'admin' AND IFNULL(u.is_public, 1) = 1
           AND s.user_id NOT IN ($exclude_ids)
           AND (s.title LIKE ? OR s.story LIKE ? OR s.org_name LIKE ?
                OR s.service_chains LIKE ? OR s.enabling_pillars LIKE ?
                OR s.thematic_categories LIKE ? OR s.county LIKE ?)
         GROUP BY s.user_id
         ORDER BY u.org_name
         LIMIT 4";

$stmt = $conn->prepare($sql3);
if ($stmt) {
    $stmt->bind_param("sssssss", $like,$like,$like,$like,$like,$like,$like);
    $stmt->execute();
    $res = $stmt->get_result();
    while ($row = $res->fetch_assoc()) {
        $link = !empty($row['slug'])
            ? '/profile/' . urlencode($row['slug'])
            : '/profile.php?user_id=' . $row['id'];
        $results[] = [
            'type'    => 'via_submission',
            'id'      => (int)$row['id'],
            'title'   => htmlspecialchars($row['org_name'] ?: 'Organization'),
            'sub'     => htmlspecialchars($row['county'] ?? ''),
            'reason'  => 'Submitted: "' . htmlspecialchars(mb_substr($row['sub_title'], 0, 60)) . '"',
            'icon'    => 'fa-file-lines',
            'color'   => 'text-teal-600',
            'url'     => $link,
        ];
    }
    $stmt->close();
}

echo json_encode(['results' => $results]);
