<?php

class BorderCrossing extends BaseModel
{
    public const STATUSES=['not_started','documents_required','documents_submitted','broker_reviewing','ready_to_cross','driver_en_route','arrived_at_border','waiting_in_queue','customs_inspection','on_hold','cleared','crossed','rejected_returned','cancelled'];

    public function all(?string $search=null):array
    {
        $sql='SELECT border_crossings.*,loads.load_number,drivers.name driver_name,trucks.unit_number,trailers.trailer_number,customs_brokers.company_name customs_broker_name FROM border_crossings JOIN loads ON loads.id=border_crossings.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 customs_brokers ON customs_brokers.id=border_crossings.customs_broker_id';$p=[];
        if($search){$sql.=' WHERE border_crossings.crossing_number LIKE ? OR loads.load_number LIKE ? OR border_crossings.crossing_name LIKE ? OR drivers.name LIKE ?';$t="%{$search}%";$p=[$t,$t,$t,$t];}$sql.=' ORDER BY COALESCE(border_crossings.planned_at,border_crossings.created_at) DESC';$s=$this->db->prepare($sql);$s->execute($p);return$s->fetchAll();
    }
    public function find(int$id):?array
    {
        $s=$this->db->prepare('SELECT border_crossings.*,loads.load_number,loads.driver_id,loads.truck_id,loads.trailer_id,loads.pickup_location,loads.delivery_location,drivers.name driver_name,drivers.phone driver_phone,trucks.unit_number,trailers.trailer_number,customs_brokers.company_name customs_broker_name,customs_brokers.contact_name customs_contact,customs_brokers.email customs_email,customs_brokers.phone customs_phone FROM border_crossings JOIN loads ON loads.id=border_crossings.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 customs_brokers ON customs_brokers.id=border_crossings.customs_broker_id WHERE border_crossings.id=?');$s->execute([$id]);$r=$s->fetch();if(!$r)return null;$r['checklist']=$this->checklist($id);$r['issues']=$this->issues($id);return$r;
    }
    public function forDriver(int$driverId):array{$s=$this->db->prepare('SELECT border_crossings.*,loads.load_number FROM border_crossings JOIN loads ON loads.id=border_crossings.load_id WHERE loads.driver_id=? ORDER BY COALESCE(planned_at,created_at) DESC');$s->execute([$driverId]);return$s->fetchAll();}
    public function forLoad(int$loadId):array{$s=$this->db->prepare('SELECT * FROM border_crossings WHERE load_id=? ORDER BY id DESC');$s->execute([$loadId]);return$s->fetchAll();}
    public function create(array$d):int
    {
        $n=trim((string)($d['crossing_number']??''))?:next_reference('BC-','border_crossings','crossing_number');$s=$this->db->prepare('INSERT INTO border_crossings (crossing_number,load_id,customs_broker_id,crossing_name,port_code,origin_country,destination_country,direction,planned_at,status,reference_number,entry_number,seal_number,commodity,package_count,weight,latitude,longitude,geofence_radius_m,detention_rate,dispatcher_notes,driver_instructions,created_by) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)');$s->execute([$n,(int)$d['load_id'],$d['customs_broker_id']?:null,trim($d['crossing_name']),trim((string)($d['port_code']??''))?:null,trim($d['origin_country']),trim($d['destination_country']),trim((string)($d['direction']??''))?:null,$d['planned_at']?:null,$d['status']??'not_started',trim((string)($d['reference_number']??''))?:null,trim((string)($d['entry_number']??''))?:null,trim((string)($d['seal_number']??''))?:null,trim((string)($d['commodity']??''))?:null,$d['package_count']?:null,$d['weight']?:null,$d['latitude']?:null,$d['longitude']?:null,(int)($d['geofence_radius_m']??1500),(float)($d['detention_rate']??0),trim((string)($d['dispatcher_notes']??''))?:null,trim((string)($d['driver_instructions']??''))?:null,$_SESSION['user_id']??null]);$id=(int)$this->db->lastInsertId();$this->seedChecklist($id,$d['checklist_items']??[]);return$id;
    }
    public function update(int$id,array$d):void
    {
        $s=$this->db->prepare('UPDATE border_crossings SET load_id=?,customs_broker_id=?,crossing_name=?,port_code=?,origin_country=?,destination_country=?,direction=?,planned_at=?,status=?,reference_number=?,entry_number=?,seal_number=?,commodity=?,package_count=?,weight=?,latitude=?,longitude=?,geofence_radius_m=?,detention_rate=?,dispatcher_notes=?,driver_instructions=? WHERE id=?');$s->execute([(int)$d['load_id'],$d['customs_broker_id']?:null,trim($d['crossing_name']),trim((string)($d['port_code']??''))?:null,trim($d['origin_country']),trim($d['destination_country']),trim((string)($d['direction']??''))?:null,$d['planned_at']?:null,$d['status']??'not_started',trim((string)($d['reference_number']??''))?:null,trim((string)($d['entry_number']??''))?:null,trim((string)($d['seal_number']??''))?:null,trim((string)($d['commodity']??''))?:null,$d['package_count']?:null,$d['weight']?:null,$d['latitude']?:null,$d['longitude']?:null,(int)($d['geofence_radius_m']??1500),(float)($d['detention_rate']??0),trim((string)($d['dispatcher_notes']??''))?:null,trim((string)($d['driver_instructions']??''))?:null,$id]);
    }
    public function setStatus(int$id,string$status):void
    {
        if(!in_array($status,self::STATUSES,true))return;$fields=['arrived_at_border'=>'arrived_at','waiting_in_queue'=>'queue_started_at','customs_inspection'=>'inspection_started_at','cleared'=>'cleared_at','crossed'=>'crossed_at'];$sql='UPDATE border_crossings SET status=?';$p=[$status];if(isset($fields[$status]))$sql.=', '.$fields[$status].'=COALESCE('.$fields[$status].',NOW())';$sql.=' WHERE id=?';$p[]=$id;$this->db->prepare($sql)->execute($p);$this->recalculateDetention($id);
    }
    public function checklist(int$id):array{$s=$this->db->prepare('SELECT border_checklist_items.*,documents.file_name FROM border_checklist_items LEFT JOIN documents ON documents.id=border_checklist_items.document_id WHERE border_crossing_id=? ORDER BY sort_order,id');$s->execute([$id]);return$s->fetchAll();}
    public function updateChecklist(int$itemId,string$status,?int$documentId=null,?string$notes=null):void{$allowed=['missing','uploaded','approved','rejected','waived'];if(!in_array($status,$allowed,true))return;$s=$this->db->prepare('UPDATE border_checklist_items SET status=?,document_id=COALESCE(?,document_id),reviewed_by=?,reviewed_at=NOW(),notes=? WHERE id=?');$s->execute([$status,$documentId,$_SESSION['user_id']??null,$notes,$itemId]);}
    public function addIssue(int$id,array$d):int{$s=$this->db->prepare('INSERT INTO border_issues (border_crossing_id,issue_type,authority_name,reference_number,status,started_at,amount,description,reported_by) VALUES (?,?,?,?,?,?,?,?,?)');$s->execute([$id,trim($d['issue_type']),trim((string)($d['authority_name']??''))?:null,trim((string)($d['reference_number']??''))?:null,$d['status']??'open',$d['started_at']?:date('Y-m-d H:i:s'),(float)($d['amount']??0),trim((string)($d['description']??''))?:null,$_SESSION['user_id']??null]);return(int)$this->db->lastInsertId();}
    public function issues(int$id):array{$s=$this->db->prepare('SELECT border_issues.*,users.name reported_by_name FROM border_issues LEFT JOIN users ON users.id=border_issues.reported_by WHERE border_crossing_id=? ORDER BY id DESC');$s->execute([$id]);return$s->fetchAll();}
    public function customsBrokers():array{return$this->db->query("SELECT * FROM customs_brokers WHERE status='active' ORDER BY company_name")->fetchAll();}
    public function createCustomsBroker(array$d):int{$s=$this->db->prepare('INSERT INTO customs_brokers (company_name,contact_name,email,phone,license_number,address,notes,status) VALUES (?,?,?,?,?,?,?,?)');$s->execute([trim($d['company_name']),trim((string)($d['contact_name']??''))?:null,trim((string)($d['email']??''))?:null,trim((string)($d['phone']??''))?:null,trim((string)($d['license_number']??''))?:null,trim((string)($d['address']??''))?:null,trim((string)($d['notes']??''))?:null,'active']);return(int)$this->db->lastInsertId();}
    private function seedChecklist(int$id,array$items):void{$defaults=['Commercial Invoice','Packing List','Bill of Lading','Customs Broker Documents','Cargo Manifest','Certificate of Origin','Driver Identification','Truck Registration','Trailer Registration','Insurance','Seal Photograph'];$items=array_values(array_filter(array_map('trim',$items?:$defaults)));$s=$this->db->prepare('INSERT INTO border_checklist_items (border_crossing_id,item_name,is_required,status,sort_order) VALUES (?,?,1,\'missing\',?)');foreach($items as$i=>$item)$s->execute([$id,$item,$i+1]);}
    private function recalculateDetention(int$id):void{$s=$this->db->prepare('SELECT queue_started_at,crossed_at,cleared_at,detention_rate FROM border_crossings WHERE id=?');$s->execute([$id]);$r=$s->fetch();if(!$r||!$r['queue_started_at'])return;$end=$r['crossed_at']?:$r['cleared_at']?:date('Y-m-d H:i:s');$minutes=max(0,(int)floor((strtotime($end)-strtotime($r['queue_started_at']))/60));$billable=max(0,$minutes-120);$amount=round(($billable/60)*(float)$r['detention_rate'],2);$this->db->prepare('UPDATE border_crossings SET detention_minutes=?,detention_amount=? WHERE id=?')->execute([$minutes,$amount,$id]);}
}
