<?php

class BiensModel {
    public const DB_CONFIG = [
        'dsn' => 'mysql:host=localhost;dbname=dykqnvjf_gespes360;charset=utf8',
        'user' => 'dykqnvjf_gespes360',
        'pass' => 'dykqnvjf_gespes360',
        'options' => [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false
        ]
    ];

    public const TABLE_NAME = 'biens';
    public const PRIMARY_KEY = 'id_bien';
    public const JOIN_TABLES = [
        'epargne_ct' => ['join_column' => 'id_epargne_ct', 'user_column' => 'id_utilisateur'],
        'dettes' => ['join_column' => 'id_dette', 'user_column' => 'id_contracteur']
    ];

    public const VALID_TYPES = [
        'Résidence principale', 'Résidence secondaire', 'Immeuble locatif',
        'Véhicule personnel', 'Action cotée', 'Fonds d\'investissement',
        'Contrat d\'assurance-vie', 'Fonds de commerce', 'Droit d\'auteur',
        'Epoux/epouse'
    ];

    public const VALID_CATEGORIES = ['immobilier', 'mobilier', 'financier', 'professionnel', 'incorporel'];
    public const VALID_STATUTS = ['actif', 'passif', 'vendu', 'non actif'];

    public $pdo;
    public static $instance = null;

    public function __construct() {
        $this->connect();
    }

    public function connect(): void {
        try {
            $this->pdo = new PDO(
                self::DB_CONFIG['dsn'],
                self::DB_CONFIG['user'],
                self::DB_CONFIG['pass'],
                self::DB_CONFIG['options']
            );
        } catch (PDOException $e) {
            throw new RuntimeException("Erreur de connexion à la base de données: " . $e->getMessage());
        }
    }

    public static function getInstance(): self {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    public function getAll(): array {
        return $this->query("SELECT * FROM " . self::TABLE_NAME . " ORDER BY date_enregistrement DESC");
    }

    public function getById(int $id): ?array {
        return $this->fetch(
            "SELECT * FROM " . self::TABLE_NAME . " WHERE " . self::PRIMARY_KEY . " = ?", 
            [$id]
        );
    }

    public function getByUser(int $userId): array {
        $join = self::JOIN_TABLES['epargne_ct'];
        return $this->fetchAll(
            "SELECT b.* FROM " . self::TABLE_NAME . " b
             JOIN epargne_ct e ON b.{$join['join_column']} = e.{$join['join_column']}
             WHERE e.{$join['user_column']} = ?
             ORDER BY b.date_enregistrement DESC",
            [$userId]
        );
    }

    public function getEpargnesByUser(int $userId): array {
        $table = 'epargne_ct';
        $join = self::JOIN_TABLES[$table];
        return $this->fetchAll(
            "SELECT * FROM {$table} WHERE {$join['user_column']} = ? ORDER BY date_enregistrement DESC",
            [$userId]
        );
    }

    public function getDettesByUser(int $userId): array {
        $table = 'dettes';
        $join = self::JOIN_TABLES[$table];
        return $this->fetchAll(
            "SELECT * FROM {$table} WHERE {$join['user_column']} = ? ORDER BY date_enregistrement DESC",
            [$userId]
        );
    }

    public function insert(array $data): bool {
        $fields = $this->getBienFields();
        $values = $this->prepareValues($data, $fields);

        $sql = "INSERT INTO " . self::TABLE_NAME . " 
                (" . implode(', ', $fields) . ", date_enregistrement, date_mise_a_jour) 
                VALUES (" . implode(', ', array_fill(0, count($fields), '?')) . ", NOW(), NOW())";

        return $this->execute($sql, $values);
    }

    public function update(int $id, array $data): bool {
        $fields = $this->getBienFields();
        $values = $this->prepareValues($data, $fields);
        $values[] = $id;

        $sql = "UPDATE " . self::TABLE_NAME . " 
                SET " . implode(' = ?, ', $fields) . " = ?, date_mise_a_jour = NOW() 
                WHERE " . self::PRIMARY_KEY . " = ?";

        return $this->execute($sql, $values);
    }

    public function delete(int $id): bool {
        return $this->execute(
            "DELETE FROM " . self::TABLE_NAME . " WHERE " . self::PRIMARY_KEY . " = ?",
            [$id]
        );
    }

    public function validateTypeBien(string $type): bool {
        return in_array($type, self::VALID_TYPES);
    }

    public function validateCategorieBien(string $categorie): bool {
        return in_array($categorie, self::VALID_CATEGORIES);
    }

    public function validateStatut(string $statut): bool {
        return in_array($statut, self::VALID_STATUTS);
    }

    public function getBienFields(): array {
        return [
            'id_epargne_ct', 'id_dette', 'libelle_bien', 'type_bien', 'description',
            'source_financement', 'responsable_gestion', 'frequence_utilisation',
            'montant_acquisition', 'date_acquisition', 'valeur_actuelle',
            'taux_depreciation', 'historique_valeurs', 'benefice_produit', 'statut',
            'categorie_bien'
        ];
    }

    public function prepareValues(array $data, array $fields): array {
        return array_map(function($field) use ($data) {
            $value = $data[$field] ?? null;
            
            // Conversion des valeurs numériques
            if (in_array($field, ['montant_acquisition', 'valeur_actuelle', 'taux_depreciation', 'benefice_produit'])) {
                return (float)$value;
            }
            
            // Nettoyage des chaînes
            if (is_string($value)) {
                return htmlspecialchars($value);
            }
            
            return $value;
        }, $fields);
    }

    public function query(string $sql): array {
        $stmt = $this->pdo->query($sql);
        return $stmt->fetchAll();
    }

    public function fetch(string $sql, array $params): ?array {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetch() ?: null;
    }

    public function fetchAll(string $sql, array $params = []): array {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    public function execute(string $sql, array $params = []): bool {
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute($params);
    }
}
?>