<?php

class TemperatureLog extends BaseModel
{
    public function forLoad(int $loadId): array
    {
        $stmt = $this->db->prepare("SELECT temperature_logs.*, drivers.name AS driver_name, load_stops.location AS stop_location, load_stops.sequence AS stop_sequence, documents.file_name AS photo_name
            FROM temperature_logs
            LEFT JOIN drivers ON drivers.id=temperature_logs.driver_id
            LEFT JOIN load_stops ON load_stops.id=temperature_logs.stop_id
            LEFT JOIN documents ON documents.id=temperature_logs.document_id
            WHERE temperature_logs.load_id=?
            ORDER BY temperature_logs.recorded_at DESC, temperature_logs.id DESC
            LIMIT 100");
        $stmt->execute([$loadId]);
        return $stmt->fetchAll();
    }

    public function create(array $data): int
    {
        $stmt = $this->db->prepare('INSERT INTO temperature_logs
            (load_id,trailer_id,driver_id,stop_id,document_id,temperature,temperature_unit,setpoint,reefer_mode,reefer_fuel_level,pulp_temperature,latitude,longitude,location_address,notes,recorded_at,created_by)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)');
        $stmt->execute([
            (int) $data['load_id'],
            !empty($data['trailer_id']) ? (int) $data['trailer_id'] : null,
            !empty($data['driver_id']) ? (int) $data['driver_id'] : null,
            !empty($data['stop_id']) ? (int) $data['stop_id'] : null,
            !empty($data['document_id']) ? (int) $data['document_id'] : null,
            (float) $data['temperature'],
            in_array(($data['temperature_unit'] ?? ''), ['F','C'], true) ? $data['temperature_unit'] : 'F',
            ($data['setpoint'] ?? '') !== '' ? (float) $data['setpoint'] : null,
            in_array(($data['reefer_mode'] ?? ''), ['continuous','cycle_sentry','off'], true) ? $data['reefer_mode'] : null,
            ($data['reefer_fuel_level'] ?? '') !== '' ? (float) $data['reefer_fuel_level'] : null,
            ($data['pulp_temperature'] ?? '') !== '' ? (float) $data['pulp_temperature'] : null,
            ($data['latitude'] ?? '') !== '' ? (float) $data['latitude'] : null,
            ($data['longitude'] ?? '') !== '' ? (float) $data['longitude'] : null,
            trim((string) ($data['location_address'] ?? '')) ?: null,
            trim((string) ($data['notes'] ?? '')) ?: null,
            !empty($data['recorded_at']) ? $data['recorded_at'] : date('Y-m-d H:i:s'),
            $_SESSION['user_id'] ?? null,
        ]);
        $id = (int) $this->db->lastInsertId();

        if (!empty($data['trailer_id'])) {
            $update = $this->db->prepare('UPDATE trailers SET current_temperature=?, current_setpoint=COALESCE(?,current_setpoint), reefer_mode=COALESCE(?,reefer_mode), reefer_fuel_level=COALESCE(?,reefer_fuel_level), last_temperature_at=? WHERE id=?');
            $update->execute([
                (float) $data['temperature'],
                ($data['setpoint'] ?? '') !== '' ? (float) $data['setpoint'] : null,
                in_array(($data['reefer_mode'] ?? ''), ['continuous','cycle_sentry','off'], true) ? $data['reefer_mode'] : null,
                ($data['reefer_fuel_level'] ?? '') !== '' ? (float) $data['reefer_fuel_level'] : null,
                !empty($data['recorded_at']) ? $data['recorded_at'] : date('Y-m-d H:i:s'),
                (int) $data['trailer_id'],
            ]);
        }
        if (!empty($data['stop_id'])) {
            $this->db->prepare('UPDATE load_stops SET temperature_reading=?, temperature_unit=? WHERE id=? AND load_id=?')
                ->execute([(float)$data['temperature'], $data['temperature_unit'] ?? 'F', (int)$data['stop_id'], (int)$data['load_id']]);
        }
        return $id;
    }

    public function latestAlertCount(): int
    {
        $sql = "SELECT COUNT(*) FROM loads l
            JOIN temperature_logs t ON t.id=(SELECT t2.id FROM temperature_logs t2 WHERE t2.load_id=l.id ORDER BY t2.recorded_at DESC,t2.id DESC LIMIT 1)
            WHERE l.temperature_controlled=1
            AND ((l.temperature_min IS NOT NULL AND t.temperature < l.temperature_min)
              OR (l.temperature_max IS NOT NULL AND t.temperature > l.temperature_max))";
        return (int) $this->db->query($sql)->fetchColumn();
    }
}
