<?php

namespace App\Http\Controllers\Manager;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use App\Models\EmployeeDetails;
use App\Models\EmployeeDailyReport;
use App\Models\EmployeeLeave;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use App\Models\CustomerVendor;
use App\Models\Machines;
use App\Models\RouteCard;
use App\Models\RouteCardProcess;
use App\Models\TaxInvoice;
use App\Models\PurchaseOrderItem;
use App\Models\SalesOrderItem;

class ManagerReportController extends Controller
{
    public function employeeReport(Request $request)
    {
        $employees = EmployeeDetails::orderBy('name')->get();

        $employeeId = $request->employee_id;
        $fromDate = $request->from_date;
        $toDate = $request->to_date;
        $tab = $request->tab ?? 'attendance';

        $attendanceReports = collect();
        $leaveReports = collect();

        // For attendance, use daily reports
        if ($tab == 'attendance') {
            $query = EmployeeDailyReport::with(['employeeDetails']);
            if ($employeeId) $query->where('employee_id', $employeeId);
            if ($fromDate && $toDate) $query->whereBetween('date', [$fromDate, $toDate]);
            $attendanceReports = $query->get();
        } elseif ($tab == 'leave') {
            $query = EmployeeLeave::with('employee');
            if ($employeeId) $query->where('employee_id', $employeeId);
            if ($fromDate && $toDate) $query->where(function ($q) use ($fromDate, $toDate) {
                $q->where('leave_from', '>=', $fromDate)
                    ->where('leave_to', '<=', $toDate);
            });
            $leaveReports = $query->get();
        }

        return view('manager.reports.employeereport', [
            'employees' => $employees,
            'tab' => $tab,
            'attendanceReports' => $attendanceReports,
            'leaveReports' => $leaveReports,
            'employeeId' => $employeeId,
            'fromDate' => $fromDate,
            'toDate' => $toDate,
        ]);
    }

    public function exportEmployeeReport(Request $request)
    {
        $employeeId = $request->employee_id;
        $fromDate = $request->from_date;
        $toDate = $request->to_date;
        $tab = $request->tab ?? 'attendance';

        if ($tab == 'attendance') {
            $query = EmployeeDailyReport::with(['employeeDetails']);
            if ($employeeId) $query->where('employee_id', $employeeId);
            if ($fromDate && $toDate) $query->whereBetween('date', [$fromDate, $toDate]);
            $data = $query->get();
        } elseif ($tab == 'leave') {
            $query = EmployeeLeave::with('employee');
            if ($employeeId) $query->where('employee_id', $employeeId);
            if ($fromDate && $toDate) $query->where(function ($q) use ($fromDate, $toDate) {
                $q->where('leave_from', '>=', $fromDate)
                    ->where('leave_to', '<=', $toDate);
            });
            $data = $query->get();
        } else {
            $data = collect();
        }

        // Generate Excel
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        if ($tab == 'attendance') {
            // All columns from your EmployeeDailyReport model
            $sheet->fromArray([
                'Date',
                'Employee Name',
                'Shift',
                'In SPFID 1',
                'Out SPFID 1',
                'In SPFID 2',
                'Out SPFID 2',
                'First Half',
                'Second Half',
                'Late In',
                'Early Out',
                'Hourly Paid Leave',
                'Hourly Unpaid Leave',
                'Over Time',
                'Auth OT',
                'Auth COFF',
                'Work Hours',
                'Manual Entry',
                'Reason'
            ], NULL, 'A1');

            $rowNum = 2;
            foreach ($data as $row) {
                $sheet->fromArray([
                    $row->date,
                    $row->employeeDetails ? $row->employeeDetails->name : '',
                    $row->shift,
                    $row->in_spfid_1,
                    $row->out_spfid_1,
                    $row->in_spfid_2,
                    $row->out_spfid_2,
                    $row->first_half,
                    $row->second_half,
                    $row->late_in,
                    $row->early_out,
                    $row->hourly_paid_leave,
                    $row->hourly_unpaid_leave,
                    $row->over_time,
                    $row->auth_ot,
                    $row->auth_coff,
                    $row->work_hours,
                    $row->man_entry,
                    $row->reason,
                ], NULL, 'A' . $rowNum);
                $rowNum++;
            }
        } elseif ($tab == 'leave') {
            $sheet->fromArray([
                'Leave From',
                'Leave To',
                'Employee',
                'Leave Type',
                'No. of Days',
                'Status'
            ], NULL, 'A1');
            $rowNum = 2;

            foreach ($data as $row) {
                $sheet->fromArray([
                    $row->leave_from,
                    $row->leave_to,
                    $row->employee->name ?? '',
                    $row->leave_type,
                    $row->number_of_days,
                    $row->status,
                ], NULL, 'A' . $rowNum);
                $rowNum++;
            }
        }

        $filename = "Employee-Report-{$tab}-" . date('Ymd_His') . ".xlsx";

        // Output to browser
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header("Content-Disposition: attachment; filename=\"$filename\"");
        header('Cache-Control: max-age=0');
        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');
        exit;
    }
    public function customerReport(Request $request)
    {
        // Get all unique company names for dropdown (filtered by role if present)
        $companyNamesQuery = CustomerVendor::query();
        if ($request->company_role) {
            $companyNamesQuery->where('company_role', $request->company_role);
        }
        $companyNames = $companyNamesQuery->orderBy('company')->pluck('company')->unique();

        // Main filter query
        $query = CustomerVendor::with('contactPersons');
        if ($request->company_role) {
            $query->where('company_role', $request->company_role);
        }
        if ($request->company) {
            $query->where('company', $request->company);
        }
        if ($request->from_date && $request->to_date) {
            $query->whereBetween('created_at', [$request->from_date, $request->to_date]);
        } elseif ($request->from_date) {
            $query->where('created_at', '>=', $request->from_date);
        } elseif ($request->to_date) {
            $query->where('created_at', '<=', $request->to_date);
        }
        $customers = $query->get();

        return view('manager.reports.customerreport', [
            'customers' => $customers,
            'companyNames' => $companyNames,
        ]);
    }

