cyberhybridhub/server/test/integration/tradable_assets_db_test.dart

@Tags(['integration', 'postgres'])
library;

import 'package:cyberhybridhub_server/alpaca/alpaca_models.dart';
import 'package:cyberhybridhub_server/trading/tradable_assets_db.dart';
import 'package:test/test.dart';

import '../helpers/test_db.dart';

AlpacaAsset _asset(
  String symbol, {
  bool tradable = true,
  bool fractionable = true,
  String status = 'active',
  String? exchange = 'NASDAQ',
  String? name,
}) {
  return AlpacaAsset(
    symbol: symbol,
    assetClass: 'us_equity',
    exchange: exchange,
    name: name ?? '$symbol Inc.',
    status: status,
    tradable: tradable,
    fractionable: fractionable,
    raw: <String, dynamic>{
      'symbol': symbol,
      'class': 'us_equity',
      'exchange': exchange,
      'name': name ?? '$symbol Inc.',
      'status': status,
      'tradable': tradable,
      'fractionable': fractionable,
    },
  );
}

void main() {
  TestDb? testDb;

  setUpAll(() async {
    testDb = await TestDb.open();
  });

  tearDown(() async {
    if (testDb != null) {
      await testDb!.truncateTradingTables();
    }
  });

  tearDownAll(() async {
    await testDb?.close();
  });

  test('upsertAll inserts new symbols with the supplied refreshed_at',
      () async {
    if (testDb == null) {
      markTestSkipped(
        'Set DATABASE_URL or TEST_DATABASE_URL for integration tests',
      );
      return;
    }

    final TradableAssetsDb db = TradableAssetsDb(testDb!.connection);
    final DateTime t0 = DateTime.utc(2026, 5, 26, 10);

    await db.upsertAll(<AlpacaAsset>[_asset('A'), _asset('B'), _asset('C')],
        now: t0);

    final TradableAssetRow? a = await db.getBySymbol('A');
    final TradableAssetRow? b = await db.getBySymbol('B');
    final TradableAssetRow? c = await db.getBySymbol('C');

    expect(a, isNotNull);
    expect(b, isNotNull);
    expect(c, isNotNull);
    expect(a!.tradable, isTrue);
    expect(a.status, 'active');
    expect(a.refreshedAt, t0);
    expect(b!.refreshedAt, t0);
    expect(c!.refreshedAt, t0);
  });

  test(
      'second upsertAll updates B*, leaves C content unchanged but bumps '
      'refreshed_at, inserts D, and marks A inactive without deleting it',
      () async {
    if (testDb == null) {
      markTestSkipped(
        'Set DATABASE_URL or TEST_DATABASE_URL for integration tests',
      );
      return;
    }

    final TradableAssetsDb db = TradableAssetsDb(testDb!.connection);

    final DateTime t0 = DateTime.utc(2026, 5, 26, 10);
    await db.upsertAll(
      <AlpacaAsset>[
        _asset('A'),
        _asset('B', name: 'B Original Inc.', exchange: 'NYSE'),
        _asset('C', exchange: 'NASDAQ'),
      ],
      now: t0,
    );

    final DateTime t1 = DateTime.utc(2026, 5, 27, 10);
    await db.upsertAll(
      <AlpacaAsset>[
        // B with new content (name + exchange).
        _asset('B', name: 'B Renamed Corp.', exchange: 'NASDAQ'),
        // C with identical content as t0 — should bump refreshed_at only.
        _asset('C', exchange: 'NASDAQ'),
        // D is new.
        _asset('D'),
      ],
      now: t1,
    );

    final TradableAssetRow? a = await db.getBySymbol('A');
    final TradableAssetRow? b = await db.getBySymbol('B');
    final TradableAssetRow? c = await db.getBySymbol('C');
    final TradableAssetRow? d = await db.getBySymbol('D');

    // A is preserved as a historical record but flipped to inactive.
    expect(a, isNotNull, reason: 'A must NOT be deleted — history preserved');
    expect(a!.status, 'inactive');
    expect(a.tradable, isFalse);
    // refreshed_at on the inactivated row stays at the original t0 so
    // operators can see "last seen as active" in the audit trail.
    expect(a.refreshedAt, t0);

    // B was updated in place: same row, new content, new refreshed_at.
    expect(b, isNotNull);
    expect(b!.name, 'B Renamed Corp.');
    expect(b.exchange, 'NASDAQ');
    expect(b.refreshedAt, t1);

    // C content unchanged but refreshed_at bumped to t1.
    expect(c, isNotNull);
    expect(c!.exchange, 'NASDAQ');
    expect(c.refreshedAt, t1);

    // D inserted.
    expect(d, isNotNull);
    expect(d!.refreshedAt, t1);
  });

  test('listActiveTradableSymbols filters to active AND tradable', () async {
    if (testDb == null) {
      markTestSkipped(
        'Set DATABASE_URL or TEST_DATABASE_URL for integration tests',
      );
      return;
    }

    final TradableAssetsDb db = TradableAssetsDb(testDb!.connection);
    final DateTime t0 = DateTime.utc(2026, 5, 26, 10);

    await db.upsertAll(
      <AlpacaAsset>[
        _asset('AAA'), // active + tradable
        _asset('BBB'), // active + tradable
        _asset('CCC', tradable: false), // active but not tradable
        _asset('DDD', status: 'inactive'), // inactive
      ],
      now: t0,
    );

    final List<String> symbols = await db.listActiveTradableSymbols();

    expect(symbols.toSet(), <String>{'AAA', 'BBB'});
  });
}