const ExcelJS = require('exceljs');
const PDFDocument = require('pdfkit');
const {pool} = require('../config/db');

const REPORT_TYPES = [
  'Daily Collection',
  'Interest Collection',
  'Outstanding Loans',
  'Closed Loans',
  'Loan Register',
  'Customer Register',
  'Overdue Loans',
  'Owner Summary',
];

const moneyColumns = new Set([
  'Loan Amount', 'Interest', 'Principal', 'Fees', 'Total', 'Outstanding', 'Net Amount',
]);

function validatePayload(payload = {}) {
  const reportType = payload.reportType || 'Owner Summary';
  const datePattern = /^\d{4}-\d{2}-\d{2}$/;
  if (!REPORT_TYPES.includes(reportType)) {
    const error = new Error('Invalid report type');
    error.statusCode = 400;
    throw error;
  }
  if (!payload.fromDate || !payload.toDate || !datePattern.test(payload.fromDate) || !datePattern.test(payload.toDate)) {
    const error = new Error('From date and to date are required in YYYY-MM-DD format');
    error.statusCode = 400;
    throw error;
  }
  if (payload.fromDate > payload.toDate) {
    const error = new Error('From date cannot be after to date');
    error.statusCode = 400;
    throw error;
  }
  return {reportType, fromDate: payload.fromDate, toDate: payload.toDate};
}

async function fetchReport(shopId, reportType, fromDate, toDate) {
  const loanColumns = `l.loan_number AS \`Loan Number\`, c.customer_name AS \`Customer\`,
    DATE_FORMAT(l.loan_date, '%Y-%m-%d') AS \`Loan Date\`, DATE_FORMAT(l.due_date, '%Y-%m-%d') AS \`Due Date\`,
    l.loan_amount AS \`Loan Amount\`, l.outstanding_balance AS \`Outstanding\`, l.status AS \`Status\``;
  const loanBase = `FROM loans l INNER JOIN customers c ON c.id = l.customer_id
    WHERE l.shop_id = ? AND l.deleted_at IS NULL`;

  let sql;
  let params = [shopId, fromDate, toDate];
  switch (reportType) {
    case 'Daily Collection':
      sql = `SELECT lp.receipt_number AS \`Receipt Number\`, DATE_FORMAT(lp.payment_date, '%Y-%m-%d') AS \`Payment Date\`,
        l.loan_number AS \`Loan Number\`, c.customer_name AS \`Customer\`, lp.payment_mode AS \`Payment Mode\`,
        lp.principal_amount AS \`Principal\`, lp.interest_amount AS \`Interest\`, lp.fee_amount AS \`Fees\`, lp.total_amount AS \`Total\`
        FROM loan_payments lp INNER JOIN loans l ON l.id = lp.loan_id INNER JOIN customers c ON c.id = l.customer_id
        WHERE l.shop_id = ? AND DATE(lp.payment_date) BETWEEN ? AND ? ORDER BY lp.payment_date`;
      break;
    case 'Interest Collection':
      sql = `SELECT lp.receipt_number AS \`Receipt Number\`, DATE_FORMAT(lp.payment_date, '%Y-%m-%d') AS \`Payment Date\`,
        l.loan_number AS \`Loan Number\`, c.customer_name AS \`Customer\`, lp.payment_mode AS \`Payment Mode\`, lp.interest_amount AS \`Interest\`
        FROM loan_payments lp INNER JOIN loans l ON l.id = lp.loan_id INNER JOIN customers c ON c.id = l.customer_id
        WHERE l.shop_id = ? AND lp.interest_amount > 0 AND DATE(lp.payment_date) BETWEEN ? AND ? ORDER BY lp.payment_date`;
      break;
    case 'Outstanding Loans':
      sql = `SELECT ${loanColumns} ${loanBase} AND l.status IN ('Active', 'Overdue') AND l.loan_date BETWEEN ? AND ? ORDER BY l.loan_date`;
      break;
    case 'Closed Loans':
      sql = `SELECT ${loanColumns}, DATE_FORMAT(l.closed_date, '%Y-%m-%d') AS \`Closed Date\` ${loanBase}
        AND l.status = 'Closed' AND l.closed_date BETWEEN ? AND ? ORDER BY l.closed_date`;
      break;
    case 'Loan Register':
      sql = `SELECT ${loanColumns}, l.net_amount AS \`Net Amount\` ${loanBase} AND l.loan_date BETWEEN ? AND ? ORDER BY l.loan_date`;
      break;
    case 'Customer Register':
      sql = `SELECT c.customer_code AS \`Customer Code\`, c.customer_name AS \`Customer\`, c.mobile AS \`Mobile\`,
        c.address AS \`Address\`, c.status AS \`Status\`, DATE_FORMAT(c.created_at, '%Y-%m-%d') AS \`Created Date\`
        FROM customers c WHERE c.shop_id = ? AND c.deleted_at IS NULL AND DATE(c.created_at) BETWEEN ? AND ? ORDER BY c.created_at`;
      break;
    case 'Overdue Loans':
      sql = `SELECT ${loanColumns} ${loanBase} AND (l.status = 'Overdue' OR (l.status = 'Active' AND l.due_date < CURDATE()))
        AND l.due_date BETWEEN ? AND ? ORDER BY l.due_date`;
      break;
    case 'Owner Summary':
      sql = `SELECT COUNT(*) AS \`Loans\`, COALESCE(SUM(l.loan_amount), 0) AS \`Loan Amount\`,
        COALESCE(SUM(l.interest_collected), 0) AS \`Interest\`, COALESCE(SUM(l.principal_collected), 0) AS \`Principal\`,
        COALESCE(SUM(l.outstanding_balance), 0) AS \`Outstanding\`
        FROM loans l WHERE l.shop_id = ? AND l.deleted_at IS NULL AND l.loan_date BETWEEN ? AND ?`;
      break;
    default:
      throw new Error('Unsupported report type');
  }
  const [rows] = await pool.execute(sql, params);
  return rows.map(row => Object.fromEntries(Object.entries(row).map(([key, value]) => [key, value == null ? '' : value])));
}

