<?php
class Blog {
    private $db;

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

        // Create blog posts table if it doesn't exist
        $this->createBlogTable();

        // Check and add missing columns if needed
        $this->checkAndUpdateTableColumns();
    }

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

        if ($dbType === 'mysql') {
            // MySQL syntax
            $sql = "CREATE TABLE IF NOT EXISTS blog_posts (
                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),
                tags TEXT,
                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 blog_posts (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                slug TEXT UNIQUE NOT NULL,
                content TEXT NOT NULL,
                meta_description TEXT,
                featured_image TEXT,
                tags TEXT,
                status TEXT DEFAULT 'published',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )";
        }

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

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

        if ($dbType === 'mysql') {
            // MySQL syntax for checking columns
            $result = $this->db->query("SHOW COLUMNS FROM blog_posts LIKE 'tags'");
            $hasTagsColumn = $result->rowCount() > 0;

            // Add tags column if it doesn't exist
            if (!$hasTagsColumn) {
                $this->db->exec("ALTER TABLE blog_posts ADD COLUMN tags TEXT");
            }
        } else {
            // SQLite syntax for checking columns
            $result = $this->db->query("PRAGMA table_info(blog_posts)");
            $columns = $result->fetchAll(PDO::FETCH_ASSOC);

            $hasTagsColumn = false;
            foreach ($columns as $column) {
                if ($column['name'] === 'tags') {
                    $hasTagsColumn = true;
                    break;
                }
            }

            // Add tags column if it doesn't exist
            if (!$hasTagsColumn) {
                $this->db->exec("ALTER TABLE blog_posts ADD COLUMN tags TEXT");
            }
        }
    }

    public function getAllPosts($limit = null, $offset = 0, $status = 'published') {
        $sql = "SELECT * FROM blog_posts 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 getPostCount($status = 'published') {
        $stmt = $this->db->prepare("SELECT COUNT(*) FROM blog_posts WHERE status = :status");
        $stmt->bindParam(':status', $status);
        $stmt->execute();
        return $stmt->fetchColumn();
    }

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

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

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

        // Check database type
        $dbType = $_ENV['DB_TYPE'] ?? 'sqlite';

        if ($dbType === 'mysql') {
            // MySQL syntax for checking columns
            $result = $this->db->query("SHOW COLUMNS FROM blog_posts LIKE 'tags'");
            $hasTagsColumn = $result->rowCount() > 0;

            // Add tags column if it doesn't exist
            if (!$hasTagsColumn) {
                $this->db->exec("ALTER TABLE blog_posts ADD COLUMN tags TEXT");
                $hasTagsColumn = true; // Column should exist now
            }
        } else {
            // SQLite syntax for checking columns
            $result = $this->db->query("PRAGMA table_info(blog_posts)");
            $columns = $result->fetchAll(PDO::FETCH_ASSOC);

            $hasTagsColumn = false;
            foreach ($columns as $column) {
                if ($column['name'] === 'tags') {
                    $hasTagsColumn = true;
                    break;
                }
            }

            // Add tags column if it doesn't exist
            if (!$hasTagsColumn) {
                $this->db->exec("ALTER TABLE blog_posts ADD COLUMN tags TEXT");
                $hasTagsColumn = true; // Column should exist now
            }
        }

        $sql = "INSERT INTO blog_posts (title, slug, content, meta_description, featured_image";

        if ($hasTagsColumn) {
            $sql .= ", tags";
        }

        $sql .= ", updated_at) VALUES (:title, :slug, :content, :meta_description, :featured_image";

        if ($hasTagsColumn) {
            $sql .= ", :tags";
        }

        $sql .= ", 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);

        if ($hasTagsColumn) {
            $stmt->bindParam(':tags', $tags);
        }

        return $stmt->execute();
    }

    public function updatePost($id, $title, $content, $metaDescription = '', $featuredImage = '', $tags = '') {
        $post = $this->getPostById($id);

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

        // Check database type
        $dbType = $_ENV['DB_TYPE'] ?? 'sqlite';

        if ($dbType === 'mysql') {
            // MySQL syntax for checking columns
            $result = $this->db->query("SHOW COLUMNS FROM blog_posts LIKE 'tags'");
            $hasTagsColumn = $result->rowCount() > 0;

            // Add tags column if it doesn't exist
            if (!$hasTagsColumn) {
                $this->db->exec("ALTER TABLE blog_posts ADD COLUMN tags TEXT");
                $hasTagsColumn = true; // Column should exist now
            }
        } else {
            // SQLite syntax for checking columns
            $result = $this->db->query("PRAGMA table_info(blog_posts)");
            $columns = $result->fetchAll(PDO::FETCH_ASSOC);

            $hasTagsColumn = false;
            foreach ($columns as $column) {
                if ($column['name'] === 'tags') {
                    $hasTagsColumn = true;
                    break;
                }
            }

            // Add tags column if it doesn't exist
            if (!$hasTagsColumn) {
                $this->db->exec("ALTER TABLE blog_posts ADD COLUMN tags TEXT");
                $hasTagsColumn = true; // Column should exist now
            }
        }

        $sql = "UPDATE blog_posts
               SET title = :title,
                   slug = :slug,
                   content = :content,
                   meta_description = :meta_description,
                   featured_image = :featured_image";

        if ($hasTagsColumn) {
            $sql .= ", tags = :tags";
        }

        $sql .= ", 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);

        if ($hasTagsColumn) {
            $stmt->bindParam(':tags', $tags);
        }

        return $stmt->execute();
    }

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

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

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

            return $result;
        } catch (Exception $e) {
            error_log('Error deleting blog post: ' . $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 blog_posts WHERE slug = :slug");
        $stmt->bindParam(':slug', $slug);
        $stmt->execute();
        return $stmt->fetchColumn() > 0;
    }

    public function getRecentPosts($limit = 5) {
        return $this->getAllPosts($limit);
    }

    public function getPostsByTag($tag, $limit = null, $offset = 0) {
        $tag = '%' . $tag . '%';
        $sql = "SELECT * FROM blog_posts WHERE tags LIKE :tag AND status = 'published' ORDER BY created_at DESC";

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

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

        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);
    }
}