<?php
namespace App\Controllers;

class WorkOrders extends BaseController
{
    private const ALLOWED_MIMES = [
        'image/jpeg','image/png','image/gif','image/webp',
        'application/pdf',
        'application/msword',
        'application/vnd.openxmlformats-officedocument.wordprocessingml.document',
        'application/vnd.ms-excel',
        'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
    ];
    private const MAX_FILE_SIZE = 5 * 1024 * 1024;

    public function index()
    {
        $filters = [
            'search'   => $this->request->getGet('search')   ?? '',
            'status'   => $this->request->getGet('status')   ?? '',
            'priority' => $this->request->getGet('priority') ?? '',
            'facility' => $this->request->getGet('facility') ?? '',
            'type'     => $this->request->getGet('type')     ?? '',
            'category' => $this->request->getGet('category') ?? '',
            'from'     => $this->request->getGet('from')     ?? '',
            'to'       => $this->request->getGet('to')       ?? '',
        ];
        $role = session()->get('user_role');
        $uid  = session()->get('user_id');

        $q = $this->db->table('work_orders w')
            ->select('w.*, f.name as facility_name, u.name as assigned_name, a.name as asset_name')
            ->join('facilities f', 'f.id=w.facility_id', 'left')
            ->join('users u',      'u.id=w.assigned_to',  'left')
            ->join('assets a',     'a.id=w.asset_id',     'left');

        if ($role === 'technician') {
            $q->where('w.assigned_to', $uid);
        }
        $this->scopeFacilities($q, 'w.facility_id');

        if ($filters['search'])   $q->groupStart()->like('w.wo_number',$filters['search'])->orLike('w.title',$filters['search'])->orLike('w.requester_name',$filters['search'])->groupEnd();
        if ($filters['status'])   $q->where('w.status',      $filters['status']);
        if ($filters['priority']) $q->where('w.priority',    $filters['priority']);
        if ($filters['facility']) $q->where('w.facility_id', (int)$filters['facility']);
        if ($filters['type'])     $q->where('w.type',        $filters['type']);
        if ($filters['category']) $q->where('w.category',    $filters['category']);
        if ($filters['from'])     $q->where('DATE(w.created_at) >=', $filters['from']);
        if ($filters['to'])       $q->where('DATE(w.created_at) <=', $filters['to']);

        $totalCount  = (clone $q)->countAllResults(false);

        $pg          = $this->paginate(25);
        $currentPage = $pg['page'];
        $perPage     = $pg['perPage'];
        $offset      = $pg['offset'];

        $workOrders = $q->orderBy('CASE w.priority WHEN \'critical\' THEN 1 WHEN \'high\' THEN 2 WHEN \'medium\' THEN 3 ELSE 4 END', '')
                        ->orderBy('w.created_at', 'DESC')
                        ->limit($perPage, $offset)
                        ->get()->getResultArray();

        $facilities = $this->scopeFacilities(
            $this->db->table('facilities')->where('status', 'active')
        )->orderBy('name', 'ASC')->get()->getResultArray();

        $kpi = [
            'open'        => $this->db->table('work_orders')->whereIn('status',['new','assigned'])->countAllResults(),
            'in_progress' => $this->db->table('work_orders')->where('status','in_progress')->countAllResults(),
            'overdue'     => $this->db->table('work_orders')->where('sla_breached',1)->whereIn('status',['new','assigned','in_progress'])->countAllResults(),
            'completed'   => $this->db->table('work_orders')->where('status','completed')->where('DATE(completed_at)',date('Y-m-d'))->countAllResults(),
        ];

        return view('workorders/index', $this->viewData([
            'title'       => 'Work Orders',
            'workOrders'  => $workOrders,
            'filters'     => $filters,
            'facilities'  => $facilities,
            'kpi'         => $kpi,
            'totalCount'  => $totalCount,
            'perPage'     => $perPage,
            'currentPage' => $currentPage,
        ]));
    }

    public function create()
    {
        $facilities  = $this->scopeFacilities(
            $this->db->table('facilities')->where('status', 'active')
        )->get()->getResultArray();
        $assets      = $this->db->table('assets')->where('status','active')->orderBy('name','ASC')->get()->getResultArray();
        $technicians = $this->db->table('users u')
            ->join('roles r','r.id=u.role_id')
            ->where('r.name','technician')->where('u.status','active')
            ->select('u.*')->get()->getResultArray();
        $vendors = $this->db->table('vendors')->where('status','active')->orderBy('name','ASC')->get()->getResultArray();
        $units   = $this->db->table('units')->where('status','occupied')->orderBy('unit_number','ASC')->get()->getResultArray();

        return view('workorders/create', $this->viewData([
            'title'       => 'Create Work Order',
            'facilities'  => $facilities,
            'assets'      => $assets,
            'technicians' => $technicians,
            'vendors'     => $vendors,
            'units'       => $units,
        ]));
    }

