<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Core\Database;
use PDO;
use RuntimeException;

final class PaymentRepository
{
    public function paginate(array $filters, int $perPage = 25): array
    {
        $where=['1=1']; $params=[];
        if (($filters['search']??'')!=='') {
            $where[]='(py.payment_reference LIKE :ref OR py.payment_method LIKE :method OR i.invoice_number LIKE :invoice OR p.first_name LIKE :first OR p.last_name LIKE :last)';
            $s='%'.trim((string)$filters['search']).'%';
            $params=['ref'=>$s,'method'=>$s,'invoice'=>$s,'first'=>$s,'last'=>$s];
        }
        if (($filters['status']??'')!=='') {$where[]='py.status=:status';$params['status']=$filters['status'];}
        $whereSql='WHERE '.implode(' AND ',$where); $pdo=Database::connection();
        $count=$pdo->prepare("SELECT COUNT(*) FROM payments py INNER JOIN invoices i ON i.id=py.invoice_id INNER JOIN patients p ON p.id=i.patient_id $whereSql");
        $count->execute($params); $total=(int)$count->fetchColumn();
        $pages=max(1,(int)ceil($total/$perPage)); $page=min(max(1,(int)($filters['page']??1)),$pages); $offset=($page-1)*$perPage;
        $sql="SELECT py.*,i.invoice_number,i.total AS invoice_total,p.patient_reference,p.first_name,p.last_name,u.full_name AS recorded_by_name
              FROM payments py INNER JOIN invoices i ON i.id=py.invoice_id INNER JOIN patients p ON p.id=i.patient_id INNER JOIN users u ON u.id=py.recorded_by
              $whereSql ORDER BY py.payment_date DESC,py.id DESC LIMIT :limit OFFSET :offset";
        $stmt=$pdo->prepare($sql); foreach($params as $k=>$v)$stmt->bindValue(':'.$k,$v); $stmt->bindValue(':limit',$perPage,PDO::PARAM_INT);$stmt->bindValue(':offset',$offset,PDO::PARAM_INT);$stmt->execute();
        return ['items'=>$stmt->fetchAll(),'total'=>$total,'page'=>$page,'pages'=>$pages,'perPage'=>$perPage];
    }

    public function find(int $id): ?array
    {
        $stmt=Database::connection()->prepare('SELECT py.*,i.invoice_number,i.total AS invoice_total,i.patient_id,p.patient_reference,p.first_name,p.last_name FROM payments py INNER JOIN invoices i ON i.id=py.invoice_id INNER JOIN patients p ON p.id=i.patient_id WHERE py.id=:id LIMIT 1');
        $stmt->execute(['id'=>$id]);$r=$stmt->fetch();return is_array($r)?$r:null;
    }

    public function invoice(int $id): ?array
    {
        $stmt=Database::connection()->prepare('SELECT i.*,p.patient_reference,p.first_name,p.last_name FROM invoices i INNER JOIN patients p ON p.id=i.patient_id WHERE i.id=:id LIMIT 1');
        $stmt->execute(['id'=>$id]);$r=$stmt->fetch();return is_array($r)?$r:null;
    }

    public function invoicePayments(int $invoiceId): array
    {
        $stmt=Database::connection()->prepare('SELECT py.*,u.full_name AS recorded_by_name,r.receipt_number FROM payments py INNER JOIN users u ON u.id=py.recorded_by LEFT JOIN receipts r ON r.payment_id=py.id WHERE py.invoice_id=:id ORDER BY py.payment_date DESC,py.id DESC');
        $stmt->execute(['id'=>$invoiceId]);return $stmt->fetchAll();
    }

    public function activePaidTotal(int $invoiceId): float
    {
        $stmt=Database::connection()->prepare("SELECT COALESCE(SUM(amount),0) FROM payments WHERE invoice_id=:id AND status='posted'");$stmt->execute(['id'=>$invoiceId]);return (float)$stmt->fetchColumn();
    }

