import type { PoolConnection } from 'mysql2/promise';
import { queryWithConn } from '../../config/database.js';
import { findAll as findAllStages } from './stage.repo.js';
import type { Kpi, RecentOrderRow, StageBreakdownRow } from '../types/index.js';

// Every product currently sitting in a real stage (open order_stage_history
// row) whose *wall-clock* time in that stage already exceeds the stage's
// idle_threshold_minutes. This is a superset pre-filter, not the final idle
// determination — office-hours-aware idle minutes (officeCalendar.service.ts's
// officeMinutesElapsed) are always <= wall-clock minutes, so anything that's
// truly idle by office-minutes must also pass this wall-clock check; letting
// SQL narrow the candidate set here keeps the per-row office-hours math (done
// in dashboard.service.ts) from having to run over every open stage line.
export async function getIdleCandidates(conn?: PoolConnection): Promise<
  { stageId: string; name: string; color: string; enteredAt: Date; thresholdMinutes: number }[]
> {
  const rows = await queryWithConn(`
    SELECT h.stage_id as stage_id, s.name, s.color, h.entered_at, s.idle_threshold_minutes
    FROM order_stage_history h
    JOIN order_products op ON op.id = h.order_product_id AND op.deleted_at IS NULL AND op.cancelled = FALSE AND op.dispatched = FALSE
    JOIN stages s ON s.id = h.stage_id AND s.deleted_at IS NULL
    WHERE h.completed_at IS NULL
      AND TIMESTAMPDIFF(MINUTE, h.entered_at, NOW()) > s.idle_threshold_minutes
  `, undefined, conn) as unknown as { stage_id: string; name: string; color: string; entered_at: Date; idle_threshold_minutes: number }[];

  return rows.map((r) => ({
    stageId: r.stage_id, name: r.name, color: r.color, enteredAt: r.entered_at, thresholdMinutes: r.idle_threshold_minutes,
  }));
}

// Bare id+name client list, gated only by DASHBOARD_VIEW — lets the Stage
// Breakdown / Stage Idle modals show a client name for each pending line
// without needing CLIENTS_VIEW/ORDERS_VIEW/etc, the same way order.repo.ts's
// findAll() already pre-joins client/employee/product names for the rest of
// the dashboard's tables.
export async function getClientNames(conn?: PoolConnection): Promise<{ id: string; name: string }[]> {
  const rows = await queryWithConn(
    'SELECT id, name FROM clients WHERE deleted_at IS NULL',
    undefined, conn,
  ) as unknown as { id: string; name: string }[];
  return rows;
}

// Product -> wire size name, gated only by DASHBOARD_VIEW — lets the Wire
// Size Club modal group lines by wire size without needing PRODUCTS_VIEW.
// Only products that actually have a wire size set are returned.
export async function getWireSizesByProduct(conn?: PoolConnection): Promise<{ productId: string; wireSizeName: string }[]> {
  const rows = await queryWithConn(`
    SELECT p.id as product_id, w.name as wire_size_name
    FROM products p
    JOIN wire_sizes w ON w.id = p.wire_size_id AND w.deleted_at IS NULL
    WHERE p.deleted_at IS NULL AND p.wire_size_id IS NOT NULL
  `, undefined, conn) as unknown as { product_id: string; wire_size_name: string }[];
  return rows.map((r) => ({ productId: r.product_id, wireSizeName: r.wire_size_name }));
}

