// src/routes/invoices.js
// Admin ▸ Invoices — lets an admin generate a branded payment-request PDF
// for a client, without hand-editing a template every time.
//
// Bank accounts are stored separately from invoices: whichever account the
// admin saves (or edits) for a currency becomes the suggested default the
// next time they start an invoice in that currency, but every account stays
// editable and an admin can save more than one per currency and pick
// between them. Invoices snapshot the bank details at generation time so
// editing a saved account later never rewrites a PDF that already went out.
const express = require('express');
const db = require('../db');
const requireAdmin = require('../middleware/requireAdmin');
const { logAudit } = require('../middleware/audit');
const { amountInWords } = require('../lib/numberToWords');
const { renderInvoicePdf } = require('../pdf/invoiceTemplate');
const { completeText } = require('../llm/provider');

const router = express.Router();

const CURRENCIES = ['TZS', 'USD'];

function money(amount, currency) {
  const n = Number(amount || 0).toLocaleString('en-US', { minimumFractionDigits: 2, maximumFractionDigits: 2 });
  return `${currency} ${n}`;
}

function isValidCurrency(c) {
  return CURRENCIES.includes(String(c || '').toUpperCase());
}

// ---------------------------------------------------------------------------
// BANK ACCOUNTS
// ---------------------------------------------------------------------------

// List saved accounts, optionally filtered by currency. Newest-edited first,
// with the current default for each currency floated to the top.
router.get('/bank-accounts', requireAdmin, (req, res) => {
  const { currency } = req.query;
  let rows;
  if (currency) {
    if (!isValidCurrency(currency)) return res.status(400).json({ error: 'Unknown currency.' });
    rows = db.prepare('SELECT * FROM bank_accounts WHERE currency = ? ORDER BY is_default DESC, updated_at DESC')
      .all(String(currency).toUpperCase());
  } else {
    rows = db.prepare('SELECT * FROM bank_accounts ORDER BY currency ASC, is_default DESC, updated_at DESC').all();
  }
  res.json(rows);
});

// Create a new saved account. First account entered for a currency is
// always made the default automatically; after that, pass makeDefault:true
// to promote a different one.
router.post('/bank-accounts', requireAdmin, (req, res) => {
  const { currency, bankName, accountName, accountNumber, makeDefault } = req.body || {};
  if (!isValidCurrency(currency)) return res.status(400).json({ error: 'Currency must be TZS or USD.' });
  if (!bankName || !String(bankName).trim()) return res.status(400).json({ error: 'Bank name is required.' });
  if (!accountName || !String(accountName).trim()) return res.status(400).json({ error: 'Account name is required.' });
  if (!accountNumber || !String(accountNumber).trim()) return res.status(400).json({ error: 'Account number is required.' });

  const curr = String(currency).toUpperCase();
  const existingCount = db.prepare('SELECT COUNT(*) AS n FROM bank_accounts WHERE currency = ?').get(curr).n;
  const shouldDefault = existingCount === 0 || !!makeDefault;

  let id;
  db.exec('BEGIN');
  try {
    if (shouldDefault) {
      db.prepare('UPDATE bank_accounts SET is_default = 0 WHERE currency = ?').run(curr);
    }
    const info = db.prepare(`
      INSERT INTO bank_accounts (currency, bank_name, account_name, account_number, is_default)
      VALUES (?, ?, ?, ?, ?)
    `).run(curr, String(bankName).trim(), String(accountName).trim(), String(accountNumber).trim(), shouldDefault ? 1 : 0);
    id = info.lastInsertRowid;
    db.exec('COMMIT');
  } catch (err) {
    db.exec('ROLLBACK');
    throw err;
  }

  const row = db.prepare('SELECT * FROM bank_accounts WHERE id = ?').get(id);
  logAudit(req, 'create', 'bank_accounts', row.id, `${row.currency} account at ${row.bank_name}`);
  res.status(201).json(row);
});

