import { NextFunction, Request, Response } from 'express';
import { RowDataPacket } from 'mysql2/promise';
import { pool } from '../config/db.js';
import { LINE_ITEM_SECTIONS, SECTION_LABELS, type LineItemSection } from '../constants/lineItems.js';
import { AuthRequest } from '../middleware/auth.middleware.js';
import { hasPermission } from '../services/permissionService.js';

interface ProjectRow extends RowDataPacket {
  id: number;
  job_code: string | null;
  name: string;
  client_name: string | null;
  pm_name: string | null;
  start_date: string;
  finish_date: string;
}

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

export async function list(_req: Request, res: Response, next: NextFunction) {
  try {
    const [rows] = await pool.query<ProjectRow[]>(
      `SELECT p.id, p.job_code, p.name, c.name AS client_name, pm.name AS pm_name, p.start_date, p.finish_date
       FROM projects p
       LEFT JOIN clients c ON c.id = p.client_id
       LEFT JOIN users pm ON pm.id = p.project_manager_id
       WHERE p.approval_status = 'approved'
       ORDER BY p.id DESC`,
    );

    // Invoice values per project, valued at the sale rate:
    //  - planned: Σ plan % up to this month (plan_percent is a % of the whole project) × project sale value
    //  - actual:  Σ actual qty × sale rate booked in the JCR
    const [saleRows] = await pool.query<AmountRow[]>(
      `SELECT li.project_id, SUM(li.qty * li.sale_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<AmountRow[]>(
      `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<AmountRow[]>(
      `SELECT li.project_id, SUM(a.actual_qty * li.sale_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' GROUP BY li.project_id`,
    );
    const saleBy = new Map(saleRows.map((r) => [r.project_id, Number(r.amount)]));
    const planPctBy = new Map(planRows.map((r) => [r.project_id, Number(r.amount)]));
    const actualBy = new Map(actualRows.map((r) => [r.project_id, Number(r.amount)]));

    res.json(
      rows.map((row) => ({
        projectId: row.id,
        jobCode: row.job_code,
        name: row.name,
        clientName: row.client_name,
        projectManagerName: row.pm_name,
        startDate: row.start_date,
        finishDate: row.finish_date,
        plannedInvoiceValue: ((planPctBy.get(row.id) ?? 0) / 100) * (saleBy.get(row.id) ?? 0),
        actualInvoiceValue: actualBy.get(row.id) ?? 0,
      })),
    );
  } catch (err) {
    next(err);
  }
}

// project_line_items.section / project_line_item_actuals.period / s_curve_plan.period
// all round-trip as plain 'YYYY-MM-DD' strings (dateStrings: true on the pool), so
// periods are compared as strings throughout rather than through Date objects.
const MAX_MONTHS = 120; // 10 years — a generous cap against bad start/finish data

function monthsBetween(start: string, finish: string): string[] {
  const [sy, sm] = start.split('-').map(Number);
  const [fy, fm] = finish.split('-').map(Number);
  const months: string[] = [];
  let y = sy;
  let m = sm;
  while ((y < fy || (y === fy && m <= fm)) && months.length < MAX_MONTHS) {
    months.push(`${y}-${String(m).padStart(2, '0')}-01`);
    m += 1;
    if (m > 12) {
      m = 1;
      y += 1;
    }
  }
  return months.length > 0 ? months : [`${sy}-${String(sm).padStart(2, '0')}-01`];
}

interface LineItemRow extends RowDataPacket {
  id: number;
  section: LineItemSection;
  description: string;
  unit: string;
  qty: string;
  ace_rate: string;
}

interface ActualRow extends RowDataPacket {
  line_item_id: number;
  period: string;
  actual_amount: string;
}

interface PlanRow extends RowDataPacket {
  line_item_id: number;
  period: string;
  plan_percent: string;
}

export async function getSCurve(req: Request, res: Response, next: NextFunction) {
  const projectId = Number(req.params.projectId);
  try {
    const [projectRows] = await pool.query<ProjectRow[]>(
      `SELECT p.id, p.job_code, p.name, c.name AS client_name, pm.name AS pm_name, p.start_date, p.finish_date
       FROM projects p
       LEFT JOIN clients c ON c.id = p.client_id
       LEFT JOIN users pm ON pm.id = p.project_manager_id
       WHERE p.id = ? AND p.approval_status = 'approved'`,
      [projectId],
    );
    const project = projectRows[0];
    if (!project) return res.status(404).json({ message: 'Project not found' });

    const [itemRows] = await pool.query<LineItemRow[]>(
      `SELECT id, section, description, unit, qty, ace_rate
       FROM project_line_items WHERE project_id = ? ORDER BY section, sno`,
      [projectId],
    );
    const itemIds = itemRows.map((r) => r.id);

    let actualRows: ActualRow[] = [];
    let planRows: PlanRow[] = [];
    if (itemIds.length > 0) {
      [[actualRows], [planRows]] = await Promise.all([
        pool.query<ActualRow[]>(
          `SELECT line_item_id, period, SUM(actual_qty * actual_rate) AS actual_amount
           FROM project_line_item_actuals WHERE line_item_id IN (?) GROUP BY line_item_id, period`,
          [itemIds],
        ),
        pool.query<PlanRow[]>(
          `SELECT line_item_id, period, plan_percent FROM s_curve_plan WHERE line_item_id IN (?)`,
          [itemIds],
        ),
      ]);
    }

    const periods = monthsBetween(project.start_date, project.finish_date);
    const totalAceAmount = itemRows.reduce((sum, r) => sum + Number(r.qty) * Number(r.ace_rate), 0);

    const actualByKey = new Map(actualRows.map((r) => [`${r.line_item_id}|${r.period}`, Number(r.actual_amount)]));
    const planByKey = new Map(planRows.map((r) => [`${r.line_item_id}|${r.period}`, Number(r.plan_percent)]));

    const itemsBySection = new Map<LineItemSection, ReturnType<typeof buildItem>[]>();
    function buildItem(row: LineItemRow) {
      const aceAmount = Number(row.qty) * Number(row.ace_rate);
      const weightPercent = totalAceAmount > 0 ? (aceAmount / totalAceAmount) * 100 : 0;
      const plan: Record<string, number> = {};
      const actual: Record<string, number> = {};
      for (const period of periods) {
        plan[period] = planByKey.get(`${row.id}|${period}`) ?? 0;
        const amount = actualByKey.get(`${row.id}|${period}`) ?? 0;
        actual[period] = totalAceAmount > 0 ? (amount / totalAceAmount) * 100 : 0;
      }
      return {
        lineItemId: row.id,
        description: row.description,
        unit: row.unit,
        aceAmount,
        weightPercent,
        plan,
        actual,
      };
    }

    for (const row of itemRows) {
      const item = buildItem(row);
      const bucket = itemsBySection.get(row.section) ?? [];
      bucket.push(item);
      itemsBySection.set(row.section, bucket);
    }

    const sections = LINE_ITEM_SECTIONS.map((section) => ({
      section,
      label: SECTION_LABELS[section],
      items: itemsBySection.get(section) ?? [],
    }));

    res.json({
      project: {
        id: project.id,
        jobCode: project.job_code,
        name: project.name,
        clientName: project.client_name,
        projectManagerName: project.pm_name,
        startDate: project.start_date,
        finishDate: project.finish_date,
      },
      totalAceAmount,
      periods,
      sections,
    });
  } catch (err) {
    next(err);
  }
}

interface PlanInput {
  lineItemId: number;
  period: string;
  planPercent: number;
}

export async function savePlan(req: AuthRequest, res: Response, next: NextFunction) {
  const projectId = Number(req.params.projectId);
  const entries = (req.body?.entries ?? []) as PlanInput[];

  try {
    if (!(await hasPermission(req.userId!, 's_curve', 'canUpdate'))) {
      return res.status(403).json({ message: 'You do not have permission to edit the S Curve plan.' });
    }
    if (!Array.isArray(entries) || entries.length === 0) {
      return res.status(400).json({ message: 'entries is required.' });
    }
    for (const entry of entries) {
      if (!entry.lineItemId || !entry.period || typeof entry.planPercent !== 'number' || !(entry.planPercent >= 0)) {
        return res.status(400).json({ message: 'Each entry needs a valid lineItemId, period and planPercent.' });
      }
    }

    const lineItemIds = [...new Set(entries.map((e) => e.lineItemId))];
    const [ownedRows] = await pool.query<RowDataPacket[]>(
      `SELECT li.id FROM project_line_items li
       JOIN projects p ON p.id = li.project_id
       WHERE li.project_id = ? AND p.approval_status = 'approved' AND li.id IN (?)`,
      [projectId, lineItemIds],
    );
    const ownedIds = new Set(ownedRows.map((r) => r.id as number));
    if (ownedIds.size !== lineItemIds.length) {
      return res.status(404).json({ message: 'One or more line items were not found on this project.' });
    }

    // Deliberately no cap tying a line item's total Plan % to its contract weight —
    // actuals routinely run behind an already-entered plan, and re-planning a
    // delayed item means adding more Plan later without first trimming earlier
    // months, so any such check would just fight normal usage.
    for (const entry of entries) {
      const period = entry.period.length === 7 ? `${entry.period}-01` : entry.period;
      await pool.query(
        `INSERT INTO s_curve_plan (line_item_id, period, plan_percent, updated_by)
         VALUES (?, ?, ?, ?)
         ON DUPLICATE KEY UPDATE plan_percent = VALUES(plan_percent), updated_by = VALUES(updated_by)`,
        [entry.lineItemId, period, entry.planPercent, req.userId],
      );
    }

    res.status(204).send();
  } catch (err) {
    next(err);
  }
}