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 price alert check...`);

  // Get all watchlist items with target_price set, for non-unsubscribed users
  const { rows: alerts } = await pool.query(`
    SELECT w.id, w.user_id, w.product_id, w.target_price,
           u.email,
           p.name, p.brand, p.image_url, p.cutout_image_url
    FROM watchlist w
    JOIN users u ON u.id = w.user_id
    JOIN products p ON p.id = w.product_id
    WHERE w.target_price IS NOT NULL
      AND u.email_verified = true
      AND (u.unsubscribed IS NULL OR u.unsubscribed = false)
  `);

  console.log(`Found ${alerts.length} active price alerts`);
  let sent = 0;

  for (const alert of alerts) {
    // Get current lowest price
    const { rows: offers } = await pool.query(`
      SELECT po.price, po.product_url, f.merchant_name as shop_name, f.id as feed_id
      FROM product_offers po
      JOIN feeds f ON f.id = po.feed_id
      WHERE po.product_id = $1 AND po.in_stock = true
      ORDER BY po.price ASC
    `, [alert.product_id]);

    if (!offers.length) continue;

    const lowestPrice = parseFloat(offers[0].price);
    if (lowestPrice > parseFloat(alert.target_price)) continue;

    // Check if we already alerted in last 7 days for this product
    const { rows: recentAlerts } = await pool.query(`
      SELECT id FROM email_log
      WHERE user_id = $1 AND product_id = $2 AND email_type = 'price_alert'
        AND created_at > NOW() - INTERVAL '7 days'
      LIMIT 1
    `, [alert.user_id, alert.product_id]);

    if (recentAlerts.length) continue;

    // Build and send email
    const imageUrl = alert.cutout_image_url || alert.image_url;
    let offerRows = '';
    for (const offer of offers.slice(0, 5)) {
      const clickRef = generateClickRef();
      const url = offer.product_url + (offer.product_url.includes('?') ? '&' : '?') + `clickRef=${clickRef}`;
      offerRows += `
        <tr>
          <td style="padding:8px 0;color:#fff">${offer.shop_name}</td>
          <td style="padding:8px 0;text-align:right">
            <a href="${url}" style="display:inline-block;background:#00FF6A;color:#000;padding:8px 16px;border-radius:8px;text-decoration:none;font-weight:bold;font-size:14px">
              €${parseFloat(offer.price).toFixed(2).replace('.', ',')}
            </a>
          </td>
        </tr>`;
    }

    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">
      <div style="text-align:center;margin-bottom:24px">
        <img src="${imageUrl}" alt="${alert.brand} ${alert.name}" style="max-width:200px;max-height:200px" />
      </div>
      <h2 style="color:#fff;margin:0 0 8px">${alert.brand} ${alert.name}</h2>
      <p style="color:#00FF6A;font-size:20px;font-weight:bold;margin:0 0 4px">Vanaf €${lowestPrice.toFixed(2).replace('.', ',')}</p>
      <p style="color:#aaa;margin:0 0 24px">Onder je target van €${parseFloat(alert.target_price).toFixed(2).replace('.', ',')} 🎯</p>
      <table style="width:100%;border-collapse:collapse">${offerRows}</table>
    </div>
    <p style="text-align:center;margin-top:32px"><a href="${BASE_URL}/api/auth/unsubscribe?uid=${alert.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: [alert.email],
        subject: `💰 Prijsdaling: ${alert.brand} ${alert.name} - vanaf €${lowestPrice.toFixed(2).replace('.', ',')}`,
        html,
      }),
    });
    const result = await res.json();

    await pool.query(
      'INSERT INTO email_log (user_id, email_type, product_id, resend_id) VALUES ($1, $2, $3, $4)',
      [alert.user_id, 'price_alert', alert.product_id, result.id || null]
    );
    sent++;
    console.log(`Sent price alert to ${alert.email} for ${alert.brand} ${alert.name} (€${lowestPrice})`);
  }

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

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