export async function getKpis(conn?: PoolConnection): Promise<Kpi[]> {
  const rows = await queryWithConn(`
    SELECT
      (SELECT COUNT(*) FROM orders WHERE deleted_at IS NULL AND order_date = CURDATE()) as todays_orders,
      (SELECT COUNT(*) FROM orders o WHERE o.deleted_at IS NULL AND o.lifecycle = 'active' AND (
        EXISTS (
          SELECT 1 FROM order_products op WHERE op.order_id = o.id AND op.deleted_at IS NULL AND op.cancelled = FALSE AND op.dispatched = FALSE
        )
        -- An order edited down to zero remaining line items has no pending
        -- line to match above, but the frontend still treats it as Pending
        -- (nothing dispatched, nothing cancelled) — count it here too so
        -- this total doesn't quietly undercount versus the Orders page.
        OR NOT EXISTS (
          SELECT 1 FROM order_products op WHERE op.order_id = o.id AND op.deleted_at IS NULL
        )
      )) as pending_orders,
      (SELECT COUNT(*) FROM orders o WHERE o.deleted_at IS NULL AND o.lifecycle = 'active' AND NOT EXISTS (
        SELECT 1 FROM order_products op WHERE op.order_id = o.id AND op.deleted_at IS NULL AND op.cancelled = FALSE AND op.dispatched = FALSE
      ) AND EXISTS (
        SELECT 1 FROM order_products op WHERE op.order_id = o.id AND op.deleted_at IS NULL AND op.cancelled = FALSE
      )) as completed_orders,
      (SELECT COUNT(*) FROM orders WHERE deleted_at IS NULL AND lifecycle = 'cancelled') as cancelled_orders,
      -- dispatch_date is a DATETIME (holds the real moment, not just the
      -- day — see migration 20260721000001), so a bare equality against
      -- CURDATE() would only ever match a dispatch recorded at exactly
      -- midnight. Wrapping in DATE() strips the time before comparing.
      (SELECT COUNT(*) FROM order_products WHERE deleted_at IS NULL AND dispatched = TRUE AND DATE(dispatch_date) = CURDATE()) as dispatch_today,
      -- JO Entry Pending: order lines still missing a JO number (data-entry
      -- gap), regardless of production stage — cancelled lines are excluded
      -- since they no longer need one.
      (SELECT COUNT(*) FROM order_products WHERE deleted_at IS NULL AND cancelled = FALSE AND (jo_no IS NULL OR jo_no = '')) as jo_entry_pending,
      -- Stage-wise Pending: every line still in the production pipeline
      -- (not yet dispatched, not cancelled), across every stage including
      -- "Not Started" — the card's own click-through breaks this down
      -- stage-by-stage via getStageBreakdown() below.
      (SELECT COUNT(*) FROM order_products WHERE deleted_at IS NULL AND cancelled = FALSE AND dispatched = FALSE) as stage_pending,
      -- SO Entry Pending: active orders still missing an SO number
      -- (data-entry gap), regardless of dispatch/cancellation state of lines.
      (SELECT COUNT(*) FROM orders o WHERE o.deleted_at IS NULL AND o.lifecycle = 'active'
        AND (o.so_number IS NULL OR o.so_number = '')
      ) as so_entry_pending,
      -- Returned Items: open (not yet completed/rejected) returns — see
      -- orderReturn.repo.ts's findOpenCountForKpi for the same query,
      -- inlined here so it lands in the one round-trip getKpis() already makes.
      (SELECT COUNT(*) FROM order_returns WHERE deleted_at IS NULL AND status IN ('pending', 'active')) as returned_items
  `, undefined, conn) as unknown as Record<string, unknown>[];

  const r = rows[0] ?? {};
  return [
    { id: 'todays-orders', label: "Today's Orders", value: Number(r.todays_orders ?? 0), delta: 0, trend: 'up' },
    { id: 'pending-orders', label: 'Pending Orders', value: Number(r.pending_orders ?? 0), delta: 0, trend: 'down', orderStatus: 'pending' },
    { id: 'completed-orders', label: 'Completed Orders', value: Number(r.completed_orders ?? 0), delta: 0, trend: 'up', orderStatus: 'completed' },
    { id: 'cancelled-orders', label: 'Cancelled Orders', value: Number(r.cancelled_orders ?? 0), delta: 0, trend: 'down', orderStatus: 'cancelled' },
    { id: 'dispatch-today', label: 'Dispatch Today', value: Number(r.dispatch_today ?? 0), delta: 0, trend: 'up', focus: 'dispatch-today' },
    { id: 'jo-pending', label: 'JO Entry Pending', value: Number(r.jo_entry_pending ?? 0), delta: 0, trend: 'down', focus: 'jo-pending' },
    { id: 'stage-pending', label: 'Stage-wise Pending', value: Number(r.stage_pending ?? 0), delta: 0, trend: 'down' },
    { id: 'so-pending', label: 'SO Entry Pending', value: Number(r.so_entry_pending ?? 0), delta: 0, trend: 'down', focus: 'so-pending' },
    { id: 'returned-items', label: 'Returned Items', value: Number(r.returned_items ?? 0), delta: 0, trend: 'down' },
  ];
}

