import { NextFunction, Request, Response } from 'express';
import { RowDataPacket } from 'mysql2/promise';
import { pool } from '../config/db.js';
import { JCR_STATUSES, type JcrStatus } from '../constants/jcr.js';
import type { LineItemSection } from '../constants/lineItems.js';
import { AuthRequest } from '../middleware/auth.middleware.js';
import { hasPermission } from '../services/permissionService.js';
import { notifyRole, notifyUsers } from '../services/notificationService.js';
import { fetchProjectDto } from './project.controller.js';

function mysqlErrorCode(err: unknown): string | undefined {
  return err && typeof err === 'object' && 'code' in err ? (err as { code?: string }).code : undefined;
}

interface JcrListRow extends RowDataPacket {
  id: number;
  job_code: string | null;
  name: string;
  client_name: string | null;
  pm_name: string | null;
  status: JcrStatus | null;
  total_items: number;
  completed_items: number;
}

interface JcrStatusRow extends RowDataPacket {
  status: JcrStatus;
}

export async function list(_req: Request, res: Response, next: NextFunction) {
  try {
    const [rows] = await pool.query<JcrListRow[]>(
      `SELECT p.id, p.job_code, p.name, c.name AS client_name, pm.name AS pm_name, j.status,
              -- Progress counts line items only; overheads are not counted
              (SELECT COUNT(*) FROM project_line_items li WHERE li.project_id = p.id) AS total_items,
              (SELECT COUNT(*) FROM project_line_items li WHERE li.project_id = p.id AND li.is_completed = 1) AS completed_items
       FROM projects p
       LEFT JOIN clients c ON c.id = p.client_id
       LEFT JOIN users pm ON pm.id = p.project_manager_id
       LEFT JOIN jcr j ON j.project_id = p.id
       WHERE p.approval_status = 'approved'
       ORDER BY p.id DESC`,
    );
    res.json(
      rows.map((row) => ({
        projectId: row.id,
        jobCode: row.job_code,
        name: row.name,
        clientName: row.client_name,
        projectManagerName: row.pm_name,
        status: row.status ?? 'yet_to_start',
        // Completion progress: line items + overheads marked completed (same rule that completes a JCR).
        totalItems: Number(row.total_items),
        completedItems: Number(row.completed_items),
      })),
    );
  } catch (err) {
    next(err);
  }
}

export async function getOne(req: Request, res: Response, next: NextFunction) {
  const projectId = Number(req.params.projectId);
  try {
    const dto = await fetchProjectDto(projectId);
    if (!dto || dto.approvalStatus !== 'approved') return res.status(404).json({ message: 'Project not found' });

    const [statusRows] = await pool.query<JcrStatusRow[]>('SELECT status FROM jcr WHERE project_id = ?', [
      projectId,
    ]);
    res.json({ ...dto, status: statusRows[0]?.status ?? 'yet_to_start' });
  } catch (err) {
    next(err);
  }
}

export async function updateStatus(req: Request, res: Response, next: NextFunction) {
  const projectId = Number(req.params.projectId);
  const status = req.body?.status as JcrStatus;

  if (!JCR_STATUSES.includes(status)) {
    return res.status(400).json({ message: 'Invalid status' });
  }

  try {
    await pool.query(
      `INSERT INTO jcr (project_id, status) VALUES (?, ?)
       ON DUPLICATE KEY UPDATE status = VALUES(status)`,
      [projectId, status],
    );
    res.json({ projectId, status });
  } catch (err) {
    next(err);
  }
}

interface RevisedAceItemInput {
  lineItemId: number;
  revisedRate: number;
}

interface RevisedOverheadInput {
  overheadId: number;
  revisedCtcPerMonth: number;
}

export async function updateRevisedAce(req: AuthRequest, res: Response, next: NextFunction) {
  const projectId = Number(req.params.projectId);
  const items = req.body?.items as RevisedAceItemInput[] | undefined;
  const overheads = req.body?.overheads as RevisedOverheadInput[] | undefined;

  const hasItems = Array.isArray(items) && items.length > 0;
  const hasOverheads = Array.isArray(overheads) && overheads.length > 0;

  try {
    if (!(await hasPermission(req.userId!, 'jcr', 'canReviseAce'))) {
      return res.status(403).json({ message: 'You do not have permission to set the revised ACE.' });
    }
    if (!hasItems && !hasOverheads) {
      return res.status(400).json({ message: 'items or overheads must be a non-empty array' });
    }

    const [jcrRows] = await pool.query<JcrStatusRow[]>('SELECT status FROM jcr WHERE project_id = ?', [projectId]);
    if (jcrRows[0]?.status === 'completed') {
      return res.status(400).json({ message: 'This JCR is Completed — ACE is locked.' });
    }

    if (hasItems) {
      const [rows] = await pool.query<RowDataPacket[]>(
        'SELECT id, ace_rate FROM project_line_items WHERE project_id = ?',
        [projectId],
      );
      const aceRateById = new Map<number, number>(rows.map((r) => [r.id as number, Number(r.ace_rate)]));

      for (const item of items!) {
        const origRate = aceRateById.get(item.lineItemId);
        if (origRate === undefined) {
          return res.status(400).json({ message: `Line item ${item.lineItemId} does not belong to this project.` });
        }
        if (!(Number(item.revisedRate) > 0)) {
          return res.status(400).json({
            message: `Revised rate for line item ${item.lineItemId} must be greater than 0.`,
          });
        }
      }

      for (const item of items!) {
        await pool.query('UPDATE project_line_items SET revised_rate = ? WHERE id = ? AND project_id = ?', [
          item.revisedRate,
          item.lineItemId,
          projectId,
        ]);
      }
    }

    if (hasOverheads) {
      const [rows] = await pool.query<RowDataPacket[]>(
        'SELECT id, ctc_per_month FROM project_overheads WHERE project_id = ?',
        [projectId],
      );
      const ctcById = new Map<number, number>(rows.map((r) => [r.id as number, Number(r.ctc_per_month)]));

      for (const overhead of overheads!) {
        const origCtc = ctcById.get(overhead.overheadId);
        if (origCtc === undefined) {
          return res.status(400).json({ message: `Overhead ${overhead.overheadId} does not belong to this project.` });
        }
        if (!(Number(overhead.revisedCtcPerMonth) > 0)) {
          return res.status(400).json({
            message: `Revised CTC/M for overhead ${overhead.overheadId} must be greater than 0.`,
          });
        }
      }

      for (const overhead of overheads!) {
        await pool.query(
          'UPDATE project_overheads SET revised_ctc_per_month = ? WHERE id = ? AND project_id = ?',
          [overhead.revisedCtcPerMonth, overhead.overheadId, projectId],
        );
      }
    }

    const dto = await fetchProjectDto(projectId);
    const [statusRows] = await pool.query<JcrStatusRow[]>('SELECT status FROM jcr WHERE project_id = ?', [
      projectId,
    ]);
    res.json({ ...dto, status: statusRows[0]?.status ?? 'yet_to_start' });
  } catch (err) {
    next(err);
  }
}

