<?php
namespace App\Models;

use App\Core\Database;
use Throwable;

final class WorkshopManagement
{
    public static function find(int $id): ?array
    {
        $stmt=Database::connection()->prepare(
            "SELECT a.*,p.libelle AS pole_libelle,
                    d.libelle AS discipline_libelle
             FROM ateliers a
             LEFT JOIN poles p ON p.id=a.pole_id
             LEFT JOIN disciplines d ON d.id=a.discipline_id
             WHERE a.id=:id
               AND a.deleted_at IS NULL
             LIMIT 1"
        );
        $stmt->execute(['id'=>$id]);
        return $stmt->fetch() ?: null;
    }

    public static function publics(): array
    {
        return Database::connection()->query(
            "SELECT id,code,libelle
             FROM publics_cibles
             WHERE actif=1
               AND deleted_at IS NULL
             ORDER BY ordre_affichage"
        )->fetchAll();
    }

    public static function disciplines(): array
    {
        return Database::connection()->query(
            "SELECT d.id,d.code,d.libelle,d.pole_id,p.code pole_code,p.libelle pole_libelle
             FROM disciplines d INNER JOIN poles p ON p.id=d.pole_id
             WHERE d.actif=1 AND d.deleted_at IS NULL
               AND p.actif=1 AND p.deleted_at IS NULL
             ORDER BY p.ordre_affichage,d.libelle"
        )->fetchAll();
    }

    public static function discipline(int $id): ?array
    {
        $s=Database::connection()->prepare(
            "SELECT d.id,d.code,d.pole_id,p.code pole_code
             FROM disciplines d INNER JOIN poles p ON p.id=d.pole_id
             WHERE d.id=? AND d.actif=1 AND d.deleted_at IS NULL
               AND p.actif=1 AND p.deleted_at IS NULL"
        );
        $s->execute([$id]);
        return $s->fetch() ?: null;
    }

    public static function selectedPublicIds(int $id): array
    {
        $stmt=Database::connection()->prepare(
            "SELECT public_cible_id
             FROM atelier_publics
             WHERE atelier_id=:id"
        );
        $stmt->execute(['id'=>$id]);
        return array_map(
            'intval',
            array_column($stmt->fetchAll(),'public_cible_id')
        );
    }

    public static function update(int $id,array $data): void
    {
        $pdo=Database::connection();
        $pdo->beginTransaction();

        try{
            $discipline=self::discipline((int)$data['discipline_id']);
            if(!$discipline){throw new \RuntimeException('La discipline est obligatoire.');}
            $publicCodes=self::publicCodes($data['public_ids']);
            $audience=count($publicCodes)>1?'MIX':($publicCodes[0]??'PUB');
            $code=self::uniqueCode(
                (int)$id,
                (string)$discipline['pole_code'],
                (string)$discipline['code'],
                $audience
            );
            $pdo->prepare(
                "UPDATE ateliers
                 SET libelle=:libelle,
                     pole_id=:pole_id,
                     discipline_id=:discipline_id,
                     public_cible_id=:public_cible_id,
                     code=:code,
                     type_atelier=:type_atelier,
                     description=:description,
                     duree_standard_minutes=:duree,
                     capacite_max=:capacite,
                     updated_at=CURRENT_TIMESTAMP
                 WHERE id=:id"
            )->execute([
                'libelle'=>$data['libelle'],
                'pole_id'=>$data['pole_id'],
                'discipline_id'=>$data['discipline_id'],
                'public_cible_id'=>$data['public_ids'][0] ?? null,
                'code'=>$code,
                'type_atelier'=>$data['type_atelier'],
                'description'=>$data['description'] ?: null,
                'duree'=>$data['duree'] ?: null,
                'capacite'=>$data['capacite'] ?: null,
                'id'=>$id,
            ]);

            $pdo->prepare(
                "DELETE FROM atelier_publics
                 WHERE atelier_id=:id"
            )->execute(['id'=>$id]);

            $link=$pdo->prepare(
                "INSERT INTO atelier_publics(
                    atelier_id,public_cible_id
                 ) VALUES(
                    :atelier_id,:public_cible_id
                 )"
            );

            foreach($data['public_ids'] as $publicId){
                $link->execute([
                    'atelier_id'=>$id,
                    'public_cible_id'=>(int)$publicId,
                ]);
            }

            $pdo->commit();
        }catch(Throwable $e){
            $pdo->rollBack();
            throw $e;
        }
    }

    private static function publicCodes(array $ids): array
    {
        if(!$ids)return [];
        $marks=implode(',',array_fill(0,count($ids),'?'));
        $s=Database::connection()->prepare("SELECT code FROM publics_cibles WHERE id IN ($marks) AND actif=1 ORDER BY ordre_affichage");
        $s->execute(array_map('intval',$ids));
        return array_column($s->fetchAll(),'code');
    }

    private static function uniqueCode(int $id,string $pole,string $discipline,string $audience): string
    {
        $normalize=static function(string $v):string{$a=iconv('UTF-8','ASCII//TRANSLIT//IGNORE',$v)?:$v;return preg_replace('/[^A-Z0-9]/','',strtoupper($a))?:'X';};
        $base=substr($normalize($pole),0,3).'-'.substr($normalize($discipline),0,6).'-'.substr($normalize($audience),0,3);
        $candidate=$base;$n=1;$s=Database::connection()->prepare('SELECT 1 FROM ateliers WHERE code=? AND id<>? LIMIT 1');
        while(true){$s->execute([$candidate,$id]);if(!$s->fetchColumn())return $candidate;$candidate=$base.'-'.(++$n);}
    }

    public static function archive(int $id,int $userId): void
    {
        Database::connection()->prepare(
            "UPDATE ateliers
             SET actif=0,
                 statut='archive',
                 archive_at=CURRENT_TIMESTAMP,
                 archive_by=:user_id,
                 updated_at=CURRENT_TIMESTAMP
             WHERE id=:id"
        )->execute([
            'user_id'=>$userId,
            'id'=>$id,
        ]);
    }
}
