const {pool, transaction} = require('../config/db');
const {getPagination} = require('../utils/pagination');
const PDFDocument = require('pdfkit');

function mapJewelry(row) {
  return {
    id: String(row.id),
    itemName: row.item_name,
    type: row.item_type,
    weight: Number(row.weight),
    purity: row.purity,
    quantity: Number(row.quantity),
    estimatedValue: Number(row.estimated_value),
    remarks: row.remarks,
    photoUrl: row.photo_url,
  };
}

function mapPayment(row) {
  return {
    id: String(row.id),
    date: row.payment_date,
    amount: Number(row.total_amount),
    mode: row.payment_mode,
    type: row.payment_type,
    receiptNumber: row.receipt_number,
    principalAmount: Number(row.principal_amount),
    interestAmount: Number(row.interest_amount),
    feeAmount: Number(row.fee_amount),
    remarks: row.remarks,
  };
}

function calculateAccruedInterest(row, asOf = new Date()) {
  const loanDate = new Date(row.loan_date);
  const elapsedDays = Math.max(0, Math.floor((asOf.getTime() - loanDate.getTime()) / 86400000));
  const rate = Number(row.interest_percentage) / 100;
  let accrued;
  if (row.interest_type === 'Daily') accrued = Number(row.loan_amount) * rate * Math.max(30, elapsedDays);
  else if (row.interest_type === 'Yearly') accrued = Number(row.loan_amount) * rate * (Math.max(30, elapsedDays) / 365);
  else accrued = Number(row.loan_amount) * rate * Math.max(1, elapsedDays / 30);
  return Math.max(0, Number((accrued - Number(row.interest_collected || 0)).toFixed(2)));
}

function mapLoan(row, jewelryItems = [], paymentHistory = []) {
  return {
    id: String(row.id),
    loanNumber: row.loan_number,
    customerId: String(row.customer_id),
    customerName: row.customer_name,
    loanDate: row.loan_date,
    dueDate: row.due_date,
    loanAmount: Number(row.loan_amount),
    interestPercentage: Number(row.interest_percentage),
    interestType: row.interest_type,
    interestCollected: Number(row.interest_collected),
    principalCollected: Number(row.principal_collected),
    netAmount: Number(row.net_amount),
    status: row.status,
    jewelryItems,
    paymentHistory,
    outstandingBalance: Number(row.outstanding_balance),
    accruedInterest: calculateAccruedInterest(row),
    maximumPayable: Number((Number(row.outstanding_balance) + calculateAccruedInterest(row)).toFixed(2)),
  };
}

async function listLoans(shopId, query) {
  const {page, pageSize, offset} = getPagination(query);
  const params = [shopId];
  let where = 'WHERE l.shop_id = ? AND l.deleted_at IS NULL';

  if (query.search) {
    where += ' AND (l.loan_number LIKE ? OR c.customer_name LIKE ?)';
    const search = `%${query.search}%`;
    params.push(search, search);
  }

  if (query.customerId) {
    where += ' AND l.customer_id = ?';
    params.push(query.customerId);
  }

  if (query.status) {
    where += ' AND l.status = ?';
    params.push(query.status);
  }

  if (query.fromDate) {
    where += ' AND l.loan_date >= ?';
    params.push(query.fromDate);
  }

  if (query.toDate) {
    where += ' AND l.loan_date <= ?';
    params.push(query.toDate);
  }

  const sort = query.sort === 'amount_desc' ? 'l.loan_amount DESC' : 'l.created_at DESC';
  const [countRows] = await pool.execute(`SELECT COUNT(*) AS total FROM loans l INNER JOIN customers c ON c.id = l.customer_id ${where}`, params);
  const [rows] = await pool.execute(
    `SELECT l.*, c.customer_name
     FROM loans l
     INNER JOIN customers c ON c.id = l.customer_id
     ${where}
     ORDER BY ${sort}
     LIMIT ? OFFSET ?`,
    [...params, pageSize, offset],
  );

  const jewelryByLoan = {};
  if (rows.length) {
    const loanIds = rows.map(row => row.id);
    const placeholders = loanIds.map(() => '?').join(', ');
    const [jewelryRows] = await pool.execute(
      `SELECT * FROM jewelry_items WHERE loan_id IN (${placeholders}) ORDER BY loan_id, id`,
      loanIds,
    );
    jewelryRows.forEach(row => {
      const loanId = String(row.loan_id);
      jewelryByLoan[loanId] = jewelryByLoan[loanId] || [];
      jewelryByLoan[loanId].push(mapJewelry(row));
    });
  }

  return {items: rows.map(row => mapLoan(row, jewelryByLoan[String(row.id)] || [])), page, pageSize, total: countRows[0].total};
}

