<?php
namespace App\Models;

use App\Core\Database;
use RuntimeException;

final class MemberProfile
{
    private static array $tables=[];
    private static array $columns=[];

    public static function source(): ?array
    {
        $candidates=[
            [
                'table'=>'membres_famille',
                'id'=>'id','first'=>'prenom','last'=>'nom',
                'birth'=>'date_naissance','email'=>'email',
                'phone'=>'telephone','family'=>'famille_id',
                'client'=>'adherent_id'
            ],
            [
                'table'=>'membres',
                'id'=>'id','first'=>'prenom','last'=>'nom',
                'birth'=>'date_naissance','email'=>'email',
                'phone'=>'telephone','family'=>'famille_id',
                'client'=>'client_id'
            ],
            [
                'table'=>'family_members',
                'id'=>'id','first'=>'prenom','last'=>'nom',
                'birth'=>'date_naissance','email'=>'email',
                'phone'=>'telephone','family'=>'family_id',
                'client'=>'client_id'
            ],
        ];

        foreach($candidates as $candidate){
            if(!self::tableExists($candidate['table'])){
                continue;
            }
            foreach(['id','first','last'] as $required){
                if(!self::hasColumn($candidate['table'],$candidate[$required])){
                    continue 2;
                }
            }
            foreach(['birth','email','phone','family','client'] as $optional){
                if(!$candidate[$optional] || !self::hasColumn($candidate['table'],$candidate[$optional])){
                    $candidate[$optional]=null;
                }
            }
            return $candidate;
        }

        return null;
    }

    public static function find(int $id): ?array
    {
        $source=self::source();

        if(!$source){
            return null;
        }

        $select=[
            "m.`{$source['id']}` AS id",
            "m.`{$source['first']}` AS prenom",
            "m.`{$source['last']}` AS nom",
        ];

        foreach([
            'birth'=>'date_naissance',
            'email'=>'email',
            'phone'=>'telephone',
            'family'=>'famille_id',
            'client'=>'client_id'
        ] as $key=>$alias){
            $select[]=$source[$key]
                ? "m.`{$source[$key]}` AS {$alias}"
                : "NULL AS {$alias}";
        }

        $stmt=Database::connection()->prepare(
            "SELECT ".implode(',',$select)."
             FROM `{$source['table']}` m
             WHERE m.`{$source['id']}`=:id
             LIMIT 1"
        );
        $stmt->execute(['id'=>$id]);

        $row=$stmt->fetch() ?: null;

        if($row){
            $row['source_table']=$source['table'];
            if(
                empty($row['client_id'])
                && !empty($row['famille_id'])
                && self::tableExists('familles')
                && self::hasColumn('familles','client_id')
            ){
                $client=Database::connection()->prepare(
                    "SELECT client_id
                     FROM familles
                     WHERE id=:famille_id
                       AND deleted_at IS NULL
                     LIMIT 1"
                );
                $client->execute(['famille_id'=>(int)$row['famille_id']]);
                $row['client_id']=(int)($client->fetchColumn() ?: 0);
            }
        }

        return $row;
    }

    public static function update(int $id,array $data): void
    {
        $source=self::source();

        if(!$source){
            throw new RuntimeException('Table des membres introuvable.');
        }

        $sets=[];
        $params=['id'=>$id];

        $map=[
            'prenom'=>'first',
            'nom'=>'last',
            'date_naissance'=>'birth',
            'email'=>'email',
            'telephone'=>'phone'
        ];

        foreach($map as $input=>$sourceKey){
            if(!$source[$sourceKey]){
                continue;
            }

            $sets[]="`{$source[$sourceKey]}`=:{$input}";
            $params[$input]=trim((string)($data[$input] ?? '')) ?: null;
        }

        if(!$sets){
            throw new RuntimeException('Aucun champ modifiable détecté.');
        }

        Database::connection()->prepare(
            "UPDATE `{$source['table']}`
             SET ".implode(',',$sets)."
             WHERE `{$source['id']}`=:id"
        )->execute($params);
    }

    public static function documents(int $memberId): array
    {
        $stmt=Database::connection()->prepare(
            "SELECT *
             FROM membre_documents
             WHERE membre_id=:membre_id
               AND deleted_at IS NULL
             ORDER BY created_at DESC,id DESC"
        );
        $stmt->execute(['membre_id'=>$memberId]);

        return $stmt->fetchAll();
    }

    public static function requests(int $memberId): array
    {
        $stmt=Database::connection()->prepare(
            "SELECT d.*,a.code AS atelier_code,a.libelle AS atelier_libelle
             FROM demandes_inscription d
             INNER JOIN ateliers a ON a.id=d.atelier_id
             WHERE d.membre_id=:membre_id
               AND d.deleted_at IS NULL
             ORDER BY d.created_at DESC,d.id DESC"
        );
        $stmt->execute(['membre_id'=>$memberId]);

        return $stmt->fetchAll();
    }

    private static function tableExists(string $table): bool
    {
        if(array_key_exists($table,self::$tables)){
            return self::$tables[$table];
        }

        $stmt=Database::connection()->prepare(
            "SELECT COUNT(*)
             FROM information_schema.TABLES
             WHERE TABLE_SCHEMA=DATABASE()
               AND TABLE_NAME=:table"
        );
        $stmt->execute(['table'=>$table]);

        return self::$tables[$table]=(int)$stmt->fetchColumn()>0;
    }

    private static function hasColumn(string $table,string $column): bool
    {
        $key=$table.'.'.$column;

        if(array_key_exists($key,self::$columns)){
            return self::$columns[$key];
        }

        $stmt=Database::connection()->prepare(
            "SELECT COUNT(*)
             FROM information_schema.COLUMNS
             WHERE TABLE_SCHEMA=DATABASE()
               AND TABLE_NAME=:table
               AND COLUMN_NAME=:column"
        );
        $stmt->execute(['table'=>$table,'column'=>$column]);

        return self::$columns[$key]=(int)$stmt->fetchColumn()>0;
    }
}
