codeManager.test.ts

import { describe, test, expect, beforeAll, beforeEach, afterAll } from 'bun:test';
import { db, initializeDatabase } from './db';
import { codeManager, normalizeCodeStatus } from './codeManager';
import { users, redeemedCodes, pendingCodes, lootTotals } from './schema/index';

const USER_A = 'discord-user-a';
const USER_B = 'discord-user-b';

beforeAll(() => {
  initializeDatabase();
});

beforeEach(() => {
  // Clear in FK-safe order
  db.delete(pendingCodes).run();
  db.delete(redeemedCodes).run();
  db.delete(lootTotals).run();
  db.delete(users).run();
  // Seed test users required by FK on redeemed_codes.discord_id
  db.insert(users)
    .values([
      { discordId: USER_A, userId: '111', userHash: 'hash-a' },
      { discordId: USER_B, userId: '222', userHash: 'hash-b' },
    ])
    .run();
});

afterAll(async () => {
  // Do not close the database — it may be shared with other test files in the
  // same Bun worker. The in-memory DB is cleaned up when the process exits.
});

// ---------------------------------------------------------------------------
// normalizeCodeStatus
// ---------------------------------------------------------------------------
describe('normalizeCodeStatus', () => {
  test('maps numeric 0 to Success', () => {
    expect(normalizeCodeStatus(0)).toBe('Success');
  });
  test('maps numeric 4 to Code Expired', () => {
    expect(normalizeCodeStatus(4)).toBe('Code Expired');
  });
  test('maps all known numeric codes', () => {
    expect(normalizeCodeStatus(1)).toBe('Already Redeemed');
    expect(normalizeCodeStatus(2)).toBe('Invalid Parameters');
    expect(normalizeCodeStatus(3)).toBe('Not a Valid Code');
    expect(normalizeCodeStatus(5)).toBe('Cannot Redeem');
  });
  test('returns Unknown Status for unmapped numeric code', () => {
    expect(normalizeCodeStatus(99)).toBe('Unknown Status');
  });
  test('passes through canonical string unchanged', () => {
    expect(normalizeCodeStatus('Success')).toBe('Success');
    expect(normalizeCodeStatus('Code Expired')).toBe('Code Expired');
  });
  test('normalizes numeric string "0" to Success', () => {
    expect(normalizeCodeStatus('0')).toBe('Success');
  });
  test('normalizes numeric string "4" to Code Expired', () => {
    expect(normalizeCodeStatus('4')).toBe('Code Expired');
  });
  test('treats partial-numeric strings as canonical (not parsed as int)', () => {
    expect(normalizeCodeStatus('4foo')).toBe('4foo');
    expect(normalizeCodeStatus('0bar')).toBe('0bar');
  });
});

// ---------------------------------------------------------------------------
// addRedeemedCode
// ---------------------------------------------------------------------------
describe('addRedeemedCode', () => {
  test('inserts a new successful redemption', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Success');
    const rows = db.select().from(redeemedCodes).all();
    expect(rows).toHaveLength(1);
    expect(rows[0]!.code).toBe('CODE1234ABCD');
    expect(rows[0]!.status).toBe('Success');
    expect(rows[0]!.discordId).toBe(USER_A);
    expect(rows[0]!.isPublic).toBe(0);
  });

  test('normalizes numeric status 0 to Success', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 0);
    const rows = db.select().from(redeemedCodes).all();
    expect(rows[0]!.status).toBe('Success');
    expect(await codeManager.isCodeRedeemedByUser('CODE1234ABCD', USER_A)).toBe(true);
  });

  test('normalizes numeric string status "0" to Success', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, '0');
    const rows = db.select().from(redeemedCodes).all();
    expect(rows[0]!.status).toBe('Success');
    expect(await codeManager.isCodeRedeemedByUser('CODE1234ABCD', USER_A)).toBe(true);
  });

  test('different users can both redeem the same code', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Success');
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_B, 'Success');
    const rows = db.select().from(redeemedCodes).all();
    expect(rows).toHaveLength(2);
  });

  test('upserts when the same user redeems the same code twice', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Success');
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Code Expired');
    const rows = db.select().from(redeemedCodes).all();
    expect(rows).toHaveLength(1);
    expect(rows[0]!.status).toBe('Code Expired');
  });

  test('propagates isPublic=true to all existing rows for the code', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Success', undefined, false);
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_B, 'Success', undefined, true);
    const rows = db.select().from(redeemedCodes).all();
    expect(rows.every((r) => r.isPublic === 1)).toBe(true);
  });

  test('stores lootDetail as JSON string', async () => {
    const loot = [{ chest_type_id: 1, count: 5 }];
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Success', loot as any);
    const row = db.select().from(redeemedCodes).limit(1).get();
    expect(row?.lootDetail).toBe(JSON.stringify(loot));
  });
});