    public function exportCustomerReport(Request $request)
    {
        $query = CustomerVendor::with('contactPersons');
        if ($request->company_role) {
            $query->where('company_role', $request->company_role);
        }
        if ($request->company) {
            $query->where('company', $request->company);
        }
        if ($request->from_date && $request->to_date) {
            $query->whereBetween('created_at', [$request->from_date, $request->to_date]);
        } elseif ($request->from_date) {
            $query->where('created_at', '>=', $request->from_date);
        } elseif ($request->to_date) {
            $query->where('created_at', '<=', $request->to_date);
        }
        $data = $query->get();

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // Header
        $sheet->fromArray([
            'Role',
            'Company',
            'Email',
            'Mobile',
            'GST No',
            'PAN No',
            'Type',
            'Bank Name',
            'Account No',
            'IFSC',
            'Status',
            'Created At',
            'Contact Persons'
        ], NULL, 'A1');

        $rowNum = 2;
        foreach ($data as $row) {
            $contacts = [];
            foreach ($row->contactPersons as $cp) {
                $contacts[] = $cp->name . ' (' . $cp->mobile . ', ' . $cp->email . ', ' . $cp->designation . ')';
            }
            $sheet->fromArray([
                $row->company_role,
                $row->company,
                $row->email,
                $row->mobile,
                $row->gstno,
                $row->panno,
                $row->company_type,
                $row->bank_name,
                $row->account_number,
                $row->ifsc_code,
                $row->status,
                $row->created_at ? $row->created_at->format('Y-m-d') : '',
                implode("; ", $contacts),
            ], NULL, 'A' . $rowNum);
            $rowNum++;
        }

        $filename = "Customer-Report-" . date('Ymd_His') . ".xlsx";
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header("Content-Disposition: attachment; filename=\"$filename\"");
        header('Cache-Control: max-age=0');
        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');
        exit;
    }
    public function machineReport(Request $request)
    {
        // For dropdowns
        $machineNames = Machines::orderBy('machine_name')->pluck('machine_name', 'machine_id');
        $processList = RouteCardProcess::select('process')->distinct()->pluck('process');

        // Main query with filters
        $query = Machines::query();
        if ($request->machine_id) {
            $query->where('machine_id', $request->machine_id);
        }

        // For showing process info in table
        $machines = $query->get();

        // For "which RouteCards used this machine", eager load
        foreach ($machines as $machine) {
            $machine->routeProcesses = RouteCardProcess::where('machine', $machine->machine_id)
                ->with(['routeCard'])
                ->get();
        }

        return view('manager.reports.machinereport', [
            'machineNames' => $machineNames,
            'processList' => $processList,
            'machines' => $machines,
            'selectedMachine' => $request->machine_id,
        ]);
    }

