<?php

namespace App\Http\Controllers\SuperAdmin;

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;
use App\Services\ExportService;

class SuperAdminReportController extends Controller
{
    protected $exportService;

    public function __construct(ExportService $exportService)
    {
        $this->exportService = $exportService;
    }

    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('superadmin.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 Styled Excel
        $title = $tab == 'attendance' ? 'Employee Attendance Report' : 'Employee Leave Report';
        $spreadsheet = $this->exportService->createStyledSpreadsheet($title);
        $sheet = $spreadsheet->getActiveSheet();
        $headerRow = 4;

        if ($tab == 'attendance') {
            $headers = ['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'];
            $colCount = count($headers);
            $sheet->fromArray($headers, NULL, 'A' . $headerRow);
            $this->exportService->styleHeaderRow($sheet, $headerRow, $colCount);

            $rowNum = $headerRow + 1;
            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++;
            }
            $this->exportService->styleDataRows($sheet, $headerRow + 1, $rowNum - 1, $colCount);
            $this->exportService->autoSizeColumns($sheet, $colCount);
        } elseif ($tab == 'leave') {
            $headers = ['Leave From', 'Leave To', 'Employee', 'Leave Type', 'No. of Days', 'Status'];
            $colCount = count($headers);
            $sheet->fromArray($headers, NULL, 'A' . $headerRow);
            $this->exportService->styleHeaderRow($sheet, $headerRow, $colCount);

            $rowNum = $headerRow + 1;
            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++;
            }
            $this->exportService->styleDataRows($sheet, $headerRow + 1, $rowNum - 1, $colCount);
            $this->exportService->autoSizeColumns($sheet, $colCount);
        }

        $filename = "Employee-Report-{$tab}-" . date('Ymd_His') . ".xlsx";
        $this->exportService->download($spreadsheet, $filename);
    }
    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('superadmin.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();

        // Generate Styled Excel
        $spreadsheet = $this->exportService->createStyledSpreadsheet('Customer / Vendor Report');
        $sheet = $spreadsheet->getActiveSheet();
        $headerRow = 4;
        
        $headers = ['Role', 'Company', 'Email', 'Mobile', 'GST No', 'PAN No', 'Type', 'Bank Name', 'Account No', 'IFSC', 'Status', 'Created At', 'Contact Persons'];
        $colCount = count($headers);
        $sheet->fromArray($headers, NULL, 'A' . $headerRow);
        $this->exportService->styleHeaderRow($sheet, $headerRow, $colCount);

        $rowNum = $headerRow + 1;
        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++;
        }
        
        $this->exportService->styleDataRows($sheet, $headerRow + 1, $rowNum - 1, $colCount);
        $this->exportService->autoSizeColumns($sheet, $colCount);

        $filename = "Customer-Report-" . date('Ymd_His') . ".xlsx";
        $this->exportService->download($spreadsheet, $filename);
    }
    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();
        }

        // Fetch Breakdown Logs
        $logsQuery = \App\Models\MachineLog::with(['machine', 'reporter']);
        if ($request->machine_id) {
            $logsQuery->where('machine_id', function($q) use ($request){
                $q->select('id')->from('machines')->where('machine_id', $request->machine_id);
            });
        }
        // Date filter? If request has dates? The method doesn't seem to process dates yet for machineReport main query
        // But usually reports have date filters. Let's add if present.
        // Assuming request might have from_date/to_date (based on other methods)
        if ($request->from_date && $request->to_date) {
            $logsQuery->whereBetween('start_time', [$request->from_date, $request->to_date]);
        }

        $machineLogs = $logsQuery->latest()->get();

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

    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();
        }

        // Generate Styled Excel
        $spreadsheet = $this->exportService->createStyledSpreadsheet('Machine Report');
        $sheet = $spreadsheet->getActiveSheet();
        $headerRow = 4;
        
        $headers = ['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'];
        $colCount = count($headers);
        $sheet->fromArray($headers, NULL, 'A' . $headerRow);
        $this->exportService->styleHeaderRow($sheet, $headerRow, $colCount);
        
        $rowNum = $headerRow + 1;
        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 {
                $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++;
            }
        }
        
        $this->exportService->styleDataRows($sheet, $headerRow + 1, $rowNum - 1, $colCount);
        $this->exportService->autoSizeColumns($sheet, $colCount);
        
        $filename = "Machine-Report-" . date('Ymd_His') . ".xlsx";
        $this->exportService->download($spreadsheet, $filename);
    }
    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('superadmin.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();

        // Generate Styled Excel
        $spreadsheet = $this->exportService->createStyledSpreadsheet('Bill / Invoice Report');
        $sheet = $spreadsheet->getActiveSheet();
        $headerRow = 4;
        
        $headers = ['Invoice No', 'Invoice Date', 'Company', 'GST', 'Phone', 'Email', 'Item Description', 'HSN', 'Qty', 'Rate', 'SGST', 'CGST', 'IGST', 'Amount', 'Total Invoice Amount'];
        $colCount = count($headers);
        $sheet->fromArray($headers, NULL, 'A' . $headerRow);
        $this->exportService->styleHeaderRow($sheet, $headerRow, $colCount);

        $rowNum = $headerRow + 1;
        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++;
            }
        }

        $this->exportService->styleDataRows($sheet, $headerRow + 1, $rowNum - 1, $colCount);
        $this->exportService->autoSizeColumns($sheet, $colCount);
        
        $filename = "Bill-Report-" . date('Ymd_His') . ".xlsx";
        $this->exportService->download($spreadsheet, $filename);
    }
    public function inventoryReport(Request $request)
    {
        // For dropdowns - get materials from both PO and SO
        $poMaterials = PurchaseOrderItem::select('item', 'description')
            ->whereNotNull('item')
            ->where('item', '!=', '')
            ->distinct()
            ->get()
            ->map(function ($m) {
                return ['code' => $m->item, 'desc' => $m->description];
            });

        $soMaterials = SalesOrderItem::select('material_code', 'material_description')
            ->whereNotNull('material_code')
            ->where('material_code', '!=', '')
            ->distinct()
            ->get()
            ->map(function ($m) {
                return ['code' => $m->material_code, 'desc' => $m->material_description];
            });

        $allMaterials = $poMaterials->merge($soMaterials)->unique('code')->sortBy('code');
        $materialOptions = $allMaterials->mapWithKeys(function ($m) {
            return [$m['code'] => $m['code'] . ' - ' . ($m['desc'] ?? 'N/A')];
        });

        $selectedMaterial = $request->material_code;

        // Get purchase (inward) items - PurchaseOrderItem uses 'item' column
        $poQuery = PurchaseOrderItem::query();
        if ($selectedMaterial) $poQuery->where('item', $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 - SalesOrderItem uses 'material_code' column
        $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 (using 'item' from PO and 'material_code' from SO)
        $allCodes = $poItems->pluck('item')->merge($soItems->pluck('material_code'))->filter()->unique();

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

            $desc = $poItem->description ?? ($soItem->material_description ?? 'N/A');

            $purchases = $poItems->where('item', $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('superadmin.reports.inventoryreport', [
            'inventory' => $inventory,
            'materialOptions' => $materialOptions,
            'selectedMaterial' => $selectedMaterial,
            'fromDate' => $request->from_date,
            'toDate' => $request->to_date,
        ]);
    }

    public function exportInventoryReport(Request $request)
    {
        // Similar logic as inventoryReport
        $selectedMaterial = $request->material_code;

        // Get purchase (inward) items - PurchaseOrderItem uses 'item' column
        $poQuery = PurchaseOrderItem::query();
        if ($selectedMaterial) $poQuery->where('item', $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 - SalesOrderItem uses 'material_code' column
        $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 (using 'item' from PO and 'material_code' from SO)
        $allCodes = $poItems->pluck('item')->merge($soItems->pluck('material_code'))->filter()->unique();

        $inventory = [];
        foreach ($allCodes as $code) {
            $poItem = $poItems->where('item', $code)->first();
            $soItem = $soItems->where('material_code', $code)->first();

            $desc = $poItem->description ?? ($soItem->material_description ?? 'N/A');

            $purchases = $poItems->where('item', $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
            ];
        }

        // Generate Styled Excel
        $spreadsheet = $this->exportService->createStyledSpreadsheet('Inventory Report');
        $sheet = $spreadsheet->getActiveSheet();
        $headerRow = 4;
        
        $headers = ['Material Code', 'Description', 'Purchased', 'Sold', 'Balance'];
        $colCount = count($headers);
        $sheet->fromArray($headers, NULL, 'A' . $headerRow);
        $this->exportService->styleHeaderRow($sheet, $headerRow, $colCount);
        
        $rowNum = $headerRow + 1;
        foreach ($inventory as $row) {
            $sheet->fromArray([
                $row['material_code'],
                $row['description'],
                $row['purchased'],
                $row['sold'],
                $row['balance'],
            ], NULL, 'A' . $rowNum);
            $rowNum++;
        }
        
        $this->exportService->styleDataRows($sheet, $headerRow + 1, $rowNum - 1, $colCount);
        $this->exportService->autoSizeColumns($sheet, $colCount);
        
        $filename = "Inventory-Report-" . date('Ymd_His') . ".xlsx";
        $this->exportService->download($spreadsheet, $filename);
    }
}
