<?php
/**
 * Domain Utilities
 * 
 * This file contains utility functions for handling domain data
 * to avoid code duplication across multiple pages.
 */

/**
 * Get domain descriptions from database or fallback to static list
 * 
 * @param mysqli $conn Database connection
 * @return array Domain descriptions with key as domain number
 */
function getDomainDescriptions($conn) {
    $domainDescriptions = [];

    // Check if domain_descriptions table exists
    $domainDescTableQuery = "SHOW TABLES LIKE 'domain_descriptions'";
    $domainDescTableExists = $conn->query($domainDescTableQuery)->num_rows > 0;

    if ($domainDescTableExists) {
        // Fetch descriptions from the domain_descriptions table
        $domainDescQuery = "SELECT Domain_ID, Description FROM domain_descriptions";
        $domainDescResult = $conn->query($domainDescQuery);
        
        if ($domainDescResult && $domainDescResult->num_rows > 0) {
            while ($desc = $domainDescResult->fetch_assoc()) {
                $domainId = str_replace('D-', '', $desc['Domain_ID']);
                $domainDescriptions[$domainId] = $desc['Description'];
            }
        }
    }

    // If we don't have descriptions from the database, use our static list
    if (empty($domainDescriptions)) {
        $domainDescriptions = [
            '1' => 'Agriculture',
            '2' => 'Energy',
            '3' => 'IoT & Automation',
            '4' => 'Healthcare',
            '5' => 'Transportation',
            '6' => 'Smart City',
            '7' => 'Education',
            '8' => 'Environment',
            '9' => 'Cybersecurity',
            '10' => 'Fintech',
            '11' => 'E-Commerce',
            '12' => 'Other'
        ];
    }
    
    return $domainDescriptions;
}

/**
 * Get all domain tables from the database
 * 
 * @param mysqli $conn Database connection
 * @return array List of domain table names
 */
function getDomainTables($conn) {
    $domainTables = [];
    
    $tablesQuery = "SHOW TABLES LIKE 'domain_%'";
    $tablesResult = $conn->query($tablesQuery);
    
    if ($tablesResult && $tablesResult->num_rows > 0) {
        while ($table = $tablesResult->fetch_array()) {
            $domainTables[] = $table[0];
        }
    }
    
    return $domainTables;
}

/**
 * Get total project count across all domains
 * 
 * @param mysqli $conn Database connection
 * @return int Total project count
 */
function getTotalProjectCount($conn) {
    $projectCount = 0;
    $domainTables = getDomainTables($conn);
    
    foreach ($domainTables as $domainTable) {
        $countQuery = "SELECT COUNT(*) as count FROM $domainTable";
        $countResult = $conn->query($countQuery);
        if ($countResult && $countResult->num_rows > 0) {
            $projectCount += $countResult->fetch_assoc()['count'];
        }
    }
    
    return $projectCount;
}

/**
 * Get the correct project name field for a given domain table
 * 
 * @param mysqli $conn Database connection
 * @param string $tableName The domain table name
 * @return string The correct field name for project name
 */
function getProjectNameField($conn, $tableName) {
    $columnCheckQuery = "SHOW COLUMNS FROM $tableName LIKE 'Project_Name'";
    $columnExists = $conn->query($columnCheckQuery)->num_rows > 0;
    return $columnExists ? 'Project_Name' : 'Project _Name';
}
?>