<?php
declare(strict_types=1);
header('Content-Type: application/json; charset=utf-8');

error_reporting(E_ALL & ~E_NOTICE & ~E_WARNING);
ini_set('display_errors','0');

$root = dirname(__DIR__, 2);
require_once $root . '/engine/engine.php';

$pdo = $GLOBALS['pdo'] ?? (function_exists('db') ? db() : null);
if (!$pdo instanceof PDO) {
  http_response_code(500);
  echo json_encode(['success'=>false,'message'=>'DB connection not available']);
  exit;
}

$search = trim((string)($_GET['search'] ?? ''));
$topN = max(1, (int)($_GET['top_n'] ?? 5));

$where = '';
$params = [];
if ($search !== '') {
$where = "WHERE (ticker LIKE :q OR name LIKE :q OR exchange LIKE :q)";
$params[':q'] = "%{$search}%";
}

$sql = "
SELECT id, ticker, name, exchange, best_month, best_winrate,
qualified_months, min_years, threshold_percent, data_json, created_at
FROM saisonale_aktien
{$where}
";
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];

$total = (int)$pdo->query("SELECT COUNT(*) FROM saisonale_aktien")->fetchColumn();
$filtered = count($rows);

// --- Aggregationen vorbereiten ---
$monthExchange = []; // [month][exchange] => count
$exchangeTotals = []; // exchange => total count
$histBins = [
'1' => 0,
'2' => 0,
'3-4' => 0,
'5-6' => 0,
'7-9' => 0,
'10-12' => 0
];
$scatter = [];
$winratesByMonth = array_fill(1, 12, []); // für Boxplots

// Helper: Bin bestimmen
$binOf = function (int $n): string {
if ($n <= 1) return '1';
if ($n == 2) return '2';
if ($n <= 4) return '3-4';
if ($n <= 6) return '5-6';
if ($n <= 9) return '7-9';
return '10-12';
};

foreach ($rows as $r) {
$ex = trim((string)($r['exchange'] ?? ''));
if ($ex === '') $ex = 'UNKNOWN';

$bm = (int)($r['best_month'] ?? 0);
if ($bm >= 1 && $bm <= 12) {
$monthExchange[$bm][$ex] = ($monthExchange[$bm][$ex] ?? 0) + 1;
$exchangeTotals[$ex] = ($exchangeTotals[$ex] ?? 0) + 1;
}

// Histogramm
$qMonths = array_filter(array_map(function ($s) {
$n = (int)trim($s);
return ($n >= 1 && $n <= 12) ? $n : null;
}, explode(',', (string)($r['qualified_months'] ?? ''))));
$histBins[$binOf(count($qMonths))]++;

// Scatter (min_years vs best_winrate)
$minYears = (int)($r['min_years'] ?? 0);
$winrate = (int)($r['best_winrate'] ?? 0);
$thr = isset($r['threshold_percent']) ? (int)$r['threshold_percent'] : null;
$scatter[] = [
'ticker' => (string)$r['ticker'],
'name' => (string)$r['name'],
'exchange' => $ex,
'best_month' => $bm,
'best_winrate' => max(0, min(100, $winrate)),
'min_years' => max(0, $minYears),
'threshold_percent' => $thr
];

// Boxplots: winrates_percent aus data_json
if (!empty($r['data_json'])) {
try {
$dj = json_decode((string)$r['data_json'], true, 512, JSON_THROW_ON_ERROR);
if (isset($dj['winrates_percent']) && is_array($dj['winrates_percent'])) {
foreach ($dj['winrates_percent'] as $m => $val) {
$mi = (int)$m; $vv = is_numeric($val) ? (float)$val : null;
if ($mi >= 1 && $mi <= 12 && $vv !== null && $vv >= 0 && $vv <= 100) {
$winratesByMonth[$mi][] = (float)$vv;
}
}
}
} catch (Throwable $e) {
// ignore JSON errors per row
}
}
}

// Top‑N Exchanges bestimmen
arsort($exchangeTotals); // desc
$topExchanges = array_slice(array_keys($exchangeTotals), 0, $topN);
$topSet = array_flip($topExchanges);
$OTHERS = 'Sonstige';

// byMonthExchange als Liste
$outByMonthExchange = [];
for ($m = 1; $m <= 12; $m++) {
$othersCnt = 0;
if (!empty($monthExchange[$m])) {
foreach ($monthExchange[$m] as $ex => $cnt) {
if (isset($topSet[$ex])) {
$outByMonthExchange[] = ['month' => $m, 'exchange' => $ex, 'count' => (int)$cnt];
} else {
$othersCnt += (int)$cnt;
}
}
}
if ($othersCnt > 0) {
$outByMonthExchange[] = ['month' => $m, 'exchange' => $OTHERS, 'count' => $othersCnt];
}
}

// Boxplot‑Arrays ggf. begrenzen (z. B. 5000 Werte/Monat), um Payload klein zu halten
$MAX_VALUES_PER_MONTH = 5000;
for ($m = 1; $m <= 12; $m++) {
if (count($winratesByMonth[$m]) > $MAX_VALUES_PER_MONTH) {
$winratesByMonth[$m] = array_slice($winratesByMonth[$m], 0, $MAX_VALUES_PER_MONTH);
}
}

echo json_encode([
'success' => true,
'meta' => [ 'total' => (int)$total, 'filtered' => (int)$filtered ],
'topExchanges' => array_values($topExchanges),
'othersKey' => $OTHERS,
'byMonthExchange' => $outByMonthExchange,
'histQualifiedMonths' => array_map(function($k, $v){ return ['bin'=>$k, 'count'=>(int)$v]; }, array_keys($histBins), $histBins),
'scatter' => $scatter,
'winratesByMonth' => $winratesByMonth
], JSON_UNESCAPED_UNICODE);