<?php
namespace App\Models;

use App\Core\Database;
use PDO;
use Throwable;

final class Family
{
    public static function search(string $query = ''): array
    {
        $pdo = Database::connection();
        $params = [];
        $where = 'f.deleted_at IS NULL';

        if ($query !== '') {
            $where .= ' AND (
                f.nom_famille LIKE :q
                OR f.reference_famille LIKE :q
                OR f.email_principal LIKE :q
                OR f.telephone_principal LIKE :q
                OR f.ville LIKE :q
                OR EXISTS (
                    SELECT 1 FROM responsables_famille r
                    WHERE r.famille_id = f.id
                      AND r.deleted_at IS NULL
                      AND (r.nom LIKE :q OR r.prenom LIKE :q OR r.email LIKE :q OR r.telephone LIKE :q)
                )
            )';
            $params['q'] = '%' . $query . '%';
        }

        $sql = "SELECT
                    f.*,
                    (SELECT COUNT(*) FROM adherents a
                     WHERE a.famille_id = f.id AND a.deleted_at IS NULL) AS nb_adherents,
                    (SELECT CONCAT(r.prenom, ' ', r.nom)
                     FROM responsables_famille r
                     WHERE r.famille_id = f.id
                       AND r.deleted_at IS NULL
                     ORDER BY r.responsable_principal DESC, r.id ASC
                     LIMIT 1) AS responsable_principal
                FROM familles f
                WHERE {$where}
                ORDER BY f.nom_famille ASC, f.id DESC";

        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    public static function find(int $id): ?array
    {
        $stmt = Database::connection()->prepare(
            'SELECT * FROM familles WHERE id = :id AND deleted_at IS NULL LIMIT 1'
        );
        $stmt->execute(['id' => $id]);
        return $stmt->fetch() ?: null;
    }

    public static function responsible(int $familyId): ?array
    {
        $stmt = Database::connection()->prepare(
            'SELECT * FROM responsables_famille
             WHERE famille_id = :family_id AND deleted_at IS NULL
             ORDER BY responsable_principal DESC, id ASC LIMIT 1'
        );
        $stmt->execute(['family_id' => $familyId]);
        return $stmt->fetch() ?: null;
    }

    public static function members(int $familyId): array
    {
        $stmt = Database::connection()->prepare(
            'SELECT id, reference_adherent, nom, prenom, date_naissance, statut
             FROM adherents
             WHERE famille_id = :family_id AND deleted_at IS NULL
             ORDER BY date_naissance ASC, nom, prenom'
        );
        $stmt->execute(['family_id' => $familyId]);
        return $stmt->fetchAll();
    }

    public static function duplicateExists(string $name, ?string $email, ?string $phone, ?int $ignoreId = null): bool
    {
        $sql = 'SELECT 1 FROM familles
                WHERE deleted_at IS NULL
                  AND nom_famille = :name
                  AND (
                      (:email <> "" AND email_principal = :email)
                      OR (:phone <> "" AND telephone_principal = :phone)
                  )';
        $params = [
            'name' => $name,
            'email' => $email ?? '',
            'phone' => $phone ?? '',
        ];

        if ($ignoreId !== null) {
            $sql .= ' AND id <> :ignore_id';
            $params['ignore_id'] = $ignoreId;
        }

        $sql .= ' LIMIT 1';
        $stmt = Database::connection()->prepare($sql);
        $stmt->execute($params);
        return (bool)$stmt->fetchColumn();
    }

