<?php

namespace App\Http\Controllers\Modules\Reports;

use App\Http\Controllers\Controller;
use App\Http\Traits\HasRoleViews;
use App\Models\EmployeeDetails;
use App\Models\EmployeeDailyReport;
use App\Models\EmployeeLeave;
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\Models\MachineLog;
use App\Services\ExportService;
use Illuminate\Http\Request;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

/**
 * Unified Report Controller
 *
 * Handles various reports for all roles.
 * Reference: SuperAdminReportController
 */
class ReportController extends Controller
{
    use HasRoleViews;

    protected ExportService $exportService;

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

    /**
     * Employee Report with attendance and leave data
     */
    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();

        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 $this->roleView('reports.employeereport', [
            'employees' => $employees,
            'tab' => $tab,
            'attendanceReports' => $attendanceReports,
            'leaveReports' => $leaveReports,
            'employeeId' => $employeeId,
            'fromDate' => $fromDate,
            'toDate' => $toDate,
        ]);
    }

    /**
     * Export Employee Report to Excel
     */
    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();
        }

        $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', 'First Half', 'Second Half', 'Late In', 'Early Out', 'Work Hours'];
            $sheet->fromArray($headers, null, 'A' . $headerRow);
            $this->exportService->styleHeaderRow($sheet, $headerRow, count($headers));

            $rowNum = $headerRow + 1;
            foreach ($data as $row) {
                $sheet->fromArray([
                    $row->date,
                    $row->employeeDetails ? $row->employeeDetails->name : '',
                    $row->shift,
                    $row->first_half,
                    $row->second_half,
                    $row->late_in,
                    $row->early_out,
                    $row->work_hours,
                ], null, 'A' . $rowNum);
                $rowNum++;
            }
        } elseif ($tab == 'leave') {
            $headers = ['Leave From', 'Leave To', 'Employee', 'Leave Type', 'No. of Days', 'Status'];
            $sheet->fromArray($headers, null, 'A' . $headerRow);
            $this->exportService->styleHeaderRow($sheet, $headerRow, count($headers));

            $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->autoSizeColumns($sheet, count($headers ?? []));
        $filename = "Employee-Report-{$tab}-" . date('Ymd_His') . ".xlsx";
        $this->exportService->download($spreadsheet, $filename);
    }

    /**
     * Customer/Vendor Report
     */
    public function customerReport(Request $request)
    {
        $companyNamesQuery = CustomerVendor::query();
        if ($request->company_role) {
            $companyNamesQuery->where('company_role', $request->company_role);
        }
        $companyNames = $companyNamesQuery->orderBy('company')->pluck('company')->unique();

        $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]);
        }
        $customers = $query->get();

        return $this->roleView('reports.customerreport', [
            'customers' => $customers,
            'companyNames' => $companyNames,
        ]);
    }

    /**
     * Export Customer Report to Excel
     */
    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]);
        }
        $data = $query->get();

        $spreadsheet = $this->exportService->createStyledSpreadsheet('Customer / Vendor Report');
        $sheet = $spreadsheet->getActiveSheet();
        $headerRow = 4;

        $headers = ['Role', 'Company', 'Email', 'Mobile', 'GST No', 'PAN No', 'Type', 'Status', 'Created At'];
        $sheet->fromArray($headers, null, 'A' . $headerRow);
        $this->exportService->styleHeaderRow($sheet, $headerRow, count($headers));

        $rowNum = $headerRow + 1;
        foreach ($data as $row) {
            $sheet->fromArray([
                $row->company_role,
                $row->company,
                $row->email,
                $row->mobile,
                $row->gstno,
                $row->panno,
                $row->company_type,
                $row->status,
                $row->created_at ? $row->created_at->format('Y-m-d') : '',
            ], null, 'A' . $rowNum);
            $rowNum++;
        }

        $this->exportService->autoSizeColumns($sheet, count($headers));
        $filename = "Customer-Report-" . date('Ymd_His') . ".xlsx";
        $this->exportService->download($spreadsheet, $filename);
    }

    /**
     * Machine Report
     */
    public function machineReport(Request $request)
    {
        $machineNames = Machines::orderBy('machine_name')->pluck('machine_name', 'machine_id');
        $processList = RouteCardProcess::select('process')->distinct()->pluck('process');

        $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_id)
                ->with(['routeCard'])
                ->get();
        }

        $logsQuery = 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);
            });
        }
        $machineLogs = $logsQuery->latest()->get();

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

    /**
     * Bill/Invoice Report
     */
    public function billReport(Request $request)
    {
        $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]);
        }

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

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

    /**
     * Inventory Report
     */
    public function inventoryReport(Request $request)
    {
        // Get materials from PO and SO
        $poMaterials = PurchaseOrderItem::select('item', 'description')
            ->whereNotNull('item')
            ->where('item', '!=', '')
            ->distinct()
            ->get()
            ->map(fn($m) => ['code' => $m->item, 'desc' => $m->description]);

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

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

        $selectedMaterial = $request->material_code;

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

        $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('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,
            ];
        }

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