<?php
/**
 * Email Validation Helper Functions
 * এই ফাইলে email duplicate check এর জন্য helper functions আছে
 */

/**
 * Check if email already exists in any table
 * @param PDO $pdo Database connection
 * @param string $email Email to check
 * @param int|null $exclude_id Exclude this ID from check (for updates)
 * @param string|null $exclude_table Exclude this table from check
 * @return array|false Returns array with table and id if found, false if not found
 */
function checkEmailExists($pdo, $email, $exclude_id = null, $exclude_table = null) {
    if (empty($email)) {
        return false;
    }
    
    $tables_to_check = [
        'users' => 'id',
        'students' => 'id', 
        'teachers' => 'id'
    ];
    
    foreach ($tables_to_check as $table => $id_field) {
        // Skip excluded table
        if ($exclude_table && $table === $exclude_table) {
            continue;
        }
        
        try {
            // Check if table exists
            $table_exists = $pdo->query("SHOW TABLES LIKE '$table'")->fetch();
            if (!$table_exists) {
                continue;
            }
            
            // Check if email column exists
            $columns = $pdo->query("DESCRIBE $table")->fetchAll();
            $has_email = false;
            foreach ($columns as $col) {
                if ($col['Field'] == 'email') {
                    $has_email = true;
                    break;
                }
            }
            
            if (!$has_email) {
                continue;
            }
            
            // Build query
            $sql = "SELECT $id_field FROM $table WHERE email = ?";
            $params = [$email];
            
            // Exclude specific ID if provided
            if ($exclude_id && $exclude_table === $table) {
                $sql .= " AND $id_field != ?";
                $params[] = $exclude_id;
            }
            
            $stmt = $pdo->prepare($sql);
            $stmt->execute($params);
            $result = $stmt->fetch();
            
            if ($result) {
                return [
                    'table' => $table,
                    'id' => $result[$id_field],
                    'message' => getEmailExistsMessage($table)
                ];
            }
            
        } catch (Exception $e) {
            // Skip table if error
            continue;
        }
    }
    
    return false;
}

/**
 * Get appropriate error message for email exists
 * @param string $table Table name where email was found
 * @return string Error message in Bengali
 */
function getEmailExistsMessage($table) {
    $messages = [
        'users' => 'এই ইমেইল ঠিকানা ইতিমধ্যে একটি ব্যবহারকারী অ্যাকাউন্টের জন্য ব্যবহৃত হয়েছে',
        'students' => 'এই ইমেইল ঠিকানা ইতিমধ্যে একজন শিক্ষার্থীর জন্য ব্যবহৃত হয়েছে',
        'teachers' => 'এই ইমেইল ঠিকানা ইতিমধ্যে একজন শিক্ষকের জন্য ব্যবহৃত হয়েছে'
    ];
    
    return $messages[$table] ?? 'এই ইমেইল ঠিকানা ইতিমধ্যে ব্যবহৃত হয়েছে';
}

/**
 * Validate email format
 * @param string $email Email to validate
 * @return bool True if valid, false if invalid
 */
function isValidEmail($email) {
    if (empty($email)) {
        return true; // Empty email is allowed
    }
    
    return filter_var($email, FILTER_VALIDATE_EMAIL) !== false;
}

/**
 * Generate unique email if needed
 * @param PDO $pdo Database connection
 * @param string $base_email Base email to make unique
 * @return string Unique email
 */
function generateUniqueEmail($pdo, $base_email) {
    if (empty($base_email)) {
        return '';
    }
    
    // If email doesn't exist, return as is
    if (!checkEmailExists($pdo, $base_email)) {
        return $base_email;
    }
    
    // Extract email parts
    $parts = explode('@', $base_email);
    if (count($parts) != 2) {
        return $base_email . '_' . time() . '@temp.local';
    }
    
    $username = $parts[0];
    $domain = $parts[1];
    
    // Try adding numbers
    for ($i = 1; $i <= 999; $i++) {
        $new_email = $username . $i . '@' . $domain;
        if (!checkEmailExists($pdo, $new_email)) {
            return $new_email;
        }
    }
    
    // If all numbers tried, add timestamp
    return $username . '_' . time() . '@' . $domain;
}

/**
 * Clean and fix duplicate emails in database
 * @param PDO $pdo Database connection
 * @return array Results of the cleanup
 */
function cleanupDuplicateEmails($pdo) {
    $results = [];
    $tables = ['users', 'students', 'teachers'];
    
    foreach ($tables as $table) {
        try {
            // Check if table exists
            $table_exists = $pdo->query("SHOW TABLES LIKE '$table'")->fetch();
            if (!$table_exists) {
                continue;
            }
            
            // Check if email column exists
            $columns = $pdo->query("DESCRIBE $table")->fetchAll();
            $has_email = false;
            foreach ($columns as $col) {
                if ($col['Field'] == 'email') {
                    $has_email = true;
                    break;
                }
            }
            
            if (!$has_email) {
                continue;
            }
            
            // Find duplicates
            $duplicates = $pdo->query("
                SELECT email, GROUP_CONCAT(id ORDER BY id) as ids, COUNT(*) as count
                FROM $table 
                WHERE email IS NOT NULL AND email != '' 
                GROUP BY email 
                HAVING COUNT(*) > 1
            ")->fetchAll();
            
            $fixed_count = 0;
            foreach ($duplicates as $dup) {
                $ids = explode(',', $dup['ids']);
                // Keep first one, update others
                for ($i = 1; $i < count($ids); $i++) {
                    $unique_email = generateUniqueEmail($pdo, $dup['email']);
                    $stmt = $pdo->prepare("UPDATE $table SET email = ? WHERE id = ?");
                    $stmt->execute([$unique_email, $ids[$i]]);
                    $fixed_count++;
                }
            }
            
            $results[$table] = [
                'duplicates_found' => count($duplicates),
                'records_fixed' => $fixed_count
            ];
            
        } catch (Exception $e) {
            $results[$table] = [
                'error' => $e->getMessage()
            ];
        }
    }
    
    return $results;
}

/**
 * Make email field nullable in all tables
 * @param PDO $pdo Database connection
 * @return array Results of the operation
 */
function makeEmailFieldsNullable($pdo) {
    $results = [];
    $tables = ['users', 'students', 'teachers'];
    
    foreach ($tables as $table) {
        try {
            // Check if table exists
            $table_exists = $pdo->query("SHOW TABLES LIKE '$table'")->fetch();
            if (!$table_exists) {
                $results[$table] = 'Table does not exist';
                continue;
            }
            
            // Make email nullable
            $pdo->exec("ALTER TABLE $table MODIFY email VARCHAR(255) NULL");
            
            // Remove unique constraint if exists
            $indexes = $pdo->query("SHOW INDEX FROM $table WHERE Column_name = 'email' AND Non_unique = 0")->fetchAll();
            foreach ($indexes as $index) {
                $pdo->exec("ALTER TABLE $table DROP INDEX " . $index['Key_name']);
            }
            
            $results[$table] = 'Success';
            
        } catch (Exception $e) {
            $results[$table] = 'Error: ' . $e->getMessage();
        }
    }
    
    return $results;
}
?>