<?php

class Document extends BaseModel
{
    public const RELATED_TYPES = ['carrier', 'driver', 'truck', 'trailer', 'broker', 'load', 'invoice', 'stop', 'border_crossing', 'border_issue', 'expense', 'vendor_bill', 'fuel_allocation', 'fuel_transaction'];

    public function all(?string $search = null): array
    {
        $sql = 'SELECT documents.*, users.name AS uploaded_by_name FROM documents LEFT JOIN users ON users.id = documents.uploaded_by';
        $params = [];
        if ($search) {
            $sql .= ' WHERE documents.document_type LIKE ? OR documents.file_name LIKE ? OR documents.related_type LIKE ? OR documents.location_address LIKE ?';
            $term = "%{$search}%";
            $params = [$term, $term, $term, $term];
        }
        $sql .= ' ORDER BY documents.id DESC';
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    public function find(int $id): ?array
    {
        $stmt = $this->db->prepare('SELECT * FROM documents WHERE id = ?');
        $stmt->execute([$id]);
        return $stmt->fetch() ?: null;
    }

    public function forRelation(string $type, int $id): array
    {
        $stmt = $this->db->prepare('SELECT * FROM documents WHERE related_type = ? AND related_id = ? ORDER BY id DESC');
        $stmt->execute([$type, $id]);
        return $stmt->fetchAll();
    }

    public function forLoadWithStops(int $loadId): array
    {
        $stmt = $this->db->prepare('SELECT documents.*,
                CASE WHEN documents.related_type = "stop" THEN load_stops.location ELSE NULL END AS stop_location,
                CASE WHEN documents.related_type = "stop" THEN load_stops.sequence ELSE NULL END AS stop_sequence
            FROM documents
            LEFT JOIN load_stops ON documents.related_type = "stop" AND documents.related_id = load_stops.id
            WHERE (documents.related_type = "load" AND documents.related_id = ?)
               OR (documents.related_type = "stop" AND load_stops.load_id = ?)
            ORDER BY documents.id DESC');
        $stmt->execute([$loadId, $loadId]);
        return $stmt->fetchAll();
    }

    public function create(array $data): int
    {
        $stmt = $this->db->prepare('INSERT INTO documents (related_type, related_id, document_type, file_name, stored_name, file_path, mime_type, file_size, expires_at, notes, latitude, longitude, location_address, captured_at, uploaded_by) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)');
        $stmt->execute([
            $data['related_type'],
            (int) $data['related_id'],
            $data['document_type'],
            $data['file_name'],
            $data['stored_name'],
            $data['file_path'],
            $data['mime_type'] ?? null,
            $data['file_size'] ?? null,
            !empty($data['expires_at']) ? $data['expires_at'] : null,
            !empty($data['notes']) ? $data['notes'] : null,
            isset($data['latitude']) && $data['latitude'] !== '' ? $data['latitude'] : null,
            isset($data['longitude']) && $data['longitude'] !== '' ? $data['longitude'] : null,
            !empty($data['location_address']) ? $data['location_address'] : null,
            !empty($data['captured_at']) ? $data['captured_at'] : date('Y-m-d H:i:s'),
            $_SESSION['user_id'] ?? null,
        ]);
        return (int) $this->db->lastInsertId();
    }

    public function delete(int $id): ?array
    {
        $document = $this->find($id);
        if (!$document) {
            return null;
        }
        $stmt = $this->db->prepare('DELETE FROM documents WHERE id = ?');
        $stmt->execute([$id]);
        return $document;
    }

    public function expiring(int $days = 30): array
    {
        $stmt = $this->db->prepare('SELECT * FROM documents WHERE expires_at IS NOT NULL AND expires_at <= DATE_ADD(CURDATE(), INTERVAL ? DAY) ORDER BY expires_at ASC LIMIT 12');
        $stmt->execute([$days]);
        return $stmt->fetchAll();
    }


    public function forCarrier(int $carrierId): array
    {
        $stmt = $this->db->prepare("SELECT DISTINCT documents.*, users.name AS uploaded_by_name
            FROM documents
            LEFT JOIN users ON users.id=documents.uploaded_by
            LEFT JOIN load_stops ON documents.related_type='stop' AND documents.related_id=load_stops.id
            LEFT JOIN loads ON (documents.related_type='load' AND documents.related_id=loads.id)
                OR (documents.related_type='stop' AND load_stops.load_id=loads.id)
            LEFT JOIN border_crossings ON documents.related_type='border_crossing' AND documents.related_id=border_crossings.id
            LEFT JOIN loads border_load ON border_load.id=border_crossings.load_id
            WHERE (documents.related_type='carrier' AND documents.related_id=?)
               OR loads.carrier_id=? OR border_load.carrier_id=?
            ORDER BY documents.id DESC");
        $stmt->execute([$carrierId,$carrierId,$carrierId]);
        return $stmt->fetchAll();
    }

    public function carrierCanAccess(int $documentId, int $carrierId): bool
    {
        $stmt = $this->db->prepare("SELECT COUNT(*) FROM documents
            LEFT JOIN load_stops ON documents.related_type='stop' AND documents.related_id=load_stops.id
            LEFT JOIN loads ON (documents.related_type='load' AND documents.related_id=loads.id)
                OR (documents.related_type='stop' AND load_stops.load_id=loads.id)
            LEFT JOIN border_crossings ON documents.related_type='border_crossing' AND documents.related_id=border_crossings.id
            LEFT JOIN loads border_load ON border_load.id=border_crossings.load_id
            WHERE documents.id=? AND ((documents.related_type='carrier' AND documents.related_id=?) OR loads.carrier_id=? OR border_load.carrier_id=?)");
        $stmt->execute([$documentId,$carrierId,$carrierId,$carrierId]);
        return (int)$stmt->fetchColumn()>0;
    }

    public function forDriver(int $driverId): array
    {
        $stmt=$this->db->prepare("SELECT DISTINCT documents.*,users.name uploaded_by_name
            FROM documents
            LEFT JOIN users ON users.id=documents.uploaded_by
            LEFT JOIN load_stops ON documents.related_type='stop' AND documents.related_id=load_stops.id
            LEFT JOIN loads ON (documents.related_type='load' AND documents.related_id=loads.id)
                OR (documents.related_type='stop' AND load_stops.load_id=loads.id)
                OR (documents.related_type='border_crossing' AND documents.related_id IN (SELECT id FROM border_crossings WHERE border_crossings.load_id=loads.id))
            LEFT JOIN fuel_transactions ON documents.related_type='fuel_transaction' AND documents.related_id=fuel_transactions.id
            LEFT JOIN fuel_allocations ON fuel_allocations.id=fuel_transactions.allocation_id
            WHERE (documents.related_type='driver' AND documents.related_id=?) OR loads.driver_id=? OR fuel_allocations.driver_id=?
            ORDER BY documents.id DESC");
        $stmt->execute([$driverId,$driverId,$driverId]);return $stmt->fetchAll();
    }

    public function driverCanAccess(int $documentId,int $driverId): bool
    {
        $stmt=$this->db->prepare("SELECT COUNT(*) FROM documents
            LEFT JOIN load_stops ON documents.related_type='stop' AND documents.related_id=load_stops.id
            LEFT JOIN loads ON (documents.related_type='load' AND documents.related_id=loads.id) OR (documents.related_type='stop' AND load_stops.load_id=loads.id)
            LEFT JOIN border_crossings ON documents.related_type='border_crossing' AND documents.related_id=border_crossings.id
            LEFT JOIN loads border_load ON border_load.id=border_crossings.load_id
            LEFT JOIN fuel_transactions ON documents.related_type='fuel_transaction' AND documents.related_id=fuel_transactions.id
            LEFT JOIN fuel_allocations ON fuel_allocations.id=fuel_transactions.allocation_id
            WHERE documents.id=? AND ((documents.related_type='driver' AND documents.related_id=?) OR loads.driver_id=? OR border_load.driver_id=? OR fuel_allocations.driver_id=?)");
        $stmt->execute([$documentId,$driverId,$driverId,$driverId,$driverId]);return (int)$stmt->fetchColumn()>0;
    }

}
