<?php
session_start();

define('DB_FILE', __DIR__ . '/../database/database.sqlite');
define('UPLOADS_DIR', __DIR__ . '/../uploads');

// Create database directory if it doesn't exist
$dbDir = dirname(DB_FILE);
if (!file_exists($dbDir)) {
    mkdir($dbDir, 0755, true);
}

// Ensure database directory is writable
if (!is_writable($dbDir)) {
    chmod($dbDir, 0755);
}

// Create database connection
function getDB() {
    static $db = null;
    if ($db === null) {
        try {
            // Vérification détaillée des permissions
            $dbFile = DB_FILE;
            $dbDir = dirname($dbFile);
            
            error_log("Checking database directory: " . $dbDir);
            if (!file_exists($dbDir)) {
                error_log("Creating database directory");
                if (!@mkdir($dbDir, 0777, true)) {
                    error_log("Failed to create database directory");
                    throw new Exception('Cannot create database directory');
                }
            }
            
            // Vérifier les permissions du dossier
            if (!is_writable($dbDir)) {
                error_log("Attempting to make database directory writable");
                @chmod($dbDir, 0777);
                if (!is_writable($dbDir)) {
                    error_log("Database directory is still not writable after chmod");
                    throw new Exception('Database directory is not writable');
                }
            }
            
            // Si le fichier existe, vérifier ses permissions
            if (file_exists($dbFile)) {
                if (!is_writable($dbFile)) {
                    error_log("Attempting to make database file writable");
                    @chmod($dbFile, 0666);
                    if (!is_writable($dbFile)) {
                        error_log("Database file is not writable after chmod");
                        throw new Exception('Database file exists but is not writable');
                    }
                }
            }
            
            error_log("Attempting to connect to database: " . $dbFile);
            $db = new SQLite3($dbFile);
            $db->enableExceptions(true);
            
            // Configuration minimale
            $db->exec('PRAGMA foreign_keys = ON');
            $db->exec('PRAGMA synchronous = NORMAL');
            $db->exec('PRAGMA journal_mode = TRUNCATE');
            
            // Test de connexion
            $db->query('SELECT 1');
            error_log("Database connection successful");
            
        } catch (Exception $e) {
            error_log("Database error: " . $e->getMessage());
            throw new Exception('Database error: ' . $e->getMessage());
        }
    }
    return $db;
}

// Initialize database
function initializeDB() {
    try {
        $db = getDB();
        $db->exec('BEGIN TRANSACTION');
        
        // Load and execute schema
        $schema = file_get_contents(__DIR__ . '/../database/schema.sql');
        if ($schema === false) {
            throw new Exception('Could not read schema file');
        }
        $db->exec($schema);

        // Create contributions table
        $db->exec("
            CREATE TABLE IF NOT EXISTS contributions (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                amount DECIMAL(10,2) NOT NULL,
                reason TEXT NOT NULL,
                contribution_date DATE NOT NULL,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        ");

        // Check if admin user exists
        $stmt = $db->prepare('SELECT COUNT(*) as count FROM users WHERE username = :username');
        $stmt->bindValue(':username', 'admin', SQLITE3_TEXT);
        $result = $stmt->execute()->fetchArray();
        
        if ($result['count'] == 0) {
            // Create default admin user with proper password hashing
            $stmt = $db->prepare('INSERT INTO users (username, password, created_at) VALUES (:username, :password, :created_at)');
            $stmt->bindValue(':username', 'admin', SQLITE3_TEXT);
            $stmt->bindValue(':password', password_hash('admin123', PASSWORD_DEFAULT), SQLITE3_TEXT);
            $stmt->bindValue(':created_at', date('Y-m-d H:i:s'), SQLITE3_TEXT);
            $stmt->execute();

            // Insert default settings
            $stmt = $db->prepare('INSERT INTO settings (association_name, consumption_period, fixed_fees, penalty_rate, created_at) 
                                VALUES (:name, :period, :fees, :rate, :created_at)');
            $stmt->bindValue(':name', 'جمعية توزيع الماء', SQLITE3_TEXT);
            $stmt->bindValue(':period', 'monthly', SQLITE3_TEXT);
            $stmt->bindValue(':fees', 10.0, SQLITE3_FLOAT);
            $stmt->bindValue(':rate', 5.0, SQLITE3_FLOAT);
            $stmt->bindValue(':created_at', date('Y-m-d H:i:s'), SQLITE3_TEXT);
            $stmt->execute();

            // Insert default consumption tiers
            $tiers = [
                ['0', '10', 3.0],
                ['11', '20', 5.0],
                ['21', '30', 7.0],
                ['31', null, 9.0]
            ];

            $stmt = $db->prepare('INSERT INTO consumption_tiers (start_range, end_range, price_per_unit) VALUES (:start, :end, :price)');
            foreach ($tiers as $tier) {
                $stmt->bindValue(':start', $tier[0], SQLITE3_TEXT);
                $stmt->bindValue(':end', $tier[1], SQLITE3_TEXT);
                $stmt->bindValue(':price', $tier[2], SQLITE3_FLOAT);
                $stmt->execute();
            }
        }
        
        $db->exec('COMMIT');
    } catch (Exception $e) {
        try {
            $db->exec('ROLLBACK');
        } catch (Exception $rollbackError) {
            // Log rollback error but throw original error
            error_log('Rollback failed: ' . $rollbackError->getMessage());
        }
        throw $e;
    }
}

// Create uploads directory with proper permissions
if (!file_exists(UPLOADS_DIR)) {
    if (!mkdir(UPLOADS_DIR, 0755, true)) {
        throw new Exception('Failed to create uploads directory');
    }
}

// Set error reporting
error_reporting(E_ALL);
ini_set('display_errors', 0);
ini_set('log_errors', 1);
ini_set('error_log', __DIR__ . '/../logs/php_errors.log');

// Create logs directory if it doesn't exist
if (!file_exists(__DIR__ . '/../logs')) {
    mkdir(__DIR__ . '/../logs', 0755, true);
}

// Set default timezone
date_default_timezone_set('Africa/Casablanca');

// Initialize database
initializeDB();