// Edit an existing saved account in place (bank name / account name /
// account number, and optionally promote it to the default for its currency).
router.put('/bank-accounts/:id', requireAdmin, (req, res) => {
  const existing = db.prepare('SELECT * FROM bank_accounts WHERE id = ?').get(req.params.id);
  if (!existing) return res.status(404).json({ error: 'Bank account not found.' });

  const { bankName, accountName, accountNumber, makeDefault } = req.body || {};
  const nextBankName = bankName != null ? String(bankName).trim() : existing.bank_name;
  const nextAccountName = accountName != null ? String(accountName).trim() : existing.account_name;
  const nextAccountNumber = accountNumber != null ? String(accountNumber).trim() : existing.account_number;
  if (!nextBankName || !nextAccountName || !nextAccountNumber) {
    return res.status(400).json({ error: 'Bank name, account name and account number cannot be empty.' });
  }

  db.exec('BEGIN');
  try {
    if (makeDefault) {
      db.prepare('UPDATE bank_accounts SET is_default = 0 WHERE currency = ?').run(existing.currency);
    }
    db.prepare(`
      UPDATE bank_accounts
      SET bank_name = ?, account_name = ?, account_number = ?, is_default = ?, updated_at = datetime('now')
      WHERE id = ?
    `).run(nextBankName, nextAccountName, nextAccountNumber, makeDefault ? 1 : existing.is_default, existing.id);
    db.exec('COMMIT');
  } catch (err) {
    db.exec('ROLLBACK');
    throw err;
  }

  const row = db.prepare('SELECT * FROM bank_accounts WHERE id = ?').get(existing.id);
  logAudit(req, 'update', 'bank_accounts', row.id, `${row.currency} account at ${row.bank_name}`);
  res.json(row);
});

router.delete('/bank-accounts/:id', requireAdmin, (req, res) => {
  const existing = db.prepare('SELECT * FROM bank_accounts WHERE id = ?').get(req.params.id);
  if (!existing) return res.status(404).json({ error: 'Bank account not found.' });
  db.prepare('DELETE FROM bank_accounts WHERE id = ?').run(existing.id);
  // If that was the default, promote the most recently updated remaining
  // account for the same currency so there is still a suggestion next time.
  if (existing.is_default) {
    const next = db.prepare('SELECT id FROM bank_accounts WHERE currency = ? ORDER BY updated_at DESC LIMIT 1').get(existing.currency);
    if (next) db.prepare('UPDATE bank_accounts SET is_default = 1 WHERE id = ?').run(next.id);
  }
  logAudit(req, 'delete', 'bank_accounts', req.params.id, `${existing.currency} account at ${existing.bank_name}`);
  res.json({ ok: true });
});

// ---------------------------------------------------------------------------
// INVOICES
// ---------------------------------------------------------------------------

function nextInvoiceNumber() {
  const year = new Date().getFullYear();
  const prefix = `YST-INV-${year}-`;
  const count = db.prepare("SELECT COUNT(*) AS n FROM invoices WHERE invoice_number LIKE ?").get(`${prefix}%`).n;
  return `${prefix}${String(count + 1).padStart(4, '0')}`;
}

router.get('/invoices', requireAdmin, (req, res) => {
  const rows = db.prepare('SELECT * FROM invoices ORDER BY created_at DESC').all();
  res.json(rows);
});

router.get('/invoices/:id', requireAdmin, (req, res) => {
  const row = db.prepare('SELECT * FROM invoices WHERE id = ?').get(req.params.id);
  if (!row) return res.status(404).json({ error: 'Invoice not found.' });
  res.json(row);
});

function validateInvoiceBody(body) {
  const { currency, recipientName, amount } = body || {};
  if (!isValidCurrency(currency)) return 'Currency must be TZS or USD.';
  if (!recipientName || !String(recipientName).trim()) return 'Recipient name / company is required.';
  if (amount == null || isNaN(Number(amount)) || Number(amount) <= 0) return 'Amount must be a positive number.';
  return null;
}

