import { NextRequest, NextResponse } from "next/server";
import { db } from "@/app/lib/db";

export async function GET(
  req: NextRequest,
  { params }: { params: Promise<{ id: string }> }
) {
  const connection = await db.getConnection();

  try {
    const { id } = await params;

    /* =========================
       1. GET OPNAME HEADER + AUDIT TRAIL
    ========================= */
    const [opnameRows]: any = await connection.query(
      `
      SELECT 
        so.id,
        so.location_id,
        so.opname_number,
        so.status,

        l.name AS warehouse_name,

        so.created_by,
        u1.name AS created_by_name,

        so.checked_by,
        u2.name AS checked_by_name,

        so.approved_by,
        u3.name AS approved_by_name,

        so.created_at,
        so.counted_at,
        so.approved_at,
        so.posted_at

      FROM stock_opnames so
      LEFT JOIN locations l ON l.id = so.location_id

      LEFT JOIN users u1 ON u1.id = so.created_by
      LEFT JOIN users u2 ON u2.id = so.checked_by
      LEFT JOIN users u3 ON u3.id = so.approved_by

      WHERE so.id = ?
      LIMIT 1
      `,
      [id]
    );

    const opname = opnameRows?.[0];

    if (!opname) {
      return NextResponse.json(
        { success: false, message: "Not found" },
        { status: 404 }
      );
    }

    /* =========================
       2. SNAPSHOT (SOURCE OF TRUTH)
    ========================= */
    const [snapshotRows]: any = await connection.query(
      `
      SELECT
        ss.item_id,
        ss.system_qty,
        ss.counted_qty,
        ss.variance_qty,

        i.name AS item_name,
        i.sku

      FROM stock_opname_snapshots ss
      JOIN items i ON i.id = ss.item_id

      WHERE ss.opname_id = ?
      ORDER BY i.name ASC
      `,
      [id]
    );

    /* =========================
       3. BIN INFO (OPTIONAL)
    ========================= */
    const [binRows]: any = await connection.query(
      `
      SELECT COUNT(*) AS total_bins
      FROM location_bins
      WHERE location_id = ?
        AND is_active = 1
      `,
      [opname.location_id]
    );

    const total_bins = binRows?.[0]?.total_bins || 0;

    /* =========================
       4. TRANSFORM SNAPSHOT
    ========================= */
    const items = snapshotRows.map((r: any) => {
      const variance = Number(r.variance_qty || 0);

      return {
        item_id: r.item_id,
        item_name: r.item_name,
        sku: r.sku,

        system_qty: Number(r.system_qty || 0),
        counted_qty: Number(r.counted_qty || 0),
        variance,

        status:
          variance === 0
            ? "MATCH"
            : variance > 0
            ? "OVER"
            : "SHORT",
      };
    });

    /* =========================
       5. SUMMARY
    ========================= */
    const summary = items.reduce(
      (acc: any, item: any) => {
        acc.total += 1;
        if (item.variance === 0) acc.ok += 1;
        else acc.mismatch += 1;

        acc.total_system += item.system_qty;
        acc.total_counted += item.counted_qty;

        return acc;
      },
      {
        total: 0,
        ok: 0,
        mismatch: 0,
        total_system: 0,
        total_counted: 0,
      }
    );

    /* =========================
       6. RESPONSE (COMPLETE REPORT)
    ========================= */
    return NextResponse.json({
      success: true,
      data: {
        /* HEADER */
        opname_id: opname.id,
        opname_number: opname.opname_number,
        status: opname.status,
        location_name: opname.warehouse_name,
        total_bins,

        /* AUDIT TRAIL (INI YANG KAMU TANYA) */
        created_by: opname.created_by_name || "-",
        checked_by: opname.checked_by_name || "-",
        approved_by: opname.approved_by_name || "-",

        created_at: opname.created_at,
        counted_at: opname.counted_at,
        approved_at: opname.approved_at,
        posted_at: opname.posted_at,

        /* MAIN DATA (SNAPSHOT LOCKED) */
        items,

        /* SUMMARY */
        summary,
      },
    });
  } catch (err: any) {
    return NextResponse.json(
      {
        success: false,
        message: err.message,
      },
      { status: 500 }
    );
  } finally {
    connection.release();
  }
}