type ApprovalStatus = 'pending' | 'approved' | 'rejected';

interface LineItemActualsRow extends RowDataPacket {
  id: number;
  section: LineItemSection;
  description: string;
  unit: string;
  qty: string;
  revised_rate: string | null;
  is_completed: number;
  actual_qty: string;
  actual_amount: string;
}

interface HistoryRow extends RowDataPacket {
  id: number;
  line_item_id: number;
  period: string;
  actual_qty: string;
  actual_rate: string;
  etc_qty: string;
  etc_rate: string;
  remarks: string | null;
  approval_status: ApprovalStatus;
  rejection_reason: string | null;
  entered_by_name: string | null;
  decided_by_name: string | null;
  approved_at: string | null;
  created_at: string;
}

interface OverheadActualsRow extends RowDataPacket {
  id: number;
  category_code: string | null;
  category_description: string | null;
  sub_category: string | null;
  ctc_per_month: string;
  nos: number;
  month: string;
  revised_ctc_per_month: string | null;
  is_completed: number;
  actual_amount: string;
}

interface OverheadHistoryRow extends RowDataPacket {
  id: number;
  overhead_id: number;
  period: string;
  actual_amount: string;
  etc_amount: string;
  remarks: string | null;
  approval_status: ApprovalStatus;
  rejection_reason: string | null;
  entered_by_name: string | null;
  decided_by_name: string | null;
  approved_at: string | null;
  created_at: string;
}

interface RejectionRow extends RowDataPacket {
  id: number;
  actual_id: number;
  period: string;
  actual_qty: string | null;
  actual_rate: string | null;
  etc_qty: string | null;
  etc_rate: string | null;
  actual_amount: string;
  etc_amount: string;
  remarks: string | null;
  rejection_reason: string | null;
  entered_by_name: string | null;
  rejected_by_name: string | null;
  rejected_at: string;
}

/** Logged rejections for the given actuals rows, grouped by actual id (oldest first). */
async function rejectionsByActual(kind: 'line_item' | 'overhead', actualIds: number[]) {
  const map = new Map<number, RejectionRow[]>();
  if (actualIds.length === 0) return map;
  const [rows] = await pool.query<RejectionRow[]>(
    `SELECT r.id, r.actual_id, r.period, r.actual_qty, r.actual_rate, r.etc_qty, r.etc_rate, r.actual_amount, r.etc_amount,
            r.remarks, r.rejection_reason, r.rejected_at, u.name AS entered_by_name, rb.name AS rejected_by_name
     FROM actuals_rejections r
     LEFT JOIN users u ON u.id = r.entered_by
     LEFT JOIN users rb ON rb.id = r.rejected_by
     WHERE r.kind = ? AND r.actual_id IN (?)
     ORDER BY r.rejected_at ASC, r.id ASC`,
    [kind, actualIds],
  );
  for (const row of rows) {
    const bucket = map.get(row.actual_id) ?? [];
    bucket.push(row);
    map.set(row.actual_id, bucket);
  }
  return map;
}

/**
 * Past rejections to show before an entry: all of them, except that when the entry is rejected
 * right now its latest rejection is the entry itself (already shown as the live row).
 */
function earlierRejections(log: RejectionRow[] | undefined, currentStatus: ApprovalStatus) {
  if (!log) return [];
  return currentStatus === 'rejected' ? log.slice(0, -1) : log;
}

/** Snapshot a just-rejected entry into the permanent rejection log. */
async function logRejection(kind: 'line_item' | 'overhead', actualId: number) {
  if (kind === 'line_item') {
    await pool.query(
      `INSERT INTO actuals_rejections
         (kind, actual_id, period, actual_qty, actual_rate, etc_qty, etc_rate, actual_amount, etc_amount,
          remarks, entered_by, rejection_reason, rejected_by, rejected_at)
       SELECT 'line_item', a.id, a.period, a.actual_qty, a.actual_rate, a.etc_qty, a.etc_rate,
              a.actual_qty * a.actual_rate, a.etc_qty * a.etc_rate,
              a.remarks, a.entered_by, a.rejection_reason, a.approved_by, COALESCE(a.approved_at, NOW())
       FROM project_line_item_actuals a WHERE a.id = ?`,
      [actualId],
    );
  } else {
    await pool.query(
      `INSERT INTO actuals_rejections
         (kind, actual_id, period, actual_amount, etc_amount, remarks, entered_by, rejection_reason, rejected_by, rejected_at)
       SELECT 'overhead', a.id, a.period, a.actual_amount, a.etc_amount,
              a.remarks, a.entered_by, a.rejection_reason, a.approved_by, COALESCE(a.approved_at, NOW())
       FROM project_overhead_actuals a WHERE a.id = ?`,
      [actualId],
    );
  }
}