// Create a new invoice (draft by default, or status: 'sent' once a PDF has
// actually been generated for it — the client sets that after a successful
// PDF download). Bank details are copied onto the row at creation time.
router.post('/invoices', requireAdmin, (req, res) => {
  const err = validateInvoiceBody(req.body);
  if (err) return res.status(400).json({ error: err });

  const { currency, bankAccountId, recipientName, recipientAddress, recipientEmail, recipientPhone,
    amount, purpose, status } = req.body;
  const curr = String(currency).toUpperCase();

  let bank = null;
  if (bankAccountId) bank = db.prepare('SELECT * FROM bank_accounts WHERE id = ?').get(bankAccountId);
  if (!bank) bank = db.prepare('SELECT * FROM bank_accounts WHERE currency = ? AND is_default = 1').get(curr);

  const invoiceNumber = nextInvoiceNumber();
  const info = db.prepare(`
    INSERT INTO invoices (invoice_number, currency, bank_account_id, bank_name, account_name, account_number,
      recipient_name, recipient_address, recipient_email, recipient_phone, amount, purpose, status)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `).run(
    invoiceNumber, curr, bank ? bank.id : null, bank ? bank.bank_name : null, bank ? bank.account_name : null,
    bank ? bank.account_number : null, String(recipientName).trim(), recipientAddress ? String(recipientAddress).trim() : null,
    recipientEmail ? String(recipientEmail).trim() : null, recipientPhone ? String(recipientPhone).trim() : null,
    Number(amount), purpose ? String(purpose).trim() : null, ['draft', 'sent', 'paid'].includes(status) ? status : 'draft'
  );

  const row = db.prepare('SELECT * FROM invoices WHERE id = ?').get(info.lastInsertRowid);
  logAudit(req, 'create', 'invoices', row.id, `${row.invoice_number} — ${row.recipient_name}`);
  res.status(201).json(row);
});

// Update an existing draft (edit details) and/or its status (draft -> sent
// -> paid). Re-snapshots bank details if a different saved account is passed.
router.put('/invoices/:id', requireAdmin, (req, res) => {
  const existing = db.prepare('SELECT * FROM invoices WHERE id = ?').get(req.params.id);
  if (!existing) return res.status(404).json({ error: 'Invoice not found.' });

  const body = req.body || {};
  if (body.recipientName != null || body.amount != null || body.currency != null) {
    const err = validateInvoiceBody({ ...existing, recipient_name: existing.recipient_name, ...body });
    if (err) return res.status(400).json({ error: err });
  }

  let bank = null;
  if (body.bankAccountId) bank = db.prepare('SELECT * FROM bank_accounts WHERE id = ?').get(body.bankAccountId);

  const currency = body.currency ? String(body.currency).toUpperCase() : existing.currency;
  const recipientName = body.recipientName != null ? String(body.recipientName).trim() : existing.recipient_name;
  const recipientAddress = body.recipientAddress != null ? String(body.recipientAddress).trim() : existing.recipient_address;
  const recipientEmail = body.recipientEmail != null ? String(body.recipientEmail).trim() : existing.recipient_email;
  const recipientPhone = body.recipientPhone != null ? String(body.recipientPhone).trim() : existing.recipient_phone;
  const amount = body.amount != null ? Number(body.amount) : existing.amount;
  const purpose = body.purpose != null ? String(body.purpose).trim() : existing.purpose;
  const officialPurpose = body.officialPurpose != null ? String(body.officialPurpose).trim() : existing.official_purpose;
  const coverMessage = body.coverMessage != null ? String(body.coverMessage).trim() : existing.cover_message;
  const status = ['draft', 'sent', 'paid'].includes(body.status) ? body.status : existing.status;
  const sentEmailAt = body.markEmailSent ? new Date().toISOString() : existing.sent_email_at;
  const sentWhatsappAt = body.markWhatsappSent ? new Date().toISOString() : existing.sent_whatsapp_at;

  db.prepare(`
    UPDATE invoices SET currency = ?, bank_account_id = ?, bank_name = ?, account_name = ?, account_number = ?,
      recipient_name = ?, recipient_address = ?, recipient_email = ?, recipient_phone = ?, amount = ?,
      purpose = ?, official_purpose = ?, cover_message = ?, status = ?, sent_email_at = ?, sent_whatsapp_at = ?,
      updated_at = datetime('now')
    WHERE id = ?
  `).run(
    currency, bank ? bank.id : existing.bank_account_id, bank ? bank.bank_name : existing.bank_name,
    bank ? bank.account_name : existing.account_name, bank ? bank.account_number : existing.account_number,
    recipientName, recipientAddress, recipientEmail, recipientPhone, amount, purpose, officialPurpose,
    coverMessage, status, sentEmailAt, sentWhatsappAt, existing.id
  );

  const row = db.prepare('SELECT * FROM invoices WHERE id = ?').get(existing.id);
  logAudit(req, 'update', 'invoices', row.id, `${row.invoice_number} — status: ${row.status}`);
  res.json(row);
});

