<?php
namespace App\Controllers;

class JobsController extends BaseController
{
    public function index()
    {
        if (!$this->can('view_jobs')) return redirect()->to('/dashboard')->with('error','Access denied');

        $db     = db_connect();
        $cid    = $this->companyId;
        $tab    = $this->request->getGet('tab') ?? 'active';
        $search = $this->request->getGet('search');
        $status = $this->request->getGet('status');
        $type   = $this->request->getGet('type');

        $b = $db->table('jobs j')
            ->select('j.*, c.first_name, c.last_name, c.company_name, js.name as status_name, js.color as status_color, jt.name as type_name, jt.color as type_color')
            ->join('customers c','c.id = j.customer_id')
            ->join('job_statuses js','js.id = j.status_id')
            ->join('job_types jt','jt.id = j.type_id','left')
            ->where('j.company_id',$cid);

        if ($tab === 'deleted') {
            $b->where('j.deleted_at IS NOT NULL',null,false);
        } else {
            $b->where('j.deleted_at IS NULL',null,false);
            if ($tab === 'completed') $b->where('js.is_complete',1);
            elseif ($tab === 'active') $b->where('js.is_complete',0)->where('js.is_cancelled',0);
        }

        if ($search) $b->groupStart()->like('j.title',$search)->orLike('j.number',$search)->groupEnd();
        if ($status) $b->where('j.status_id',$status);
        if ($type)   $b->where('j.type_id',$type);

        $jobs = $b->orderBy('j.created_at','DESC')->get()->getResultArray();

        $jobTypes    = $db->table('job_types')->where('company_id',$cid)->where('active',1)->get()->getResultArray();
        $jobStatuses = $db->table('job_statuses')->where('company_id',$cid)->where('active',1)->get()->getResultArray();

        $counts = [
            'active'    => $db->table('jobs j')->join('job_statuses js','js.id=j.status_id')->where('j.company_id',$cid)->where('j.deleted_at IS NULL',null,false)->where('js.is_complete',0)->where('js.is_cancelled',0)->countAllResults(),
            'completed' => $db->table('jobs j')->join('job_statuses js','js.id=j.status_id')->where('j.company_id',$cid)->where('j.deleted_at IS NULL',null,false)->where('js.is_complete',1)->countAllResults(),
            'deleted'   => $db->table('jobs')->where('company_id',$cid)->where('deleted_at IS NOT NULL',null,false)->countAllResults(),
        ];

        return $this->view('jobs/index', compact('jobs','counts','tab','search','status','type','jobTypes','jobStatuses'));
    }