export async function getActuals(_req: Request, res: Response, next: NextFunction) {
  const projectId = Number(_req.params.projectId);
  try {
    const project = await fetchProjectDto(projectId);
    if (!project || project.approvalStatus !== 'approved') {
      return res.status(404).json({ message: 'Project not found' });
    }

    const [itemRows] = await pool.query<LineItemActualsRow[]>(
      `SELECT li.id, li.section, li.description, li.unit, li.qty, li.revised_rate, li.is_completed,
              COALESCE(agg.actual_qty, 0) AS actual_qty,
              COALESCE(agg.actual_amount, 0) AS actual_amount
       FROM project_line_items li
       LEFT JOIN (
         SELECT line_item_id, SUM(actual_qty) AS actual_qty, SUM(actual_qty * actual_rate) AS actual_amount
         FROM project_line_item_actuals
         GROUP BY line_item_id
       ) agg ON agg.line_item_id = li.id
       WHERE li.project_id = ?
       ORDER BY li.section, li.sno`,
      [projectId],
    );

    const itemIds = itemRows.map((r) => r.id);
    const historyByItem = new Map<number, HistoryRow[]>();
    if (itemIds.length > 0) {
      const [historyRows] = await pool.query<HistoryRow[]>(
        `SELECT a.id, a.line_item_id, a.period, a.actual_qty, a.actual_rate, a.etc_qty, a.etc_rate,
                a.remarks, a.approval_status, a.rejection_reason, a.approved_at, a.created_at,
                u.name AS entered_by_name, ab.name AS decided_by_name
         FROM project_line_item_actuals a
         LEFT JOIN users u ON u.id = a.entered_by
         LEFT JOIN users ab ON ab.id = a.approved_by
         WHERE a.line_item_id IN (?)
         ORDER BY a.period ASC, a.created_at ASC`,
        [itemIds],
      );
      for (const row of historyRows) {
        const bucket = historyByItem.get(row.line_item_id) ?? [];
        bucket.push(row);
        historyByItem.set(row.line_item_id, bucket);
      }
    }

    const lineRejections = await rejectionsByActual(
      'line_item',
      [...historyByItem.values()].flat().map((h) => h.id),
    );

    const bySection = (key: LineItemSection) =>
      itemRows
        .filter((r) => r.section === key)
        .map((r) => {
          const qty = Number(r.qty);
          const revisedRate = r.revised_rate === null ? null : Number(r.revised_rate);
          const actualQty = Number(r.actual_qty);
          const actualAmount = Number(r.actual_amount);
          const history = historyByItem.get(r.id) ?? [];
          const latest = history[history.length - 1];
          const etcQty = latest ? Number(latest.etc_qty) : 0;
          const etcRate = latest ? Number(latest.etc_rate) : 0;
          const etcAmount = etcQty * etcRate;
          const aceAmount = revisedRate === null ? null : qty * revisedRate;
          const eacAmount = actualAmount + etcAmount;
          const balanceQty = qty - actualQty;
          const balanceAmount = aceAmount === null ? 0 : aceAmount - actualAmount;
          const vac = aceAmount === null ? 0 : aceAmount - eacAmount;
          // PM can't open a new month's entry while the previous one is still
          // pending/rejected — mirrors the server-side gate in submitActual.
          const awaitingApproval = !!latest && latest.approval_status !== 'approved';

          return {
            id: r.id,
            description: r.description,
            unit: r.unit,
            qty,
            awaitingRevision: revisedRate === null,
            aceRate: revisedRate,
            aceAmount,
            actualQty,
            actualAmount,
            balanceQty,
            balanceAmount,
            etcQty,
            etcRate,
            etcAmount,
            eacAmount,
            vac,
            isCompleted: !!r.is_completed,
            awaitingApproval,
            // Each month's earlier rejections (read-only, archived) come just before its current entry
            history: history.flatMap((h) => [
              ...earlierRejections(lineRejections.get(h.id), h.approval_status).map((r) => ({
                id: h.id,
                archived: true,
                period: r.period,
                actualQty: Number(r.actual_qty ?? 0),
                actualRate: Number(r.actual_rate ?? 0),
                actualAmount: Number(r.actual_amount),
                etcQty: Number(r.etc_qty ?? 0),
                etcRate: Number(r.etc_rate ?? 0),
                etcAmount: Number(r.etc_amount),
                remarks: r.remarks,
                approvalStatus: 'rejected' as ApprovalStatus,
                rejectionReason: r.rejection_reason,
                decidedBy: r.rejected_by_name,
                decidedAt: r.rejected_at,
                enteredBy: r.entered_by_name,
                createdAt: r.rejected_at,
              })),
              {
                id: h.id,
                archived: false,
                period: h.period,
                actualQty: Number(h.actual_qty),
                actualRate: Number(h.actual_rate),
                actualAmount: Number(h.actual_qty) * Number(h.actual_rate),
                etcQty: Number(h.etc_qty),
                etcRate: Number(h.etc_rate),
                etcAmount: Number(h.etc_qty) * Number(h.etc_rate),
                remarks: h.remarks,
                approvalStatus: h.approval_status,
                rejectionReason: h.rejection_reason,
                decidedBy: h.approval_status === 'pending' ? null : h.decided_by_name,
                decidedAt: h.approval_status === 'pending' ? null : h.approved_at,
                enteredBy: h.entered_by_name,
                createdAt: h.created_at,
              },
            ]),
          };
        });

    const [overheadRows] = await pool.query<OverheadActualsRow[]>(
      `SELECT o.id, cat.item_code AS category_code, cat.description AS category_description, o.sub_category,
              o.ctc_per_month, o.nos, o.month, o.revised_ctc_per_month, o.is_completed,
              COALESCE(agg.actual_amount, 0) AS actual_amount
       FROM project_overheads o
       LEFT JOIN categories cat ON cat.id = o.category_id
       LEFT JOIN (
         SELECT overhead_id, SUM(actual_amount) AS actual_amount
         FROM project_overhead_actuals
         GROUP BY overhead_id
       ) agg ON agg.overhead_id = o.id
       WHERE o.project_id = ?
       ORDER BY o.sno`,
      [projectId],
    );

    const overheadIds = overheadRows.map((r) => r.id);
    const overheadHistoryById = new Map<number, OverheadHistoryRow[]>();
    if (overheadIds.length > 0) {
      const [historyRows] = await pool.query<OverheadHistoryRow[]>(
        `SELECT a.id, a.overhead_id, a.period, a.actual_amount, a.etc_amount,
                a.remarks, a.approval_status, a.rejection_reason, a.approved_at, a.created_at,
                u.name AS entered_by_name, ab.name AS decided_by_name
         FROM project_overhead_actuals a
         LEFT JOIN users u ON u.id = a.entered_by
         LEFT JOIN users ab ON ab.id = a.approved_by
         WHERE a.overhead_id IN (?)
         ORDER BY a.period ASC, a.created_at ASC`,
        [overheadIds],
      );
      for (const row of historyRows) {
        const bucket = overheadHistoryById.get(row.overhead_id) ?? [];
        bucket.push(row);
        overheadHistoryById.set(row.overhead_id, bucket);
      }
    }

    const overheadRejections = await rejectionsByActual(
      'overhead',
      [...overheadHistoryById.values()].flat().map((h) => h.id),
    );

    const overheads = overheadRows.map((r) => {
      const revisedCtc = r.revised_ctc_per_month === null ? null : Number(r.revised_ctc_per_month);
      const nos = Number(r.nos);
      const month = Number(r.month);
      const actualAmount = Number(r.actual_amount);
      const history = overheadHistoryById.get(r.id) ?? [];
      const latest = history[history.length - 1];
      const etcAmount = latest ? Number(latest.etc_amount) : 0;
      const aceAmount = revisedCtc === null ? null : revisedCtc * nos * month;
      const eacAmount = actualAmount + etcAmount;
      const balanceAmount = aceAmount === null ? 0 : aceAmount - actualAmount;
      const vac = aceAmount === null ? 0 : aceAmount - eacAmount;
      const awaitingApproval = !!latest && latest.approval_status !== 'approved';

      return {
        id: r.id,
        categoryDescription: r.category_description,
        categoryCode: r.category_code,
        subCategory: r.sub_category ?? '',
        nos,
        month,
        revisedCtcPerMonth: revisedCtc,
        awaitingRevision: revisedCtc === null,
        aceAmount,
        actualAmount,
        balanceAmount,
        etcAmount,
        eacAmount,
        vac,
        isCompleted: !!r.is_completed,
        awaitingApproval,
        // Each month's earlier rejections (read-only, archived) come just before its current entry
        history: history.flatMap((h) => [
          ...earlierRejections(overheadRejections.get(h.id), h.approval_status).map((rj) => ({
            id: h.id,
            archived: true,
            period: rj.period,
            actualAmount: Number(rj.actual_amount),
            etcAmount: Number(rj.etc_amount),
            remarks: rj.remarks,
            approvalStatus: 'rejected' as ApprovalStatus,
            rejectionReason: rj.rejection_reason,
            decidedBy: rj.rejected_by_name,
            decidedAt: rj.rejected_at,
            enteredBy: rj.entered_by_name,
            createdAt: rj.rejected_at,
          })),
          {
            id: h.id,
            archived: false,
            period: h.period,
            actualAmount: Number(h.actual_amount),
            etcAmount: Number(h.etc_amount),
            remarks: h.remarks,
            approvalStatus: h.approval_status,
            rejectionReason: h.rejection_reason,
            decidedBy: h.approval_status === 'pending' ? null : h.decided_by_name,
            decidedAt: h.approval_status === 'pending' ? null : h.approved_at,
            enteredBy: h.entered_by_name,
            createdAt: h.created_at,
          },
        ]),
      };
    });

    res.json({
      supply: bySection('supply'),
      civil: bySection('civil'),
      installation: bySection('installation'),
      tc: bySection('tc'),
      overheads,
    });
  } catch (err) {
    next(err);
  }
}

