import pg from 'pg';

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

async function aggregateDailyStats() {
  console.log(`[${new Date().toISOString()}] Starting daily stats aggregation...`);

  // Clicks per product per day
  await pool.query(`
    INSERT INTO daily_stats (date, dimension, dimension_id, clicks)
    SELECT DATE(created_at), 'product', product_id::text, COUNT(*)
    FROM click_events WHERE created_at >= CURRENT_DATE - INTERVAL '2 days'
    GROUP BY DATE(created_at), product_id
    ON CONFLICT (date, dimension, dimension_id) DO UPDATE SET clicks = EXCLUDED.clicks
  `);

  // Clicks per shop per day
  await pool.query(`
    INSERT INTO daily_stats (date, dimension, dimension_id, clicks)
    SELECT DATE(created_at), 'shop', shop_id::text, COUNT(*)
    FROM click_events WHERE created_at >= CURRENT_DATE - INTERVAL '2 days'
    GROUP BY DATE(created_at), shop_id
    ON CONFLICT (date, dimension, dimension_id) DO UPDATE SET clicks = EXCLUDED.clicks
  `);

  // Clicks per source per day
  await pool.query(`
    INSERT INTO daily_stats (date, dimension, dimension_id, clicks)
    SELECT DATE(created_at), 'source', source, COUNT(*)
    FROM click_events WHERE created_at >= CURRENT_DATE - INTERVAL '2 days'
    GROUP BY DATE(created_at), source
    ON CONFLICT (date, dimension, dimension_id) DO UPDATE SET clicks = EXCLUDED.clicks
  `);

  // Revenue per product per day
  await pool.query(`
    INSERT INTO daily_stats (date, dimension, dimension_id, conversions, revenue)
    SELECT DATE(conversion_time), 'product', product_id::text, COUNT(*), SUM(commission)
    FROM conversions
    WHERE conversion_time >= CURRENT_DATE - INTERVAL '2 days' AND status != 'rejected' AND matched = true
    GROUP BY DATE(conversion_time), product_id
    ON CONFLICT (date, dimension, dimension_id) DO UPDATE SET conversions = EXCLUDED.conversions, revenue = EXCLUDED.revenue
  `);

  // Pageviews per product per day (uses impressions column for views)
  await pool.query(`
    INSERT INTO daily_stats (date, dimension, dimension_id, impressions)
    SELECT DATE(created_at), 'product', product_id::text, COUNT(*)
    FROM page_views
    WHERE created_at >= CURRENT_DATE - INTERVAL '2 days' AND product_id IS NOT NULL
    GROUP BY DATE(created_at), product_id
    ON CONFLICT (date, dimension, dimension_id) DO UPDATE SET impressions = EXCLUDED.impressions
  `);

  // Total pageviews per day
  await pool.query(`
    INSERT INTO daily_stats (date, dimension, dimension_id, impressions)
    SELECT DATE(created_at), 'total', 'all', COUNT(*)
    FROM page_views
    WHERE created_at >= CURRENT_DATE - INTERVAL '2 days'
    GROUP BY DATE(created_at)
    ON CONFLICT (date, dimension, dimension_id) DO UPDATE SET impressions = EXCLUDED.impressions
  `);

  // Total stats per day
  await pool.query(`
    INSERT INTO daily_stats (date, dimension, dimension_id, clicks, conversions, revenue)
    SELECT d.date, 'total', 'all',
      COALESCE(c.clicks, 0), COALESCE(r.convs, 0), COALESCE(r.rev, 0)
    FROM (SELECT generate_series(CURRENT_DATE - 2, CURRENT_DATE, '1 day')::date AS date) d
    LEFT JOIN (
      SELECT DATE(created_at) AS date, COUNT(*) AS clicks FROM click_events WHERE created_at >= CURRENT_DATE - 2 GROUP BY DATE(created_at)
    ) c ON c.date = d.date
    LEFT JOIN (
      SELECT DATE(conversion_time) AS date, COUNT(*) AS convs, SUM(commission) AS rev
      FROM conversions WHERE conversion_time >= CURRENT_DATE - 2 AND status != 'rejected' GROUP BY DATE(conversion_time)
    ) r ON r.date = d.date
    ON CONFLICT (date, dimension, dimension_id) DO UPDATE SET clicks = EXCLUDED.clicks, conversions = EXCLUDED.conversions, revenue = EXCLUDED.revenue
  `);

  console.log('Daily stats aggregation complete.');
}

aggregateDailyStats().then(() => pool.end()).catch(err => { console.error(err); process.exit(1); });
