<?php

declare(strict_types=1);

$dialect = getenv('POC_DIALECT');
if (!in_array($dialect, ['mysql', 'dm'], true)) exit(2);
$pdo = static function (bool $ai) use ($dialect): PDO {
    $prefix = $ai ? 'POC_AI_' : 'POC_';
    $host = getenv($prefix . 'HOST');
    $port = getenv($prefix . 'PORT');
    $schema = getenv($prefix . 'SCHEMA');
    $dsn = $dialect === 'dm'
        ? "dm:host=$host;port=$port;schema=$schema"
        : "mysql:host=$host;port=$port;dbname=$schema;charset=utf8mb4";
    return new PDO($dsn, getenv($prefix . 'USER'), getenv($prefix . 'PASSWORD'),
        [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
};
$ai = $pdo(true);
$store = $pdo(false);
$activity = $dialect === 'dm' ? 'YOSHOP_ACTIVITY' : 'yoshop_activity';
$registration = $dialect === 'dm' ? 'YOSHOP_ACTIVITY_REGISTER' : 'yoshop_activity_register';
$health = $dialect === 'dm' ? 'YDY_HEALTH_RECORD' : 'ydy_health_record';
$session = $dialect === 'dm' ? 'YDY_MEASUREMENT_SESSION' : 'ydy_measurement_session';
$sql = 'SELECT A.ID,A.START_TIME,A.END_TIME,COUNT(R.ID) AS REG_COUNT FROM '
    . $activity . ' A LEFT JOIN ' . $registration . ' R ON R.ACTIVITY_ID=A.ID'
    . ' WHERE A.START_TIME IS NOT NULL AND A.END_TIME IS NOT NULL'
    . ' GROUP BY A.ID,A.START_TIME,A.END_TIME ORDER BY REG_COUNT DESC,A.ID DESC '
    . ($dialect === 'dm' ? 'FETCH FIRST 10 ROWS ONLY' : 'LIMIT 10');
$candidates = array_map('array_change_key_case', $ai->query($sql)->fetchAll(PDO::FETCH_ASSOC));
// The registration table belongs to the AI connection; enumerate IDs there instead of cross-DB SQL.
$register = $ai->prepare('SELECT USER_ID FROM ' . $registration . ' WHERE ACTIVITY_ID=?');
foreach ($candidates as $row) {
    $id = (int)$row['id'];
    $register->execute([$id]);
    $users = array_map('intval', $register->fetchAll(PDO::FETCH_COLUMN));
    $healthRows = 0;
    if ($users) {
        $marks = implode(',', array_fill(0, count($users), '?'));
        $query = $store->prepare('SELECT COUNT(*) FROM ' . $health . ' R JOIN '
            . $session . ' S ON R.SESSION_ID=S.SESSION_ID'
            . ' WHERE R.AI_USER_ID IN (' . $marks . ')'
            . " AND S.DEVICE_TYPE='jump_rope' AND S.IS_VALID=1 AND R.METRIC_CODE='jump_count'"
            . ' AND R.START_TIME BETWEEN ? AND ?');
        $query->execute(array_merge($users, [(string)$row['start_time'], (string)$row['end_time']]));
        $healthRows = (int)$query->fetchColumn();
    }
    echo json_encode(['dialect' => $dialect, 'activity_id' => $id,
        'registrations' => count($users), 'matching_health_rows' => $healthRows]) . PHP_EOL;
}