/**
 * Auto-Deals: Finds products with price drops and updates the deals table.
 * Run daily at 06:15 via cron.
 * Usage: npx tsx scripts/update-deals.ts
 */
import pg from 'pg';

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });

async function logCronStart(name: string): Promise<number> {
  const r = await pool.query('INSERT INTO cron_runs (name) VALUES ($1) RETURNING id', [name]);
  return r.rows[0].id;
}

async function logCronEnd(id: number, status: string, summary: string, error?: string) {
  await pool.query('UPDATE cron_runs SET finished_at = NOW(), status = $1, output_summary = $2, error_message = $3 WHERE id = $4',
    [status, summary, error || null, id]);
}

async function main() {
  const cronId = await logCronStart('update-deals');
  try {
    // Find products with price drops vs 7 days ago
    const result = await pool.query(`
      WITH latest AS (
        SELECT DISTINCT ON (product_id) product_id, price as current_price
        FROM price_history ORDER BY product_id, recorded_at DESC
      ),
      week_ago AS (
        SELECT DISTINCT ON (product_id) product_id, price as old_price
        FROM price_history WHERE recorded_at <= NOW() - INTERVAL '7 days'
        ORDER BY product_id, recorded_at DESC
      )
      SELECT l.product_id, l.current_price, w.old_price,
        ROUND((w.old_price - l.current_price) / w.old_price * 100, 1) as discount_pct
      FROM latest l
      JOIN week_ago w ON l.product_id = w.product_id
      JOIN products p ON p.id = l.product_id AND p.is_sneaker = true
      WHERE l.current_price < w.old_price AND w.old_price > 0 AND l.current_price > 0
      ORDER BY discount_pct DESC
    `);

    // Clear old deals and insert new ones
    await pool.query('DELETE FROM deals');

    if (result.rows.length > 0) {
      const values = result.rows.map((r: any, i: number) => {
        const base = i * 4;
        return `($${base + 1}, $${base + 2}, $${base + 3}, $${base + 4})`;
      }).join(',');

      const params = result.rows.flatMap((r: any) => [r.product_id, r.current_price, r.old_price, r.discount_pct]);

      await pool.query(`
        INSERT INTO deals (product_id, current_price, previous_price, discount_pct)
        VALUES ${values}
        ON CONFLICT (product_id) DO UPDATE SET
          current_price = EXCLUDED.current_price,
          previous_price = EXCLUDED.previous_price,
          discount_pct = EXCLUDED.discount_pct,
          updated_at = NOW()
      `, params);
    }

    const summary = `Found ${result.rows.length} deals. Top discount: ${result.rows[0]?.discount_pct || 0}%`;
    console.log(summary);
    await logCronEnd(cronId, 'ok', summary);
  } catch (err: any) {
    console.error('Error:', err.message);
    await logCronEnd(cronId, 'error', '', err.message);
    process.exit(1);
  } finally {
    await pool.end();
  }
}

main();
