<?php
declare(strict_types=1);

namespace App\Services;

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

final class AdherentAnnualService
{
    public static function activeYear(int $organisationId): ?array
    {
        $stmt = Database::connection()->prepare(
            "SELECT id, organisation_id, libelle, date_debut, date_fin, active, inscriptions_ouvertes
             FROM annees_scolaires
             WHERE organisation_id = :organisation_id
               AND active = 1
             ORDER BY date_debut DESC
             LIMIT 1"
        );
        $stmt->execute(['organisation_id' => $organisationId]);
        return $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
    }

    public static function annualFiles(int $clientId): array
    {
        $stmt = Database::connection()->prepare(
            "SELECT d.*, a.libelle AS annee_libelle, a.date_debut, a.date_fin,
                    f.id AS facturation_item_id, f.libelle AS facturation_libelle,
                    f.montant_ttc, f.statut AS facturation_statut
             FROM cda_dossiers_adherents_annuels d
             INNER JOIN annees_scolaires a ON a.id = d.annee_scolaire_id
             LEFT JOIN cda_facturation_a_preparer f
                    ON f.dossier_annuel_id = d.id
                   AND f.type_element = 'adhesion_annuelle'
                   AND f.deleted_at IS NULL
             WHERE d.client_id = :client_id
               AND d.deleted_at IS NULL
             ORDER BY a.date_debut DESC"
        );
        $stmt->execute(['client_id' => $clientId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public static function activate(int $clientId, int $organisationId, int $userId): array
    {
        $year = self::activeYear($organisationId);
        if (!$year) {
            throw new RuntimeException('Aucune année d’action active. Activez d’abord une année scolaire dans les paramètres.');
        }

        $pdo = Database::connection();
        $pdo->beginTransaction();

        try {
            // Verrouille toujours l'adhérent, même lorsqu'aucun dossier annuel
            // n'existe encore. Deux clics simultanés sont ainsi sérialisés.
            $stmt = $pdo->prepare(
                "SELECT id
                 FROM clients
                 WHERE id = :client_id
                   AND organisation_id = :organisation_id
                   AND deleted_at IS NULL
                 FOR UPDATE"
            );
            $stmt->execute([
                'client_id' => $clientId,
                'organisation_id' => $organisationId,
            ]);
            if (!$stmt->fetchColumn()) {
                throw new RuntimeException('Adhérent introuvable.');
            }

            $stmt = $pdo->prepare(
                "SELECT id, statut
                 FROM cda_dossiers_adherents_annuels
                 WHERE client_id = :client_id
                   AND annee_scolaire_id = :annee_id
                   AND deleted_at IS NULL
                 LIMIT 1"
            );
            $stmt->execute([
                'client_id' => $clientId,
                'annee_id' => (int)$year['id'],
            ]);
            $existing = $stmt->fetch(PDO::FETCH_ASSOC);
            $newlyActivated = false;

            if ($existing) {
                $dossierId = (int)$existing['id'];
                if (($existing['statut'] ?? '') !== 'actif') {
                    $pdo->prepare(
                        "UPDATE cda_dossiers_adherents_annuels
                         SET statut='actif', activated_at=COALESCE(activated_at, NOW()), updated_by=:user_id
                         WHERE id=:id"
                    )->execute(['user_id' => $userId ?: null, 'id' => $dossierId]);
                    $newlyActivated = true;
                }
            } else {
                $stmt = $pdo->prepare(
                    "INSERT INTO cda_dossiers_adherents_annuels(
                        organisation_id, client_id, annee_scolaire_id, statut,
                        activated_at, created_by, updated_by
                    ) VALUES(
                        :organisation_id, :client_id, :annee_id, 'actif',
                        NOW(), :created_by, :updated_by
                    )"
                );
                $stmt->execute([
                    'organisation_id' => $organisationId,
                    'client_id' => $clientId,
                    'annee_id' => (int)$year['id'],
                    'created_by' => $userId ?: null,
                    'updated_by' => $userId ?: null,
                ]);
                $dossierId = (int)$pdo->lastInsertId();
                $newlyActivated = true;
            }

            $stmt = $pdo->prepare(
                "SELECT id, statut
                 FROM cda_facturation_a_preparer
                 WHERE dossier_annuel_id = :dossier_id
                   AND type_element = 'adhesion_annuelle'
                   AND deleted_at IS NULL
                 LIMIT 1"
            );
            $stmt->execute(['dossier_id' => $dossierId]);
            $billing = $stmt->fetch(PDO::FETCH_ASSOC);

            if (!$billing) {
                $stmt = $pdo->prepare(
                    "INSERT INTO cda_facturation_a_preparer(
                        organisation_id, client_id, dossier_annuel_id, annee_scolaire_id,
                        type_element, libelle, montant_ttc, statut, source_module,
                        created_by, updated_by
                    ) VALUES(
                        :organisation_id, :client_id, :dossier_id, :annee_id,
                        'adhesion_annuelle', :libelle, NULL, 'a_facturer', 'adherents',
                        :created_by, :updated_by
                    )"
                );
                $stmt->execute([
                    'organisation_id' => $organisationId,
                    'client_id' => $clientId,
                    'dossier_id' => $dossierId,
                    'annee_id' => (int)$year['id'],
                    'libelle' => 'Adhésion ' . (string)$year['libelle'],
                    'created_by' => $userId ?: null,
                    'updated_by' => $userId ?: null,
                ]);
                $billingId = (int)$pdo->lastInsertId();
                $billingStatus = 'a_facturer';
                $billingCreated = true;
            } else {
                $billingId = (int)$billing['id'];
                $billingStatus = (string)$billing['statut'];
                $billingCreated = false;
            }

            if ($newlyActivated) {
                $stmt = $pdo->prepare(
                    "INSERT INTO cda_adherent_timeline(
                        organisation_id, client_id, dossier_annuel_id,
                        event_code, title, description, created_by
                     )
                     SELECT
                        :organisation_id, :client_id, :dossier_id,
                        'annual_file.activated', :title, :description, :created_by
                     WHERE NOT EXISTS (
                        SELECT 1
                        FROM cda_adherent_timeline
                        WHERE dossier_annuel_id = :dossier_id_check
                          AND event_code = 'annual_file.activated'
                     )"
                );
                $stmt->execute([
                    'organisation_id' => $organisationId,
                    'client_id' => $clientId,
                    'dossier_id' => $dossierId,
                    'title' => 'Dossier annuel activé',
                    'description' => 'Activation du dossier ' . (string)$year['libelle'] . ' et transmission de l’adhésion au module Facturation.',
                    'created_by' => $userId ?: null,
                    'dossier_id_check' => $dossierId,
                ]);
            }

            $pdo->commit();

            return [
                'dossier_id' => $dossierId,
                'annee' => $year,
                'billing_id' => $billingId,
                'billing_status' => $billingStatus,
                'newly_activated' => $newlyActivated,
                'billing_created' => $billingCreated,
            ];
        } catch (Throwable $e) {
            $pdo->rollBack();
            throw $e;
        }
    }

