<?php

namespace App\Http\Controllers\Api\Reports;

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

class SaleReportController extends Controller
{
    public function getCustomerSalesReport(Request $request)
    {
        // Get the date range from the request, or set default to the last 1 month
        $endDate = $request->input('end_date') ?: Carbon::today()->format('Y-m-d');
        $startDate = $request->input('start_date') ?: Carbon::today()->subMonth()->format('Y-m-d') ;

        // Query the database
        $report = DB::table('invoices')
            // ->join('users', 'invoices.customer_id', '=', 'users.id') // Join the customer table
            ->where('invoices.is_approved', 1)
            ->select(
                'customer_id','display_name',
                DB::raw('COUNT(CASE WHEN is_approved = 1 THEN 1 END) as number_of_sales'),
                DB::raw('SUM(CASE WHEN is_approved = 1 THEN subtotal ELSE 0 END) as total_sales_amount'),
                DB::raw('SUM(CASE WHEN is_approved = 1 THEN total ELSE 0 END) as total_amount')
            )
            ->whereBetween('transaction_date', [$startDate, $endDate])
            ->groupBy('customer_id','display_name')
            ->orderBy('total_amount', 'DESC')
            ->get();

        // Return the report as JSON or a view
        $response=[
            "success"=>true,
            "data"=>$report,
            "message"=>"Customer Sales Report Generate Successfully",
            "start_date"=>$startDate,
            "end_date"=>$endDate
        ];

        return response()->json($response);
    }

    public function sale_by_item_report(Request $request){

        // Get the date range from the request, or set default to the last 1 month
        $endDate = $request->input('end_date') ?: Carbon::today()->format('Y-m-d');
        $startDate = $request->input('start_date') ?: Carbon::today()->startOfMonth()->format('Y-m-d') ;

        $data = InvoiceItem::select(
                'item_id',
                DB::raw('SUM(quantity) as quantity_sold'),
                DB::raw('SUM(rate) as average_price'),
                DB::raw('SUM(gross_amount) as amount')
            )
            ->with('item:id,name,sku,unit')
            ->where('type','!=','service')
            ->whereHas('invoice', function ($query) use ($startDate, $endDate) {
                $query->whereBetween('transaction_date', [$startDate, $endDate]);
            })
            ->groupBy('item_id')
            ->get();

            // Total amount and quantity sold on that date
        $total = InvoiceItem::select(
                DB::raw('SUM(quantity) as total_quantity_sold'),
                // DB::raw('SUM(rate) as total_rate'),
                DB::raw('Round(SUM(gross_amount),2) as total_amount')
            )
            ->whereHas('invoice', function ($query) use ($startDate, $endDate) {
                $query->whereBetween('transaction_date', [$startDate, $endDate]);
            })
            ->get();

        // Return the report as JSON or a view
        $response=[
            "success"=>true,
            "message"=>"Sale by Item Report Generate Successfully",
            "start_date"=>$startDate,
            "end_date"=>$endDate,
            "total_calculation"=>$total,
            "data"=>$data,
        ];

        return response()->json($response);

    }
}
