<?php

class Invoice extends BaseModel
{
    public const STATUSES=['draft','approved','sent','viewed','partially_paid','paid','overdue','cancelled','written_off','void'];
    public function all(?string$search=null):array{$sql='SELECT invoices.*,loads.load_number,brokers.company_name broker_name,COALESCE((SELECT SUM(amount) FROM payments WHERE payments.invoice_id=invoices.id),0) paid_amount FROM invoices LEFT JOIN loads ON loads.id=invoices.load_id LEFT JOIN brokers ON brokers.id=invoices.broker_id';$p=[];if($search){$sql.=' WHERE invoices.invoice_number LIKE ? OR loads.load_number LIKE ? OR brokers.company_name LIKE ?';$t="%{$search}%";$p=[$t,$t,$t];}$sql.=' ORDER BY invoices.id DESC';$s=$this->db->prepare($sql);$s->execute($p);return$s->fetchAll();}
    public function find(int$id):?array{$s=$this->db->prepare('SELECT invoices.*,loads.load_number,loads.pickup_location,loads.delivery_location,loads.pickup_datetime,loads.delivery_datetime,loads.miles,loads.reference_number,loads.commodity,loads.weight,drivers.name driver_name,trucks.unit_number,trailers.trailer_number,brokers.company_name broker_name,brokers.contact_name,brokers.email broker_email,brokers.phone broker_phone,brokers.address broker_address FROM invoices LEFT JOIN loads ON loads.id=invoices.load_id LEFT JOIN drivers ON drivers.id=loads.driver_id LEFT JOIN trucks ON trucks.id=loads.truck_id LEFT JOIN trailers ON trailers.id=loads.trailer_id LEFT JOIN brokers ON brokers.id=invoices.broker_id WHERE invoices.id=?');$s->execute([$id]);$r=$s->fetch();if(!$r)return null;$r['items']=$this->items($id);$r['payments']=$this->payments($id);$r['paid_amount']=array_sum(array_map(fn($x)=>(float)$x['amount'],$r['payments']));$r['balance_due']=max(0,(float)$r['total']-$r['paid_amount']);return$r;}
    public function items(int$id):array{$s=$this->db->prepare('SELECT * FROM invoice_items WHERE invoice_id=? ORDER BY id');$s->execute([$id]);return$s->fetchAll();}
    public function payments(int$id):array{$s=$this->db->prepare('SELECT * FROM payments WHERE invoice_id=? ORDER BY payment_date DESC,id DESC');$s->execute([$id]);return$s->fetchAll();}
    public function create(array$d):int{$items=$this->normalizeItems($d);$subtotal=array_sum(array_column($items,'amount'));$tax=(float)($d['tax']??0);$discount=(float)($d['discount']??0);$deductions=(float)($d['deductions']??0);$total=max(0,$subtotal+$tax-$discount-$deductions);$n=trim((string)($d['invoice_number']??''))?:$this->nextNumber();$this->db->beginTransaction();try{$s=$this->db->prepare('INSERT INTO invoices (invoice_number,load_id,broker_id,subtotal,deductions,tax,discount,total,balance_due,status,invoice_date,due_date,payment_terms,billing_notes,notes) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)');$s->execute([$n,$d['load_id']?:null,$d['broker_id']?:null,$subtotal,$deductions,$tax,$discount,$total,$total,$d['status']??'draft',$d['invoice_date']?:date('Y-m-d'),$d['due_date']?:null,trim((string)($d['payment_terms']??''))?:null,trim((string)($d['billing_notes']??''))?:null,trim((string)($d['notes']??''))?:null]);$id=(int)$this->db->lastInsertId();$this->saveItems($id,$items);if(!empty($d['load_id']))$this->db->prepare("UPDATE loads SET status='invoiced' WHERE id=? AND status<>'paid'")->execute([(int)$d['load_id']]);$this->db->commit();return$id;}catch(Throwable$e){$this->db->rollBack();throw$e;}}
    public function update(int$id,array$d):void{$items=$this->normalizeItems($d);$subtotal=array_sum(array_column($items,'amount'));$tax=(float)($d['tax']??0);$discount=(float)($d['discount']??0);$deductions=(float)($d['deductions']??0);$total=max(0,$subtotal+$tax-$discount-$deductions);$paid=(float)$this->db->query('SELECT COALESCE(SUM(amount),0) FROM payments WHERE invoice_id='.(int)$id)->fetchColumn();$this->db->beginTransaction();try{$s=$this->db->prepare('UPDATE invoices SET invoice_number=?,load_id=?,broker_id=?,subtotal=?,deductions=?,tax=?,discount=?,total=?,balance_due=?,status=?,invoice_date=?,due_date=?,payment_terms=?,billing_notes=?,notes=? WHERE id=?');$s->execute([trim($d['invoice_number']),$d['load_id']?:null,$d['broker_id']?:null,$subtotal,$deductions,$tax,$discount,$total,max(0,$total-$paid),$d['status']??'draft',$d['invoice_date']?:date('Y-m-d'),$d['due_date']?:null,trim((string)($d['payment_terms']??''))?:null,trim((string)($d['billing_notes']??''))?:null,trim((string)($d['notes']??''))?:null,$id]);$this->db->prepare('DELETE FROM invoice_items WHERE invoice_id=?')->execute([$id]);$this->saveItems($id,$items);$this->db->commit();}catch(Throwable$e){$this->db->rollBack();throw$e;}}
    public function addPayment(int$id,array$d):void{$a=(float)($d['amount']??0);if($a<=0)return;$date=$d['payment_date']?:date('Y-m-d');$s=$this->db->prepare('INSERT INTO payments (invoice_id,amount,payment_date,method,reference,notes) VALUES (?,?,?,?,?,?)');$s->execute([$id,$a,$date,trim((string)($d['method']??''))?:null,trim((string)($d['reference']??''))?:null,trim((string)($d['notes']??''))?:null]);$inv=$this->find($id);if(!$inv)return;$balance=max(0,(float)$inv['total']-(float)$inv['paid_amount']);$status=$balance<=0?'paid':'partially_paid';$this->db->prepare('UPDATE invoices SET balance_due=?,status=?,paid_at=? WHERE id=?')->execute([$balance,$status,$status==='paid'?date('Y-m-d H:i:s'):null,$id]);if($status==='paid'&&!empty($inv['load_id']))$this->db->prepare("UPDATE loads SET status='paid' WHERE id=?")->execute([(int)$inv['load_id']]);}
    public function markStatus(int$id,string$status):void{if(!in_array($status,self::STATUSES,true))return;$extra='';if($status==='sent')$extra=',sent_at=NOW()';if($status==='viewed')$extra=',viewed_at=NOW()';$this->db->prepare("UPDATE invoices SET status=?,paid_at=? {$extra} WHERE id=?")->execute([$status,$status==='paid'?date('Y-m-d H:i:s'):null,$id]);}
    public function delete(int$id):void{$this->db->prepare('DELETE FROM invoices WHERE id=?')->execute([$id]);}
    public function nextNumber():string{return next_reference('INV-','invoices','invoice_number');}
    public function openLoads():array{return$this->db->query("SELECT loads.*,brokers.company_name broker_name FROM loads LEFT JOIN brokers ON brokers.id=loads.broker_id WHERE loads.status IN ('delivered','invoiced') ORDER BY loads.id DESC")->fetchAll();}
    public function overdue():array{return$this->db->query("SELECT invoices.*,brokers.company_name broker_name FROM invoices LEFT JOIN brokers ON brokers.id=invoices.broker_id WHERE invoices.status NOT IN ('paid','void','cancelled','written_off') AND invoices.due_date<CURDATE() ORDER BY invoices.due_date LIMIT 12")->fetchAll();}
    private function normalizeItems(array$d):array{$des=$d['item_description']??[];$am=$d['item_amount']??[];$items=[];foreach($des as$i=>$x){$x=trim((string)$x);$a=(float)($am[$i]??0);if($x===''&&$a<=0)continue;$items[]=['description'=>$x?:'Service','amount'=>$a];}if(!$items)$items[]=['description'=>'Freight charge','amount'=>(float)($d['default_amount']??0)];return$items;}
    private function saveItems(int$id,array$items):void{$s=$this->db->prepare('INSERT INTO invoice_items (invoice_id,description,amount) VALUES (?,?,?)');foreach($items as$x)$s->execute([$id,$x['description'],$x['amount']]);}
}