/**
 * JCR status is driven entirely by line-item progress, not a manual control:
 * the first actuals entry on any item moves it to Inprogress, and once every
 * item in the project is marked Completed it flips to Completed automatically.
 */
async function recomputeJcrStatus(projectId: number) {
  const [lineRows] = await pool.query<RowDataPacket[]>(
    'SELECT is_completed FROM project_line_items WHERE project_id = ?',
    [projectId],
  );
  const [overheadRows] = await pool.query<RowDataPacket[]>(
    'SELECT is_completed FROM project_overheads WHERE project_id = ?',
    [projectId],
  );
  const rows = [...lineRows, ...overheadRows];
  if (rows.length === 0) return;
  const allCompleted = rows.every((r) => !!r.is_completed);
  await pool.query(
    `INSERT INTO jcr (project_id, status) VALUES (?, ?)
     ON DUPLICATE KEY UPDATE status = VALUES(status)`,
    [projectId, allCompleted ? 'completed' : 'inprogress'],
  );
}

async function notifyAdvisorOfPendingActual(projectId: number, enteredByUserId?: number) {
  const [rows] = await pool.query<RowDataPacket[]>('SELECT name FROM projects WHERE id = ?', [projectId]);
  const name = rows[0]?.name ?? `Project #${projectId}`;
  await notifyRole(
    'Advisor',
    {
      type: 'actuals_pending_approval',
      title: 'Actuals & ETC awaiting approval',
      message: `A new monthly entry for "${name}" needs your approval.`,
      link: `/jcr/${projectId}`,
      relatedProjectId: projectId,
    },
    enteredByUserId,
  );
}

