<?php
declare(strict_types=1);
namespace App\Repositories;
use App\Core\Database;
use PDO;
final class BillingRateRepository {
    public function patients(): array { return Database::connection()->query("SELECT id,patient_reference,first_name,last_name FROM patients WHERE deleted_at IS NULL AND status='active' ORDER BY last_name,first_name")->fetchAll(); }
    public function rates(int $patientId): array {
        $st=Database::connection()->prepare("SELECT pbr.id,pbr.patient_id,pbr.service_id,pbr.rate,pbr.label,s.name AS service_name,s.price AS catalogue_price FROM patient_billing_rates pbr INNER JOIN services s ON s.id=pbr.service_id WHERE pbr.patient_id=:patient_id ORDER BY s.name");
        $st->execute(['patient_id'=>$patientId]); return $st->fetchAll();
    }
    public function services(): array { return Database::connection()->query("SELECT id,name,price FROM services WHERE status='active' ORDER BY name")->fetchAll(); }
    public function save(int $patientId,int $serviceId,float $rate,string $label): void {
        $st=Database::connection()->prepare("INSERT INTO patient_billing_rates(patient_id,service_id,rate,label) VALUES(:patient_id,:service_id,:rate,:label) ON DUPLICATE KEY UPDATE rate=VALUES(rate),label=VALUES(label),updated_at=NOW()");
        $st->execute(['patient_id'=>$patientId,'service_id'=>$serviceId,'rate'=>number_format($rate,2,'.',''),'label'=>$label!==''?$label:null]);
    }
    public function delete(int $id): void { Database::connection()->prepare('DELETE FROM patient_billing_rates WHERE id=:id')->execute(['id'=>$id]); }
    public function rateFor(int $patientId,int $serviceId): ?float { $st=Database::connection()->prepare('SELECT rate FROM patient_billing_rates WHERE patient_id=:patient_id AND service_id=:service_id LIMIT 1');$st->execute(['patient_id'=>$patientId,'service_id'=>$serviceId]);$v=$st->fetchColumn();return $v===false?null:(float)$v; }
    public function ensureDefaults(int $patientId): void {
        $sql="INSERT IGNORE INTO patient_billing_rates(patient_id,service_id,rate,label) SELECT :patient_id,id,price,'Standard' FROM services WHERE status='active'";
        Database::connection()->prepare($sql)->execute(['patient_id'=>$patientId]);
    }
}
