import { NotFoundException } from '@nestjs/common';
import { DataSource } from 'typeorm';
import { StatsService, timeSeriesTimestampSql, hourBucketSql, maxCreatedAtSql } from './stats.service';
import { Session, SessionStatus } from '../session/entities/session.entity';
import { Message, MessageDirection, MessageStatus } from '../message/entities/message.entity';

// these two analytics queries used SQLite-only strftime(), which 500s on a Postgres
// data DB. The dialect-correct SQL is generated by these pure helpers; the Postgres branch is
// unit-tested here (a live Postgres isn't available in CI), and the SQLite path is exercised
// end-to-end below so the fix can't regress the working backend.
describe('stats SQL dialect helpers', () => {
  it('uses strftime on sqlite (hour + day buckets)', () => {
    expect(timeSeriesTimestampSql('sqlite', 'hour')).toBe(`strftime('%Y-%m-%d %H:00:00', m.createdAt)`);
    expect(timeSeriesTimestampSql('sqlite', 'day')).toBe(`strftime('%Y-%m-%d', m.createdAt)`);
    expect(hourBucketSql('sqlite')).toBe(`CAST(strftime('%H', m.createdAt) AS INTEGER)`);
  });

  it('uses to_char/extract with a quoted createdAt on postgres', () => {
    expect(timeSeriesTimestampSql('postgres', 'hour')).toBe(`to_char(m."createdAt", 'YYYY-MM-DD HH24:00:00')`);
    expect(timeSeriesTimestampSql('postgres', 'day')).toBe(`to_char(m."createdAt", 'YYYY-MM-DD')`);
    expect(hourBucketSql('postgres')).toBe(`CAST(EXTRACT(HOUR FROM m."createdAt") AS INTEGER)`);
  });

  it('formats MAX(createdAt) to an identical text timestamp on both engines (lastActive parity)', () => {
    // Postgres returns a timestamp the driver hydrates to a JS Date (serialized to a different ISO
    // string than SQLite's stored text); pinning both to the same to_char/strftime format keeps the
    // lastActive field stable regardless of the backing database.
    expect(maxCreatedAtSql('sqlite')).toBe(`strftime('%Y-%m-%d %H:%M:%S', MAX(m.createdAt))`);
    expect(maxCreatedAtSql('postgres')).toBe(`to_char(MAX(m."createdAt"), 'YYYY-MM-DD HH24:MI:SS')`);
  });
});