async function getLoan(shopId, id) {
  const [loanRows] = await pool.execute(
    `SELECT l.*, c.customer_name
     FROM loans l
     INNER JOIN customers c ON c.id = l.customer_id
     WHERE l.shop_id = ? AND l.id = ? AND l.deleted_at IS NULL
     LIMIT 1`,
    [shopId, id],
  );

  if (!loanRows.length) {
    const error = new Error('Loan not found');
    error.statusCode = 404;
    throw error;
  }

  const [jewelryRows] = await pool.execute('SELECT * FROM jewelry_items WHERE loan_id = ? ORDER BY id', [id]);
  const [paymentRows] = await pool.execute('SELECT * FROM loan_payments WHERE loan_id = ? ORDER BY payment_date DESC', [id]);
  return mapLoan(loanRows[0], jewelryRows.map(mapJewelry), paymentRows.map(mapPayment));
}

async function createLoan(shopId, userId, payload) {
  const loanId = await transaction(async connection => {
    const [counter] = await connection.execute('SELECT COUNT(*) + 1 AS next_number FROM loans WHERE shop_id = ?', [shopId]);
    const loanNumber = payload.loanNumber || `LN-${String(counter[0].next_number).padStart(5, '0')}`;
    const loanAmount = Number(payload.loanAmount || 0);
    const netAmount = Number(payload.netAmount || loanAmount - Number(payload.processingFee || 0));

    const [result] = await connection.execute(
      `INSERT INTO loans (
        shop_id, customer_id, loan_number, loan_date, due_date, loan_amount, interest_percentage,
        interest_type, processing_fee, net_amount, outstanding_balance, status, notes, created_by
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        shopId,
        payload.customerId,
        loanNumber,
        payload.loanDate || new Date().toISOString().slice(0, 10),
        payload.dueDate || null,
        loanAmount,
        Number(payload.interestPercentage || 0),
        payload.interestType || 'Monthly',
        Number(payload.processingFee || 0),
        netAmount,
        loanAmount,
        payload.status === 'Draft' ? 'Draft' : 'Active',
        payload.notes || null,
        userId,
      ],
    );

    const jewelryItems = payload.jewelryItems || [];
    for (const item of jewelryItems) {
      await connection.execute(
        `INSERT INTO jewelry_items (loan_id, item_name, item_type, weight, purity, quantity, estimated_value, remarks, photo_url)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          result.insertId,
          item.itemName,
          item.type,
          Number(item.weight || 0),
          item.purity || null,
          Number(item.quantity || 1),
          Number(item.estimatedValue || 0),
          item.remarks || null,
          item.photoUrl || null,
        ],
      );
    }

    await connection.execute(
      `INSERT INTO loan_timeline (loan_id, event_type, event_title, event_description, created_by)
       VALUES (?, 'Created', 'Loan created', ?, ?)`,
      [result.insertId, `Loan ${loanNumber} created`, userId],
    );

    return result.insertId;
  });
  return getLoan(shopId, loanId);
}

function paymentError(message) {
  const error = new Error(message);
  error.statusCode = 400;
  return error;
}

async function getLockedLoan(connection, shopId, loanId) {
  const [rows] = await connection.execute(
    `SELECT * FROM loans WHERE shop_id = ? AND id = ? AND deleted_at IS NULL FOR UPDATE`,
    [shopId, loanId],
  );
  if (!rows.length) {
    const error = new Error('Loan not found');
    error.statusCode = 404;
    throw error;
  }
  if (!['Active', 'Overdue'].includes(rows[0].status)) throw paymentError('Payments can only be recorded for an active or overdue loan');
  return rows[0];
}

