/**
 * Query OptionsFlow_monthly by date
 * Usage: node scripts/queryByDate.js [date]
 * Example: node scripts/queryByDate.js 2025-01-15
 */
import { rawQuery } from '../src/db.js';
import dotenv from 'dotenv';
import { fileURLToPath } from 'url';
import { dirname, join } from 'path';

const __filename = fileURLToPath(import.meta.url);
const __dirname = dirname(__filename);

dotenv.config({ path: join(__dirname, '..', '.env') });

async function queryByDate(dateStr) {
  console.log(`🔍 Querying OptionsFlow_monthly for date: ${dateStr || 'today'}\n`);

  try {
    let query, params;
    
    if (dateStr) {
      // Try to parse the date - support multiple formats
      let searchDate;
      if (dateStr.match(/^\d{4}-\d{2}-\d{2}$/)) {
        // YYYY-MM-DD format
        searchDate = dateStr;
      } else if (dateStr.match(/^\d{1,2}\/\d{1,2}\/\d{4}$/)) {
        // M/D/YYYY format (like 9/9/2025)
        const [m, d, y] = dateStr.split('/');
        searchDate = `${y}-${m.padStart(2, '0')}-${d.padStart(2, '0')}`;
      } else {
        searchDate = dateStr;
      }
      
      // Query with date filter - handle both formats
      query = `
        SELECT "CreatedDate", "CreatedTime", "Symbol", "CallPut", "Strike", "Premium", "Volume"
        FROM "OptionsFlow_monthly"
        WHERE "CreatedDate" = $1 
           OR "CreatedDate" = $2
           OR "CreatedDate" LIKE $3
        ORDER BY "CreatedTime" DESC
        LIMIT 50
      `;
      params = [searchDate, dateStr, `${dateStr}%`];
    } else {
      // No date specified - show today's records
      const today = new Date().toISOString().split('T')[0];
      const todayFormatted = `${today.split('-')[1]}/${today.split('-')[2]}/${today.split('-')[0]}`;
      
      query = `
        SELECT "CreatedDate", "CreatedTime", "Symbol", "CallPut", "Strike", "Premium", "Volume"
        FROM "OptionsFlow_monthly"
        WHERE "CreatedDate" = $1 
           OR "CreatedDate" = $2
           OR "CreatedDate" LIKE $3
        ORDER BY "CreatedTime" DESC
        LIMIT 50
      `;
      params = [today, todayFormatted, `${today}%`];
    }

    const records = await rawQuery(query, params);
    
    if (records.length === 0) {
      console.log('❌ No records found for this date');
      console.log('\n💡 Try checking available dates:');
      const dates = await rawQuery(`
        SELECT DISTINCT "CreatedDate", COUNT(*) as count
        FROM "OptionsFlow_monthly"
        GROUP BY "CreatedDate"
        ORDER BY "CreatedDate" DESC
        LIMIT 10
      `);
      dates.forEach(d => {
        console.log(`   ${d.CreatedDate}: ${d.count} records`);
      });
    } else {
      console.log(`✅ Found ${records.length} records:\n`);
      records.forEach((r, i) => {
        console.log(`${i + 1}. ${r.CreatedDate} ${r.CreatedTime || ''} | ${r.Symbol} ${r.CallPut} ${r.Strike || ''} | Premium: ${r.Premium} | Vol: ${r.Volume || ''}`);
      });
      
      // Get total count for this date
      const countQuery = `
        SELECT COUNT(*) as count
        FROM "OptionsFlow_monthly"
        WHERE "CreatedDate" = $1 
           OR "CreatedDate" = $2
           OR "CreatedDate" LIKE $3
      `;
      const countResult = await rawQuery(countQuery, params);
      console.log(`\n📊 Total records for this date: ${countResult[0].count}`);
    }

  } catch (error) {
    console.error('❌ Error:', error.message);
    process.exit(1);
  }
}

// Get date from command line or use today
const dateArg = process.argv[2];
queryByDate(dateArg);