    public function show(int $id)
    {
        if (!$this->can('view_jobs')) return redirect()->to('/dashboard')->with('error','Access denied');
        $job = $this->getJobOr404($id);
        $db  = db_connect();
        $cid = $this->companyId;

        $job['customer']   = $db->table('customers')->where('id',$job['customer_id'])->get()->getRowArray();
        $job['status']     = $db->table('job_statuses')->where('id',$job['status_id'])->get()->getRowArray();
        $job['type']       = $job['type_id'] ? $db->table('job_types')->where('id',$job['type_id'])->get()->getRowArray() : null;
        $job['assignees']  = $db->table('job_assignees ja')->select('ja.*, u.first_name, u.last_name, u.color, u.initials')->join('users u','u.id=ja.user_id')->where('ja.job_id',$id)->get()->getResultArray();
        $job['checklists'] = $db->table('job_checklists')->where('job_id',$id)->orderBy('sort_order')->get()->getResultArray();
        $job['timeEntries']= $db->table('time_entries te')->select('te.*, u.first_name, u.last_name')->join('users u','u.id=te.user_id')->where('te.company_id',$cid)->where('te.job_id',$id)->where('te.deleted_at IS NULL',null,false)->orderBy('te.date','DESC')->get()->getResultArray();
        $job['visits']     = $db->table('visits')->where('company_id',$cid)->where('job_id',$id)->orderBy('scheduled_start','ASC')->get()->getResultArray();
        $job['notes']      = $db->table('notes n')->select('n.*, u.first_name, u.last_name')->join('users u','u.id=n.created_by','left')->where('n.company_id',$cid)->where('n.entity_type','job')->where('n.entity_id',$id)->where('n.deleted_at IS NULL',null,false)->orderBy('n.created_at','DESC')->get()->getResultArray();
        $job['files']      = $db->table('files')->where('company_id',$cid)->where('entity_type','job')->where('entity_id',$id)->where('deleted_at IS NULL',null,false)->get()->getResultArray();
        $job['invoices']   = $db->table('invoices')->where('company_id',$cid)->where('job_id',$id)->where('deleted_at IS NULL',null,false)->get()->getResultArray();
        $job['credits']    = $db->table('job_credits')->where('company_id',$cid)->where('job_id',$id)->get()->getResultArray();
        $job['checkIns']   = $db->table('check_ins ci')->select('ci.*, u.first_name, u.last_name')->join('users u','u.id=ci.user_id')->where('ci.company_id',$cid)->where('ci.job_id',$id)->orderBy('ci.checked_in_at','DESC')->get()->getResultArray();
        $job['sourceQuote'] = $job['source_quote_id'] ? $db->table('quotes')->where('id',$job['source_quote_id'])->get()->getRowArray() : null;

        $users        = $db->table('users')->where('company_id',$cid)->where('status','active')->where('deleted_at IS NULL',null,false)->get()->getResultArray();
        $jobStatuses  = $db->table('job_statuses')->where('company_id',$cid)->where('active',1)->orderBy('sort_order')->get()->getResultArray();
        $totalMins    = array_sum(array_column($job['timeEntries'],'duration_mins'));
        $billableMins = array_sum(array_map(fn($t) => $t['billable'] ? $t['duration_mins'] : 0, $job['timeEntries']));
        $tab          = $this->request->getGet('tab') ?? 'overview';

        return $this->view('jobs/show', compact('job','users','jobStatuses','totalMins','billableMins','tab'));
    }

    public function create()
    {
        if (!$this->can('edit_jobs')) return redirect()->to('/jobs')->with('error','Access denied');
        $db  = db_connect();
        $cid = $this->companyId;

        $customers    = $db->table('customers')->where('company_id',$cid)->where('status','active')->where('deleted_at IS NULL',null,false)->orderBy('last_name')->get()->getResultArray();
        $jobTypes     = $db->table('job_types')->where('company_id',$cid)->where('active',1)->orderBy('sort_order')->get()->getResultArray();
        $jobStatuses  = $db->table('job_statuses')->where('company_id',$cid)->where('active',1)->orderBy('sort_order')->get()->getResultArray();
        $pricingTypes = $db->table('pricing_types')->where('company_id',$cid)->where('active',1)->orderBy('sort_order')->get()->getResultArray();
        $users        = $db->table('users')->where('company_id',$cid)->where('status','active')->where('deleted_at IS NULL',null,false)->orderBy('first_name')->get()->getResultArray();
        $templates    = $db->table('checklist_templates')->where('company_id',$cid)->get()->getResultArray();
        $quotes       = $db->table('quotes')->where('company_id',$cid)->whereIn('status',['sent','accepted'])->where('deleted_at IS NULL',null,false)->orderBy('created_at','DESC')->get()->getResultArray();

        $jobNext   = (int)(model('CompanySettingModel')->getSetting($cid,'numbering','job_next') ?? 1);
        $jobPrefix = model('CompanySettingModel')->getSetting($cid,'numbering','job_prefix') ?? 'JOB-';
        $nextNum   = $jobPrefix.str_pad($jobNext,config('Fieldly')->numberPadding,'0',STR_PAD_LEFT);
        $prefill   = $this->request->getGet('customer_id') ? ['customer_id' => (int)$this->request->getGet('customer_id')] : [];

        return $this->view('jobs/create', compact('customers','jobTypes','jobStatuses','pricingTypes','users','templates','nextNum','quotes','prefill'));
    }

