<?php

class Load extends BaseModel
{
    public const STATUSES = ['new','assigned','arrived_pickup','picked_up','in_transit','arrived_delivery','delivered','invoiced','paid','cancelled'];

    public function all(?string $search=null): array
    {
        $sql='SELECT loads.*,brokers.company_name broker_name,carriers.company_name carrier_name,drivers.name driver_name,
            trucks.unit_number truck_number,trailers.trailer_number,trailers.type trailer_type,trailers.reefer_capable,
            trailers.current_temperature trailer_current_temperature,trailers.temperature_unit trailer_temperature_unit
            FROM loads
            LEFT JOIN brokers ON brokers.id=loads.broker_id
            LEFT JOIN carriers ON carriers.id=loads.carrier_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';
        $params=[];
        if($search){
            $sql.=' WHERE loads.load_number LIKE ? OR loads.reference_number LIKE ? OR brokers.company_name LIKE ? OR carriers.company_name LIKE ? OR drivers.name LIKE ? OR trucks.unit_number LIKE ? OR trailers.trailer_number LIKE ? OR loads.pickup_location LIKE ? OR loads.delivery_location LIKE ? OR loads.last_location_address LIKE ?';
            $term="%{$search}%";$params=array_fill(0,10,$term);
        }
        $sql.=' ORDER BY loads.id DESC';$stmt=$this->db->prepare($sql);$stmt->execute($params);return $stmt->fetchAll();
    }

