/**
 * Auto-Homepage: Updates homepage sections with trending, deals, and new arrivals.
 * Run daily at 06:00 via cron.
 * Usage: npx tsx scripts/update-homepage.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-homepage');
  try {
    // Trending: top clicked products in last 7 days
    const trending = await pool.query(`
      SELECT dimension_id as product_id FROM daily_stats
      WHERE dimension = 'product' AND date >= CURRENT_DATE - 7
      GROUP BY dimension_id ORDER BY SUM(clicks) DESC LIMIT 20
    `);
    const trendingIds = trending.rows.map((r: any) => parseInt(r.product_id)).filter((id: number) => !isNaN(id));

    // Deals: biggest price drops in last 7 days
    const deals = await pool.query(`
      WITH latest AS (
        SELECT DISTINCT ON (product_id) product_id, price as current_price, recorded_at
        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 FROM latest l
      JOIN week_ago w ON l.product_id = w.product_id
      WHERE l.current_price < w.old_price AND w.old_price > 0
      ORDER BY (w.old_price - l.current_price) / w.old_price DESC
      LIMIT 20
    `);
    const dealIds = deals.rows.map((r: any) => r.product_id);

    // New arrivals: recently synced sneakers
    const newArrivals = await pool.query(`
      SELECT id FROM products WHERE is_sneaker = true
      ORDER BY first_seen_at DESC NULLS LAST, created_at DESC LIMIT 20
    `);
    const newIds = newArrivals.rows.map((r: any) => r.id);

    // Update homepage sections
    const sections = await pool.query(`SELECT id, type, config FROM homepage_sections WHERE enabled = true`);
    let updated = 0;

    for (const s of sections.rows) {
      let productIds: number[] | null = null;
      if (s.type === 'trending' && trendingIds.length > 0) productIds = trendingIds;
      else if (s.type === 'deals' && dealIds.length > 0) productIds = dealIds;
      else if (s.type === 'new_arrivals' && newIds.length > 0) productIds = newIds;

      if (productIds) {
        const config = { ...(s.config || {}), product_ids: productIds };
        await pool.query('UPDATE homepage_sections SET config = $1, updated_at = NOW() WHERE id = $2', [JSON.stringify(config), s.id]);
        updated++;
      }
    }

    const summary = `Updated ${updated} sections. Trending: ${trendingIds.length}, Deals: ${dealIds.length}, New: ${newIds.length}`;
    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();
