<?php
declare(strict_types=1);

/**
 * Shared filter builder for the company list and the CSV export, so both
 * always agree on what "the current result set" means.
 *
 * Returns [whereSql, params, orderSql].
 */
function companies_filter(): array
{
    $where  = [];
    $params = [];

    if ($q = get_param('q')) {
        $where[] = '(c.name LIKE :q OR c.aka LIKE :q OR c.summary LIKE :q)';
        $params['q'] = '%' . str_replace(['%', '_'], ['\%', '\_'], (string) $q) . '%';
    }

    $tags = get_list('tag');
    if ($tags) {
        $names = [];
        foreach ($tags as $i => $slug) {
            $names[] = ':tag' . $i;
            $params['tag' . $i] = $slug;
        }
        $inList = implode(',', $names);

        if (get_param('tag_mode') === 'all') {
            $where[] = "(SELECT COUNT(DISTINCT t.id)
                           FROM company_tags ct JOIN tags t ON t.id = ct.tag_id
                          WHERE ct.company_id = c.id AND t.slug IN ({$inList})) = " . count($tags);
        } else {
            $where[] = "EXISTS (SELECT 1 FROM company_tags ct JOIN tags t ON t.id = ct.tag_id
                                 WHERE ct.company_id = c.id AND t.slug IN ({$inList}))";
        }
    }

    if ($country = get_param('country')) {
        $where[] = 'c.country_code = :country';
        $params['country'] = strtoupper((string) $country);
    }

    $relTypes = ['competitor', 'partner', 'customer', 'distributor', 'supplier', 'watchlist'];
    if (($rel = get_param('rel_type')) && in_array($rel, $relTypes, true)) {
        $where[] = 'EXISTS (SELECT 1 FROM company_relationships r
                             WHERE r.company_id = c.id AND r.rel_type = :rel)';
        $params['rel'] = $rel;
    }

    $statuses = ['active', 'acquired', 'merged', 'inactive', 'unknown'];
    if (($status = get_param('status')) && in_array($status, $statuses, true)) {
        $where[] = 'c.status = :status';
        $params['status'] = $status;
    }

    if (($threatMin = get_int('threat_min')) !== null) {
        $where[] = 'c.threat_level >= :threat_min';
        $params['threat_min'] = $threatMin;
    }

    if (get_param('unrated') === '1') {
        $where[] = 'c.threat_level IS NULL';
    }

    if (get_param('core') === '1') {
        $where[] = 'EXISTS (SELECT 1 FROM company_tags ct JOIN tags t ON t.id = ct.tag_id
                             WHERE ct.company_id = c.id AND t.is_core_competition = 1)';
    }

    if (($assigned = get_int('assigned_to')) !== null) {
        $where[] = 'w.assigned_user_id = :assigned';
        $params['assigned'] = $assigned;
    }

    switch (get_param('queue')) {
        case 'overdue':
            $where[] = 'w.next_due_date IS NOT NULL AND w.next_due_date < CURDATE() AND w.is_paused = 0';
            break;
        case 'due_soon':
            $where[] = 'w.next_due_date BETWEEN CURDATE() AND (CURDATE() + INTERVAL 7 DAY) AND w.is_paused = 0';
            break;
        case 'never_checked':
            $where[] = 'w.last_visit_date IS NULL';
            break;
        case 'stale':
            $where[] = '(w.last_visit_date IS NULL OR w.last_visit_date < (CURDATE() - INTERVAL '
                     . (int) STALE_AFTER_DAYS . ' DAY))';
            break;
    }

    // Sorting is whitelisted; nothing from the query string reaches SQL directly.
    $sortable = [
        'name'       => 'v.name',
        'country'    => 'v.country_name',
        'threat'     => 'v.threat_level',
        'last_visit' => 'v.last_visit_date',
        'next_due'   => 'v.next_due_date',
        'products'   => 'v.product_count',
        'stale'      => 'v.days_since_visit',
    ];
    $sortKey = (string) get_param('sort', 'name');
    $column  = $sortable[$sortKey] ?? 'v.name';
    $dir     = strtolower((string) get_param('dir', 'asc')) === 'desc' ? 'DESC' : 'ASC';

    $orderSql = "ORDER BY ({$column} IS NULL), {$column} {$dir}, v.name ASC";

    return [
        $where ? 'WHERE ' . implode("\n  AND ", $where) : '',
        $params,
        $orderSql,
    ];
}
