<?php

namespace App\Http\Controllers;

use App\Models\Invoice;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Validator;
use App\Models\Customer;
use App\Models\Branch;
use App\Models\UserAccount;
use App\Models\IssuedItem;
use App\Models\Stock;

class InvoiceController extends Controller
{
    /**
     * Get sum of sub_total for current date by branch and monthly sales
     *
     * @param Request $request
     * @return \Illuminate\Http\JsonResponse
     */
    public function getInvoices(Request $request)
    {
        // Validate request parameters
        $validator = Validator::make($request->all(), [
            'customer_id' => 'nullable|integer',
            'invoice_no' => 'nullable|string',
            'start_date' => 'nullable|date',
            'end_date' => 'nullable|date',
            'branch_id' => 'nullable|string',
            'page' => 'nullable|integer|min:1',
            'limit' => 'nullable|integer|min:1',
            'sort_by' => 'nullable|string|in:added_date,tot_inv_price,inv_no',
            'sort_direction' => 'nullable|string|in:asc,desc',
        ]);

        if ($validator->fails()) {
            return response()->json([
                'status' => 'error',
                'message' => 'Invalid parameters',
                'errors' => $validator->errors()
            ], 400);
        }

        // Get parameters
        $customerId = $request->input('customer_id');
        $invoiceNo = $request->input('invoice_no');
        $startDate = $request->input('start_date');
        $endDate = $request->input('end_date');
        $branchId = $request->input('branch_id');
        $page = $request->input('page', 1);
        $limit = $request->input('limit', 10);
        $sortBy = $request->input('sort_by', 'added_date');
        $sortDirection = $request->input('sort_direction', 'desc');

        try {
            // Start query
            $query = Invoice::query()
                ->select(
                    'invoice.*',
                    'customer.cus_Name',
                    'customer.contact_no',
                    'customer.cus_address'
                )
                ->leftJoin('customer', 'invoice.cus_id', '=', 'customer.cus_id');

            // Add filters if provided
            if ($customerId) {
                $query->where('invoice.cus_id', $customerId);
            }

            if ($invoiceNo) {
                $query->where('invoice.inv_no', 'like', "%$invoiceNo%");
            }

            if ($startDate && $endDate) {
                $query->whereBetween('invoice.added_date', [$startDate, $endDate]);
            } elseif ($startDate) {
                $query->where('invoice.added_date', '>=', $startDate);
            } elseif ($endDate) {
                $query->where('invoice.added_date', '<=', $endDate);
            }

            if ($branchId) {
                $query->where('invoice.branch_id', $branchId);
            }

            // Count total results (for pagination)
            $total = $query->count();

            // Apply sorting and pagination
            $results = $query
                ->orderBy($sortBy, $sortDirection)
                ->get();

            $branch = Branch::all();

            return response()->json([
                'status' => 'success',
                'data' => [
                    'invoices' => $results,
                    'branch' => $branch,
                    'pagination' => [
                        'total' => $total,
                        'page' => $page,
                        'limit' => $limit,
                        'total_pages' => ceil($total / $limit)
                    ]
                ]
            ]);
        } catch (\Exception $e) {
            return response()->json([
                'status' => 'error',
                'message' => 'Failed to fetch invoices',
                'error' => $e->getMessage()
            ], 500);
        }
    }

