server/scratch/test_mod_duplicates.js

// Setup testing database environment variables BEFORE any imports
process.env.DB_MODE = 'sqlite';
process.env.SQLITE_DB_PATH = './test_database.db';
// Clear Supabase keys to force SQLite mode in db.js
process.env.SUPABASE_URL = '';
process.env.SUPABASE_KEY = '';
process.env.SUPABASE_SERVICE_ROLE_KEY = '';

import Database from 'better-sqlite3';
import fs from 'fs';

// Clean up test database
if (fs.existsSync('./test_database.db')) {
  fs.unlinkSync('./test_database.db');
}

const sqliteDb = new Database('./test_database.db');

// Create minimal schema for testing
sqliteDb.exec(`
  CREATE TABLE IF NOT EXISTS streams (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    start_time TEXT,
    end_time TEXT,
    title TEXT,
    twitch_stream_id TEXT UNIQUE,
    category TEXT
  );

  CREATE TABLE IF NOT EXISTS mod_actions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    stream_id INTEGER,
    action_type TEXT,
    moderator TEXT,
    target_user TEXT,
    duration INTEGER,
    reason TEXT,
    message_text TEXT,
    timestamp TEXT,
    reaction_time REAL
  );
`);

// Insert a fake stream
sqliteDb.prepare(`
  INSERT INTO streams (id, start_time, title, twitch_stream_id, category)
  VALUES (1, datetime('now'), 'Test Stream', 'test-stream-123', 'Testing')
`).run();

// Dynamically import db.js so it reads the env vars we just set
const { logModAction } = await import('../db.js');

async function runTest() {
  console.log('--- STARTING DUPLICATE MERGE TEST ---');
  
  const timestamp = new Date().toISOString();
  
  // 1. Simulate IRC event (anonymous, moderator = TwitchIRC, no reason)
  console.log('1. Logging IRC mod action (anonymous)...');
  await logModAction({
    actionType: 'timeout',
    moderator: 'TwitchIRC',
    targetUser: 'spammer_user',
    duration: 600,
    timestamp: timestamp
  });
  
  // Verify it exists in DB
  let record = sqliteDb.prepare('SELECT * FROM mod_actions WHERE target_user = ?').get('spammer_user');
  console.log('Record after IRC:', record);
  if (!record || record.moderator !== 'TwitchIRC' || record.reason !== null) {
    console.error('FAIL: Initial IRC record is incorrect.');
    process.exit(1);
  }
  
  // 2. Simulate EventSub event (real moderator, custom reason, same timestamp)
  console.log('2. Logging EventSub mod action (with real mod & reason)...');
  await logModAction({
    actionType: 'timeout',
    moderator: 'GoodModName',
    targetUser: 'spammer_user',
    duration: 600,
    reason: 'Links spamming in chat',
    timestamp: timestamp
  });
  
  // Verify it updated in DB instead of creating a duplicate or ignoring
  const records = sqliteDb.prepare('SELECT * FROM mod_actions WHERE target_user = ?').all('spammer_user');
  console.log('All records in DB after EventSub:', records);
  
  if (records.length !== 1) {
    console.error(`FAIL: Expected exactly 1 record, found ${records.length}`);
    process.exit(1);
  }
  
  record = records[0];
  if (record.moderator !== 'goodmodname' && record.moderator !== 'GoodModName') { // depending on casing
    console.error(`FAIL: Expected moderator to be GoodModName/goodmodname, got: ${record.moderator}`);
    process.exit(1);
  }
  
  if (record.reason !== 'Links spamming in chat') {
    console.error(`FAIL: Expected reason "Links spamming in chat", got: ${record.reason}`);
    process.exit(1);
  }
  
  console.log('SUCCESS: Duplicate mod action successfully merged/enriched!');
  
  // Clean up
  sqliteDb.close();
  if (fs.existsSync('./test_database.db')) {
    fs.unlinkSync('./test_database.db');
  }
}

runTest().catch(err => {
  console.error('Test crashed:', err);
  process.exit(1);
});