    public function create(array $data, int $actor): int
    {
        $pdo=Database::connection();$pdo->beginTransaction();
        try {
            $stmt=$pdo->prepare("INSERT INTO payments (invoice_id,amount,payment_date,payment_method,payment_reference,status,notes,recorded_by) VALUES (:invoice_id,:amount,:payment_date,:payment_method,:payment_reference,'posted',:notes,:recorded_by)");
            $stmt->execute($data+['recorded_by'=>$actor]);$id=(int)$pdo->lastInsertId();
            $this->syncInvoiceStatus($pdo,(int)$data['invoice_id']);$receipt=$this->nextReceiptNumber($pdo);
            $pdo->prepare('INSERT INTO receipts (receipt_number,payment_id,created_by) VALUES (:number,:payment,:created_by)')->execute(['number'=>$receipt,'payment'=>$id,'created_by'=>$actor]);
            $pdo->commit();return $id;
        } catch(\Throwable $e){if($pdo->inTransaction())$pdo->rollBack();throw $e;}
    }

    public function void(int $id,int $actor): void
    {
        $pdo=Database::connection();$pdo->beginTransaction();
        try {
            $payment=$this->find($id);if(!$payment)throw new RuntimeException('Payment not found.');
            if(($payment['status']??'posted')!=='posted')throw new RuntimeException('Only posted payments can be voided.');
            $stmt=$pdo->prepare("UPDATE payments SET status='voided',voided_at=NOW(),voided_by=:actor WHERE id=:id");$stmt->execute(['actor'=>$actor,'id'=>$id]);
            $this->syncInvoiceStatus($pdo,(int)$payment['invoice_id']);$pdo->commit();
        } catch(\Throwable $e){if($pdo->inTransaction())$pdo->rollBack();throw $e;}
    }

    public function audit(int $userId,string $action,int $entityId,array $metadata=[]):void
    { $stmt=Database::connection()->prepare('INSERT INTO audit_logs (user_id,action,entity_type,entity_id,ip_address,metadata,created_at) VALUES (:user_id,:action,:entity_type,:entity_id,:ip,:metadata,NOW())');$stmt->execute(['user_id'=>$userId,'action'=>$action,'entity_type'=>'payment','entity_id'=>$entityId,'ip'=>$_SERVER['REMOTE_ADDR']??null,'metadata'=>json_encode($metadata,JSON_THROW_ON_ERROR)]); }

    private function syncInvoiceStatus(PDO $pdo,int $invoiceId):void
    {
        $stmt=$pdo->prepare('SELECT total FROM invoices WHERE id=:id FOR UPDATE');$stmt->execute(['id'=>$invoiceId]);$total=(float)$stmt->fetchColumn();
        $sum=$pdo->prepare("SELECT COALESCE(SUM(amount),0) FROM payments WHERE invoice_id=:id AND status='posted'");$sum->execute(['id'=>$invoiceId]);$paid=(float)$sum->fetchColumn();
        $status=$paid<=0?'unpaid':($paid+0.00001 >= $total?'paid':'partially_paid');
        $pdo->prepare('UPDATE invoices SET status=:status,updated_at=NOW() WHERE id=:id AND status<>\'cancelled\'')->execute(['status'=>$status,'id'=>$invoiceId]);
    }

    private function nextReceiptNumber(PDO $pdo):string
    {
        $stmt=$pdo->prepare('SELECT id,prefix,next_number,padding FROM numbering_sequences WHERE sequence_key=:key FOR UPDATE');$stmt->execute(['key'=>'receipt']);$row=$stmt->fetch();
        if(!$row){$pdo->prepare('INSERT INTO numbering_sequences(sequence_key,prefix,next_number,padding) VALUES ("receipt","RCT-",1,6)')->execute();$row=['prefix'=>'RCT-','next_number'=>1,'padding'=>6];}
        $number=(string)$row['prefix'].str_pad((string)$row['next_number'],(int)$row['padding'],'0',STR_PAD_LEFT);
        $pdo->prepare('UPDATE numbering_sequences SET next_number=next_number+1,updated_at=NOW() WHERE sequence_key=:key')->execute(['key'=>'receipt']);return $number;
    }
}
