<?php
class Page {
    private $db;

    public function __construct() {
        $database = new Database();
        $this->db = $database->getConnection();

        // Create pages table if it doesn't exist
        $this->createPagesTable();
    }

    private function createPagesTable() {
        // Check database type
        $dbType = $_ENV['DB_TYPE'] ?? 'sqlite';

        if ($dbType === 'mysql') {
            // MySQL syntax
            $sql = "CREATE TABLE IF NOT EXISTS pages (
                id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                title VARCHAR(255) NOT NULL,
                slug VARCHAR(255) UNIQUE NOT NULL,
                content TEXT NOT NULL,
                meta_description TEXT,
                featured_image VARCHAR(255),
                status VARCHAR(50) DEFAULT 'published',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
        } else {
            // SQLite syntax
            $sql = "CREATE TABLE IF NOT EXISTS pages (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                slug TEXT UNIQUE NOT NULL,
                content TEXT NOT NULL,
                meta_description TEXT,
                featured_image TEXT,
                status TEXT DEFAULT 'published',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )";
        }

        $this->db->exec($sql);
    }

    public function getAllPages($limit = null, $offset = 0, $status = 'published') {
        $sql = "SELECT * FROM pages WHERE status = :status ORDER BY created_at DESC";

        if ($limit !== null) {
            $sql .= " LIMIT :limit OFFSET :offset";
        }

        $stmt = $this->db->prepare($sql);
        $stmt->bindParam(':status', $status);

        if ($limit !== null) {
            $stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
            $stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
        }

        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getPageCount($status = 'published') {
        $stmt = $this->db->prepare("SELECT COUNT(*) FROM pages WHERE status = :status");
        $stmt->bindParam(':status', $status);
        $stmt->execute();
        return $stmt->fetchColumn();
    }

    public function getPageBySlug($slug) {
        $stmt = $this->db->prepare("SELECT * FROM pages WHERE slug = :slug AND status = 'published'");
        $stmt->bindParam(':slug', $slug);
        $stmt->execute();
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    public function getPageById($id) {
        $stmt = $this->db->prepare("SELECT * FROM pages WHERE id = :id");
        $stmt->bindParam(':id', $id);
        $stmt->execute();
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    public function createPage($title, $content, $metaDescription = '', $featuredImage = '') {
        $slug = $this->generateSlug($title);

        $sql = "INSERT INTO pages (title, slug, content, meta_description, featured_image, updated_at)
                VALUES (:title, :slug, :content, :meta_description, :featured_image, CURRENT_TIMESTAMP)";

        $stmt = $this->db->prepare($sql);

        $stmt->bindParam(':title', $title);
        $stmt->bindParam(':slug', $slug);
        $stmt->bindParam(':content', $content);
        $stmt->bindParam(':meta_description', $metaDescription);
        $stmt->bindParam(':featured_image', $featuredImage);

        return $stmt->execute();
    }

    public function updatePage($id, $title, $content, $metaDescription = '', $featuredImage = '') {
        $page = $this->getPageById($id);

        // If title changed, update slug
        $slug = $page['slug'];
        if ($page['title'] !== $title) {
            $slug = $this->generateSlug($title);
        }

        $sql = "UPDATE pages
               SET title = :title,
                   slug = :slug,
                   content = :content,
                   meta_description = :meta_description,
                   featured_image = :featured_image,
                   updated_at = CURRENT_TIMESTAMP
                WHERE id = :id";

        $stmt = $this->db->prepare($sql);

        $stmt->bindParam(':id', $id);
        $stmt->bindParam(':title', $title);
        $stmt->bindParam(':slug', $slug);
        $stmt->bindParam(':content', $content);
        $stmt->bindParam(':meta_description', $metaDescription);
        $stmt->bindParam(':featured_image', $featuredImage);

        return $stmt->execute();
    }

    public function deletePage($id) {
        try {
            // First, get the page details to check if it has a featured image
            $stmt = $this->db->prepare("SELECT featured_image FROM pages WHERE id = :id");
            $stmt->bindParam(':id', $id);
            $stmt->execute();
            $page = $stmt->fetch(PDO::FETCH_ASSOC);

            // Delete the page from the database
            $stmt = $this->db->prepare("DELETE FROM pages WHERE id = :id");
            $stmt->bindParam(':id', $id);
            $result = $stmt->execute();

            // If the page has a featured image, delete the image file
            if ($result && $page && !empty($page['featured_image'])) {
                $imagePath = __DIR__ . '/..' . $page['featured_image'];
                if (file_exists($imagePath)) {
                    unlink($imagePath);
                }
            }

            return $result;
        } catch (Exception $e) {
            error_log('Error deleting page: ' . $e->getMessage());
            return false;
        }
    }

    private function generateSlug($title) {
        // Convert to lowercase and replace spaces with hyphens
        $slug = strtolower(trim(preg_replace('/[^a-zA-Z0-9]+/', '-', $title), '-'));

        // Check if slug already exists
        $originalSlug = $slug;
        $count = 1;

        while ($this->slugExists($slug)) {
            $slug = $originalSlug . '-' . $count;
            $count++;
        }

        return $slug;
    }

    private function slugExists($slug) {
        $stmt = $this->db->prepare("SELECT COUNT(*) FROM pages WHERE slug = :slug");
        $stmt->bindParam(':slug', $slug);
        $stmt->execute();
        return $stmt->fetchColumn() > 0;
    }
}