    public function store()
    {
        if (!$this->can('edit_jobs')) return redirect()->to('/jobs')->with('error','Access denied');

        $post = $this->request->getPost();

        if (!$this->validate(['title'=>'required','customer_id'=>'required|integer','status_id'=>'required|integer'])) {
            return redirect()->back()->withInput()->with('errors',$this->validator->getErrors());
        }

        $db = db_connect();
        $db->table('jobs')->insert([
            'company_id'       => $this->companyId,
            'customer_id'      => (int)$post['customer_id'],
            'type_id'          => $post['type_id'] ?: null,
            'status_id'        => (int)$post['status_id'],
            'pricing_type_id'  => $post['pricing_type_id'] ?: null,
            'source_quote_id'  => $post['source_quote_id'] ?: null,
            'number'           => $this->nextNumber('job_prefix','job_next'),
            'title'            => trim($post['title']),
            'description'      => trim($post['description'] ?? ''),
            'internal_notes'   => trim($post['internal_notes'] ?? ''),
            'customer_notes'   => trim($post['customer_notes'] ?? ''),
            'priority'         => $post['priority'] ?? 'normal',
            'estimated_mins'   => $post['estimated_mins'] ?: null,
            'scheduled_date'   => $post['scheduled_date'] ?: null,
            'scheduled_time'   => $post['scheduled_time'] ?: null,
            'pricing_rate'     => $post['pricing_rate'] ?: null,
            'source'           => 'manual',
            'created_by'       => $this->userId,
            'created_at'       => date('Y-m-d H:i:s'),
            'updated_at'       => date('Y-m-d H:i:s'),
        ]);
        $id = $db->insertID();

        if (!empty($post['assigned_to'])) {
            foreach ((array)$post['assigned_to'] as $uid) {
                $db->table('job_assignees')->insert(['job_id'=>$id,'user_id'=>(int)$uid,'assigned_by'=>$this->userId,'assigned_at'=>date('Y-m-d H:i:s')]);
            }
        }

        if (!empty($post['checklist_template_id'])) {
            $items = $db->table('checklist_template_items')->where('template_id',(int)$post['checklist_template_id'])->orderBy('sort_order')->get()->getResultArray();
            foreach ($items as $i => $item) {
                $db->table('job_checklists')->insert(['job_id'=>$id,'company_id'=>$this->companyId,'template_id'=>(int)$post['checklist_template_id'],'text'=>$item['text'],'sort_order'=>$i]);
            }
        }

        $this->log('job.created','job',$id,"Job created: {$post['title']}");
        return redirect()->to("/jobs/{$id}")->with('success','Job created successfully.');
    }

    public function edit(int $id)
    {
        if (!$this->can('edit_jobs')) return redirect()->to('/jobs')->with('error','Access denied');
        $job = $this->getJobOr404($id);
        $db  = db_connect();
        $cid = $this->companyId;

        $customers    = $db->table('customers')->where('company_id',$cid)->where('status','active')->where('deleted_at IS NULL',null,false)->orderBy('last_name')->get()->getResultArray();
        $jobTypes     = $db->table('job_types')->where('company_id',$cid)->where('active',1)->orderBy('sort_order')->get()->getResultArray();
        $jobStatuses  = $db->table('job_statuses')->where('company_id',$cid)->where('active',1)->orderBy('sort_order')->get()->getResultArray();
        $pricingTypes = $db->table('pricing_types')->where('company_id',$cid)->where('active',1)->orderBy('sort_order')->get()->getResultArray();
        $users        = $db->table('users')->where('company_id',$cid)->where('status','active')->where('deleted_at IS NULL',null,false)->orderBy('first_name')->get()->getResultArray();
        $quotes       = $db->table('quotes')->where('company_id',$cid)->whereIn('status',['sent','accepted'])->where('deleted_at IS NULL',null,false)->orderBy('created_at','DESC')->get()->getResultArray();
        $templates    = $db->table('checklist_templates')->where('company_id',$cid)->get()->getResultArray();
        $job['assigned_to'] = array_column($db->table('job_assignees')->where('job_id',$id)->get()->getResultArray(),'user_id');

        return $this->view('jobs/edit', compact('job','customers','jobTypes','jobStatuses','pricingTypes','users','quotes','templates'));
    }

