<?php
declare(strict_types=1);
namespace App\Services;
use App\Core\Database;
use InvalidArgumentException;
use PDO;
use RuntimeException;
final class StudioReportService
{
    public function modules(): array { return Database::connection()->query("SELECT id,code,name FROM axiom_studio_modules WHERE archived_at IS NULL ORDER BY name")->fetchAll(PDO::FETCH_ASSOC) ?: []; }
    public function reports(int $moduleId): array { $q=Database::connection()->prepare("SELECT * FROM axiom_studio_reports WHERE module_id=:m AND archived_at IS NULL ORDER BY name");$q->execute(['m'=>$moduleId]);return $q->fetchAll(PDO::FETCH_ASSOC)?:[]; }
    public function report(int $id): ?array { $q=Database::connection()->prepare("SELECT * FROM axiom_studio_reports WHERE id=:id AND archived_at IS NULL");$q->execute(['id'=>$id]);$r=$q->fetch(PDO::FETCH_ASSOC);if(!$r)return null;$r['config']=json_decode((string)$r['config_json'],true)?:[];return $r; }
    public function entities(int $moduleId): array { $q=Database::connection()->prepare("SELECT id,name,code,table_name FROM axiom_studio_entities WHERE module_id=:m AND archived_at IS NULL ORDER BY position,id");$q->execute(['m'=>$moduleId]);return $q->fetchAll(PDO::FETCH_ASSOC)?:[]; }
    public function fields(int $entityId): array { $q=Database::connection()->prepare("SELECT id,label AS name,code,field_type,position FROM axiom_studio_fields WHERE entity_id=:e AND archived_at IS NULL ORDER BY position,id");$q->execute(['e'=>$entityId]);return $q->fetchAll(PDO::FETCH_ASSOC)?:[]; }
    public function save(int $moduleId,array $data,?int $userId): int {
        $name=trim((string)($data['name']??''));$entityId=(int)($data['entity_id']??0);if($moduleId<1||$entityId<1||$name==='')throw new InvalidArgumentException('Module, nom et entité obligatoires.');
        $columns=array_values(array_filter(array_map('intval',(array)($data['columns']??[]))));if(!$columns)throw new InvalidArgumentException('Sélectionnez au moins une colonne.');
        $config=['columns'=>$columns,'filter_field_id'=>(int)($data['filter_field_id']??0),'filter_operator'=>$this->operator((string)($data['filter_operator']??'contains')),'filter_value'=>trim((string)($data['filter_value']??'')),'sort_field_id'=>(int)($data['sort_field_id']??0),'sort_direction'=>strtoupper((string)($data['sort_direction']??'ASC'))==='DESC'?'DESC':'ASC','group_field_id'=>(int)($data['group_field_id']??0),'limit'=>max(1,min(500,(int)($data['limit']??50))),'orientation'=>in_array(($data['orientation']??'landscape'),['portrait','landscape'],true)?$data['orientation']:'landscape'];
        $id=(int)($data['report_id']??0);$pdo=Database::connection();
        if($id>0){$q=$pdo->prepare("UPDATE axiom_studio_reports SET name=:n,description=:d,entity_id=:e,config_json=:c,status='draft',updated_by=:u,updated_at=CURRENT_TIMESTAMP WHERE id=:id AND module_id=:m AND archived_at IS NULL");$q->execute(['n'=>$name,'d'=>trim((string)($data['description']??''))?:null,'e'=>$entityId,'c'=>json_encode($config,JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES),'u'=>$userId,'id'=>$id,'m'=>$moduleId]);return $id;}
        $code=$this->uniqueCode($moduleId,$this->code((string)($data['code']??$name)));$q=$pdo->prepare("INSERT INTO axiom_studio_reports(module_id,entity_id,name,code,description,config_json,status,created_by,updated_by) VALUES(:m,:e,:n,:code,:d,:c,'draft',:u,:u)");$q->execute(['m'=>$moduleId,'e'=>$entityId,'n'=>$name,'code'=>$code,'d'=>trim((string)($data['description']??''))?:null,'c'=>json_encode($config,JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES),'u'=>$userId]);return (int)$pdo->lastInsertId(); }
    public function publish(int $id,?int $userId): void { $q=Database::connection()->prepare("UPDATE axiom_studio_reports SET status='published',published_at=CURRENT_TIMESTAMP,updated_by=:u,updated_at=CURRENT_TIMESTAMP WHERE id=:id AND archived_at IS NULL");$q->execute(['u'=>$userId,'id'=>$id]); }
    public function archive(int $id,?int $userId): void { $q=Database::connection()->prepare("UPDATE axiom_studio_reports SET archived_at=CURRENT_TIMESTAMP,updated_by=:u,updated_at=CURRENT_TIMESTAMP WHERE id=:id");$q->execute(['u'=>$userId,'id'=>$id]); }
    public function preview(int $id): array { $report=$this->report($id);if(!$report)throw new RuntimeException('Rapport introuvable.');$entity=$this->entity((int)$report['entity_id']);if(!$entity)throw new RuntimeException('Entité introuvable.');$cfg=$report['config'];$fields=$this->fieldMap((int)$entity['id']);$selected=[];foreach($cfg['columns']??[] as $fid){if(isset($fields[(int)$fid]))$selected[]=$fields[(int)$fid];}if(!$selected)return ['columns'=>[],'rows'=>[]];$table=$this->identifier((string)$entity['table_name']);$cols=array_map(fn($f)=>'`'.$this->identifier((string)$f['code']).'`',$selected);$sql='SELECT '.implode(',',$cols).' FROM `'.$table.'`';$params=[];$ff=(int)($cfg['filter_field_id']??0);$val=(string)($cfg['filter_value']??'');if($ff&&$val!==''&&isset($fields[$ff])){$col='`'.$this->identifier((string)$fields[$ff]['code']).'`';$op=$cfg['filter_operator']??'contains';if($op==='equals'){$sql.=" WHERE $col = :v";$params['v']=$val;}elseif($op==='starts'){$sql.=" WHERE $col LIKE :v";$params['v']=$val.'%';}else{$sql.=" WHERE $col LIKE :v";$params['v']='%'.$val.'%';}}$sf=(int)($cfg['sort_field_id']??0);if($sf&&isset($fields[$sf]))$sql.=' ORDER BY `'.$this->identifier((string)$fields[$sf]['code']).'` '.(($cfg['sort_direction']??'ASC')==='DESC'?'DESC':'ASC');$sql.=' LIMIT '.max(1,min(500,(int)($cfg['limit']??50)));$q=Database::connection()->prepare($sql);$q->execute($params);return ['columns'=>$selected,'rows'=>$q->fetchAll(PDO::FETCH_ASSOC)?:[],'report'=>$report]; }
    private function entity(int $id): ?array {$q=Database::connection()->prepare("SELECT * FROM axiom_studio_entities WHERE id=:id AND archived_at IS NULL");$q->execute(['id'=>$id]);$r=$q->fetch(PDO::FETCH_ASSOC);return $r?:null;}
    private function fieldMap(int $entityId): array {$m=[];foreach($this->fields($entityId) as $f)$m[(int)$f['id']]=$f;return $m;}
    private function operator(string $v): string {return in_array($v,['contains','equals','starts'],true)?$v:'contains';}
    private function identifier(string $v): string {if(!preg_match('/^[a-zA-Z0-9_]+$/',$v))throw new RuntimeException('Identifiant SQL invalide.');return $v;}
    private function code(string $v): string {$v=iconv('UTF-8','ASCII//TRANSLIT//IGNORE',$v)?:$v;$v=strtolower(trim(preg_replace('/[^a-zA-Z0-9]+/','_',$v),'_'));return $v?:'rapport';}
    private function uniqueCode(int $m,string $base): string {$c=$base;$i=2;$q=Database::connection()->prepare("SELECT COUNT(*) FROM axiom_studio_reports WHERE module_id=:m AND code=:c AND archived_at IS NULL");while(true){$q->execute(['m'=>$m,'c'=>$c]);if((int)$q->fetchColumn()===0)return $c;$c=$base.'_'.$i++;}}
}