function parsePayment(payload, loan, action) {
  const accruedInterest = calculateAccruedInterest(loan);
  const outstanding = Number(loan.outstanding_balance);
  const principal = Number(payload.principalAmount || 0);
  const interest = Number(payload.interestAmount || 0);
  const fee = Number(payload.feeAmount || 0);
  const total = Number((principal + interest + fee).toFixed(2));
  if (![principal, interest, fee].every(Number.isFinite) || principal < 0 || interest < 0 || fee < 0 || total <= 0) {
    throw paymentError('Enter valid non-negative payment amounts');
  }
  if (principal > outstanding + 0.009) throw paymentError(`Principal cannot exceed the outstanding amount of ${outstanding.toFixed(2)}`);
  if (interest > accruedInterest + 0.009) throw paymentError(`Interest cannot exceed the accrued interest of ${accruedInterest.toFixed(2)}`);
  if (total > outstanding + accruedInterest + 0.009) {
    throw paymentError(`Payment cannot exceed the current payable amount of ${(outstanding + accruedInterest).toFixed(2)}`);
  }
  if (action === 'renew' && (Math.abs(interest - accruedInterest) > 0.009 || principal !== 0 || fee !== 0)) {
    throw paymentError(`Renewal requires the exact outstanding interest of ${accruedInterest.toFixed(2)}`);
  }
  if (action === 'close' && (Math.abs(principal - outstanding) > 0.009 || Math.abs(interest - accruedInterest) > 0.009 || fee !== 0)) {
    throw paymentError(`Closure requires the exact payable amount of ${(outstanding + accruedInterest).toFixed(2)}`);
  }
  return {principal, interest, fee, total, accruedInterest, outstanding};
}

