import { NextFunction, Request, Response } from 'express';
import { RowDataPacket } from 'mysql2/promise';
import { pool } from '../config/db.js';
import { computeProjectRollup } from './jcrReport.controller.js';

const SECTIONS: { key: 'supply' | 'civil' | 'installation' | 'tc'; label: string }[] = [
  { key: 'supply', label: 'Supply' },
  { key: 'civil', label: 'Civil' },
  { key: 'installation', label: 'Installation' },
  { key: 'tc', label: 'T & C' },
];

interface SectionTotalRow extends RowDataPacket {
  section: string;
  total: string;
}

interface TotalRow extends RowDataPacket {
  total: string;
}

interface ProjectIdRow extends RowDataPacket {
  id: number;
}

interface ProjectBasicRow extends RowDataPacket {
  id: number;
  job_code: string | null;
  name: string;
}

interface StatusCountRow extends RowDataPacket {
  approval_status: string;
  total: number;
}

interface ProjectAmountRow extends RowDataPacket {
  project_id: number;
  amount: string | number;
}

interface MonthlyRow extends RowDataPacket {
  ym: string;
  total: string;
}

function clampPercent(actual: number, ace: number) {
  if (ace <= 0) return 0;
  return Math.max(0, Math.min(100, (actual / ace) * 100));
}

async function fetchCostBreakdown() {
  const [aceRows] = await pool.query<SectionTotalRow[]>(
    `SELECT section, SUM(qty * ace_rate) AS total FROM project_line_items GROUP BY section`,
  );
  const [actualRows] = await pool.query<SectionTotalRow[]>(
    `SELECT li.section AS section, SUM(a.actual_qty * a.actual_rate) AS total
     FROM project_line_item_actuals a
     JOIN project_line_items li ON li.id = a.line_item_id
     GROUP BY li.section`,
  );
  const [[overheadAce]] = await pool.query<TotalRow[]>(
    `SELECT COALESCE(SUM(ctc_per_month * nos * month), 0) AS total FROM project_overheads`,
  );
  const [[overheadActual]] = await pool.query<TotalRow[]>(
    `SELECT COALESCE(SUM(actual_amount), 0) AS total FROM project_overhead_actuals`,
  );

  const aceBySection = new Map(aceRows.map((r) => [r.section, Number(r.total)]));
  const actualBySection = new Map(actualRows.map((r) => [r.section, Number(r.total)]));

  const breakdown: { section: string; label: string; aceCr: number; actualCr: number; percent: number }[] =
    SECTIONS.map(({ key, label }) => {
      const ace = aceBySection.get(key) ?? 0;
      const actual = actualBySection.get(key) ?? 0;
      return { section: key, label, aceCr: ace / 1e7, actualCr: actual / 1e7, percent: clampPercent(actual, ace) };
    });

  const overheadAceTotal = Number(overheadAce.total);
  const overheadActualTotal = Number(overheadActual.total);
  breakdown.push({
    section: 'overheads',
    label: 'Overheads',
    aceCr: overheadAceTotal / 1e7,
    actualCr: overheadActualTotal / 1e7,
    percent: clampPercent(overheadActualTotal, overheadAceTotal),
  });

  return breakdown;
}

/** The same section-wise ACE vs actual breakdown as fetchCostBreakdown, for a single project (amounts in ₹). */
export async function fetchProjectCostBreakdown(projectId: number) {
  const [aceRows] = await pool.query<SectionTotalRow[]>(
    'SELECT section, SUM(qty * ace_rate) AS total FROM project_line_items WHERE project_id = ? GROUP BY section',
    [projectId],
  );
  const [actualRows] = await pool.query<SectionTotalRow[]>(
    `SELECT li.section AS section, SUM(a.actual_qty * a.actual_rate) AS total
     FROM project_line_item_actuals a JOIN project_line_items li ON li.id = a.line_item_id
     WHERE li.project_id = ? GROUP BY li.section`,
    [projectId],
  );
  const [[overheadAce]] = await pool.query<TotalRow[]>(
    'SELECT COALESCE(SUM(ctc_per_month * nos * month), 0) AS total FROM project_overheads WHERE project_id = ?',
    [projectId],
  );
  const [[overheadActual]] = await pool.query<TotalRow[]>(
    `SELECT COALESCE(SUM(a.actual_amount), 0) AS total
     FROM project_overhead_actuals a JOIN project_overheads o ON o.id = a.overhead_id
     WHERE o.project_id = ?`,
    [projectId],
  );

  const aceBySection = new Map(aceRows.map((r) => [r.section, Number(r.total)]));
  const actualBySection = new Map(actualRows.map((r) => [r.section, Number(r.total)]));
  const breakdown = SECTIONS.map(({ key, label }) => {
    const ace = aceBySection.get(key) ?? 0;
    const actual = actualBySection.get(key) ?? 0;
    return { section: key as string, label, ace, actual, percent: clampPercent(actual, ace) };
  });
  const overheadAceTotal = Number(overheadAce.total);
  const overheadActualTotal = Number(overheadActual.total);
  breakdown.push({
    section: 'overheads',
    label: 'Overheads',
    ace: overheadAceTotal,
    actual: overheadActualTotal,
    percent: clampPercent(overheadActualTotal, overheadAceTotal),
  });
  return breakdown;
}

