<?php

class ReportEngine
{
    public static function execute(
        Database $db,
        array $report,
        array $params,
        int $start,
        int $length,
        string $search,
        int $orderCol,
        string $orderDir
    ): array {
        $baseSql = $report['query_template'];
        if (empty($baseSql)) {
            return ['data' => [], 'recordsTotal' => 0, 'recordsFiltered' => 0];
        }

        $bindings = [];
        $filters = json_decode($report['available_filters'] ?? '[]', true) ?: [];
        $columns = json_decode($report['default_columns'] ?? '[]', true) ?: [];

        $whereClauses = [];
        foreach ($filters as $filter) {
            $name = $filter['name'] ?? '';
            $column = $filter['column'] ?? '';
            $operator = strtoupper($filter['operator'] ?? '=');

            if (!isset($params[$name]) || $params[$name] === '' || $params[$name] === null) {
                continue;
            }

            $val = $params[$name];

            if ($operator === 'LIKE') {
                $searchCols = explode(',', $column);
                $likeParts = [];
                foreach ($searchCols as $sc) {
                    $sc = trim($sc);
                    $likeParts[] = "{$sc} LIKE ?";
                    $bindings[] = "%{$val}%";
                }
                $whereClauses[] = '(' . implode(' OR ', $likeParts) . ')';
            } elseif ($operator === 'IN' && is_array($val)) {
                $placeholders = implode(',', array_fill(0, count($val), '?'));
                $whereClauses[] = "{$column} IN ({$placeholders})";
                $bindings = array_merge($bindings, $val);
            } elseif (in_array($operator, ['=', '!=', '>', '<', '>=', '<='])) {
                $whereClauses[] = "{$column} {$operator} ?";
                $bindings[] = $val;
            }
        }

        if (!empty($search)) {
            $searchParts = [];
            foreach ($columns as $col) {
                if (($col['searchable'] ?? true)) {
                    $key = $col['db'] ?? $col['key'] ?? '';
                    if ($key) {
                        $searchParts[] = "{$key} LIKE ?";
                        $bindings[] = "%{$search}%";
                    }
                }
            }
            if (!empty($searchParts)) {
                $whereClauses[] = '(' . implode(' OR ', $searchParts) . ')';
            }
        }

        $whereStr = '';
        if (!empty($whereClauses)) {
            if (stripos($baseSql, 'WHERE') !== false) {
                $whereStr = ' AND ' . implode(' AND ', $whereClauses);
            } else {
                $whereStr = ' WHERE ' . implode(' AND ', $whereClauses);
            }
        }

        $fullSql = $baseSql . $whereStr;

        $countSql = "SELECT COUNT(*) FROM ({$fullSql}) AS __cnt";
        $totalFiltered = (int) $db->fetchColumn($countSql, $bindings);

        $baseSqlNoFilter = $baseSql;
        $totalAll = (int) $db->fetchColumn("SELECT COUNT(*) FROM ({$baseSqlNoFilter}) AS __cnt_all", []);

        $orderClause = '';
        if (!empty($columns) && isset($columns[$orderCol])) {
            $orderKey = $columns[$orderCol]['db'] ?? $columns[$orderCol]['key'] ?? '';
            if ($orderKey) {
                $orderKey = preg_replace('/[^a-zA-Z0-9_.]/', '', $orderKey);
                $orderClause = " ORDER BY {$orderKey} {$orderDir}";
            }
        }
        if (empty($orderClause) && !empty($report['default_sort'])) {
            $orderClause = " ORDER BY " . $report['default_sort'];
        }

        $dataSql = $fullSql . $orderClause . " LIMIT " . (int)$length . " OFFSET " . (int)$start;
        $rows = $db->fetchAll($dataSql, $bindings);

        return [
            'data'            => $rows,
            'recordsTotal'    => $totalAll,
            'recordsFiltered' => $totalFiltered,
        ];
    }
}