    public function update(int $id)
    {
        if (!$this->can('edit_jobs')) return redirect()->back()->with('error','Access denied');
        $job  = $this->getJobOr404($id);
        $post = $this->request->getPost();
        $db   = db_connect();

        $db->table('jobs')->where('id',$id)->update([
            'customer_id'     => (int)$post['customer_id'],
            'type_id'         => $post['type_id'] ?: null,
            'status_id'       => (int)$post['status_id'],
            'pricing_type_id' => $post['pricing_type_id'] ?: null,
            'title'           => trim($post['title']),
            'description'     => trim($post['description'] ?? ''),
            'internal_notes'  => trim($post['internal_notes'] ?? ''),
            'customer_notes'  => trim($post['customer_notes'] ?? ''),
            'priority'        => $post['priority'] ?? 'normal',
            'estimated_mins'  => $post['estimated_mins'] ?: null,
            'scheduled_date'  => $post['scheduled_date'] ?: null,
            'scheduled_time'  => $post['scheduled_time'] ?: null,
            'pricing_rate'    => $post['pricing_rate'] ?: null,
            'updated_at'      => date('Y-m-d H:i:s'),
        ]);

        $db->table('job_assignees')->where('job_id',$id)->delete();
        if (!empty($post['assigned_to'])) {
            foreach ((array)$post['assigned_to'] as $uid) {
                $db->table('job_assignees')->insert(['job_id'=>$id,'user_id'=>(int)$uid,'assigned_by'=>$this->userId,'assigned_at'=>date('Y-m-d H:i:s')]);
            }
        }

        $this->log('job.updated','job',$id,"Job {$job['number']} updated");
        return redirect()->to("/jobs/{$id}")->with('success','Job updated.');
    }

    public function delete(int $id)
    {
        if (!$this->can('edit_jobs')) return redirect()->back()->with('error','Access denied');
        $job = $this->getJobOr404($id);
        $db  = db_connect();
        $db->table('jobs')->where('id',$id)->update(['deleted_at' => date('Y-m-d H:i:s')]);
        $db->table('deleted_items')->insert([
            'company_id'     => $this->companyId,
            'entity_type'    => 'job',
            'entity_id'      => $id,
            'entity_ref'     => $job['number'] ?? null,
            'entity_summary' => "{$job['number']} – {$job['title']}",
            'snapshot_json'  => json_encode($job),
            'deleted_by'     => $this->userId,
        ]);
        $this->log('job.deleted','job',$id,"Job {$job['number']} deleted");
        return redirect()->to('/jobs')->with('success','Job moved to recycle bin.');
    }

    public function restore(int $id)
    {
        if (!$this->can('edit_jobs')) return redirect()->back()->with('error','Access denied');
        $db   = db_connect();
        $item = $db->table('deleted_items')->where('company_id',$this->companyId)->where('entity_type','job')->where('entity_id',$id)->where('purged_at IS NULL',null,false)->get()->getRowArray();
        if (!$item) return redirect()->back()->with('error','Not found in recycle bin.');
        $db->table('jobs')->where('id',$id)->update(['deleted_at' => null]);
        $db->table('deleted_items')->where('id',$item['id'])->update(['restored_at'=>date('Y-m-d H:i:s'),'restored_by'=>$this->userId]);
        return redirect()->to("/jobs/{$id}")->with('success','Job restored.');
    }

    public function complete(int $id)
    {
        if (!$this->can('complete_jobs')) return redirect()->back()->with('error','Access denied');
        $job = $this->getJobOr404($id);
        $db  = db_connect();
        $completeStatus = $db->table('job_statuses')->where('company_id',$this->companyId)->where('is_complete',1)->get()->getRowArray();
        $db->table('jobs')->where('id',$id)->update([
            'status_id'       => $completeStatus['id'] ?? $job['status_id'],
            'completed_at'    => date('Y-m-d H:i:s'),
            'completion_note' => trim($this->request->getPost('note') ?? ''),
            'updated_at'      => date('Y-m-d H:i:s'),
        ]);
        $this->log('job.completed','job',$id,"Job {$job['number']} completed");
        return redirect()->to("/jobs/{$id}")->with('success','Job marked as complete.');
    }