    public function forDriver(int $driverId): array
    {
        $stmt=$this->db->prepare('SELECT loads.*,brokers.company_name broker_name,carriers.company_name carrier_name,trucks.unit_number truck_number,
            trailers.trailer_number,trailers.type trailer_type,trailers.reefer_capable,trailers.current_temperature trailer_current_temperature
            FROM loads
            LEFT JOIN brokers ON brokers.id=loads.broker_id
            LEFT JOIN carriers ON carriers.id=loads.carrier_id
            LEFT JOIN trucks ON trucks.id=loads.truck_id
            LEFT JOIN trailers ON trailers.id=loads.trailer_id
            WHERE loads.driver_id=?
            ORDER BY FIELD(loads.status,"assigned","arrived_pickup","picked_up","in_transit","arrived_delivery","delivered","invoiced","paid","cancelled","new"),loads.pickup_datetime ASC');
        $stmt->execute([$driverId]);return $stmt->fetchAll();
    }

    public function find(int $id): ?array
    {
        $stmt=$this->db->prepare('SELECT loads.*,brokers.company_name broker_name,carriers.company_name carrier_name,
            carriers.dispatch_fee_type carrier_default_fee_type,carriers.dispatch_fee_value carrier_default_fee_value,
            drivers.name driver_name,trucks.unit_number truck_number,trailers.trailer_number,trailers.type trailer_type,
            trailers.reefer_capable,trailers.current_temperature trailer_current_temperature,trailers.current_setpoint trailer_current_setpoint,
            trailers.temperature_unit trailer_temperature_unit,trailers.reefer_mode trailer_reefer_mode,trailers.reefer_fuel_level trailer_reefer_fuel_level
            FROM loads
            LEFT JOIN brokers ON brokers.id=loads.broker_id
            LEFT JOIN carriers ON carriers.id=loads.carrier_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
            WHERE loads.id=?');
        $stmt->execute([$id]);$load=$stmt->fetch();if(!$load)return null;$load['stops']=$this->stops($id);return $load;
    }

    public function create(array $data): int
    {
        $financial=$this->resolveFinancials($data);
        $this->db->beginTransaction();
        try{
            $stmt=$this->db->prepare('INSERT INTO loads
              (load_number,reference_number,broker_id,carrier_id,driver_id,truck_id,trailer_id,pickup_location,delivery_location,pickup_datetime,delivery_datetime,
               rate,carrier_rate,dispatch_fee_type,dispatch_fee_value,dispatch_fee_amount,carrier_net_amount,equipment_source,payment_responsibility,carrier_reference,carrier_confirmation_status,
               miles,commodity,weight,temperature_controlled,temperature_unit,temperature_setpoint,temperature_min,temperature_max,reefer_mode,precool_required,precool_temperature,
               pulp_temperature_required,washout_required,seal_required,temperature_instructions,status,notes,created_by)
               VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)');
            $stmt->execute($this->payload($data,$financial));
            $id=(int)$this->db->lastInsertId();$this->saveStops($id,$data);$this->syncAssignments($data);$this->addEvent($id,'load_created','Load created for '.($data['carrier_id']?'carrier assignment':'unassigned carrier'));$this->db->commit();return $id;
        }catch(Throwable $e){$this->db->rollBack();throw $e;}
    }

    public function update(int $id,array $data): void
    {
        $old=$this->find($id);$financial=$this->resolveFinancials($data);
        $this->db->beginTransaction();
        try{
            $stmt=$this->db->prepare('UPDATE loads SET
              load_number=?,reference_number=?,broker_id=?,carrier_id=?,driver_id=?,truck_id=?,trailer_id=?,pickup_location=?,delivery_location=?,pickup_datetime=?,delivery_datetime=?,
              rate=?,carrier_rate=?,dispatch_fee_type=?,dispatch_fee_value=?,dispatch_fee_amount=?,carrier_net_amount=?,equipment_source=?,payment_responsibility=?,carrier_reference=?,carrier_confirmation_status=?,
              miles=?,commodity=?,weight=?,temperature_controlled=?,temperature_unit=?,temperature_setpoint=?,temperature_min=?,temperature_max=?,reefer_mode=?,precool_required=?,precool_temperature=?,
              pulp_temperature_required=?,washout_required=?,seal_required=?,temperature_instructions=?,status=?,notes=? WHERE id=?');
            $params=$this->payload($data,$financial);array_pop($params);$params[]=$id;$stmt->execute($params);
            $this->db->prepare('DELETE FROM load_stops WHERE load_id=?')->execute([$id]);$this->saveStops($id,$data);$this->releaseOldAssignments($old,$data);$this->syncAssignments($data);$this->addEvent($id,'load_updated','Load, carrier, equipment, and requirements updated');$this->db->commit();
        }catch(Throwable $e){$this->db->rollBack();throw $e;}
    }

    public function updateStatus(int $id,string $status,?float $latitude=null,?float $longitude=null,?string $locationAddress=null): void
    {
        if(!in_array($status,self::STATUSES,true))return;
        $this->db->prepare('UPDATE loads SET status=? WHERE id=?')->execute([$status,$id]);
        $this->addEvent($id,'status_'.$status,'Status changed to '.readable_status($status),$latitude,$longitude,$locationAddress);
        if(in_array($status,['delivered','invoiced','paid','cancelled'],true)){
            $load=$this->find($id);if($load){
                if($load['driver_id'])$this->db->prepare("UPDATE drivers SET status='available' WHERE id=?")->execute([(int)$load['driver_id']]);
                if($load['truck_id'])$this->db->prepare("UPDATE trucks SET status='available' WHERE id=?")->execute([(int)$load['truck_id']]);
                if($load['trailer_id'])$this->db->prepare("UPDATE trailers SET status='available' WHERE id=?")->execute([(int)$load['trailer_id']]);
            }
        }
    }

    public function updateLocation(int $loadId,int $driverId,float $latitude,float $longitude,?float $accuracy=null,?string $address=null,?string $status=null,?string $notes=null,?float $speed=null,?float $heading=null,?float $batteryLevel=null): void
    {
        $this->db->beginTransaction();
        try{
            $stmt=$this->db->prepare('INSERT INTO driver_locations (driver_id,user_id,load_id,status,latitude,longitude,accuracy,speed,heading,battery_level,location_address,notes) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)');
            $stmt->execute([$driverId,$_SESSION['user_id']??null,$loadId,$status?:null,$latitude,$longitude,$accuracy,$speed,$heading,$batteryLevel,$address?:null,$notes?:null]);
            $this->db->prepare('UPDATE loads SET current_latitude=?,current_longitude=?,last_location_address=?,last_location_at=NOW() WHERE id=?')->execute([$latitude,$longitude,$address?:null,$loadId]);
            $this->addEvent($loadId,'location_updated','Driver location updated',$latitude,$longitude,$address);$this->db->commit();
        }catch(Throwable $e){$this->db->rollBack();throw $e;}
    }

    public function latestLocations(int $loadId): array
    {
        $stmt=$this->db->prepare('SELECT driver_locations.*,drivers.name driver_name FROM driver_locations LEFT JOIN drivers ON drivers.id=driver_locations.driver_id WHERE driver_locations.load_id=? ORDER BY driver_locations.id DESC LIMIT 20');$stmt->execute([$loadId]);return $stmt->fetchAll();
    }

    public function completeStop(int $stopId,int $loadId,?float $latitude=null,?float $longitude=null): void
    {
        $this->db->prepare('UPDATE load_stops SET completed_at=NOW(),completed_by=?,latitude=COALESCE(?,latitude),longitude=COALESCE(?,longitude) WHERE id=? AND load_id=?')->execute([$_SESSION['user_id']??null,$latitude,$longitude,$stopId,$loadId]);
        $this->addEvent($loadId,'stop_completed','Stop marked completed',$latitude,$longitude);
    }

    public function addEvent(int $loadId,string $eventType,?string $description=null,?float $latitude=null,?float $longitude=null,?string $locationAddress=null): void
    {
        try{$this->db->prepare('INSERT INTO load_events (load_id,user_id,event_type,description,latitude,longitude,location_address) VALUES (?,?,?,?,?,?,?)')->execute([$loadId,$_SESSION['user_id']??null,$eventType,$description,$latitude,$longitude,$locationAddress]);}catch(Throwable $e){}
    }

    public function events(int $loadId): array
    {
        $stmt=$this->db->prepare('SELECT load_events.*,users.name user_name FROM load_events LEFT JOIN users ON users.id=load_events.user_id WHERE load_events.load_id=? ORDER BY load_events.id DESC LIMIT 60');$stmt->execute([$loadId]);return $stmt->fetchAll();
    }

    public function delete(int $id): void {$this->db->prepare('DELETE FROM loads WHERE id=?')->execute([$id]);}

    public function board(): array
    {
        $board=[];foreach(self::STATUSES as $status)$board[$status]=[];foreach($this->all() as $load)$board[$load['status']][]=$load;return $board;
    }

    public function stops(int $loadId): array
    {
        $stmt=$this->db->prepare('SELECT load_stops.*,users.name completed_by_name FROM load_stops LEFT JOIN users ON users.id=load_stops.completed_by WHERE load_id=? ORDER BY sequence,id');$stmt->execute([$loadId]);return $stmt->fetchAll();
    }

    public function stopBelongsToLoad(int $stopId,int $loadId): bool
    {
        $stmt=$this->db->prepare('SELECT COUNT(*) FROM load_stops WHERE id=? AND load_id=?');$stmt->execute([$stopId,$loadId]);return (int)$stmt->fetchColumn()>0;
    }

    public function todaySchedule(): array
    {
        return $this->db->query("SELECT loads.*,carriers.company_name carrier_name FROM loads LEFT JOIN carriers ON carriers.id=loads.carrier_id WHERE DATE(pickup_datetime)=CURDATE() OR DATE(delivery_datetime)=CURDATE() ORDER BY COALESCE(pickup_datetime,delivery_datetime) LIMIT 12")->fetchAll();
    }

    private function payload(array $data,array $financial): array
    {
        return [
            trim((string)($data['load_number']??'')),trim((string)($data['reference_number']??''))?:null,
            !empty($data['broker_id'])?(int)$data['broker_id']:null,!empty($data['carrier_id'])?(int)$data['carrier_id']:null,
            !empty($data['driver_id'])?(int)$data['driver_id']:null,!empty($data['truck_id'])?(int)$data['truck_id']:null,!empty($data['trailer_id'])?(int)$data['trailer_id']:null,
            trim((string)($data['pickup_location']??'')),trim((string)($data['delivery_location']??'')),
            !empty($data['pickup_datetime'])?str_replace('T',' ',$data['pickup_datetime']):null,!empty($data['delivery_datetime'])?str_replace('T',' ',$data['delivery_datetime']):null,
            $financial['rate'],$financial['carrier_rate'],$financial['dispatch_fee_type'],$financial['dispatch_fee_value'],$financial['dispatch_fee_amount'],$financial['carrier_net_amount'],
            in_array(($data['equipment_source']??''),['external_carrier','company_equipment','rental','power_only'],true)?$data['equipment_source']:'external_carrier',
            in_array(($data['payment_responsibility']??''),['broker_pays_carrier','dispatch_collects','carrier_collects'],true)?$data['payment_responsibility']:'broker_pays_carrier',
            trim((string)($data['carrier_reference']??''))?:null,
            in_array(($data['carrier_confirmation_status']??''),['not_sent','sent','accepted','rejected'],true)?$data['carrier_confirmation_status']:'not_sent',
            (float)($data['miles']??0),trim((string)($data['commodity']??''))?:null,($data['weight']??'')!==''?(float)$data['weight']:null,
            !empty($data['temperature_controlled'])?1:0,in_array(($data['temperature_unit']??''),['F','C'],true)?$data['temperature_unit']:'F',
            ($data['temperature_setpoint']??'')!==''?(float)$data['temperature_setpoint']:null,($data['temperature_min']??'')!==''?(float)$data['temperature_min']:null,
            ($data['temperature_max']??'')!==''?(float)$data['temperature_max']:null,in_array(($data['reefer_mode']??''),['continuous','cycle_sentry'],true)?$data['reefer_mode']:null,
            !empty($data['precool_required'])?1:0,($data['precool_temperature']??'')!==''?(float)$data['precool_temperature']:null,!empty($data['pulp_temperature_required'])?1:0,
            !empty($data['washout_required'])?1:0,!empty($data['seal_required'])?1:0,trim((string)($data['temperature_instructions']??''))?:null,
            in_array(($data['status']??''),self::STATUSES,true)?$data['status']:'new',trim((string)($data['notes']??''))?:null,$_SESSION['user_id']??null,
        ];
    }

    private function resolveFinancials(array $data): array
    {
        $rate=(float)($data['rate']??0);$carrierRate=($data['carrier_rate']??'')!==''?(float)$data['carrier_rate']:$rate;
        $type=in_array(($data['dispatch_fee_type']??''),['percentage','fixed'],true)?$data['dispatch_fee_type']:null;
        $value=($data['dispatch_fee_value']??'')!==''?(float)$data['dispatch_fee_value']:null;
        if((!$type || $value===null) && !empty($data['carrier_id'])){
            $stmt=$this->db->prepare('SELECT dispatch_fee_type,dispatch_fee_value FROM carriers WHERE id=?');$stmt->execute([(int)$data['carrier_id']]);$carrier=$stmt->fetch();
            if($carrier){
                if(!$type)$type=$carrier['dispatch_fee_type']==='percentage'?'percentage':'fixed';
                if($value===null)$value=$carrier['dispatch_fee_type']==='monthly'?0:(float)$carrier['dispatch_fee_value'];
            }
        }
        $type=$type?:'percentage';$value=$value??0;
        $fee=$type==='percentage'?round($carrierRate*$value/100,2):round($value,2);
        return ['rate'=>$rate,'carrier_rate'=>$carrierRate,'dispatch_fee_type'=>$type,'dispatch_fee_value'=>$value,'dispatch_fee_amount'=>$fee,'carrier_net_amount'=>max(0,round($carrierRate-$fee,2))];
    }

    private function saveStops(int $loadId,array $data): void
    {
        $types=$data['stop_type']??[];$locations=$data['stop_location']??[];$datetimes=$data['stop_datetime']??[];$contacts=$data['stop_contact']??[];$notes=$data['stop_notes']??[];
        $latitudes=$data['stop_latitude']??[];$longitudes=$data['stop_longitude']??[];$temps=$data['stop_temperature']??[];$tempUnits=$data['stop_temperature_unit']??[];$seals=$data['stop_seal_number']??[];
        $stmt=$this->db->prepare('INSERT INTO load_stops (load_id,stop_type,sequence,location,scheduled_at,contact_name,latitude,longitude,temperature_reading,temperature_unit,seal_number,notes) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)');
        $sequence=1;foreach($locations as $i=>$location){$location=trim((string)$location);if($location==='')continue;$type=in_array(($types[$i]??'pickup'),['pickup','delivery'],true)?$types[$i]:'pickup';
            $stmt->execute([$loadId,$type,$sequence++,$location,!empty($datetimes[$i])?str_replace('T',' ',$datetimes[$i]):null,trim((string)($contacts[$i]??''))?:null,
            ($latitudes[$i]??'')!==''?$latitudes[$i]:null,($longitudes[$i]??'')!==''?$longitudes[$i]:null,($temps[$i]??'')!==''?$temps[$i]:null,
            in_array(($tempUnits[$i]??''),['F','C'],true)?$tempUnits[$i]:null,trim((string)($seals[$i]??''))?:null,trim((string)($notes[$i]??''))?:null]);
        }
    }

    private function syncAssignments(array $data): void
    {
        $active=!in_array(($data['status']??'new'),['new','delivered','invoiced','paid','cancelled'],true);
        if($active&&!empty($data['driver_id']))$this->db->prepare("UPDATE drivers SET status='on_load' WHERE id=?")->execute([(int)$data['driver_id']]);
        if($active&&!empty($data['truck_id']))$this->db->prepare("UPDATE trucks SET status='on_load' WHERE id=?")->execute([(int)$data['truck_id']]);
        if($active&&!empty($data['trailer_id']))$this->db->prepare("UPDATE trailers SET status='assigned' WHERE id=?")->execute([(int)$data['trailer_id']]);
    }

    private function releaseOldAssignments(?array $old,array $data): void
    {
        if(!$old)return;
        if($old['driver_id'] && (string)$old['driver_id']!==(string)($data['driver_id']??''))$this->db->prepare("UPDATE drivers SET status='available' WHERE id=?")->execute([(int)$old['driver_id']]);
        if($old['truck_id'] && (string)$old['truck_id']!==(string)($data['truck_id']??''))$this->db->prepare("UPDATE trucks SET status='available' WHERE id=?")->execute([(int)$old['truck_id']]);
        if($old['trailer_id'] && (string)$old['trailer_id']!==(string)($data['trailer_id']??''))$this->db->prepare("UPDATE trailers SET status='available' WHERE id=?")->execute([(int)$old['trailer_id']]);
    }
}
