knowledge/tests/deep-sql-debug.ts

import 'dotenv/config';
import { Pool } from 'pg';

async function debugInsert() {
  const pool = new Pool({
    connectionString: process.env.DATABASE_URL,
  });

  console.log('🚀 Starting Deep SQL Debugging...');

  const testData = {
    filename: 'test-video.mp4',
    storageKey: `uploads/${Date.now()}-test.mp4`,
    mimeType: 'video/mp4',
    size: 1024, // Using number first
    status: 'pending'
  };

  try {
    console.log('\n--- Test 1: Basic Insert with Number for size ---');
    const res1 = await pool.query(
      `INSERT INTO media (filename, storage_key, mime_type, size, status)
       VALUES ($1, $2, $3, $4, $5) RETURNING id`,
      [testData.filename, testData.storageKey, testData.mimeType, testData.size, testData.status]
    );
    console.log('✅ Success! ID:', res1.rows[0].id);

    console.log('\n--- Test 2: Insert with BigInt string for size ---');
    const res2 = await pool.query(
      `INSERT INTO media (filename, storage_key, mime_type, size, status)
       VALUES ($1, $2, $3, $4, $5) RETURNING id`,
      [testData.filename, testData.storageKey, testData.mimeType, '1024', testData.status]
    );
    console.log('✅ Success! ID:', res2.rows[0].id);

    console.log('\n--- Test 3: Insert with actual BigInt ---');
    const res3 = await pool.query(
      `INSERT INTO media (filename, storage_key, mime_type, size, status)
       VALUES ($1, $2, $3, $4, $5) RETURNING id`,
      [testData.filename, testData.storageKey, testData.mimeType, BigInt(testData.size), testData.status]
    );
    console.log('✅ Success! ID:', res3.rows[0].id);

  } catch (err: any) {
    console.error('\n❌ SQL Error Detected!');
    console.error('Message:', err.message);
    console.error('Code:', err.code);
    console.error('Detail:', err.detail);
    console.error('Hint:', err.hint);
    console.error('Table:', err.table);
  } finally {
    await pool.end();
  }
}

debugInsert();