import { NextResponse } from "next/server";
import { queryOne, queryRows } from "@/lib/db";
import { requireMomoUser } from "@/lib/momo-guard";

export async function GET() {
  const r = await requireMomoUser();
  if (r instanceof NextResponse) return r;

  if (r.user.role === "USER") {
    const summary = await queryOne(
      `SELECT
         COUNT(*) FILTER (WHERE status = 'REQUESTED') AS "REQUESTED_CNT",
         COUNT(*) FILTER (WHERE status IN ('APPROVED','SHIPPED','INVOICED')) AS "PROGRESS_CNT",
         COALESCE(SUM(total_amount) FILTER (WHERE date_trunc('month', order_date) = date_trunc('month', CURRENT_DATE)), 0) AS "MONTH_AMOUNT",
         COALESCE(SUM(total_amount - paid_amount) FILTER (WHERE status = 'INVOICED'), 0) AS "UNPAID"
       FROM momo_orders
       WHERE customer_objid = $1 AND COALESCE(is_del,'N') != 'Y'`,
      [r.user.objid]
    );
    const recent = await queryRows(
      `SELECT objid AS "OBJID", order_no AS "ORDER_NO",
              TO_CHAR(order_date,'YYYY-MM-DD') AS "ORDER_DATE",
              status AS "STATUS", total_amount AS "TOTAL_AMOUNT"
       FROM momo_orders WHERE customer_objid = $1 AND COALESCE(is_del,'N') != 'Y'
       ORDER BY regdate DESC LIMIT 5`,
      [r.user.objid]
    );
    return NextResponse.json({ summary, recent });
  }

  // ADMIN
  const summary = await queryOne(
    `SELECT
       COUNT(*) FILTER (WHERE status = 'REQUESTED' AND COALESCE(is_del,'N') != 'Y') AS "PENDING_CNT",
       COUNT(*) FILTER (WHERE order_date = CURRENT_DATE AND COALESCE(is_del,'N') != 'Y') AS "TODAY_CNT",
       COALESCE(SUM(total_amount) FILTER (WHERE date_trunc('month', order_date) = date_trunc('month', CURRENT_DATE)), 0) AS "MONTH_AMOUNT",
       COALESCE(SUM(total_amount - paid_amount) FILTER (WHERE status IN ('APPROVED','SHIPPED','INVOICED')), 0) AS "UNPAID"
     FROM momo_orders WHERE COALESCE(is_del,'N') != 'Y'`
  );
  const lowStock = await queryRows(
    `SELECT I.objid AS "OBJID", I.item_code AS "ITEM_CODE", I.item_name AS "ITEM_NAME",
            COALESCE(SUM(S.qty),0) AS "STOCK_QTY"
     FROM momo_items I
     LEFT JOIN momo_stocks S ON S.item_objid = I.objid
     WHERE I.status = 'ACTIVE' AND COALESCE(I.is_del,'N') != 'Y'
     GROUP BY I.objid, I.item_code, I.item_name
     HAVING COALESCE(SUM(S.qty),0) < 10
     ORDER BY "STOCK_QTY" ASC LIMIT 10`
  );
  const daily = await queryRows(
    `SELECT TO_CHAR(order_date, 'MM-DD') AS "DAY",
            COUNT(*) AS "CNT", COALESCE(SUM(total_amount),0) AS "AMOUNT"
     FROM momo_orders
     WHERE order_date >= CURRENT_DATE - INTERVAL '13 days' AND COALESCE(is_del,'N') != 'Y'
     GROUP BY order_date ORDER BY order_date ASC`
  );
  const pending = await queryRows(
    `SELECT O.objid AS "OBJID", O.order_no AS "ORDER_NO",
            U.user_name AS "COMPANY_NAME",
            TO_CHAR(O.order_date,'YYYY-MM-DD') AS "ORDER_DATE",
            O.total_amount AS "TOTAL_AMOUNT"
     FROM momo_orders O LEFT JOIN user_info U ON U.user_id = O.customer_objid
     WHERE O.status = 'REQUESTED' AND COALESCE(O.is_del,'N') != 'Y'
     ORDER BY O.regdate ASC LIMIT 5`
  );
  return NextResponse.json({ summary, lowStock, daily, pending });
}
