<?php
namespace App\Models;
use App\Core\Database;

final class Treasury
{
 public static function dashboard(int $org): array {$pdo=Database::connection();$q=$pdo->prepare("SELECT r.*,u.nom holder_nom,u.prenom holder_prenom FROM cda_cash_registers r LEFT JOIN utilisateurs u ON u.id=r.holder_user_id WHERE r.organisation_id=? AND r.active=1 ORDER BY r.is_treasurer_cash DESC,r.name");$q->execute([$org]);$registers=$q->fetchAll();$q=$pdo->prepare("SELECT c.*,cl.nom,cl.prenom,i.document_number FROM cda_cheques c JOIN cda_payments p ON p.id=c.payment_id JOIN cda_invoices i ON i.id=p.invoice_id JOIN clients cl ON cl.id=i.client_id WHERE c.organisation_id=? ORDER BY c.id DESC LIMIT 200");$q->execute([$org]);$cheques=$q->fetchAll();$q=$pdo->prepare("SELECT * FROM cda_cheque_deposits WHERE organisation_id=? ORDER BY id DESC LIMIT 50");$q->execute([$org]);return ['registers'=>$registers,'cheques'=>$cheques,'deposits'=>$q->fetchAll()];}
 public static function users(int $org): array {$q=Database::connection()->prepare("SELECT id,nom,prenom,email FROM utilisateurs WHERE organisation_id=? AND actif=1 ORDER BY nom,prenom");$q->execute([$org]);return $q->fetchAll();}
 public static function clients(int $org):array {$q=Database::connection()->prepare("SELECT id,code_client,nom,prenom,email FROM clients WHERE organisation_id=? AND deleted_at IS NULL AND statut='actif' ORDER BY nom,prenom");$q->execute([$org]);return $q->fetchAll();}
 public static function advances(int $org):array {$q=Database::connection()->prepare("SELECT a.*,c.code_client,c.nom,c.prenom,r.name register_name FROM cda_client_advances a JOIN clients c ON c.id=a.client_id JOIN cda_cash_registers r ON r.id=a.register_id WHERE a.organisation_id=? ORDER BY a.id DESC LIMIT 200");$q->execute([$org]);$rows=$q->fetchAll();$invoices=Database::connection()->prepare("SELECT id,document_number,total_amount,paid_amount FROM cda_invoices WHERE organisation_id=? AND client_id=? AND document_type='invoice' AND status IN('issued','part_paid') AND total_amount>paid_amount ORDER BY issue_date,id");foreach($rows as &$row){$invoices->execute([$org,$row['client_id']]);$row['open_invoices']=$invoices->fetchAll();}unset($row);return $rows;}
 public static function createClientAdvance(int $org,int $client,int $register,float $amount,string $date,string $notes,int $user):int {if($amount<=0)throw new \RuntimeException('Le montant de l’avance doit être positif.');$pdo=Database::connection();$pdo->beginTransaction();try{$q=$pdo->prepare("SELECT c.code_client,c.nom,c.prenom,r.name register_name FROM clients c JOIN cda_cash_registers r ON r.id=? AND r.organisation_id=? AND r.active=1 WHERE c.id=? AND c.organisation_id=? AND c.deleted_at IS NULL FOR UPDATE");$q->execute([$register,$org,$client,$org]);$data=$q->fetch();if(!$data)throw new \RuntimeException('Adhérent ou caisse introuvable.');$year=(int)substr($date,0,4);$pdo->prepare("INSERT INTO cda_document_sequences(organisation_id,document_type,fiscal_year,last_number) VALUES(?,'advance_receipt',?,0) ON DUPLICATE KEY UPDATE last_number=last_number")->execute([$org,$year]);$pdo->prepare("UPDATE cda_document_sequences SET last_number=LAST_INSERT_ID(last_number+1) WHERE organisation_id=? AND document_type='advance_receipt' AND fiscal_year=?")->execute([$org,$year]);$number='REC-AV-'.$year.'-'.str_pad((string)$pdo->lastInsertId(),6,'0',STR_PAD_LEFT);$q=$pdo->prepare("INSERT INTO cda_client_advances(organisation_id,client_id,register_id,receipt_number,received_date,amount,available_amount,notes,created_by) VALUES(?,?,?,?,?,?,?,?,?)");$q->execute([$org,$client,$register,$number,$date,$amount,$amount,$notes?:null,$user]);$id=(int)$pdo->lastInsertId();$pdo->prepare("UPDATE cda_cash_registers SET current_balance=current_balance+? WHERE id=? AND organisation_id=?")->execute([$amount,$register,$org]);$pdo->prepare("INSERT INTO cda_cash_movements(organisation_id,movement_date,movement_type,target_register_id,amount,label,status,requested_by,accepted_by,accepted_at) VALUES(?,?,'client_advance',?,? ,?,'accepted',?,?,NOW())")->execute([$org,$date,$register,$amount,'Avance '.$number.' · '.$data['code_client'],$user,$user]);$s=Billing::settings($org);$label='Avance espèces '.$number.' · '.$data['nom'].' '.$data['prenom'];$e=$pdo->prepare("INSERT INTO cda_accounting_entries(invoice_id,entry_date,journal_code,account_number,label,debit,credit,fiscal_year,entry_kind) VALUES(NULL,?,?,?,?,?,?,?,?)");$e->execute([$date,$s['cash_journal']??'CA',$s['cash_account']??'531000',$label,$amount,0,$year,'client_advance']);$e->execute([$date,$s['cash_journal']??'CA',$data['code_client'],$label,0,$amount,$year,'client_advance']);$pdo->commit();return $id;}catch(\Throwable $e){$pdo->rollBack();throw $e;}}
 public static function allocateAdvance(int $org,int $advance,int $invoice,float $amount,int $user):void {if($amount<=0)throw new \RuntimeException('Montant invalide.');$pdo=Database::connection();$pdo->beginTransaction();try{$q=$pdo->prepare("SELECT * FROM cda_client_advances WHERE id=? AND organisation_id=? AND available_amount>=? AND status IN('available','part_allocated') FOR UPDATE");$q->execute([$advance,$org,$amount]);$a=$q->fetch();if(!$a)throw new \RuntimeException('Avance insuffisante ou indisponible.');$q=$pdo->prepare("SELECT * FROM cda_invoices WHERE id=? AND organisation_id=? AND client_id=? AND document_type='invoice' AND status IN('issued','part_paid') FOR UPDATE");$q->execute([$invoice,$org,$a['client_id']]);$i=$q->fetch();if(!$i)throw new \RuntimeException('La facture ne correspond pas à cet adhérent.');$balance=round((float)$i['total_amount']-(float)$i['paid_amount'],2);if($amount>$balance)throw new \RuntimeException('Le montant dépasse le solde de la facture.');$q=$pdo->prepare("INSERT INTO cda_payments(invoice_id,payment_date,amount,payment_method,reference,notes,created_by) VALUES(?,CURDATE(),?,'Avance client',?,'Imputation sans nouveau mouvement de caisse',?)");$q->execute([$invoice,$amount,$a['receipt_number'],$user]);$payment=(int)$pdo->lastInsertId();$pdo->prepare("INSERT INTO cda_client_advance_allocations(advance_id,invoice_id,payment_id,amount,allocated_by) VALUES(?,?,?,?,?)")->execute([$advance,$invoice,$payment,$amount,$user]);$pdo->prepare("UPDATE cda_client_advances SET available_amount=available_amount-?,status=CASE WHEN available_amount-?<=0 THEN 'allocated' ELSE 'part_allocated' END WHERE id=?")->execute([$amount,$amount,$advance]);$pdo->prepare("UPDATE cda_invoices SET paid_amount=(SELECT COALESCE(SUM(amount),0) FROM cda_payments WHERE invoice_id=?),status=CASE WHEN (SELECT COALESCE(SUM(amount),0) FROM cda_payments WHERE invoice_id=?)>=total_amount THEN 'paid' ELSE 'part_paid' END WHERE id=?")->execute([$invoice,$invoice,$invoice]);$pdo->commit();}catch(\Throwable $e){$pdo->rollBack();throw $e;}}
 public static function advanceReceipt(int $org,int $id):?array {$q=Database::connection()->prepare("SELECT a.*,c.code_client,c.nom,c.prenom,c.email,r.name register_name,u.nom cashier_nom,u.prenom cashier_prenom,s.legal_name,s.address_line1,s.postal_code,s.city,s.siret,s.vat_mention FROM cda_client_advances a JOIN clients c ON c.id=a.client_id JOIN cda_cash_registers r ON r.id=a.register_id LEFT JOIN utilisateurs u ON u.id=a.created_by LEFT JOIN cda_billing_settings s ON s.organisation_id=a.organisation_id WHERE a.id=? AND a.organisation_id=?");$q->execute([$id,$org]);$r=$q->fetch();return $r?:null;}
 public static function advancesByClient(int $org,int $client):array {$q=Database::connection()->prepare("SELECT a.*,r.name register_name,(SELECT COALESCE(SUM(x.amount),0) FROM cda_client_advance_allocations x WHERE x.advance_id=a.id) allocated_amount FROM cda_client_advances a JOIN cda_cash_registers r ON r.id=a.register_id WHERE a.organisation_id=? AND a.client_id=? ORDER BY a.received_date DESC,a.id DESC");$q->execute([$org,$client]);return $q->fetchAll();}
 public static function emailAdvanceReceipt(int $org,int $id):void {$r=self::advanceReceipt($org,$id);if(!$r)throw new \RuntimeException('Reçu introuvable.');$email=filter_var($r['email']??'',FILTER_VALIDATE_EMAIL);if(!$email)throw new \RuntimeException('Aucune adresse e-mail valide pour cet adhérent.');$subject='Votre reçu d’avance '.$r['receipt_number'].' - Carrefour des Arts';$body='<h1>Reçu d’avance '.htmlspecialchars($r['receipt_number']).'</h1><p>Bonjour '.htmlspecialchars($r['prenom'].' '.$r['nom']).',</p><p>Nous confirmons la réception de <strong>'.number_format((float)$r['amount'],2,',',' ').' €</strong> en espèces le '.htmlspecialchars($r['received_date']).'.</p><p>Cette avance reste affectée à votre compte adhérent '.htmlspecialchars($r['code_client']).' jusqu’à son utilisation sur une facture.</p><p>Carrefour des Arts · 96, rue Auguste Coolen · 59123 Bray-Dunes</p>';$headers="MIME-Version: 1.0\r\nContent-Type: text/html; charset=UTF-8\r\n";$ok=@mail($email,$subject,$body,$headers);Database::connection()->prepare("UPDATE cda_client_advances SET last_emailed_at=NOW(),email_status=? WHERE id=? AND organisation_id=?")->execute([$ok?'sent':'failed',$id,$org]);if(!$ok)throw new \RuntimeException('Le serveur de messagerie a refusé l’envoi.');}
 public static function refundAdvance(int $org,int $id,string $reason,int $user):void {$pdo=Database::connection();$pdo->beginTransaction();try{$q=$pdo->prepare("SELECT a.*,c.code_client,c.nom,c.prenom,r.current_balance,r.name register_name FROM cda_client_advances a JOIN clients c ON c.id=a.client_id JOIN cda_cash_registers r ON r.id=a.register_id WHERE a.id=? AND a.organisation_id=? AND a.available_amount>0 AND a.status IN('available','part_allocated') FOR UPDATE");$q->execute([$id,$org]);$a=$q->fetch();if(!$a)throw new \RuntimeException('Aucun reliquat remboursable.');$amount=(float)$a['available_amount'];if((float)$a['current_balance']<$amount)throw new \RuntimeException('Solde de caisse insuffisant pour rembourser le reliquat.');$pdo->prepare("UPDATE cda_cash_registers SET current_balance=current_balance-? WHERE id=?")->execute([$amount,$a['register_id']]);$pdo->prepare("INSERT INTO cda_cash_movements(organisation_id,movement_date,movement_type,source_register_id,amount,label,status,requested_by,accepted_by,accepted_at) VALUES(?,CURDATE(),'advance_refund',?,?,?,'accepted',?,?,NOW())")->execute([$org,$a['register_id'],$amount,'Remboursement '.$a['receipt_number'].' · '.$a['code_client'],$user,$user]);$s=Billing::settings($org);$label='Remboursement avance '.$a['receipt_number'].' · '.$a['nom'].' '.$a['prenom'];$e=$pdo->prepare("INSERT INTO cda_accounting_entries(invoice_id,entry_date,journal_code,account_number,label,debit,credit,fiscal_year,entry_kind) VALUES(NULL,CURDATE(),?,?,?,?,?,?,?)");$e->execute([$s['cash_journal']??'CA',$a['code_client'],$label,$amount,0,(int)date('Y'),'advance_refund']);$e->execute([$s['cash_journal']??'CA',$s['cash_account']??'531000',$label,0,$amount,(int)date('Y'),'advance_refund']);$pdo->prepare("UPDATE cda_client_advances SET available_amount=0,status='refunded',refunded_amount=COALESCE(refunded_amount,0)+?,refunded_at=NOW(),refund_reason=? WHERE id=?")->execute([$amount,$reason?:null,$id]);$pdo->commit();}catch(\Throwable $e){$pdo->rollBack();throw $e;}}
 public static function cashJournal(int $org,string $from,string $to,int $register=0):array {$sql="SELECT m.*,COALESCE(t.name,s.name) register_name,COALESCE(t.id,s.id) view_register_id,CONCAT(c.code_client,' · ',c.nom,' ',c.prenom) client_label FROM cda_cash_movements m LEFT JOIN cda_cash_registers s ON s.id=m.source_register_id LEFT JOIN cda_cash_registers t ON t.id=m.target_register_id LEFT JOIN cda_client_advances a ON m.label LIKE CONCAT('%',a.receipt_number,'%') AND a.organisation_id=m.organisation_id LEFT JOIN clients c ON c.id=a.client_id WHERE m.organisation_id=? AND m.movement_date BETWEEN ? AND ?".($register?" AND (m.source_register_id=? OR m.target_register_id=?)":"")." ORDER BY m.movement_date,m.id";$q=Database::connection()->prepare($sql);$args=[$org,$from,$to];if($register){$args[]=$register;$args[]=$register;}$q->execute($args);return $q->fetchAll();}
 public static function accountingEntries(int $org,string $from,string $to,string $journal=''):array {$sql="SELECT e.* FROM cda_accounting_entries e LEFT JOIN cda_invoices i ON i.id=e.invoice_id WHERE (i.organisation_id=? OR (e.invoice_id IS NULL AND EXISTS(SELECT 1 FROM cda_billing_settings s WHERE s.organisation_id=?))) AND e.entry_date BETWEEN ? AND ?".($journal!==''?" AND e.journal_code=?":"")." ORDER BY e.entry_date,e.journal_code,e.id";$q=Database::connection()->prepare($sql);$args=[$org,$org,$from,$to];if($journal!=='')$args[]=$journal;$q->execute($args);return $q->fetchAll();}
 public static function createRegister(int $org,array $v,int $user): void {$opening=!empty($v['is_treasurer_cash'])?round((float)$v['opening_balance'],2):0;$pdo=Database::connection();$pdo->beginTransaction();try{$q=$pdo->prepare("INSERT INTO cda_cash_registers(organisation_id,code,name,holder_user_id,is_treasurer_cash,opening_balance,current_balance) VALUES(?,?,?,?,?,?,?)");$q->execute([$org,strtoupper(trim($v['code'])),$v['name'],(int)$v['holder_user_id']?:null,!empty($v['is_treasurer_cash'])?1:0,$opening,$opening]);$id=(int)$pdo->lastInsertId();if($opening!=0)$pdo->prepare("INSERT INTO cda_cash_movements(organisation_id,movement_date,movement_type,target_register_id,amount,label,status,requested_by,accepted_by,accepted_at) VALUES(?,CURDATE(),'opening',?,?,'Solde initial','accepted',?,?,NOW())")->execute([$org,$id,$opening,$user,$user]);$pdo->commit();}catch(\Throwable $e){$pdo->rollBack();throw $e;}}
 public static function requestTransfer(int $org,int $source,int $target,float $amount,int $user): void {if($amount<=0)throw new \RuntimeException('Montant invalide.');Database::connection()->prepare("INSERT INTO cda_cash_movements(organisation_id,movement_date,movement_type,source_register_id,target_register_id,amount,label,status,requested_by) SELECT ?,CURDATE(),'transfer',s.id,t.id,?,'Remise à la trésorière','pending',? FROM cda_cash_registers s JOIN cda_cash_registers t ON t.id=? AND t.organisation_id=? WHERE s.id=? AND s.organisation_id=? AND s.current_balance>=?")->execute([$org,$amount,$user,$target,$org,$source,$org,$amount]);}
 public static function acceptTransfer(int $org,int $id,int $user): void {$pdo=Database::connection();$pdo->beginTransaction();try{$q=$pdo->prepare("SELECT m.* FROM cda_cash_movements m JOIN cda_cash_registers t ON t.id=m.target_register_id WHERE m.id=? AND m.organisation_id=? AND m.status='pending' AND t.is_treasurer_cash=1 FOR UPDATE");$q->execute([$id,$org]);$m=$q->fetch();if(!$m)throw new \RuntimeException('Remise introuvable.');$pdo->prepare("UPDATE cda_cash_registers SET current_balance=current_balance-? WHERE id=? AND current_balance>=?")->execute([$m['amount'],$m['source_register_id'],$m['amount']]);if(!$pdo->query("SELECT ROW_COUNT()")->fetchColumn())throw new \RuntimeException('Solde de caisse insuffisant.');$pdo->prepare("UPDATE cda_cash_registers SET current_balance=current_balance+? WHERE id=?")->execute([$m['amount'],$m['target_register_id']]);$pdo->prepare("UPDATE cda_cash_movements SET status='accepted',accepted_by=?,accepted_at=NOW() WHERE id=?")->execute([$user,$id]);$pdo->commit();}catch(\Throwable $e){$pdo->rollBack();throw $e;}}
 public static function countCash(int $org,int $register,string $month,array $qty,int $user): void {$denoms=[500,200,100,50,20,10,5,2,1,.5,.2,.1,.05,.02,.01];$pdo=Database::connection();$pdo->beginTransaction();try{$q=$pdo->prepare("SELECT current_balance FROM cda_cash_registers WHERE id=? AND organisation_id=?");$q->execute([$register,$org]);$theoretical=(float)$q->fetchColumn();$counted=0;foreach($denoms as $d)$counted+=round($d*(int)($qty[(string)$d]??0),2);$pdo->prepare("INSERT INTO cda_cash_counts(organisation_id,register_id,count_date,period_month,theoretical_amount,counted_amount,difference_amount,status,counted_by) VALUES(?,?,CURDATE(),?,?,?,?, 'draft',?) ON DUPLICATE KEY UPDATE count_date=CURDATE(),theoretical_amount=VALUES(theoretical_amount),counted_amount=VALUES(counted_amount),difference_amount=VALUES(difference_amount),counted_by=VALUES(counted_by)")->execute([$org,$register,$month,$theoretical,$counted,round($counted-$theoretical,2),$user]);$id=(int)$pdo->lastInsertId();if(!$id){$q=$pdo->prepare("SELECT id FROM cda_cash_counts WHERE register_id=? AND period_month=?");$q->execute([$register,$month]);$id=(int)$q->fetchColumn();}$pdo->prepare("DELETE FROM cda_cash_count_denominations WHERE cash_count_id=?")->execute([$id]);$ins=$pdo->prepare("INSERT INTO cda_cash_count_denominations(cash_count_id,denomination,quantity,total_amount) VALUES(?,?,?,?)");foreach($denoms as $d){$n=(int)($qty[(string)$d]??0);$ins->execute([$id,$d,$n,round($d*$n,2)]);}$pdo->commit();}catch(\Throwable $e){$pdo->rollBack();throw $e;}}
 public static function recentCounts(int $org):array {$q=Database::connection()->prepare("SELECT c.*,r.name register_name,u.nom validator_nom,u.prenom validator_prenom FROM cda_cash_counts c JOIN cda_cash_registers r ON r.id=c.register_id LEFT JOIN utilisateurs u ON u.id=c.validated_by WHERE c.organisation_id=? ORDER BY c.period_month DESC,c.id DESC LIMIT 24");$q->execute([$org]);return $q->fetchAll();}
 public static function validateCount(int $org,int $id,int $user):void {$q=Database::connection()->prepare("UPDATE cda_cash_counts SET status='validated',validated_by=?,validated_at=NOW() WHERE id=? AND organisation_id=? AND status='draft'");$q->execute([$user,$id,$org]);if(!$q->rowCount())throw new \RuntimeException('Contrôle introuvable ou déjà validé.');}
 public static function createDeposit(int $org,array $ids,int $user): int {$ids=array_values(array_unique(array_filter(array_map('intval',$ids))));if(!$ids)throw new \RuntimeException('Sélectionnez au moins un chèque.');$pdo=Database::connection();$pdo->beginTransaction();try{$n='REM-'.date('Y').'-'.str_pad((string)((int)$pdo->query("SELECT COUNT(*)+1 FROM cda_cheque_deposits WHERE organisation_id=".$org." AND YEAR(deposit_date)=YEAR(CURDATE())")->fetchColumn()),5,'0',STR_PAD_LEFT);$pdo->prepare("INSERT INTO cda_cheque_deposits(organisation_id,deposit_number,deposit_date,created_by) VALUES(?,?,CURDATE(),?)")->execute([$org,$n,$user]);$deposit=(int)$pdo->lastInsertId();$ins=$pdo->prepare("INSERT INTO cda_cheque_deposit_items(deposit_id,cheque_id) SELECT ?,id FROM cda_cheques WHERE id=? AND organisation_id=? AND status IN('received','pending_deposit')");foreach($ids as $id)$ins->execute([$deposit,$id,$org]);$pdo->prepare("UPDATE cda_cheque_deposits SET total_amount=(SELECT COALESCE(SUM(c.amount),0) FROM cda_cheque_deposit_items di JOIN cda_cheques c ON c.id=di.cheque_id WHERE di.deposit_id=?) WHERE id=?")->execute([$deposit,$deposit]);$pdo->prepare("UPDATE cda_cheques c JOIN cda_cheque_deposit_items di ON di.cheque_id=c.id SET c.status='pending_deposit' WHERE di.deposit_id=?")->execute([$deposit]);$pdo->commit();return $deposit;}catch(\Throwable $e){$pdo->rollBack();throw $e;}}
 public static function deposit(int $org,int $id,int $user): void {$pdo=Database::connection();$pdo->beginTransaction();try{$q=$pdo->prepare("SELECT * FROM cda_cheque_deposits WHERE id=? AND organisation_id=? AND status='draft' FOR UPDATE");$q->execute([$id,$org]);$d=$q->fetch();if(!$d)throw new \RuntimeException('Bordereau introuvable.');$s=Billing::settings($org);$pdo->prepare("UPDATE cda_cheque_deposits SET status='deposited',deposited_by=?,deposited_at=NOW() WHERE id=?")->execute([$user,$id]);$pdo->prepare("UPDATE cda_cheques c JOIN cda_cheque_deposit_items di ON di.cheque_id=c.id SET c.status='deposited' WHERE di.deposit_id=?")->execute([$id]);$q=$pdo->prepare("SELECT p.invoice_id,SUM(c.amount) amount FROM cda_cheque_deposit_items di JOIN cda_cheques c ON c.id=di.cheque_id JOIN cda_payments p ON p.id=c.payment_id WHERE di.deposit_id=? GROUP BY p.invoice_id");$q->execute([$id]);$e=$pdo->prepare("INSERT INTO cda_accounting_entries(invoice_id,entry_date,journal_code,account_number,label,debit,credit,fiscal_year,entry_kind) VALUES(?,?,?,?,?,?,?,?,?)");foreach($q->fetchAll() as $r){$label='Remise chèques '.$d['deposit_number'];$e->execute([$r['invoice_id'],date('Y-m-d'),$s['bank_journal']??'CM',$s['bank_account']??'512000',$label,$r['amount'],0,(int)date('Y'),'cheque_deposit']);$e->execute([$r['invoice_id'],date('Y-m-d'),$s['bank_journal']??'CM',$s['cheque_account']??'511200',$label,0,$r['amount'],(int)date('Y'),'cheque_deposit']);}$pdo->commit();}catch(\Throwable $e){$pdo->rollBack();throw $e;}}
 public static function cashDeposit(int $org,int $register,float $amount,string $date,int $user):void {$pdo=Database::connection();$pdo->beginTransaction();try{$q=$pdo->prepare("SELECT * FROM cda_cash_registers WHERE id=? AND organisation_id=? AND is_treasurer_cash=1 AND current_balance>=? FOR UPDATE");$q->execute([$register,$org,$amount]);$r=$q->fetch();if(!$r||$amount<=0)throw new \RuntimeException('Caisse trésorière ou montant invalide.');$s=Billing::settings($org);$pdo->prepare("UPDATE cda_cash_registers SET current_balance=current_balance-? WHERE id=?")->execute([$amount,$register]);$pdo->prepare("INSERT INTO cda_cash_movements(organisation_id,movement_date,movement_type,source_register_id,amount,label,status,requested_by,accepted_by,accepted_at) VALUES(?,?,'bank_deposit',?,?,'Dépôt espèces en banque','accepted',?,?,NOW())")->execute([$org,$date,$register,$amount,$user,$user]);$e=$pdo->prepare("INSERT INTO cda_accounting_entries(invoice_id,entry_date,journal_code,account_number,label,debit,credit,fiscal_year,entry_kind) VALUES(NULL,?,?,?,?,?,?,?,?)");$e->execute([$date,$s['bank_journal']??'CM',$s['bank_account']??'512000','Dépôt espèces '.$r['name'],$amount,0,(int)substr($date,0,4),'cash_bank_deposit']);$e->execute([$date,$s['bank_journal']??'CM',$s['cash_account']??'531000','Dépôt espèces '.$r['name'],0,$amount,(int)substr($date,0,4),'cash_bank_deposit']);$pdo->commit();}catch(\Throwable $e){$pdo->rollBack();throw $e;}}
 public static function pendingTransfers(int $org):array {$q=Database::connection()->prepare("SELECT m.*,s.name source_name,t.name target_name FROM cda_cash_movements m JOIN cda_cash_registers s ON s.id=m.source_register_id JOIN cda_cash_registers t ON t.id=m.target_register_id WHERE m.organisation_id=? AND m.movement_type='transfer' AND m.status='pending' ORDER BY m.id");$q->execute([$org]);return $q->fetchAll();}
}
