<?php

class Settlement extends BaseModel
{
    public function all(?string $search = null): array
    {
        $sql = 'SELECT driver_settlements.*, drivers.name AS driver_name, loads.load_number
                FROM driver_settlements
                LEFT JOIN drivers ON drivers.id = driver_settlements.driver_id
                LEFT JOIN loads ON loads.id = driver_settlements.load_id';
        $params = [];
        if ($search) {
            $sql .= ' WHERE drivers.name LIKE ? OR loads.load_number LIKE ? OR driver_settlements.settlement_number LIKE ?';
            $term = "%{$search}%";
            $params = [$term, $term, $term];
        }
        $sql .= ' ORDER BY driver_settlements.id DESC';
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    public function find(int $id): ?array
    {
        $stmt = $this->db->prepare('SELECT driver_settlements.*, drivers.name AS driver_name, drivers.pay_type, drivers.rate AS driver_rate, loads.load_number, loads.rate AS load_rate, loads.miles
                                   FROM driver_settlements
                                   LEFT JOIN drivers ON drivers.id = driver_settlements.driver_id
                                   LEFT JOIN loads ON loads.id = driver_settlements.load_id
                                   WHERE driver_settlements.id = ?');
        $stmt->execute([$id]);
        return $stmt->fetch() ?: null;
    }

    public function create(array $data): int
    {
        $gross = (float) ($data['gross_amount'] ?? 0);
        $deductions = (float) ($data['deductions'] ?? 0);
        $net = max(0, $gross - $deductions);
        $stmt = $this->db->prepare('INSERT INTO driver_settlements (settlement_number, driver_id, load_id, gross_amount, deductions, net_amount, status, paid_at, notes) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)');
        $stmt->execute([
            trim($data['settlement_number'] ?? '') ?: $this->nextNumber(), (int) $data['driver_id'], $data['load_id'] ?: null,
            $gross, $deductions, $net, $data['status'] ?: 'pending', ($data['status'] ?? '') === 'paid' ? date('Y-m-d H:i:s') : null, $data['notes'] ?: null,
        ]);
        return (int) $this->db->lastInsertId();
    }

    public function markPaid(int $id): void
    {
        $stmt = $this->db->prepare("UPDATE driver_settlements SET status = 'paid', paid_at = NOW() WHERE id = ?");
        $stmt->execute([$id]);
    }

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

    public function nextNumber(): string
    {
        return 'SET-' . date('Ymd') . '-' . str_pad((string) random_int(1, 9999), 4, '0', STR_PAD_LEFT);
    }

    public function payableLoads(): array
    {
        return $this->db->query("SELECT loads.*, drivers.name AS driver_name, drivers.pay_type, drivers.rate AS driver_rate
                                FROM loads
                                INNER JOIN drivers ON drivers.id = loads.driver_id
                                LEFT JOIN driver_settlements ds ON ds.load_id = loads.id
                                WHERE loads.status IN ('delivered', 'invoiced', 'paid') AND ds.id IS NULL
                                ORDER BY loads.id DESC")->fetchAll();
    }

    public function calculateForLoad(int $loadId): ?array
    {
        $stmt = $this->db->prepare('SELECT loads.*, drivers.name AS driver_name, drivers.pay_type, drivers.rate AS driver_rate
                                   FROM loads INNER JOIN drivers ON drivers.id = loads.driver_id WHERE loads.id = ?');
        $stmt->execute([$loadId]);
        $load = $stmt->fetch();
        if (!$load) {
            return null;
        }

        $gross = match ($load['pay_type']) {
            'percentage' => ((float) $load['rate'] * (float) $load['driver_rate']) / 100,
            'per_mile' => (float) $load['miles'] * (float) $load['driver_rate'],
            default => (float) $load['driver_rate'],
        };
        $load['calculated_gross'] = round($gross, 2);
        return $load;
    }
}