router.delete('/invoices/:id', requireAdmin, (req, res) => {
  const existing = db.prepare('SELECT * FROM invoices WHERE id = ?').get(req.params.id);
  if (!existing) return res.status(404).json({ error: 'Invoice not found.' });
  db.prepare('DELETE FROM invoices WHERE id = ?').run(existing.id);
  logAudit(req, 'delete', 'invoices', req.params.id, existing.invoice_number);
  res.json({ ok: true });
});

// Stream the generated PDF for preview/download. This does not change the
// invoice's status on its own — a PDF can be previewed and re-previewed
// while still a draft. Status moves to "sent" only when the admin actually
// sends it (by email/WhatsApp) or explicitly marks it sent.
router.get('/invoices/:id/pdf', requireAdmin, (req, res) => {
  const row = db.prepare('SELECT * FROM invoices WHERE id = ?').get(req.params.id);
  if (!row) return res.status(404).json({ error: 'Invoice not found.' });

  res.set('Content-Type', 'application/pdf');
  res.set('Content-Disposition', `inline; filename="${row.invoice_number}.pdf"`);

  const doc = renderInvoicePdf({ ...row, amountInWords: amountInWords(row.amount, row.currency) });
  doc.pipe(res);
  doc.end();
});

// ---------------------------------------------------------------------------
// AI POLISH — turn rough notes into a printable purpose line + cover message
// ---------------------------------------------------------------------------
const POLISH_SYSTEM_PROMPT = `You are a professional invoicing assistant for Yellow Stone Traders & General Supplies, a Tanzanian construction, gold mining and general supplies contractor. Given rough notes about what a payment is for, produce two pieces of text and respond with ONLY a raw JSON object (no markdown fences, no commentary), in this exact shape:
{"officialPurpose": "...", "coverMessage": "..."}

officialPurpose: a concise, professional 1-2 sentence description of what the invoice is for, suitable for printing directly on the invoice's "Description / Purpose of Payment" line. No greeting, no signature — just the description.

coverMessage: a short, polite message (4-6 sentences) to send alongside the invoice to the recipient. Address it to the recipient by name if given, mention the invoice is attached, state the amount and currency, ask them to use the invoice number as the payment reference, thank them, and sign off as "Yellow Stone Traders & General Supplies". Keep it warm but businesslike.`;