// Breaks the "Stage-wise Pending" KPI card down by stage — one row per
// stage (in stage order) plus a leading "Not Started" bucket, each with the
// count of pending (not dispatched, not cancelled) lines currently sitting
// there. Drives the dashboard's stage picker modal.
export async function getStageBreakdown(conn?: PoolConnection): Promise<StageBreakdownRow[]> {
  const stages = await findAllStages(conn);

  const rows = await queryWithConn(`
    SELECT completed_stages, COUNT(*) as cnt
    FROM order_products
    WHERE deleted_at IS NULL AND cancelled = FALSE AND dispatched = FALSE
    GROUP BY completed_stages
  `, undefined, conn) as unknown as { completed_stages: number; cnt: number }[];

  const countByPosition = new Map(rows.map((r) => [r.completed_stages, Number(r.cnt)]));

  const notStarted: StageBreakdownRow = { stageId: 'not_started', name: 'Not Started', color: '#94a3b8', count: countByPosition.get(0) ?? 0 };
  const stageRows: StageBreakdownRow[] = stages.map((s, i) => ({
    stageId: s.id,
    name: s.name,
    color: s.color,
    count: countByPosition.get(i + 1) ?? 0,
  }));

  return [notStarted, ...stageRows];
}

export async function getMonthlyOrders(conn?: PoolConnection): Promise<{ month: string; current_year: number; previous_year: number }[]> {
  const rows = await queryWithConn(`
    SELECT DATE_FORMAT(order_date, '%b') as month,
           SUM(CASE WHEN YEAR(order_date) = YEAR(CURDATE()) THEN 1 ELSE 0 END) as current_year,
           SUM(CASE WHEN YEAR(order_date) = YEAR(CURDATE()) - 1 THEN 1 ELSE 0 END) as previous_year
    FROM orders WHERE deleted_at IS NULL
    GROUP BY DATE_FORMAT(order_date, '%Y-%m'), DATE_FORMAT(order_date, '%b')
    ORDER BY DATE_FORMAT(order_date, '%Y-%m') ASC
  `, undefined, conn) as unknown as { month: string; current_year: number; previous_year: number }[];
  return rows;
}

export async function getMonthlyProduction(
  conn?: PoolConnection,
): Promise<{ month: string; target: number; achieved: number }[]> {
  const rows = await queryWithConn(`
    SELECT DATE_FORMAT(o.order_date, '%b') as month,
           SUM(op.quantity) as target,
           SUM(op.dispatched_quantity) as achieved
    FROM orders o
    JOIN order_products op ON op.order_id = o.id AND op.deleted_at IS NULL
    WHERE o.deleted_at IS NULL
    GROUP BY DATE_FORMAT(o.order_date, '%Y-%m'), DATE_FORMAT(o.order_date, '%b')
    ORDER BY DATE_FORMAT(o.order_date, '%Y-%m') ASC
  `, undefined, conn) as unknown as { month: string; target: number; achieved: number }[];
  return rows;
}

export async function getDispatchAnalysis(
  conn?: PoolConnection,
): Promise<{ date: string; day: string; dispatched: number; pending: number; delayed: number }[]> {
  const rows = await queryWithConn(`
    SELECT o.delivery_date as date, DATE_FORMAT(o.delivery_date, '%a') as day,
           SUM(op.dispatched_quantity) as dispatched,
           SUM(CASE WHEN o.delivery_date >= CURDATE() THEN op.quantity - op.dispatched_quantity - op.cancelled_quantity ELSE 0 END) as pending,
           SUM(CASE WHEN o.delivery_date < CURDATE() THEN op.quantity - op.dispatched_quantity - op.cancelled_quantity ELSE 0 END) as \`delayed\`
    FROM orders o
    JOIN order_products op ON op.order_id = o.id AND op.deleted_at IS NULL
    WHERE o.deleted_at IS NULL AND o.delivery_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 6 DAY) AND CURDATE()
    GROUP BY o.delivery_date
    ORDER BY o.delivery_date ASC
  `, undefined, conn) as unknown as { date: string; day: string; dispatched: number; pending: number; delayed: number }[];
  return rows;
}

export async function getRecentOrders(limit = 6, conn?: PoolConnection): Promise<RecentOrderRow[]> {
  const rows = await queryWithConn(`
    SELECT o.order_no as order_number, c.name as customer, o.order_date as date,
           o.priority, o.lifecycle, e.name as employee
    FROM orders o
    JOIN clients c ON c.id = o.client_id AND c.deleted_at IS NULL
    JOIN employees e ON e.id = o.taken_by_employee_id AND e.deleted_at IS NULL
    WHERE o.deleted_at IS NULL
    ORDER BY o.created_at DESC
    LIMIT ?
  `, [limit], conn) as unknown as Record<string, unknown>[];

  return rows.map((r) => ({
    order_number: r.order_number as string,
    customer: r.customer as string,
    date: r.date as string,
    amount: 0,
    status: String(r.lifecycle),
    employee: r.employee as string,
  }));
}