#!/usr/bin/env node
import { Pool } from 'pg';

const connectionString = process.env.SUPABASE_DB_URL || process.env.DATABASE_URL;
const resendApiKey = process.env.RESEND_API_KEY;
const fromEmail = process.env.REMINDER_FROM_EMAIL;
const appUrl = process.env.REMINDER_APP_URL || 'http://localhost:5173';
const dryRun = process.env.REMINDER_DRY_RUN === 'true';

if (!connectionString) {
  console.error('Missing SUPABASE_DB_URL (or DATABASE_URL).');
  process.exit(1);
}

if (!dryRun && (!resendApiKey || !fromEmail)) {
  console.error('Missing RESEND_API_KEY or REMINDER_FROM_EMAIL.');
  process.exit(1);
}

const pool = new Pool({ connectionString });

const findPendingReminders = async (client) => {
  const { rows } = await client.query(`
    select
      a.id,
      a.name,
      a.pat_type,
      a.datum,
      a.created_at,
      u.email
    from public.assessments a
    join auth.users u on u.id = a.user_id
    where a.is_finalized = false
      and a.created_at <= now() - interval '3 days'
      and a.finalization_reminder_sent_at is null
      and coalesce(u.email, '') <> ''
    order by a.created_at asc
  `);

  return rows;
};

const buildEmailPayload = (row) => {
  const subject = `PAT Test noch abschließen: ${row.name || row.pat_type}`;
  const html = `
    <div style="font-family: Arial, sans-serif; line-height: 1.5; color: #111827;">
      <h2 style="margin-bottom: 12px;">PAT Test noch abschließen</h2>
      <p>Dein offener Test wurde seit mehr als 3 Tagen nicht final abgeschlossen.</p>
      <ul>
        <li><strong>Name:</strong> ${row.name || '—'}</li>
        <li><strong>PAT Typ:</strong> ${row.pat_type || '—'}</li>
        <li><strong>Datum:</strong> ${row.datum || '—'}</li>
      </ul>
      <p>Bitte öffne PAT Stats und schließe den Test final ab.</p>
      <p>
        <a href="${appUrl}" style="display: inline-block; margin-top: 8px; padding: 10px 16px; background: #059669; color: #ffffff; text-decoration: none; border-radius: 6px;">
          PAT Stats öffnen
        </a>
      </p>
    </div>
  `;

  return {
    from: fromEmail,
    to: [row.email],
    subject,
    html
  };
};

const sendEmail = async (payload) => {
  const response = await fetch('https://api.resend.com/emails', {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
      Authorization: `Bearer ${resendApiKey}`
    },
    body: JSON.stringify(payload)
  });

  if (!response.ok) {
    const message = await response.text();
    throw new Error(`Resend request failed: ${response.status} ${message}`);
  }
};

const markReminderSent = async (client, assessmentId) => {
  await client.query(
    `
      update public.assessments
      set finalization_reminder_sent_at = now()
      where id = $1
        and is_finalized = false
        and finalization_reminder_sent_at is null
    `,
    [assessmentId]
  );
};

const run = async () => {
  const client = await pool.connect();

  try {
    const rows = await findPendingReminders(client);

    if (!rows.length) {
      console.log('No pending finalization reminders.');
      return;
    }

    for (const row of rows) {
      if (dryRun) {
        console.log(`[dry-run] Would remind ${row.email} for assessment ${row.id}`);
        continue;
      }

      await sendEmail(buildEmailPayload(row));
      await markReminderSent(client, row.id);
      console.log(`Reminder sent for assessment ${row.id} to ${row.email}`);
    }
  } finally {
    client.release();
    await pool.end();
  }
};

run().catch((error) => {
  console.error('Failed to send finalization reminders:', error.message);
  process.exit(1);
});