<?php

class BaseController extends Controller
{
    protected function handleDataTable(Request $request, string $baseQuery, array $baseParams, array $columns, ?string $defaultOrder = null): void
    {
        $db = Database::getInstance();
        $draw = (int) $request->get('draw', 1);
        $start = (int) $request->get('start', 0);
        $length = (int) $request->get('length', 25);
        $searchValue = trim($request->get('search')['value'] ?? '');

        // Total count
        $countSql = "SELECT COUNT(*) FROM ({$baseQuery}) AS total_count";
        $totalRecords = (int) $db->fetchColumn($countSql, $baseParams);

        // Search
        $filteredQuery = $baseQuery;
        $filteredParams = $baseParams;
        if ($searchValue !== '') {
            $searchClauses = [];
            foreach ($columns as $col) {
                if (!empty($col['searchable'])) {
                    $searchClauses[] = "{$col['db']} LIKE ?";
                    $filteredParams[] = "%{$searchValue}%";
                }
            }
            if (!empty($searchClauses)) {
                $filteredQuery = "SELECT * FROM ({$baseQuery}) AS sub WHERE (" . implode(' OR ', $searchClauses) . ")";
            }
        }

        $filteredCountSql = "SELECT COUNT(*) FROM ({$filteredQuery}) AS filtered_count";
        $filteredRecords = (int) $db->fetchColumn($filteredCountSql, $filteredParams);

        // Ordering
        $orderCol = (int) ($request->get('order')[0]['column'] ?? 0);
        $orderDir = strtoupper($request->get('order')[0]['dir'] ?? 'DESC') === 'ASC' ? 'ASC' : 'DESC';
        $orderColumn = $columns[$orderCol]['db'] ?? $columns[0]['db'];
        if ($defaultOrder && $searchValue === '') {
            $orderClause = $defaultOrder;
        } else {
            $orderClause = "{$orderColumn} {$orderDir}";
        }

        $dataSql = "{$filteredQuery} ORDER BY {$orderClause} LIMIT " . (int)$length . " OFFSET " . (int)$start;
        $rows = $db->fetchAll($dataSql, $filteredParams);

        $this->json([
            'draw' => $draw,
            'recordsTotal' => $totalRecords,
            'recordsFiltered' => $filteredRecords,
            'data' => $rows,
        ]);
    }

    protected function memberSearchResults(Request $request): void
    {
        $q = trim($request->get('q', ''));
        if (mb_strlen($q) < 2) {
            $this->json(['results' => []]);
            return;
        }
        $db = Database::getInstance();
        $members = $db->fetchAll(
            "SELECT id, membership_number, full_name_en, full_name_ar, primary_phone, photo_path
             FROM members
             WHERE is_deleted = 0 AND (
                 membership_number LIKE ? OR full_name_en LIKE ? OR full_name_ar LIKE ?
                 OR national_id LIKE ? OR primary_phone LIKE ?
             )
             ORDER BY full_name_en LIMIT 20",
            ["%{$q}%", "%{$q}%", "%{$q}%", "%{$q}%", "%{$q}%"]
        );
        $results = [];
        foreach ($members as $m) {
            $results[] = [
                'id' => $m['id'],
                'text' => $m['membership_number'] . ' - ' . ($m['full_name_en'] ?: $m['full_name_ar']),
                'membership_number' => $m['membership_number'],
                'name' => $m['full_name_en'],
                'phone' => $m['primary_phone'],
            ];
        }
        $this->json(['results' => $results]);
    }
}