// ---------------------------------------------------------------------------
// isCodeRedeemed (global — any user)
// ---------------------------------------------------------------------------
describe('isCodeRedeemed', () => {
  test('returns false when code has never been seen', async () => {
    expect(await codeManager.isCodeRedeemed('UNKNOWN1ABCD')).toBe(false);
  });

  test('returns true when any user has a Success row', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Success');
    expect(await codeManager.isCodeRedeemed('CODE1234ABCD')).toBe(true);
  });

  test('returns true when any user has a Code Expired row', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Code Expired');
    expect(await codeManager.isCodeRedeemed('CODE1234ABCD')).toBe(true);
  });
});

// ---------------------------------------------------------------------------
// isCodeRedeemedByUser (per-user)
// ---------------------------------------------------------------------------
describe('isCodeRedeemedByUser', () => {
  test('returns false when code has not been redeemed by the user', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Success');
    expect(await codeManager.isCodeRedeemedByUser('CODE1234ABCD', USER_B)).toBe(false);
  });

  test('returns true when the user has successfully redeemed the code', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Success');
    expect(await codeManager.isCodeRedeemedByUser('CODE1234ABCD', USER_A)).toBe(true);
  });

  test('returns true when the user has a Code Expired row', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Code Expired');
    expect(await codeManager.isCodeRedeemedByUser('CODE1234ABCD', USER_A)).toBe(true);
  });

  test('returns true when the user has an Already Redeemed row', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Already Redeemed');
    expect(await codeManager.isCodeRedeemedByUser('CODE1234ABCD', USER_A)).toBe(true);
  });

  test('Already Redeemed for one user does not affect another user', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Already Redeemed');
    expect(await codeManager.isCodeRedeemedByUser('CODE1234ABCD', USER_B)).toBe(false);
  });

  test('returns false for unknown code', async () => {
    expect(await codeManager.isCodeRedeemedByUser('UNKNOWN1ABCD', USER_A)).toBe(false);
  });
});

// ---------------------------------------------------------------------------
// isCodeExpired
// ---------------------------------------------------------------------------
describe('isCodeExpired', () => {
  test('returns false when code has not been seen', async () => {
    expect(await codeManager.isCodeExpired('UNKNOWN1ABCD')).toBe(false);
  });

  test('returns false for a successful redemption', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Success');
    expect(await codeManager.isCodeExpired('CODE1234ABCD')).toBe(false);
  });

  test('returns true after markCodeAsExpired', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Success');
    await codeManager.markCodeAsExpired('CODE1234ABCD');
    expect(await codeManager.isCodeExpired('CODE1234ABCD')).toBe(true);
  });

  test('returns true for a row inserted with Code Expired status', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Code Expired');
    expect(await codeManager.isCodeExpired('CODE1234ABCD')).toBe(true);
  });
});

// ---------------------------------------------------------------------------
// isCodeSuccessfullyRedeemedByOther
// ---------------------------------------------------------------------------
describe('isCodeSuccessfullyRedeemedByOther', () => {
  test('returns false when only the querying user has redeemed it', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Success');
    expect(await codeManager.isCodeSuccessfullyRedeemedByOther('CODE1234ABCD', USER_A)).toBe(false);
  });

  test('returns true when a different user has a Success row', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Success');
    expect(await codeManager.isCodeSuccessfullyRedeemedByOther('CODE1234ABCD', USER_B)).toBe(true);
  });

  test('returns false when the other user has only an Expired row', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Code Expired');
    expect(await codeManager.isCodeSuccessfullyRedeemedByOther('CODE1234ABCD', USER_B)).toBe(false);
  });
});

// ---------------------------------------------------------------------------
// getAllValidCodes
// ---------------------------------------------------------------------------
describe('getAllValidCodes', () => {
  test('returns empty array when no codes exist', async () => {
    expect(await codeManager.getAllValidCodes()).toEqual([]);
  });

  test('returns distinct codes with at least one Success row', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Success');
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_B, 'Success');
    const codes = await codeManager.getAllValidCodes();
    expect(codes).toHaveLength(1);
    expect(codes[0]).toBe('CODE1234ABCD');
  });

  test('excludes codes that are expired', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Success');
    await codeManager.markCodeAsExpired('CODE1234ABCD');
    expect(await codeManager.getAllValidCodes()).toEqual([]);
  });

  test('returns multiple distinct valid codes', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Success');
    await codeManager.addRedeemedCode('WXYZ5678IJKL', USER_A, 'Success');
    const codes = await codeManager.getAllValidCodes();
    expect(codes).toHaveLength(2);
    expect(codes).toContain('CODE1234ABCD');
    expect(codes).toContain('WXYZ5678IJKL');
  });
});

