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

function mapCustomer(row) {
  return {
    id: String(row.id),
    customerCode: row.customer_code,
    name: row.customer_name,
    fatherOrHusbandName: row.father_or_husband_name,
    mobile: row.mobile,
    email: row.email,
    alternativeNumber: row.alternative_number,
    address: row.address,
    dateOfBirth: row.date_of_birth,
    aadhaarNumber: row.id_number,
    notes: row.admin_notes,
    verified: Boolean(row.verified_customer),
    premium: Boolean(row.premium_member),
    totalLoans: Number(row.total_loans || 0),
    status: row.status,
    photoUrl: row.photo_url,
    idProofUrl: row.id_proof_url,
  };
}

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

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

  const [countRows] = await pool.execute(`SELECT COUNT(*) AS total FROM customers c ${where}`, params);
  const [rows] = await pool.execute(
    `SELECT c.*, COUNT(l.id) AS total_loans
     FROM customers c
     LEFT JOIN loans l ON l.customer_id = c.id AND l.deleted_at IS NULL
     ${where}
     GROUP BY c.id
     ORDER BY c.created_at DESC
     LIMIT ? OFFSET ?`,
    [...params, pageSize, offset],
  );

  return {items: rows.map(mapCustomer), page, pageSize, total: countRows[0].total};
}

async function getCustomer(shopId, id) {
  const [rows] = await pool.execute(
    `SELECT c.*, COUNT(l.id) AS total_loans
     FROM customers c
     LEFT JOIN loans l ON l.customer_id = c.id AND l.deleted_at IS NULL
     WHERE c.shop_id = ? AND c.id = ? AND c.deleted_at IS NULL
     GROUP BY c.id
     LIMIT 1`,
    [shopId, id],
  );

  if (!rows.length) {
    const error = new Error('Customer not found');
    error.statusCode = 404;
    throw error;
  }
  return mapCustomer(rows[0]);
}

function asBoolean(value) {
  return value === true || value === 'true' || value === '1' ? 1 : 0;
}

async function createCustomer(shopId, userId, payload) {
  console.log('Creating customer with payload:', payload); // Log the payload for debugging
  const customerId = await transaction(async connection => {
    const [counter] = await connection.execute('SELECT COUNT(*) + 1 AS next_number FROM customers WHERE shop_id = ?', [shopId]);
    const customerCode = payload.customerCode || `CUS-${String(counter[0].next_number).padStart(5, '0')}`;
    const [result] = await connection.execute(
      `INSERT INTO customers (
        shop_id, customer_code, customer_name, father_or_husband_name, mobile, alternative_number, email,
        address, date_of_birth, id_number, photo_url, id_proof_url, admin_notes,
        verified_customer, premium_member, status, created_by
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        shopId,
        customerCode,
        payload.name || payload.customerName,
        payload.fatherOrHusbandName || null,
        payload.mobile,
        payload.alternativeNumber || null,
        payload.email || null,
        payload.address,
        payload.dateOfBirth || null,
        payload.aadhaarNumber || payload.idNumber,
        payload.photoUrl || null,
        payload.idProofUrl || null,
        payload.notes || null,
        asBoolean(payload.verified),
        asBoolean(payload.premium),
        payload.status || 'Active',
        userId,
      ],
    );
    return result.insertId;
  });
  return getCustomer(shopId, customerId);
}

async function updateCustomer(shopId, userId, id, payload) {
  console.log('Creating customer with payload:', payload); // Log the payload for debugging
  await pool.execute(
    `UPDATE customers
     SET customer_name = COALESCE(?, customer_name),
         father_or_husband_name = COALESCE(?, father_or_husband_name),
         mobile = COALESCE(?, mobile),
         alternative_number = ?,
         email = ?,
         address = COALESCE(?, address),
         date_of_birth = COALESCE(?, date_of_birth),
         id_number = COALESCE(?, id_number),
         photo_url = COALESCE(?, photo_url),
         id_proof_url = COALESCE(?, id_proof_url),
         admin_notes = ?,
         verified_customer = COALESCE(?, verified_customer),
         premium_member = COALESCE(?, premium_member),
         status = COALESCE(?, status),
         updated_by = ?
     WHERE shop_id = ? AND id = ? AND deleted_at IS NULL`,
    [
      payload.name || payload.customerName || null,
      payload.fatherOrHusbandName || null,
      payload.mobile || null,
      payload.alternativeNumber || null,
      payload.email || null,
      payload.address || null,
      payload.dateOfBirth || null,
      payload.aadhaarNumber || payload.idNumber || null,
      payload.photoUrl || null,
      payload.idProofUrl || null,
      payload.notes || null,
      payload.verified === undefined ? null : asBoolean(payload.verified),
      payload.premium === undefined ? null : asBoolean(payload.premium),
      payload.status || null,
      userId,
      shopId,
      id,
    ],
  );
  return getCustomer(shopId, id);
}

module.exports = {createCustomer, getCustomer, listCustomers, updateCustomer};
