<?php
// db_init.php
require_once 'config.php';

echo "<h2>SaniBook Database Initialization</h2>";

if (!$conn) {
    die("<p style='color:red'>Error: Database connection failed. Check config.php</p>");
}

// 1. Create categories table
$sql_table = "CREATE TABLE IF NOT EXISTS categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    full_desc TEXT NOT NULL,
    image_file VARCHAR(255),
    icon VARCHAR(50),
    color VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";

if ($conn->query($sql_table)) {
    echo "<p style='color:green'>✓ Categories table verified/created.</p>";
} else {
    die("<p style='color:red'>Error creating categories table: " . $conn->error . "</p>");
}

// 1b. Ensure columns exist (in case table was created with older schema)
$cols_to_check = ['icon', 'color', 'short_desc'];
foreach ($cols_to_check as $col) {
    $check = $conn->query("SHOW COLUMNS FROM categories LIKE '$col'");
    if ($check->num_rows == 0) {
        if ($conn->query("ALTER TABLE categories ADD COLUMN $col VARCHAR(255) NULL")) {
            echo "<p style='color:green'>✓ Column '$col' added to categories table.</p>";
        } else {
            echo "<p style='color:red'>Error adding column '$col': " . $conn->error . "</p>";
        }
    } else if ($col === 'short_desc') {
        // If short_desc exists but has no default and is NOT NULL, make it NULLable
        if ($conn->query("ALTER TABLE categories MODIFY COLUMN short_desc VARCHAR(255) NULL")) {
            echo "<p style='color:green'>✓ Column 'short_desc' updated to be nullable.</p>";
        }
    }
}

// 2. Populate Categories
$categories = [
    [1, 'Why Sanitation Matters', '<p class="mb-4">Sanitation is not just a health issue; it is a massive economic engine waiting to be unlocked. Every dollar invested in sanitation yields a $5.50 return in economic productivity.</p><p>We must shift the narrative from charity to investment.</p>', 'ch1_why_matters.jpg', 'fa-chart-line', 'text-brand-dark'],
    [2, 'Coverage Gaps', '<p class="mb-4">Despite progress, significant regional disparities exist. We need to identify and target the most underserved areas to ensure equitable access for all Kenyans.</p>', 'ch2_access_gaps.jpg', 'fa-triangle-exclamation', 'text-brand-red'],
    [3, 'Behavioral Barriers', '<p class="mb-4">Infrastructure alone is not enough. We must address the cultural and social norms that influence sanitation behaviors and adoption of new solutions.</p>', 'ch3_behavioral_change.jpg', 'fa-brain', 'text-brand-gold'],
    [4, 'Technology Adoption', '<h3 class="text-xl font-bold mb-2">The Approval Bottleneck</h3><p class="mb-4">Innovators face a 2-3 year waiting period for certification. We need a Single-window clearance system to fast-track proven solutions.</p><h3 class="text-xl font-bold mb-2">Single-Window Clearance</h3><p>By streamlining approvals, we can reduce deployment time by 60%.</p>', 'ch4_tech_innovation.jpg', 'fa-microchip', 'text-blue-600'],
    [5, 'Chronic Underfunding', '<p>The sector currently faces a 60% financing gap. Innovative financing models like blended finance and sanitation bonds are essential to bridge this divide.</p>', 'ch5_financing.jpg', 'fa-money-bill-wave', 'text-green-600'],
    [6, 'Fragmented Governance', '<p class="mb-4">Overlapping mandates and lack of coordination hinder progress. We need a unified regulatory framework to streamline efforts and maximize impact.</p>', 'ch6_governance.jpg', 'fa-scale-balanced', 'text-purple-600'],
    [7, 'Data Deficiencies', '<p class="mb-4">We cannot manage what we cannot measure. Robust data systems are needed to track progress, identify gaps, and inform evidence-based decision making.</p>', 'ch7_data_hub.jpg', 'fa-magnifying-glass-chart', 'text-gray-600'],
    [8, 'Unmet Aspirations', '<p class="mb-4">Community voices must be at the center of sanitation solutions. We need to listen to their needs and aspirations to design interventions that truly work.</p>', 'ch8_frontline_heroes.jpg', 'fa-hands-holding-circle', 'text-orange-600'],
    [9, 'Stakeholder Portal', '<p class="text-lg">Welcome to the central registry for Kenya\'s sanitation actors. By registering your organization, you become visible to partners, funders, and policymakers.</p><p class="mt-4">This data directly informs the National Sanitation Strategy.</p>', 'ch9_stakeholder_portal.jpg', 'fa-users-viewfinder', 'text-teal-600']
];

$sql_insert = "INSERT INTO categories (id, title, full_desc, image_file, icon, color) 
               VALUES (?, ?, ?, ?, ?, ?) 
               ON DUPLICATE KEY UPDATE 
               title = VALUES(title), 
               full_desc = VALUES(full_desc), 
               image_file = VALUES(image_file), 
               icon = VALUES(icon), 
               color = VALUES(color)";

$stmt = $conn->prepare($sql_insert);

if (!$stmt) {
    die("<p style='color:red'>Error preparing statement: " . $conn->error . "</p>");
}

foreach ($categories as $cat) {
    if (!$stmt->bind_param("isssss", $cat[0], $cat[1], $cat[2], $cat[3], $cat[4], $cat[5])) {
        echo "<p style='color:red'>Error binding parameters for ID " . $cat[0] . ": " . $stmt->error . "</p>";
        continue;
    }
    if (!$stmt->execute()) {
        echo "<p style='color:red'>Error executing INSERT for ID " . $cat[0] . ": " . $stmt->error . "</p>";
    }
}
echo "<p style='color:green'>✓ Categories data populated/updated.</p>";

// 3. Add category_id to submissions if missing
$check_col = $conn->query("SHOW COLUMNS FROM submissions LIKE 'category_id'");
if ($check_col->num_rows == 0) {
    if ($conn->query("ALTER TABLE submissions ADD COLUMN category_id INT AFTER county")) {
        echo "<p style='color:green'>✓ Column 'category_id' added to submissions table.</p>";
    } else {
        echo "<p style='color:red'>Error adding column: " . $conn->error . "</p>";
    }
} else {
    echo "<p style='color:blue'>i Column 'category_id' already exists in submissions table.</p>";
}

echo "<hr><p><b>Database Initialization Complete.</b> You can now delete this file and refresh <a href='category.php?id=1'>category.php</a></p>";
?>