<?php
class Database {
    private $db;
    
    public function __construct() {
        try {
            $this->db = new PDO('sqlite:' . __DIR__ . '/../database/store.db');
            $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $this->initDatabase();
            $this->updateExistingTables();
        } catch(PDOException $e) {
            die("خطأ في الاتصال بقاعدة البيانات: " . $e->getMessage());
        }
    }

    private function initDatabase() {
        // إنشاء جدول المستخدمين
        $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 DEFAULT 'user',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )");

        // إنشاء جدول المنتجات
        $this->db->exec("CREATE TABLE IF NOT EXISTS products (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            price REAL NOT NULL,
            quantity INTEGER NOT NULL,
            expiry_date DATE,
            image_path TEXT,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )");

        // إنشاء جدول المبيعات
        $this->db->exec("CREATE TABLE IF NOT EXISTS sales (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            product_id INTEGER,
            quantity INTEGER NOT NULL,
            price REAL NOT NULL,
            sale_date DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (product_id) REFERENCES products(id)
        )");

        // إنشاء جدول الزبائن
        $this->db->exec("CREATE TABLE IF NOT EXISTS customers (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            phone TEXT,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )");

        // إنشاء جدول حركات المخزون
        $this->db->exec("CREATE TABLE IF NOT EXISTS stock_movements (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            product_id INTEGER NOT NULL,
            quantity INTEGER NOT NULL,
            type TEXT NOT NULL CHECK(type IN ('in', 'out')),
            reason TEXT,
            movement_date DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (product_id) REFERENCES products(id)
        )");

        // إنشاء جدول الديون
        $this->db->exec("CREATE TABLE IF NOT EXISTS debts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            customer_id INTEGER,
            amount REAL NOT NULL,
            paid REAL DEFAULT 0,
            date DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (customer_id) REFERENCES customers(id)
        )");
    }

    private function updateExistingTables() {
        try {
            // Vérifier si la colonne role existe dans la table users
            $stmt = $this->db->query("PRAGMA table_info(users)");
            $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
            $hasRoleColumn = false;
            
            foreach ($columns as $column) {
                if ($column['name'] === 'role') {
                    $hasRoleColumn = true;
                    break;
                }
            }
            
            // Ajouter la colonne role si elle n'existe pas
            if (!$hasRoleColumn) {
                $this->db->exec("ALTER TABLE users ADD COLUMN role TEXT DEFAULT 'user'");
                
                // Définir le premier utilisateur comme administrateur
                $this->db->exec("UPDATE users SET role = 'admin' WHERE id = (SELECT MIN(id) FROM users)");
            }
        } catch(PDOException $e) {
            // Ignorer les erreurs silencieusement
        }
    }

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

    public function resetDatabase() {
        $this->db->exec("DELETE FROM debts");
        $this->db->exec("DELETE FROM sales");
        $this->db->exec("DELETE FROM customers");
        $this->db->exec("DELETE FROM products");
        $this->db->exec("DELETE FROM users");
        
        // Réinitialiser les compteurs d'auto-incrémentation
        $this->db->exec("DELETE FROM sqlite_sequence");
    }

    public function updateUsersTable() {
        try {
            // Vérifier si la colonne role existe
            $stmt = $this->db->query("PRAGMA table_info(users)");
            $columns = $stmt->fetchAll();
            $hasRoleColumn = false;
            foreach ($columns as $column) {
                if ($column['name'] == 'role') {
                    $hasRoleColumn = true;
                    break;
                }
            }
            if (!$hasRoleColumn) {
                // Ajouter la colonne role
                $this->db->exec("ALTER TABLE users ADD COLUMN role TEXT DEFAULT 'user'");
                
                // Définir le premier utilisateur comme admin
                $this->db->exec("UPDATE users SET role = 'admin' ORDER BY id LIMIT 1");
            }
        } catch(PDOException $e) {
            // Ignorer les erreurs silencieusement
        }
    }
}
?>