    public function store()
    {
        $rules = [
            'title'       => 'required|min_length[5]|max_length[255]',
            'facility_id' => 'required|integer',
            'type'        => 'required',
            'priority'    => 'required|in_list[low,medium,high,critical]',
        ];
        if (!$this->validate($rules)) {
            return redirect()->back()->withInput()->with('errors', $this->validator->getErrors());
        }

        $facilityId = (int) $this->request->getPost('facility_id');
        $this->assertFacilityAccess($facilityId);

        $priority    = $this->request->getPost('priority');
        $role        = session()->get('user_role');
        $uid         = session()->get('user_id');
        $assignedTo  = $this->request->getPost('assigned_to') ?: null;
        $vendorId    = $this->request->getPost('vendor_id')   ?: null;

        $privilegedRoles = ['super_admin','facility_manager'];
        $needsApproval   = in_array($priority,['high','critical']) && !in_array($role,$privilegedRoles);
        $approvalStatus  = $needsApproval ? 'pending' : 'approved';

        $slaRule = $this->db->table('sla_rules')->where('priority',$priority)->get()->getRowArray();
        $slaDue  = $slaRule ? date('Y-m-d H:i:s', strtotime("+{$slaRule['resolution_hours']} hours")) : null;

        $woNumber  = $this->generateNumber('WO','work_orders','wo_number');
        $companyId = $this->companyScope()->resolveCompanyIdForFacility($facilityId);

        $insert = [
            'wo_number'       => $woNumber,
            'facility_id'     => $facilityId,
            'unit_id'         => $this->request->getPost('unit_id') ?: null,
            'asset_id'        => $this->request->getPost('asset_id') ?: null,
            'title'           => esc($this->request->getPost('title')),
            'description'     => esc($this->request->getPost('description')),
            'type'            => $this->request->getPost('type'),
            'category'        => $this->request->getPost('category') ?: null,
            'priority'        => $priority,
            'status'          => $assignedTo ? 'assigned' : 'new',
            'assigned_to'     => $assignedTo,
            'vendor_id'       => $vendorId,
            'created_by'      => $uid,
            'sla_due'         => $slaDue,
            'planned_start'   => $this->request->getPost('planned_start') ?: null,
            'planned_end'     => $this->request->getPost('planned_end')   ?: null,
            'estimated_cost'  => $this->request->getPost('estimated_cost') ?: null,
            'requester_name'  => esc($this->request->getPost('requester_name')  ?? ''),
            'requester_phone' => esc($this->request->getPost('requester_phone') ?? ''),
            'requester_email' => esc($this->request->getPost('requester_email') ?? ''),
            'approval_status' => $approvalStatus,
        ];
        if ($this->db->fieldExists('company_id', 'work_orders')) {
            $insert['company_id'] = $companyId;
        }
        $this->db->table('work_orders')->insert($insert);
        $woId = $this->db->insertID();

        if ($assignedTo) {
            $this->_notify($assignedTo,"Work Order Assigned: $woNumber",'You have been assigned: '.esc($this->request->getPost('title')),'work_order',$woId);
        }
        if ($needsApproval) {
            $managers = $this->db->table('users u')->join('roles r','r.id=u.role_id')->whereIn('r.name',['super_admin','facility_manager'])->where('u.status','active')->select('u.id')->get()->getResultArray();
            foreach ($managers as $m) $this->_notify($m['id'],"Approval Needed: $woNumber","High/critical WO requires approval.",'approval',$woId);
        }

        $this->_handleFileUpload($woId);
        $this->logActivity('create','work_orders',$woId,"Created WO $woNumber");

        return redirect()->to(base_url('workorders/view/'.$woId))->with('success',"Work Order $woNumber created.");
    }