// ETC is compared against the remaining plan (Balance = ACE − Actual to date),
// not the total ACE — Balance and ETC are both "what's left" in the same units,
// so a mismatch there is the meaningful anomaly to flag with a remark.
const AMOUNT_EPSILON = 0.01;

export async function submitActual(req: AuthRequest, res: Response, next: NextFunction) {
  const projectId = Number(req.params.projectId);
  const { lineItemId, period, actualQty, actualRate, etcQty, etcRate, completed, remarks } = req.body ?? {};

  try {
    if (!(await hasPermission(req.userId!, 'jcr', 'canEnterActuals'))) {
      return res.status(403).json({ message: 'You do not have permission to enter actuals.' });
    }

    const [itemRows] = await pool.query<RowDataPacket[]>(
      'SELECT id, qty, revised_rate, is_completed FROM project_line_items WHERE id = ? AND project_id = ?',
      [lineItemId, projectId],
    );
    const item = itemRows[0];
    if (!item) return res.status(404).json({ message: 'Line item not found' });
    if (item.is_completed) {
      return res.status(400).json({ message: 'This line item is already marked completed.' });
    }
    if (item.revised_rate === null) {
      return res
        .status(400)
        .json({ message: 'This line item is awaiting a revised ACE before actuals can be entered.' });
    }
    if (
      !period ||
      !(Number(actualQty) >= 0) ||
      !(Number(actualRate) >= 0) ||
      !(Number(etcQty) >= 0) ||
      !(Number(etcRate) >= 0)
    ) {
      return res
        .status(400)
        .json({ message: 'period, actualQty, actualRate, etcQty and etcRate are required.' });
    }

    // A new month's entry can't be submitted while the immediately-preceding
    // one is still pending/rejected — the advisor must clear it first.
    const [priorRows] = await pool.query<RowDataPacket[]>(
      'SELECT approval_status FROM project_line_item_actuals WHERE line_item_id = ? ORDER BY period DESC LIMIT 1',
      [lineItemId],
    );
    const priorStatus = priorRows[0]?.approval_status as ApprovalStatus | undefined;
    if (priorStatus === 'pending') {
      return res.status(400).json({ message: "Previous month's entry is awaiting advisor approval." });
    }
    if (priorStatus === 'rejected') {
      return res
        .status(400)
        .json({ message: "Previous month's entry was rejected — resubmit that entry before continuing." });
    }

    // The allocated quantity is fixed — monthly entries record progress against
    // it, they never grow the total. Reject anything that would push cumulative
    // Actual Qty past what was actually allocated for this item.
    const [sumRows] = await pool.query<RowDataPacket[]>(
      'SELECT COALESCE(SUM(actual_qty), 0) AS cumulative_qty, COALESCE(SUM(actual_qty * actual_rate), 0) AS cumulative_amount FROM project_line_item_actuals WHERE line_item_id = ?',
      [lineItemId],
    );
    const cumulativeSoFar = Number(sumRows[0]?.cumulative_qty ?? 0);
    const cumulativeAmountSoFar = Number(sumRows[0]?.cumulative_amount ?? 0);
    const allocatedQty = Number(item.qty);
    if (cumulativeSoFar + Number(actualQty) > allocatedQty + 1e-9) {
      return res.status(400).json({
        message: `Actual quantity cannot exceed the allocated quantity. Allocated ${allocatedQty}, already recorded ${cumulativeSoFar}, remaining ${allocatedQty - cumulativeSoFar}.`,
      });
    }

    // Remarks are required whenever this month's ETC diverges from the
    // remaining plan (Balance) — an unexplained forecast swing needs context
    // for the advisor before it can be approved.
    const aceAmount = Number(item.revised_rate) * allocatedQty;
    const balanceAmountBefore = aceAmount - cumulativeAmountSoFar;
    const etcAmount = Number(etcQty) * Number(etcRate);
    const etcDiffersFromBalance = Math.abs(etcAmount - balanceAmountBefore) > AMOUNT_EPSILON;
    if (etcDiffersFromBalance && !(typeof remarks === 'string' && remarks.trim())) {
      return res.status(400).json({
        message: 'Remarks are required when the ETC amount differs from the remaining plan.',
      });
    }

    // Mark Completed only makes sense once there's nothing left to forecast.
    if (completed && etcAmount > 1e-9) {
      return res
        .status(400)
        .json({ message: 'ETC must be fully worked down to 0 before marking this item Completed.' });
    }

    // Only one entry per line item per month — re-opening a month means editing
    // that entry, not adding a second one on top of it.
    const [existingRows] = await pool.query<RowDataPacket[]>(
      'SELECT id FROM project_line_item_actuals WHERE line_item_id = ? AND period = ?',
      [lineItemId, period],
    );
    if (existingRows[0]) {
      return res.status(400).json({ message: 'An entry for this month has already been recorded for this line item.' });
    }

    await pool.query(
      `INSERT INTO project_line_item_actuals (line_item_id, period, actual_qty, actual_rate, etc_qty, etc_rate, remarks, entered_by)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
      [lineItemId, period, actualQty, actualRate, etcQty, etcRate, remarks || null, req.userId],
    );

    if (completed) {
      await pool.query('UPDATE project_line_items SET is_completed = 1 WHERE id = ?', [lineItemId]);
    }

    await recomputeJcrStatus(projectId);
    notifyAdvisorOfPendingActual(projectId, req.userId).catch(() => {});

    res.status(201).json({ ok: true });
  } catch (err) {
    if (mysqlErrorCode(err) === 'ER_DUP_ENTRY') {
      return res.status(400).json({ message: 'An entry for this month has already been recorded for this line item.' });
    }
    next(err);
  }
}

export async function submitOverheadActual(req: AuthRequest, res: Response, next: NextFunction) {
  const projectId = Number(req.params.projectId);
  const { overheadId, period, actualAmount, etcAmount, completed, remarks } = req.body ?? {};

  try {
    if (!(await hasPermission(req.userId!, 'jcr', 'canEnterActuals'))) {
      return res.status(403).json({ message: 'You do not have permission to enter actuals.' });
    }

    const [overheadRows] = await pool.query<RowDataPacket[]>(
      'SELECT id, ctc_per_month, nos, month, revised_ctc_per_month, is_completed FROM project_overheads WHERE id = ? AND project_id = ?',
      [overheadId, projectId],
    );
    const overhead = overheadRows[0];
    if (!overhead) return res.status(404).json({ message: 'Overhead not found' });
    if (overhead.is_completed) {
      return res.status(400).json({ message: 'This overhead is already marked completed.' });
    }
    if (overhead.revised_ctc_per_month === null) {
      return res
        .status(400)
        .json({ message: 'This overhead is awaiting a revised ACE before actuals can be entered.' });
    }
    if (!period || !(Number(actualAmount) >= 0) || !(Number(etcAmount) >= 0)) {
      return res.status(400).json({ message: 'period, actualAmount and etcAmount are required.' });
    }

    // A new month's entry can't be submitted while the immediately-preceding
    // one is still pending/rejected — the advisor must clear it first.
    const [priorRows] = await pool.query<RowDataPacket[]>(
      'SELECT approval_status FROM project_overhead_actuals WHERE overhead_id = ? ORDER BY period DESC LIMIT 1',
      [overheadId],
    );
    const priorStatus = priorRows[0]?.approval_status as ApprovalStatus | undefined;
    if (priorStatus === 'pending') {
      return res.status(400).json({ message: "Previous month's entry is awaiting advisor approval." });
    }
    if (priorStatus === 'rejected') {
      return res
        .status(400)
        .json({ message: "Previous month's entry was rejected — resubmit that entry before continuing." });
    }

    // Same fixed-allocation rule as line items: the revised budget is the ceiling,
    // monthly spend entries record progress against it and never grow the total.
    const [sumRows] = await pool.query<RowDataPacket[]>(
      'SELECT COALESCE(SUM(actual_amount), 0) AS cumulative FROM project_overhead_actuals WHERE overhead_id = ?',
      [overheadId],
    );
    const cumulativeSoFar = Number(sumRows[0]?.cumulative ?? 0);
    const aceAmount = Number(overhead.revised_ctc_per_month) * Number(overhead.nos) * Number(overhead.month);
    if (cumulativeSoFar + Number(actualAmount) > aceAmount + 1e-9) {
      return res.status(400).json({
        message: `Actual amount cannot exceed the revised budget. Budget ₹${aceAmount}, already recorded ₹${cumulativeSoFar}, remaining ₹${aceAmount - cumulativeSoFar}.`,
      });
    }

    // Remarks required whenever ETC diverges from the remaining plan (Balance).
    const balanceAmountBefore = aceAmount - cumulativeSoFar;
    const etcDiffersFromBalance = Math.abs(Number(etcAmount) - balanceAmountBefore) > AMOUNT_EPSILON;
    if (etcDiffersFromBalance && !(typeof remarks === 'string' && remarks.trim())) {
      return res.status(400).json({
        message: 'Remarks are required when the ETC amount differs from the remaining plan.',
      });
    }

    if (completed && Number(etcAmount) > 1e-9) {
      return res
        .status(400)
        .json({ message: 'ETC must be fully worked down to 0 before marking this item Completed.' });
    }

    // Only one entry per overhead per month.
    const [existingRows] = await pool.query<RowDataPacket[]>(
      'SELECT id FROM project_overhead_actuals WHERE overhead_id = ? AND period = ?',
      [overheadId, period],
    );
    if (existingRows[0]) {
      return res.status(400).json({ message: 'An entry for this month has already been recorded for this overhead.' });
    }

    await pool.query(
      `INSERT INTO project_overhead_actuals (overhead_id, period, actual_amount, etc_amount, remarks, entered_by)
       VALUES (?, ?, ?, ?, ?, ?)`,
      [overheadId, period, actualAmount, etcAmount, remarks || null, req.userId],
    );

    if (completed) {
      await pool.query('UPDATE project_overheads SET is_completed = 1 WHERE id = ?', [overheadId]);
    }

    await recomputeJcrStatus(projectId);
    notifyAdvisorOfPendingActual(projectId, req.userId).catch(() => {});

    res.status(201).json({ ok: true });
  } catch (err) {
    if (mysqlErrorCode(err) === 'ER_DUP_ENTRY') {
      return res.status(400).json({ message: 'An entry for this month has already been recorded for this overhead.' });
    }
    next(err);
  }
}

// Lets the PM correct an entry they got wrong before the Advisor has acted on
// it — allowed while the entry is 'pending' (not yet reviewed) or 'rejected'
// (this doubles as the "resubmit that entry" path referenced by the rejection
// message in submitActual). Once 'approved', the entry is a closed record.
export async function updateActual(req: AuthRequest, res: Response, next: NextFunction) {
  const projectId = Number(req.params.projectId);
  const actualId = Number(req.params.actualId);
  const { actualQty, actualRate, etcQty, etcRate, completed, remarks } = req.body ?? {};

  try {
    if (!(await hasPermission(req.userId!, 'jcr', 'canEnterActuals'))) {
      return res.status(403).json({ message: 'You do not have permission to enter actuals.' });
    }

    const [rows] = await pool.query<RowDataPacket[]>(
      `SELECT a.id, a.line_item_id, a.approval_status, li.qty AS item_qty, li.revised_rate
       FROM project_line_item_actuals a
       JOIN project_line_items li ON li.id = a.line_item_id
       WHERE a.id = ? AND li.project_id = ?`,
      [actualId, projectId],
    );
    const row = rows[0];
    if (!row) return res.status(404).json({ message: 'Actuals entry not found' });
    if (row.approval_status === 'approved') {
      return res.status(400).json({ message: 'This entry has already been approved and can no longer be edited.' });
    }
    if (row.revised_rate === null) {
      return res.status(400).json({ message: 'This line item is awaiting a revised ACE before actuals can be entered.' });
    }
    if (!(Number(actualQty) >= 0) || !(Number(actualRate) >= 0) || !(Number(etcQty) >= 0) || !(Number(etcRate) >= 0)) {
      return res.status(400).json({ message: 'actualQty, actualRate, etcQty and etcRate are required.' });
    }

    const lineItemId = row.line_item_id as number;
    const [sumRows] = await pool.query<RowDataPacket[]>(
      `SELECT COALESCE(SUM(actual_qty), 0) AS cumulative_qty, COALESCE(SUM(actual_qty * actual_rate), 0) AS cumulative_amount
       FROM project_line_item_actuals WHERE line_item_id = ? AND id != ?`,
      [lineItemId, actualId],
    );
    const cumulativeSoFar = Number(sumRows[0]?.cumulative_qty ?? 0);
    const cumulativeAmountSoFar = Number(sumRows[0]?.cumulative_amount ?? 0);
    const allocatedQty = Number(row.item_qty);
    if (cumulativeSoFar + Number(actualQty) > allocatedQty + 1e-9) {
      return res.status(400).json({
        message: `Actual quantity cannot exceed the allocated quantity. Allocated ${allocatedQty}, already recorded ${cumulativeSoFar}, remaining ${allocatedQty - cumulativeSoFar}.`,
      });
    }

    const aceAmount = Number(row.revised_rate) * allocatedQty;
    const balanceAmountBefore = aceAmount - cumulativeAmountSoFar;
    const etcAmount = Number(etcQty) * Number(etcRate);
    if (Math.abs(etcAmount - balanceAmountBefore) > AMOUNT_EPSILON && !(typeof remarks === 'string' && remarks.trim())) {
      return res.status(400).json({ message: 'Remarks are required when the ETC amount differs from the remaining plan.' });
    }
    if (completed && etcAmount > 1e-9) {
      return res
        .status(400)
        .json({ message: 'ETC must be fully worked down to 0 before marking this item Completed.' });
    }

    await pool.query(
      `UPDATE project_line_item_actuals
       SET actual_qty = ?, actual_rate = ?, etc_qty = ?, etc_rate = ?, remarks = ?,
           approval_status = 'pending', approved_by = NULL, approved_at = NULL, rejection_reason = NULL
       WHERE id = ?`,
      [actualQty, actualRate, etcQty, etcRate, remarks || null, actualId],
    );
    await pool.query('UPDATE project_line_items SET is_completed = ? WHERE id = ?', [completed ? 1 : 0, lineItemId]);

    await recomputeJcrStatus(projectId);
    notifyAdvisorOfPendingActual(projectId, req.userId).catch(() => {});

    res.json({ ok: true });
  } catch (err) {
    next(err);
  }
}

export async function updateOverheadActual(req: AuthRequest, res: Response, next: NextFunction) {
  const projectId = Number(req.params.projectId);
  const actualId = Number(req.params.actualId);
  const { actualAmount, etcAmount, completed, remarks } = req.body ?? {};

  try {
    if (!(await hasPermission(req.userId!, 'jcr', 'canEnterActuals'))) {
      return res.status(403).json({ message: 'You do not have permission to enter actuals.' });
    }

    const [rows] = await pool.query<RowDataPacket[]>(
      `SELECT a.id, a.overhead_id, a.approval_status, o.ctc_per_month, o.nos, o.month, o.revised_ctc_per_month
       FROM project_overhead_actuals a
       JOIN project_overheads o ON o.id = a.overhead_id
       WHERE a.id = ? AND o.project_id = ?`,
      [actualId, projectId],
    );
    const row = rows[0];
    if (!row) return res.status(404).json({ message: 'Actuals entry not found' });
    if (row.approval_status === 'approved') {
      return res.status(400).json({ message: 'This entry has already been approved and can no longer be edited.' });
    }
    if (row.revised_ctc_per_month === null) {
      return res.status(400).json({ message: 'This overhead is awaiting a revised ACE before actuals can be entered.' });
    }
    if (!(Number(actualAmount) >= 0) || !(Number(etcAmount) >= 0)) {
      return res.status(400).json({ message: 'actualAmount and etcAmount are required.' });
    }

    const overheadId = row.overhead_id as number;
    const [sumRows] = await pool.query<RowDataPacket[]>(
      `SELECT COALESCE(SUM(actual_amount), 0) AS cumulative FROM project_overhead_actuals WHERE overhead_id = ? AND id != ?`,
      [overheadId, actualId],
    );
    const cumulativeSoFar = Number(sumRows[0]?.cumulative ?? 0);
    const aceAmount = Number(row.revised_ctc_per_month) * Number(row.nos) * Number(row.month);
    if (cumulativeSoFar + Number(actualAmount) > aceAmount + 1e-9) {
      return res.status(400).json({
        message: `Actual amount cannot exceed the revised budget. Budget ₹${aceAmount}, already recorded ₹${cumulativeSoFar}, remaining ₹${aceAmount - cumulativeSoFar}.`,
      });
    }

    const balanceAmountBefore = aceAmount - cumulativeSoFar;
    if (
      Math.abs(Number(etcAmount) - balanceAmountBefore) > AMOUNT_EPSILON &&
      !(typeof remarks === 'string' && remarks.trim())
    ) {
      return res.status(400).json({ message: 'Remarks are required when the ETC amount differs from the remaining plan.' });
    }
    if (completed && Number(etcAmount) > 1e-9) {
      return res
        .status(400)
        .json({ message: 'ETC must be fully worked down to 0 before marking this item Completed.' });
    }

    await pool.query(
      `UPDATE project_overhead_actuals
       SET actual_amount = ?, etc_amount = ?, remarks = ?,
           approval_status = 'pending', approved_by = NULL, approved_at = NULL, rejection_reason = NULL
       WHERE id = ?`,
      [actualAmount, etcAmount, remarks || null, actualId],
    );
    await pool.query('UPDATE project_overheads SET is_completed = ? WHERE id = ?', [completed ? 1 : 0, overheadId]);

    await recomputeJcrStatus(projectId);
    notifyAdvisorOfPendingActual(projectId, req.userId).catch(() => {});

    res.json({ ok: true });
  } catch (err) {
    next(err);
  }
}

function parseDecision(body: unknown): { decision: 'approved' | 'rejected'; reason?: string } | null {
  const decision = (body as { decision?: unknown })?.decision;
  const reason = (body as { reason?: unknown })?.reason;
  if (decision !== 'approved' && decision !== 'rejected') return null;
  return { decision, reason: typeof reason === 'string' ? reason : undefined };
}

async function notifyPmOfApproval(projectId: number) {
  const [rows] = await pool.query<RowDataPacket[]>(
    'SELECT project_manager_id, name FROM projects WHERE id = ?',
    [projectId],
  );
  const pmId = rows[0]?.project_manager_id as number | null | undefined;
  if (!pmId) return;
  await notifyUsers([pmId], {
    type: 'actuals_approved',
    title: 'Actuals & ETC entry approved',
    message: `Your monthly entry for "${rows[0].name}" was approved by the Advisor.`,
    link: `/jcr/${projectId}`,
    relatedProjectId: projectId,
  });
}

export async function decideLineItemActual(req: AuthRequest, res: Response, next: NextFunction) {
  const projectId = Number(req.params.projectId);
  const actualId = Number(req.params.actualId);
  const parsed = parseDecision(req.body);

  try {
    if (!(await hasPermission(req.userId!, 'jcr', 'canApprove'))) {
      return res.status(403).json({ message: 'You do not have permission to approve actuals entries.' });
    }
    if (!parsed) {
      return res.status(400).json({ message: "decision must be 'approved' or 'rejected'." });
    }
    if (parsed.decision === 'rejected' && !parsed.reason?.trim()) {
      return res.status(400).json({ message: 'A rejection reason is required.' });
    }

    const [rows] = await pool.query<RowDataPacket[]>(
      `SELECT a.id FROM project_line_item_actuals a
       JOIN project_line_items li ON li.id = a.line_item_id
       WHERE a.id = ? AND li.project_id = ?`,
      [actualId, projectId],
    );
    if (!rows[0]) return res.status(404).json({ message: 'Actuals entry not found' });

    await pool.query(
      `UPDATE project_line_item_actuals
       SET approval_status = ?, approved_by = ?, approved_at = NOW(), rejection_reason = ?
       WHERE id = ?`,
      [parsed.decision, req.userId, parsed.decision === 'rejected' ? parsed.reason : null, actualId],
    );

    if (parsed.decision === 'rejected') {
      await logRejection('line_item', actualId);
    } else {
      notifyPmOfApproval(projectId).catch(() => {});
    }

    res.json({ ok: true });
  } catch (err) {
    next(err);
  }
}

export async function decideOverheadActual(req: AuthRequest, res: Response, next: NextFunction) {
  const projectId = Number(req.params.projectId);
  const actualId = Number(req.params.actualId);
  const parsed = parseDecision(req.body);

  try {
    if (!(await hasPermission(req.userId!, 'jcr', 'canApprove'))) {
      return res.status(403).json({ message: 'You do not have permission to approve actuals entries.' });
    }
    if (!parsed) {
      return res.status(400).json({ message: "decision must be 'approved' or 'rejected'." });
    }
    if (parsed.decision === 'rejected' && !parsed.reason?.trim()) {
      return res.status(400).json({ message: 'A rejection reason is required.' });
    }

    const [rows] = await pool.query<RowDataPacket[]>(
      `SELECT a.id FROM project_overhead_actuals a
       JOIN project_overheads o ON o.id = a.overhead_id
       WHERE a.id = ? AND o.project_id = ?`,
      [actualId, projectId],
    );
    if (!rows[0]) return res.status(404).json({ message: 'Actuals entry not found' });

    await pool.query(
      `UPDATE project_overhead_actuals
       SET approval_status = ?, approved_by = ?, approved_at = NOW(), rejection_reason = ?
       WHERE id = ?`,
      [parsed.decision, req.userId, parsed.decision === 'rejected' ? parsed.reason : null, actualId],
    );

    if (parsed.decision === 'rejected') {
      await logRejection('overhead', actualId);
    } else {
      notifyPmOfApproval(projectId).catch(() => {});
    }

    res.json({ ok: true });
  } catch (err) {
    next(err);
  }
}