    /**
     * Get invoice details by invoice number
     *
     * @param string $invNo
     * @return \Illuminate\Http\JsonResponse
     */
    public function getInvoiceById(Request $request)
    {
        $invoice_no = $request->query('invoice_no');

        if (!$invoice_no) {
            return response()->json([
                'status' => 'error',
                'message' => 'Invoice number is required'
            ], 400);
        }

        try {
            // Replicate the exact SQL query you provided using Laravel's query builder
            $invoiceData = DB::table('invoice')
                ->join('issued_items', 'invoice.inv_no', '=', 'issued_items.inv_no')
                ->join('user_account', 'invoice.added_by', '=', 'user_account.employee_ID')
                ->join('customer', 'invoice.cus_id', '=', 'customer.cus_id')
                ->join('stock', 'issued_items.item_stk_id', '=', 'stock.item_stk_id')
                ->join('item', 'stock.item_code', '=', 'item.item_code')
                ->join('tbl_branch', 'invoice.branch_id', '=', 'tbl_branch.branch_id')
                ->select(
                    'invoice.inv_no',
                    'invoice.cus_id',
                    'customer.cus_Name',
                    'invoice.tot_inv_price',
                    'invoice.tot_discount',
                    'invoice.sub_total',
                    'invoice.added_date',
                    'invoice.added_time',
                    'invoice.transport_charge',
                    'invoice.document_charge',
                    'invoice.calculate_penalty',
                    'invoice.po_no',
                    'invoice.bill_discount',
                    'tbl_branch.branch_name',
                    'item.barcode',
                    'item.item_name',
                    'issued_items.issued_qty',
                    'issued_items.unit_price',
                    'issued_items.tot_price',
                    'issued_items.discount_prs',
                    'issued_items.unit_discount',
                    'issued_items.discount',
                    'issued_items.net_unit_price',
                    'issued_items.sub_total as item_subtotal',
                    'issued_items.warranty_period',
                    'issued_items.item_name as issued_item_name',
                    'issued_items.group_name',
                    'issued_items.group_no',
                    'issued_items.g_issue_qty',
                    'issued_items.g_unit_price',
                    'issued_items.g_unit_discount',
                    'issued_items.g_net_unit_price',
                    'issued_items.g_sub_total',
                    'issued_items.group_color',
                    'user_account.user_Name as added_by'
                )
                ->where('invoice.inv_no', $invoice_no)
                ->get();

            if ($invoiceData->isEmpty()) {
                return response()->json([
                    'status' => 'error',
                    'message' => 'Invoice not found'
                ], 404);
            }

            // Group the data by invoice and separate the items
            $invoice = $invoiceData->first();

            // Extract invoice header information
            $invoiceHeader = [
                'inv_no' => $invoice->inv_no,
                'cus_id' => $invoice->cus_id,
                'cus_Name' => $invoice->cus_Name,
                'tot_inv_price' => $invoice->tot_inv_price,
                'tot_discount' => $invoice->tot_discount,
                'sub_total' => $invoice->sub_total,
                'added_date' => $invoice->added_date,
                'added_time' => $invoice->added_time,
                'transport_charge' => $invoice->transport_charge,
                'document_charge' => $invoice->document_charge,
                'calculate_penalty' => $invoice->calculate_penalty,
                'po_no' => $invoice->po_no,
                'bill_discount' => $invoice->bill_discount,
                'branch_name' => $invoice->branch_name,
                'added_by' => $invoice->added_by
            ];

            // Extract all items
            $items = $invoiceData->map(function($item) {
                return [
                    'barcode' => $item->barcode,
                    'item_name' => $item->item_name,
                    'issued_qty' => $item->issued_qty,
                    'unit_price' => $item->unit_price,
                    'tot_price' => $item->tot_price,
                    'discount_prs' => $item->discount_prs,
                    'unit_discount' => $item->unit_discount,
                    'discount' => $item->discount,
                    'net_unit_price' => $item->net_unit_price,
                    'subtotal' => $item->item_subtotal,
                    'warranty_period' => $item->warranty_period,
                    'issued_item_name' => $item->issued_item_name,
                    'group_name' => $item->group_name,
                    'group_no' => $item->group_no,
                    'g_issue_qty' => $item->g_issue_qty,
                    'g_unit_price' => $item->g_unit_price,
                    'g_unit_discount' => $item->g_unit_discount,
                    'g_net_unit_price' => $item->g_net_unit_price,
                    'g_sub_total' => $item->g_sub_total,
                    'group_color' => $item->group_color
                ];
            });

            // Format the response
            $responseData = [
                'invoice' => $invoiceHeader,
                'items' => $items
            ];

            return response()->json([
                'status' => 'success',
                'data' => $responseData
            ]);

        } catch (\Exception $e) {
            return response()->json([
                'status' => 'error',
                'message' => 'Failed to fetch invoice details',
                'error' => $e->getMessage(),
                'trace' => $e->getTraceAsString()
            ], 500);
        }
    }
    /**
     * Get invoices by customer ID
     *
     * @param int $customerId
     * @param Request $request
     * @return \Illuminate\Http\JsonResponse
     */
    public function getCustomerInvoices($customerId, Request $request)
    {
        // Validate request parameters
        $validator = Validator::make($request->all(), [
            'page' => 'nullable|integer|min:1',
            'limit' => 'nullable|integer|min:1',
        ]);

        if ($validator->fails()) {
            return response()->json([
                'status' => 'error',
                'message' => 'Invalid parameters',
                'errors' => $validator->errors()
            ], 400);
        }

        $page = $request->input('page', 1);
        $limit = $request->input('limit', 10);

        try {
            // Check if customer exists
            $customer = Customer::find($customerId);
            if (!$customer) {
                return response()->json([
                    'status' => 'error',
                    'message' => 'Customer not found'
                ], 404);
            }

            // Get customer invoices
            $query = Invoice::where('cus_id', $customerId);
            $total = $query->count();

            $invoices = $query->orderBy('added_date', 'desc')
                ->offset(($page - 1) * $limit)
                ->limit($limit)
                ->get();



            return response()->json([
                'status' => 'success',
                'data' => [
                    'customer' => $customer,
                    'invoices' => $invoices,
                    'pagination' => [
                        'total' => $total,
                        'page' => $page,
                        'limit' => $limit,
                        'total_pages' => ceil($total / $limit)
                    ]
                ]
            ]);
        } catch (\Exception $e) {
            return response()->json([
                'status' => 'error',
                'message' => 'Failed to fetch customer invoices',
                'error' => $e->getMessage()
            ], 500);
        }
    }

