huntingclubroster/classes/LedgerService.php

<?php
/** Append-only financial records. Every write and its audit event commit together. */
class LedgerService extends RosterService {
    private function actor(): int {return (int)($_SESSION[self::USER_SERIAL]??0);}
    private function key(string $key): void {if(!preg_match('/\A[a-f0-9]{64}\z/',$key))throw new AppException('Invalid request key.');}
    private function reason(string $reason): string {$reason=trim($reason);if(!$reason||strlen($reason)>500)throw new AppException('Enter a correction reason, up to 500 bytes.');return $reason;}
    private function transaction(callable $work): void {
        $this->requireAdministrator();$db=$this->connect();
        DatabaseTransaction::run($db,$work);
    }
    private function lockedDue(PDO $db,int $club,int $due): array {
        $this->club($club,$db,true);
        $stmt=$db->prepare('SELECT * FROM club_dues WHERE due_serial=? AND club_serial=? FOR UPDATE');$stmt->execute([$due,$club]);$row=$stmt->fetch();
        if(!$row)throw new AppException('Dues record not found in this club.',404);
        $stmt=$db->prepare('SELECT COALESCE(SUM(amount_cents),0) FROM club_due_adjustments WHERE due_serial=?');$stmt->execute([$due]);$row['effective']=(int)$row['amount_cents']+(int)$stmt->fetchColumn();
        $stmt=$db->prepare('SELECT COALESCE(SUM(p.amount_cents),0) FROM club_payments p WHERE p.due_serial=? AND NOT EXISTS(SELECT 1 FROM club_payment_reversals r WHERE r.payment_serial=p.payment_serial)');$stmt->execute([$due]);$row['paid']=(int)$stmt->fetchColumn();return $row;
    }
    public function charge(int $club,int $membership,int $season,string $amount): void {
        $cents=self::cents($amount);
        $this->transaction(function(PDO $db)use($club,$membership,$season,$cents){
            $this->club($club,$db,true);
            $stmt=$db->prepare("SELECT membership_serial FROM club_memberships WHERE membership_serial=? AND club_serial=? AND membership_status='active' FOR UPDATE");$stmt->execute([$membership,$club]);if(!$stmt->fetch())throw new AppException('Select an active member of this club.',409);
            $stmt=$db->prepare('SELECT season_serial FROM club_seasons WHERE season_serial=? AND club_serial=? FOR UPDATE');$stmt->execute([$season,$club]);if(!$stmt->fetch())throw new AppException('Select a season belonging to this club.',409);
            $stmt=$db->prepare('INSERT INTO club_dues(club_serial,membership_serial,season_serial,amount_cents,recorded_by) VALUES(?,?,?,?,?)');$stmt->execute([$club,$membership,$season,$cents,$this->actor()]);
            Audit::required($db,$this->actor(),'created dues '.json_encode(['due'=>(int)$db->lastInsertId(),'club'=>$club,'membership'=>$membership,'season'=>$season,'amount_cents'=>$cents]));
        });
    }
    public function payment(int $club,int $due,string $amount,string $paid,string $key,string $method='unspecified',string $reference='',?int $membership=null): void {
        $cents=self::cents($amount);self::date($paid);$this->key($key);$reference=trim($reference);
        if($cents<=0||$paid>date('Y-m-d')||!in_array($method,['unspecified','cash','check','transfer','other'],true)||strlen($reference)>100)throw new AppException('Enter a positive payment, a valid date/method, and a reference up to 100 bytes.');
        $this->transaction(function(PDO $db)use($club,$due,$cents,$paid,$key,$method,$reference,$membership){
            $row=$this->lockedDue($db,$club,$due);
            if($membership!==null&&(int)$row['membership_serial']!==$membership)throw new AppException('Dues record does not belong to this membership.',404);
            $stmt=$db->prepare('SELECT * FROM club_payments WHERE request_key=?');$stmt->execute([$key]);$existing=$stmt->fetch();
            if($existing){if((int)$existing['due_serial']!==$due||(int)$existing['amount_cents']!==$cents||$existing['paid_on']!==$paid||$existing['payment_method']!==$method||$existing['payment_reference']!==$reference)throw new AppException('This request key was already used for different payment details.',409);return;}
            if($cents>$row['effective']-$row['paid'])throw new AppException('Payment exceeds the outstanding balance.',409);
            $stmt=$db->prepare('INSERT INTO club_payments(due_serial,amount_cents,paid_on,request_key,recorded_by,payment_method,payment_reference) VALUES(?,?,?,?,?,?,?)');$stmt->execute([$due,$cents,$paid,$key,$this->actor(),$method,$reference]);
            Audit::required($db,$this->actor(),'received payment '.json_encode(['payment'=>(int)$db->lastInsertId(),'club'=>$club,'due'=>$due,'amount_cents'=>$cents,'paid_on'=>$paid,'method'=>$method,'reference'=>$reference]));
        });
    }
    public function adjust(int $club,int $due,string $total,string $reason,string $key,?int $membership=null): void {
        $cents=self::cents($total);$reason=$this->reason($reason);$this->key($key);
        $this->transaction(function(PDO $db)use($club,$due,$cents,$reason,$key,$membership){
            $row=$this->lockedDue($db,$club,$due);
            if($membership!==null&&(int)$row['membership_serial']!==$membership)throw new AppException('Dues record does not belong to this membership.',404);
            $stmt=$db->prepare('SELECT * FROM club_due_adjustments WHERE request_key=?');$stmt->execute([$key]);$existing=$stmt->fetch();
            if($existing){if((int)$existing['due_serial']!==$due||(int)$existing['corrected_amount_cents']!==$cents||$existing['reason']!==$reason)throw new AppException('This request key was already used for different correction details.',409);return;}
            if($cents<$row['paid'])throw new AppException('Corrected dues cannot be below received payments. Reverse an incorrect payment first.',409);
            if($cents===$row['effective'])throw new AppException('The corrected amount must differ from the current dues.',409);
            $stmt=$db->prepare('INSERT INTO club_due_adjustments(due_serial,amount_cents,corrected_amount_cents,reason,recorded_by,request_key) VALUES(?,?,?,?,?,?)');$stmt->execute([$due,$cents-$row['effective'],$cents,$reason,$this->actor(),$key]);
            Audit::required($db,$this->actor(),'corrected dues '.json_encode(['adjustment'=>(int)$db->lastInsertId(),'club'=>$club,'due'=>$due,'before_cents'=>$row['effective'],'after_cents'=>$cents,'reason'=>$reason]));
        });
    }
    public function reverse(int $club,int $payment,string $reason,string $key,?int $membership=null): void {
        $reason=$this->reason($reason);$this->key($key);
        $this->transaction(function(PDO $db)use($club,$payment,$reason,$key,$membership){
            $this->club($club,$db,true);$stmt=$db->prepare('SELECT p.* FROM club_payments p JOIN club_dues d ON d.due_serial=p.due_serial WHERE p.payment_serial=? AND d.club_serial=?');$stmt->execute([$payment,$club]);$row=$stmt->fetch();if(!$row)throw new AppException('Payment not found in this club.',404);
            $dueRow=$this->lockedDue($db,$club,(int)$row['due_serial']);
            if($membership!==null&&(int)$dueRow['membership_serial']!==$membership)throw new AppException('Payment does not belong to this membership.',404);
            $stmt=$db->prepare('SELECT * FROM club_payment_reversals WHERE request_key=? OR payment_serial=?');$stmt->execute([$key,$payment]);$existing=$stmt->fetch();
            if($existing){if($existing['request_key']===$key&&(int)$existing['payment_serial']===$payment&&$existing['reason']===$reason)return;throw new AppException('Payment is already reversed or this request key was already used.',409);}
            $stmt=$db->prepare('INSERT INTO club_payment_reversals(payment_serial,reason,recorded_by,request_key) VALUES(?,?,?,?)');$stmt->execute([$payment,$reason,$this->actor(),$key]);
            Audit::required($db,$this->actor(),'reversed payment '.json_encode(['reversal'=>(int)$db->lastInsertId(),'club'=>$club,'payment'=>$payment,'amount_cents'=>(int)$row['amount_cents'],'reason'=>$reason]));
        });
    }
    public function history(int $club,?int $page=null,?int $adjustmentPage=null,?int $membership=null): array {
        $this->club($club);$result=[];$db=$this->connect();
        $scope=$this->manages($club)?'':' AND m.user_serial=?';$args=$this->manages($club)?[$club]:[$club,$_SESSION[self::USER_SERIAL]??0];
        if($membership!==null){$this->member($club,$membership);$scope.=' AND m.membership_serial=?';$args[]=$membership;}
        $result['payments']=BoundedList::rows($db,'SELECT p.*,m.user_serial,r.reversal_serial,r.reason AS reversal_reason,r.created_at AS reversed_at,CONCAT_WS(" ",u.user_first_name,u.user_last_name) AS member_name,a.user_name AS actor,ra.user_name AS reversal_actor FROM club_payments p JOIN club_dues d ON d.due_serial=p.due_serial JOIN club_memberships m ON m.membership_serial=d.membership_serial JOIN users u ON u.user_serial=m.user_serial LEFT JOIN club_payment_reversals r ON r.payment_serial=p.payment_serial LEFT JOIN users a ON a.user_serial=p.recorded_by LEFT JOIN users ra ON ra.user_serial=r.recorded_by WHERE d.club_serial=?'.$scope.' ORDER BY p.paid_on DESC,p.payment_serial DESC',$args,$page,'payments');
        $result['adjustments']=BoundedList::rows($db,'SELECT x.*,CONCAT_WS(" ",u.user_first_name,u.user_last_name) AS member_name,a.user_name AS actor FROM club_due_adjustments x JOIN club_dues d ON d.due_serial=x.due_serial JOIN club_memberships m ON m.membership_serial=d.membership_serial JOIN users u ON u.user_serial=m.user_serial JOIN users a ON a.user_serial=x.recorded_by WHERE d.club_serial=?'.$scope.' ORDER BY x.adjustment_serial DESC',$args,$adjustmentPage,'adjustments');return $result;
    }
}