// ---------------------------------------------------------------------------
// isCodePublic / markCodeAsPublic / markCodeAsPrivate
// ---------------------------------------------------------------------------
describe('public/private code management', () => {
  test('new codes are private by default', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Success');
    expect(await codeManager.isCodePublic('CODE1234ABCD')).toBe(false);
  });

  test('markCodeAsPublic makes the code public', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Success');
    await codeManager.markCodeAsPublic('CODE1234ABCD');
    expect(await codeManager.isCodePublic('CODE1234ABCD')).toBe(true);
  });

  test('markCodeAsPrivate reverts a public code', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Success', undefined, true);
    await codeManager.markCodeAsPrivate('CODE1234ABCD');
    expect(await codeManager.isCodePublic('CODE1234ABCD')).toBe(false);
  });
});

// ---------------------------------------------------------------------------
// pending codes
// ---------------------------------------------------------------------------
describe('pending codes', () => {
  test('addPendingCode / getPendingCodes', async () => {
    await codeManager.addPendingCode('PEND1234ABCD', USER_A);
    const codes = await codeManager.getPendingCodes();
    expect(codes).toContain('PEND1234ABCD');
  });

  test('addPendingCode ignores duplicate code inserts', async () => {
    await codeManager.addPendingCode('PEND1234ABCD', USER_A);
    await codeManager.addPendingCode('PEND1234ABCD', USER_A); // duplicate — should not throw or create two rows
    const codes = await codeManager.getPendingCodes();
    expect(codes.filter((c) => c === 'PEND1234ABCD')).toHaveLength(1);
  });

  test('getPendingCodes filtered by discordId', async () => {
    await codeManager.addPendingCode('PEND1234ABCD', USER_A);
    await codeManager.addPendingCode('PEND5678EFGH', USER_B);
    expect(await codeManager.getPendingCodes(USER_A)).toEqual(['PEND1234ABCD']);
    expect(await codeManager.getPendingCodes(USER_B)).toEqual(['PEND5678EFGH']);
  });

  test('removePendingCode removes a specific code', async () => {
    await codeManager.addPendingCode('PEND1234ABCD', USER_A);
    await codeManager.removePendingCode('PEND1234ABCD');
    expect(await codeManager.getPendingCodes()).toEqual([]);
  });

  test('clearPendingCodes removes all codes', async () => {
    await codeManager.addPendingCode('PEND1234ABCD', USER_A);
    await codeManager.addPendingCode('PEND5678EFGH', USER_B);
    await codeManager.clearPendingCodes();
    expect(await codeManager.getPendingCodes()).toEqual([]);
  });

  test('clearPendingCodes with discordId only removes that user\'s codes', async () => {
    await codeManager.addPendingCode('PEND1234ABCD', USER_A);
    await codeManager.addPendingCode('PEND5678EFGH', USER_B);
    await codeManager.clearPendingCodes(USER_A);
    expect(await codeManager.getPendingCodes()).toEqual(['PEND5678EFGH']);
  });

  test('addNewPendingCodes returns only newly inserted codes', async () => {
    const result = await codeManager.addNewPendingCodes(['CODE1111AAAA', 'CODE2222BBBB']);
    expect(result).toContain('CODE1111AAAA');
    expect(result).toContain('CODE2222BBBB');
  });

  test('addNewPendingCodes skips already-present codes', async () => {
    await codeManager.addPendingCode('CODE1111AAAA');
    const result = await codeManager.addNewPendingCodes(['CODE1111AAAA', 'CODE2222BBBB']);
    expect(result).not.toContain('CODE1111AAAA');
    expect(result).toContain('CODE2222BBBB');
  });

  test('addNewPendingCodes returns empty array for empty input', async () => {
    const result = await codeManager.addNewPendingCodes([]);
    expect(result).toEqual([]);
  });
});

// ---------------------------------------------------------------------------
// getRedeemedCodeDetails
// ---------------------------------------------------------------------------
describe('getRedeemedCodeDetails', () => {
  test('returns codes for the specified user in descending order', async () => {
    await codeManager.addRedeemedCode('CODE1111AAAA', USER_A, 'Success');
    await codeManager.addRedeemedCode('CODE2222BBBB', USER_A, 'Success');
    await codeManager.addRedeemedCode('CODE3333CCCC', USER_B, 'Success');
    const details = await codeManager.getRedeemedCodeDetails(USER_A);
    expect(details).toHaveLength(2);
    expect(details.every((r) => r.discordId === USER_A)).toBe(true);
  });

  test('respects the limit parameter', async () => {
    for (let i = 1; i <= 7; i++) {
      await codeManager.addRedeemedCode(`CODE${String(i).padStart(4, '0')}ZZZZ`, USER_A, 'Success');
    }
    const details = await codeManager.getRedeemedCodeDetails(USER_A, 3);
    expect(details).toHaveLength(3);
  });

  test('respects the offset parameter for page 2', async () => {
    // Insert 7 codes — SQLite preserves insertion order for equal timestamps,
    // but we want deterministic ordering so we verify via set membership.
    const allCodes = ['AAAAAAAAAAAA', 'BBBBBBBBBBBB', 'CCCCCCCCCCCC', 'DDDDDDDDDDDD', 'EEEEEEEEEEEE', 'FFFFFFFFFFFF', 'GGGGGGGGGGGG'];
    for (const code of allCodes) {
      await codeManager.addRedeemedCode(code, USER_A, 'Success');
    }
    const page1 = await codeManager.getRedeemedCodeDetails(USER_A, 5, 0);
    const page2 = await codeManager.getRedeemedCodeDetails(USER_A, 5, 5);
    expect(page1).toHaveLength(5);
    expect(page2).toHaveLength(2);
    // Pages must not overlap
    const page1Codes = new Set(page1.map((r) => r.code));
    for (const row of page2) {
      expect(page1Codes.has(row.code)).toBe(false);
    }
  });

  test('returns empty array when offset exceeds total', async () => {
    await codeManager.addRedeemedCode('CODE1111AAAA', USER_A, 'Success');
    const details = await codeManager.getRedeemedCodeDetails(USER_A, 5, 100);
    expect(details).toHaveLength(0);
  });
});

