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

async function ensureDefaultSettings() {
  const [rows] = await db.query('SELECT id FROM settings LIMIT 1');
  if (rows.length) return rows[0].id;
  const [result] = await db.query(`
    INSERT INTO settings (
      business_name, address, phone, email, gstin, state_name, state_code,
      cgst_percent, sgst_percent, igst_percent, default_hsn, invoice_prefix,
      invoice_financial_year, invoice_padding, next_invoice_number, declaration_text, terms_text
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `, [
    'ECO FINA',
    'Building No. 9/190 A, Feroke College Road, Melekalathodi, Ramanattukara, Feroke, Kozhikode, Kerala - 673631',
    '+91 8129806530',
    '',
    '32BSOPB0944D1Z8',
    'Kerala',
    '32',
    2.5,
    2.5,
    5,
    '4803',
    'EF',
    '2026-27',
    4,
    1,
    'We declare that this invoice shows the actual price of the goods described and that all particulars are true and correct.',
    'Goods once sold will not be taken back.'
  ]);
  return result.insertId;
}

async function getSettings() {
  await ensureDefaultSettings();
  const [rows] = await db.query('SELECT * FROM settings ORDER BY id ASC LIMIT 1');
  return rows[0];
}

async function updateSettings(data) {
  const settings = await getSettings();
  const fields = [
    'business_name','address','phone','email','gstin','state_name','state_code','logo_path','signature_path','seal_path',
    'cgst_percent','sgst_percent','igst_percent','default_hsn','invoice_prefix','invoice_financial_year','invoice_padding',
    'next_invoice_number','declaration_text','terms_text','bank_details'
  ];
  const updates = [];
  const values = [];
  fields.forEach((field) => {
    if (Object.prototype.hasOwnProperty.call(data, field) && data[field] !== undefined) {
      updates.push(`${field} = ?`);
      values.push(data[field]);
    }
  });
  if (!updates.length) return settings;
  values.push(settings.id);
  await db.query(`UPDATE settings SET ${updates.join(', ')} WHERE id = ?`, values);
  return getSettings();
}

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

async function incrementInvoiceCounter(connection) {
  const executor = connection || db;
  await executor.query('UPDATE settings SET next_invoice_number = next_invoice_number + 1 ORDER BY id ASC LIMIT 1');
}

module.exports = { ensureDefaultSettings, getSettings, updateSettings, nextInvoiceNumber, incrementInvoiceCounter };
