const db = require('../config/db');
const { logAuditFromReq } = require('../middlewares/audit');

exports.getAll = async (req, res, next) => {
  try {
    // Auto-update delinquency status for customers with outstanding balances
    await db.query(`
      UPDATE customers c
      LEFT JOIN (
        SELECT t.customer_id,
               MIN(t.created_at) AS oldest_unpaid
        FROM transactions t
        WHERE t.customer_id IS NOT NULL
          AND t.payment_method IN ('credit', 'pay_later')
          AND NOT EXISTS (
            SELECT 1 FROM customer_payments cp
            WHERE cp.customer_id = t.customer_id
              AND cp.transaction_id = t.id
          )
        GROUP BY t.customer_id
      ) ou ON ou.customer_id = c.id
      SET c.risk_level = CASE
        WHEN c.ledger_balance <= 0 THEN 'safe'
        WHEN ou.oldest_unpaid IS NULL THEN 'low'
        WHEN DATEDIFF(NOW(), ou.oldest_unpaid) > 60 THEN 'critical'
        WHEN DATEDIFF(NOW(), ou.oldest_unpaid) > 30 THEN 'high'
        ELSE 'low'
      END,
      c.balance_status = CASE
        WHEN c.ledger_balance <= 0 THEN 'settled'
        WHEN ou.oldest_unpaid IS NULL THEN 'current'
        WHEN DATEDIFF(NOW(), ou.oldest_unpaid) > 30 THEN 'past_due'
        ELSE 'current'
      END
      WHERE c.ledger_balance > 0 OR c.risk_level != 'safe'
    `);

    const { customer_type, risk_level, balance_status, search } = req.query;
    let sql = 'SELECT * FROM customers WHERE 1=1';
    const params = [];

    if (customer_type) {
      params.push(customer_type);
      sql += ' AND customer_type = ?';
    }
    if (risk_level) {
      params.push(risk_level);
      sql += ' AND risk_level = ?';
    }
    if (balance_status) {
      params.push(balance_status);
      sql += ' AND balance_status = ?';
    }
    if (search) {
      const like = `%${search}%`;
      params.push(like, like, like, like);
      sql += ' AND (first_name LIKE ? OR last_name LIKE ? OR phone LIKE ? OR email LIKE ?)';
    }

    sql += ' ORDER BY id DESC';
    const [rows] = await db.query(sql, params);
    res.json(rows);
  } catch (err) {
    next(err);
  }
};

exports.getById = async (req, res, next) => {
  try {
    const [rows] = await db.query('SELECT * FROM customers WHERE id = ?', [req.params.id]);
    if (rows.length === 0) return res.status(404).json({ error: 'Customer not found' });
    res.json(rows[0]);
  } catch (err) {
    next(err);
  }
};

exports.create = async (req, res, next) => {
  try {
    const { first_name, last_name, phone, email, customer_type, risk_level, notes, ledger_balance } = req.body;
    if (!first_name || !last_name) {
      return res.status(400).json({ error: 'first_name and last_name are required' });
    }

    // Enforce unique phone number
    if (phone && phone.trim()) {
      const [existing] = await db.query('SELECT id FROM customers WHERE phone = ?', [phone.trim()]);
      if (existing.length > 0) {
        return res.status(400).json({ error: `Phone number "${phone.trim()}" is already registered to another customer` });
      }
    }

    const initialBalance = ledger_balance != null && !isNaN(Number(ledger_balance)) ? Number(ledger_balance) : 0;

    const [result] = await db.query(
      `INSERT INTO customers (first_name, last_name, phone, email, customer_type, risk_level, notes, ledger_balance)
       VALUES (?,?,?,?,?,?,?,?)`,
      [first_name, last_name, phone || null, email || null, customer_type || 'Retail', risk_level || 'safe', notes || null, initialBalance]
    );
    const [rows] = await db.query('SELECT * FROM customers WHERE id = ?', [result.insertId]);

    logAuditFromReq(req, 'create', 'customer', result.insertId, {
      name: `${first_name} ${last_name}`, customer_type: customer_type || 'Retail',
    });

    res.status(201).json(rows[0]);
  } catch (err) {
    next(err);
  }
};

