<?php
namespace App\Models;

use App\Core\Database;
use Throwable;

final class Client
{
    public static function accountEmailExists(string $email): bool
    {
        $stmt = Database::connection()->prepare(
            'SELECT 1 FROM utilisateurs WHERE email = :email AND deleted_at IS NULL LIMIT 1'
        );
        $stmt->execute(['email' => mb_strtolower(trim($email), 'UTF-8')]);
        return (bool)$stmt->fetchColumn();
    }

    public static function clientIdForUser(int $userId): ?int
    {
        $stmt = Database::connection()->prepare(
            'SELECT client_id FROM cda_family_accounts WHERE utilisateur_id=:user_id LIMIT 1'
        );
        $stmt->execute(['user_id' => $userId]);
        $id = (int)$stmt->fetchColumn();
        return $id > 0 ? $id : null;
    }

    public static function search(string $query = '', ?int $onlyClientId = null): array
    {
        $params = [];
        $where = 'c.deleted_at IS NULL';
        if ($onlyClientId !== null) {
            $where .= ' AND c.id = :only_client_id';
            $params['only_client_id'] = $onlyClientId;
        }

        if ($query !== '') {
            $where .= ' AND (
                c.nom LIKE :q_nom
                OR c.prenom LIKE :q_prenom
                OR CONCAT(c.nom, " ", c.prenom) LIKE :q_nom_prenom
                OR CONCAT(c.prenom, " ", c.nom) LIKE :q_prenom_nom
                OR c.code_client LIKE :q_code
                OR c.email LIKE :q_email
                OR c.telephone_principal LIKE :q_phone
            )';

            $like = '%' . $query . '%';

            $params += [
                'q_nom' => $like,
                'q_prenom' => $like,
                'q_nom_prenom' => $like,
                'q_prenom_nom' => $like,
                'q_code' => $like,
                'q_email' => $like,
                'q_phone' => $like,
            ];
        }

        $stmt = Database::connection()->prepare(
            "SELECT
                c.*,
                f.id AS famille_id,
                f.reference_famille,
                f.nom_affichage,
                (
                    SELECT COUNT(*)
                    FROM membres_famille m
                    WHERE m.famille_id = f.id
                      AND m.deleted_at IS NULL
                ) AS nb_membres,
                (
                    SELECT COUNT(*)
                    FROM membres_famille m
                    WHERE m.famille_id = f.id
                      AND m.deleted_at IS NULL
                      AND m.participant_ateliers = 1
                ) AS nb_participants
             FROM clients c
             LEFT JOIN familles f
                    ON f.client_id = c.id
                   AND f.deleted_at IS NULL
             WHERE {$where}
             ORDER BY c.nom, c.prenom"
        );

        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    public static function find(int $id): ?array
    {
        $stmt = Database::connection()->prepare(
            "SELECT
                c.*,
                f.id AS famille_id,
                f.reference_famille,
                f.nom_affichage
             FROM clients c
             LEFT JOIN familles f
                    ON f.client_id = c.id
                   AND f.deleted_at IS NULL
             WHERE c.id = :id
               AND c.deleted_at IS NULL
             LIMIT 1"
        );

        $stmt->execute(['id' => $id]);
        $client = $stmt->fetch() ?: null;
        if (!$client) {
            return null;
        }

        $holder = null;
        if (!empty($client['famille_id'])) {
            $holderStmt = Database::connection()->prepare(
                "SELECT *
                 FROM membres_famille
                 WHERE famille_id = :famille_id
                   AND est_client_titulaire = 1
                   AND deleted_at IS NULL
                 ORDER BY id
                 LIMIT 1"
            );
            $holderStmt->execute(['famille_id' => (int)$client['famille_id']]);
            $holder = $holderStmt->fetch() ?: null;
        }

        $takeIfMissing = static function (array &$target, string $key, $fallback): void {
            $value = $target[$key] ?? null;
            if (($value === null || $value === '' || $value === '1900-01-01')
                && $fallback !== null && $fallback !== '' && $fallback !== '1900-01-01') {
                $target[$key] = $fallback;
            }
        };

        if ($holder) {
            foreach ([
                'civilite', 'nom', 'prenom', 'date_naissance', 'email',
                'telephone_principal', 'telephone_secondaire', 'notes',
            ] as $field) {
                $takeIfMissing($client, $field, $holder[$field] ?? null);
            }
            $client['rgpd_accepte'] = max(
                (int)($client['rgpd_accepte'] ?? 0),
                (int)($holder['rgpd_accepte'] ?? 0)
            );
            $client['client_participant'] = (int)($holder['participant_ateliers'] ?? 0);
            $client['titulaire_membre_id'] = (int)($holder['id'] ?? 0);
        } else {
            $client['client_participant'] = 0;
            $client['titulaire_membre_id'] = 0;
        }

        return $client;
    }

