<?php

class Carrier extends BaseModel
{
    public function all(?string $search = null): array
    {
        $sql = "SELECT carriers.*,
            (SELECT COUNT(*) FROM drivers WHERE drivers.carrier_id = carriers.id) AS driver_count,
            (SELECT COUNT(*) FROM trucks WHERE trucks.carrier_id = carriers.id) AS truck_count,
            (SELECT COUNT(*) FROM trailers WHERE trailers.carrier_id = carriers.id) AS trailer_count,
            (SELECT COUNT(*) FROM loads WHERE loads.carrier_id = carriers.id AND loads.status NOT IN ('paid','cancelled')) AS active_load_count,
            (SELECT COALESCE(SUM(loads.dispatch_fee_amount),0) FROM loads WHERE loads.carrier_id = carriers.id AND loads.status <> 'cancelled') AS dispatch_revenue
            FROM carriers";
        $params = [];
        if ($search) {
            $sql .= ' WHERE carriers.company_name LIKE ? OR carriers.dba_name LIKE ? OR carriers.contact_name LIKE ? OR carriers.mc_number LIKE ? OR carriers.dot_number LIKE ? OR carriers.email LIKE ?';
            $term = "%{$search}%";
            $params = [$term, $term, $term, $term, $term, $term];
        }
        $sql .= ' ORDER BY carriers.company_name';
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    public function active(): array
    {
        return $this->db->query("SELECT id, company_name, dba_name, dispatch_fee_type, dispatch_fee_value, status, onboarding_status
            FROM carriers
            WHERE status IN ('active','on_hold')
            ORDER BY company_name")->fetchAll();
    }

    public function find(int $id): ?array
    {
        $stmt = $this->db->prepare("SELECT carriers.*,
            (SELECT COUNT(*) FROM drivers WHERE drivers.carrier_id = carriers.id) AS driver_count,
            (SELECT COUNT(*) FROM trucks WHERE trucks.carrier_id = carriers.id) AS truck_count,
            (SELECT COUNT(*) FROM trailers WHERE trailers.carrier_id = carriers.id) AS trailer_count
            FROM carriers WHERE carriers.id = ?");
        $stmt->execute([$id]);
        return $stmt->fetch() ?: null;
    }

    public function create(array $data): int
    {
        $stmt = $this->db->prepare('INSERT INTO carriers
            (company_name,dba_name,contact_name,email,phone,mc_number,dot_number,tax_id,address,dispatch_fee_type,dispatch_fee_value,payment_terms,authority_status,safety_rating,insurance_expiry,cargo_insurance_expiry,onboarding_status,status,notes)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)');
        $stmt->execute($this->payload($data));
        return (int) $this->db->lastInsertId();
    }

    public function update(int $id, array $data): void
    {
        $stmt = $this->db->prepare('UPDATE carriers SET
            company_name=?,dba_name=?,contact_name=?,email=?,phone=?,mc_number=?,dot_number=?,tax_id=?,address=?,dispatch_fee_type=?,dispatch_fee_value=?,payment_terms=?,authority_status=?,safety_rating=?,insurance_expiry=?,cargo_insurance_expiry=?,onboarding_status=?,status=?,notes=?
            WHERE id=?');
        $params = $this->payload($data);
        $params[] = $id;
        $stmt->execute($params);
    }

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

    public function loads(int $carrierId): array
    {
        $stmt = $this->db->prepare("SELECT loads.*, brokers.company_name AS broker_name, drivers.name AS driver_name,
            trucks.unit_number AS truck_number, trailers.trailer_number
            FROM loads
            LEFT JOIN brokers ON brokers.id=loads.broker_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.carrier_id=?
            ORDER BY FIELD(loads.status,'assigned','arrived_pickup','picked_up','in_transit','arrived_delivery','new','delivered','invoiced','paid','cancelled'), loads.pickup_datetime DESC");
        $stmt->execute([$carrierId]);
        return $stmt->fetchAll();
    }

    public function equipment(int $carrierId): array
    {
        $stmt = $this->db->prepare("SELECT id, unit_number AS identifier, 'Truck' AS kind, status, ownership_type, NULL AS equipment_type, NULL AS current_temperature, NULL AS temperature_unit FROM trucks WHERE carrier_id=?
            UNION ALL
            SELECT id, trailer_number AS identifier, 'Trailer' AS kind, status, ownership_type, type AS equipment_type, current_temperature, temperature_unit FROM trailers WHERE carrier_id=?
            ORDER BY kind, identifier");
        $stmt->execute([$carrierId, $carrierId]);
        return $stmt->fetchAll();
    }

    private function payload(array $data): array
    {
        $feeType = in_array(($data['dispatch_fee_type'] ?? ''), ['percentage','fixed_per_load','monthly'], true)
            ? $data['dispatch_fee_type'] : 'percentage';
        return [
            trim((string) ($data['company_name'] ?? '')),
            trim((string) ($data['dba_name'] ?? '')) ?: null,
            trim((string) ($data['contact_name'] ?? '')) ?: null,
            trim((string) ($data['email'] ?? '')) ?: null,
            trim((string) ($data['phone'] ?? '')) ?: null,
            trim((string) ($data['mc_number'] ?? '')) ?: null,
            trim((string) ($data['dot_number'] ?? '')) ?: null,
            trim((string) ($data['tax_id'] ?? '')) ?: null,
            trim((string) ($data['address'] ?? '')) ?: null,
            $feeType,
            (float) ($data['dispatch_fee_value'] ?? 0),
            trim((string) ($data['payment_terms'] ?? 'Weekly')) ?: 'Weekly',
            in_array(($data['authority_status'] ?? ''), ['active','pending','inactive','revoked'], true) ? $data['authority_status'] : 'active',
            trim((string) ($data['safety_rating'] ?? '')) ?: null,
            !empty($data['insurance_expiry']) ? $data['insurance_expiry'] : null,
            !empty($data['cargo_insurance_expiry']) ? $data['cargo_insurance_expiry'] : null,
            in_array(($data['onboarding_status'] ?? ''), ['lead','documents_pending','review','approved','on_hold','inactive'], true) ? $data['onboarding_status'] : 'documents_pending',
            in_array(($data['status'] ?? ''), ['active','on_hold','inactive'], true) ? $data['status'] : 'active',
            trim((string) ($data['notes'] ?? '')) ?: null,
        ];
    }
}