function hasReportData(reportType, rows) {
  if (!rows.length) return false;
  if (reportType === 'Owner Summary') return Number(rows[0].Loans || 0) > 0;
  return true;
}

async function checkReportAvailability(shopId, payload) {
  const {reportType, fromDate, toDate} = validatePayload(payload);
  const rows = await fetchReport(shopId, reportType, fromDate, toDate);
  return {hasData: hasReportData(reportType, rows)};
}

function createPdf(reportType, fromDate, toDate, rows) {
  return new Promise((resolve, reject) => {
    const document = new PDFDocument({size: 'A4', layout: 'landscape', margin: 30});
    const chunks = [];
    document.on('data', chunk => chunks.push(chunk));
    document.on('end', () => resolve(Buffer.concat(chunks)));
    document.on('error', reject);
    document.fontSize(18).text(reportType, {align: 'center'});
    document.fontSize(10).text(`${fromDate} to ${toDate}`, {align: 'center'}).moveDown();
    if (!rows.length) {
      document.fontSize(12).text('No records found for the selected date range.');
      document.end();
      return;
    }
    const columns = Object.keys(rows[0]);
    const pageWidth = document.page.width - 60;
    const columnWidth = pageWidth / columns.length;
    const drawRow = (row, header = false) => {
      const height = 22;
      if (document.y + height > document.page.height - 35) document.addPage();
      const y = document.y;
      columns.forEach((column, index) => {
        const value = header ? column : row[column];
        document.font(header ? 'Helvetica-Bold' : 'Helvetica').fontSize(7)
          .text(String(value), 30 + index * columnWidth, y + 5, {width: columnWidth - 5, height: height - 5, ellipsis: true});
      });
      document.moveTo(30, y + height).lineTo(30 + pageWidth, y + height).strokeColor('#cccccc').stroke();
      document.y = y + height;
    };
    drawRow({}, true);
    rows.forEach(row => drawRow(row));
    document.end();
  });
}

async function createExcel(reportType, fromDate, toDate, rows) {
  const workbook = new ExcelJS.Workbook();
  const sheet = workbook.addWorksheet(reportType.slice(0, 31));
  sheet.addRow([reportType]);
  sheet.addRow([`From ${fromDate} to ${toDate}`]);
  sheet.addRow([]);
  if (rows.length) {
    const columns = Object.keys(rows[0]);
    sheet.addRow(columns);
    rows.forEach(row => sheet.addRow(columns.map(column => row[column])));
    sheet.getRow(4).font = {bold: true, color: {argb: 'FFFFFFFF'}};
    sheet.getRow(4).fill = {type: 'pattern', pattern: 'solid', fgColor: {argb: 'FF6750A4'}};
    columns.forEach((column, index) => {
      sheet.getColumn(index + 1).width = Math.min(35, Math.max(14, column.length + 3));
      if (moneyColumns.has(column)) sheet.getColumn(index + 1).numFmt = '₹#,##0.00';
    });
    sheet.autoFilter = {from: {row: 4, column: 1}, to: {row: 4, column: columns.length}};
  } else {
    sheet.addRow(['No records found for the selected date range.']);
  }
  sheet.getRow(1).font = {bold: true, size: 16};
  return Buffer.from(await workbook.xlsx.writeBuffer());
}

async function exportReport(shopId, userId, format, payload) {
  const {reportType, fromDate, toDate} = validatePayload(payload);
  const [result] = await pool.execute(
    `INSERT INTO report_exports (shop_id, report_type, export_format, from_date, to_date, status, requested_by)
     VALUES (?, ?, ?, ?, ?, 'Queued', ?)`,
    [shopId, reportType, format, fromDate, toDate, userId],
  );
  try {
    const rows = await fetchReport(shopId, reportType, fromDate, toDate);
    const buffer = format === 'PDF'
      ? await createPdf(reportType, fromDate, toDate, rows)
      : await createExcel(reportType, fromDate, toDate, rows);
    await pool.execute(`UPDATE report_exports SET status = 'Generated', completed_at = NOW() WHERE id = ?`, [result.insertId]);
    const safeName = reportType.toLowerCase().replace(/[^a-z0-9]+/g, '-').replace(/^-|-$/g, '');
    return {
      buffer,
      filename: `${safeName}-${fromDate}-to-${toDate}.${format === 'PDF' ? 'pdf' : 'xlsx'}`,
      contentType: format === 'PDF' ? 'application/pdf' : 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
    };
  } catch (error) {
    await pool.execute(`UPDATE report_exports SET status = 'Failed', completed_at = NOW() WHERE id = ?`, [result.insertId]);
    throw error;
  }
}

async function listReports() {
  return REPORT_TYPES;
}

module.exports = {checkReportAvailability, exportReport, listReports};