// ---------------------------------------------------------------------------
// getRedeemedCodeCount
// ---------------------------------------------------------------------------
describe('getRedeemedCodeCount', () => {
  test('returns 0 when user has no codes', async () => {
    expect(await codeManager.getRedeemedCodeCount(USER_A)).toBe(0);
  });

  test('returns correct count for a single user', async () => {
    await codeManager.addRedeemedCode('CODE1111AAAA', USER_A, 'Success');
    await codeManager.addRedeemedCode('CODE2222BBBB', USER_A, 'Code Expired');
    expect(await codeManager.getRedeemedCodeCount(USER_A)).toBe(2);
  });

  test('does not count codes belonging to another user', async () => {
    await codeManager.addRedeemedCode('CODE1111AAAA', USER_A, 'Success');
    await codeManager.addRedeemedCode('CODE2222BBBB', USER_B, 'Success');
    expect(await codeManager.getRedeemedCodeCount(USER_A)).toBe(1);
  });

  test('counts all statuses (Success, Code Expired, etc.)', async () => {
    await codeManager.addRedeemedCode('CODE1111AAAA', USER_A, 'Success');
    await codeManager.addRedeemedCode('CODE2222BBBB', USER_A, 'Code Expired');
    await codeManager.addRedeemedCode('CODE3333CCCC', USER_A, 'Already Redeemed');
    expect(await codeManager.getRedeemedCodeCount(USER_A)).toBe(3);
  });
});

// ---------------------------------------------------------------------------
// deleteUserRedeemedCodes
// ---------------------------------------------------------------------------
describe('deleteUserRedeemedCodes', () => {
  test('returns 0 and does nothing when user has no codes', async () => {
    const count = await codeManager.deleteUserRedeemedCodes(USER_A);
    expect(count).toBe(0);
    expect(db.select().from(redeemedCodes).all()).toHaveLength(0);
  });

  test('deletes all redeemed code rows for the user and returns the count', async () => {
    await codeManager.addRedeemedCode('CODE1111AAAA', USER_A, 'Success');
    await codeManager.addRedeemedCode('CODE2222BBBB', USER_A, 'Code Expired');
    const count = await codeManager.deleteUserRedeemedCodes(USER_A);
    expect(count).toBe(2);
    const remaining = db.select().from(redeemedCodes).all();
    expect(remaining).toHaveLength(0);
  });

  test('only deletes rows belonging to the specified user', async () => {
    await codeManager.addRedeemedCode('CODE1111AAAA', USER_A, 'Success');
    await codeManager.addRedeemedCode('CODE2222BBBB', USER_B, 'Success');
    const count = await codeManager.deleteUserRedeemedCodes(USER_A);
    expect(count).toBe(1);
    const remaining = db.select().from(redeemedCodes).all();
    expect(remaining).toHaveLength(1);
    expect(remaining[0]!.discordId).toBe(USER_B);
  });

  test('does not affect the other user\'s records when both have redeemed the same code', async () => {
    await codeManager.addRedeemedCode('CODE1111AAAA', USER_A, 'Success');
    await codeManager.addRedeemedCode('CODE1111AAAA', USER_B, 'Success');
    await codeManager.deleteUserRedeemedCodes(USER_A);
    const remaining = db.select().from(redeemedCodes).all();
    expect(remaining).toHaveLength(1);
    expect(remaining[0]!.discordId).toBe(USER_B);
  });
});