    public function duplicate(int $id)
    {
        if (!$this->can('edit_jobs')) return redirect()->back()->with('error','Access denied');
        $job = $this->getJobOr404($id);
        $db  = db_connect();
        $defaultStatus = $db->table('job_statuses')->where('company_id',$this->companyId)->where('is_default',1)->get()->getRowArray();
        $dup = $job;
        unset($dup['id'],$dup['deleted_at'],$dup['completed_at'],$dup['completion_note'],$dup['completion_sig_path']);
        $dup['number']     = $this->nextNumber('job_prefix','job_next');
        $dup['status_id']  = $defaultStatus['id'] ?? $job['status_id'];
        $dup['source']     = 'duplicate';
        $dup['created_by'] = $this->userId;
        $dup['created_at'] = date('Y-m-d H:i:s');
        $dup['updated_at'] = date('Y-m-d H:i:s');
        $db->table('jobs')->insert($dup);
        $newId = $db->insertID();
        foreach ($db->table('job_assignees')->where('job_id',$id)->get()->getResultArray() as $a) {
            $db->table('job_assignees')->insert(['job_id'=>$newId,'user_id'=>$a['user_id'],'assigned_by'=>$this->userId,'assigned_at'=>date('Y-m-d H:i:s')]);
        }
        $this->log('job.duplicated','job',$newId,"Job {$dup['number']} duplicated from {$job['number']}");
        return redirect()->to("/jobs/{$newId}")->with('success','Job duplicated.');
    }

    public function toggleChecklist(int $jobId, int $checkId)
    {
        $this->getJobOr404($jobId);
        $db   = db_connect();
        $item = $db->table('job_checklists')->where('id',$checkId)->where('job_id',$jobId)->get()->getRowArray();
        if (!$item) return redirect()->back();
        $done = !$item['completed'];
        $db->table('job_checklists')->where('id',$checkId)->update(['completed'=>$done?1:0,'completed_by'=>$done?$this->userId:null,'completed_at'=>$done?date('Y-m-d H:i:s'):null]);
        return redirect()->to("/jobs/{$jobId}?tab=checklist")->with('success','Checklist updated.');
    }

    public function addNote(int $id)
    {
        if (!$this->can('manage_notes')) return redirect()->back()->with('error','Access denied');
        $this->getJobOr404($id);
        $text = trim($this->request->getPost('text') ?? '');
        if (!$text) return redirect()->back()->with('error','Note cannot be empty.');
        db_connect()->table('notes')->insert(['company_id'=>$this->companyId,'entity_type'=>'job','entity_id'=>$id,'text'=>$text,'is_internal'=>1,'created_by'=>$this->userId,'created_at'=>date('Y-m-d H:i:s')]);
        return redirect()->to("/jobs/{$id}?tab=notes")->with('success','Note added.');
    }

    public function logTime(int $id)
    {
        if (!$this->can('log_time')) return redirect()->back()->with('error','Access denied');
        $this->getJobOr404($id);
        $post = $this->request->getPost();
        $user = db_connect()->table('users')->where('id',$this->userId)->get()->getRowArray();
        db_connect()->table('time_entries')->insert([
            'company_id'    => $this->companyId,
            'job_id'        => $id,
            'user_id'       => $this->userId,
            'date'          => $post['date'] ?? date('Y-m-d'),
            'duration_mins' => (int)$post['duration_mins'],
            'billable'      => isset($post['billable']) ? 1 : 0,
            'rate'          => $post['rate'] ?? ($user['hourly_rate'] ?? 0),
            'notes'         => trim($post['notes'] ?? ''),
            'created_at'    => date('Y-m-d H:i:s'),
        ]);
        return redirect()->to("/jobs/{$id}?tab=time")->with('success','Time logged.');
    }

