<?php
namespace App\Controllers;

class InvoicesController extends BaseController
{
    public function index()
    {
        if (!$this->can('view_invoices')) return redirect()->to('/')->with('error','Access denied');
        $db  = db_connect();
        $cid = $this->companyId;
        $tab = $this->request->getGet('tab') ?? 'all';
        $search = $this->request->getGet('search');
        $status = $this->request->getGet('status');

        $b = $db->table('invoices i')
            ->select('i.*, c.first_name, c.last_name, c.company_name')
            ->join('customers c','c.id=i.customer_id')
            ->where('i.company_id',$cid);

        if ($tab === 'deleted') $b->where('i.deleted_at IS NOT NULL',null,false);
        else { $b->where('i.deleted_at IS NULL',null,false); if ($status) $b->where('i.status',$status); }
        if ($search) $b->groupStart()->like('i.number',$search)->orLike('i.title',$search)->orLike('c.first_name',$search)->orLike('c.last_name',$search)->groupEnd();

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

        $counts = [
            'all'      => $db->table('invoices')->where('company_id',$cid)->where('deleted_at IS NULL',null,false)->countAllResults(),
            'overdue'  => $db->table('invoices')->where('company_id',$cid)->where('deleted_at IS NULL',null,false)->where('status','overdue')->countAllResults(),
            'unpaid'   => $db->table('invoices')->where('company_id',$cid)->where('deleted_at IS NULL',null,false)->whereIn('status',['sent','part_paid','overdue'])->countAllResults(),
            'deleted'  => $db->table('invoices')->where('company_id',$cid)->where('deleted_at IS NOT NULL',null,false)->countAllResults(),
        ];

        return $this->view('invoices/index', compact('invoices','counts','tab','search','status'));
    }

    public function show(int $id)
    {
        if (!$this->can('view_invoices')) return redirect()->to('/')->with('error','Access denied');
        $inv = $this->getInvOr404($id);
        $db  = db_connect();
        $inv['customer']  = $db->table('customers')->where('id',$inv['customer_id'])->get()->getRowArray();
        $inv['items']     = $db->table('invoice_items')->where('invoice_id',$id)->orderBy('sort_order')->get()->getResultArray();
        $inv['payments']  = $db->table('payments')->where('invoice_id',$id)->where('deleted_at IS NULL',null,false)->orderBy('paid_at','DESC')->get()->getResultArray();
        $inv['job']       = $inv['job_id'] ? $db->table('jobs')->where('id',$inv['job_id'])->get()->getRowArray() : null;
        $inv['quote']     = $inv['quote_id'] ? $db->table('quotes')->where('id',$inv['quote_id'])->get()->getRowArray() : null;
        $taxRates         = $db->table('tax_rates')->where('company_id',$this->companyId)->where('active',1)->get()->getResultArray();
        $currency         = $this->settings['regional']['currency'] ?? 'GBP';
        return $this->view('invoices/show', compact('inv','taxRates','currency'));
    }