    public function exportMachineReport(Request $request)
    {
        $query = Machines::query();
        if ($request->machine_id) {
            $query->where('machine_id', $request->machine_id);
        }
        $machines = $query->get();

        foreach ($machines as $machine) {
            $machine->routeProcesses = RouteCardProcess::where('machine', $machine->machine_name)
                ->with(['routeCard'])
                ->get();
        }

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        // Header
        $sheet->fromArray([
            'Machine Name',
            'Serial No',
            'Make',
            'Model',
            'Diameter',
            'Length',
            'Weight',
            'Remarks',
            'Route Card No',
            'Route Card Date',
            'Order No',
            'Process',
            'Operator',
            'Cycle Hours',
            'Cycle Minutes',
            'Process Start',
            'Process End'
        ], NULL, 'A1');
        $rowNum = 2;
        foreach ($machines as $machine) {
            if ($machine->routeProcesses->count()) {
                foreach ($machine->routeProcesses as $proc) {
                    $sheet->fromArray([
                        $machine->machine_name,
                        $machine->machine_sl_no,
                        $machine->make,
                        $machine->model_type,
                        $machine->diameter,
                        $machine->length,
                        $machine->weight,
                        $machine->remarks,
                        $proc->routeCard ? $proc->routeCard->route_no : '',
                        $proc->routeCard ? $proc->routeCard->route_date : '',
                        $proc->routeCard ? $proc->routeCard->order_no : '',
                        $proc->process,
                        $proc->operatorUser ? $proc->operatorUser->name : '',
                        $proc->cycle_hours,
                        $proc->cycle_minutes,
                        $proc->start_date,
                        $proc->end_date,
                    ], NULL, 'A' . $rowNum);
                    $rowNum++;
                }
            } else {
                // If machine has no routeProcesses
                $sheet->fromArray([
                    $machine->machine_name,
                    $machine->machine_sl_no,
                    $machine->make,
                    $machine->model_type,
                    $machine->diameter,
                    $machine->length,
                    $machine->weight,
                    $machine->remarks,
                    '',
                    '',
                    '',
                    '',
                    '',
                    '',
                    '',
                    '',
                    ''
                ], NULL, 'A' . $rowNum);
                $rowNum++;
            }
        }
        $filename = "Machine-Report-" . date('Ymd_His') . ".xlsx";
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header("Content-Disposition: attachment; filename=\"$filename\"");
        header('Cache-Control: max-age=0');
        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');
        exit;
    }
    public function billReport(Request $request)
    {
        // For company dropdown
        $companies = CustomerVendor::orderBy('company')->pluck('company', 'id');
        $query = TaxInvoice::with(['items', 'company']);

        if ($request->company_id) {
            $query->where('company_id', $request->company_id);
        }
        if ($request->from_date && $request->to_date) {
            $query->whereBetween('invoice_date', [$request->from_date, $request->to_date]);
        } elseif ($request->from_date) {
            $query->where('invoice_date', '>=', $request->from_date);
        } elseif ($request->to_date) {
            $query->where('invoice_date', '<=', $request->to_date);
        }

        $invoices = $query->orderBy('invoice_date', 'desc')->get();

        return view('manager.reports.billreport', [
            'companies' => $companies,
            'invoices' => $invoices,
            'selectedCompany' => $request->company_id,
            'fromDate' => $request->from_date,
            'toDate' => $request->to_date,
        ]);
    }