// ---------------------------------------------------------------------------
// getRedeemedCodesFromList
// ---------------------------------------------------------------------------
describe('getRedeemedCodesFromList', () => {
  test('returns empty set for empty codes list', async () => {
    const result = await codeManager.getRedeemedCodesFromList([]);
    expect(result.size).toBe(0);
  });
  test('returns empty set when no codes have been redeemed', async () => {
    const result = await codeManager.getRedeemedCodesFromList(['CODE1234ABCD']);
    expect(result.size).toBe(0);
  });
  test('returns codes with Success status', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Success');
    const result = await codeManager.getRedeemedCodesFromList(['CODE1234ABCD', 'CODE1111AAAA']);
    expect(result.has('CODE1234ABCD')).toBe(true);
    expect(result.has('CODE1111AAAA')).toBe(false);
  });
  test('returns codes with Code Expired status', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Code Expired');
    const result = await codeManager.getRedeemedCodesFromList(['CODE1234ABCD']);
    expect(result.has('CODE1234ABCD')).toBe(true);
  });
  test('excludes non-qualifying statuses like Invalid Parameters', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Invalid Parameters');
    const result = await codeManager.getRedeemedCodesFromList(['CODE1234ABCD']);
    expect(result.has('CODE1234ABCD')).toBe(false);
  });
  test('chunks queries when codes list exceeds 997 (SQLite variable budget)', async () => {
    // Insert 998 codes — spans 2 chunks (chunk size = 997).
    const codes = Array.from({ length: 998 }, (_, i) => `CHUNK${String(i).padStart(4, '0')}AAAA`);
    for (const code of codes) {
      await codeManager.addRedeemedCode(code, USER_A, 'Success');
    }
    const result = await codeManager.getRedeemedCodesFromList(codes);
    expect(result.size).toBe(998);
    for (const code of codes) expect(result.has(code)).toBe(true);
  });
});

// ---------------------------------------------------------------------------
// addNewPendingCodes chunking
// ---------------------------------------------------------------------------
describe('addNewPendingCodes chunking', () => {
  test('chunks inserts when codes list exceeds 499 (2 params per row)', async () => {
    // 500 codes — spans 2 chunks (chunk size = 499).
    const codes = Array.from({ length: 500 }, (_, i) => `PEND${String(i).padStart(4, '0')}AAAA`);
    const inserted = await codeManager.addNewPendingCodes(codes);
    expect(inserted).toHaveLength(500);
    // Second call skips already-present codes across both chunks.
    const duplicate = await codeManager.addNewPendingCodes(codes);
    expect(duplicate).toHaveLength(0);
  });
});

// ---------------------------------------------------------------------------
// getRedeemedCodesByUsers
// ---------------------------------------------------------------------------
describe('getRedeemedCodesByUsers', () => {
  test('returns empty map when codes list is empty', async () => {
    const result = await codeManager.getRedeemedCodesByUsers([], [USER_A]);
    expect(result.size).toBe(0);
  });

  test('returns empty map when discordIds list is empty', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Success');
    const result = await codeManager.getRedeemedCodesByUsers(['CODE1234ABCD'], []);
    expect(result.size).toBe(0);
  });

  test('returns redeemed codes per user for Success status', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Success');
    const result = await codeManager.getRedeemedCodesByUsers(['CODE1234ABCD'], [USER_A, USER_B]);
    expect(result.get(USER_A)?.has('CODE1234ABCD')).toBe(true);
    expect(result.has(USER_B)).toBe(false);
  });

  test('includes Already Redeemed and Code Expired statuses', async () => {
    await codeManager.addRedeemedCode('CODE1111AAAA', USER_A, 'Already Redeemed');
    await codeManager.addRedeemedCode('CODE2222BBBB', USER_B, 'Code Expired');
    const result = await codeManager.getRedeemedCodesByUsers(
      ['CODE1111AAAA', 'CODE2222BBBB'],
      [USER_A, USER_B]
    );
    expect(result.get(USER_A)?.has('CODE1111AAAA')).toBe(true);
    expect(result.get(USER_B)?.has('CODE2222BBBB')).toBe(true);
  });

  test('excludes non-qualifying statuses like Invalid Parameters', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Invalid Parameters');
    const result = await codeManager.getRedeemedCodesByUsers(['CODE1234ABCD'], [USER_A]);
    expect(result.has(USER_A)).toBe(false);
  });

  test('only returns codes that are in the requested codes list', async () => {
    await codeManager.addRedeemedCode('CODE1111AAAA', USER_A, 'Success');
    await codeManager.addRedeemedCode('CODE2222BBBB', USER_A, 'Success');
    const result = await codeManager.getRedeemedCodesByUsers(['CODE1111AAAA'], [USER_A]);
    expect(result.get(USER_A)?.has('CODE1111AAAA')).toBe(true);
    expect(result.get(USER_A)?.has('CODE2222BBBB')).toBe(false);
  });

  test('handles multiple codes per user correctly', async () => {
    await codeManager.addRedeemedCode('CODE1111AAAA', USER_A, 'Success');
    await codeManager.addRedeemedCode('CODE2222BBBB', USER_A, 'Already Redeemed');
    const result = await codeManager.getRedeemedCodesByUsers(
      ['CODE1111AAAA', 'CODE2222BBBB'],
      [USER_A]
    );
    expect(result.get(USER_A)?.has('CODE1111AAAA')).toBe(true);
    expect(result.get(USER_A)?.has('CODE2222BBBB')).toBe(true);
  });

  test('throws when codes array exceeds the SQLite parameter limit', async () => {
    const codes = Array.from({ length: 996 }, (_, i) => `CODE${String(i).padStart(8, '0')}`);
    await expect(codeManager.getRedeemedCodesByUsers(codes, [USER_A])).rejects.toThrow(
      'Too many codes provided to getRedeemedCodesByUsers'
    );
  });

  test('chunks large discordIds lists to stay within SQLite param limit', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Success');
    // With 1 code: chunkSize = 999 - 1 - 3 = 995. 996 ids forces a second chunk iteration.
    const manyIds = [USER_A, ...Array.from({ length: 995 }, (_, i) => `fake-user-${i}`)];
    const result = await codeManager.getRedeemedCodesByUsers(['CODE1234ABCD'], manyIds);
    expect(result.get(USER_A)?.has('CODE1234ABCD')).toBe(true);
  });
});