    /**
     * Get invoices by branch ID
     *
     * @param string $branchId
     * @param Request $request
     * @return \Illuminate\Http\JsonResponse
     */
    public function getBranchInvoices($branchId, Request $request)
    {
        // Validate request parameters
        $validator = Validator::make($request->all(), [
            'start_date' => 'nullable|date',
            'end_date' => 'nullable|date',
            'page' => 'nullable|integer|min:1',
            'limit' => 'nullable|integer|min:1',
        ]);

        if ($validator->fails()) {
            return response()->json([
                'status' => 'error',
                'message' => 'Invalid parameters',
                'errors' => $validator->errors()
            ], 400);
        }

        $startDate = $request->input('start_date');
        $endDate = $request->input('end_date');
        $page = $request->input('page', 1);
        $limit = $request->input('limit', 10);

        try {
            // Build query
            $query = Invoice::select(
                    'invoice.*',
                    'customer.cus_Name',
                    'customer.contact_no'
                )
                ->leftJoin('customer', 'invoice.cus_id', '=', 'customer.cus_id')
                ->where('invoice.cmp_id', $branchId);

            // Apply date filters if provided
            if ($startDate && $endDate) {
                $query->whereBetween('invoice.added_date', [$startDate, $endDate]);
            } elseif ($startDate) {
                $query->where('invoice.added_date', '>=', $startDate);
            } elseif ($endDate) {
                $query->where('invoice.added_date', '<=', $endDate);
            }

            $total = $query->count();

            $invoices = $query->orderBy('invoice.added_date', 'desc')
                ->offset(($page - 1) * $limit)
                ->limit($limit)
                ->get();

            return response()->json([
                'status' => 'success',
                'data' => [
                    'invoices' => $invoices,
                    'pagination' => [
                        'total' => $total,
                        'page' => $page,
                        'limit' => $limit,
                        'total_pages' => ceil($total / $limit)
                    ]
                ]
            ]);
        } catch (\Exception $e) {
            return response()->json([
                'status' => 'error',
                'message' => 'Failed to fetch branch invoices',
                'error' => $e->getMessage()
            ], 500);
        }
    }

    public function getBranchList()
    {
        $users = Branch::all();
        return response()->json([
            'status' => 'success',
            'data' => $users
        ]);

    }
    public function customers()
    {
        $users = Customer::all();
        return response()->json([
            'status' => 'success',
            'data' => $users
        ]);

    }


    public function getSalesReport(Request $request)
    {
        // Get branch_id from request
        $branchId = $request->branch_id;
        $currentDate = $request->cur_date;
        // Get today's sales sum
        $todaySales = Invoice::where('branch_id', $branchId)
            ->whereDate('added_date', $currentDate)
            ->sum('sub_total');

        $dateObject = Carbon::parse($currentDate);
        $monthStart = $dateObject->copy()->startOfMonth();
        $monthEnd = $dateObject->copy()->endOfMonth();

        $monthlySales = Invoice::where('branch_id', $branchId)
            ->whereBetween('added_date', [$monthStart, $monthEnd])
            ->sum('sub_total');

        return response()->json([
            'status' => 'success',
            'data' => [
                'today_sales' => $todaySales,
                'monthly_sales' => $monthlySales,
                'branch_id' => $branchId,
                'date' => $currentDate,
                'month_start' => $monthStart->toDateString(),
                'month_end' => $monthEnd->toDateString()
            ]
        ]);
    }

