import 'dotenv/config';
import pg from 'pg';
import { generateClickRef } from '../src/lib/tracking';

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
const RESEND_API_KEY = process.env.RESEND_API_KEY || '';
const BASE_URL = process.env.SITE_URL || 'https://sneakerpicks.nl';

async function main() {
  console.log(`[${new Date().toISOString()}] Starting weekly watchlist digest...`);

  // Get all users with watchlist items
  const { rows: users } = await pool.query(`
    SELECT DISTINCT u.id, u.email
    FROM users u
    JOIN watchlist w ON w.user_id = u.id
    WHERE u.email_verified = true
      AND (u.unsubscribed IS NULL OR u.unsubscribed = false)
  `);

  console.log(`Found ${users.length} users with watchlist items`);
  let sent = 0;

  for (const user of users) {
    const { rows: items } = await pool.query(`
      SELECT w.product_id, p.name, p.brand, p.slug, p.image_url, p.cutout_image_url,
             (SELECT MIN(po.price) FROM product_offers po WHERE po.product_id = p.id AND po.in_stock = true) as current_price,
             (SELECT MIN(ph.price) FROM price_history ph WHERE ph.product_id = p.id AND ph.recorded_at < NOW() - INTERVAL '6 days') as price_7d_ago
      FROM watchlist w
      JOIN products p ON p.id = w.product_id
      WHERE w.user_id = $1
      ORDER BY w.created_at DESC
    `, [user.id]);

    if (!items.length) continue;

    // Sort by biggest price drop
    const sorted = items
      .filter(i => i.current_price)
      .sort((a, b) => {
        const dropA = a.price_7d_ago ? (a.price_7d_ago - a.current_price) / a.price_7d_ago : 0;
        const dropB = b.price_7d_ago ? (b.price_7d_ago - b.current_price) / b.price_7d_ago : 0;
        return dropB - dropA;
      });

    let productBlocks = '';
    for (const item of sorted) {
      const img = item.cutout_image_url || item.image_url;
      let priceChange = '';
      if (item.price_7d_ago && parseFloat(item.price_7d_ago) !== parseFloat(item.current_price)) {
        const diff = parseFloat(item.current_price) - parseFloat(item.price_7d_ago);
        const color = diff < 0 ? '#00FF6A' : '#ff4444';
        const arrow = diff < 0 ? '↓' : '↑';
        priceChange = `<span style="color:${color};font-size:12px;margin-left:8px">${arrow} €${Math.abs(diff).toFixed(2).replace('.', ',')}</span>`;
      }

      const clickRef = generateClickRef();
      const productUrl = `${BASE_URL}/sneakers/${item.slug}?ref=${clickRef}`;

      productBlocks += `
        <div style="padding:16px 0;border-bottom:1px solid #333">
          <table style="width:100%"><tr>
            <td style="width:80px;vertical-align:top">
              <img src="${img}" alt="${item.brand} ${item.name}" style="width:80px;height:80px;object-fit:contain" />
            </td>
            <td style="padding-left:16px;vertical-align:top">
              <p style="color:#888;font-size:11px;text-transform:uppercase;margin:0">${item.brand}</p>
              <p style="color:#fff;font-weight:bold;margin:4px 0">${item.name}</p>
              <p style="margin:4px 0">
                <span style="color:#00FF6A;font-weight:bold">€${parseFloat(item.current_price).toFixed(2).replace('.', ',')}</span>
                ${priceChange}
              </p>
              <a href="${productUrl}" style="color:#00FF6A;font-size:13px;text-decoration:none">Bekijk deals →</a>
            </td>
          </tr></table>
        </div>`;
    }

    const html = `<!DOCTYPE html><html><head><meta charset="utf-8"></head>
<body style="margin:0;padding:0;background:#0a0a0a;font-family:-apple-system,BlinkMacSystemFont,'Segoe UI',sans-serif">
  <div style="max-width:560px;margin:0 auto;padding:32px 16px">
    <div style="text-align:center;margin-bottom:24px">
      <span style="font-size:24px;font-weight:900;color:#fff">Sneaker</span><span style="font-size:24px;font-weight:900;color:#00FF6A">Picks</span>
    </div>
    <div style="background:#141414;border-radius:16px;padding:24px;border:1px solid #222">
      <h2 style="color:#fff;margin:0 0 8px">Je wekelijkse watchlist update 👀</h2>
      <p style="color:#aaa;margin:0 0 24px">${sorted.length} sneaker${sorted.length !== 1 ? 's' : ''} op je watchlist</p>
      ${productBlocks}
      <div style="text-align:center;margin-top:24px">
        <a href="${BASE_URL}/watchlist" style="display:inline-block;background:#00FF6A;color:#000;padding:12px 32px;border-radius:12px;text-decoration:none;font-weight:bold">Bekijk je watchlist</a>
      </div>
    </div>
    <p style="text-align:center;margin-top:32px"><a href="${BASE_URL}/api/auth/unsubscribe?uid=${user.id}" style="color:#666;font-size:12px;text-decoration:underline">Uitschrijven</a></p>
  </div>
</body></html>`;

    const res = await fetch('https://api.resend.com/emails', {
      method: 'POST',
      headers: { 'Authorization': `Bearer ${RESEND_API_KEY}`, 'Content-Type': 'application/json' },
      body: JSON.stringify({
        from: 'SneakerPicks <alerts@sneakerpicks.nl>',
        to: [user.email],
        subject: `👀 Watchlist update: ${sorted.length} sneakers`,
        html,
      }),
    });
    const result = await res.json();

    await pool.query(
      'INSERT INTO email_log (user_id, email_type, resend_id) VALUES ($1, $2, $3)',
      [user.id, 'weekly_watchlist', result.id || null]
    );
    sent++;
    console.log(`Sent digest to ${user.email} with ${sorted.length} items`);
  }

  console.log(`Done. Sent ${sent} digest emails.`);
  await pool.end();
}

main().catch(e => { console.error(e); process.exit(1); });