describe('StatsService time-series + hourly activity on SQLite (end-to-end regression)', () => {
  let ds: DataSource;
  let service: StatsService;

  beforeEach(async () => {
    ds = new DataSource({
      type: 'better-sqlite3',
      database: ':memory:',
      entities: [Session, Message],
      synchronize: true,
    });
    await ds.initialize();
    const cache = { setSessionsStats: jest.fn() };
    const config = { get: () => 30000 };
    service = new StatsService(ds.getRepository(Session), ds.getRepository(Message), cache as never, config as never);
  });

  afterEach(async () => {
    await ds.destroy();
  });

  const seedMessage = (over: Partial<Message>) =>
    ds.getRepository(Message).save(
      ds.getRepository(Message).create({
        sessionId: 's1',
        chatId: 'c1',
        from: 'a',
        to: 'b',
        type: 'text',
        direction: MessageDirection.OUTGOING,
        status: MessageStatus.SENT,
        ...over,
      }),
    );

  it('getMessageStats returns a populated time series without throwing', async () => {
    await ds
      .getRepository(Session)
      .save(ds.getRepository(Session).create({ id: 's1', name: 'n', status: SessionStatus.READY, config: {} }));
    await seedMessage({ direction: MessageDirection.OUTGOING });
    await seedMessage({ direction: MessageDirection.INCOMING });

    const stats = await service.getMessageStats('24h');
    expect(Array.isArray(stats.timeSeries)).toBe(true);
    expect(stats.timeSeries.length).toBeGreaterThanOrEqual(1);
    const totals = stats.timeSeries.reduce(
      (acc, p) => ({ sent: acc.sent + p.sent, received: acc.received + p.received }),
      {
        sent: 0,
        received: 0,
      },
    );
    expect(totals.sent).toBe(1);
    expect(totals.received).toBe(1);
    // The hour bucket must be the zero-padded chronological label strftime produces.
    expect(stats.timeSeries[0].timestamp).toMatch(/^\d{4}-\d{2}-\d{2} \d{2}:00:00$/);
  });

  it('getMessageStats topChats surfaces chatName via the MAX aggregate (ignoring null rows)', async () => {
    await ds
      .getRepository(Session)
      .save(ds.getRepository(Session).create({ id: 's1', name: 'n', status: SessionStatus.READY, config: {} }));
    await seedMessage({ chatId: 'alice@c.us', chatName: 'Alice', direction: MessageDirection.INCOMING });
    await seedMessage({ chatId: 'alice@c.us', chatName: undefined, direction: MessageDirection.INCOMING });

    const stats = await service.getMessageStats('24h');
    const chat = stats.topChats.find(c => c.chatId === 'alice@c.us');
    // MAX(m.chatName) across the group returns the non-null name, so a legacy null row can't blank it.
    expect(chat?.chatName).toBe('Alice');
  });

  it('getMessageStats byType excludes content-less system/event rows (no body AND no metadata)', async () => {
    await ds
      .getRepository(Session)
      .save(ds.getRepository(Session).create({ id: 's1', name: 'n', status: SessionStatus.READY, config: {} }));
    await seedMessage({ body: 'hello' });
    // A media message with no caption (body '') but a metadata.media payload must still be counted —
    // only rows empty on BOTH are excluded.
    await seedMessage({ type: 'image', body: '', metadata: { media: { mimetype: 'image/png', data: 'x' } } });
    // The misleading slice: an @lid privacy-user event the engine maps to `unknown` — no content at all.
    await seedMessage({ type: 'unknown', body: '', direction: MessageDirection.INCOMING });

    const stats = await service.getMessageStats('24h');
    expect(stats.byType).toEqual({ text: 1, image: 1 });
  });

  it('time-series query never groups by the bare reserved word `timestamp` (Postgres-safe)', async () => {
    await ds
      .getRepository(Session)
      .save(ds.getRepository(Session).create({ id: 's1', name: 'n', status: SessionStatus.READY, config: {} }));
    await seedMessage({ direction: MessageDirection.OUTGOING });

    // SQLite tolerates `GROUP BY timestamp`, but `timestamp` is a reserved type keyword in Postgres
    // and crashes there ("column m.createdAt must appear in the GROUP BY"). Assert the generated SQL,
    // not the result, so the fix can't silently regress on the backend the test DB doesn't exercise.
    const captured: string[] = [];
    const repo = ds.getRepository(Message);
    const origCreate = repo.createQueryBuilder.bind(repo);
    jest.spyOn(repo, 'createQueryBuilder').mockImplementation((alias?: string) => {
      const qb = origCreate(alias);
      const origGetRawMany = qb.getRawMany.bind(qb);
      jest.spyOn(qb, 'getRawMany').mockImplementation((async () => {
        captured.push(qb.getQuery());
        return origGetRawMany();
      }) as never);
      return qb;
    });

    await service.getMessageStats('24h');

    expect(captured.some(sql => /GROUP BY/i.test(sql))).toBe(true); // sanity: a grouped query was built
    for (const sql of captured) {
      expect(sql).not.toMatch(/GROUP BY\s+timestamp\b/i);
    }
  });

  it('no analytics query groups/orders by a bare reserved word — on the Postgres dialect shape too', async () => {
    await ds
      .getRepository(Session)
      .save(ds.getRepository(Session).create({ id: 's1', name: 'n', status: SessionStatus.READY, config: {} }));
    await seedMessage({ direction: MessageDirection.OUTGOING });

    // Force the Postgres SQL shape and capture the generated SQL WITHOUT executing (SQLite can't run
    // to_char/EXTRACT). groupBy/orderBy arguments are emitted verbatim, so this sweeps every grouped
    // analytics query for a reserved-word alias on the dialect the SQLite test DB can't exercise —
    // the exact #476 blindness class, extended beyond the single time-series query.
    // Shadow the (prototype) getter on this fresh per-test instance so it can't leak to other tests.
    Object.defineProperty(service, 'dataDbType', { get: () => 'postgres', configurable: true });

    const captured: string[] = [];
    const repo = ds.getRepository(Message);
    const origCreate = repo.createQueryBuilder.bind(repo);
    jest.spyOn(repo, 'createQueryBuilder').mockImplementation((alias?: string) => {
      const qb = origCreate(alias);
      jest.spyOn(qb, 'getRawMany').mockImplementation(() => {
        captured.push(qb.getQuery());
        return Promise.resolve([]);
      });
      return qb;
    });

    await service.getMessageStats('24h'); // time-series (bucket) + byType + bySession + topChats
    await service.getSessionStats('s1'); // hourly activity (hour)

    expect(captured.length).toBeGreaterThan(0);
    // A small set of PostgreSQL reserved type/keywords that a naive alias could collide with.
    const PG_RESERVED = ['timestamp', 'user', 'order', 'end', 'all', 'column', 'table'];
    for (const sql of captured) {
      for (const kw of PG_RESERVED) {
        expect(sql).not.toMatch(new RegExp(`GROUP BY\\s+["']?${kw}["']?\\s*(,|$|\\s)`, 'i'));
        expect(sql).not.toMatch(new RegExp(`ORDER BY\\s+["']?${kw}["']?\\s*(,|$|\\s|ASC|DESC)`, 'i'));
      }
    }
  });

  it('no analytics query orders/groups by an unquoted mixed-case alias (Postgres case-folds it → 42703)', async () => {
    await ds
      .getRepository(Session)
      .save(ds.getRepository(Session).create({ id: 's1', name: 'n', status: SessionStatus.READY, config: {} }));
    await seedMessage({ direction: MessageDirection.OUTGOING });

    Object.defineProperty(service, 'dataDbType', { get: () => 'postgres', configurable: true });

    const captured: string[] = [];
    const repo = ds.getRepository(Message);
    const origCreate = repo.createQueryBuilder.bind(repo);
    jest.spyOn(repo, 'createQueryBuilder').mockImplementation((alias?: string) => {
      const qb = origCreate(alias);
      jest.spyOn(qb, 'getRawMany').mockImplementation(() => {
        captured.push(qb.getQuery());
        return Promise.resolve([]);
      });
      return qb;
    });

    await service.getMessageStats('24h'); // time-series + byType + bySession + topChats
    await service.getSessionStats('s1');

    expect(captured.length).toBeGreaterThan(0);
    // Postgres folds an UNQUOTED identifier to lowercase; a mixed-case alias defined with quotes
    // (COUNT(*) AS "messageCount") then no longer matches a bare `ORDER BY messageCount` → it looks for
    // "messagecount" and 42703s. SQLite is case-insensitive so it never surfaced. Flag any bare
    // (unquoted) ORDER BY / GROUP BY term that contains an uppercase letter.
    const offenders: string[] = [];
    for (const sql of captured) {
      for (const clause of sql.match(/\b(?:ORDER|GROUP) BY\s+[^\s,]+/gi) ?? []) {
        const term = clause.replace(/\b(?:ORDER|GROUP) BY\s+/i, '');
        // Only a BARE identifier (no quotes, no dots, no parens) folds — `COUNT(*)`, `to_char(...)`,
        // `"m"."chatId"` are all safe; `messageCount` is the landmine.
        if (/^[A-Za-z_][A-Za-z0-9_]*$/.test(term) && /[A-Z]/.test(term)) offenders.push(`${clause}  ::  ${sql}`);
      }
    }
    expect(offenders).toEqual([]);
  });

  it('getSessionStats returns 24 hourly buckets with the right counts', async () => {
    await ds
      .getRepository(Session)
      .save(ds.getRepository(Session).create({ id: 's1', name: 'n', status: SessionStatus.READY, config: {} }));
    await seedMessage({ direction: MessageDirection.OUTGOING });
    await seedMessage({ direction: MessageDirection.OUTGOING });
    await seedMessage({ direction: MessageDirection.INCOMING });

    const stats = await service.getSessionStats('s1');
    expect(stats.hourlyActivity).toHaveLength(24);
    const totals = stats.hourlyActivity.reduce(
      (acc, h) => ({ sent: acc.sent + h.sent, received: acc.received + h.received }),
      { sent: 0, received: 0 },
    );
    expect(totals.sent).toBe(2);
    expect(totals.received).toBe(1);
  });
});