router.post('/invoices/:id/polish', requireAdmin, async (req, res) => {
  const row = db.prepare('SELECT * FROM invoices WHERE id = ?').get(req.params.id);
  if (!row) return res.status(404).json({ error: 'Invoice not found.' });

  const notes = (req.body && req.body.notes) || row.purpose;
  if (!notes || !String(notes).trim()) {
    return res.status(400).json({ error: 'Add a few rough notes about what this payment is for first.' });
  }

  const userPrompt = `Invoice number: ${row.invoice_number}
Recipient: ${row.recipient_name}
Amount: ${money(row.amount, row.currency)}
Rough notes on what the payment is for: ${notes}`;

  try {
    const raw = await completeText(POLISH_SYSTEM_PROMPT, userPrompt, { temperature: 0.5, maxTokens: 400 });
    const cleaned = raw.replace(/^```json\s*|```$/g, '').trim();
    let parsed;
    try {
      parsed = JSON.parse(cleaned);
    } catch {
      // Model didn't return clean JSON — fall back to using the whole
      // reply as the official purpose so the admin still gets something
      // to review rather than a hard failure.
      parsed = { officialPurpose: cleaned, coverMessage: '' };
    }
    res.json({
      officialPurpose: parsed.officialPurpose || '',
      coverMessage: parsed.coverMessage || ''
    });
  } catch (err) {
    console.error('[invoices] AI polish failed:', err.message);
    res.status(502).json({ error: 'AI rewrite is unavailable right now — please write the purpose and cover message yourself, or try again shortly.' });
  }
});

// ---------------------------------------------------------------------------
// SEND — email (with PDF attached, if SMTP is configured) and a pre-filled
// WhatsApp click-to-chat link
// ---------------------------------------------------------------------------

router.post('/invoices/:id/send-email', requireAdmin, async (req, res) => {
  const row = db.prepare('SELECT * FROM invoices WHERE id = ?').get(req.params.id);
  if (!row) return res.status(404).json({ error: 'Invoice not found.' });

  const to = (req.body && req.body.to) || row.recipient_email;
  if (!to || !String(to).trim()) return res.status(400).json({ error: 'Add the recipient\u2019s email address first.' });

  const subject = `Payment Invoice ${row.invoice_number} — Yellow Stone Traders & General Supplies`;
  const body = row.cover_message || `Dear ${row.recipient_name},\n\nPlease find attached our invoice ${row.invoice_number} for ${money(row.amount, row.currency)}. Kindly use the invoice number as the payment reference.\n\nThank you for your business.\n\nYellow Stone Traders & General Supplies`;

  if (!process.env.SMTP_HOST) {
    // No mail server configured — hand back a pre-filled mailto: link so
    // the admin's own mail client opens instead. The PDF isn't attached
    // automatically in this fallback; the admin downloads and attaches it.
    const mailto = `mailto:${encodeURIComponent(to)}?subject=${encodeURIComponent(subject)}&body=${encodeURIComponent(body)}`;
    return res.json({ sent: false, mailto });
  }

  try {
    const nodemailer = require('nodemailer');
    const transporter = nodemailer.createTransport({
      host: process.env.SMTP_HOST,
      port: Number(process.env.SMTP_PORT || 587),
      secure: Number(process.env.SMTP_PORT) === 465,
      auth: process.env.SMTP_USER ? { user: process.env.SMTP_USER, pass: process.env.SMTP_PASS } : undefined
    });

    const doc = renderInvoicePdf({ ...row, amountInWords: amountInWords(row.amount, row.currency) });
    const chunks = [];
    await new Promise((resolve, reject) => {
      doc.on('data', (chunk) => chunks.push(chunk));
      doc.on('end', resolve);
      doc.on('error', reject);
      doc.end();
    });

    await transporter.sendMail({
      from: process.env.SMTP_FROM || process.env.SMTP_USER,
      to,
      subject,
      text: body,
      attachments: [{ filename: `${row.invoice_number}.pdf`, content: Buffer.concat(chunks) }]
    });

    db.prepare("UPDATE invoices SET status = 'sent', sent_email_at = datetime('now'), recipient_email = ?, updated_at = datetime('now') WHERE id = ?")
      .run(String(to).trim(), row.id);
    logAudit(req, 'update', 'invoices', row.id, `${row.invoice_number} — emailed to ${to}`);
    res.json({ sent: true });
  } catch (err) {
    console.error('[invoices] send-email failed:', err.message);
    res.status(502).json({ error: 'Could not send the email — check the SMTP settings in .env, or download the PDF and send it manually.' });
  }
});