    public function view(int $id)
    {
        $role = session()->get('user_role');
        $uid  = session()->get('user_id');

        $wo = $this->db->table('work_orders w')
            ->select('w.*, f.name as facility_name, a.name as asset_name, a.asset_code,
                      a.category as asset_category, a.brand as asset_brand, a.model as asset_model,
                      a.warranty_expiry, a.next_maintenance,
                      u.name as assigned_name, cu.name as created_by_name,
                      v.name as vendor_name, au.name as approved_by_name,
                      un.unit_number, un.tenant_name, un.tenant_mobile')
            ->join('facilities f',  'f.id=w.facility_id',  'left')
            ->join('assets a',      'a.id=w.asset_id',     'left')
            ->join('users u',       'u.id=w.assigned_to',  'left')
            ->join('users cu',      'cu.id=w.created_by',  'left')
            ->join('vendors v',     'v.id=w.vendor_id',    'left')
            ->join('users au',      'au.id=w.approved_by', 'left')
            ->join('units un',      'un.id=w.unit_id',     'left')
            ->where('w.id',$id)->get()->getRowArray();

        if (!$wo) {
            throw \CodeIgniter\Exceptions\PageNotFoundException::forPageNotFound();
        }
        $this->assertWorkOrderAccess($id);
        if ($role === 'technician' && (int) $wo['assigned_to'] !== (int) $uid) {
            return redirect()->to(base_url('workorders'))->with('error', 'Access denied.');
        }

        $comments     = $this->db->table('wo_comments c')->select('c.*, u.name as user_name')->join('users u','u.id=c.user_id','left')->where('c.wo_id',$id)->orderBy('c.created_at','ASC')->get()->getResultArray();
        $attachments  = $this->db->table('wo_attachments')->where('wo_id',$id)->orderBy('id','DESC')->get()->getResultArray();
        $labor        = $this->db->table('wo_labor l')->select('l.*, u.name as tech_name')->join('users u','u.id=l.user_id','left')->where('l.wo_id',$id)->orderBy('l.work_date','ASC')->get()->getResultArray();
        $materials    = $this->db->table('wo_materials m')->select('m.*, u.name as added_by_name')->join('users u','u.id=m.added_by','left')->where('m.wo_id',$id)->orderBy('m.created_at','ASC')->get()->getResultArray();
        $costing      = $this->db->table('maintenance_costing')->where('wo_id',$id)->get()->getRowArray();
        $approvals    = $this->db->table('wo_approvals wa')->select('wa.*, u.name as actioned_by_name')->join('users u','u.id=wa.actioned_by','left')->where('wa.wo_id',$id)->orderBy('wa.created_at','DESC')->get()->getResultArray();

        // Chat messages (DB-02 fix — wo_chat_messages now used)
        $chatMessages = $this->db->table('wo_chat_messages m')
            ->select('m.id, m.user_id, m.message, m.created_at, u.name as sender_name')
            ->join('users u','u.id=m.user_id','left')
            ->where('m.wo_id',$id)->orderBy('m.created_at','ASC')->get()->getResultArray();

        $meterReadings = $wo['asset_id']
            ? $this->db->table('asset_meter_readings amr')->select('amr.*, u.name as recorded_by_name')->join('users u','u.id=amr.recorded_by','left')->where('amr.asset_id',$wo['asset_id'])->orderBy('amr.reading_date','DESC')->limit(5)->get()->getResultArray()
            : [];

        $technicians    = $this->db->table('users u')->join('roles r','r.id=u.role_id')->where('r.name','technician')->where('u.status','active')->select('u.*')->get()->getResultArray();
        $inventoryItems = $this->db->table('inventory_items')->where('quantity >',0)->orderBy('name','ASC')->get()->getResultArray();

        $laborTotal    = array_sum(array_column($labor,   'labor_cost'));
        $materialTotal = array_sum(array_column($materials,'total_cost'));
        $vendorCost    = (float)($costing['vendor_cost'] ?? 0);
        $totalCost     = $laborTotal + $materialTotal + $vendorCost;

        $workflow = $this->workflowService()->workflowSummary($id);

        return view('workorders/view', $this->viewData([
            'title'          => $wo['wo_number'],
            'wo'             => $wo,
            'comments'       => $comments,
            'attachments'    => $attachments,
            'labor'          => $labor,
            'materials'      => $materials,
            'costing'        => $costing,
            'approvals'      => $approvals,
            'chatMessages'   => $chatMessages,
            'meterReadings'  => $meterReadings,
            'technicians'    => $technicians,
            'inventoryItems' => $inventoryItems,
            'laborTotal'     => $laborTotal,
            'materialTotal'  => $materialTotal,
            'vendorCost'     => $vendorCost,
            'totalCost'      => $totalCost,
            'workflow'       => $workflow,
        ]));
    }

    public function edit(int $id)
    {
        $wo = $this->db->table('work_orders')->where('id',$id)->get()->getRowArray();
        if (!$wo) throw \CodeIgniter\Exceptions\PageNotFoundException::forPageNotFound();
        $this->requireRole('super_admin','facility_manager','procurement_officer');

        $facilities  = $this->scopeFacilities(
            $this->db->table('facilities')->where('status', 'active')
        )->get()->getResultArray();
        $assets      = $this->db->table('assets')->where('status','active')->get()->getResultArray();
        $technicians = $this->db->table('users u')->join('roles r','r.id=u.role_id')->where('r.name','technician')->where('u.status','active')->select('u.*')->get()->getResultArray();
        $vendors     = $this->db->table('vendors')->where('status','active')->orderBy('name','ASC')->get()->getResultArray();
        $units       = $this->db->table('units')->orderBy('unit_number','ASC')->get()->getResultArray();

        return view('workorders/edit', $this->viewData([
            'title'       => 'Edit '.$wo['wo_number'],
            'wo'          => $wo,
            'facilities'  => $facilities,
            'assets'      => $assets,
            'technicians' => $technicians,
            'vendors'     => $vendors,
            'units'       => $units,
        ]));
    }

    public function update(int $id)
    {
        $this->requireRole('super_admin','facility_manager','procurement_officer');
        $status     = $this->request->getPost('status');
        $assignedTo = $this->request->getPost('assigned_to') ?: null;
        $existing   = $this->db->table('work_orders')->select('completed_at,started_at')->where('id',$id)->get()->getRowArray();

        $update = [
            'title'            => esc($this->request->getPost('title')),
            'description'      => esc($this->request->getPost('description')),
            'type'             => $this->request->getPost('type'),
            'category'         => $this->request->getPost('category') ?: null,
            'priority'         => $this->request->getPost('priority'),
            'status'           => $status,
            'facility_id'      => (int)$this->request->getPost('facility_id'),
            'unit_id'          => $this->request->getPost('unit_id') ?: null,
            'asset_id'         => $this->request->getPost('asset_id') ?: null,
            'assigned_to'      => $assignedTo,
            'vendor_id'        => $this->request->getPost('vendor_id') ?: null,
            'planned_start'    => $this->request->getPost('planned_start') ?: null,
            'planned_end'      => $this->request->getPost('planned_end')   ?: null,
            'estimated_cost'   => $this->request->getPost('estimated_cost') ?: null,
            'actual_cost'      => $this->request->getPost('actual_cost')    ?: null,
            'completion_notes' => esc($this->request->getPost('completion_notes') ?? ''),
        ];

        if ($status === 'completed' && empty($existing['completed_at'])) $update['completed_at'] = date('Y-m-d H:i:s');
        if ($status === 'in_progress' && empty($existing['started_at']))  $update['started_at']   = date('Y-m-d H:i:s');

        $this->db->table('work_orders')->where('id',$id)->update($update);
        $this->logActivity('update','work_orders',$id);
        return redirect()->to(base_url('workorders/view/'.$id))->with('success','Work Order updated.');
    }

    public function delete(int $id)
    {
        $this->requireRole('super_admin','facility_manager');
        $this->db->table('work_orders')->where('id',$id)->update(['status'=>'cancelled','deleted_at'=>date('Y-m-d H:i:s')]);
        $this->logActivity('delete','work_orders',$id);
        return redirect()->to(base_url('workorders'))->with('success','Work Order cancelled.');
    }

    public function updateStatus()
    {
        $id     = (int)$this->request->getPost('id');
        $status = $this->request->getPost('status');
        if (!$id || !$status) return $this->response->setJSON(['status'=>false,'message'=>'Invalid request']);

        $uid  = session()->get('user_id');
        $role = session()->get('user_role');
        $wo   = $this->db->table('work_orders')->where('id',$id)->get()->getRowArray();
        if (!$wo) return $this->response->setJSON(['status'=>false,'message'=>'Not found']);
        if ($role === 'technician' && (int)$wo['assigned_to'] !== (int)$uid) {
            return $this->response->setJSON(['status'=>false,'message'=>'Access denied']);
        }

        try {
            $this->workflowGuard()->assertTransition($wo['status'], $status);
            if ($status === 'completed') {
                $this->workflowGuard()->assertCanComplete($wo);
            }
            if ($status === 'closed') {
                $this->workflowGuard()->assertCanClose($wo);
            }
        } catch (\Throwable $e) {
            return $this->response->setJSON(['status'=>false,'message'=>$e->getMessage()]);
        }

        $update = ['status'=>$status,'completion_notes'=>esc($this->request->getPost('completion_notes')??'')];
        $cost = $this->request->getPost('actual_cost');
        if ($cost !== null && $cost !== '') $update['actual_cost'] = (float)$cost;
        if ($status === 'completed' && empty($wo['completed_at'])) $update['completed_at'] = date('Y-m-d H:i:s');
        if ($status === 'in_progress' && empty($wo['started_at'])) $update['started_at']   = date('Y-m-d H:i:s');

        $this->db->table('work_orders')->where('id',$id)->update($update);

        if ($status === 'completed') {
            try {
                $this->workflowService()->onStatusChanged($id, 'completed');
            } catch (\Throwable $e) {
                log_message('error', 'WO workflow: ' . $e->getMessage());
            }
        }

        if ($wo['sla_due'] && strtotime($wo['sla_due']) < time() && !$wo['sla_breached']) {
            $this->db->table('work_orders')->where('id',$id)->update(['sla_breached'=>1]);
        }

        $this->db->table('wo_comments')->insert([
            'wo_id'=>$id,'user_id'=>$uid,
            'comment'=>'Status changed to: '.ucfirst(str_replace('_',' ',$status)).($this->request->getPost('completion_notes') ? "\n".$this->request->getPost('completion_notes') : ''),
        ]);

        $this->logActivity('status_change','work_orders',$id,"Status → $status");
        return $this->response->setJSON(['status'=>true,'message'=>'Status updated',csrf_token()=>csrf_hash()]);
    }

    public function addComment(int $id)
    {
        $comment = trim($this->request->getPost('comment') ?? '');
        if (empty($comment)) return $this->response->setJSON(['status'=>false,'message'=>'Comment cannot be empty']);

        $uid  = session()->get('user_id');
        $role = session()->get('user_role');
        $wo   = $this->db->table('work_orders')->select('assigned_to')->where('id',$id)->get()->getRowArray();
        if ($role === 'technician' && $wo && (int)$wo['assigned_to'] !== (int)$uid) {
            return $this->response->setJSON(['status'=>false,'message'=>'Access denied']);
        }

        $imagePath = null;
        $file = $this->request->getFile('comment_image');
        if ($file && $file->isValid() && !$file->hasMoved()) $imagePath = $this->_saveFile($file,'workorders');

        $this->db->table('wo_comments')->insert(['wo_id'=>$id,'user_id'=>$uid,'comment'=>esc($comment),'image_path'=>$imagePath]);
        $user = $this->db->table('users')->where('id',$uid)->get()->getRowArray();

        return $this->response->setJSON([
            'status'=>true,'id'=>$this->db->insertID(),
            'comment'=>esc($comment),'image_path'=>$imagePath,
            'user_name'=>esc($user['name']??'You'),'created_at'=>date('d M Y H:i'),
            csrf_token()=>csrf_hash(),
        ]);
    }

    public function uploadAttachment(int $id)
    {
        $file = $this->request->getFile('attachment');
        if (!$file || !$file->isValid()) return $this->response->setJSON(['status'=>false,'message'=>'No file received']);
        $path = $this->_saveFile($file,'workorders');
        if (!$path) return $this->response->setJSON(['status'=>false,'message'=>'Invalid file type or size. Max 5MB.']);
        $this->db->table('wo_attachments')->insert(['wo_id'=>$id,'file_name'=>esc($file->getClientName()),'file_path'=>$path,'uploaded_by'=>session()->get('user_id')]);
        return $this->response->setJSON(['status'=>true,'file'=>basename($path),'original'=>esc($file->getClientName()),csrf_token()=>csrf_hash()]);
    }

    public function escalate(int $id)
    {
        $this->requireRole('super_admin','facility_manager');
        $wo = $this->db->table('work_orders')->where('id',$id)->get()->getRowArray();
        if (!$wo) return redirect()->back()->with('error','Work order not found');
        $levels = ['low'=>'medium','medium'=>'high','high'=>'critical','critical'=>'critical'];
        $newPriority = $levels[$wo['priority']] ?? 'critical';
        $this->db->table('work_orders')->where('id',$id)->update(['priority'=>$newPriority,'sla_breached'=>1]);
        $this->logActivity('escalate','work_orders',$id,"Escalated → $newPriority");
        return redirect()->to(base_url('workorders/view/'.$id))->with('success','Work order escalated to '.ucfirst($newPriority).' priority.');
    }

    public function addLabor(int $id)
    {
        $userId   = $this->request->getPost('user_id') ?: session()->get('user_id');
        $hours    = (float)($this->request->getPost('hours_worked')   ?? 0);
        $overtime = (float)($this->request->getPost('overtime_hours') ?? 0);
        $rate     = (float)($this->request->getPost('hourly_rate')    ?? 0);
        if ($rate <= 0) { $emp = $this->db->table('employees')->where('user_id',$userId)->get()->getRowArray(); $rate = $emp ? (float)$emp['hourly_rate'] : 0; }
        $laborCost = ($hours + $overtime * 1.5) * $rate;
        $this->db->table('wo_labor')->insert(['wo_id'=>$id,'user_id'=>$userId,'work_date'=>$this->request->getPost('work_date') ?: date('Y-m-d'),'start_time'=>$this->request->getPost('start_time') ?: null,'end_time'=>$this->request->getPost('end_time') ?: null,'hours_worked'=>$hours,'overtime_hours'=>$overtime,'hourly_rate'=>$rate,'labor_cost'=>$laborCost,'notes'=>esc($this->request->getPost('labor_notes')??''),'created_by'=>session()->get('user_id')]);
        $this->_refreshCosting($id);
        return redirect()->to(base_url('workorders/view/'.$id).'#tab-labor')->with('success','Labor entry added.');
    }

    public function deleteLabor(int $laborId)
    {
        $this->requireRole('super_admin','facility_manager');
        $entry = $this->db->table('wo_labor')->where('id',$laborId)->get()->getRowArray();
        if ($entry) { $this->db->table('wo_labor')->where('id',$laborId)->delete(); $this->_refreshCosting($entry['wo_id']); return redirect()->to(base_url('workorders/view/'.$entry['wo_id']).'#tab-labor')->with('success','Labor entry removed.'); }
        return redirect()->back()->with('error','Entry not found.');
    }

    public function addMaterial(int $id)
    {
        $itemId   = $this->request->getPost('item_id') ?: null;
        $qty      = (float)($this->request->getPost('quantity')  ?? 1);
        $unitCost = (float)($this->request->getPost('unit_cost') ?? 0);
        $itemName = esc($this->request->getPost('item_name') ?? '');
        $itemCode = null;
        $deducted = 0;

        if ($itemId) {
            $inv = $this->db->table('inventory_items')->where('id',$itemId)->get()->getRowArray();
            if ($inv) {
                $itemName = $inv['name']; $itemCode = $inv['item_code'];
                if ($unitCost <= 0) $unitCost = (float)$inv['unit_cost'];
                if ($inv['quantity'] < $qty) return redirect()->back()->with('error',"Insufficient stock for '{$inv['name']}'. Available: {$inv['quantity']} {$inv['unit']}.");
                $this->db->table('inventory_items')->where('id',$itemId)->update(['quantity'=>$inv['quantity']-$qty]);
                $this->db->table('stock_movements')->insert(['item_id'=>$itemId,'movement_type'=>'out','quantity'=>(int)$qty,'reference'=>'WO-'.$id,'notes'=>'Used in work order','created_by'=>session()->get('user_id')]);
                $deducted = 1;
            }
        }

        $this->db->table('wo_materials')->insert(['wo_id'=>$id,'item_id'=>$itemId,'item_name'=>$itemName,'item_code'=>$itemCode,'quantity'=>$qty,'unit_cost'=>$unitCost,'total_cost'=>$qty*$unitCost,'deducted_from_stock'=>$deducted,'notes'=>esc($this->request->getPost('mat_notes')??''),'added_by'=>session()->get('user_id')]);
        $this->_refreshCosting($id);
        return redirect()->to(base_url('workorders/view/'.$id).'#tab-materials')->with('success','Material added.');
    }

    public function deleteMaterial(int $matId)
    {
        $this->requireRole('super_admin','facility_manager');
        $entry = $this->db->table('wo_materials')->where('id',$matId)->get()->getRowArray();
        if ($entry) {
            if ($entry['deducted_from_stock'] && $entry['item_id']) {
                $inv = $this->db->table('inventory_items')->where('id',$entry['item_id'])->get()->getRowArray();
                if ($inv) { $this->db->table('inventory_items')->where('id',$entry['item_id'])->update(['quantity'=>$inv['quantity']+$entry['quantity']]); $this->db->table('stock_movements')->insert(['item_id'=>$entry['item_id'],'movement_type'=>'in','quantity'=>(int)$entry['quantity'],'reference'=>'WO-'.$entry['wo_id'].'-RETURN','notes'=>'Returned from WO','created_by'=>session()->get('user_id')]); }
            }
            $this->db->table('wo_materials')->where('id',$matId)->delete();
            $this->_refreshCosting($entry['wo_id']);
            return redirect()->to(base_url('workorders/view/'.$entry['wo_id']).'#tab-materials')->with('success','Material removed and stock restored.');
        }
        return redirect()->back()->with('error','Entry not found.');
    }

    public function approve(int $id)
    {
        $this->requireRole('super_admin','facility_manager');
        $this->db->table('work_orders')->where('id',$id)->update(['approval_status'=>'approved','approved_by'=>session()->get('user_id'),'approved_at'=>date('Y-m-d H:i:s')]);
        $this->db->table('wo_approvals')->insert(['wo_id'=>$id,'approval_type'=>$this->request->getPost('approval_type')?:'supervisor','action'=>'approved','notes'=>esc($this->request->getPost('approval_notes')??''),'actioned_by'=>session()->get('user_id')]);
        $wo = $this->db->table('work_orders')->select('wo_number,created_by')->where('id',$id)->get()->getRowArray();
        if ($wo) $this->_notify($wo['created_by'],"WO Approved: {$wo['wo_number']}",'Your work order has been approved.','approval',$id);
        $this->logActivity('approve','work_orders',$id);
        return redirect()->to(base_url('workorders/view/'.$id).'#tab-approval')->with('success','Work Order approved.');
    }

    public function reject(int $id)
    {
        $this->requireRole('super_admin','facility_manager');
        $this->db->table('work_orders')->where('id',$id)->update(['approval_status'=>'rejected']);
        $this->db->table('wo_approvals')->insert(['wo_id'=>$id,'approval_type'=>$this->request->getPost('approval_type')?:'supervisor','action'=>'rejected','notes'=>esc($this->request->getPost('approval_notes')??''),'actioned_by'=>session()->get('user_id')]);
        $this->logActivity('reject','work_orders',$id);
        return redirect()->to(base_url('workorders/view/'.$id).'#tab-approval')->with('success','Work Order rejected.');
    }

    public function qaApprove(int $id)
    {
        $this->requireRole('super_admin', 'facility_manager', 'qa_inspector');
        $this->assertWorkOrderAccess($id);
        try {
            $this->workflowService()->qaApprove($id, (int) session()->get('user_id'), $this->request->getPost('notes'));
            $this->logActivity('qa_approve', 'work_orders', $id);
            return redirect()->to(base_url('workorders/view/' . $id) . '#tab-closure')->with('success', 'QA approved. Awaiting client sign-off.');
        } catch (\Throwable $e) {
            return redirect()->back()->with('error', $e->getMessage());
        }
    }

    public function qaReject(int $id)
    {
        $this->requireRole('super_admin', 'facility_manager', 'qa_inspector');
        $this->assertWorkOrderAccess($id);
        try {
            $this->workflowService()->qaReject($id, (int) session()->get('user_id'), $this->request->getPost('notes'));
            return redirect()->to(base_url('workorders/view/' . $id))->with('warning', 'QA rejected — work order returned to in progress.');
        } catch (\Throwable $e) {
            return redirect()->back()->with('error', $e->getMessage());
        }
    }

    public function clientApprove(int $id)
    {
        $this->requireRole('super_admin', 'facility_manager', 'client');
        $this->assertWorkOrderAccess($id);
        try {
            $inv = $this->workflowService()->clientApprove($id, (int) session()->get('user_id'), $this->request->getPost('notes'));
            $this->logActivity('client_approve', 'work_orders', $id, 'Invoice ' . $inv['invoice_number']);
            foreach ($this->usersByRole('finance_manager') as $fm) {
                $this->_notify($fm['id'], 'Draft Invoice Created', $inv['invoice_number'] . ' ready for finance review.', 'invoice', $inv['invoice_id']);
            }
            return redirect()->to(base_url('finance/invoices/view/' . $inv['invoice_id']))
                ->with('success', 'Client approved. Draft invoice ' . $inv['invoice_number'] . ' created.');
        } catch (\Throwable $e) {
            return redirect()->back()->with('error', $e->getMessage());
        }
    }

    public function clientReject(int $id)
    {
        $this->requireRole('super_admin', 'facility_manager', 'client');
        $this->assertWorkOrderAccess($id);
        try {
            $this->workflowService()->clientReject($id, (int) session()->get('user_id'), $this->request->getPost('notes'));
            return redirect()->to(base_url('workorders/view/' . $id) . '#tab-closure')->with('success', 'Client approval rejected.');
        } catch (\Throwable $e) {
            return redirect()->back()->with('error', $e->getMessage());
        }
    }

    public function addMeterReading(int $id)
    {
        $wo = $this->db->table('work_orders')->where('id',$id)->get()->getRowArray();
        if (!$wo || !$wo['asset_id']) return redirect()->back()->with('error','No asset linked.');
        $this->db->table('asset_meter_readings')->insert(['asset_id'=>$wo['asset_id'],'wo_id'=>$id,'reading_type'=>$this->request->getPost('reading_type')?:'hours','reading_value'=>(float)($this->request->getPost('reading_value')??0),'reading_date'=>$this->request->getPost('reading_date')?:date('Y-m-d'),'notes'=>esc($this->request->getPost('meter_notes')??''),'recorded_by'=>session()->get('user_id')]);
        return redirect()->to(base_url('workorders/view/'.$id).'#tab-asset')->with('success','Meter reading recorded.');
    }

    public function printView(int $id)
    {
        $wo = $this->db->table('work_orders w')->select('w.*, f.name as facility_name, u.name as assigned_name, a.name as asset_name, un.unit_number')->join('facilities f','f.id=w.facility_id','left')->join('users u','u.id=w.assigned_to','left')->join('assets a','a.id=w.asset_id','left')->join('units un','un.id=w.unit_id','left')->where('w.id',$id)->get()->getRowArray();
        if (!$wo) throw \CodeIgniter\Exceptions\PageNotFoundException::forPageNotFound();
        $role = session()->get('user_role'); $uid = session()->get('user_id');
        if ($role === 'technician' && (int)$wo['assigned_to'] !== (int)$uid) return redirect()->to(base_url('workorders'))->with('error','Access denied.');
        $materials = $this->db->table('wo_materials')->where('wo_id',$id)->get()->getResultArray();
        return view('workorders/print', $this->viewData(['title'=>'Print: '.$wo['wo_number'],'wo'=>$wo,'materials'=>$materials,'usePdf'=>true]));
    }

    public function schedule()
    {
        $pg          = $this->paginate(20);
        $currentPage = $pg['page'];
        $perPage     = $pg['perPage'];
        $offset      = $pg['offset'];

        $upcomingQ = $this->db->table('work_orders w')
            ->select('w.*, f.name as facility_name, u.name as assigned_name, a.name as asset_name')
            ->join('facilities f','f.id=w.facility_id','left')
            ->join('users u','u.id=w.assigned_to','left')
            ->join('assets a','a.id=w.asset_id','left')
            ->whereIn('w.type',['preventive','predictive'])
            ->whereIn('w.status',['new','assigned','in_progress']);
        $this->scopeFacilities($upcomingQ, 'w.facility_id');
        $upcoming = $upcomingQ->orderBy('w.sla_due','ASC')
            ->limit($perPage,$offset)->get()->getResultArray();

        $facilities = $this->scopeFacilities(
            $this->db->table('facilities')->where('status', 'active')
        )->get()->getResultArray();
        return view('workorders/schedule', $this->viewData(['title'=>'Maintenance Schedule','upcoming'=>$upcoming,'facilities'=>$facilities,'currentPage'=>$currentPage,'perPage'=>$perPage]));
    }

    // ── Helpers ───────────────────────────────────────────────
    private function _saveFile(\CodeIgniter\HTTP\Files\UploadedFile $file, string $subDir='general'): ?string
    {
        if (!$file->isValid() || $file->hasMoved()) return null;
        if (!in_array($file->getMimeType(), self::ALLOWED_MIMES)) return null;
        if ($file->getSize() > self::MAX_FILE_SIZE) return null;
        $dir = WRITEPATH.'uploads/'.$subDir.'/';
        if (!is_dir($dir)) mkdir($dir,0755,true);
        $newName = bin2hex(random_bytes(16)).'.'.$file->getExtension();
        $file->move($dir,$newName);
        return 'uploads/'.$subDir.'/'.$newName;
    }

    private function _handleFileUpload(int $woId): void
    {
        $file = $this->request->getFile('attachment');
        if ($file && $file->isValid() && !$file->hasMoved()) {
            $path = $this->_saveFile($file,'workorders');
            if ($path) $this->db->table('wo_attachments')->insert(['wo_id'=>$woId,'file_name'=>esc($file->getClientName()),'file_path'=>$path,'uploaded_by'=>session()->get('user_id')]);
        }
    }

    private function _refreshCosting(int $woId): void
    {
        $laborRows    = $this->db->table('wo_labor')->where('wo_id',$woId)->get()->getResultArray();
        $materialRows = $this->db->table('wo_materials')->where('wo_id',$woId)->get()->getResultArray();
        $wo           = $this->db->table('work_orders')->select('estimated_cost')->where('id',$woId)->get()->getRowArray();
        $laborHours   = array_sum(array_column($laborRows,'hours_worked'));
        $laborCost    = array_sum(array_column($laborRows,'labor_cost'));
        $partsCost    = array_sum(array_column($materialRows,'total_cost'));
        $totalCost    = $laborCost + $partsCost;
        $estimate     = (float)($wo['estimated_cost'] ?? 0);
        $data = ['wo_id'=>$woId,'labor_hours'=>$laborHours,'labor_cost'=>$laborCost,'parts_cost'=>$partsCost,'total_cost'=>$totalCost,'cost_estimate'=>$estimate,'job_profit'=>$estimate-$totalCost,'created_by'=>session()->get('user_id')];
        $existing = $this->db->table('maintenance_costing')->where('wo_id',$woId)->get()->getRowArray();
        $existing ? $this->db->table('maintenance_costing')->where('wo_id',$woId)->update($data) : $this->db->table('maintenance_costing')->insert($data);
        $this->db->table('work_orders')->where('id',$woId)->update(['actual_cost'=>$totalCost]);
    }

    private function _notify(int $userId, string $title, string $message, string $type, int $refId): void
    {
        try { $this->db->table('notifications')->insert(['user_id'=>$userId,'title'=>$title,'message'=>$message,'type'=>$type,'reference_id'=>$refId]); } catch (\Throwable $e) {}
    }
}