describe('StatsService aggregate memo (in-process TTL)', () => {
  let ds: DataSource;

  beforeEach(async () => {
    ds = new DataSource({
      type: 'better-sqlite3',
      database: ':memory:',
      entities: [Session, Message],
      synchronize: true,
    });
    await ds.initialize();
    const sessions = ds.getRepository(Session);
    await sessions.save(sessions.create({ id: 's1', name: 'n1', status: SessionStatus.READY, config: {} }));
    await sessions.save(sessions.create({ id: 's2', name: 'n2', status: SessionStatus.READY, config: {} }));
    const messages = ds.getRepository(Message);
    const base = {
      chatId: 'c1',
      from: 'a',
      to: 'b',
      type: 'text',
      direction: MessageDirection.OUTGOING,
      status: MessageStatus.SENT,
    };
    await messages.save(messages.create({ ...base, sessionId: 's1' }));
    await messages.save(messages.create({ ...base, sessionId: 's2' }));
  });

  afterEach(async () => {
    await ds.destroy();
  });

  const makeService = (ttlMs: number) =>
    new StatsService(
      ds.getRepository(Session),
      ds.getRepository(Message),
      { setSessionsStats: jest.fn() } as never,
      { get: () => ttlMs } as never,
    );

  it('serves a repeated identical call from the memo within the TTL (no second DB hit)', async () => {
    const service = makeService(30000);
    const spy = jest.spyOn(ds.getRepository(Message), 'createQueryBuilder');

    await service.getMessageStats('24h');
    const afterFirst = spy.mock.calls.length;
    expect(afterFirst).toBeGreaterThan(0);

    await service.getMessageStats('24h');
    expect(spy.mock.calls.length).toBe(afterFirst);
  });

  it('re-runs the aggregate once the TTL has expired', async () => {
    const service = makeService(30000);
    const spy = jest.spyOn(ds.getRepository(Message), 'createQueryBuilder');
    const nowSpy = jest.spyOn(Date, 'now').mockReturnValue(1_000_000);
    try {
      await service.getMessageStats('24h');
      const afterFirst = spy.mock.calls.length;

      nowSpy.mockReturnValue(1_000_000 + 30_001);
      await service.getMessageStats('24h');
      expect(spy.mock.calls.length).toBeGreaterThan(afterFirst);
    } finally {
      nowSpy.mockRestore();
    }
  });

  it('keys the memo by query shape and by session id', async () => {
    const service = makeService(30000);
    const spy = jest.spyOn(ds.getRepository(Message), 'createQueryBuilder');

    await service.getMessageStats('24h');
    let n = spy.mock.calls.length;
    await service.getMessageStats('7d'); // different period → different key → DB hit
    expect(spy.mock.calls.length).toBeGreaterThan(n);

    n = spy.mock.calls.length;
    await service.getSessionStats('s1');
    await service.getSessionStats('s1'); // memo hit — no new queries
    const afterS1 = spy.mock.calls.length;
    expect(afterS1).toBeGreaterThan(n);

    await service.getSessionStats('s2'); // different session → different key → DB hit
    expect(spy.mock.calls.length).toBeGreaterThan(afterS1);
  });

  it('a 0 TTL disables the memo (every call hits the DB)', async () => {
    const service = makeService(0);
    const spy = jest.spyOn(ds.getRepository(Message), 'createQueryBuilder');

    await service.getMessageStats('24h');
    const n = spy.mock.calls.length;
    await service.getMessageStats('24h');
    expect(spy.mock.calls.length).toBeGreaterThan(n);
  });

  it('does not serve a deleted session from the memo — the stale entry is evicted, not served', async () => {
    const service = makeService(30000);

    const first = await service.getSessionStats('s1'); // populates the 'session:s1' memo entry
    expect(first.session.name).toBe('n1');

    await ds.getRepository(Session).delete('s1');
    // Within the TTL the memo still holds the deleted session's snapshot; serving it would
    // resurrect a deleted session with a 200 instead of the honest 404.
    await expect(service.getSessionStats('s1')).rejects.toThrow(NotFoundException);

    // The stale entry is evicted, not just bypassed: a re-created session recomputes immediately
    // instead of waiting out the TTL with the pre-delete snapshot.
    await ds
      .getRepository(Session)
      .save(
        ds.getRepository(Session).create({ id: 's1', name: 'n1-recreated', status: SessionStatus.READY, config: {} }),
      );
    const recomputed = await service.getSessionStats('s1');
    expect(recomputed.session.name).toBe('n1-recreated');
  });

  it('bounds every cross-session aggregate with a createdAt range predicate the standalone index serves', async () => {
    const service = makeService(30000);
    // Capture the generated SQL the same way the reserved-word regression tests above do.
    const captured: string[] = [];
    const repo = ds.getRepository(Message);
    const origCreate = repo.createQueryBuilder.bind(repo);
    jest.spyOn(repo, 'createQueryBuilder').mockImplementation((alias?: string) => {
      const qb = origCreate(alias);
      const origGetRawMany = qb.getRawMany.bind(qb);
      jest.spyOn(qb, 'getRawMany').mockImplementation((async () => {
        captured.push(qb.getQuery());
        return origGetRawMany();
      }) as never);
      return qb;
    });

    await service.getMessageStats('24h'); // time-series + byType + bySession + topChats

    expect(captured.length).toBeGreaterThan(0);
    // IDX_messages_createdAt serves `createdAt >= ?`; an unbounded GROUP BY here would be the
    // full-history scan this service must no longer run.
    for (const sql of captured) {
      expect(sql).toMatch(/WHERE\s+.*"createdAt"\s*>=/i);
    }
  });
});