    public function exportBillReport(Request $request)
    {
        $query = TaxInvoice::with(['items', 'company']);
        if ($request->company_id) {
            $query->where('company_id', $request->company_id);
        }
        if ($request->from_date && $request->to_date) {
            $query->whereBetween('invoice_date', [$request->from_date, $request->to_date]);
        } elseif ($request->from_date) {
            $query->where('invoice_date', '>=', $request->from_date);
        } elseif ($request->to_date) {
            $query->where('invoice_date', '<=', $request->to_date);
        }

        $invoices = $query->orderBy('invoice_date', 'desc')->get();

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // Header
        $sheet->fromArray([
            'Invoice No',
            'Invoice Date',
            'Company',
            'GST',
            'Phone',
            'Email',
            'Item Description',
            'HSN',
            'Qty',
            'Rate',
            'SGST',
            'CGST',
            'IGST',
            'Amount',
            'Total Invoice Amount'
        ], NULL, 'A1');

        $rowNum = 2;
        foreach ($invoices as $inv) {
            $company = $inv->company ? $inv->company->company : '';
            if ($inv->items->count()) {
                foreach ($inv->items as $item) {
                    $sheet->fromArray([
                        $inv->invoice_no,
                        $inv->invoice_date,
                        $company,
                        $inv->gst,
                        $inv->phone,
                        $inv->email,
                        $item->description,
                        $item->hsn,
                        $item->quantity,
                        $item->rate,
                        $item->sgst,
                        $item->cgst,
                        $item->igst,
                        $item->amount,
                        $inv->total_amount,
                    ], NULL, 'A' . $rowNum);
                    $rowNum++;
                }
            } else {
                $sheet->fromArray([
                    $inv->invoice_no,
                    $inv->invoice_date,
                    $company,
                    $inv->gst,
                    $inv->phone,
                    $inv->email,
                    '',
                    '',
                    '',
                    '',
                    '',
                    '',
                    '',
                    '',
                    $inv->total_amount
                ], NULL, 'A' . $rowNum);
                $rowNum++;
            }
        }

        $filename = "Bill-Report-" . date('Ymd_His') . ".xlsx";
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header("Content-Disposition: attachment; filename=\"$filename\"");
        header('Cache-Control: max-age=0');
        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');
        exit;
    }
    public function inventoryReport(Request $request)
    {
        // For dropdowns
        $materials = PurchaseOrderItem::select('material_code', 'material_description')
            ->distinct()->orderBy('material_code')
            ->get();
        $materialOptions = $materials->mapWithKeys(function ($m) {
            return [$m->material_code => $m->material_code . ' - ' . $m->material_description];
        });

        $selectedMaterial = $request->material_code;

        // Get purchase (inward) items
        $poQuery = PurchaseOrderItem::query();
        if ($selectedMaterial) $poQuery->where('material_code', $selectedMaterial);
        if ($request->from_date && $request->to_date) {
            $poQuery->whereHas('purchaseOrder', function ($q) use ($request) {
                $q->whereBetween('purchase_order_date', [$request->from_date, $request->to_date]);
            });
        }
        $poItems = $poQuery->with('purchaseOrder')->get();

        // Get sales (outward) items
        $soQuery = SalesOrderItem::query();
        if ($selectedMaterial) $soQuery->where('material_code', $selectedMaterial);
        if ($request->from_date && $request->to_date) {
            $soQuery->whereHas('salesOrder', function ($q) use ($request) {
                $q->whereBetween('sales_order_date', [$request->from_date, $request->to_date]);
            });
        }
        $soItems = $soQuery->with('salesOrder')->get();

        // Combine all unique materials
        $allCodes = $poItems->pluck('material_code')->merge($soItems->pluck('material_code'))->unique();

        // Prepare inventory rows
        $inventory = [];
        foreach ($allCodes as $code) {
            $desc = $poItems->where('material_code', $code)->first()->material_description
                ?? $soItems->where('material_code', $code)->first()->material_description;

            $purchases = $poItems->where('material_code', $code)->sum('quantity');
            $sales = $soItems->where('material_code', $code)->sum('quantity');
            $balance = $purchases - $sales;

            $inventory[] = [
                'material_code' => $code,
                'description'   => $desc,
                'purchased'     => $purchases,
                'sold'          => $sales,
                'balance'       => $balance
            ];
        }

        return view('manager.reports.inventoryreport', [
            'inventory' => $inventory,
            'materialOptions' => $materialOptions,
            'selectedMaterial' => $selectedMaterial,
            'fromDate' => $request->from_date,
            'toDate' => $request->to_date,
        ]);
    }

    public function exportInventoryReport(Request $request)
    {
        // Similar logic as above
        $materials = PurchaseOrderItem::select('material_code', 'material_description')
            ->distinct()->orderBy('material_code')->get();
        $selectedMaterial = $request->material_code;

        $poQuery = PurchaseOrderItem::query();
        if ($selectedMaterial) $poQuery->where('material_code', $selectedMaterial);
        if ($request->from_date && $request->to_date) {
            $poQuery->whereHas('purchaseOrder', function ($q) use ($request) {
                $q->whereBetween('purchase_order_date', [$request->from_date, $request->to_date]);
            });
        }
        $poItems = $poQuery->with('purchaseOrder')->get();

        $soQuery = SalesOrderItem::query();
        if ($selectedMaterial) $soQuery->where('material_code', $selectedMaterial);
        if ($request->from_date && $request->to_date) {
            $soQuery->whereHas('salesOrder', function ($q) use ($request) {
                $q->whereBetween('sales_order_date', [$request->from_date, $request->to_date]);
            });
        }
        $soItems = $soQuery->with('salesOrder')->get();

        $allCodes = $poItems->pluck('material_code')->merge($soItems->pluck('material_code'))->unique();

        $inventory = [];
        foreach ($allCodes as $code) {
            $desc = $poItems->where('material_code', $code)->first()->material_description
                ?? $soItems->where('material_code', $code)->first()->material_description;

            $purchases = $poItems->where('material_code', $code)->sum('quantity');
            $sales = $soItems->where('material_code', $code)->sum('quantity');
            $balance = $purchases - $sales;

            $inventory[] = [
                'material_code' => $code,
                'description'   => $desc,
                'purchased'     => $purchases,
                'sold'          => $sales,
                'balance'       => $balance
            ];
        }

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->fromArray(['Material Code', 'Description', 'Purchased', 'Sold', 'Balance'], NULL, 'A1');
        $rowNum = 2;
        foreach ($inventory as $row) {
            $sheet->fromArray([
                $row['material_code'],
                $row['description'],
                $row['purchased'],
                $row['sold'],
                $row['balance'],
            ], NULL, 'A' . $rowNum);
            $rowNum++;
        }
        $filename = "Inventory-Report-" . date('Ymd_His') . ".xlsx";
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header("Content-Disposition: attachment; filename=\"$filename\"");
        header('Cache-Control: max-age=0');
        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');
        exit;
    }
}