    public function create()
    {
        if (!$this->can('edit_invoices')) return redirect()->to('/fieldly/public/index.php/invoices')->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();
        $jobs      = $db->table('jobs j')->select('j.*, js.name as status_name')->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)->orderBy('j.created_at','DESC')->get()->getResultArray();
        $taxRates  = $db->table('tax_rates')->where('company_id',$cid)->where('active',1)->get()->getResultArray();
        $invNext   = (int)(model('CompanySettingModel')->getSetting($cid,'numbering','inv_next') ?? 1);
        $invPrefix = model('CompanySettingModel')->getSetting($cid,'numbering','inv_prefix') ?? 'INV-';
        $nextNum   = $invPrefix.str_pad($invNext,config('Fieldly')->numberPadding,'0',STR_PAD_LEFT);
        $currency  = $this->settings['regional']['currency'] ?? 'GBP';
        $prefill   = [];
        return $this->view('invoices/create', compact('customers','jobs','taxRates','nextNum','currency','prefill'));
    }

    public function fromJob(int $jobId)
    {
        if (!$this->can('edit_invoices')) return redirect()->to('/fieldly/public/index.php/invoices')->with('error','Access denied');
        $db  = db_connect();
        $cid = $this->companyId;

        $job = $db->table('jobs')->where('id',$jobId)->where('company_id',$cid)->get()->getRowArray();
        if (!$job) return redirect()->to('/fieldly/public/index.php/jobs')->with('error','Job not found.');

        // Duplicate guard
        $existing = $db->table('invoices')->where('company_id',$cid)->where('job_id',$jobId)->where('deleted_at IS NULL',null,false)->where('status !=','void')->get()->getRowArray();
        if ($existing) return redirect()->to("/invoices/{$existing['id']}")->with('error',"Invoice {$existing['number']} already exists for this job.");

        // Build prefill from quote, time entries or fixed rate
        $prefill = ['customer_id'=>$job['customer_id'],'job_id'=>$jobId,'title'=>$job['title'].' - Invoice'];
        $linkedQ = $job['source_quote_id'] ? $db->table('quotes')->where('id',$job['source_quote_id'])->get()->getRowArray() : null;

        if ($linkedQ) {
            $prefill['items']    = $db->table('quote_items')->where('quote_id',$linkedQ['id'])->orderBy('sort_order')->get()->getResultArray();
            $prefill['subtotal'] = $linkedQ['subtotal'];
            $prefill['tax_total']= $linkedQ['tax_total'];
            $prefill['total']    = $linkedQ['total'];
            $prefill['quote_id'] = $linkedQ['id'];
            $prefill['source']   = 'quote';
        } else {
            $timeEntries = $db->table('time_entries')->where('company_id',$cid)->where('job_id',$jobId)->where('billable',1)->where('deleted_at IS NULL',null,false)->get()->getResultArray();
            if ($timeEntries) {
                $prefill['items'] = array_map(fn($t) => ['description'=>$t['notes']?:('Labour: '.round($t['duration_mins']/60,2).' hrs'),'qty'=>round($t['duration_mins']/60,2),'unit_price'=>$t['rate'],'discount'=>0,'tax_rate'=>20,'line_total'=>round(($t['duration_mins']/60)*$t['rate'],2)], $timeEntries);
                $prefill['source'] = 'time';
            } elseif ($job['pricing_rate'] > 0) {
                $prefill['items'] = [['description'=>$job['title'],'qty'=>1,'unit_price'=>$job['pricing_rate'],'discount'=>0,'tax_rate'=>20,'line_total'=>$job['pricing_rate']]];
                $prefill['source'] = 'fixed';
            } else {
                $prefill['items'] = [['description'=>$job['title'],'qty'=>1,'unit_price'=>0,'discount'=>0,'tax_rate'=>20,'line_total'=>0]];
            }
        }

        // Apply job credits
        $credits = $db->table('job_credits')->where('company_id',$cid)->where('job_id',$jobId)->where('applied',0)->get()->getResultArray();
        $prefill['credits'] = $credits;
        $prefill['credit_total'] = array_sum(array_column($credits,'amount'));

        $customers = $db->table('customers')->where('company_id',$cid)->where('status','active')->where('deleted_at IS NULL',null,false)->orderBy('last_name')->get()->getResultArray();
        $jobs      = $db->table('jobs j')->select('j.*, js.name as status_name')->join('job_statuses js','js.id=j.status_id')->where('j.company_id',$cid)->where('j.deleted_at IS NULL',null,false)->orderBy('j.created_at','DESC')->get()->getResultArray();
        $taxRates  = $db->table('tax_rates')->where('company_id',$cid)->where('active',1)->get()->getResultArray();
        $invNext   = (int)(model('CompanySettingModel')->getSetting($cid,'numbering','inv_next') ?? 1);
        $invPrefix = model('CompanySettingModel')->getSetting($cid,'numbering','inv_prefix') ?? 'INV-';
        $nextNum   = $invPrefix.str_pad($invNext,config('Fieldly')->numberPadding,'0',STR_PAD_LEFT);
        $currency  = $this->settings['regional']['currency'] ?? 'GBP';

        return $this->view('invoices/create', compact('customers','jobs','taxRates','nextNum','currency','prefill'));
    }

    public function store()
    {
        if (!$this->can('edit_invoices')) return redirect()->to('/fieldly/public/index.php/invoices')->with('error','Access denied');
        $post = $this->request->getPost();

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

        // Duplicate guard
        if (!empty($post['job_id'])) {
            $existing = db_connect()->table('invoices')->where('company_id',$this->companyId)->where('job_id',(int)$post['job_id'])->where('deleted_at IS NULL',null,false)->where('status !=','void')->get()->getRowArray();
            if ($existing) return redirect()->back()->withInput()->with('error',"Invoice {$existing['number']} already exists for this job. Void it first.");
        }

        $items = $post['items'] ?? [];
        $sub = 0; $tax = 0;
        foreach ($items as &$item) {
            $lt = round((float)$item['qty'] * (float)$item['unit_price'] * (1 - (float)($item['discount']??0)/100), 2);
            $item['line_total'] = $lt;
            $sub += $lt;
            $tax += round($lt * (float)($item['tax_rate']??20) / 100, 2);
        }
        $total = round($sub+$tax, 2);
        $amountPaid = (float)($post['amount_paid'] ?? 0);

        $id = model('InvoiceModel')->insert([
            'company_id'  => $this->companyId,
            'customer_id' => (int)$post['customer_id'],
            'job_id'      => $post['job_id'] ?: null,
            'quote_id'    => $post['quote_id'] ?: null,
            'number'      => $this->nextNumber('inv_prefix','inv_next'),
            'title'       => trim($post['title'] ?? ''),
            'status'      => $amountPaid >= $total ? 'paid' : 'draft',
            'due_date'    => $post['due_date'] ?: null,
            'subtotal'    => round($sub,2),
            'tax_total'   => round($tax,2),
            'total'       => $total,
            'amount_paid' => $amountPaid,
            'balance_due' => max(0,$total-$amountPaid),
            'notes'       => trim($post['notes'] ?? ''),
            'payment_terms'=> trim($post['payment_terms'] ?? ''),
            'created_by'  => $this->userId,
        ]);

        $db = db_connect();
        foreach ($items as $i => $item) {
            $db->table('invoice_items')->insert(['invoice_id'=>$id,'description'=>$item['description']??'','qty'=>$item['qty']??1,'unit_price'=>$item['unit_price']??0,'discount'=>$item['discount']??0,'tax_rate'=>$item['tax_rate']??20,'line_total'=>$item['line_total']??0,'sort_order'=>$i]);
        }

        // Apply job credits
        if (!empty($post['apply_credits']) && !empty($post['job_id'])) {
            $credits = $db->table('job_credits')->where('company_id',$this->companyId)->where('job_id',(int)$post['job_id'])->where('applied',0)->get()->getResultArray();
            foreach ($credits as $credit) {
                $db->table('job_credits')->where('id',$credit['id'])->update(['applied'=>1,'invoice_id'=>$id]);
                $db->table('payments')->insert(['company_id'=>$this->companyId,'invoice_id'=>$id,'amount'=>$credit['amount'],'method'=>$credit['method'],'reference'=>'Job Credit: '.($credit['reference']??''),'paid_at'=>date('Y-m-d H:i:s'),'recorded_by'=>$this->userId,'notes'=>'Pre-payment applied from job']);
            }
        }

        $this->log('invoice.created','invoice',$id,"Invoice created");
        return redirect()->to("/invoices/{$id}")->with('success','Invoice created successfully.');
    }

    public function edit(int $id)
    {
        if (!$this->can('edit_invoices')) return redirect()->to('/fieldly/public/index.php/invoices')->with('error','Access denied');
        $inv = $this->getInvOr404($id);
        $db  = db_connect();
        $cid = $this->companyId;
        $inv['items']    = $db->table('invoice_items')->where('invoice_id',$id)->orderBy('sort_order')->get()->getResultArray();
        $customers = $db->table('customers')->where('company_id',$cid)->where('status','active')->where('deleted_at IS NULL',null,false)->orderBy('last_name')->get()->getResultArray();
        $jobs      = $db->table('jobs j')->select('j.*, js.name as status_name')->join('job_statuses js','js.id=j.status_id')->where('j.company_id',$cid)->where('j.deleted_at IS NULL',null,false)->orderBy('j.created_at','DESC')->get()->getResultArray();
        $taxRates  = $db->table('tax_rates')->where('company_id',$cid)->where('active',1)->get()->getResultArray();
        $currency  = $this->settings['regional']['currency'] ?? 'GBP';
        return $this->view('invoices/edit', compact('inv','customers','jobs','taxRates','currency'));
    }

    public function update(int $id)
    {
        if (!$this->can('edit_invoices')) return redirect()->back()->with('error','Access denied');
        $inv  = $this->getInvOr404($id);
        $post = $this->request->getPost();
        $items = $post['items'] ?? [];
        $db = db_connect();
        $db->table('invoice_items')->where('invoice_id',$id)->delete();
        $sub = 0; $tax = 0;
        foreach ($items as $i => $item) {
            $lt = round((float)$item['qty'] * (float)$item['unit_price'] * (1 - (float)($item['discount']??0)/100), 2);
            $item['line_total'] = $lt; $sub += $lt; $tax += round($lt*(float)($item['tax_rate']??20)/100,2);
            $db->table('invoice_items')->insert(['invoice_id'=>$id,'description'=>$item['description']??'','qty'=>$item['qty']??1,'unit_price'=>$item['unit_price']??0,'discount'=>$item['discount']??0,'tax_rate'=>$item['tax_rate']??20,'line_total'=>$lt,'sort_order'=>$i]);
        }
        $total = round($sub+$tax,2);
        model('InvoiceModel')->update($id,['customer_id'=>(int)$post['customer_id'],'title'=>trim($post['title']??''),'status'=>$post['status']??$inv['status'],'due_date'=>$post['due_date']?:null,'subtotal'=>round($sub,2),'tax_total'=>round($tax,2),'total'=>$total,'notes'=>trim($post['notes']??''),'payment_terms'=>trim($post['payment_terms']??'')]);
        $this->log('invoice.updated','invoice',$id,"Invoice {$inv['number']} updated");
        return redirect()->to("/invoices/{$id}")->with('success','Invoice updated.');
    }

    public function delete(int $id)
    {
        if (!$this->can('edit_invoices')) return redirect()->back()->with('error','Access denied');
        $inv = $this->getInvOr404($id);
        $this->softDelete('InvoiceModel',$id,'invoice',$inv['number']);
        $this->log('invoice.deleted','invoice',$id,"Invoice {$inv['number']} deleted");
        return redirect()->to('/fieldly/public/index.php/invoices')->with('success','Invoice moved to recycle bin.');
    }

    public function restore(int $id)
    {
        if (!$this->can('edit_invoices')) return redirect()->back()->with('error','Access denied');
        $item = model('DeletedItemModel')->where('company_id',$this->companyId)->where('entity_type','invoice')->where('entity_id',$id)->where('purged_at IS NULL',null,false)->first();
        if (!$item) return redirect()->back()->with('error','Not found in recycle bin.');
        $snap = json_decode($item['snapshot_json'],true); unset($snap['deleted_at']);
        model('InvoiceModel')->insert($snap);
        model('DeletedItemModel')->update($item['id'],['restored_at'=>date('Y-m-d H:i:s'),'restored_by'=>$this->userId]);
        return redirect()->to("/invoices/{$id}")->with('success','Invoice restored.');
    }

    public function send(int $id)
    {
        if (!$this->can('edit_invoices')) return redirect()->back()->with('error','Access denied');
        $inv = $this->getInvOr404($id);
        model('InvoiceModel')->update($id,['status'=>'sent','sent_at'=>date('Y-m-d H:i:s')]);
        $this->log('invoice.sent','invoice',$id,"Invoice {$inv['number']} marked as sent");
        return redirect()->to("/invoices/{$id}")->with('success','Invoice marked as sent.');
    }

    public function void(int $id)
    {
        if (!$this->can('edit_invoices')) return redirect()->back()->with('error','Access denied');
        $inv = $this->getInvOr404($id);
        model('InvoiceModel')->update($id,['status'=>'void','voided_at'=>date('Y-m-d H:i:s'),'voided_by'=>$this->userId]);
        $this->log('invoice.voided','invoice',$id,"Invoice {$inv['number']} voided");
        return redirect()->to("/invoices/{$id}")->with('success','Invoice voided.');
    }

    public function recordPayment(int $id)
    {
        if (!$this->can('record_payments')) return redirect()->back()->with('error','Access denied');
        $inv  = $this->getInvOr404($id);
        $post = $this->request->getPost();
        $amount = (float)$post['amount'];
        if ($amount <= 0) return redirect()->back()->with('error','Invalid amount.');

        db_connect()->table('payments')->insert(['company_id'=>$this->companyId,'invoice_id'=>$id,'amount'=>$amount,'method'=>$post['method'],'reference'=>trim($post['reference']??''),'paid_at'=>$post['paid_at']??date('Y-m-d H:i:s'),'recorded_by'=>$this->userId,'notes'=>trim($post['notes']??'')]);

        $totalPaid = (float)(db_connect()->table('payments')->selectSum('amount')->where('invoice_id',$id)->where('deleted_at IS NULL',null,false)->get()->getRow()->amount ?? 0);
        $newBal    = max(0,(float)$inv['total']-$totalPaid);
        $newStatus = $totalPaid >= (float)$inv['total'] ? 'paid' : 'part_paid';
        model('InvoiceModel')->update($id,['amount_paid'=>$totalPaid,'balance_due'=>$newBal,'status'=>$newStatus,'paid_at'=>$newStatus==='paid'?date('Y-m-d H:i:s'):null]);

        $this->log('payment.recorded','invoice',$id,'Payment £'.number_format($amount,2)." on {$inv['number']}");
        return redirect()->to("/invoices/{$id}")->with('success','Payment recorded.');
    }

    public function deletePayment(int $invId, int $payId)
    {
        if (!$this->can('edit_invoices')) return redirect()->back()->with('error','Access denied');
        $inv = $this->getInvOr404($invId);
        db_connect()->table('payments')->where('id',$payId)->update(['deleted_at'=>date('Y-m-d H:i:s')]);
        $totalPaid = (float)(db_connect()->table('payments')->selectSum('amount')->where('invoice_id',$invId)->where('deleted_at IS NULL',null,false)->get()->getRow()->amount ?? 0);
        model('InvoiceModel')->update($invId,['amount_paid'=>$totalPaid,'balance_due'=>max(0,(float)$inv['total']-$totalPaid),'status'=>$totalPaid<=0?'sent':'part_paid']);
        return redirect()->to("/invoices/{$invId}")->with('success','Payment deleted.');
    }

    public function pdf(int $id)
    {
        // TODO: integrate mPDF or Dompdf
        return redirect()->to("/invoices/{$id}")->with('error','PDF generation coming soon.');
    }

    private function getInvOr404(int $id): array
    {
        $inv = model('InvoiceModel')->find($id);
        if (!$inv || (int)$inv['company_id'] !== $this->companyId) throw \CodeIgniter\Exceptions\PageNotFoundException::forPageNotFound();
        return $inv;
    }
}
