<?php
function ensure_users_table($pdo) {
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS `users` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `username` varchar(50) NOT NULL,
          `password` varchar(255) NOT NULL,
          `full_name` varchar(100) DEFAULT NULL,
          `email` varchar(100) DEFAULT NULL,
          `role` varchar(50) DEFAULT 'admin',
          `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
          PRIMARY KEY (`id`),
          UNIQUE KEY `username` (`username`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
    ");
}

function ensure_pages_table($pdo) {
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS `pages` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `slug` varchar(100) NOT NULL,
          `title` varchar(255) NOT NULL,
          `content` text DEFAULT NULL,
          PRIMARY KEY (`id`),
          UNIQUE KEY `slug` (`slug`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
    ");
}

function get_page_content($pdo, $slug) {
    $stmt = $pdo->prepare("SELECT * FROM pages WHERE slug = ?");
    $stmt->execute([$slug]);
    return $stmt->fetch();
}

function get_contact_info($pdo) {
    $stmt = $pdo->query("SELECT * FROM contact_info");
    $info = [];
    foreach ($stmt->fetchAll() as $row) {
        $info[$row['type']] = $row['value'];
    }
    return $info;
}

function ensure_certifications_table($pdo) {
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS certifications (
            id INT AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(255) NOT NULL,
            description TEXT,
            image_url VARCHAR(255),
            sort_order INT NOT NULL DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ");

    try {
        $pdo->query("SELECT image_url FROM certifications LIMIT 1");
    } catch (PDOException $e) {
        if ($e->getCode() === '42S22') {
            $pdo->exec("ALTER TABLE certifications ADD COLUMN image_url VARCHAR(255) NULL");
        } else {
            throw $e;
        }
    }

    try {
        $pdo->query("SELECT sort_order FROM certifications LIMIT 1");
    } catch (PDOException $e) {
        if ($e->getCode() === '42S22') { // Column not found
            $pdo->exec("ALTER TABLE certifications ADD COLUMN sort_order INT NOT NULL DEFAULT 0");
        } else {
            throw $e;
        }
    }
}

function get_all_users($pdo) {
    $stmt = $pdo->query("SELECT * FROM users ORDER BY id DESC");
    return $stmt->fetchAll();
}

function get_user_by_id($pdo, $id) {
    $stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
    $stmt->execute([$id]);
    return $stmt->fetch();
}

function get_user_by_username($pdo, $username) {
    $stmt = $pdo->prepare("SELECT * FROM users WHERE username = ?");
    $stmt->execute([$username]);
    return $stmt->fetch();
}

function ensure_sliders_table($pdo) {
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS sliders (
            id INT AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(255) NOT NULL,
            description TEXT,
            image_url VARCHAR(255),
            link_url VARCHAR(255),
            sort_order INT DEFAULT 0,
            active BOOLEAN DEFAULT 1,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ");
}

function ensure_contact_messages_table($pdo) {
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS contact_messages (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `name` varchar(255) NOT NULL,
          `email` varchar(255) NOT NULL,
          `message` text NOT NULL,
          `is_read` tinyint(1) NOT NULL DEFAULT 0,
          `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
    ");
}

function ensure_services_table($pdo) {
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS `services` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `name` varchar(255) NOT NULL,
          `description` text NOT NULL,
          `image_url` varchar(255) DEFAULT NULL,
          `sort_order` int(11) NOT NULL DEFAULT 0,
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
    ");

    try {
        $pdo->query("SELECT image_url FROM services LIMIT 1");
    } catch (PDOException $e) {
        if ($e->getCode() === '42S22') { // Column not found
            $pdo->exec("ALTER TABLE services ADD COLUMN image_url VARCHAR(255) DEFAULT NULL");
        } else {
            throw $e;
        }
    }

    try {
        $pdo->query("SELECT sort_order FROM services LIMIT 1");
    } catch (PDOException $e) {
        if ($e->getCode() === '42S22') { // Column not found
            $pdo->exec("ALTER TABLE services ADD COLUMN sort_order INT NOT NULL DEFAULT 0");
        } else {
            throw $e;
        }
    }
}

function get_all_services($pdo) {
    ensure_services_table($pdo);
    $stmt = $pdo->query("SELECT * FROM services ORDER BY sort_order ASC, id ASC");
    return $stmt->fetchAll();
}

function get_all_certifications($pdo) {
    ensure_certifications_table($pdo);
    $stmt = $pdo->query("SELECT * FROM certifications ORDER BY id DESC");
    return $stmt->fetchAll();
}

function get_all_sliders($pdo) {
    ensure_sliders_table($pdo);
    $stmt = $pdo->query("SELECT * FROM sliders ORDER BY sort_order ASC, id ASC");
    return $stmt->fetchAll();
}
?>
