import { NextResponse } from "next/server";
import { db } from "../../lib/db";

export async function GET() {
  try {
    /* ================= SUMMARY ================= */
    const [[summary]]: any = await db.query(`
      SELECT 
        (SELECT COUNT(*) FROM items) as total_items,
        (SELECT COUNT(*) FROM locations) as total_locations,
        (SELECT COALESCE(SUM(quantity),0) FROM inventories) as total_stock,
        (SELECT COUNT(*) FROM inventories WHERE quantity < 10) as low_stock
    `);

    /* ================= INVENTORY BY LOCATION ================= */
    const [inventory_by_location]: any = await db.query(`
      SELECT 
        l.name as location,
        COALESCE(SUM(i.quantity),0) as qty
      FROM locations l
      LEFT JOIN inventories i ON i.location_id = l.id
      GROUP BY l.id
      ORDER BY qty DESC
    `);

    /* ================= LOW STOCK ITEMS ================= */
    const [low_stock_items]: any = await db.query(`
      SELECT 
        name,
        0 as qty
      FROM items
      LIMIT 5
    `);

    /* ================= RECENT MOVEMENTS ================= */
    const [recent_movements]: any = await db.query(`
      SELECT 
        sm.id,
        it.name as item,
        l.name as location,
        sm.movement_type as type,
        sm.quantity as qty
      FROM stock_movements sm
      JOIN items it ON it.id = sm.item_id
      JOIN locations l ON l.id = sm.location_id
      ORDER BY sm.created_at DESC
      LIMIT 10
    `);

    /* ================= STO ================= */
    const [[sto]]: any = await db.query(`
      SELECT
        SUM(status='DRAFT') as draft,
        SUM(status='CHECKED') as checked,
        SUM(status='APPROVED') as approved,
        SUM(status='IN_TRANSIT') as in_transit,
        SUM(status='COMPLETED') as completed
      FROM stock_transfer_orders
    `);

    /* ================= APPROVAL ================= */
    const [approvals]: any = await db.query(`
      SELECT 
        sto.sto_number as sto,
        SUM(a.role='CHECKER' AND a.status='APPROVED') as checker,
        SUM(a.role='SUPERVISOR' AND a.status='APPROVED') as supervisor
      FROM stock_transfer_orders sto
      LEFT JOIN stock_transfer_approvals a ON a.sto_id = sto.id
      GROUP BY sto.id
      ORDER BY sto.created_at DESC
      LIMIT 5
    `);

    return NextResponse.json({
      summary,
      inventory_by_location,
      low_stock_items,
      recent_movements,
      sto,
      approvals,
    });
  } catch (err: any) {
    console.error(err);
    return NextResponse.json(
      { error: "Dashboard error", detail: err.message },
      { status: 500 }
    );
  }
}