    public static function findByReference(string $reference): ?array
    {
        $reference = trim($reference);
        if ($reference === '') {
            return null;
        }

        $stmt = Database::connection()->prepare(
            "SELECT
                c.*,
                f.id AS famille_id,
                f.reference_famille,
                f.nom_affichage
             FROM clients c
             LEFT JOIN familles f
                    ON f.client_id = c.id
                   AND f.deleted_at IS NULL
             WHERE c.code_client = :reference
               AND c.deleted_at IS NULL
             LIMIT 1"
        );
        $stmt->execute(['reference' => $reference]);
        return $stmt->fetch() ?: null;
    }

    public static function findByPublicToken(string $token): ?array
    {
        $token = mb_strtolower(trim($token), 'UTF-8');
        if (!preg_match('/\A[a-f0-9]{64}\z/', $token)) {
            return null;
        }

        $stmt = Database::connection()->prepare(
            "SELECT c.id
             FROM clients c
             WHERE c.public_token = :public_token
               AND c.deleted_at IS NULL
             LIMIT 1"
        );
        $stmt->execute(['public_token' => $token]);
        $id = (int)$stmt->fetchColumn();
        return $id > 0 ? self::find($id) : null;
    }

    public static function publicUrl(array $client, string $tab = ''): string
    {
        if (\App\Core\Permission::isFamilyPortalIdentity()) {
            $map = ['facturation'=>'factures'];
            $cleanTab = $map[$tab] ?? $tab;
            if (!in_array($cleanTab, ['identite','famille','annees','documents','factures','historique'], true)) {
                $cleanTab = 'identite';
            }
            return '/mon-espace/dossier/' . $cleanTab;
        }
        $token = (string)($client['public_token'] ?? '');
        if (!preg_match('/\A[a-f0-9]{64}\z/', $token)) {
            throw new \RuntimeException('Identifiant public du dossier indisponible.');
        }

        $url = '/adherents/dossier?dossier=' . rawurlencode($token);
        if ($tab !== '') {
            $url .= '&onglet=' . rawurlencode($tab);
        }
        return $url;
    }

    public static function members(int $clientId): array
    {
        $stmt = Database::connection()->prepare(
            "SELECT m.*
             FROM membres_famille m
             INNER JOIN familles f ON f.id = m.famille_id
             WHERE f.client_id = :client_id
               AND m.deleted_at IS NULL
             ORDER BY
                m.est_client_titulaire DESC,
                m.date_naissance,
                m.nom,
                m.prenom"
        );

        $stmt->execute(['client_id' => $clientId]);
        return $stmt->fetchAll();
    }