async function fetchStats() {
  const [[projectCount]] = await pool.query<TotalRow[]>(`SELECT COUNT(*) AS total FROM projects`);
  const [[projectCountLastYear]] = await pool.query<TotalRow[]>(
    `SELECT COUNT(*) AS total FROM projects WHERE created_at <= NOW() - INTERVAL 1 YEAR`,
  );
  const [[saleValue]] = await pool.query<TotalRow[]>(
    `SELECT COALESCE(SUM(qty * sale_rate), 0) AS total FROM project_line_items`,
  );
  const [[activeUsers]] = await pool.query<TotalRow[]>(
    `SELECT COUNT(*) AS total FROM users WHERE is_active = 1`,
  );

  return {
    totalProjects: Number(projectCount.total),
    totalProjectsLastYear: Number(projectCountLastYear.total),
    totalProjectValue: Number(saleValue.total),
    totalActiveUsers: Number(activeUsers.total),
  };
}

async function fetchJcrSummary() {
  const [approvedProjects] = await pool.query<ProjectIdRow[]>(
    `SELECT id FROM projects WHERE approval_status = 'approved'`,
  );
  const [[saleValueRow]] = await pool.query<TotalRow[]>(
    `SELECT COALESCE(SUM(li.qty * li.sale_rate), 0) AS total
     FROM project_line_items li
     JOIN projects p ON p.id = li.project_id
     WHERE p.approval_status = 'approved'`,
  );

  const rollups = await Promise.all(approvedProjects.map((p) => computeProjectRollup(p.id)));
  const totals = rollups.reduce(
    (acc, r) => ({
      aceRevised: acc.aceRevised + r.aceRevised,
      actualTotal: acc.actualTotal + r.actualTotal,
      etcTotal: acc.etcTotal + r.etcTotal,
    }),
    { aceRevised: 0, actualTotal: 0, etcTotal: 0 },
  );

  const eac = totals.actualTotal + totals.etcTotal;
  const vac = totals.aceRevised - eac;

  return {
    aceRevised: totals.aceRevised,
    actualTotal: totals.actualTotal,
    etcTotal: totals.etcTotal,
    eac,
    saleValue: Number(saleValueRow.total),
    vac,
  };
}

async function fetchTopProjects(limit = 6) {
  const [rows] = await pool.query<ProjectBasicRow[]>(
    `SELECT id, job_code, name FROM projects WHERE approval_status = 'approved'`,
  );

  const rollups = await Promise.all(
    rows.map(async (p) => {
      const r = await computeProjectRollup(p.id);
      // EAC (Estimate At Completion) = Actual + ETC; VAC = ACE − EAC;
      // % Spent = Actual Spent ÷ ACE × 100 (null when there is no ACE to measure against)
      const eac = r.bac;
      const percentSpent = r.aceRevised > 0 ? (r.actualTotal / r.aceRevised) * 100 : null;
      return {
        projectId: p.id,
        jobCode: p.job_code,
        name: p.name,
        aceRevised: r.aceRevised,
        eac,
        actualTotal: r.actualTotal,
        vac: r.vac,
        vacPct: r.vacPct,
        percentSpent,
      };
    }),
  );

  return rollups.sort((a, b) => b.aceRevised - a.aceRevised).slice(0, limit);
}