    /**
     * Get detailed sales report with filtering options
     *
     * @param Request $request
     * @return \Illuminate\Http\JsonResponse
     */
    public function getDetailedSalesReport(Request $request)
    {
        // Validate request
        $request->validate([
            'start_date' => 'nullable|date',
            'end_date' => 'nullable|date',
            'branch_id' => 'nullable|string'
        ]);

        $branchId = $request->branch_id;
        $startDate = $request->start_date ? Carbon::parse($request->start_date) : Carbon::now()->startOfMonth();
        $endDate = $request->end_date ? Carbon::parse($request->end_date) : Carbon::now();

        $query = Invoice::where('branch_id', $branchId)
            ->whereBetween('added_date', [$startDate, $endDate]);

        $salesData = [
            'total_sales' => $query->sum('sub_total'),
            'total_discount' => $query->sum('tot_discount'),
            'total_invoices' => $query->count(),
            'average_sale' => $query->avg('sub_total'),
        ];

        // Daily breakdown
        $dailySales = Invoice::where('branch_id', $branchId)
            ->whereBetween('added_date', [$startDate, $endDate])
            ->select(
                DB::raw('DATE(added_date) as date'),
                DB::raw('SUM(sub_total) as daily_total')
            )
            ->groupBy(DB::raw('DATE(added_date)'))
            ->orderBy('date')
            ->get();

        return response()->json([
            'status' => 'success',
            'data' => [
                'summary' => $salesData,
                'daily_breakdown' => $dailySales,
                'filters' => [
                    'branch_id' => $branchId,
                    'start_date' => $startDate->toDateString(),
                    'end_date' => $endDate->toDateString()
                ]
            ]
        ]);
    }

    public function getSalesChartData(Request $request)
    {
        // Validate request
        $request->validate([
            'branch_id' => 'required|string',
            'period' => 'required|string|in:daily,weekly,monthly,yearly',
            'date' => 'required|date_format:Y-m-d'
        ]);

        $branchId = $request->branch_id;
        $period = $request->period;
        $date = Carbon::parse($request->date);

        // Initialize response data
        $chartData = [];
        $labels = [];

        switch ($period) {
            case 'daily':
                // Last 24 hours sales by hour
                for ($i = 0; $i < 24; $i++) {
                    $hourStart = Carbon::parse($date)->startOfDay()->addHours($i);
                    $hourEnd = Carbon::parse($date)->startOfDay()->addHours($i + 1);

                    $sales = Invoice::where('branch_id', $branchId)
                        ->whereBetween('added_date', [$hourStart, $hourEnd])
                        ->sum('sub_total');

                    $chartData[] = $sales;
                    $labels[] = $hourStart->format('ha');
                }
                break;

            case 'weekly':
                // Last 7 days
                for ($i = 6; $i >= 0; $i--) {
                    $dayDate = Carbon::parse($date)->subDays($i)->startOfDay();

                    $sales = Invoice::where('branch_id', $branchId)
                        ->whereDate('added_date', $dayDate)
                        ->sum('sub_total');

                    $chartData[] = $sales;
                    $labels[] = $dayDate->format('D');
                }
                break;

            case 'monthly':
                // Last 30 days
                $daysInMonth = Carbon::parse($date)->daysInMonth;

                for ($i = 1; $i <= $daysInMonth; $i++) {
                    $dayDate = Carbon::parse($date)->startOfMonth()->addDays($i - 1);

                    $sales = Invoice::where('branch_id', $branchId)
                        ->whereDate('added_date', $dayDate)
                        ->sum('sub_total');

                    $chartData[] = $sales;
                    $labels[] = (string)$i;
                }
                break;

            case 'yearly':
                // 12 months
                for ($i = 1; $i <= 12; $i++) {
                    $monthStart = Carbon::parse($date)->startOfYear()->addMonths($i - 1)->startOfMonth();
                    $monthEnd = Carbon::parse($date)->startOfYear()->addMonths($i - 1)->endOfMonth();

                    $sales = Invoice::where('branch_id', $branchId)
                        ->whereBetween('added_date', [$monthStart, $monthEnd])
                        ->sum('sub_total');

                    $chartData[] = $sales;
                    $labels[] = $monthStart->format('M');
                }
                break;
        }

        return response()->json([
            'status' => 'success',
            'data' => [
                'values' => $chartData,
                'labels' => $labels,
                'period' => $period
            ]
        ]);
    }

}
