<?php
/**
 * TÄIUSTATUD database.php (LÕPLIK PARANDUS)
 */

require_once __DIR__ . '/config.php';

try {
    // Andmebaasi faili tee alamkausta andmekausta
    $db_path = BASE_PATH . '/data/estreka.db';
    $data_dir = dirname($db_path);
    
    if (!is_dir($data_dir)) {
        if (!mkdir($data_dir, 0755, true)) {
            throw new Exception('Ei suutnud luua andmete kausta: ' . $data_dir);
        }
    }
    
    $pdo = new PDO('sqlite:' . $db_path);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    $pdo->exec('PRAGMA foreign_keys = ON');
    
    // Tabelite loomine (täielikud ja korrektsed)
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            firstName TEXT NOT NULL,
            lastName TEXT NOT NULL,
            email TEXT UNIQUE NOT NULL,
            phone TEXT,
            birthDate DATE NOT NULL,
            username TEXT UNIQUE NOT NULL,
            password TEXT NOT NULL,
            role TEXT NOT NULL DEFAULT 'athlete',
            isActive BOOLEAN NOT NULL DEFAULT 1,
            email_verified BOOLEAN DEFAULT 1,
            verification_token TEXT,
            verification_expires DATETIME,
            reset_token TEXT,
            reset_expires DATETIME,
            createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
            updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
        )
    ");
    
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS competitions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            date DATE NOT NULL,
            start_date DATE,
            end_date DATE,
            is_multi_day BOOLEAN DEFAULT 0,
            location TEXT,
            discipline TEXT NOT NULL,
            disciplines TEXT,
            description TEXT,
            createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
            updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
        )
    ");
    
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS results (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            userId INTEGER NOT NULL,
            competitionId INTEGER NOT NULL,
            discipline TEXT NOT NULL,
            points REAL NOT NULL DEFAULT 0,
            finished BOOLEAN NOT NULL DEFAULT 1,
            placement INTEGER,
            percentage REAL,
            scheme TEXT,
            faultPoints INTEGER,
            height INTEGER,
            time_seconds INTEGER,
            distance REAL,
            pulse INTEGER,
            notes TEXT,
            createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
            updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (userId) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY (competitionId) REFERENCES competitions(id) ON DELETE CASCADE
        )
    ");
    
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS horses (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            userId INTEGER NOT NULL,
            name TEXT NOT NULL,
            age INTEGER,
            specialization TEXT,
            notes TEXT,
            createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
            updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (userId) REFERENCES users(id) ON DELETE CASCADE
        )
    ");
    
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS training_log (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            userId INTEGER NOT NULL,
            date DATE NOT NULL,
            duration INTEGER,
            horse TEXT,
            trainingType TEXT NOT NULL,
            goal TEXT,
            achievement TEXT,
            mood INTEGER,
            notes TEXT,
            createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (userId) REFERENCES users(id) ON DELETE CASCADE
        )
    ");
    
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS activity_log (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER,
            action TEXT NOT NULL,
            details TEXT,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
        )
    ");
    
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS system_settings (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            setting_key TEXT UNIQUE NOT NULL,
            setting_value TEXT NOT NULL,
            description TEXT,
            updated_by INTEGER,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
        )
    ");
    
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS email_logs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER,
            email_type TEXT NOT NULL,
            recipient_email TEXT NOT NULL,
            subject TEXT,
            success BOOLEAN NOT NULL DEFAULT 0,
            error_message TEXT,
            sent_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
        )
    ");
    
    // MOTIVATSIOONIMOODULI TABELID (TÄIELIKUD JA KORREKTSED)
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS user_achievements (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            userId INTEGER NOT NULL,
            achievement_key TEXT NOT NULL,
            unlocked_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (userId) REFERENCES users(id) ON DELETE CASCADE,
            UNIQUE(userId, achievement_key)
        )
    ");
    
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS user_challenges (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            userId INTEGER NOT NULL,
            challenge_key TEXT NOT NULL,
            start_date DATE NOT NULL,
            end_date DATE NOT NULL,
            target_value INTEGER NOT NULL,
            current_progress INTEGER DEFAULT 0,
            completed BOOLEAN DEFAULT 0,
            completed_at DATETIME,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (userId) REFERENCES users(id) ON DELETE CASCADE
        )
    ");

    $pdo->exec("
        CREATE TABLE IF NOT EXISTS user_motivation_stats (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            userId INTEGER UNIQUE NOT NULL,
            total_achievements INTEGER DEFAULT 0,
            total_challenge_points INTEGER DEFAULT 0,
            current_level INTEGER DEFAULT 1,
            total_experience INTEGER DEFAULT 0,
            last_updated DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (userId) REFERENCES users(id) ON DELETE CASCADE
        )
    ");

    // Siia tuleksid teie ülejäänud ALTER TABLE ja CREATE INDEX laused, mis on korrektsed.
    // ...

    // Admin kasutaja ja näidisandmete lisamine
    // ...

    // Süsteemi seadete lisamine
    // ...
    
    // Distsipliinide välja uuendamine
    // ...

    // MOTIVATSIOONIMOODULI FUNKTSIOONID (PARANDATUD JA AINULT SIIN)
    
    function ensureUserMotivationStats($user_id) {
        global $pdo;
        try {
            $stmt = $pdo->prepare("SELECT COUNT(*) as count FROM user_motivation_stats WHERE userId = ?");
            $stmt->execute([$user_id]);
            
            if ($stmt->fetch()['count'] == 0) {
                $stmt_insert = $pdo->prepare("INSERT OR IGNORE INTO user_motivation_stats (userId) VALUES (?)");
                $stmt_insert->execute([$user_id]);
            }
            return true;
        } catch (Exception $e) {
            error_log('Ensure motivation stats error: ' . $e->getMessage());
            return false;
        }
    }
    
    function updateUserMotivationStats($user_id) {
        global $pdo;
        try {
            $stmt_achievements = $pdo->prepare("SELECT COUNT(*) as count FROM user_achievements WHERE userId = ?");
            $stmt_achievements->execute([$user_id]);
            $achievements_count = $stmt_achievements->fetch()['count'];
            
            $stmt_challenges = $pdo->prepare("SELECT COUNT(*) as completed_challenges FROM user_challenges WHERE userId = ? AND completed = 1");
            $stmt_challenges->execute([$user_id]);
            $challenge_points = $stmt_challenges->fetch()['completed_challenges'] * 5;
            
            $total_experience = ($achievements_count * 10) + $challenge_points;
            $current_level = max(1, floor($total_experience / 100) + 1);
            
            $stmt_update = $pdo->prepare("
                UPDATE user_motivation_stats 
                SET total_achievements = ?,
                    total_challenge_points = ?,
                    current_level = ?,
                    total_experience = ?,
                    last_updated = CURRENT_TIMESTAMP
                WHERE userId = ?
            ");
            $stmt_update->execute([
                $achievements_count, 
                $challenge_points, 
                $current_level, 
                $total_experience, 
                $user_id
            ]);
            
        } catch (Exception $e) {
            error_log('Update motivation stats error for user ' . $user_id . ': ' . $e->getMessage());
        }
    }
    
    initializeDynamicConstants();
    
} catch(Exception $e) {
    die('Andmebaasi viga: ' . $e->getMessage());
}
?>