<?php

declare(strict_types=1);

namespace App\Repositories;

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

final class InvoiceRepository
{
    public function paginate(array $filters, int $perPage = 25): array
    {
        $where = ['1=1']; $params = [];
        if (($filters['search'] ?? '') !== '') {
            $where[] = '(i.invoice_number LIKE :search_invoice OR p.patient_reference LIKE :search_ref OR p.first_name LIKE :search_first OR p.last_name LIKE :search_last)';
            $search = '%' . trim((string)$filters['search']) . '%';
            $params += ['search_invoice'=>$search,'search_ref'=>$search,'search_first'=>$search,'search_last'=>$search];
        }
        if (($filters['status'] ?? '') !== '') { $where[]='i.status=:status'; $params['status']=$filters['status']; }
        $whereSql='WHERE '.implode(' AND ',$where); $pdo=Database::connection();
        $count=$pdo->prepare("SELECT COUNT(*) FROM invoices i 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 i.*, p.patient_reference,p.first_name,p.last_name,
                     COALESCE((SELECT SUM(py.amount) FROM payments py WHERE py.invoice_id=i.id),0) AS paid_amount,
                     i.total-COALESCE((SELECT SUM(py.amount) FROM payments py WHERE py.invoice_id=i.id),0) AS balance
              FROM invoices i INNER JOIN patients p ON p.id=i.patient_id $whereSql
              ORDER BY i.invoice_date DESC,i.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 i.*,p.patient_reference,p.first_name,p.last_name,p.email AS patient_email,u.full_name AS created_by_name FROM invoices i INNER JOIN patients p ON p.id=i.patient_id INNER JOIN users u ON u.id=i.created_by WHERE i.id=:id LIMIT 1');
        $stmt->execute(['id'=>$id]); $row=$stmt->fetch(); return is_array($row)?$row:null;
    }

    public function items(int $invoiceId): array
    {
        $stmt=Database::connection()->prepare('SELECT ii.*,s.name AS service_name FROM invoice_items ii LEFT JOIN services s ON s.id=ii.service_id WHERE ii.invoice_id=:id ORDER BY ii.id');
        $stmt->execute(['id'=>$invoiceId]); return $stmt->fetchAll();
    }

    public function patients(): array
    { return Database::connection()->query('SELECT id,patient_reference,first_name,last_name FROM patients WHERE status="active" AND deleted_at IS NULL ORDER BY last_name,first_name')->fetchAll(); }

    public function services(): array
    { return Database::connection()->query('SELECT id,name,price FROM services WHERE status="active" ORDER BY name')->fetchAll(); }

    public function create(array $data, array $items): int
    {
        $pdo=Database::connection(); $pdo->beginTransaction();
        try {
            $number=$this->nextNumber($pdo);
            $stmt=$pdo->prepare('INSERT INTO invoices (invoice_number,patient_id,invoice_date,due_date,subtotal,discount,tax,total,status,notes,created_by) VALUES (:invoice_number,:patient_id,:invoice_date,:due_date,:subtotal,:discount,:tax,:total,"unpaid",:notes,:created_by)');
            $stmt->execute($data+['invoice_number'=>$number]); $id=(int)$pdo->lastInsertId();
            $this->insertItems($pdo,$id,$items); $pdo->commit(); return $id;
        } catch (\Throwable $e) { if($pdo->inTransaction())$pdo->rollBack(); throw $e; }
    }

    public function createFromSessions(array $data, array $items): int
    {
        return $this->create($data, $items);
    }

    public function update(int $id,array $data,array $items): void
    {
        $pdo=Database::connection(); $pdo->beginTransaction();
        try {
            if($this->paymentCount($id)>0) throw new RuntimeException('This invoice has payments recorded and can no longer be edited.');
            $stmt=$pdo->prepare('UPDATE invoices SET patient_id=:patient_id,invoice_date=:invoice_date,due_date=:due_date,subtotal=:subtotal,discount=:discount,tax=:tax,total=:total,notes=:notes,updated_at=NOW() WHERE id=:id');
            $stmt->execute($data+['id'=>$id]);
            $pdo->prepare('DELETE FROM invoice_items WHERE invoice_id=:id')->execute(['id'=>$id]); $this->insertItems($pdo,$id,$items); $pdo->commit();
        } catch(\Throwable $e){if($pdo->inTransaction())$pdo->rollBack();throw $e;}
    }

    public function setStatus(int $id,string $status): void
    { $stmt=Database::connection()->prepare('UPDATE invoices SET status=:status,updated_at=NOW() WHERE id=:id'); $stmt->execute(['id'=>$id,'status'=>$status]); }

    public function paymentCount(int $id): int
    { $stmt=Database::connection()->prepare('SELECT COUNT(*) FROM payments WHERE invoice_id=:id');$stmt->execute(['id'=>$id]);return (int)$stmt->fetchColumn(); }

    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'=>'invoice','entity_id'=>$entityId,'ip'=>$_SERVER['REMOTE_ADDR']??null,'metadata'=>json_encode($metadata,JSON_THROW_ON_ERROR)]); }

    private function insertItems(PDO $pdo,int $invoiceId,array $items): void
    {
        $hasSession=$this->hasSessionField(); $hasAssessment=$this->hasAssessmentField();
        $columns='invoice_id,service_id'; $values=':invoice_id,:service_id';
        if($hasSession){$columns.=',session_id';$values.=',:session_id';}
        if($hasAssessment){$columns.=',assessment_id';$values.=',:assessment_id';}
        $columns.=',description,service_date,quantity,unit_price,line_total'; $values.=',:description,:service_date,:quantity,:unit_price,:line_total';
        $stmt=$pdo->prepare("INSERT INTO invoice_items ($columns) VALUES ($values)");
        foreach($items as $item){$params=['invoice_id'=>$invoiceId,'service_id'=>$item['service_id']??null,'description'=>$item['description'],'service_date'=>$item['service_date']??null,'quantity'=>$item['quantity'],'unit_price'=>$item['unit_price'],'line_total'=>$item['line_total']]; if($hasSession)$params['session_id']=$item['session_id']??null; if($hasAssessment)$params['assessment_id']=$item['assessment_id']??null; $stmt->execute($params);}
    }

    private function hasSessionField(): bool
    {
        static $result=null;
        if($result!==null) return $result;
        $stmt=Database::connection()->prepare("SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='invoice_items' AND column_name='session_id'");
        $stmt->execute();
        return $result=((int)$stmt->fetchColumn()===1);
    }

    private function hasAssessmentField(): bool
    {
        static $result=null; if($result!==null)return $result; $stmt=Database::connection()->prepare("SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='invoice_items' AND column_name='assessment_id'"); $stmt->execute(); return $result=((int)$stmt->fetchColumn()===1);
    }

    private function nextNumber(PDO $pdo): string
    {
        $stmt=$pdo->prepare('SELECT id,prefix,next_number,padding FROM numbering_sequences WHERE sequence_key=:key FOR UPDATE');$stmt->execute(['key'=>'invoice']);$row=$stmt->fetch();
        if(!$row){$pdo->prepare('INSERT INTO numbering_sequences(sequence_key,prefix,next_number,padding) VALUES ("invoice","INV-",1,6)')->execute();$row=['prefix'=>'INV-','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'=>'invoice']); return $number;
    }
}
