const db = require('../config/db');

async function findOrCreateCustomer(connection, invoice) {
  if (!invoice.customer_gstin && !invoice.customer_phone) return null;
  const [existing] = await connection.query(
    'SELECT id FROM customers WHERE (gstin <> "" AND gstin = ?) OR (phone <> "" AND phone = ?) LIMIT 1',
    [invoice.customer_gstin || '', invoice.customer_phone || '']
  );
  if (existing.length) {
    await connection.query(`
      UPDATE customers SET name = ?, address = ?, phone = ?, email = ?, gstin = ?, state_name = ?, state_code = ? WHERE id = ?
    `, [invoice.customer_name, invoice.customer_address, invoice.customer_phone, invoice.customer_email, invoice.customer_gstin, invoice.customer_state_name, invoice.customer_state_code, existing[0].id]);
    return existing[0].id;
  }
  const [result] = await connection.query(`
    INSERT INTO customers (name, address, phone, email, gstin, state_name, state_code) VALUES (?, ?, ?, ?, ?, ?, ?)
  `, [invoice.customer_name, invoice.customer_address, invoice.customer_phone, invoice.customer_email, invoice.customer_gstin, invoice.customer_state_name, invoice.customer_state_code]);
  return result.insertId;
}

async function createInvoice(invoice, items, totals, amountInWords) {
  const connection = await db.getConnection();
  try {
    await connection.beginTransaction();
    const lockClause = db.isSQLite ? '' : ' FOR UPDATE';
    const [[settings]] = await connection.query(`SELECT invoice_prefix, invoice_financial_year, invoice_padding, next_invoice_number FROM settings ORDER BY id ASC LIMIT 1${lockClause}`);
    const sequence = String(settings.next_invoice_number).padStart(settings.invoice_padding || 4, '0');
    const invoiceNumber = `${settings.invoice_prefix}/${settings.invoice_financial_year}/${sequence}`;
    const customerId = await findOrCreateCustomer(connection, invoice);
    const [result] = await connection.query(`
      INSERT INTO invoices (
        invoice_number, invoice_date, customer_id, customer_name, customer_address, customer_phone, customer_email,
        customer_gstin, customer_state_name, customer_state_code, consignee_name, consignee_address, consignee_gstin,
        consignee_state_name, consignee_state_code, payment_mode, delivery_note, buyer_order_no, dispatch_doc_no,
        delivery_date, destination, terms_of_delivery, taxable_amount, cgst_amount, sgst_amount, igst_amount,
        round_off, grand_total, amount_in_words, show_logo, show_signature, show_seal, status
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `, [
      invoiceNumber, invoice.invoice_date, customerId, invoice.customer_name, invoice.customer_address, invoice.customer_phone,
      invoice.customer_email, invoice.customer_gstin, invoice.customer_state_name, invoice.customer_state_code,
      invoice.consignee_name, invoice.consignee_address, invoice.consignee_gstin, invoice.consignee_state_name,
      invoice.consignee_state_code, invoice.payment_mode, invoice.delivery_note, invoice.buyer_order_no, invoice.dispatch_doc_no,
      invoice.delivery_date || null, invoice.destination, invoice.terms_of_delivery, totals.taxable_amount, totals.cgst_amount,
      totals.sgst_amount, totals.igst_amount, totals.round_off, totals.grand_total, amountInWords,
      invoice.show_logo ? 1 : 0, invoice.show_signature ? 1 : 0, invoice.show_seal ? 1 : 0, invoice.status || 'active'
    ]);
    const invoiceId = result.insertId;
    for (const item of items) {
      await connection.query(`
        INSERT INTO invoice_items (
          invoice_id, description, paper_type, hsn_code, quantity, rate, discount_percent, taxable_amount,
          cgst_percent, cgst_amount, sgst_percent, sgst_amount, igst_percent, igst_amount, total_amount
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      `, [invoiceId, item.description, item.paper_type, item.hsn_code, item.quantity, item.rate, item.discount_percent,
        item.taxable_amount, item.cgst_percent, item.cgst_amount, item.sgst_percent, item.sgst_amount, item.igst_percent,
        item.igst_amount, item.total_amount]);
    }
    if (db.isSQLite) {
      await connection.query('UPDATE settings SET next_invoice_number = next_invoice_number + 1 WHERE id = (SELECT id FROM settings ORDER BY id ASC LIMIT 1)');
    } else {
      await connection.query('UPDATE settings SET next_invoice_number = next_invoice_number + 1 ORDER BY id ASC LIMIT 1');
    }
    await connection.commit();
    return invoiceId;
  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    connection.release();
  }
}

async function listInvoices(filters = {}) {
  const where = [];
  const params = [];
  if (filters.from) { where.push('invoice_date >= ?'); params.push(filters.from); }
  if (filters.to) { where.push('invoice_date <= ?'); params.push(filters.to); }
  if (filters.q) {
    where.push('(invoice_number LIKE ? OR customer_name LIKE ? OR customer_gstin LIKE ?)');
    params.push(`%${filters.q}%`, `%${filters.q}%`, `%${filters.q}%`);
  }
  if (filters.status) { where.push('status = ?'); params.push(filters.status); }
  const sql = `SELECT * FROM invoices ${where.length ? `WHERE ${where.join(' AND ')}` : ''} ORDER BY invoice_date DESC, id DESC LIMIT 300`;
  const [rows] = await db.query(sql, params);
  return rows;
}

async function getInvoice(id) {
  const [[invoice]] = await db.query('SELECT * FROM invoices WHERE id = ?', [id]);
  if (!invoice) return null;
  const [items] = await db.query('SELECT * FROM invoice_items WHERE invoice_id = ? ORDER BY id ASC', [id]);
  return { invoice, items };
}

async function cancelInvoice(id) {
  await db.query('UPDATE invoices SET status = "cancelled" WHERE id = ?', [id]);
}

async function nextInvoicePreview() {
  const [[settings]] = await db.query('SELECT * FROM settings ORDER BY id ASC LIMIT 1');
  const number = String(settings.next_invoice_number).padStart(settings.invoice_padding || 4, '0');
  return `${settings.invoice_prefix}/${settings.invoice_financial_year}/${number}`;
}

module.exports = { createInvoice, listInvoices, getInvoice, cancelInvoice, nextInvoicePreview };
