<?php
namespace App\Models;

use App\Core\Database;
use PDO;

final class Billing
{
    private static array $columnCache=[];
    private static function hasColumn(PDO $pdo,string $table,string $column):bool
    {
        $key=$table.'.'.$column;
        if(array_key_exists($key,self::$columnCache))return self::$columnCache[$key];
        $q=$pdo->prepare("SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=? AND COLUMN_NAME=?");
        $q->execute([$table,$column]);
        return self::$columnCache[$key]=(int)$q->fetchColumn()>0;
    }
    public static function dashboard(int $org): array
    {
        $pdo=Database::connection();
        $q=$pdo->prepare("SELECT i.*,c.nom,c.prenom,c.email FROM cda_invoices i JOIN clients c ON c.id=i.client_id WHERE i.organisation_id=? AND i.deleted_at IS NULL ORDER BY i.id DESC LIMIT 200");
        $q->execute([$org]);
        return $q->fetchAll();
    }
    public static function preparedItems(int $org): array
    {
        $q=Database::connection()->prepare("SELECT f.*,c.code_client,c.nom,c.prenom,a.label assignment_label,a.revenue_account,a.pole_code assignment_pole,y.libelle school_year FROM cda_facturation_a_preparer f JOIN clients c ON c.id=f.client_id LEFT JOIN cda_accounting_assignments a ON a.id=f.assignment_id LEFT JOIN annees_scolaires y ON y.id=f.annee_scolaire_id WHERE f.organisation_id=? AND f.statut='a_facturer' AND f.deleted_at IS NULL AND f.invoice_id IS NULL ORDER BY c.nom,c.prenom,f.id");
        $q->execute([$org]);return $q->fetchAll();
    }
    public static function syncAnnualMemberships(int $org,int $user): int
    {
        $pdo=Database::connection();
        $before=$pdo->prepare("SELECT COUNT(*) FROM cda_facturation_a_preparer WHERE organisation_id=? AND type_element='adhesion_annuelle' AND deleted_at IS NULL");
        $before->execute([$org]);$countBefore=(int)$before->fetchColumn();
        $sql="INSERT INTO cda_facturation_a_preparer(
                organisation_id,client_id,dossier_annuel_id,annee_scolaire_id,type_element,
                libelle,montant_ttc,statut,source_module,created_by,updated_by,source_key,
                assignment_id,pole_code,service_start,service_end,gross_amount,discount_amount,net_amount
              )
              SELECT d.organisation_id,d.client_id,d.id,d.annee_scolaire_id,'adhesion_annuelle',
                CONCAT('Adhésion ',y.libelle),
                COALESCE((SELECT pc.montant FROM cda_prestation_catalog pc
                          WHERE pc.organisation_id=d.organisation_id AND pc.categorie='adhesion'
                            AND pc.actif=1 AND (pc.annee_scolaire_id=d.annee_scolaire_id OR pc.annee_scolaire_id IS NULL)
                          ORDER BY (pc.annee_scolaire_id=d.annee_scolaire_id) DESC,pc.id DESC LIMIT 1),5.00),
                'a_facturer','adherents',?,?,CONCAT('adhesion_annuelle:',d.id),
                (SELECT aa.id FROM cda_accounting_assignments aa
                 WHERE aa.organisation_id=d.organisation_id AND aa.active=1
                   AND (UPPER(aa.pole_code) IN('STR','STRUCTURE') OR aa.revenue_account='706010')
                 ORDER BY aa.is_default DESC,aa.id LIMIT 1),
                'STR',y.date_debut,y.date_fin,
                COALESCE((SELECT pc.montant FROM cda_prestation_catalog pc
                          WHERE pc.organisation_id=d.organisation_id AND pc.categorie='adhesion'
                            AND pc.actif=1 AND (pc.annee_scolaire_id=d.annee_scolaire_id OR pc.annee_scolaire_id IS NULL)
                          ORDER BY (pc.annee_scolaire_id=d.annee_scolaire_id) DESC,pc.id DESC LIMIT 1),5.00),
                0,
                COALESCE((SELECT pc.montant FROM cda_prestation_catalog pc
                          WHERE pc.organisation_id=d.organisation_id AND pc.categorie='adhesion'
                            AND pc.actif=1 AND (pc.annee_scolaire_id=d.annee_scolaire_id OR pc.annee_scolaire_id IS NULL)
                          ORDER BY (pc.annee_scolaire_id=d.annee_scolaire_id) DESC,pc.id DESC LIMIT 1),5.00)
              FROM cda_dossiers_adherents_annuels d
              INNER JOIN annees_scolaires y ON y.id=d.annee_scolaire_id
              WHERE d.organisation_id=? AND d.statut='actif' AND d.deleted_at IS NULL
                AND NOT EXISTS(
                    SELECT 1 FROM cda_facturation_a_preparer f
                    WHERE f.dossier_annuel_id=d.id AND f.type_element='adhesion_annuelle'
                      AND f.deleted_at IS NULL
                )";
        $insert=$pdo->prepare($sql);$insert->execute([$user?:null,$user?:null,$org]);
        $repair=$pdo->prepare(
            "UPDATE cda_facturation_a_preparer f
             INNER JOIN cda_dossiers_adherents_annuels d ON d.id=f.dossier_annuel_id
             INNER JOIN annees_scolaires y ON y.id=d.annee_scolaire_id
             SET f.statut=IF(f.invoice_id IS NULL,'a_facturer',f.statut),
                 f.source_key=CONCAT('adhesion_annuelle:',d.id),
                 f.pole_code=COALESCE(NULLIF(f.pole_code,''),'STR'),
                 f.service_start=COALESCE(f.service_start,y.date_debut),
                 f.service_end=COALESCE(f.service_end,y.date_fin),
                 f.gross_amount=COALESCE(NULLIF(f.gross_amount,0),f.montant_ttc),
                 f.net_amount=COALESCE(NULLIF(f.net_amount,0),f.montant_ttc),
                 f.deleted_at=NULL,f.updated_by=?,f.updated_at=NOW()
             WHERE f.organisation_id=? AND f.type_element='adhesion_annuelle'
               AND d.statut='actif' AND d.deleted_at IS NULL"
        );
        $repair->execute([$user?:null,$org]);
        $after=$pdo->prepare("SELECT COUNT(*) FROM cda_facturation_a_preparer WHERE organisation_id=? AND type_element='adhesion_annuelle' AND deleted_at IS NULL");
        $after->execute([$org]);
        return max(0,(int)$after->fetchColumn()-$countBefore);
    }
    public static function syncPrepared(int $org,int $user): int
    {
        $pdo=Database::connection();$before=(int)$pdo->query("SELECT COUNT(*) FROM cda_facturation_a_preparer WHERE organisation_id=".$org." AND deleted_at IS NULL")->fetchColumn();
        $sql="INSERT INTO cda_facturation_a_preparer(organisation_id,client_id,dossier_annuel_id,annee_scolaire_id,type_element,libelle,montant_ttc,statut,source_module,created_by,updated_by,source_key,assignment_id,pole_code,service_start,service_end,gross_amount,discount_amount,net_amount)
              SELECT ic.organisation_id,f.client_id,d.id,y.id,'inscription_cours',CONCAT(a.libelle,' — ',mf.prenom,' ',mf.nom),COALESCE(tl.montant,a.tarif_base,0),'a_facturer','inscriptions',?,?,CONCAT('inscription_cours:',ic.id),aa.id,COALESCE(p.code,''),ic.date_debut,COALESCE(ic.date_fin,y.date_fin),COALESCE(tl.montant,a.tarif_base,0),0,COALESCE(tl.montant,a.tarif_base,0)
              FROM inscriptions_cours ic JOIN membres_famille mf ON ic.participant_source='membres_famille' AND mf.id=ic.participant_id AND mf.deleted_at IS NULL JOIN familles f ON f.id=mf.famille_id AND f.deleted_at IS NULL JOIN annees_scolaires y ON y.organisation_id=ic.organisation_id AND y.active=1 JOIN cda_dossiers_adherents_annuels d ON d.client_id=f.client_id AND d.annee_scolaire_id=y.id AND d.deleted_at IS NULL JOIN ateliers a ON a.id=ic.atelier_id LEFT JOIN disciplines di ON di.id=a.discipline_id LEFT JOIN poles p ON p.id=di.pole_id LEFT JOIN grille_tarifaire_lignes tl ON tl.id=(SELECT l.id FROM grille_tarifaire_lignes l JOIN grilles_tarifaires g ON g.id=l.grille_id WHERE g.organisation_id=ic.organisation_id AND g.annee_scolaire_id=y.id AND g.statut='active' AND l.actif=1 AND (l.atelier_id=a.id OR l.atelier_id IS NULL) ORDER BY (l.atelier_id=a.id) DESC,l.priorite,l.id LIMIT 1) LEFT JOIN cda_accounting_assignments aa ON aa.id=(SELECT ca.id FROM cda_accounting_assignments ca WHERE ca.organisation_id=ic.organisation_id AND ca.active=1 AND (UPPER(ca.pole_code)=UPPER(p.code) OR ca.is_default=1) ORDER BY (UPPER(ca.pole_code)=UPPER(p.code)) DESC,ca.is_default DESC,ca.id LIMIT 1)
              WHERE ic.organisation_id=? AND ic.statut='validee' AND NOT EXISTS(SELECT 1 FROM cda_facturation_a_preparer x WHERE x.organisation_id=ic.organisation_id AND x.source_key=CONCAT('inscription_cours:',ic.id) AND x.deleted_at IS NULL)";
        $q=$pdo->prepare($sql);$q->execute([$user,$user,$org]);self::applyAutomaticMultiActivityDiscounts($pdo,$org);$after=(int)$pdo->query("SELECT COUNT(*) FROM cda_facturation_a_preparer WHERE organisation_id=".$org." AND deleted_at IS NULL")->fetchColumn();return max(0,$after-$before);
    }
    private static function applyAutomaticMultiActivityDiscounts(PDO $pdo,int $org): void
    {
        $rule=$pdo->prepare("SELECT r.* FROM regles_reduction r JOIN annees_scolaires y ON y.id=r.annee_scolaire_id AND y.active=1 WHERE r.organisation_id=? AND r.actif=1 AND r.condition_type='multi_activites' AND CURDATE() BETWEEN r.date_debut AND COALESCE(r.date_fin,'2999-12-31') ORDER BY r.priorite,r.id LIMIT 1");$rule->execute([$org]);$r=$rule->fetch();if(!$r)return;
        $clients=$pdo->prepare("SELECT client_id,COUNT(*) qty,SUM(gross_amount) total FROM cda_facturation_a_preparer WHERE organisation_id=? AND statut='a_facturer' AND invoice_id IS NULL AND deleted_at IS NULL AND type_element='inscription_cours' GROUP BY client_id HAVING COUNT(*)>=2 AND SUM(gross_amount)>0");$clients->execute([$org]);$lines=$pdo->prepare("SELECT id,gross_amount FROM cda_facturation_a_preparer WHERE organisation_id=? AND client_id=? AND statut='a_facturer' AND invoice_id IS NULL AND deleted_at IS NULL AND type_element='inscription_cours' ORDER BY id");$update=$pdo->prepare("UPDATE cda_facturation_a_preparer SET discount_amount=?,net_amount=gross_amount-?,montant_ttc=gross_amount-?,auto_discount_rule_id=? WHERE id=?");
        foreach($clients->fetchAll() as $client){$total=(float)$client['total'];$discount=$r['type_reduction']==='pourcentage'?round($total*(float)$r['valeur']/100,2):min($total,(float)$r['valeur']);$lines->execute([$org,$client['client_id']]);$rows=$lines->fetchAll();$used=0.0;foreach($rows as $idx=>$line){$share=$idx===count($rows)-1?round($discount-$used,2):round($discount*((float)$line['gross_amount']/$total),2);$share=min((float)$line['gross_amount'],max(0,$share));$used+=$share;$update->execute([$share,$share,$share,$r['id'],$line['id']]);}}
    }
    public static function generatePrepared(int $org,array $ids,array $values,int $user): array
    {
        $pdo=Database::connection();$pdo->beginTransaction();try{$marks=implode(',',array_fill(0,count($ids),'?'));$q=$pdo->prepare("SELECT f.*,c.code_client FROM cda_facturation_a_preparer f JOIN clients c ON c.id=f.client_id WHERE f.id IN ($marks) AND f.organisation_id=? AND f.statut='a_facturer' AND f.invoice_id IS NULL AND f.deleted_at IS NULL FOR UPDATE");$q->execute([...$ids,$org]);$rows=$q->fetchAll();if(count($rows)!==count($ids))throw new \RuntimeException('Une ligne a déjà été facturée ou n’est plus disponible.');$groups=[];foreach($rows as $row){$id=(int)$row['id'];$gross=round((float)($values['gross_amount'][$id]??$row['gross_amount']),2);$discount=round((float)($values['discount_amount'][$id]??$row['discount_amount']),2);$assignment=(int)($values['assignment_id'][$id]??$row['assignment_id']);$aq=$pdo->prepare("SELECT * FROM cda_accounting_assignments WHERE id=? AND organisation_id=? AND active=1");$aq->execute([$assignment,$org]);$a=$aq->fetch();if(!$a)throw new \RuntimeException('Affectation comptable manquante pour « '.$row['libelle'].' ».');if($gross<=0)throw new \RuntimeException('Montant à compléter pour « '.$row['libelle'].' ».');$discount=max(0,min($gross,$discount));$row['prepared_gross']=$gross;$row['prepared_discount']=$discount;$row['prepared_net']=round($gross-$discount,2);$row['prepared_assignment']=$a;$groups[$row['client_id']][]=$row;}$created=[];foreach($groups as $client=>$items){$account=$items[0]['code_client'];$total=array_sum(array_column($items,'prepared_net'));
            $draft=$pdo->prepare("SELECT id FROM cda_invoices WHERE organisation_id=? AND client_id=? AND document_type='invoice' AND status='draft' AND deleted_at IS NULL ORDER BY id LIMIT 1 FOR UPDATE");$draft->execute([$org,$client]);$invoice=(int)$draft->fetchColumn();
            if($invoice){$pdo->prepare("UPDATE cda_invoices SET total_amount=total_amount+?,updated_by=? WHERE id=?")->execute([$total,$user?:null,$invoice]);}
            else{$pdo->prepare("INSERT INTO cda_invoices(public_token,organisation_id,client_id,client_account,document_type,total_amount,created_by) VALUES(?,?,?,?,'invoice',?,?)")->execute([bin2hex(random_bytes(32)),$org,$client,$account,$total,$user]);$invoice=(int)$pdo->lastInsertId();}
            $created[]=$invoice;foreach($items as $item){$a=$item['prepared_assignment'];$start=$values['service_start'][$item['id']]??$item['service_start'];$end=$values['service_end'][$item['id']]??$item['service_end'];$mode=$end&&substr((string)$end,0,4)>substr((string)$start,0,4)?'period':'current_year';$pdo->prepare("INSERT INTO cda_invoice_lines(invoice_id,description,assignment_id,pole_code,project_code,revenue_account,service_start,service_end,allocation_mode,quantity,unit_price,gross_amount,discount_amount,net_amount,source_line_id) VALUES(?,?,?,?,?,?,?,?,?,1,?,?,?,?,?)")->execute([$invoice,$item['libelle'],$a['id'],$a['pole_code']?:$item['pole_code'],$a['project_code'],$a['revenue_account'],$start?:null,$end?:null,$mode,$item['prepared_gross'],$item['prepared_gross'],$item['prepared_discount'],$item['prepared_net'],$item['id']]);self::allocate($pdo,(int)$pdo->lastInsertId(),$item['prepared_net'],$start?:null,$end?:null,$mode);$pdo->prepare("UPDATE cda_facturation_a_preparer SET montant_ttc=?,gross_amount=?,discount_amount=?,net_amount=?,assignment_id=?,service_start=?,service_end=?,statut='facturee',invoice_id=?,updated_by=?,updated_at=NOW() WHERE id=?")->execute([$item['prepared_net'],$item['prepared_gross'],$item['prepared_discount'],$item['prepared_net'],$a['id'],$start?:null,$end?:null,$invoice,$user,$item['id']]);}$pdo->prepare("INSERT INTO cda_adherent_timeline(organisation_id,client_id,dossier_annuel_id,event_code,title,description,created_by) SELECT ?,?,d.id,'billing.prepared','Brouillon complété','Les inscriptions ont été regroupées dans le brouillon unique de l’adhérent.',? FROM cda_dossiers_adherents_annuels d WHERE d.client_id=? AND d.deleted_at IS NULL ORDER BY d.id DESC LIMIT 1")->execute([$org,$client,$user,$client]);}$pdo->commit();return array_values(array_unique($created));}catch(\Throwable $e){$pdo->rollBack();throw $e;}
    }

    public static function cancelPreparedItem(int $org,int $id,int $user): void
    {
        $pdo=Database::connection();$releasedSlotId=0;$pdo->beginTransaction();
        try {
            $q=$pdo->prepare("SELECT * FROM cda_facturation_a_preparer WHERE id=? AND organisation_id=? AND statut='a_facturer' AND invoice_id IS NULL AND deleted_at IS NULL FOR UPDATE");
            $q->execute([$id,$org]);$item=$q->fetch();if(!$item)throw new \RuntimeException('Cette ligne n’est plus disponible.');
            $source=(string)($item['source_key']??'');
            if(preg_match('/^(inscription_cours|pret_instrument):(\d+)$/',$source,$m)){
                $enrollmentId=(int)$m[2];
                $requestId=0;
                if(self::hasColumn($pdo,'cda_slot_enrollments','inscription_id')){
                    $request=$pdo->prepare("SELECT demande_inscription_id,slot_id FROM cda_slot_enrollments WHERE organisation_id=? AND inscription_id=? ORDER BY id DESC LIMIT 1");
                    $request->execute([$org,$enrollmentId]);$link=$request->fetch();$requestId=(int)($link['demande_inscription_id']??0);$releasedSlotId=(int)($link['slot_id']??0);
                }
                if(!$requestId){$request=$pdo->prepare("SELECT d.id FROM inscriptions_cours i INNER JOIN demandes_inscription d ON d.organisation_id=i.organisation_id AND d.membre_id=i.participant_id AND d.atelier_id=i.atelier_id AND d.statut='validee' AND d.deleted_at IS NULL WHERE i.id=? AND i.organisation_id=? ORDER BY d.validation_direction_at DESC,d.id DESC LIMIT 1");$request->execute([$enrollmentId,$org]);$requestId=(int)$request->fetchColumn();}
                if($requestId){
                    $pdo->prepare("UPDATE demandes_inscription SET deleted_at=COALESCE(deleted_at,NOW()),updated_at=NOW() WHERE id=? AND organisation_id=?")->execute([$requestId,$org]);
                    $pdo->prepare("UPDATE cda_slot_enrollments SET deleted_at=COALESCE(deleted_at,NOW()) WHERE organisation_id=? AND demande_inscription_id=?")->execute([$org,$requestId]);
                }
                $pdo->prepare("UPDATE inscriptions_cours SET deleted_at=COALESCE(deleted_at,NOW()) WHERE id=? AND organisation_id=?")->execute([$enrollmentId,$org]);
                if(self::hasColumn($pdo,'groupe_participants','inscription_id')){
                    $pdo->prepare("UPDATE groupe_participants SET deleted_at=COALESCE(deleted_at,NOW()) WHERE inscription_id=?")->execute([$enrollmentId]);
                }
                $pdo->prepare("UPDATE cda_facturation_a_preparer SET deleted_at=NOW(),updated_by=?,updated_at=NOW() WHERE organisation_id=? AND invoice_id IS NULL AND source_key IN(?,?)")->execute([$user?:null,$org,'inscription_cours:'.$enrollmentId,'pret_instrument:'.$enrollmentId]);
            } else {
                $pdo->prepare("UPDATE cda_facturation_a_preparer SET deleted_at=NOW(),updated_by=?,updated_at=NOW() WHERE id=? AND organisation_id=?")->execute([$user?:null,$id,$org]);
            }
            $pdo->commit();
            if($releasedSlotId>0){ EnrollmentRequest::promoteNextForSlot($org,$releasedSlotId,$user); }
        } catch(\Throwable $e){if($pdo->inTransaction())$pdo->rollBack();throw $e;}
    }

    public static function mergeDrafts(int $org,int $user): int
    {
        $pdo=Database::connection();$pdo->beginTransaction();
        try {
            $clients=$pdo->prepare("SELECT client_id FROM cda_invoices WHERE organisation_id=? AND document_type='invoice' AND status='draft' AND deleted_at IS NULL GROUP BY client_id HAVING COUNT(*)>1 FOR UPDATE");
            $clients->execute([$org]);$merged=0;
            foreach($clients->fetchAll(PDO::FETCH_COLUMN) as $clientId){
                $drafts=$pdo->prepare("SELECT id FROM cda_invoices WHERE organisation_id=? AND client_id=? AND document_type='invoice' AND status='draft' AND deleted_at IS NULL ORDER BY id FOR UPDATE");
                $drafts->execute([$org,(int)$clientId]);$ids=array_map('intval',$drafts->fetchAll(PDO::FETCH_COLUMN));
                if(count($ids)<2)continue;
                $target=array_shift($ids);$marks=implode(',',array_fill(0,count($ids),'?'));
                $pdo->prepare("UPDATE cda_invoice_lines SET invoice_id=? WHERE invoice_id IN ($marks)")->execute([$target,...$ids]);
                $pdo->prepare("UPDATE cda_facturation_a_preparer SET invoice_id=?,updated_by=?,updated_at=NOW() WHERE invoice_id IN ($marks)")->execute([$target,$user?:null,...$ids]);
                $pdo->prepare("UPDATE cda_invoices SET total_amount=(SELECT COALESCE(SUM(net_amount),0) FROM cda_invoice_lines WHERE invoice_id=?),updated_by=? WHERE id=?")->execute([$target,$user?:null,$target]);
                $pdo->prepare("UPDATE cda_invoices SET deleted_at=NOW(),updated_by=? WHERE id IN ($marks)")->execute([$user?:null,...$ids]);
                $merged+=count($ids);
            }
            $pdo->commit();return $merged;
        } catch(\Throwable $e){if($pdo->inTransaction())$pdo->rollBack();throw $e;}
    }

    public static function issueDraftsTogether(int $org,array $ids,int $user): int
    {
        $ids=array_values(array_unique(array_filter(array_map('intval',$ids))));
        if(count($ids)<2)throw new \RuntimeException('Sélectionnez au moins deux brouillons du même adhérent.');
        $pdo=Database::connection();$pdo->beginTransaction();
        try {
            $marks=implode(',',array_fill(0,count($ids),'?'));
            $q=$pdo->prepare("SELECT id,client_id,document_type FROM cda_invoices WHERE organisation_id=? AND id IN ($marks) AND status='draft' AND deleted_at IS NULL ORDER BY id FOR UPDATE");
            $q->execute([$org,...$ids]);$drafts=$q->fetchAll();
            if(count($drafts)!==count($ids))throw new \RuntimeException('Un des brouillons sélectionnés est introuvable ou déjà émis.');
            $clientId=(int)$drafts[0]['client_id'];$type=(string)$drafts[0]['document_type'];
            foreach($drafts as $draft){
                if((int)$draft['client_id']!==$clientId)throw new \RuntimeException('Les brouillons sélectionnés doivent appartenir au même adhérent.');
                if((string)$draft['document_type']!==$type)throw new \RuntimeException('Les documents sélectionnés doivent être du même type.');
            }
            $target=(int)$drafts[0]['id'];$sources=array_values(array_filter($ids,fn($id)=>$id!==$target));
            $sourceMarks=implode(',',array_fill(0,count($sources),'?'));
            $pdo->prepare("UPDATE cda_invoice_lines SET invoice_id=? WHERE invoice_id IN ($sourceMarks)")->execute([$target,...$sources]);
            $pdo->prepare("UPDATE cda_facturation_a_preparer SET invoice_id=?,updated_by=?,updated_at=NOW() WHERE invoice_id IN ($sourceMarks)")->execute([$target,$user?:null,...$sources]);
            $pdo->prepare("UPDATE cda_invoices SET total_amount=(SELECT COALESCE(SUM(net_amount),0) FROM cda_invoice_lines WHERE invoice_id=?),updated_by=? WHERE id=?")->execute([$target,$user?:null,$target]);
            $pdo->prepare("UPDATE cda_invoices SET deleted_at=NOW(),updated_by=? WHERE id IN ($sourceMarks)")->execute([$user?:null,...$sources]);
            $pdo->commit();
            self::issue($target,$org);
            return $target;
        } catch(\Throwable $e){if($pdo->inTransaction())$pdo->rollBack();throw $e;}
    }

    public static function deleteDraft(int $org,int $id,int $user): void
    {
        $pdo=Database::connection();$pdo->beginTransaction();
        try {
            $q=$pdo->prepare("SELECT id FROM cda_invoices WHERE id=? AND organisation_id=? AND status='draft' AND deleted_at IS NULL FOR UPDATE");$q->execute([$id,$org]);if(!$q->fetchColumn())throw new \RuntimeException('Seul un brouillon peut être supprimé.');
            $pdo->prepare("UPDATE cda_facturation_a_preparer SET statut='a_facturer',invoice_id=NULL,updated_by=?,updated_at=NOW() WHERE invoice_id=? AND organisation_id=?")->execute([$user?:null,$id,$org]);
            $pdo->prepare("DELETE a FROM cda_line_allocations a INNER JOIN cda_invoice_lines l ON l.id=a.invoice_line_id WHERE l.invoice_id=?")->execute([$id]);
            $pdo->prepare("DELETE FROM cda_invoice_lines WHERE invoice_id=?")->execute([$id]);
            $pdo->prepare("DELETE FROM cda_invoices WHERE id=? AND organisation_id=? AND status='draft'")->execute([$id,$org]);
            $pdo->commit();
        } catch(\Throwable $e){if($pdo->inTransaction())$pdo->rollBack();throw $e;}
    }
    public static function pendingPayments(int $org): array
    {
        $q=Database::connection()->prepare("SELECT i.id,i.document_number,i.issue_date,i.due_date,i.total_amount,i.paid_amount,(i.total_amount-i.paid_amount) balance_amount,i.client_id,i.client_account,c.nom,c.prenom,c.email FROM cda_invoices i JOIN clients c ON c.id=i.client_id WHERE i.organisation_id=? AND i.document_type='invoice' AND i.status IN('issued','part_paid') AND i.total_amount>i.paid_amount ORDER BY COALESCE(i.due_date,i.issue_date),c.nom,c.prenom,i.id");
        $q->execute([$org]);return $q->fetchAll();
    }
    public static function validatePendingPayments(int $org,array $ids,int $user): int
    {
        $pdo=Database::connection();$pdo->beginTransaction();$count=0;
        try {
            $select=$pdo->prepare("SELECT i.* FROM cda_invoices i WHERE i.id=? AND i.organisation_id=? AND i.document_type='invoice' AND i.status IN('issued','part_paid') AND i.total_amount>i.paid_amount FOR UPDATE");
            $payment=$pdo->prepare("INSERT INTO cda_payments(invoice_id,payment_date,amount,payment_method,reference,banked_at,notes,created_by) VALUES(?,CURDATE(),?,'Validation comptable du relevé',NULL,CURDATE(),'Paiement confirmé manuellement par la comptable',?)");
            $audit=$pdo->prepare("INSERT INTO cda_payment_validations(organisation_id,invoice_id,payment_id,validated_by) VALUES(?,?,?,?)");
            foreach($ids as $id){$select->execute([(int)$id,$org]);$invoice=$select->fetch();if(!$invoice)continue;$balance=round((float)$invoice['total_amount']-(float)$invoice['paid_amount'],2);if($balance<=0)continue;$payment->execute([$id,$balance,$user]);$paymentId=(int)$pdo->lastInsertId();$audit->execute([$org,$id,$paymentId,$user]);$pdo->prepare("UPDATE cda_invoices SET paid_amount=total_amount,banked_amount=total_amount,status='paid' WHERE id=?")->execute([$id]);$pending=$pdo->prepare("SELECT COUNT(*) FROM cda_invoices WHERE organisation_id=? AND client_id=? AND document_type='invoice' AND status IN('issued','part_paid') AND total_amount>paid_amount");$pending->execute([$org,$invoice['client_id']]);if((int)$pending->fetchColumn()===0){$pdo->prepare("UPDATE cda_facturation_a_preparer SET statut='payee',updated_by=?,updated_at=NOW() WHERE organisation_id=? AND client_id=? AND statut='facturee' AND deleted_at IS NULL")->execute([$user,$org,$invoice['client_id']]);}$pdo->prepare("INSERT INTO cda_adherent_timeline(organisation_id,client_id,dossier_annuel_id,event_code,title,description,created_by) SELECT ?,?,d.id,'payment.validated','Paiement validé','La facture a été marquée payée après contrôle manuel du relevé bancaire.',? FROM cda_dossiers_adherents_annuels d WHERE d.client_id=? AND d.deleted_at IS NULL ORDER BY d.id DESC LIMIT 1")->execute([$org,$invoice['client_id'],$user,$invoice['client_id']]);$count++;}
            $pdo->commit();return $count;
        } catch(\Throwable $e){$pdo->rollBack();throw $e;}
    }
    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 ORDER BY nom,prenom");
        $q->execute([$org]); return $q->fetchAll();
    }
    public static function assignments(int $org, bool $active=true): array
    {
        $sql="SELECT * FROM cda_accounting_assignments WHERE organisation_id=?".($active?' AND active=1':'')." ORDER BY label";
        $q=Database::connection()->prepare($sql);$q->execute([$org]);return $q->fetchAll();
    }
    public static function settings(int $org): array
    {
        $q=Database::connection()->prepare("SELECT * FROM cda_billing_settings WHERE organisation_id=?");$q->execute([$org]);return $q->fetch() ?: [];
    }
    public static function create(int $org,int $clientId,array $lines,string $type,int $userId,?int $parent=null): int
    {
        $pdo=Database::connection();$pdo->beginTransaction();
        try {
            $q=$pdo->prepare("SELECT code_client FROM clients WHERE id=? AND organisation_id=?");$q->execute([$clientId,$org]);$account=(string)$q->fetchColumn();
            if($account==='') throw new \RuntimeException('Adhérent introuvable.');
            $total=0.0;foreach($lines as $l)$total+=(float)$l['net'];
            $q=$pdo->prepare("INSERT INTO cda_invoices(public_token,organisation_id,client_id,client_account,document_type,parent_invoice_id,total_amount,created_by) VALUES(?,?,?,?,?,?,?,?)");
            $q->execute([bin2hex(random_bytes(32)),$org,$clientId,$account,$type,$parent,$total,$userId]);$id=(int)$pdo->lastInsertId();
            foreach($lines as $l){
                $q=$pdo->prepare("INSERT INTO cda_invoice_lines(invoice_id,description,assignment_id,pole_code,project_code,revenue_account,service_start,service_end,allocation_mode,quantity,unit_price,gross_amount,discount_amount,net_amount,source_line_id) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
                $q->execute([$id,$l['description'],$l['assignment_id']?:null,$l['pole_code']?:null,$l['project_code']?:null,$l['account'],$l['start']?:null,$l['end']?:null,$l['mode'],$l['quantity'],$l['unit_price'],$l['gross'],$l['discount'],$l['net'],$l['source_line_id']??null]);
                self::allocate($pdo,(int)$pdo->lastInsertId(),(float)$l['net'],$l['start']?:null,$l['end']?:null,$l['mode']);
            }
            $pdo->commit();return $id;
        }catch(\Throwable $e){$pdo->rollBack();throw $e;}
    }
    private static function allocate(PDO $pdo,int $lineId,float $amount,?string $start,?string $end,string $mode): void
    {
        $year=(int)date('Y');$current=$amount;$deferred=0.0;$reversal=null;
        if($mode==='period'&&$start&&$end&&$end>=$start){
            $year=(int)substr($start,0,4);$cut=$year.'-12-31';
            if($end>$cut){$a=new \DateTimeImmutable($start);$b=new \DateTimeImmutable($end);$total=(int)$a->diff($b)->format('%a')+1;$current=round($amount*(((int)$a->diff(new \DateTimeImmutable($cut))->format('%a')+1)/$total),2);$deferred=round($amount-$current,2);$reversal=($year+1).'-01-01';}
        }
        $q=$pdo->prepare("INSERT INTO cda_line_allocations(invoice_line_id,fiscal_year,revenue_amount,deferred_amount,reversal_date) VALUES(?,?,?,?,?)");$q->execute([$lineId,$year,$current,$deferred,$reversal]);
    }
    public static function find(int $id,int $org): ?array
    {
        $pdo=Database::connection();$q=$pdo->prepare("SELECT i.*,c.nom,c.prenom,c.email,c.adresse_ligne1,c.adresse_ligne2,c.code_postal,c.ville FROM cda_invoices i JOIN clients c ON c.id=i.client_id WHERE i.id=? AND i.organisation_id=? AND i.deleted_at IS NULL");$q->execute([$id,$org]);$invoice=$q->fetch();if(!$invoice)return null;
        $q=$pdo->prepare("SELECT l.*,a.revenue_amount,a.deferred_amount,a.reversal_date FROM cda_invoice_lines l LEFT JOIN cda_line_allocations a ON a.invoice_line_id=l.id WHERE l.invoice_id=? ORDER BY l.id");$q->execute([$id]);$invoice['lines']=$q->fetchAll();
        $q=$pdo->prepare("SELECT * FROM cda_payments WHERE invoice_id=? ORDER BY payment_date,id");$q->execute([$id]);$invoice['payments']=$q->fetchAll();
        $q=$pdo->prepare("SELECT * FROM cda_accounting_entries WHERE invoice_id=? ORDER BY entry_date,id");$q->execute([$id]);$invoice['entries']=$q->fetchAll();
        $q=$pdo->prepare("SELECT * FROM cda_payment_schedules WHERE invoice_id=? ORDER BY installment_number");$q->execute([$id]);$invoice['schedules']=$q->fetchAll();return $invoice;
    }
    public static function updateDraft(int $id,int $org,array $data,int $user):void
    {
        $pdo=Database::connection();$pdo->beginTransaction();
        try{$q=$pdo->prepare("SELECT status FROM cda_invoices WHERE id=? AND organisation_id=? AND deleted_at IS NULL FOR UPDATE");$q->execute([$id,$org]);if($q->fetchColumn()!=='draft')throw new \RuntimeException('Cette facture n’est plus un brouillon.');
            $lines=$pdo->prepare("SELECT id FROM cda_invoice_lines WHERE invoice_id=? ORDER BY id");$lines->execute([$id]);$ids=array_map('intval',$lines->fetchAll(PDO::FETCH_COLUMN));$total=0.0;
            $update=$pdo->prepare("UPDATE cda_invoice_lines SET description=?,gross_amount=?,discount_amount=?,net_amount=?,unit_price=? WHERE id=? AND invoice_id=?");
            foreach($ids as $lineId){$description=trim((string)($data['description'][$lineId]??''));$gross=round((float)($data['gross_amount'][$lineId]??0),2);$discount=max(0,min($gross,round((float)($data['discount_amount'][$lineId]??0),2)));if($description===''||$gross<0)throw new \RuntimeException('Chaque ligne doit avoir un libellé et un montant valides.');$net=round($gross-$discount,2);$update->execute([$description,$gross,$discount,$net,$gross,$lineId,$id]);$total+=$net;}
            $pdo->prepare("UPDATE cda_invoices SET total_amount=?,due_date=?,updated_by=? WHERE id=? AND organisation_id=?")->execute([$total,($data['due_date']??'')?:null,$user?:null,$id,$org]);$pdo->commit();
        }catch(\Throwable$e){if($pdo->inTransaction())$pdo->rollBack();throw$e;}
    }
    public static function issue(int $id,int $org): void
    {
        $pdo=Database::connection();$pdo->beginTransaction();try{$q=$pdo->prepare("SELECT document_type FROM cda_invoices WHERE id=? AND organisation_id=? AND status='draft' FOR UPDATE");$q->execute([$id,$org]);$type=$q->fetchColumn();if(!$type)throw new \RuntimeException('Document déjà émis ou introuvable.');$year=(int)date('Y');$pdo->prepare("INSERT INTO cda_document_sequences VALUES(?,?,?,0) ON DUPLICATE KEY UPDATE last_number=last_number")->execute([$org,$type,$year]);$pdo->prepare("UPDATE cda_document_sequences SET last_number=LAST_INSERT_ID(last_number+1) WHERE organisation_id=? AND document_type=? AND fiscal_year=?")->execute([$org,$type,$year]);$n=(int)$pdo->lastInsertId();$s=self::settings($org);$prefix=$type==='credit'?($s['credit_prefix']??'AV'):($type==='proforma'?($s['proforma_prefix']??'PRO'):($s['invoice_prefix']??'FAC'));$number=$prefix.'-'.$year.'-'.str_pad((string)$n,6,'0',STR_PAD_LEFT);$pdo->prepare("UPDATE cda_invoices SET document_number=?,status='issued',issue_date=CURDATE(),due_date=DATE_ADD(CURDATE(),INTERVAL 30 DAY) WHERE id=?")->execute([$number,$id]);self::buildEntries($pdo,$id,$org);$pdo->commit();}catch(\Throwable $e){$pdo->rollBack();throw $e;}
    }
    private static function buildEntries(PDO $pdo,int $id,int $org): void
    {
        $inv=self::find($id,$org);if(!$inv||$inv['document_type']==='proforma')return;$sign=$inv['document_type']==='credit'?-1:1;$settings=self::settings($org);$date=$inv['issue_date']?:date('Y-m-d');$year=(int)substr($date,0,4);$journal=$settings['sales_journal']??'VE';$label=($inv['document_type']==='credit'?'Avoir ':'Facture ').$inv['document_number'];
        $q=$pdo->prepare("INSERT INTO cda_accounting_entries(invoice_id,entry_date,journal_code,account_number,label,debit,credit,fiscal_year,entry_kind) VALUES(?,?,?,?,?,?,?,?,?)");
        $q->execute([$id,$date,$journal,$inv['client_account'],$label,$sign>0?$inv['total_amount']:0,$sign<0?$inv['total_amount']:0,$year,'customer']);
        foreach($inv['lines'] as $l){
            $net=(float)$l['net_amount'];$def=(float)$l['deferred_amount'];
            $q->execute([$id,$date,$journal,$l['revenue_account'],$label,$sign<0?$net:0,$sign>0?$net:0,$year,'revenue']);
            if($def>0){
                $pca=$settings['pca_account']??'487000';$od=$settings['od_journal']??'OD';$closing=$year.'-12-31';$rd=$l['reversal_date']?:($year+1).'-01-01';
                $q->execute([$id,$closing,$od,$l['revenue_account'],'PCA '.$label,$sign>0?$def:0,$sign<0?$def:0,$year,'pca']);
                $q->execute([$id,$closing,$od,$pca,'PCA '.$label,$sign<0?$def:0,$sign>0?$def:0,$year,'pca']);
                $q->execute([$id,$rd,$od,$pca,'Extourne '.$label,$sign>0?$def:0,$sign<0?$def:0,$year+1,'reversal']);
                $q->execute([$id,$rd,$od,$l['revenue_account'],'Extourne '.$label,$sign<0?$def:0,$sign>0?$def:0,$year+1,'reversal']);
            }
        }
    }
    public static function payment(int $id,int $org,float $amount,string $method,string $date,string $reference,int $user): void
    { $pdo=Database::connection();$pdo->beginTransaction();try{$q=$pdo->prepare("INSERT INTO cda_payments(invoice_id,payment_date,amount,payment_method,reference,created_by) SELECT id,?,?,?,?,? FROM cda_invoices WHERE id=? AND organisation_id=? AND document_type='invoice'");$q->execute([$date,$amount,$method,$reference,$user,$id,$org]);$paymentId=(int)$pdo->lastInsertId();$inv=self::find($id,$org);$s=self::settings($org);$label='Règlement '.($inv['document_number']??$id);$journal=$s['bank_journal']??'CM';$debit=$s['bank_account']??'512000';if(stripos($method,'esp')!==false){$journal=$s['cash_journal']??'CA';$debit=$s['cash_account']??'531000';$register=(int)$pdo->query("SELECT id FROM cda_cash_registers WHERE organisation_id=".$org." AND holder_user_id=".$user." AND active=1 ORDER BY is_treasurer_cash DESC,id LIMIT 1")->fetchColumn();if(!$register)throw new \RuntimeException('Aucune caisse active n’est attribuée à cet utilisateur.');$pdo->prepare("INSERT INTO cda_cash_movements(organisation_id,movement_date,movement_type,target_register_id,payment_id,amount,label,status,requested_by,accepted_by,accepted_at) VALUES(?,?,'cash_payment',?,?,?,'Encaissement espèces','accepted',?,?,NOW())")->execute([$org,$date,$register,$paymentId,$amount,$user,$user]);$pdo->prepare("UPDATE cda_cash_registers SET current_balance=current_balance+? WHERE id=?")->execute([$amount,$register]);}elseif(stripos($method,'ch')!==false){$journal=$s['cheque_journal']??'CH';$debit=$s['cheque_account']??'511200';$pdo->prepare("INSERT INTO cda_cheques(organisation_id,payment_id,cheque_number,amount,received_date,status) VALUES(?,?,?,?,?,'received')")->execute([$org,$paymentId,$reference?:('CH-'.$paymentId),$amount,$date]);}$e=$pdo->prepare("INSERT INTO cda_accounting_entries(invoice_id,entry_date,journal_code,account_number,label,debit,credit,fiscal_year,entry_kind) VALUES(?,?,?,?,?,?,?,?,?)");$e->execute([$id,$date,$journal,$debit,$label,$amount,0,(int)substr($date,0,4),'payment']);$e->execute([$id,$date,$journal,$inv['client_account'],$label,0,$amount,(int)substr($date,0,4),'payment']);$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=? AND organisation_id=?")->execute([$id,$id,$id,$org]);$pdo->commit();}catch(\Throwable $e){$pdo->rollBack();throw $e;} }
    public static function bank(int $paymentId,int $org): void
    { Database::connection()->prepare("UPDATE cda_payments p JOIN cda_invoices i ON i.id=p.invoice_id SET p.banked_at=CURDATE(),i.banked_amount=(SELECT COALESCE(SUM(x.amount),0) FROM cda_payments x WHERE x.invoice_id=i.id AND x.banked_at IS NOT NULL) WHERE p.id=? AND i.organisation_id=?")->execute([$paymentId,$org]); }
    public static function saveSettings(int $org,array $v): void
    { $q=Database::connection()->prepare("UPDATE cda_billing_settings SET legal_name=?,address_line1=?,postal_code=?,city=?,siret=?,vat_mention=?,pca_account=?,sales_journal=?,bank_journal=?,od_journal=?,cash_journal=?,cheque_journal=?,bank_account=?,cash_account=?,cheque_account=?,bank_name=?,iban=?,bic=?,account_holder=?,cheque_order=?,cheque_instruction=? WHERE organisation_id=?");$q->execute([$v['legal_name'],$v['address_line1'],$v['postal_code'],$v['city'],$v['siret'],$v['vat_mention'],$v['pca_account'],$v['sales_journal'],$v['bank_journal'],$v['od_journal'],$v['cash_journal'],$v['cheque_journal'],$v['bank_account'],$v['cash_account'],$v['cheque_account'],$v['bank_name'],$v['iban'],$v['bic'],$v['account_holder'],$v['cheque_order'],$v['cheque_instruction'],$org]); }
    public static function saveAssignment(int $org,array $v): void
    { $q=Database::connection()->prepare("INSERT INTO cda_accounting_assignments(organisation_id,code,label,pole_code,project_code,revenue_account,is_default) VALUES(?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE label=VALUES(label),pole_code=VALUES(pole_code),project_code=VALUES(project_code),revenue_account=VALUES(revenue_account),active=1");$q->execute([$org,$v['code'],$v['label'],$v['pole_code']?:null,$v['project_code']?:null,$v['revenue_account'],0]); }
}