    public function checkIn(int $id)
    {
        $this->getJobOr404($id);
        $db   = db_connect();
        $open = $db->table('check_ins')->where('company_id',$this->companyId)->where('user_id',$this->userId)->where('checked_out_at IS NULL',null,false)->get()->getRowArray();
        if ($open) return redirect()->back()->with('error','You are already checked in. Check out first.');
        $db->table('check_ins')->insert(['company_id'=>$this->companyId,'job_id'=>$id,'user_id'=>$this->userId,'checked_in_at'=>date('Y-m-d H:i:s'),'auto_time_entry'=>1]);
        return redirect()->to("/jobs/{$id}")->with('success','Checked in.');
    }

    public function checkOut(int $id)
    {
        $this->getJobOr404($id);
        $db   = db_connect();
        $ci   = $db->table('check_ins')->where('company_id',$this->companyId)->where('user_id',$this->userId)->where('job_id',$id)->where('checked_out_at IS NULL',null,false)->get()->getRowArray();
        if (!$ci) return redirect()->back()->with('error','No active check-in found.');
        $mins = max(1,(int)round((time()-strtotime($ci['checked_in_at']))/60));
        $db->table('check_ins')->where('id',$ci['id'])->update(['checked_out_at'=>date('Y-m-d H:i:s'),'duration_mins'=>$mins]);
        if ($ci['auto_time_entry']) {
            $user = $db->table('users')->where('id',$this->userId)->get()->getRowArray();
            $db->table('time_entries')->insert(['company_id'=>$this->companyId,'job_id'=>$id,'check_in_id'=>$ci['id'],'user_id'=>$this->userId,'date'=>date('Y-m-d',strtotime($ci['checked_in_at'])),'duration_mins'=>$mins,'billable'=>1,'rate'=>$user['hourly_rate']??0,'notes'=>'Auto-logged from check-in','created_at'=>date('Y-m-d H:i:s')]);
        }
        return redirect()->to("/jobs/{$id}")->with('success','Checked out. '.$mins.' minutes logged.');
    }

    public function addCredit(int $id)
    {
        if (!$this->can('record_payments')) return redirect()->back()->with('error','Access denied');
        $this->getJobOr404($id);
        $post = $this->request->getPost();
        $db   = db_connect();
        $db->table('job_credits')->insert(['company_id'=>$this->companyId,'job_id'=>$id,'amount'=>(float)$post['amount'],'method'=>$post['method'],'reference'=>trim($post['reference']??''),'notes'=>trim($post['notes']??''),'invoice_id'=>$post['invoice_id']?:null,'applied'=>$post['invoice_id']?1:0,'created_by'=>$this->userId,'created_at'=>date('Y-m-d H:i:s')]);
        $this->log('payment.recorded','job',$id,'Payment £'.number_format((float)$post['amount'],2).' recorded on job');
        return redirect()->to("/jobs/{$id}")->with('success','Payment recorded.');
    }

    public function uploadFile(int $id)
    {
        if (!$this->can('manage_files')) return redirect()->back()->with('error','Access denied');
        $this->getJobOr404($id);
        $file = $this->request->getFile('file');
        if (!$file || !$file->isValid()) return redirect()->back()->with('error','No valid file.');
        $name = $file->getRandomName();
        $path = WRITEPATH.'uploads/'.$this->companyId.'/';
        if (!is_dir($path)) mkdir($path,0755,true);
        $file->move($path,$name);
        db_connect()->table('files')->insert(['company_id'=>$this->companyId,'entity_type'=>'job','entity_id'=>$id,'filename'=>$name,'original_name'=>$file->getClientName(),'mime_type'=>$file->getClientMimeType(),'file_size'=>$file->getSize(),'disk'=>'local','disk_path'=>'uploads/'.$this->companyId.'/'.$name,'uploaded_by'=>$this->userId,'created_at'=>date('Y-m-d H:i:s')]);
        return redirect()->to("/jobs/{$id}?tab=files")->with('success','File uploaded.');
    }

    private function getJobOr404(int $id): array
    {
        $job = db_connect()->table('jobs')->where('id',$id)->where('company_id',$this->companyId)->get()->getRowArray();
        if (!$job) throw \CodeIgniter\Exceptions\PageNotFoundException::forPageNotFound();
        return $job;
    }
}