<?php
namespace App\Controllers;

class Facilities extends BaseController
{
    public function index()
    {
        $pg          = $this->paginate(20);
        $perPage     = $pg['perPage'];
        $offset      = $pg['offset'];
        $currentPage = $pg['page'];

        $companyId = $this->companyScope()->activeCompanyId();
        $companySql = $companyId ? ' AND f.company_id = ' . (int) $companyId : '';

        $facilities = $this->db->query("
            SELECT f.*,
                   u.name AS manager_name,
                   (SELECT COUNT(*) FROM assets a WHERE a.facility_id=f.id AND (a.deleted_at IS NULL OR a.deleted_at='0000-00-00 00:00:00')) AS asset_count,
                   (SELECT COUNT(*) FROM work_orders w WHERE w.facility_id=f.id AND w.status NOT IN ('completed','closed','cancelled')) AS open_wo,
                   (SELECT COUNT(*) FROM work_orders w WHERE w.facility_id=f.id AND w.sla_breached=1 AND w.status NOT IN ('completed','closed','cancelled')) AS sla_breaches,
                   (SELECT COUNT(*) FROM units un WHERE un.facility_id=f.id AND (un.deleted_at IS NULL OR un.deleted_at='0000-00-00 00:00:00')) AS total_units,
                   (SELECT COUNT(*) FROM units un WHERE un.facility_id=f.id AND un.status='occupied') AS occupied_units,
                   (SELECT COALESCE(SUM(i.total),0) FROM invoices i WHERE i.facility_id=f.id AND i.status='paid') AS total_revenue
            FROM facilities f
            LEFT JOIN users u ON u.id=f.manager_id
            WHERE f.status='active' $companySql
            ORDER BY f.name ASC
            LIMIT ? OFFSET ?
        ", [$perPage, $offset])->getResultArray();

        $totalCount = $this->scopeCompany(
            $this->db->table('facilities')->where('status', 'active')
        )->countAllResults();

        $kpi = [
            'total'           => $this->db->table('facilities')->where('status','active')->countAllResults(),
            'total_units'     => (int)($this->db->table('units')->selectSum('id','cnt')->get()->getRowArray()['cnt'] ?? 0),
            'total_assets'    => $this->db->table('assets')->where('status','active')->countAllResults(),
            'open_work_orders'=> $this->db->table('work_orders')->whereIn('status',['new','assigned','in_progress'])->countAllResults(),
        ];

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

    public function view(int $id)
    {
        $facility = $this->db->table('facilities f')
            ->select('f.*, u.name as manager_name')
            ->join('users u','u.id=f.manager_id','left')
            ->where('f.id',$id)->get()->getRowArray();

        if (!$facility) throw \CodeIgniter\Exceptions\PageNotFoundException::forPageNotFound();

        $assets = $this->db->table('assets')
            ->where('facility_id',$id)->orderBy('name','ASC')->get()->getResultArray();

        $contracts = $this->db->table('contracts')
            ->where('facility_id',$id)->orderBy('end_date','ASC')->get()->getResultArray();

        // Include unit_number in open WOs
        $openWO = $this->db->table('work_orders w')
            ->select('w.id, w.wo_number, w.title, w.status, w.priority, w.assigned_to, u.name as assigned_name, un.unit_number')
            ->join('users u','u.id=w.assigned_to','left')
            ->join('units un','un.id=w.unit_id','left')
            ->where('w.facility_id',$id)
            ->whereNotIn('w.status',['completed','closed','cancelled'])
            ->orderBy('CASE w.priority WHEN \'critical\' THEN 1 WHEN \'high\' THEN 2 WHEN \'medium\' THEN 3 ELSE 4 END', '')
            ->get()->getResultArray();

        // Unit KPIs for this facility
        $unitStats = $this->db->query("
            SELECT
              COUNT(*) AS total_units,
              SUM(CASE WHEN status='occupied' THEN 1 ELSE 0 END) AS occupied_units,
              SUM(CASE WHEN status='vacant' THEN 1 ELSE 0 END) AS vacant_units,
              SUM(CASE WHEN status='maintenance' THEN 1 ELSE 0 END) AS maintenance_units
            FROM units
            WHERE facility_id=? AND (deleted_at IS NULL OR deleted_at='0000-00-00 00:00:00')
        ", [$id])->getRowArray();

        $kpi = [
            'total_units'     => (int)($unitStats['total_units'] ?? 0),
            'occupied_units'  => (int)($unitStats['occupied_units'] ?? 0),
            'vacant_units'    => (int)($unitStats['vacant_units'] ?? 0),
            'maintenance_units'=> (int)($unitStats['maintenance_units'] ?? 0),
        ];

        $facilityUnits = $this->db->table('units')
            ->where('facility_id', $id)
            ->where('deleted_at', null)
            ->orderBy('unit_number', 'ASC')
            ->get()
            ->getResultArray();

        return view('facilities/view', $this->viewData([
            'title'         => $facility['name'],
            'facility'      => $facility,
            'assets'        => $assets,
            'contracts'     => $contracts,
            'openWO'        => $openWO,
            'kpi'           => $kpi,
            'facilityUnits' => $facilityUnits,
        ]));
    }

    public function create()
    {
        $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.*')->get()->getResultArray();
        return view('facilities/create', $this->viewData(['title'=>'Add Facility','managers'=>$managers]));
    }

    public function store()
    {
        $rules = [
            'name'    => 'required|min_length[2]|max_length[200]',
            'code'    => 'required|max_length[20]|is_unique[facilities.code]',
            'address' => 'required',
        ];
        if (!$this->validate($rules)) return redirect()->back()->withInput()->with('errors',$this->validator->getErrors());

        $this->db->table('facilities')->insert([
            'name'       => esc($this->request->getPost('name')),
            'code'       => strtoupper(esc($this->request->getPost('code'))),
            'address'    => esc($this->request->getPost('address')),
            'city'       => esc($this->request->getPost('city')??''),
            'country'    => esc($this->request->getPost('country')??''),
            'manager_id' => $this->request->getPost('manager_id') ?: null,
            'area_sqm'   => $this->request->getPost('area_sqm')   ?: null,
            'floors'     => $this->request->getPost('floors')      ?: null,
            'status'     => 'active',
        ]);
        $fid = $this->db->insertID();
        $this->logActivity('create','facilities',$fid);
        return redirect()->to(base_url('facilities/view/'.$fid))->with('success','Facility created.');
    }

    public function edit(int $id)
    {
        $facility = $this->db->table('facilities')->where('id',$id)->get()->getRowArray();
        if (!$facility) throw \CodeIgniter\Exceptions\PageNotFoundException::forPageNotFound();
        $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.*')->get()->getResultArray();
        return view('facilities/edit', $this->viewData(['title'=>'Edit '.$facility['name'],'facility'=>$facility,'managers'=>$managers]));
    }

    public function update(int $id)
    {
        $this->db->table('facilities')->where('id',$id)->update([
            'name'       => esc($this->request->getPost('name')),
            'address'    => esc($this->request->getPost('address')),
            'city'       => esc($this->request->getPost('city')??''),
            'country'    => esc($this->request->getPost('country')??''),
            'manager_id' => $this->request->getPost('manager_id') ?: null,
            'area_sqm'   => $this->request->getPost('area_sqm')   ?: null,
            'floors'     => $this->request->getPost('floors')      ?: null,
            'status'     => $this->request->getPost('status'),
        ]);
        $this->logActivity('update','facilities',$id);
        return redirect()->to(base_url('facilities/view/'.$id))->with('success','Facility updated.');
    }

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