// ---------------------------------------------------------------------------
// getRedeemedCodes (code-only list, up to 100)
// ---------------------------------------------------------------------------
describe('getRedeemedCodes', () => {
  test('returns empty array when user has no codes', async () => {
    expect(await codeManager.getRedeemedCodes(USER_A)).toEqual([]);
  });

  test('returns codes for the specified user', async () => {
    await codeManager.addRedeemedCode('CODE1111AAAA', USER_A, 'Success');
    await codeManager.addRedeemedCode('CODE2222BBBB', USER_A, 'Code Expired');
    const codes = await codeManager.getRedeemedCodes(USER_A);
    expect(codes).toHaveLength(2);
    expect(codes).toContain('CODE1111AAAA');
    expect(codes).toContain('CODE2222BBBB');
  });

  test('does not return codes belonging to another user', async () => {
    await codeManager.addRedeemedCode('CODE1111AAAA', USER_A, 'Success');
    await codeManager.addRedeemedCode('CODE2222BBBB', USER_B, 'Success');
    expect(await codeManager.getRedeemedCodes(USER_A)).toEqual(['CODE1111AAAA']);
  });
});

// ---------------------------------------------------------------------------
// getSuccessfulRedeemCount
// ---------------------------------------------------------------------------
describe('getSuccessfulRedeemCount', () => {
  test('returns 0 when no one has redeemed the code', async () => {
    expect(await codeManager.getSuccessfulRedeemCount('UNKNOWN1ABCD')).toBe(0);
  });

  test('returns 1 when one user has a Success row', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Success');
    expect(await codeManager.getSuccessfulRedeemCount('CODE1234ABCD')).toBe(1);
  });

  test('returns 2 when two users have Success rows for the same code', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Success');
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_B, 'Success');
    expect(await codeManager.getSuccessfulRedeemCount('CODE1234ABCD')).toBe(2);
  });

  test('does not count Code Expired rows', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Code Expired');
    expect(await codeManager.getSuccessfulRedeemCount('CODE1234ABCD')).toBe(0);
  });
});

// ---------------------------------------------------------------------------
// getPublicUnexpiredCodes
// ---------------------------------------------------------------------------
describe('getPublicUnexpiredCodes', () => {
  test('returns empty array when no public codes exist', async () => {
    expect(await codeManager.getPublicUnexpiredCodes()).toEqual([]);
  });

  test('returns public Success codes', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Success', undefined, true);
    const rows = await codeManager.getPublicUnexpiredCodes();
    expect(rows).toHaveLength(1);
    expect(rows[0]!.code).toBe('CODE1234ABCD');
  });

  test('does not return private codes', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Success', undefined, false);
    expect(await codeManager.getPublicUnexpiredCodes()).toEqual([]);
  });

  test('does not return expired public codes', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Success', undefined, true);
    await codeManager.markCodeAsExpired('CODE1234ABCD');
    expect(await codeManager.getPublicUnexpiredCodes()).toEqual([]);
  });

  test('returns multiple public unexpired codes', async () => {
    await codeManager.addRedeemedCode('CODE1111AAAA', USER_A, 'Success', undefined, true);
    await codeManager.addRedeemedCode('CODE2222BBBB', USER_B, 'Success', undefined, true);
    const rows = await codeManager.getPublicUnexpiredCodes();
    expect(rows).toHaveLength(2);
  });

  test('returns one row per code when multiple users redeemed the same public code', async () => {
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_A, 'Success', undefined, true);
    await codeManager.addRedeemedCode('CODE1234ABCD', USER_B, 'Success', undefined, true);
    const rows = await codeManager.getPublicUnexpiredCodes();
    expect(rows).toHaveLength(1);
    expect(rows[0]!.code).toBe('CODE1234ABCD');
  });
});

