<?php
class Database {
    private $db;
    private $db_file = __DIR__ . "/../database/association.db";

    public function __construct() {
        try {
            $this->db = new PDO("sqlite:" . $this->db_file);
            $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            
            // Enable foreign keys
            $this->db->exec('PRAGMA foreign_keys = ON');

            // Create tables if they don't exist
            $this->createTables();
        } catch(PDOException $e) {
            die("Erreur de connexion à la base de données: " . $e->getMessage());
        }
    }

    private function createTables() {
        try {
            // Create users table
            $this->db->exec("CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                username TEXT UNIQUE NOT NULL,
                password TEXT NOT NULL,
                role TEXT NOT NULL,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )");

            // Add default admin user if not exists
            $stmt = $this->db->prepare("SELECT COUNT(*) FROM users WHERE username = 'admin'");
            $stmt->execute();
            if ($stmt->fetchColumn() == 0) {
                $hashedPassword = password_hash('admin123', PASSWORD_DEFAULT);
                $stmt = $this->db->prepare("INSERT INTO users (username, password, role) VALUES (?, ?, 'admin')");
                $stmt->execute(['admin', $hashedPassword]);
            }

            // Add default president user if not exists
            $stmt = $this->db->prepare("SELECT COUNT(*) FROM users WHERE username = 'president'");
            $stmt->execute();
            if ($stmt->fetchColumn() == 0) {
                $hashedPassword = password_hash('president123', PASSWORD_DEFAULT);
                $stmt = $this->db->prepare("INSERT INTO users (username, password, role) VALUES (?, ?, 'president')");
                $stmt->execute(['president', $hashedPassword]);
            }

            // Create members table
            $this->db->exec("CREATE TABLE IF NOT EXISTS members (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                full_name TEXT NOT NULL,
                role TEXT NOT NULL,
                phone TEXT,
                email TEXT,
                status TEXT DEFAULT 'active',
                join_date DATETIME DEFAULT CURRENT_TIMESTAMP
            )");

            // Create income_categories table
            $this->db->exec("CREATE TABLE IF NOT EXISTS income_categories (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                description TEXT
            )");

            // Create expense_categories table
            $this->db->exec("CREATE TABLE IF NOT EXISTS expense_categories (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                description TEXT
            )");

            // Add new columns to financial_transactions if they don't exist
            $columns = $this->db->query("PRAGMA table_info(financial_transactions)")->fetchAll(PDO::FETCH_ASSOC);
            $columnNames = array_column($columns, 'name');
            
            if (!in_array('account_type', $columnNames)) {
                $this->db->exec("ALTER TABLE financial_transactions ADD COLUMN account_type TEXT CHECK(account_type IN ('bank', 'cash'))");
            }
            if (!in_array('account_id', $columnNames)) {
                $this->db->exec("ALTER TABLE financial_transactions ADD COLUMN account_id INTEGER");
            }
            if (!in_array('transfer_to_type', $columnNames)) {
                $this->db->exec("ALTER TABLE financial_transactions ADD COLUMN transfer_to_type TEXT CHECK(transfer_to_type IN ('bank', 'cash'))");
            }
            if (!in_array('transfer_to_id', $columnNames)) {
                $this->db->exec("ALTER TABLE financial_transactions ADD COLUMN transfer_to_id INTEGER");
            }

            // Create financial_transactions table if not exists
            $this->db->exec("CREATE TABLE IF NOT EXISTS financial_transactions (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                type TEXT NOT NULL CHECK(type IN ('income', 'expense', 'transfer')),
                amount DECIMAL(10,2) NOT NULL,
                category_id INTEGER,
                description TEXT,
                transaction_date DATE NOT NULL,
                payment_method TEXT,
                account_type TEXT CHECK(account_type IN ('bank', 'cash')),
                account_id INTEGER,
                transfer_to_type TEXT CHECK(transfer_to_type IN ('bank', 'cash')),
                transfer_to_id INTEGER,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                created_by INTEGER,
                FOREIGN KEY(created_by) REFERENCES users(id),
                FOREIGN KEY(category_id) REFERENCES income_categories(id),
                FOREIGN KEY(category_id) REFERENCES expense_categories(id),
                FOREIGN KEY(account_id) REFERENCES bank_accounts(id) ON DELETE RESTRICT,
                FOREIGN KEY(account_id) REFERENCES cash_funds(id) ON DELETE RESTRICT
            )");

            // Create activities table
            $this->db->exec("CREATE TABLE IF NOT EXISTS activities (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                description TEXT,
                date DATE NOT NULL,
                time TIME,
                location TEXT,
                status TEXT DEFAULT 'planned',
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                created_by INTEGER,
                FOREIGN KEY(created_by) REFERENCES users(id)
            )");

            // Create assets table
            $this->db->exec("CREATE TABLE IF NOT EXISTS assets (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                description TEXT,
                acquisition_date DATE,
                value DECIMAL(10,2),
                status TEXT DEFAULT 'active',
                location TEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )");

            // Create association_settings table
            $this->db->exec("CREATE TABLE IF NOT EXISTS association_settings (
                id INTEGER PRIMARY KEY CHECK (id = 1),
                name TEXT NOT NULL,
                address TEXT,
                phone TEXT,
                email TEXT,
                logo_path TEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )");

            // Create bank_accounts table
            $this->db->exec("CREATE TABLE IF NOT EXISTS bank_accounts (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                bank_name TEXT NOT NULL,
                account_number TEXT NOT NULL,
                description TEXT,
                balance DECIMAL(10,2) DEFAULT 0,
                status TEXT DEFAULT 'active',
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )");

            // Create bank_transactions table
            $this->db->exec("CREATE TABLE IF NOT EXISTS bank_transactions (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                account_id INTEGER NOT NULL,
                type TEXT NOT NULL CHECK(type IN ('deposit', 'withdrawal')),
                amount DECIMAL(10,2) NOT NULL,
                description TEXT,
                reference_number TEXT,
                transaction_date DATE NOT NULL,
                created_by INTEGER,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY(account_id) REFERENCES bank_accounts(id),
                FOREIGN KEY(created_by) REFERENCES users(id)
            )");

            // Create cash_funds table
            $this->db->exec("CREATE TABLE IF NOT EXISTS cash_funds (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                description TEXT,
                balance DECIMAL(10,2) DEFAULT 0,
                status TEXT DEFAULT 'active',
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )");

        

            // Create cash_transactions table
            $this->db->exec("CREATE TABLE IF NOT EXISTS cash_transactions (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                fund_id INTEGER NOT NULL,
                type TEXT NOT NULL CHECK(type IN ('deposit', 'withdrawal')),
                amount DECIMAL(10,2) NOT NULL,
                description TEXT,
                reference_number TEXT,
                transaction_date DATE NOT NULL,
                created_by INTEGER,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY(fund_id) REFERENCES cash_funds(id),
                FOREIGN KEY(created_by) REFERENCES users(id)
            )");
        } catch(PDOException $e) {
            die("Erreur de création des tables: " . $e->getMessage());
        }
    }

    public function getConnection() {
        return $this->db;
    }
}
?>