async function insertPayment(connection, loanId, userId, payload, values, paymentType) {
    const [counter] = await connection.execute('SELECT COUNT(*) + 1 AS next_number FROM loan_payments');
    const receiptNumber = payload.receiptNumber || `RCPT-${String(counter[0].next_number).padStart(6, '0')}`;
    await connection.execute(
      `INSERT INTO loan_payments (
        loan_id, receipt_number, payment_date, payment_type, payment_mode,
        principal_amount, interest_amount, fee_amount, total_amount, remarks, received_by
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        loanId,
        receiptNumber,
        payload.paymentDate || new Date(),
        paymentType || payload.paymentType || (values.principal > 0 ? 'Part Payment' : 'Interest'),
        payload.paymentMode || 'Cash',
        values.principal,
        values.interest,
        values.fee,
        values.total,
        payload.remarks || null,
        userId,
      ],
    );

    return receiptNumber;
}

async function receivePayment(shopId, userId, loanId, payload) {
  await transaction(async connection => {
    const loan = await getLockedLoan(connection, shopId, loanId);
    const values = parsePayment(payload, loan, 'payment');
    const receiptNumber = await insertPayment(connection, loanId, userId, payload, values);
    await connection.execute(
      `UPDATE loans
       SET principal_collected = principal_collected + ?,
           interest_collected = interest_collected + ?,
           outstanding_balance = GREATEST(outstanding_balance - ?, 0),
           updated_by = ?
       WHERE shop_id = ? AND id = ?`,
      [values.principal, values.interest, values.principal, userId, shopId, loanId],
    );

    await connection.execute(
      `INSERT INTO loan_timeline (loan_id, event_type, event_title, event_description, created_by)
       VALUES (?, 'Payment Received', 'Payment received', ?, ?)`,
      [loanId, `Receipt ${receiptNumber} for ${values.total}`, userId],
    );
  });
  return getLoan(shopId, loanId);
}

async function closeLoan(shopId, userId, loanId, payload) {
  await transaction(async connection => {
    const loan = await getLockedLoan(connection, shopId, loanId);
    const values = parsePayment(payload, loan, 'close');
    const receiptNumber = await insertPayment(connection, loanId, userId, payload, values, 'Closure');
    await connection.execute(
      `UPDATE loans SET principal_collected = principal_collected + ?, interest_collected = interest_collected + ?,
       status = 'Closed', closed_date = CURDATE(), outstanding_balance = 0, updated_by = ? WHERE id = ?`,
      [values.principal, values.interest, userId, loanId],
    );
    await connection.execute(
      `UPDATE jewelry_items SET status = 'Released' WHERE loan_id = ?`, [loanId],
    );
    await connection.execute(
      `INSERT INTO loan_timeline (loan_id, event_type, event_title, event_description, created_by)
       VALUES (?, 'Closed', 'Loan closed', ?, ?)`,
      [loanId, `Closed with receipt ${receiptNumber} for ${values.total}`, userId],
    );
  });
  return getLoan(shopId, loanId);
}

async function renewLoan(shopId, userId, loanId, payload) {
  await transaction(async connection => {
    const loan = await getLockedLoan(connection, shopId, loanId);
    const values = parsePayment(payload, loan, 'renew');
    const receiptNumber = await insertPayment(connection, loanId, userId, payload, values, 'Renewal Fee');
    await connection.execute(
      `UPDATE loans SET interest_collected = interest_collected + ?, status = 'Renewed', closed_date = CURDATE(),
       outstanding_balance = 0, updated_by = ? WHERE id = ?`,
      [values.interest, userId, loanId],
    );
    await connection.execute(
      `INSERT INTO loan_timeline (loan_id, event_type, event_title, event_description, created_by)
       VALUES (?, 'Renewed', 'Loan renewed', ?, ?)`,
      [loanId, `Outstanding interest collected with receipt ${receiptNumber}`, userId],
    );
  });
  return getLoan(shopId, loanId);
}

function createLoanPdf(loan) {
  return new Promise((resolve, reject) => {
    const document = new PDFDocument({size: 'A4', margin: 58, bufferPages: true});
    const chunks = [];
    document.on('data', chunk => chunks.push(chunk));
    document.on('end', () => resolve(Buffer.concat(chunks)));
    document.on('error', reject);

    const navy = '#041627';
    const gold = '#735C00';
    const grey = '#44474C';
    const pale = '#F3F4F5';
    const left = 58;
    const width = document.page.width - 116;
    const money = value => `Rs. ${Number(value || 0).toFixed(2)}`;
    const formatPdfDate = value => {
      if (!value) return '-';
      const parsed = new Date(value);
      return Number.isNaN(parsed.getTime()) ? String(value) : parsed.toLocaleDateString('en-IN');
    };
    const drawFrame = () => {
      const pageWidth = document.page.width;
      const pageHeight = document.page.height;
      document.save();
      document.lineWidth(7).strokeColor('#111111').rect(14, 14, pageWidth - 28, pageHeight - 28).stroke();
      document.lineWidth(1).strokeColor('#111111').rect(23, 23, pageWidth - 46, pageHeight - 46).stroke();
      document.lineWidth(0.7).strokeColor('#74777D').rect(27, 27, pageWidth - 54, pageHeight - 54).stroke();
      document.restore();
    };
    document.on('pageAdded', drawFrame);
    drawFrame();

    document.fillColor(navy).font('Helvetica-Bold').fontSize(22).text('PRESTIGE LEDGER', left, 60, {align: 'center', width});
    document.fillColor(gold).fontSize(10).text('JEWELRY PAWN LOAN STATEMENT', {align: 'center', characterSpacing: 1.5});
    document.moveDown(0.7).strokeColor(gold).lineWidth(1.5).moveTo(left, document.y).lineTo(left + width, document.y).stroke();
    document.moveDown(1.2);

    const titleY = document.y;
    document.fillColor(navy).font('Helvetica-Bold').fontSize(17).text(`LOAN ${loan.loanNumber}`, left, titleY);
    document.fillColor(loan.status === 'Closed' ? '#93000A' : '#006C4C').fontSize(10).text(String(loan.status).toUpperCase(), left, titleY + 4, {align: 'right', width});
    document.y = titleY + 30;

    const detailRows = [
      ['Customer', loan.customerName, 'Loan date', formatPdfDate(loan.loanDate)],
      ['Loan amount', money(loan.loanAmount), 'Due date', formatPdfDate(loan.dueDate)],
      ['Outstanding', money(loan.outstandingBalance), 'Interest rate', `${loan.interestPercentage}% ${loan.interestType}`],
      ['Accrued interest', money(loan.accruedInterest), 'Maximum payable', money(loan.maximumPayable)],
    ];
    const cellWidth = width / 2;
    detailRows.forEach((row, index) => {
      const y = document.y;
      if (index % 2 === 0) document.fillColor(pale).rect(left, y - 5, width, 35).fill();
      document.fillColor(grey).font('Helvetica').fontSize(8).text(String(row[0]).toUpperCase(), left + 8, y, {width: 82});
      document.fillColor(navy).font('Helvetica-Bold').fontSize(10).text(String(row[1]), left + 90, y, {width: cellWidth - 98});
      document.fillColor(grey).font('Helvetica').fontSize(8).text(String(row[2]).toUpperCase(), left + cellWidth + 8, y, {width: 78});
      document.fillColor(navy).font('Helvetica-Bold').fontSize(10).text(String(row[3]), left + cellWidth + 86, y, {width: cellWidth - 94});
      document.y = y + 35;
    });

    const section = title => {
      document.moveDown(0.8);
      document.fillColor(navy).font('Helvetica-Bold').fontSize(13).text(title.toUpperCase());
      document.moveDown(0.25).strokeColor('#C4C6CD').lineWidth(0.8).moveTo(left, document.y).lineTo(left + width, document.y).stroke();
      document.moveDown(0.55);
    };

    section('Pledged Items');
    if (!loan.jewelryItems.length) document.fillColor(grey).font('Helvetica').fontSize(9).text('No items recorded.');
    loan.jewelryItems.forEach((item, index) => {
      const y = document.y;
      document.fillColor(navy).font('Helvetica-Bold').fontSize(10).text(`${index + 1}. ${item.itemName}`, left, y, {width: 170});
      document.fillColor(grey).font('Helvetica').fontSize(9).text(`${item.type} | ${item.weight}g | ${item.purity || '-'} | Qty ${item.quantity}`, left + 175, y, {width: 210});
      document.fillColor(gold).font('Helvetica-Bold').text(money(item.estimatedValue), left + 385, y, {align: 'right', width: width - 385});
      document.y = Math.max(document.y, y + 22);
    });

    section('Payment History');
    const columns = [0, 82, 185, 292, 390];
    const headers = ['Date', 'Receipt', 'Type / Mode', 'Principal', 'Total'];
    const headerY = document.y;
    document.fillColor(pale).rect(left, headerY - 4, width, 22).fill();
    headers.forEach((header, index) => document.fillColor(grey).font('Helvetica-Bold').fontSize(8).text(header.toUpperCase(), left + columns[index] + 4, headerY, {width: index === 2 ? 105 : 88, align: index > 2 ? 'right' : 'left'}));
    document.y = headerY + 24;
    if (!loan.paymentHistory.length) document.fillColor(grey).font('Helvetica').fontSize(9).text('No payments recorded.');
    loan.paymentHistory.forEach(payment => {
      const y = document.y;
      const cells = [formatPdfDate(payment.date), payment.receiptNumber || '-', `${payment.type || '-'} / ${payment.mode || '-'}`, money(payment.principalAmount), money(payment.amount)];
      cells.forEach((cell, index) => document.fillColor(index === 4 ? navy : grey).font(index === 4 ? 'Helvetica-Bold' : 'Helvetica').fontSize(8).text(cell, left + columns[index] + 4, y, {width: index === 2 ? 105 : 88, align: index > 2 ? 'right' : 'left'}));
      document.y = y + 20;
      document.strokeColor('#E1E3E4').lineWidth(0.5).moveTo(left, document.y - 4).lineTo(left + width, document.y - 4).stroke();
    });

    document.moveDown(2);
    document.fillColor(grey).font('Helvetica').fontSize(8).text('This computer-generated statement records the loan and pledged assets shown above.', left, document.y, {align: 'center', width});
    document.end();
  });
}

async function generateLoanPdf(shopId, loanId) {
  const loan = await getLoan(shopId, loanId);
  return {buffer: await createLoanPdf(loan), filename: `loan-${loan.loanNumber}.pdf`};
}

module.exports = {closeLoan, createLoan, generateLoanPdf, getLoan, listLoans, receivePayment, renewLoan};
