<?php

require_once APP_ROOT . '/helpers/Slug.php';
require_once APP_ROOT . '/models/ArticleTag.php';

class Article
{
    private PDO $db;
    public const PER_PAGE = 12;

    public function __construct()
    {
        $this->db = Database::getInstance();
    }

    public function findById(int $id): ?array
    {
        $stmt = $this->db->prepare('SELECT * FROM articles WHERE id = :id LIMIT 1');
        $stmt->execute(['id' => $id]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public function findPublishedBySlug(string $slug): ?array
    {
        $stmt = $this->db->prepare('SELECT a.*, c.slug AS category_slug, c.name AS category_name,
                u.username AS author_username, u.display_name AS author_name
            FROM articles a
            LEFT JOIN article_categories c ON c.id = a.category_id
            LEFT JOIN users u ON u.id = a.user_id
            WHERE a.slug = :s AND a.status = "published" LIMIT 1');
        $stmt->execute(['s' => $slug]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public function findAnyBySlug(string $slug): ?array
    {
        $stmt = $this->db->prepare('SELECT * FROM articles WHERE slug = :s LIMIT 1');
        $stmt->execute(['s' => $slug]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public function resolveSlugHistory(string $slug): ?int
    {
        $stmt = $this->db->prepare('SELECT article_id FROM article_slug_history WHERE old_slug = :s LIMIT 1');
        $stmt->execute(['s' => $slug]);
        $row = $stmt->fetch();
        return $row ? (int)$row['article_id'] : null;
    }

    /**
     * Build a list query with filters.
     * $filters keys: locale, category_id, tag_id, q, status (default published)
     */
    private function buildListQuery(array $filters, bool $count = false): array
    {
        $where = [];
        $params = [];
        $joins = '';

        $status = $filters['status'] ?? 'published';
        $where[] = 'a.status = :status';
        $params['status'] = $status;

        if (!empty($filters['locale'])) {
            $where[] = 'a.locale = :locale';
            $params['locale'] = $filters['locale'];
        }

        if (!empty($filters['category_id'])) {
            $where[] = 'a.category_id = :cid';
            $params['cid'] = (int)$filters['category_id'];
        }

        if (!empty($filters['tag_id'])) {
            $joins .= ' INNER JOIN article_tag_map m ON m.article_id = a.id AND m.tag_id = :tid';
            $params['tid'] = (int)$filters['tag_id'];
        }

        $orderExpr = 'a.published_at DESC, a.id DESC';

        if (!empty($filters['q'])) {
            $q = trim($filters['q']);
            if (mb_strlen($q) >= 3) {
                $where[] = '(MATCH(a.title, a.excerpt, a.content) AGAINST (:q IN NATURAL LANGUAGE MODE) OR a.title LIKE :qlike)';
                $params['q'] = $q;
                $params['qlike'] = '%' . $q . '%';
                $orderExpr = 'MATCH(a.title, a.excerpt, a.content) AGAINST (:q2 IN NATURAL LANGUAGE MODE) DESC, a.published_at DESC';
                $params['q2'] = $q;
            } else {
                $where[] = '(a.title LIKE :qlike OR a.excerpt LIKE :qlike)';
                $params['qlike'] = '%' . $q . '%';
            }
        }

        $select = $count
            ? 'SELECT COUNT(DISTINCT a.id) AS total'
            : 'SELECT DISTINCT a.*, c.slug AS category_slug, c.name AS category_name,
               u.username AS author_username, u.display_name AS author_name';

        $sql = $select . ' FROM articles a '
            . 'LEFT JOIN article_categories c ON c.id = a.category_id '
            . 'LEFT JOIN users u ON u.id = a.user_id '
            . $joins
            . ' WHERE ' . implode(' AND ', $where);

        if (!$count) {
            $sql .= ' ORDER BY ' . $orderExpr;
        } else {
            // COUNT query doesn't use ORDER BY — drop any params that only appear there.
            unset($params['q2']);
        }

        return [$sql, $params];
    }

    public function listPublished(array $filters, int $page = 1, int $perPage = self::PER_PAGE): array
    {
        $page = max(1, $page);
        $offset = ($page - 1) * $perPage;
        [$sql, $params] = $this->buildListQuery($filters);
        $sql .= ' LIMIT ' . (int)$perPage . ' OFFSET ' . (int)$offset;
        $stmt = $this->db->prepare($sql);
        foreach ($params as $k => $v) {
            $stmt->bindValue(':' . $k, $v);
        }
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function countPublished(array $filters): int
    {
        [$sql, $params] = $this->buildListQuery($filters, true);
        $stmt = $this->db->prepare($sql);
        foreach ($params as $k => $v) {
            $stmt->bindValue(':' . $k, $v);
        }
        $stmt->execute();
        $row = $stmt->fetch();
        return (int)($row['total'] ?? 0);
    }

    public function listAllForAdmin(?string $q = null): array
    {
        $sql = 'SELECT a.*, c.name AS category_name FROM articles a
                LEFT JOIN article_categories c ON c.id = a.category_id';
        $params = [];
        if ($q) {
            $sql .= ' WHERE a.title LIKE :q';
            $params['q'] = '%' . $q . '%';
        }
        $sql .= ' ORDER BY a.updated_at DESC';
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    public function latestPublished(int $limit = 20, ?string $locale = null): array
    {
        $sql = 'SELECT a.*, c.slug AS category_slug, c.name AS category_name,
                u.username AS author_username, u.display_name AS author_name
                FROM articles a
                LEFT JOIN article_categories c ON c.id = a.category_id
                LEFT JOIN users u ON u.id = a.user_id
                WHERE a.status = "published"'
                . ($locale ? ' AND a.locale = :loc' : '') .
                ' ORDER BY a.published_at DESC LIMIT ' . (int)$limit;
        $stmt = $this->db->prepare($sql);
        if ($locale) {
            $stmt->bindValue(':loc', $locale);
        }
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function related(int $articleId, ?int $categoryId, int $limit = 3): array
    {
        if (!$categoryId) {
            return [];
        }
        $stmt = $this->db->prepare('SELECT a.*, c.slug AS category_slug, c.name AS category_name
            FROM articles a
            LEFT JOIN article_categories c ON c.id = a.category_id
            WHERE a.status = "published" AND a.id != :id AND a.category_id = :cid
            ORDER BY a.published_at DESC LIMIT ' . (int)$limit);
        $stmt->execute(['id' => $articleId, 'cid' => $categoryId]);
        return $stmt->fetchAll();
    }

    public function prevInCategory(int $articleId, ?int $categoryId, ?string $publishedAt): ?array
    {
        if (!$categoryId || !$publishedAt) return null;
        $stmt = $this->db->prepare('SELECT slug, title FROM articles
            WHERE status="published" AND category_id = :cid AND published_at < :p AND id != :id
            ORDER BY published_at DESC LIMIT 1');
        $stmt->execute(['cid' => $categoryId, 'p' => $publishedAt, 'id' => $articleId]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public function nextInCategory(int $articleId, ?int $categoryId, ?string $publishedAt): ?array
    {
        if (!$categoryId || !$publishedAt) return null;
        $stmt = $this->db->prepare('SELECT slug, title FROM articles
            WHERE status="published" AND category_id = :cid AND published_at > :p AND id != :id
            ORDER BY published_at ASC LIMIT 1');
        $stmt->execute(['cid' => $categoryId, 'p' => $publishedAt, 'id' => $articleId]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public function uniqueSlug(string $base, ?int $excludeId = null): string
    {
        $base = Slug::make($base);
        $candidate = $base;
        $i = 2;
        while ($this->slugExists($candidate, $excludeId)) {
            $candidate = $base . '-' . $i;
            $i++;
            if ($i > 500) {
                $candidate = $base . '-' . substr(bin2hex(random_bytes(3)), 0, 6);
                break;
            }
        }
        return $candidate;
    }

    private function slugExists(string $slug, ?int $excludeId): bool
    {
        $sql = 'SELECT id FROM articles WHERE slug = :s';
        $params = ['s' => $slug];
        if ($excludeId) {
            $sql .= ' AND id != :e';
            $params['e'] = $excludeId;
        }
        $sql .= ' LIMIT 1';
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        if ($stmt->fetch()) return true;
        // also check slug history
        $stmt2 = $this->db->prepare('SELECT article_id FROM article_slug_history WHERE old_slug = :s LIMIT 1');
        $stmt2->execute(['s' => $slug]);
        $row = $stmt2->fetch();
        if ($row) {
            if ($excludeId && (int)$row['article_id'] === $excludeId) return false;
            return true;
        }
        return false;
    }

    public function create(array $data): int
    {
        $stmt = $this->db->prepare('INSERT INTO articles
            (user_id, category_id, slug, title, excerpt, content, featured_image, featured_image_alt,
             locale, status, published_at, word_count, reading_minutes, meta_title, meta_description)
            VALUES
            (:user_id, :category_id, :slug, :title, :excerpt, :content, :featured_image, :featured_image_alt,
             :locale, :status, :published_at, :word_count, :reading_minutes, :meta_title, :meta_description)');
        $stmt->execute([
            'user_id' => $data['user_id'],
            'category_id' => $data['category_id'] ?: null,
            'slug' => $data['slug'],
            'title' => $data['title'],
            'excerpt' => $data['excerpt'] ?? null,
            'content' => $data['content'],
            'featured_image' => $data['featured_image'] ?? null,
            'featured_image_alt' => $data['featured_image_alt'] ?? null,
            'locale' => $data['locale'],
            'status' => $data['status'],
            'published_at' => $data['published_at'],
            'word_count' => $data['word_count'] ?? 0,
            'reading_minutes' => $data['reading_minutes'] ?? 1,
            'meta_title' => $data['meta_title'] ?? null,
            'meta_description' => $data['meta_description'] ?? null,
        ]);
        return (int)$this->db->lastInsertId();
    }

    public function update(int $id, array $data, ?string $previousSlug = null): bool
    {
        // Debug: Log that update method is being called
        Debug::log('Article update method called for ID: ' . $id);
        
        // Debug: Check if article exists before update
        $checkStmt = $this->db->prepare('SELECT id, content FROM articles WHERE id = :id LIMIT 1');
        $checkStmt->execute(['id' => $id]);
        $existingRow = $checkStmt->fetch();
        Debug::log('Existing article check: ' . ($existingRow ? 'EXISTS' : 'NOT FOUND'));
        if ($existingRow) {
            Debug::log('Existing content length: ' . strlen($existingRow['content']));
            Debug::log('New content length: ' . strlen($data['content']));
            Debug::log('Content identical: ' . ($existingRow['content'] === $data['content'] ? 'YES' : 'NO'));
        }
        
        if ($previousSlug && $previousSlug !== $data['slug']) {
            // Record old slug for 301 redirect
            $this->db->prepare('INSERT IGNORE INTO article_slug_history (article_id, old_slug) VALUES (:a, :s)')
                ->execute(['a' => $id, 's' => $previousSlug]);
            // Remove any history entry that matches new slug
            $this->db->prepare('DELETE FROM article_slug_history WHERE old_slug = :s')
                ->execute(['s' => $data['slug']]);
        }

        $stmt = $this->db->prepare('UPDATE articles SET
            category_id = :category_id, slug = :slug, title = :title, excerpt = :excerpt,
            content = :content, featured_image = :featured_image, featured_image_alt = :featured_image_alt,
            locale = :locale, status = :status, published_at = :published_at,
            word_count = :word_count, reading_minutes = :reading_minutes,
            meta_title = :meta_title, meta_description = :meta_description,
            updated_at = NOW()
            WHERE id = :id');
        
        // Debug: Log the SQL and parameters
        Debug::log('SQL UPDATE prepared for article ID: ' . $id);
        Debug::log('Content field value being updated: ' . $data['content']);
        
        $result = $stmt->execute([
            'id' => $id,
            'category_id' => $data['category_id'] ?: null,
            'slug' => $data['slug'],
            'title' => $data['title'],
            'excerpt' => $data['excerpt'] ?? null,
            'content' => $data['content'],
            'featured_image' => $data['featured_image'] ?? null,
            'featured_image_alt' => $data['featured_image_alt'] ?? null,
            'locale' => $data['locale'],
            'status' => $data['status'],
            'published_at' => $data['published_at'],
            'word_count' => $data['word_count'] ?? 0,
            'reading_minutes' => $data['reading_minutes'] ?? 1,
            'meta_title' => $data['meta_title'] ?? null,
            'meta_description' => $data['meta_description'] ?? null,
        ]);
        
        // Debug: Log the result and rows affected
        Debug::log('Update result: ' . ($result ? 'SUCCESS' : 'FAILED'));
        Debug::log('Rows affected: ' . $stmt->rowCount());
        
        return $result;
    }

    public function delete(int $id): bool
    {
        return $this->db->prepare('DELETE FROM articles WHERE id = :id')->execute(['id' => $id]);
    }

    public function incrementViews(int $id): void
    {
        $this->db->prepare('UPDATE articles SET views = views + 1 WHERE id = :id')->execute(['id' => $id]);
    }

    public function lastModifiedAll(?string $locale = null): ?string
    {
        $sql = 'SELECT MAX(updated_at) AS m FROM articles WHERE status = "published"';
        $params = [];
        if ($locale) {
            $sql .= ' AND locale = :l';
            $params['l'] = $locale;
        }
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        $row = $stmt->fetch();
        return $row && $row['m'] ? $row['m'] : null;
    }
}