    public static function create(array $data, ?array $responsible, int $userId): int
    {
        $pdo = Database::connection();
        $pdo->beginTransaction();

        try {
            $reference = self::nextReference();

            $stmt = $pdo->prepare(
                'INSERT INTO familles (
                    organisation_id, reference_famille, nom_famille,
                    adresse_ligne1, adresse_ligne2, code_postal, ville, pays,
                    telephone_principal, telephone_secondaire,
                    email_principal, email_secondaire,
                    situation_familiale, quotient_familial,
                    autorise_contact_telephone, autorise_contact_email, autorise_contact_sms,
                    rgpd_accepte, rgpd_accepte_at, notes, created_by, updated_by
                ) VALUES (
                    :organisation_id, :reference_famille, :nom_famille,
                    :adresse_ligne1, :adresse_ligne2, :code_postal, :ville, :pays,
                    :telephone_principal, :telephone_secondaire,
                    :email_principal, :email_secondaire,
                    :situation_familiale, :quotient_familial,
                    :autorise_contact_telephone, :autorise_contact_email, :autorise_contact_sms,
                    :rgpd_accepte, :rgpd_accepte_at, :notes, :created_by, :updated_by
                )'
            );

            $stmt->execute([
                'organisation_id' => $data['organisation_id'],
                'reference_famille' => $reference,
                'nom_famille' => $data['nom_famille'],
                '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',
                'telephone_principal' => $data['telephone_principal'] ?: null,
                'telephone_secondaire' => $data['telephone_secondaire'] ?: null,
                'email_principal' => $data['email_principal'] ?: null,
                'email_secondaire' => $data['email_secondaire'] ?: null,
                'situation_familiale' => $data['situation_familiale'] ?: null,
                'quotient_familial' => $data['quotient_familial'] !== '' ? $data['quotient_familial'] : null,
                'autorise_contact_telephone' => $data['autorise_contact_telephone'],
                'autorise_contact_email' => $data['autorise_contact_email'],
                'autorise_contact_sms' => $data['autorise_contact_sms'],
                '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,
            ]);

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

            if ($responsible && ($responsible['nom'] !== '' || $responsible['prenom'] !== '')) {
                self::saveResponsible($familyId, $responsible);
            }

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

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

        try {
            $stmt = $pdo->prepare(
                'UPDATE familles SET
                    nom_famille = :nom_famille,
                    adresse_ligne1 = :adresse_ligne1,
                    adresse_ligne2 = :adresse_ligne2,
                    code_postal = :code_postal,
                    ville = :ville,
                    pays = :pays,
                    telephone_principal = :telephone_principal,
                    telephone_secondaire = :telephone_secondaire,
                    email_principal = :email_principal,
                    email_secondaire = :email_secondaire,
                    situation_familiale = :situation_familiale,
                    quotient_familial = :quotient_familial,
                    autorise_contact_telephone = :autorise_contact_telephone,
                    autorise_contact_email = :autorise_contact_email,
                    autorise_contact_sms = :autorise_contact_sms,
                    rgpd_accepte = :rgpd_accepte,
                    rgpd_accepte_at = CASE
                        WHEN :rgpd_accepte = 1 AND rgpd_accepte_at IS NULL THEN NOW()
                        WHEN :rgpd_accepte = 0 THEN NULL
                        ELSE rgpd_accepte_at
                    END,
                    notes = :notes,
                    updated_by = :updated_by
                 WHERE id = :id AND deleted_at IS NULL'
            );

            $stmt->execute([
                'id' => $id,
                'nom_famille' => $data['nom_famille'],
                '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',
                'telephone_principal' => $data['telephone_principal'] ?: null,
                'telephone_secondaire' => $data['telephone_secondaire'] ?: null,
                'email_principal' => $data['email_principal'] ?: null,
                'email_secondaire' => $data['email_secondaire'] ?: null,
                'situation_familiale' => $data['situation_familiale'] ?: null,
                'quotient_familial' => $data['quotient_familial'] !== '' ? $data['quotient_familial'] : null,
                'autorise_contact_telephone' => $data['autorise_contact_telephone'],
                'autorise_contact_email' => $data['autorise_contact_email'],
                'autorise_contact_sms' => $data['autorise_contact_sms'],
                'rgpd_accepte' => $data['rgpd_accepte'],
                'notes' => $data['notes'] ?: null,
                'updated_by' => $userId,
            ]);

            if ($responsible) {
                self::saveResponsible($id, $responsible);
            }

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

    private static function saveResponsible(int $familyId, array $data): void
    {
        $pdo = Database::connection();
        $existing = self::responsible($familyId);

        if ($existing) {
            $stmt = $pdo->prepare(
                'UPDATE responsables_famille SET
                    civilite = :civilite, nom = :nom, prenom = :prenom,
                    lien_avec_adherent = :lien, autorite_parentale = :autorite_parentale,
                    responsable_principal = 1, payeur_principal = :payeur_principal,
                    telephone = :telephone, email = :email
                 WHERE id = :id'
            );
            $stmt->execute([
                'id' => $existing['id'],
                'civilite' => $data['civilite'],
                'nom' => $data['nom'],
                'prenom' => $data['prenom'],
                'lien' => $data['lien'],
                'autorite_parentale' => $data['autorite_parentale'],
                'payeur_principal' => $data['payeur_principal'],
                'telephone' => $data['telephone'] ?: null,
                'email' => $data['email'] ?: null,
            ]);
            return;
        }

        $stmt = $pdo->prepare(
            'INSERT INTO responsables_famille (
                famille_id, civilite, nom, prenom, lien_avec_adherent,
                autorite_parentale, responsable_principal, payeur_principal,
                telephone, email
            ) VALUES (
                :famille_id, :civilite, :nom, :prenom, :lien,
                :autorite_parentale, 1, :payeur_principal, :telephone, :email
            )'
        );
        $stmt->execute([
            'famille_id' => $familyId,
            'civilite' => $data['civilite'],
            'nom' => $data['nom'],
            'prenom' => $data['prenom'],
            'lien' => $data['lien'],
            'autorite_parentale' => $data['autorite_parentale'],
            'payeur_principal' => $data['payeur_principal'],
            'telephone' => $data['telephone'] ?: null,
            'email' => $data['email'] ?: null,
        ]);
    }

    private static function nextReference(): string
    {
        $year = date('Y');
        $prefix = 'FAM-' . $year . '-';

        $stmt = Database::connection()->prepare(
            'SELECT reference_famille FROM familles
             WHERE reference_famille LIKE :prefix
             ORDER BY id DESC LIMIT 1'
        );
        $stmt->execute(['prefix' => $prefix . '%']);
        $last = $stmt->fetchColumn();

        $number = $last ? ((int)substr((string)$last, -6) + 1) : 1;
        return $prefix . str_pad((string)$number, 6, '0', STR_PAD_LEFT);
    }
}