// ---------------------------------------------------------------------------
// getServerCodeStats
// ---------------------------------------------------------------------------
describe('getServerCodeStats', () => {
  test('returns zeros when no codes exist', async () => {
    const stats = await codeManager.getServerCodeStats();
    expect(stats.totalCodes).toBe(0);
    expect(stats.totalRedemptions).toBe(0);
  });

  test('counts distinct codes and total redemptions across users', async () => {
    await codeManager.addRedeemedCode('CODE1111AAAA', USER_A, 'Success');
    await codeManager.addRedeemedCode('CODE1111AAAA', USER_B, 'Success'); // same code, 2nd user
    await codeManager.addRedeemedCode('CODE2222BBBB', USER_A, 'Success');
    const stats = await codeManager.getServerCodeStats();
    expect(stats.totalCodes).toBe(2);       // 2 distinct codes
    expect(stats.totalRedemptions).toBe(3); // 3 successful rows
  });

  test('does not count non-Success statuses', async () => {
    await codeManager.addRedeemedCode('CODE1111AAAA', USER_A, 'Code Expired');
    await codeManager.addRedeemedCode('CODE2222BBBB', USER_A, 'Already Redeemed');
    const stats = await codeManager.getServerCodeStats();
    expect(stats.totalCodes).toBe(0);
    expect(stats.totalRedemptions).toBe(0);
  });

  test('only counts Success rows when mixed statuses exist for a code', async () => {
    await codeManager.addRedeemedCode('CODE1111AAAA', USER_A, 'Success');
    await codeManager.addRedeemedCode('CODE1111AAAA', USER_B, 'Code Expired');
    const stats = await codeManager.getServerCodeStats();
    expect(stats.totalCodes).toBe(1);
    expect(stats.totalRedemptions).toBe(1);
  });
});

// ---------------------------------------------------------------------------
// getAggregateLoot
// ---------------------------------------------------------------------------
describe('getAggregateLoot', () => {
  test('returns empty summary when no redeemed codes exist', async () => {
    const loot = await codeManager.getAggregateLoot();
    expect(loot.chests).toEqual({});
    expect(loot.items).toEqual({});
  });

  test('returns empty summary for a user with no codes', async () => {
    const loot = await codeManager.getAggregateLoot(USER_A);
    expect(loot.chests).toEqual({});
    expect(loot.items).toEqual({});
  });

  test('aggregates chest loot from a single redemption', async () => {
    const lootData = [{ chest_type_id: 1, count: 5 }];
    await codeManager.addRedeemedCode('CODE1111AAAA', USER_A, 'Success', lootData as any);
    const loot = await codeManager.getAggregateLoot(USER_A);
    expect(loot.chests['Silver Chest']).toBe(5);
  });

  test('aggregates named item loot', async () => {
    const lootData = [{ loot_item: 'ruby_coins', count: 100 }];
    await codeManager.addRedeemedCode('CODE1111AAAA', USER_A, 'Success', lootData as any);
    const loot = await codeManager.getAggregateLoot(USER_A);
    expect(loot.items['ruby coins']).toBe(100);
  });

  test('sums chest loot across multiple codes', async () => {
    const loot1 = [{ chest_type_id: 1, count: 5 }];
    const loot2 = [{ chest_type_id: 1, count: 3 }];
    await codeManager.addRedeemedCode('CODE1111AAAA', USER_A, 'Success', loot1 as any);
    await codeManager.addRedeemedCode('CODE2222BBBB', USER_A, 'Success', loot2 as any);
    const loot = await codeManager.getAggregateLoot(USER_A);
    expect(loot.chests['Silver Chest']).toBe(8);
  });

  test('sums across multiple chest types in one code', async () => {
    const lootData = [
      { chest_type_id: 1, count: 5 },
      { chest_type_id: 2, count: 2 },
    ];
    await codeManager.addRedeemedCode('CODE1111AAAA', USER_A, 'Success', lootData as any);
    const loot = await codeManager.getAggregateLoot(USER_A);
    expect(loot.chests['Silver Chest']).toBe(5);
    expect(loot.chests['Gold Chest']).toBe(2);
  });

  test('server-wide aggregate includes all users', async () => {
    const lootA = [{ chest_type_id: 1, count: 5 }];
    const lootB = [{ chest_type_id: 1, count: 3 }];
    await codeManager.addRedeemedCode('CODE1111AAAA', USER_A, 'Success', lootA as any);
    await codeManager.addRedeemedCode('CODE2222BBBB', USER_B, 'Success', lootB as any);
    const loot = await codeManager.getAggregateLoot();
    expect(loot.chests['Silver Chest']).toBe(8);
  });

  test('user-scoped aggregate excludes other users\' loot', async () => {
    const lootA = [{ chest_type_id: 1, count: 5 }];
    const lootB = [{ chest_type_id: 2, count: 3 }];
    await codeManager.addRedeemedCode('CODE1111AAAA', USER_A, 'Success', lootA as any);
    await codeManager.addRedeemedCode('CODE2222BBBB', USER_B, 'Success', lootB as any);
    const loot = await codeManager.getAggregateLoot(USER_A);
    expect(loot.chests['Silver Chest']).toBe(5);
    expect(loot.chests['Gold Chest']).toBeUndefined();
  });

  test('skips non-Success rows', async () => {
    const lootData = [{ chest_type_id: 1, count: 5 }];
    await codeManager.addRedeemedCode('CODE1111AAAA', USER_A, 'Code Expired', lootData as any);
    const loot = await codeManager.getAggregateLoot(USER_A);
    expect(loot.chests).toEqual({});
  });

  test('skips codes with null loot detail', async () => {
    await codeManager.addRedeemedCode('CODE1111AAAA', USER_A, 'Success');
    const loot = await codeManager.getAggregateLoot(USER_A);
    expect(loot.chests).toEqual({});
    expect(loot.items).toEqual({});
  });

  test('uses "Chest N" fallback for unknown chest type IDs', async () => {
    const lootData = [{ chest_type_id: 9999, count: 2 }];
    await codeManager.addRedeemedCode('CODE1111AAAA', USER_A, 'Success', lootData as any);
    const loot = await codeManager.getAggregateLoot(USER_A);
    expect(loot.chests['Chest 9999']).toBe(2);
  });

  test('skips items with missing or non-numeric count', async () => {
    const lootData = [
      { chest_type_id: 1, count: undefined },
      { chest_type_id: 2, count: 'bad' },
      { chest_type_id: 230, count: 3 },
    ];
    await codeManager.addRedeemedCode('CODE1111AAAA', USER_A, 'Success', lootData as any);
    const loot = await codeManager.getAggregateLoot(USER_A);
    expect(loot.chests['Silver Chest']).toBeUndefined();
    expect(loot.chests['Gold Chest']).toBeUndefined();
    expect(loot.chests['Modron Chest']).toBe(3);
  });

  test('skips items with zero or negative count', async () => {
    const lootData = [
      { chest_type_id: 1, count: 0 },
      { chest_type_id: 2, count: -1 },
      { chest_type_id: 230, count: 1 },
    ];
    await codeManager.addRedeemedCode('CODE1111AAAA', USER_A, 'Success', lootData as any);
    const loot = await codeManager.getAggregateLoot(USER_A);
    expect(loot.chests['Silver Chest']).toBeUndefined();
    expect(loot.chests['Gold Chest']).toBeUndefined();
    expect(loot.chests['Modron Chest']).toBe(1);
  });
});