    public static function duplicateExists(
        string $nom,
        string $prenom,
        ?string $dateNaissance
    ): bool {
        $pdo = Database::connection();

        if ($dateNaissance === null || $dateNaissance === '') {
            $stmt = $pdo->prepare(
                "SELECT 1
                 FROM clients
                 WHERE deleted_at IS NULL
                   AND nom = :nom
                   AND prenom = :prenom
                   AND date_naissance IS NULL
                 LIMIT 1"
            );

            $stmt->execute([
                'nom' => $nom,
                'prenom' => $prenom,
            ]);
        } else {
            $stmt = $pdo->prepare(
                "SELECT 1
                 FROM clients
                 WHERE deleted_at IS NULL
                   AND nom = :nom
                   AND prenom = :prenom
                   AND date_naissance = :date_naissance
                 LIMIT 1"
            );

            $stmt->execute([
                'nom' => $nom,
                'prenom' => $prenom,
                'date_naissance' => $dateNaissance,
            ]);
        }

        return (bool)$stmt->fetchColumn();
    }

    /** @return array{client_id:int,invitation:?array} */
    public static function create(array $data, int $userId): array
    {
        $pdo = Database::connection();
        $pdo->beginTransaction();

        try {
            $code = self::generateCode(
                (int)$data['organisation_id'],
                $data['nom'],
                $data['prenom']
            );

            $stmt = $pdo->prepare(
                "INSERT INTO clients(
                    organisation_id,
                    code_client,
                    public_token,
                    civilite,
                    nom,
                    prenom,
                    date_naissance,
                    email,
                    telephone_principal,
                    telephone_secondaire,
                    adresse_ligne1,
                    adresse_ligne2,
                    code_postal,
                    ville,
                    pays,
                    rgpd_accepte,
                    rgpd_accepte_at,
                    notes,
                    created_by,
                    updated_by
                ) VALUES(
                    :organisation_id,
                    :code_client,
                    :public_token,
                    :civilite,
                    :nom,
                    :prenom,
                    :date_naissance,
                    :email,
                    :telephone_principal,
                    :telephone_secondaire,
                    :adresse_ligne1,
                    :adresse_ligne2,
                    :code_postal,
                    :ville,
                    :pays,
                    :rgpd_accepte,
                    :rgpd_accepte_at,
                    :notes,
                    :created_by,
                    :updated_by
                )"
            );

            $stmt->execute([
                'organisation_id' => $data['organisation_id'],
                'code_client' => $code,
                'public_token' => bin2hex(random_bytes(32)),
                'civilite' => $data['civilite'],
                'nom' => $data['nom'],
                'prenom' => $data['prenom'],
                'date_naissance' => $data['date_naissance'] ?: null,
                'email' => $data['email'] ?: null,
                'telephone_principal' => $data['telephone_principal'] ?: null,
                'telephone_secondaire' => $data['telephone_secondaire'] ?: null,
                'adresse_ligne1' => $data['adresse_ligne1'] ?: null,
                'adresse_ligne2' => $data['adresse_ligne2'] ?: null,
                'code_postal' => $data['code_postal'] ?: null,
                'ville' => $data['ville'] ?: null,
                'pays' => $data['pays'] ?: 'France',
                'rgpd_accepte' => $data['rgpd_accepte'],
                'rgpd_accepte_at' => $data['rgpd_accepte']
                    ? date('Y-m-d H:i:s')
                    : null,
                'notes' => $data['notes'] ?: null,
                'created_by' => $userId,
                'updated_by' => $userId,
            ]);

            $clientId = (int)$pdo->lastInsertId();

            $referenceFamille = 'FAM-' .
                date('Y') . '-' .
                str_pad((string)$clientId, 6, '0', STR_PAD_LEFT);

            $stmt = $pdo->prepare(
                "INSERT INTO familles(
                    organisation_id,
                    client_id,
                    reference_famille,
                    nom_affichage
                ) VALUES(
                    :organisation_id,
                    :client_id,
                    :reference_famille,
                    :nom_affichage
                )"
            );

            $stmt->execute([
                'organisation_id' => $data['organisation_id'],
                'client_id' => $clientId,
                'reference_famille' => $referenceFamille,
                'nom_affichage' => 'Famille ' . $data['nom'],
            ]);

            $familleId = (int)$pdo->lastInsertId();