async function fetchApprovalStatusCounts() {
  const [rows] = await pool.query<StatusCountRow[]>(
    `SELECT approval_status, COUNT(*) AS total FROM projects GROUP BY approval_status`,
  );

  const counts = { approved: 0, pending: 0 };
  for (const r of rows) {
    if (r.approval_status === 'approved') counts.approved += Number(r.total);
    else counts.pending += Number(r.total);
  }
  return counts;
}

/** Actual cost incurred per calendar month for the current year, combining both
 * direct line-item actuals and overhead actuals — a real, un-forecast trend
 * (no fabricated budget-pacing line, since VPMT has no time-phased budget). */
async function fetchMonthlyActuals() {
  const year = new Date().getFullYear();
  const [lineRows] = await pool.query<MonthlyRow[]>(
    `SELECT DATE_FORMAT(period, '%Y-%m') AS ym, SUM(actual_qty * actual_rate) AS total
     FROM project_line_item_actuals
     WHERE YEAR(period) = ?
     GROUP BY ym`,
    [year],
  );
  const [overheadRows] = await pool.query<MonthlyRow[]>(
    `SELECT DATE_FORMAT(period, '%Y-%m') AS ym, SUM(actual_amount) AS total
     FROM project_overhead_actuals
     WHERE YEAR(period) = ?
     GROUP BY ym`,
    [year],
  );

  const totals = new Map<string, number>();
  for (const r of [...lineRows, ...overheadRows]) {
    totals.set(r.ym, (totals.get(r.ym) ?? 0) + Number(r.total));
  }

  return Array.from({ length: 12 }, (_, i) => {
    const ym = `${year}-${String(i + 1).padStart(2, '0')}`;
    const label = new Date(year, i, 1).toLocaleDateString('en-US', { month: 'short' });
    return { month: label, actualCr: (totals.get(ym) ?? 0) / 1e7 };
  });
}

/**
 * SPI (Sales Performance Index) = Actual Progress ÷ Planned Progress, to date, across approved projects.
 * Uses the S Curve basis: planned progress is the sum of each line item's monthly plan % (of the
 * project's ACE value) up to the current month; actual progress is the line-item actuals booked
 * up to the current month. Both are converted to amounts so projects weigh by their size.
 */
async function fetchScheduleProgress() {
  const [aceRows] = await pool.query<ProjectAmountRow[]>(
    `SELECT li.project_id, SUM(li.qty * li.ace_rate) AS amount
     FROM project_line_items li JOIN projects p ON p.id = li.project_id
     WHERE p.approval_status = 'approved' GROUP BY li.project_id`,
  );
  const [planRows] = await pool.query<ProjectAmountRow[]>(
    `SELECT li.project_id, SUM(sp.plan_percent) AS amount
     FROM s_curve_plan sp
     JOIN project_line_items li ON li.id = sp.line_item_id
     JOIN projects p ON p.id = li.project_id
     WHERE p.approval_status = 'approved' AND sp.period <= LAST_DAY(CURDATE())
     GROUP BY li.project_id`,
  );
  const [actualRows] = await pool.query<ProjectAmountRow[]>(
    `SELECT li.project_id, SUM(a.actual_qty * a.actual_rate) AS amount
     FROM project_line_item_actuals a
     JOIN project_line_items li ON li.id = a.line_item_id
     JOIN projects p ON p.id = li.project_id
     WHERE p.approval_status = 'approved' AND a.period <= LAST_DAY(CURDATE())
     GROUP BY li.project_id`,
  );

  const aceByProject = new Map(aceRows.map((r) => [r.project_id, Number(r.amount)]));
  const totalAce = [...aceByProject.values()].reduce((a, b) => a + b, 0);
  // plan_percent is a % of the project's ACE value, so weigh it by that project's ACE.
  const plannedAmount = planRows.reduce((sum, r) => sum + (Number(r.amount) / 100) * (aceByProject.get(r.project_id) ?? 0), 0);
  const actualAmount = actualRows.reduce((sum, r) => sum + Number(r.amount), 0);

  return {
    plannedProgressPct: totalAce > 0 ? (plannedAmount / totalAce) * 100 : 0,
    actualProgressPct: totalAce > 0 ? (actualAmount / totalAce) * 100 : 0,
    spi: plannedAmount > 0 ? actualAmount / plannedAmount : null,
  };
}