// ---------------------------------------------------------------------------
// backfillLootTotals
// ---------------------------------------------------------------------------
describe('backfillLootTotals', () => {
  test('populates loot_totals from existing successful redemptions', async () => {
    // Insert directly into redeemed_codes, bypassing addRedeemedCode so loot_totals stays empty
    db.insert(redeemedCodes)
      .values({ code: 'CODE1111AAAA', discordId: USER_A, status: 'Success', lootDetail: JSON.stringify([{ chest_type_id: 1, count: 5 }]) })
      .run();
    const loot = await codeManager.getAggregateLoot(USER_A);
    expect(loot.chests).toEqual({}); // loot_totals still empty
    await codeManager.backfillLootTotals();
    const filled = await codeManager.getAggregateLoot(USER_A);
    expect(filled.chests['Silver Chest']).toBe(5);
  });

  test('exits early and skips rows already covered if loot_totals is non-empty', async () => {
    // Seed via addRedeemedCode (which writes to loot_totals)
    await codeManager.addRedeemedCode('CODE1111AAAA', USER_A, 'Success', [{ chest_type_id: 1, count: 5 }] as any);
    // Insert a second row directly — it should NOT be picked up by backfill since table is non-empty
    db.insert(redeemedCodes)
      .values({ code: 'CODE2222BBBB', discordId: USER_A, status: 'Success', lootDetail: JSON.stringify([{ chest_type_id: 1, count: 3 }]) })
      .run();
    await codeManager.backfillLootTotals();
    const loot = await codeManager.getAggregateLoot(USER_A);
    expect(loot.chests['Silver Chest']).toBe(5); // Only the original 5, not 5+3=8
  });

  test('skips rows with null loot detail', async () => {
    db.insert(redeemedCodes)
      .values({ code: 'CODE1111AAAA', discordId: USER_A, status: 'Success', lootDetail: null })
      .run();
    await codeManager.backfillLootTotals();
    const loot = await codeManager.getAggregateLoot(USER_A);
    expect(loot.chests).toEqual({});
    expect(loot.items).toEqual({});
  });

  test('skips rows with malformed JSON loot detail', async () => {
    db.insert(redeemedCodes)
      .values({ code: 'CODE1111AAAA', discordId: USER_A, status: 'Success', lootDetail: 'not-valid-json{' })
      .run();
    await codeManager.backfillLootTotals();
    const loot = await codeManager.getAggregateLoot(USER_A);
    expect(loot.chests).toEqual({});
    expect(loot.items).toEqual({});
  });
});