<?php

class Fuel extends BaseModel
{
    public function allocations(?int $driverId = null, ?string $status = null): array
    {
        $sql = "SELECT fuel_allocations.*,
                    loads.load_number,
                    carriers.company_name AS carrier_name,
                    drivers.name AS driver_name,
                    trucks.unit_number,
                    bank_accounts.account_name AS funding_account,
                    users.name AS created_by_name
                FROM fuel_allocations
                LEFT JOIN loads ON loads.id = fuel_allocations.load_id
                LEFT JOIN carriers ON carriers.id = fuel_allocations.carrier_id
                LEFT JOIN drivers ON drivers.id = fuel_allocations.driver_id
                LEFT JOIN trucks ON trucks.id = fuel_allocations.truck_id
                LEFT JOIN bank_accounts ON bank_accounts.id = fuel_allocations.bank_account_id
                LEFT JOIN users ON users.id = fuel_allocations.created_by
                WHERE 1=1";
        $params = [];
        if ($driverId) {
            $sql .= ' AND fuel_allocations.driver_id = ?';
            $params[] = $driverId;
        }
        if ($status) {
            $sql .= ' AND fuel_allocations.status = ?';
            $params[] = $status;
        }
        $sql .= ' ORDER BY fuel_allocations.id DESC';
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

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

    public function createAllocation(array $data): int
    {
        $plannedMiles = max(0, (float) ($data['planned_miles'] ?? 0));
        $targetMpg = max(0.1, (float) ($data['target_mpg'] ?? 6.5));
        $fuelPrice = max(0, (float) ($data['expected_price_per_gallon'] ?? 0));
        $reservePercent = max(0, (float) ($data['reserve_percent'] ?? 0));
        $recommendedGallons = $plannedMiles > 0 ? $plannedMiles / $targetMpg : 0;
        $recommendedAmount = $recommendedGallons * $fuelPrice * (1 + ($reservePercent / 100));
        $allocatedAmount = (float) ($data['allocated_amount'] ?? 0);
        if ($allocatedAmount <= 0) {
            $allocatedAmount = $recommendedAmount;
        }

        $number = trim((string) ($data['allocation_number'] ?? '')) ?: next_reference('FUEL-', 'fuel_allocations', 'allocation_number');
        $status = in_array(($data['status'] ?? ''), ['draft', 'approved', 'issued'], true) ? $data['status'] : 'draft';
        $approvedBy = in_array($status, ['approved', 'issued'], true) ? ($_SESSION['user_id'] ?? null) : null;
        $approvedAt = in_array($status, ['approved', 'issued'], true) ? date('Y-m-d H:i:s') : null;

        $stmt = $this->db->prepare('INSERT INTO fuel_allocations
            (allocation_number, load_id, carrier_id, driver_id, truck_id, bank_account_id, allocation_method, calculation_method,
             planned_miles, target_mpg, expected_price_per_gallon, reserve_percent, recommended_gallons, recommended_amount,
             allocated_amount, spent_amount, remaining_amount, valid_from, valid_until, status, notes, created_by, approved_by, approved_at)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)');
        $stmt->execute([
            $number,
            !empty($data['load_id']) ? (int) $data['load_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['bank_account_id']) ? (int) $data['bank_account_id'] : null,
            $data['allocation_method'] ?? 'fuel_card',
            $data['calculation_method'] ?? 'miles_mpg',
            $plannedMiles,
            $targetMpg,
            $fuelPrice,
            $reservePercent,
            round($recommendedGallons, 2),
            round($recommendedAmount, 2),
            round($allocatedAmount, 2),
            0,
            round($allocatedAmount, 2),
            !empty($data['valid_from']) ? $data['valid_from'] : date('Y-m-d'),
            !empty($data['valid_until']) ? $data['valid_until'] : null,
            $status,
            trim((string) ($data['notes'] ?? '')) ?: null,
            $_SESSION['user_id'] ?? null,
            $approvedBy,
            $approvedAt,
        ]);
        return (int) $this->db->lastInsertId();
    }

    public function setAllocationStatus(int $id, string $status): void
    {
        $allowed = ['draft', 'approved', 'issued', 'closed', 'cancelled'];
        if (!in_array($status, $allowed, true)) return;
        $sql = 'UPDATE fuel_allocations SET status = ?';
        $params = [$status];
        if (in_array($status, ['approved', 'issued'], true)) {
            $sql .= ', approved_by = ?, approved_at = NOW()';
            $params[] = $_SESSION['user_id'] ?? null;
        }
        $sql .= ' WHERE id = ?';
        $params[] = $id;
        $this->db->prepare($sql)->execute($params);
    }

    public function transactions(?int $allocationId = null, ?int $driverId = null): array
    {
        $sql = "SELECT fuel_transactions.*,
                    fuel_allocations.allocation_number,
                    loads.load_number,
                    drivers.name AS driver_name,
                    trucks.unit_number,
                    documents.file_name AS receipt_name
                FROM fuel_transactions
                JOIN fuel_allocations ON fuel_allocations.id = fuel_transactions.allocation_id
                LEFT JOIN loads ON loads.id = fuel_allocations.load_id
                LEFT JOIN drivers ON drivers.id = fuel_allocations.driver_id
                LEFT JOIN trucks ON trucks.id = fuel_allocations.truck_id
                LEFT JOIN documents ON documents.id = fuel_transactions.receipt_document_id
                WHERE 1=1";
        $params = [];
        if ($allocationId) {
            $sql .= ' AND fuel_transactions.allocation_id = ?';
            $params[] = $allocationId;
        }
        if ($driverId) {
            $sql .= ' AND fuel_allocations.driver_id = ?';
            $params[] = $driverId;
        }
        $sql .= ' ORDER BY fuel_transactions.id DESC';
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    public function createTransaction(array $data): int
    {
        $gallons = max(0, (float) ($data['gallons'] ?? 0));
        $price = max(0, (float) ($data['price_per_gallon'] ?? 0));
        $amount = max(0, (float) ($data['amount'] ?? 0));
        if ($amount <= 0 && $gallons > 0 && $price > 0) {
            $amount = $gallons * $price;
        }
        if ($amount <= 0) {
            throw new RuntimeException('Fuel transaction amount is required.');
        }
        $number = next_reference('FTX-', 'fuel_transactions', 'transaction_number');
        $stmt = $this->db->prepare('INSERT INTO fuel_transactions
            (allocation_id, transaction_number, transaction_date, merchant, city_state, gallons, price_per_gallon, amount, odometer,
             status, notes, latitude, longitude, location_address, submitted_by)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)');
        $stmt->execute([
            (int) $data['allocation_id'],
            $number,
            !empty($data['transaction_date']) ? $data['transaction_date'] : date('Y-m-d'),
            trim((string) ($data['merchant'] ?? '')) ?: null,
            trim((string) ($data['city_state'] ?? '')) ?: null,
            $gallons,
            $price,
            round($amount, 2),
            !empty($data['odometer']) ? (int) $data['odometer'] : null,
            'submitted',
            trim((string) ($data['notes'] ?? '')) ?: null,
            ($data['latitude'] ?? '') !== '' ? $data['latitude'] : null,
            ($data['longitude'] ?? '') !== '' ? $data['longitude'] : null,
            trim((string) ($data['location_address'] ?? '')) ?: null,
            $_SESSION['user_id'] ?? null,
        ]);
        return (int) $this->db->lastInsertId();
    }

    public function attachReceipt(int $transactionId, int $documentId): void
    {
        $this->db->prepare('UPDATE fuel_transactions SET receipt_document_id = ? WHERE id = ?')->execute([$documentId, $transactionId]);
    }

    public function setTransactionStatus(int $id, string $status): void
    {
        $allowed = ['submitted', 'approved', 'rejected', 'posted'];
        if (!in_array($status, $allowed, true)) return;
        $sql = 'UPDATE fuel_transactions SET status = ?';
        $params = [$status];
        if (in_array($status, ['approved', 'posted'], true)) {
            $sql .= ', approved_by = ?, approved_at = NOW()';
            $params[] = $_SESSION['user_id'] ?? null;
        }
        $sql .= ' WHERE id = ?';
        $params[] = $id;
        $this->db->prepare($sql)->execute($params);
        $stmt = $this->db->prepare('SELECT allocation_id FROM fuel_transactions WHERE id = ?');
        $stmt->execute([$id]);
        $allocationId = (int) ($stmt->fetchColumn() ?: 0);
        if ($allocationId) $this->refreshAllocationTotals($allocationId);
    }

    public function transaction(int $id): ?array
    {
        $stmt = $this->db->prepare('SELECT fuel_transactions.*, fuel_allocations.load_id, fuel_allocations.driver_id, fuel_allocations.truck_id, fuel_allocations.allocation_method FROM fuel_transactions JOIN fuel_allocations ON fuel_allocations.id=fuel_transactions.allocation_id WHERE fuel_transactions.id=?');
        $stmt->execute([$id]);
        return $stmt->fetch() ?: null;
    }

    public function linkExpense(int $transactionId, int $expenseId): void
    {
        $this->db->prepare('UPDATE fuel_transactions SET expense_id = ?, status = "posted" WHERE id = ?')->execute([$expenseId, $transactionId]);
        $transaction = $this->transaction($transactionId);
        if ($transaction) $this->refreshAllocationTotals((int) $transaction['allocation_id']);
    }

    public function refreshAllocationTotals(int $allocationId): void
    {
        $stmt = $this->db->prepare("SELECT COALESCE(SUM(amount),0) FROM fuel_transactions WHERE allocation_id=? AND status IN ('approved','posted')");
        $stmt->execute([$allocationId]);
        $spent = (float) $stmt->fetchColumn();
        $allocation = $this->findAllocation($allocationId);
        if (!$allocation) return;
        $remaining = (float) $allocation['allocated_amount'] - $spent;
        $status = $allocation['status'];
        if (!in_array($status, ['draft', 'cancelled', 'closed'], true)) {
            if ($spent <= 0) $status = $status === 'approved' ? 'approved' : 'issued';
            elseif ($remaining > 0.009) $status = 'partially_used';
            else $status = 'used';
        }
        $this->db->prepare('UPDATE fuel_allocations SET spent_amount=?, remaining_amount=?, status=? WHERE id=?')
            ->execute([round($spent,2), round($remaining,2), $status, $allocationId]);
    }

    public function summary(?int $driverId = null): array
    {
        $where = $driverId ? ' WHERE driver_id = ?' : '';
        $params = $driverId ? [$driverId] : [];
        $stmt = $this->db->prepare("SELECT
            COALESCE(SUM(CASE WHEN status NOT IN ('cancelled','closed') THEN allocated_amount ELSE 0 END),0) AS allocated,
            COALESCE(SUM(CASE WHEN status NOT IN ('cancelled') THEN spent_amount ELSE 0 END),0) AS spent,
            COALESCE(SUM(CASE WHEN status NOT IN ('cancelled','closed') THEN remaining_amount ELSE 0 END),0) AS remaining,
            SUM(CASE WHEN status IN ('draft','approved') THEN 1 ELSE 0 END) AS pending_count
            FROM fuel_allocations{$where}");
        $stmt->execute($params);
        return $stmt->fetch() ?: ['allocated'=>0,'spent'=>0,'remaining'=>0,'pending_count'=>0];
    }
}