    public static function timeline(int $clientId, int $limit = 30): array
    {
        $limit = max(1, min(100, $limit));
        $stmt = Database::connection()->prepare(
            "SELECT t.*, a.libelle AS annee_libelle
             FROM cda_adherent_timeline t
             LEFT JOIN cda_dossiers_adherents_annuels d ON d.id = t.dossier_annuel_id
             LEFT JOIN annees_scolaires a ON a.id = d.annee_scolaire_id
             WHERE t.client_id = :client_id
             ORDER BY t.created_at DESC
             LIMIT {$limit}"
        );
        $stmt->execute(['client_id' => $clientId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public static function dashboard(int $organisationId): array
    {
        $pdo = Database::connection();
        $year = self::activeYear($organisationId);
        if (!$year) {
            return ['active_year' => null, 'active_files' => 0, 'pending_memberships' => 0, 'incomplete_files' => 0];
        }

        $stmt = $pdo->prepare(
            "SELECT
                COUNT(DISTINCT d.id) AS active_files,
                SUM(CASE WHEN f.statut='a_facturer' THEN 1 ELSE 0 END) AS pending_memberships,
                SUM(CASE WHEN c.rgpd_accepte=0 OR c.email IS NULL OR c.telephone_principal IS NULL THEN 1 ELSE 0 END) AS incomplete_files
             FROM cda_dossiers_adherents_annuels d
             INNER JOIN clients c ON c.id=d.client_id AND c.deleted_at IS NULL
             LEFT JOIN cda_facturation_a_preparer f
                    ON f.dossier_annuel_id=d.id
                   AND f.type_element='adhesion_annuelle'
                   AND f.deleted_at IS NULL
             WHERE d.organisation_id=:organisation_id
               AND d.annee_scolaire_id=:annee_id
               AND d.deleted_at IS NULL
               AND d.statut='actif'"
        );
        $stmt->execute(['organisation_id' => $organisationId, 'annee_id' => (int)$year['id']]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];

        return [
            'active_year' => $year,
            'active_files' => (int)($row['active_files'] ?? 0),
            'pending_memberships' => (int)($row['pending_memberships'] ?? 0),
            'incomplete_files' => (int)($row['incomplete_files'] ?? 0),
        ];
    }
}