// Builds the pre-filled WhatsApp click-to-chat text. No network call here —
// WhatsApp's wa.me links open the chat with a message already typed in, they
// can't carry a file attachment, so this is always a manual "attach + send"
// step for the admin unless WHATSAPP_TOKEN + WHATSAPP_PHONE_NUMBER_ID are
// set (Meta WhatsApp Cloud API), in which case we send it directly below.
router.post('/invoices/:id/send-whatsapp', requireAdmin, async (req, res) => {
  const row = db.prepare('SELECT * FROM invoices WHERE id = ?').get(req.params.id);
  if (!row) return res.status(404).json({ error: 'Invoice not found.' });

  const rawPhone = (req.body && req.body.phone) || row.recipient_phone;
  if (!rawPhone || !String(rawPhone).trim()) return res.status(400).json({ error: 'Add the recipient\u2019s WhatsApp number first.' });
  const phone = String(rawPhone).replace(/[^\d+]/g, '').replace(/^00/, '+').replace(/^(?!\+)/, '+');

  const text = row.cover_message || `Dear ${row.recipient_name}, please find our invoice ${row.invoice_number} for ${money(row.amount, row.currency)}. Kindly use the invoice number as the payment reference. Thank you for your business — Yellow Stone Traders & General Supplies.`;

  if (process.env.WHATSAPP_TOKEN && process.env.WHATSAPP_PHONE_NUMBER_ID) {
    try {
      const apiBase = `https://graph.facebook.com/v20.0/${process.env.WHATSAPP_PHONE_NUMBER_ID}`;
      const doc = renderInvoicePdf({ ...row, amountInWords: amountInWords(row.amount, row.currency) });
      const chunks = [];
      await new Promise((resolve, reject) => {
        doc.on('data', (chunk) => chunks.push(chunk));
        doc.on('end', resolve);
        doc.on('error', reject);
        doc.end();
      });
      const pdfBuffer = Buffer.concat(chunks);

      const form = new FormData();
      form.append('messaging_product', 'whatsapp');
      form.append('file', new Blob([pdfBuffer], { type: 'application/pdf' }), `${row.invoice_number}.pdf`);
      const uploadResp = await fetch(`${apiBase}/media`, {
        method: 'POST',
        headers: { Authorization: `Bearer ${process.env.WHATSAPP_TOKEN}` },
        body: form
      });
      const uploadData = await uploadResp.json();
      if (!uploadResp.ok || !uploadData.id) throw new Error(uploadData.error?.message || 'Media upload failed');

      const sendResp = await fetch(`${apiBase}/messages`, {
        method: 'POST',
        headers: { Authorization: `Bearer ${process.env.WHATSAPP_TOKEN}`, 'Content-Type': 'application/json' },
        body: JSON.stringify({
          messaging_product: 'whatsapp',
          to: phone.replace('+', ''),
          type: 'document',
          document: { id: uploadData.id, filename: `${row.invoice_number}.pdf`, caption: text }
        })
      });
      if (!sendResp.ok) {
        const errData = await sendResp.json().catch(() => ({}));
        throw new Error(errData.error?.message || 'WhatsApp send failed');
      }

      db.prepare("UPDATE invoices SET status = 'sent', sent_whatsapp_at = datetime('now'), recipient_phone = ?, updated_at = datetime('now') WHERE id = ?")
        .run(phone, row.id);
      logAudit(req, 'update', 'invoices', row.id, `${row.invoice_number} — sent via WhatsApp Cloud API`);
      return res.json({ sent: true });
    } catch (err) {
      console.error('[invoices] WhatsApp Cloud API send failed, falling back to chat link:', err.message);
      // fall through to the pre-filled link fallback below
    }
  }

  const waLink = `https://wa.me/${phone.replace('+', '')}?text=${encodeURIComponent(text)}`;
  res.json({ sent: false, waLink });
});

module.exports = router;
