<?php
namespace App\Models;

use App\Core\Database;

final class PricingCatalog
{
    public static function poles(int $organisationId): array
    {
        $q = Database::connection()->prepare("SELECT id,code,libelle FROM poles WHERE organisation_id=? AND actif=1 AND deleted_at IS NULL ORDER BY ordre_affichage,libelle");
        $q->execute([$organisationId]);
        return $q->fetchAll();
    }

    public static function disciplines(int $organisationId): array
    {
        $q = Database::connection()->prepare("SELECT id,code,libelle,pole_id FROM disciplines WHERE organisation_id=? AND actif=1 ORDER BY libelle");
        $q->execute([$organisationId]);
        return $q->fetchAll();
    }

    public static function courses(int $organisationId): array
    {
        $q = Database::connection()->prepare("SELECT id,code,libelle,pole_id,discipline_id FROM ateliers WHERE organisation_id=? AND actif=1 AND deleted_at IS NULL AND discipline_id IS NOT NULL ORDER BY libelle");
        $q->execute([$organisationId]);
        return $q->fetchAll();
    }

    public static function publics(int $organisationId): array
    {
        $q = Database::connection()->prepare("SELECT id,code,libelle FROM publics_cibles WHERE organisation_id=? AND actif=1 ORDER BY ordre_affichage,libelle");
        $q->execute([$organisationId]);
        return $q->fetchAll();
    }

    public static function years(int $organisationId): array
    {
        $q = Database::connection()->prepare("SELECT id,libelle,date_debut,date_fin,active,inscriptions_ouvertes FROM annees_scolaires WHERE organisation_id=? ORDER BY date_debut DESC");
        $q->execute([$organisationId]);
        return $q->fetchAll();
    }

    public static function all(int $organisationId): array
    {
        $q = Database::connection()->prepare("SELECT c.*,p.libelle pole_libelle,d.libelle discipline_libelle,a.libelle atelier_libelle,y.libelle annee_libelle,GROUP_CONCAT(DISTINCT pc.libelle ORDER BY pc.ordre_affichage SEPARATOR ' + ') public_libelle,GROUP_CONCAT(DISTINCT pc.id ORDER BY pc.ordre_affichage) public_ids FROM cda_prestation_catalog c JOIN poles p ON p.id=c.pole_id LEFT JOIN disciplines d ON d.id=c.discipline_id LEFT JOIN ateliers a ON a.id=c.atelier_id LEFT JOIN annees_scolaires y ON y.id=c.annee_scolaire_id LEFT JOIN cda_prestation_public cpp ON cpp.prestation_id=c.id LEFT JOIN publics_cibles pc ON pc.id=cpp.public_cible_id WHERE c.organisation_id=? GROUP BY c.id ORDER BY c.actif DESC,p.libelle,c.categorie,c.libelle");
        $q->execute([$organisationId]);
        return $q->fetchAll();
    }

    public static function createYear(int $organisationId, array $values): void
    {
        $start = trim((string)($values['date_debut'] ?? ''));
        $end = trim((string)($values['date_fin'] ?? ''));
        if (!$start || !$end || $end < $start) {
            throw new \RuntimeException('Les dates de l’année scolaire sont invalides.');
        }
        $label = trim((string)($values['libelle'] ?? ''));
        if ($label === '') {
            $label = substr($start, 0, 4) . '-' . substr($end, 0, 4);
        }
        $pdo = Database::connection();
        $pdo->beginTransaction();
        try {
            if (!empty($values['active'])) {
                $q = $pdo->prepare("UPDATE annees_scolaires SET active=0 WHERE organisation_id=?");
                $q->execute([$organisationId]);
            }
            $q = $pdo->prepare("INSERT INTO annees_scolaires(organisation_id,libelle,date_debut,date_fin,active,inscriptions_ouvertes) VALUES(?,?,?,?,?,?)");
            $q->execute([$organisationId,$label,$start,$end,!empty($values['active'])?1:0,!empty($values['inscriptions_ouvertes'])?1:0]);
            $pdo->commit();
        } catch (\Throwable $e) {
            $pdo->rollBack();
            throw $e;
        }
    }

    public static function save(int $organisationId, array $v): void
    {
        $pdo = Database::connection();
        $id = (int)($v['id'] ?? 0);
        $amount = ($v['mode_tarif'] ?? '') === 'libre' ? null : (float)$v['montant'];
        if (($v['categorie'] ?? '') === 'adhesion') {
            $q = $pdo->prepare("SELECT id FROM poles WHERE id=? AND organisation_id=? AND (UPPER(code) IN ('STR','POL-STR') OR LOWER(libelle)='structure') AND actif=1");
            $q->execute([(int)$v['pole_id'],$organisationId]);
            if (!$q->fetchColumn()) throw new \RuntimeException('Une adhésion doit être rattachée au pôle Structure.');
        }
        $year = ($v['annee_scolaire_id'] ?? '') !== '' ? (int)$v['annee_scolaire_id'] : null;
        $labelMode = in_array($v['mode_libelle'] ?? '', ['inscription','propose','libre'], true) ? $v['mode_libelle'] : 'propose';
        $label = trim((string)($v['libelle'] ?? ''));
        if ($labelMode === 'libre' && $label === '') $label = 'Libellé à saisir en facturation';
        $publicIds=array_values(array_unique(array_filter(array_map('intval',(array)($v['public_cible_ids']??[])))));
        $data=[$v['code'],$label,$v['categorie'],(int)$v['pole_id'],($v['discipline_id']??'')!==''?(int)$v['discipline_id']:null,($v['atelier_id']??'')!==''?(int)$v['atelier_id']:null,$year,$labelMode,$v['mode_tarif'],$amount,$v['unite'],$v['date_debut']?:null,$v['date_fin']?:null,$v['compte_produit']?:null,isset($v['actif'])?1:0];
        $pdo->beginTransaction();
        try {
            if ($id) {
                $q=$pdo->prepare("UPDATE cda_prestation_catalog SET code=?,libelle=?,categorie=?,pole_id=?,discipline_id=?,atelier_id=?,public_cible_id=NULL,annee_scolaire_id=?,mode_libelle=?,mode_tarif=?,montant=?,unite=?,date_debut=?,date_fin=?,compte_produit=?,actif=? WHERE id=? AND organisation_id=?");
                $data[]=$id;$data[]=$organisationId;$q->execute($data);
            } else {
                $q=$pdo->prepare("INSERT INTO cda_prestation_catalog(code,libelle,categorie,pole_id,discipline_id,atelier_id,public_cible_id,annee_scolaire_id,mode_libelle,mode_tarif,montant,unite,date_debut,date_fin,compte_produit,actif,organisation_id) VALUES(?,?,?,?,?,?,NULL,?,?,?,?,?,?,?,?,?,?)");
                $data[]=$organisationId;$q->execute($data);$id=(int)$pdo->lastInsertId();
            }
            $pdo->prepare("DELETE FROM cda_prestation_public WHERE prestation_id=?")->execute([$id]);
            $insert=$pdo->prepare("INSERT INTO cda_prestation_public(prestation_id,public_cible_id) SELECT ?,id FROM publics_cibles WHERE id=? AND organisation_id=?");
            foreach($publicIds as $publicId)$insert->execute([$id,$publicId,$organisationId]);
            $pdo->commit();
        } catch (\Throwable $e) {if($pdo->inTransaction())$pdo->rollBack();throw $e;}
    }
}