/** The same S Curve schedule basis as fetchScheduleProgress, for a single project. */
export async function fetchProjectScheduleProgress(projectId: number) {
  const [[aceRow]] = await pool.query<ProjectAmountRow[]>(
    'SELECT ? AS project_id, SUM(qty * ace_rate) AS amount FROM project_line_items WHERE project_id = ?',
    [projectId, projectId],
  );
  const [[planRow]] = await pool.query<ProjectAmountRow[]>(
    `SELECT ? AS project_id, SUM(sp.plan_percent) AS amount
     FROM s_curve_plan sp JOIN project_line_items li ON li.id = sp.line_item_id
     WHERE li.project_id = ? AND sp.period <= LAST_DAY(CURDATE())`,
    [projectId, projectId],
  );
  const [[actualRow]] = await pool.query<ProjectAmountRow[]>(
    `SELECT ? AS project_id, SUM(a.actual_qty * a.actual_rate) AS amount
     FROM project_line_item_actuals a JOIN project_line_items li ON li.id = a.line_item_id
     WHERE li.project_id = ? AND a.period <= LAST_DAY(CURDATE())`,
    [projectId, projectId],
  );

  const ace = Number(aceRow?.amount ?? 0);
  const plannedAmount = (Number(planRow?.amount ?? 0) / 100) * ace;
  const actualAmount = Number(actualRow?.amount ?? 0);
  return {
    plannedProgressPct: ace > 0 ? (plannedAmount / ace) * 100 : 0,
    actualProgressPct: ace > 0 ? (actualAmount / ace) * 100 : 0,
    spi: plannedAmount > 0 ? actualAmount / plannedAmount : null,
  };
}

/**
 * Earned Value and Actual Cost to date, across approved projects (or for one project).
 *  - Line items: EV = actual qty × budgeted rate (revised ACE rate, or the ACE rate until revised);
 *    AC = actual qty × actual rate.
 *  - Overheads have no quantity to measure progress by, so their earned value equals what was spent.
 */
export async function fetchEarnedValue(projectId?: number) {
  // All approved projects, or just the one project when an id is given
  const scope = projectId === undefined ? `p.approval_status = 'approved'` : 'p.id = ?';
  const params = projectId === undefined ? [] : [projectId];
  const [[lineRow]] = await pool.query<TotalRow[]>(
    `SELECT COALESCE(SUM(a.actual_qty * COALESCE(li.revised_rate, li.ace_rate)), 0) AS earned,
            COALESCE(SUM(a.actual_qty * a.actual_rate), 0) AS total
     FROM project_line_item_actuals a
     JOIN project_line_items li ON li.id = a.line_item_id
     JOIN projects p ON p.id = li.project_id
     WHERE ${scope}`,
    params,
  );
  const [[overheadRow]] = await pool.query<TotalRow[]>(
    `SELECT COALESCE(SUM(a.actual_amount), 0) AS total
     FROM project_overhead_actuals a
     JOIN project_overheads o ON o.id = a.overhead_id
     JOIN projects p ON p.id = o.project_id
     WHERE ${scope}`,
    params,
  );
  const overheadActual = Number(overheadRow.total);
  return {
    earnedValue: Number((lineRow as TotalRow & { earned: string | number }).earned) + overheadActual,
    actualCost: Number(lineRow.total) + overheadActual,
  };
}

export async function summary(_req: Request, res: Response, next: NextFunction) {
  try {
    const [stats, costBreakdown, jcr, topProjects, approvalStatusCounts, monthlyActuals, schedule, earned] = await Promise.all([
      fetchStats(),
      fetchCostBreakdown(),
      fetchJcrSummary(),
      fetchTopProjects(),
      fetchApprovalStatusCounts(),
      fetchMonthlyActuals(),
      fetchScheduleProgress(),
      fetchEarnedValue(),
    ]);
    // CPI (Cost Performance Index) = Earned Value ÷ Actual Cost — above 1 is under budget.
    const performance = {
      cpi: earned.actualCost > 0 ? earned.earnedValue / earned.actualCost : null,
      earnedValue: earned.earnedValue,
      actualCost: earned.actualCost,
      ...schedule,
    };
    res.json({ stats, costBreakdown, jcr, topProjects, approvalStatusCounts, monthlyActuals, performance });
  } catch (err) {
    next(err);
  }
}