ClickHouse Backend Rules

// ❌ WRONG: Returns int(0) when value is 0 → type error
final avgTime = await client.scalar<double>(
  'SELECT avg(reading_time) FROM ...',
);  // Returns int(0) when result is 0 → double cast fails!

// ✅ CORRECT: Receive as num and convert with toDouble()
final avgTimeRaw = await client.scalar<num>(
  'SELECT avg(reading_time) FROM ...',
);
final avgTime = avgTimeRaw?.toDouble() ?? 0.0;

// See analytics_service.dart
double _toDouble(Object? value) {
  if (value == null) return 0;
  if (value is double) return value;
  if (value is int) return value.toDouble();
  if (value is num) return value.toDouble();
  return 0;
}

// Usage
final result = await client.scalar<num>('SELECT sum(amount) FROM ...');
final amount = _toDouble(result);

// ❌ WRONG: Direct casting
final rows = await client.select('SELECT value FROM ...');
final value = rows.first['value'] as double;  // Could be int!

// ✅ CORRECT: Use helper function
final rows = await client.select('SELECT value FROM ...');
final value = _toDouble(rows.first['value']);

// ❌ WRONG: Storage and query table mismatch
// EventTrackingService → Stores in ClickHouse `events` table
await eventService.track('screen_view', ...);

// AnalyticsService → Queries from ClickHouse `user_event` table
final result = await client.select('SELECT * FROM user_event');
// Result: Empty data! (different table)

// ✅ CORRECT: Use CDC flow
// PostgreSQL `user_event` → CDC → ClickHouse `user_event`
await UserEvent.db.insertRow(session, event);
final result = await client.select('SELECT * FROM user_event');

// CDC method (recommended - most business events)
final event = UserEvent(
  userId: userId,
  eventName: 'screen_view',
  properties: jsonEncode(properties),
  timestamp: DateTime.now(),
);
await UserEvent.db.insertRow(session, event);

// Direct storage method (high-frequency events only)
await eventService.track('mouse_move', properties: {...});

// ClickHouse treats NULL as 0 or empty string by default
// Explicit NULL handling recommended

// ❌ WRONG: No NULL handling
SELECT avg(value) FROM table  // Unexpected results when NULL is included

// ✅ CORRECT: Explicit NULL handling
SELECT avgIf(value, value IS NOT NULL) FROM table
SELECT coalesce(avg(value), 0) FROM table

// ClickHouse stores in UTC
// KST (Korea Standard Time) conversion required

// ✅ Timezone conversion
SELECT toDateTime(timestamp, 'Asia/Seoul') as local_time FROM ...

// ✅ Date grouping (Korea time-based)
SELECT toDate(timestamp, 'Asia/Seoul') as date, count() FROM ...
GROUP BY date