exports.update = async (req, res, next) => {
  try {
    const { first_name, last_name, phone, email, customer_type, risk_level, balance_status, notes, ledger_balance } = req.body;

    // Enforce unique phone number (exclude current customer)
    if (phone && phone.trim()) {
      const [existing] = await db.query('SELECT id FROM customers WHERE phone = ? AND id != ?', [phone.trim(), req.params.id]);
      if (existing.length > 0) {
        return res.status(400).json({ error: `Phone number "${phone.trim()}" is already registered to another customer` });
      }
    }

    const [result] = await db.query(
      `UPDATE customers SET
         first_name     = COALESCE(?, first_name),
         last_name      = COALESCE(?, last_name),
         phone          = COALESCE(?, phone),
         email          = COALESCE(?, email),
         customer_type  = COALESCE(?, customer_type),
         risk_level     = COALESCE(?, risk_level),
         balance_status = COALESCE(?, balance_status),
         notes          = COALESCE(?, notes),
         ledger_balance = COALESCE(?, ledger_balance),
         updated_at     = NOW()
       WHERE id = ?`,
      [first_name, last_name, phone, email, customer_type, risk_level, balance_status, notes, ledger_balance != null ? ledger_balance : null, req.params.id]
    );
    if (result.affectedRows === 0) return res.status(404).json({ error: 'Customer not found' });
    const [rows] = await db.query('SELECT * FROM customers WHERE id = ?', [req.params.id]);

    logAuditFromReq(req, 'update', 'customer', parseInt(req.params.id, 10), {
      fields_updated: Object.keys(req.body),
    });

    res.json(rows[0]);
  } catch (err) {
    next(err);
  }
};

exports.remove = async (req, res, next) => {
  try {
    const [customerRows] = await db.query(
      'SELECT id, ledger_balance, COALESCE(advance_balance, 0) AS advance_balance FROM customers WHERE id = ?',
      [req.params.id]
    );
    if (customerRows.length === 0) return res.status(404).json({ error: 'Customer not found' });

    const customer = customerRows[0];
    if ((Number(customer.ledger_balance) || 0) > 0) {
      return res.status(400).json({ error: 'Cannot delete customer with outstanding balance' });
    }
    if ((Number(customer.advance_balance) || 0) > 0) {
      return res.status(400).json({ error: 'Cannot delete customer with advance credit' });
    }

    const [result] = await db.query('DELETE FROM customers WHERE id = ?', [req.params.id]);
    if (result.affectedRows === 0) return res.status(404).json({ error: 'Customer not found' });

    logAuditFromReq(req, 'delete', 'customer', parseInt(req.params.id, 10));

    res.json({ message: 'Customer deleted' });
  } catch (err) {
    next(err);
  }
};

exports.getAllPayments = async (req, res, next) => {
  try {
    const { from_date, to_date } = req.query;
    let sql = `
      SELECT cp.*, CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
             u.full_name AS user_name
      FROM customer_payments cp
      LEFT JOIN customers c ON c.id = cp.customer_id
      LEFT JOIN users u ON u.id = cp.user_id
      WHERE 1=1
    `;
    const params = [];
    if (from_date) { params.push(from_date); sql += ' AND cp.created_at >= ?'; }
    if (to_date) { params.push(to_date); sql += ' AND cp.created_at <= DATE_ADD(?, INTERVAL 1 DAY)'; }
    sql += ' ORDER BY cp.created_at DESC';
    const [rows] = await db.query(sql, params);
    res.json(rows);
  } catch (err) {
    next(err);
  }
};

exports.getLedger = async (req, res, next) => {
  try {
    const [rows] = await db.query(
      `SELECT t.id, t.transaction_number, t.type, t.total_amount, t.payment_method, t.created_at,
              COALESCE(t.is_deleted, 0) AS is_deleted, t.deleted_at,
              del_u.full_name AS deleted_by
       FROM transactions t
       LEFT JOIN users del_u ON del_u.id = t.deleted_by
       WHERE t.customer_id = ?
       ORDER BY t.created_at DESC`,
      [req.params.id]
    );
    res.json(rows);
  } catch (err) {
    next(err);
  }
};

exports.getPayments = async (req, res, next) => {
  try {
    const [rows] = await db.query(
      `SELECT * FROM customer_payments
       WHERE customer_id = ?
       ORDER BY created_at DESC`,
      [req.params.id]
    );
    res.json(rows);
  } catch (err) {
    next(err);
  }
};

