huntingclubroster/classes/ClubMemberNotes.php

<?php

/** Private notes reference a club membership, never a global user account. */
class ClubMemberNotes extends RosterService {
    public const TYPE='ClubMember';
    public const LIMIT=2000;

    public function listing(int $club,int $membership,int $page): array {
        (new TenantAccess())->requireManager($club);$this->member($club,$membership);
        return BoundedList::rows($this->connect(),'SELECT note_serial,note_text,note_origin,note_timestamp FROM notes WHERE note_type=? AND note_reference=? ORDER BY note_timestamp DESC,note_serial DESC',[self::TYPE,$membership],$page,'notes');
    }

    public function save(int $club,int $membership,int $id,string $text,string $version=''): int {
        (new TenantAccess())->requireManager($club);
        $this->requireTransactionalNotes();
        $text=trim($text);
        if(!mb_check_encoding($text,'UTF-8')||$text===''||mb_strlen($text,'UTF-8')>self::LIMIT)throw new AppException('Enter a note of up to 2,000 characters.');
        return DatabaseTransaction::run($this->connect(),function(PDO $db)use($club,$membership,$id,$text,$version){
            $this->club($club,$db,true);(new TenantAccess())->requireManager($club,$db);$this->member($club,$membership);
            if($id){
                $stmt=$db->prepare('SELECT note_text FROM notes WHERE note_serial=? AND note_type=? AND note_reference=? FOR UPDATE');$stmt->execute([$id,self::TYPE,$membership]);$old=$stmt->fetchColumn();
                if($old===false)throw new AppException('Note not found in this membership.',404);
                if(!hash_equals(hash('sha256',$old),$version))throw new AppException('This note changed since you opened it. Reload the summary before editing again.',409);
                $stmt=$db->prepare('UPDATE notes SET note_text=?,note_origin=? WHERE note_serial=? AND note_type=? AND note_reference=?');$stmt->execute([$text,$_SESSION[self::USER_NAME]??'',$id,self::TYPE,$membership]);
            }else{
                $stmt=$db->prepare('INSERT INTO notes(note_type,note_reference,note_text,note_origin) VALUES(?,?,?,?)');$stmt->execute([self::TYPE,$membership,$text,$_SESSION[self::USER_NAME]??'']);$id=(int)$db->lastInsertId();
            }
            Audit::required($db,(int)$_SESSION[self::USER_SERIAL],'saved club member note '.json_encode(['club'=>$club,'membership'=>$membership,'note'=>$id]));
            return $id;
        });
    }

    public function delete(int $club,int $membership,int $id,string $version): void {
        (new TenantAccess())->requireManager($club);$this->requireTransactionalNotes();
        DatabaseTransaction::run($this->connect(),function(PDO $db)use($club,$membership,$id,$version){
            $this->club($club,$db,true);(new TenantAccess())->requireManager($club,$db);$this->member($club,$membership);
            $stmt=$db->prepare('SELECT note_text FROM notes WHERE note_serial=? AND note_type=? AND note_reference=? FOR UPDATE');$stmt->execute([$id,self::TYPE,$membership]);$old=$stmt->fetchColumn();
            if($old===false)throw new AppException('Note not found in this membership.',404);
            if(!hash_equals(hash('sha256',$old),$version))throw new AppException('This note changed since you opened it. Reload the summary before deleting it.',409);
            $stmt=$db->prepare('DELETE FROM notes WHERE note_serial=? AND note_type=? AND note_reference=?');$stmt->execute([$id,self::TYPE,$membership]);
            Audit::required($db,(int)$_SESSION[self::USER_SERIAL],'deleted club member note '.json_encode(['club'=>$club,'membership'=>$membership,'note'=>$id]));
        });
    }

    private function requireTransactionalNotes(): void {
        $engine=$this->connect()->query("SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='notes'")->fetchColumn();
        if(strtoupper((string)$engine)!=='INNODB')throw new AppException('Administrative notes require transactional note storage. Contact support.',503);
    }
}