'use strict';

const express = require('express');
const { randomUUID } = require('node:crypto');
const { pool, withTransaction } = require('../db');
const { requireUser, requirePermission } = require('../auth');
const {
  calculateLine,
  nullableText,
  round,
  riyadhDate,
  isIsoDate,
  asyncHandler,
} = require('../utils');
const { writeAudit } = require('../audit');
const { buildCustodyWorkbook } = require('../excel');

const router = express.Router();
router.use(requireUser);

function requireAnyPermission(...keys) {
  return function anyPermission(req, res, next) {
    if (!keys.some((key) => req.user.permissions.includes(key))) {
      return res.status(403).json({ message: 'ليس لديك صلاحية لهذه القائمة.' });
    }
    return next();
  };
}

function positiveId(value) {
  const id = Number.parseInt(value, 10);
  return Number.isInteger(id) && id > 0 ? id : null;
}

function safeFilename(value) {
  return String(value || 'عهدة_الخضار')
    .trim()
    .replace(/[\\/:*?"<>|]+/g, '_')
    .replace(/\s+/g, '_')
    .slice(0, 100) || 'عهدة_الخضار';
}

function parseCustodyItems(rawItems) {
  if (!Array.isArray(rawItems) || rawItems.length < 1 || rawItems.length > 300) {
    const error = new Error('يجب أن تحتوي العهدة على سطر واحد على الأقل وبحد أقصى 300 سطر.');
    error.statusCode = 400;
    throw error;
  }

  const items = [];
  let hasMeaningfulData = false;

  for (let index = 0; index < rawItems.length; index += 1) {
    const raw = rawItems[index] || {};
    const productName = nullableText(raw.productName, 180);

    if (!productName) {
      const error = new Error(`اسم الصنف مطلوب في السطر ${index + 1}.`);
      error.statusCode = 400;
      throw error;
    }

    for (const field of ['quantity', 'packSize', 'unitCost', 'salePrice']) {
      const value = raw[field];
      if (value !== '' && value !== null && value !== undefined) {
        const number = Number(value);
        if (!Number.isFinite(number) || number < 0) {
          const error = new Error(`قيمة رقمية غير صحيحة في السطر ${index + 1}.`);
          error.statusCode = 400;
          throw error;
        }
      }
    }

    const calculated = calculateLine(raw);
    const productId = Number.isInteger(Number(raw.productId)) && Number(raw.productId) > 0
      ? Number(raw.productId)
      : null;
    const unitName = nullableText(raw.unitName, 80);
    const note = nullableText(raw.note, 255);

    if (
      unitName || note || calculated.quantity !== null || calculated.packSize !== null
      || calculated.unitCost !== null || calculated.salePrice !== null
    ) {
      hasMeaningfulData = true;
    }

    items.push({
      productId,
      lineNo: index + 1,
      productName,
      unitName,
      note,
      ...calculated,
    });
  }

  if (!hasMeaningfulData) {
    const error = new Error('العهدة فارغة. أدخل بيانات صنف واحد على الأقل.');
    error.statusCode = 400;
    throw error;
  }

  return items;
}

async function resolveProducts(connection, items, { updateDefaults }) {
  const [sortRows] = await connection.query(
    'SELECT sort_order FROM products ORDER BY sort_order DESC LIMIT 1 FOR UPDATE',
  );
  let nextSortOrder = sortRows.length ? Number(sortRows[0].sort_order) : 0;

  for (const item of items) {
    let resolvedProductId = item.productId;
    let productWasCreated = false;

    if (resolvedProductId) {
      const [existingProduct] = await connection.execute(
        'SELECT id FROM products WHERE id = ? LIMIT 1',
        [resolvedProductId],
      );
      if (!existingProduct.length) resolvedProductId = null;
    }

    if (!resolvedProductId) {
      const [sameNameRows] = await connection.execute(
        'SELECT id FROM products WHERE name = ? LIMIT 1 FOR UPDATE',
        [item.productName],
      );

      if (sameNameRows.length) {
        resolvedProductId = sameNameRows[0].id;
      } else {
        nextSortOrder += 1;
        const [productInsert] = await connection.execute(
          `INSERT INTO products
           (sort_order, name, default_unit, default_cost, default_sale_price, default_note)
           VALUES (?, ?, ?, ?, ?, ?)`,
          [
            nextSortOrder,
            item.productName,
            item.unitName,
            item.unitCost,
            item.salePrice,
            item.note,
          ],
        );
        resolvedProductId = productInsert.insertId;
        productWasCreated = true;
      }
    }

    if (updateDefaults || productWasCreated) {
      await connection.execute(
        `UPDATE products
         SET name = ?, default_unit = ?, default_cost = ?, default_sale_price = ?, default_note = ?
         WHERE id = ?`,
        [
          item.productName,
          item.unitName,
          item.unitCost,
          item.salePrice,
          item.note,
          resolvedProductId,
        ],
      );
    }

    item.productId = resolvedProductId;
  }
}

async function insertCustodyItems(connection, custodyId, items) {
  const placeholders = items.map(() => '(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)').join(',');
  const params = [];

  for (const item of items) {
    params.push(
      custodyId,
      item.productId,
      item.lineNo,
      item.productName,
      item.unitName,
      item.quantity,
      item.packSize,
      item.totalUnits,
      item.unitCost,
      item.totalCost,
      item.costPerUnit,
      item.salePrice,
      item.note,
    );
  }

  await connection.query(
    `INSERT INTO custody_items
     (custody_id, product_id, line_no, product_name, unit_name, quantity, pack_size,
      total_units, unit_cost, total_cost, cost_per_unit, sale_price, note)
     VALUES ${placeholders}`,
    params,
  );
}

router.get('/prices/latest', requirePermission('prices'), asyncHandler(async (_req, res) => {
  const [custodyRows] = await pool.query(
    `SELECT id, custody_number, custody_name, custody_date, created_at
     FROM custodies ORDER BY id DESC LIMIT 1`,
  );
  if (!custodyRows.length) return res.json({ custody: null, prices: [] });

  const custody = custodyRows[0];
  const [prices] = await pool.execute(
    `SELECT line_no, product_name, sale_price
     FROM custody_items
     WHERE custody_id = ? AND sale_price IS NOT NULL
     ORDER BY line_no`,
    [custody.id],
  );
  return res.json({ custody, prices });
}));

router.get('/custodies/template', requirePermission('custody'), asyncHandler(async (_req, res) => {
  const [products] = await pool.query(
    `SELECT id, name, default_unit, default_cost, default_sale_price, default_note
     FROM products
     ORDER BY sort_order, id`,
  );

  res.json({
    custodyDate: riyadhDate(),
    source: { type: 'products' },
    items: products.map((product) => ({
      productId: product.id,
      productName: product.name,
      unitName: product.default_unit || '',
      quantity: null,
      packSize: null,
      unitCost: product.default_cost,
      salePrice: product.default_sale_price,
      note: product.default_note,
    })),
  });
}));

router.delete('/products/:id', requirePermission('custody'), asyncHandler(async (req, res) => {
  const productId = positiveId(req.params.id);
  if (!productId) return res.status(400).json({ message: 'رقم الصنف غير صحيح.' });

  const [rows] = await pool.execute(
    'SELECT id, name FROM products WHERE id = ? LIMIT 1',
    [productId],
  );
  if (!rows.length) return res.status(404).json({ message: 'الصنف غير موجود.' });

  await withTransaction(async (connection) => {
    await connection.execute('DELETE FROM products WHERE id = ?', [productId]);
    await writeAudit({
      req,
      actorType: 'user',
      actorId: req.user.id,
      actorName: req.user.username,
      actionKey: 'product_deleted',
      entityType: 'product',
      entityId: productId,
      details: { productName: rows[0].name },
      connection,
    });
  });

  return res.json({
    message: 'تم حذف الصنف من العهد القادمة، ولن تتأثر العهد المحفوظة سابقًا.',
  });
}));

router.get('/custodies/current', requireAnyPermission('custody', 'files'), asyncHandler(async (_req, res) => {
  const [rows] = await pool.query(
    `SELECT id, custody_number, custody_name, custody_date,
            created_by_name, created_by_category, line_count, total_cost, created_at,
            last_edited_by_name, last_edited_at
     FROM custodies ORDER BY id DESC LIMIT 1`,
  );
  if (!rows.length) return res.json({ custody: null, items: [] });

  const [items] = await pool.execute(
    'SELECT * FROM custody_items WHERE custody_id = ? ORDER BY line_no',
    [rows[0].id],
  );
  return res.json({ custody: rows[0], items });
}));

router.get('/custodies', requirePermission('files'), asyncHandler(async (req, res) => {
  const page = Math.max(1, Number.parseInt(req.query.page || '1', 10));
  const limit = Math.min(100, Math.max(10, Number.parseInt(req.query.limit || '30', 10)));
  const offset = (page - 1) * limit;
  const [[countRow]] = await pool.query('SELECT COUNT(*) AS total FROM custodies');
  const [rows] = await pool.execute(
    `SELECT id, custody_number, custody_name, custody_date,
            created_by_name, created_by_category, line_count, total_cost, created_at,
            last_edited_by_name, last_edited_at
     FROM custodies
     ORDER BY id DESC
     LIMIT ? OFFSET ?`,
    [limit, offset],
  );
  return res.json({
    rows,
    page,
    limit,
    total: countRow.total,
    pages: Math.max(1, Math.ceil(countRow.total / limit)),
  });
}));

router.get('/custodies/:id/export', requirePermission('files'), asyncHandler(async (req, res) => {
  const custodyId = positiveId(req.params.id);
  if (!custodyId) return res.status(400).json({ message: 'رقم العهدة غير صحيح.' });

  const [custodyRows] = await pool.execute(
    'SELECT * FROM custodies WHERE id = ? LIMIT 1',
    [custodyId],
  );
  if (!custodyRows.length) return res.status(404).json({ message: 'العهدة غير موجودة.' });

  const [items] = await pool.execute(
    'SELECT * FROM custody_items WHERE custody_id = ? ORDER BY line_no',
    [custodyId],
  );
  const buffer = await buildCustodyWorkbook(custodyRows[0], items);
  const filename = `${safeFilename(custodyRows[0].custody_name)}_${custodyRows[0].custody_date}.xlsx`;

  await writeAudit({
    req,
    actorType: 'user',
    actorId: req.user.id,
    actorName: req.user.username,
    actionKey: 'custody_exported',
    entityType: 'custody',
    entityId: custodyId,
  });

  res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  res.setHeader('Content-Disposition', `attachment; filename*=UTF-8''${encodeURIComponent(filename)}`);
  res.setHeader('Content-Length', buffer.length);
  return res.send(Buffer.from(buffer));
}));

router.get('/custodies/:id', requireAnyPermission('files', 'custody'), asyncHandler(async (req, res) => {
  const custodyId = positiveId(req.params.id);
  if (!custodyId) return res.status(400).json({ message: 'رقم العهدة غير صحيح.' });

  const [custodyRows] = await pool.execute(
    `SELECT id, custody_number, custody_name, custody_date,
            created_by_name, created_by_category, line_count, total_cost, created_at,
            last_edited_by_name, last_edited_at
     FROM custodies WHERE id = ? LIMIT 1`,
    [custodyId],
  );
  if (!custodyRows.length) return res.status(404).json({ message: 'العهدة غير موجودة.' });

  const [items] = await pool.execute(
    'SELECT * FROM custody_items WHERE custody_id = ? ORDER BY line_no',
    [custodyId],
  );
  return res.json({ custody: custodyRows[0], items });
}));

router.post('/custodies', requirePermission('custody'), asyncHandler(async (req, res) => {
  const custodyName = nullableText(req.body?.custodyName, 180);
  const custodyDate = String(req.body?.custodyDate || riyadhDate());
  const clientRequestId = nullableText(req.body?.requestId, 80);

  if (!custodyName || custodyName.length < 2) {
    return res.status(400).json({ message: 'اكتب اسمًا واضحًا للعهدة.' });
  }
  if (!clientRequestId || !/^[A-Za-z0-9_-]{16,80}$/.test(clientRequestId)) {
    return res.status(400).json({ message: 'معرّف عملية الحفظ غير صحيح. حدّث الصفحة وحاول مرة أخرى.' });
  }
  if (!isIsoDate(custodyDate)) return res.status(400).json({ message: 'تاريخ العهدة غير صحيح.' });
  if (custodyDate > riyadhDate()) return res.status(400).json({ message: 'لا يمكن تسجيل عهدة بتاريخ مستقبلي.' });

  const items = parseCustodyItems(req.body?.items);

  const [existingRows] = await pool.execute(
    'SELECT id, custody_number FROM custodies WHERE client_request_id = ? LIMIT 1',
    [clientRequestId],
  );
  if (existingRows.length) {
    return res.json({
      message: 'هذه العهدة محفوظة مسبقًا.',
      custodyId: existingRows[0].id,
      custodyNumber: existingRows[0].custody_number,
      alreadySaved: true,
    });
  }

  const totalCost = round(items.reduce((sum, item) => sum + (item.totalCost || 0), 0), 2);
  const result = await withTransaction(async (connection) => {
    await resolveProducts(connection, items, { updateDefaults: true });

    const temporaryNumber = `TMP-${randomUUID()}`;
    const [insert] = await connection.execute(
      `INSERT INTO custodies
       (custody_number, custody_name, client_request_id, custody_date,
        created_by_user_id, created_by_name, created_by_category, line_count, total_cost)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        temporaryNumber,
        custodyName,
        clientRequestId,
        custodyDate,
        req.user.id,
        req.user.username,
        req.user.category,
        items.length,
        totalCost,
      ],
    );
    const custodyId = insert.insertId;
    const custodyNumber = `VEG-${custodyDate.replaceAll('-', '')}-${String(custodyId).padStart(6, '0')}`;
    await connection.execute(
      'UPDATE custodies SET custody_number = ? WHERE id = ?',
      [custodyNumber, custodyId],
    );

    await insertCustodyItems(connection, custodyId, items);

    await writeAudit({
      req,
      actorType: 'user',
      actorId: req.user.id,
      actorName: req.user.username,
      actionKey: 'custody_created',
      entityType: 'custody',
      entityId: custodyId,
      details: { custodyName, custodyNumber, custodyDate, lineCount: items.length, totalCost },
      connection,
    });
    return { custodyId, custodyNumber };
  });

  return res.status(201).json({
    message: 'تم حفظ العهدة داخل قاعدة البيانات بنجاح.',
    custodyId: result.custodyId,
    custodyNumber: result.custodyNumber,
  });
}));

router.put('/custodies/:id', requireAnyPermission('custody', 'files'), asyncHandler(async (req, res) => {
  const custodyId = positiveId(req.params.id);
  if (!custodyId) return res.status(400).json({ message: 'رقم العهدة غير صحيح.' });

  const custodyName = nullableText(req.body?.custodyName, 180);
  if (!custodyName || custodyName.length < 2) {
    return res.status(400).json({ message: 'اكتب اسمًا واضحًا للعهدة.' });
  }

  const items = parseCustodyItems(req.body?.items);
  const totalCost = round(items.reduce((sum, item) => sum + (item.totalCost || 0), 0), 2);

  const result = await withTransaction(async (connection) => {
    const [custodyRows] = await connection.execute(
      `SELECT id, custody_number, custody_name, custody_date
       FROM custodies WHERE id = ? LIMIT 1 FOR UPDATE`,
      [custodyId],
    );
    if (!custodyRows.length) {
      const error = new Error('العهدة غير موجودة.');
      error.statusCode = 404;
      throw error;
    }

    const [[latestRow]] = await connection.query('SELECT MAX(id) AS latest_id FROM custodies');
    const isLatestCustody = Number(latestRow.latest_id) === custodyId;

    await resolveProducts(connection, items, { updateDefaults: isLatestCustody });
    await connection.execute('DELETE FROM custody_items WHERE custody_id = ?', [custodyId]);
    await insertCustodyItems(connection, custodyId, items);

    await connection.execute(
      `UPDATE custodies
       SET custody_name = ?, line_count = ?, total_cost = ?,
           last_edited_by_user_id = ?, last_edited_by_name = ?, last_edited_at = CURRENT_TIMESTAMP
       WHERE id = ?`,
      [custodyName, items.length, totalCost, req.user.id, req.user.username, custodyId],
    );

    await writeAudit({
      req,
      actorType: 'user',
      actorId: req.user.id,
      actorName: req.user.username,
      actionKey: 'custody_updated',
      entityType: 'custody',
      entityId: custodyId,
      details: {
        oldCustodyName: custodyRows[0].custody_name,
        custodyName,
        custodyNumber: custodyRows[0].custody_number,
        custodyDate: custodyRows[0].custody_date,
        lineCount: items.length,
        totalCost,
        originalDatePreserved: true,
      },
      connection,
    });

    return {
      custodyNumber: custodyRows[0].custody_number,
      custodyDate: custodyRows[0].custody_date,
    };
  });

  return res.json({
    message: 'تم حفظ التعديلات بنجاح دون تغيير تاريخ العهدة.',
    custodyId,
    custodyNumber: result.custodyNumber,
    custodyDate: result.custodyDate,
  });
}));

module.exports = router;