exports.addPayment = async (req, res, next) => {
  try {
    const { amount, payment_method, transaction_id, notes } = req.body;
    if (!amount) return res.status(400).json({ error: 'amount is required' });

    const conn = await db.pool.getConnection();
    try {
      await conn.beginTransaction();

      const paymentAmount = Number(amount);
      if (!Number.isFinite(paymentAmount) || paymentAmount <= 0) {
        await conn.rollback();
        return res.status(400).json({ error: 'amount must be a positive number' });
      }

      const [customerRows] = await conn.query(
        'SELECT id, ledger_balance, COALESCE(advance_balance, 0) AS advance_balance FROM customers WHERE id = ? FOR UPDATE',
        [req.params.id]
      );
      if (customerRows.length === 0) {
        await conn.rollback();
        return res.status(404).json({ error: 'Customer not found' });
      }

      const currentDue = Math.max(Number(customerRows[0].ledger_balance) || 0, 0);
      const currentAdvance = Math.max(Number(customerRows[0].advance_balance) || 0, 0);
      const appliedToDue = Math.min(paymentAmount, currentDue);
      const advanceCredit = paymentAmount - appliedToDue;
      const nextDue = currentDue - appliedToDue;
      const nextAdvance = currentAdvance + advanceCredit;

      const [result] = await conn.query(
        `INSERT INTO customer_payments (customer_id, transaction_id, amount, payment_method, notes, user_id)
         VALUES (?,?,?,?,?,?)`,
        [req.params.id, transaction_id || null, amount, payment_method || 'cash', notes || null, req.user.id]
      );
      const [rows] = await conn.query('SELECT * FROM customer_payments WHERE id = ?', [result.insertId]);

      await conn.query(
        `UPDATE customers SET
           ledger_balance = ?,
           advance_balance = ?,
           balance_status = ?,
           risk_level = ?,
           updated_at = NOW()
         WHERE id = ?`,
        [
          nextDue,
          nextAdvance,
          nextDue > 0 ? 'current' : 'settled',
          nextDue > 0 ? 'low' : 'safe',
          req.params.id,
        ]
      );

      await conn.commit();

      logAuditFromReq(req, 'payment', 'customer', parseInt(req.params.id, 10), {
        amount: paymentAmount,
        applied_to_due: appliedToDue,
        stored_as_advance: advanceCredit,
        payment_method: payment_method || 'cash',
        payment_id: result.insertId,
      });

      res.status(201).json(rows[0]);
    } catch (e) {
      await conn.rollback();
      throw e;
    } finally {
      conn.release();
    }
  } catch (err) {
    next(err);
  }
};

exports.addDue = async (req, res, next) => {
  try {
    const { amount, category, due_date, description } = req.body;
    if (!amount) return res.status(400).json({ error: 'amount is required' });

    const conn = await db.pool.getConnection();
    try {
      await conn.beginTransaction();

      const dueAmount = Number(amount);
      if (!Number.isFinite(dueAmount) || dueAmount <= 0) {
        await conn.rollback();
        return res.status(400).json({ error: 'amount must be a positive number' });
      }

      const [customerRows] = await conn.query(
        'SELECT id, ledger_balance FROM customers WHERE id = ? FOR UPDATE',
        [req.params.id]
      );
      if (customerRows.length === 0) {
        await conn.rollback();
        return res.status(404).json({ error: 'Customer not found' });
      }

      const currentBalance = Math.max(Number(customerRows[0].ledger_balance) || 0, 0);
      const newBalance = currentBalance + dueAmount;

      // Insert into customer_dues
      const [result] = await conn.query(
        `INSERT INTO customer_dues (customer_id, amount, category, due_date, description, user_id)
         VALUES (?,?,?,?,?,?)`,
        [req.params.id, dueAmount, category || 'Service Fee', due_date || null, description || null, req.user.id]
      );

      // Update customer balance
      await conn.query(
        `UPDATE customers SET
           ledger_balance = ?,
           balance_status = 'current',
           risk_level = CASE WHEN ? > 0 THEN GREATEST(risk_level, 'low') ELSE risk_level END,
           updated_at = NOW()
         WHERE id = ?`,
        [newBalance, newBalance, req.params.id]
      );

      await conn.commit();

      const [rows] = await conn.query('SELECT * FROM customer_dues WHERE id = ?', [result.insertId]);

      logAuditFromReq(req, 'add_due', 'customer', parseInt(req.params.id, 10), {
        amount: dueAmount,
        category: category || 'Service Fee',
        due_id: result.insertId,
      });

      res.status(201).json(rows[0]);
    } catch (e) {
      await conn.rollback();
      throw e;
    } finally {
      conn.release();
    }
  } catch (err) {
    next(err);
  }
};