            $stmt = $pdo->prepare(
                "INSERT INTO membres_famille(
                    famille_id,
                    client_source_id,
                    est_client_titulaire,
                    civilite,
                    nom,
                    prenom,
                    date_naissance,
                    lien_familial,
                    email,
                    telephone_principal,
                    telephone_secondaire,
                    participant_ateliers,
                    responsable_legal,
                    autorite_parentale,
                    payeur,
                    recoit_emails,
                    recoit_sms,
                    recoit_factures,
                    droit_image,
                    rgpd_accepte
                ) VALUES(
                    :famille_id,
                    :client_source_id,
                    1,
                    :civilite,
                    :nom,
                    :prenom,
                    :date_naissance,
                    'titulaire',
                    :email,
                    :telephone_principal,
                    :telephone_secondaire,
                    :participant_ateliers,
                    1,
                    1,
                    1,
                    1,
                    1,
                    1,
                    'non_renseigne',
                    :rgpd_accepte
                )"
            );

            $stmt->execute([
                'famille_id' => $familleId,
                'client_source_id' => $clientId,
                'civilite' => $data['civilite'],
                'nom' => $data['nom'],
                'prenom' => $data['prenom'],
                'date_naissance' => $data['date_naissance'] ?: date('Y-m-d'),
                'email' => $data['email'] ?: null,
                'telephone_principal' => $data['telephone_principal'] ?: null,
                'telephone_secondaire' => $data['telephone_secondaire'] ?: null,
                'participant_ateliers' => $data['client_participant'],
                'rgpd_accepte' => $data['rgpd_accepte'],
            ]);

            $invitation = null;
            if (!empty($data['create_family_account'])) {
                $invitation = (new \App\Services\PortalAccountService())->provisionFamilyAccount(
                    $pdo,
                    (int)$data['organisation_id'],
                    $clientId,
                    $familleId,
                    (string)$data['nom'],
                    (string)$data['prenom'],
                    (string)$data['email'],
                    $userId
                );
            }

            $pdo->commit();
            return ['client_id'=>$clientId,'invitation'=>$invitation];
        } catch (Throwable $error) {
            $pdo->rollBack();
            throw $error;
        }
    }

    public static function addMember(int $clientId, array $data): int
    {
        $client = self::find($clientId);

        if (!$client || empty($client['famille_id'])) {
            throw new \RuntimeException('Famille introuvable.');
        }

        $stmt = Database::connection()->prepare(
            "INSERT INTO membres_famille(
                famille_id,
                est_client_titulaire,
                civilite,
                nom,
                prenom,
                date_naissance,
                lien_familial,
                email,
                telephone_principal,
                telephone_secondaire,
                participant_ateliers,
                responsable_legal,
                autorite_parentale,
                payeur,
                recoit_emails,
                recoit_sms,
                recoit_factures,
                droit_image,
                rgpd_accepte,
                notes
            ) VALUES(
                :famille_id,
                0,
                :civilite,
                :nom,
                :prenom,
                :date_naissance,
                :lien_familial,
                :email,
                :telephone_principal,
                :telephone_secondaire,
                :participant_ateliers,
                :responsable_legal,
                :autorite_parentale,
                :payeur,
                :recoit_emails,
                :recoit_sms,
                :recoit_factures,
                :droit_image,
                :rgpd_accepte,
                :notes
            )"
        );

        $stmt->execute([
            'famille_id' => $client['famille_id'],
            'civilite' => $data['civilite'],
            'nom' => $data['nom'],
            'prenom' => $data['prenom'],
            'date_naissance' => $data['date_naissance'],
            'lien_familial' => $data['lien_familial'],
            'email' => $data['email'] ?: null,
            'telephone_principal' => $data['telephone_principal'] ?: null,
            'telephone_secondaire' => $data['telephone_secondaire'] ?: null,
            'participant_ateliers' => $data['participant_ateliers'],
            'responsable_legal' => $data['responsable_legal'],
            'autorite_parentale' => $data['autorite_parentale'],
            'payeur' => $data['payeur'],
            'recoit_emails' => $data['recoit_emails'],
            'recoit_sms' => $data['recoit_sms'],
            'recoit_factures' => $data['recoit_factures'],
            'droit_image' => $data['droit_image'],
            'rgpd_accepte' => $data['rgpd_accepte'],
            'notes' => $data['notes'] ?: null,
        ]);

        return (int)Database::connection()->lastInsertId();
    }

    public static function update(int $clientId, array $data, int $userId): void
    {
        $pdo = Database::connection();
        $old = self::find($clientId);
        if (!$old) throw new \RuntimeException('Adhérent introuvable.');
        $pdo->beginTransaction();
        try {
            $fields = ['civilite','nom','prenom','date_naissance','email','telephone_principal','telephone_secondaire','adresse_ligne1','adresse_ligne2','code_postal','ville','pays','rgpd_accepte','notes'];
            $set=[]; $params=['id'=>$clientId,'updated_by'=>$userId];
            foreach ($fields as $field) { $set[]="$field=:$field"; $params[$field]=($data[$field] ?? '') !== '' ? $data[$field] : null; }
            $params['pays']=$data['pays'] ?: 'France';
            $params['rgpd_accepte']=(int)$data['rgpd_accepte'];
            $stmt=$pdo->prepare('UPDATE clients SET '.implode(',',$set).', updated_by=:updated_by WHERE id=:id');
            $stmt->execute($params);
            $stmt=$pdo->prepare("UPDATE membres_famille m INNER JOIN familles f ON f.id=m.famille_id SET m.civilite=:civilite,m.nom=:nom,m.prenom=:prenom,m.date_naissance=:date_naissance,m.email=:email,m.telephone_principal=:telephone_principal,m.telephone_secondaire=:telephone_secondaire,m.rgpd_accepte=:rgpd,m.participant_ateliers=:participant_ateliers WHERE f.client_id=:id AND m.est_client_titulaire=1 AND m.deleted_at IS NULL");
            $stmt->execute(['civilite'=>$data['civilite'],'nom'=>$data['nom'],'prenom'=>$data['prenom'],'date_naissance'=>$data['date_naissance'] ?: '1900-01-01','email'=>$data['email'] ?: null,'telephone_principal'=>$data['telephone_principal'] ?: null,'telephone_secondaire'=>$data['telephone_secondaire'] ?: null,'rgpd'=>(int)$data['rgpd_accepte'],'participant_ateliers'=>(int)($data['client_participant'] ?? 0),'id'=>$clientId]);
            $new=array_intersect_key($data,array_flip($fields));
            $stmt=$pdo->prepare('INSERT INTO adherent_modification_audit(organisation_id,client_id,user_id,old_values,new_values) VALUES(:organisation_id,:client_id,:user_id,:old_values,:new_values)');
            $stmt->execute(['organisation_id'=>(int)$old['organisation_id'],'client_id'=>$clientId,'user_id'=>$userId,'old_values'=>json_encode(array_intersect_key($old,array_flip($fields)),JSON_UNESCAPED_UNICODE),'new_values'=>json_encode($new,JSON_UNESCAPED_UNICODE)]);
            $pdo->commit();
        } catch (Throwable $error) { $pdo->rollBack(); throw $error; }
    }

    private static function generateCode(
        int $organisationId,
        string $nom,
        string $prenom
    ): string {
        $pdo = Database::connection();

        $stmt = $pdo->prepare(
            "CALL cda_generate_unique_client_code(
                :organisation_id,
                :nom,
                :prenom,
                @generated_code
            )"
        );

        $stmt->execute([
            'organisation_id' => $organisationId,
            'nom' => $nom,
            'prenom' => $prenom,
        ]);

        $stmt->closeCursor();

        return (string)$pdo
            ->query('SELECT @generated_code')
            ->fetchColumn();
    }
}
