create_domain_descriptions.php

<?php
/**
 * Domain Descriptions Table Creation
 * 
 * This script creates a domain_descriptions table and populates it with
 * descriptions for each domain, fetching domain IDs from existing domain tables.
 */

// Include database connection
require_once 'includes/db_connect.php';
require_once 'includes/domain_utils.php';

// Check if table already exists
$checkTableQuery = "SHOW TABLES LIKE 'domain_descriptions'";
$tableExists = $conn->query($checkTableQuery)->num_rows > 0;

if ($tableExists) {
    // Drop the existing table if needed
    $dropTableQuery = "DROP TABLE domain_descriptions";
    if ($conn->query($dropTableQuery) === TRUE) {
        echo "Existing domain_descriptions table dropped successfully.<br>";
    } else {
        echo "Error dropping table: " . $conn->error . "<br>";
        exit;
    }
}

// Create domain_descriptions table
$createTableQuery = "CREATE TABLE domain_descriptions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    Domain_ID VARCHAR(10) NOT NULL UNIQUE,
    Description VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";

if ($conn->query($createTableQuery) === TRUE) {
    echo "Domain descriptions table created successfully.<br>";
} else {
    echo "Error creating table: " . $conn->error . "<br>";
    exit;
}

// Default descriptions for all domains
$defaultDescriptions = [
    '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'
];

// Get all domain tables
$domainTables = getDomainTables($conn);
$insertedDomains = [];

// Get domain IDs from existing tables
foreach ($domainTables as $domainTable) {
    $domainNumber = str_replace('domain_', '', $domainTable);
    $domainIdQuery = "SELECT DISTINCT Domain_ID FROM $domainTable LIMIT 1";
    $domainIdResult = $conn->query($domainIdQuery);
    
    if ($domainIdResult && $domainIdResult->num_rows > 0) {
        $domainId = $domainIdResult->fetch_assoc()['Domain_ID'];
        $description = isset($defaultDescriptions[$domainNumber]) ? $defaultDescriptions[$domainNumber] : 'Domain ' . $domainNumber;
        
        // Insert into domain_descriptions table
        $insertQuery = "INSERT INTO domain_descriptions (Domain_ID, Description) VALUES (?, ?)";
        $stmt = $conn->prepare($insertQuery);
        $stmt->bind_param("ss", $domainId, $description);
        
        if ($stmt->execute()) {
            echo "Added description for $domainId: $description<br>";
            $insertedDomains[] = $domainId;
        } else {
            echo "Error adding description for $domainId: " . $stmt->error . "<br>";
        }
        
        $stmt->close();
    }
}

// Insert any missing domains from our default list
foreach ($defaultDescriptions as $domainNumber => $description) {
    $domainId = "D-" . str_pad($domainNumber, 2, '0', STR_PAD_LEFT);
    
    if (!in_array($domainId, $insertedDomains)) {
        $insertQuery = "INSERT INTO domain_descriptions (Domain_ID, Description) VALUES (?, ?)";
        $stmt = $conn->prepare($insertQuery);
        $stmt->bind_param("ss", $domainId, $description);
        
        if ($stmt->execute()) {
            echo "Added description for $domainId: $description (from defaults)<br>";
        } else {
            echo "Error adding description for $domainId: " . $stmt->error . "<br>";
        }
        
        $stmt->close();
    }
}

echo "<br>Domain descriptions setup complete.";

// Close connection
$conn->close();
?>