<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use App\Models\Order;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class AdminReportController extends Controller
{
    /**
     * Display the reports page with joined data
     */
    public function index(Request $request)
    {
        // -------------------------------
        // FETCH REPORT DATA
        // ONLY orders that have BOTH:
        // 1. Invoice
        // 2. Shipment
        // -------------------------------
        $reports = DB::table('orders')
            ->join('shipment_items', 'shipment_items.order_id', '=', 'orders.id')
            ->join('shipments', 'shipments.id', '=', 'shipment_items.shipment_id')
            ->join('invoices', 'invoices.order_id', '=', 'orders.id')
            ->leftJoin('mark_list', 'mark_list.mark_no', '=', 'orders.mark_no')
            ->leftJoin('users', 'users.customer_id', '=', 'mark_list.customer_id')

            ->select(
                'orders.id as order_pk',
                'orders.order_id',
                'orders.mark_no',
                'orders.origin',
                'orders.destination',

                'shipments.id as shipment_pk',
                'shipments.shipment_id',
                'shipments.status as shipment_status',
                'shipments.shipment_date',

                'invoices.invoice_number',
                'invoices.invoice_date',
                'invoices.final_amount',
                'invoices.status as invoice_status',

                'mark_list.company_name',
                'mark_list.customer_name'
            )

            ->orderBy('shipments.shipment_date', 'desc')
            ->get();

        return view('admin.reports', compact('reports'));
    }
}