<?php

namespace App\Models;

use App\Core\Model;

class Article extends Model
{
    protected static string $table = 'articles';

    public static function findBySite(int $siteId, int $limit = 50): array
    {
        $stmt = self::db()->prepare(
            "SELECT a.*, t.name as topic_name, s.name as site_name
             FROM articles a
             JOIN topics t ON a.topic_id = t.id
             JOIN sites s ON a.site_id = s.id
             WHERE a.site_id = :site_id
             ORDER BY a.created_at DESC
             LIMIT {$limit}"
        );
        $stmt->execute(['site_id' => $siteId]);
        return $stmt->fetchAll();
    }

    public static function findAllWithRelations(int $limit = 50, int $offset = 0, ?int $siteId = null): array
    {
        $sql = "SELECT a.*, t.name as topic_name, s.name as site_name
                FROM articles a
                JOIN topics t ON a.topic_id = t.id
                JOIN sites s ON a.site_id = s.id";

        if ($siteId !== null) {
            $sql .= " WHERE a.site_id = :site_id";
        }

        $sql .= " ORDER BY a.created_at DESC
                  LIMIT :limit OFFSET :offset";

        $stmt = self::db()->prepare($sql);

        if ($siteId !== null) {
            $stmt->bindValue('site_id', $siteId, \PDO::PARAM_INT);
        }

        $stmt->bindValue('limit', $limit, \PDO::PARAM_INT);
        $stmt->bindValue('offset', $offset, \PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public static function findPublishedBySiteForRepublish(int $siteId): array
    {
        $stmt = self::db()->prepare(
            "SELECT a.id, a.site_id, a.topic_id, a.title, a.content, a.published_at, a.created_at
             FROM articles a
             WHERE a.site_id = :site_id
               AND a.status = 'published'
               AND a.content <> ''
             ORDER BY COALESCE(a.published_at, a.created_at) ASC, a.id ASC"
        );
        $stmt->execute(['site_id' => $siteId]);
        return $stmt->fetchAll();
    }

    public static function findWithRelations(int $id): ?array
    {
        $stmt = self::db()->prepare(
            "SELECT a.*, t.name as topic_name, s.name as site_name, s.url as site_url,
                    s.api_user as site_api_user, s.api_token as site_api_token
             FROM articles a
             JOIN topics t ON a.topic_id = t.id
             JOIN sites s ON a.site_id = s.id
             WHERE a.id = :id"
        );
        $stmt->execute(['id' => $id]);
        $result = $stmt->fetch();
        return $result ?: null;
    }

    public static function getRecentTitlesByTopic(int $topicId, int $limit = 20): array
    {
        $stmt = self::db()->prepare(
            "SELECT title FROM articles
             WHERE topic_id = :topic_id
             ORDER BY created_at DESC
             LIMIT {$limit}"
        );
        $stmt->execute(['topic_id' => $topicId]);
        return $stmt->fetchAll(\PDO::FETCH_COLUMN);
    }

    public static function findPublishedLinkCandidatesBySite(int $siteId, int $limit = 60): array
    {
        $stmt = self::db()->prepare(
            "SELECT a.id, a.title, a.content, a.wp_post_id, a.wp_post_url, a.published_at, a.created_at
             FROM articles a
             WHERE a.site_id = :site_id
               AND a.status = 'published'
               AND a.wp_post_id IS NOT NULL
               AND a.content <> ''
             ORDER BY COALESCE(a.published_at, a.created_at) DESC, a.id DESC
             LIMIT {$limit}"
        );
        $stmt->execute(['site_id' => $siteId]);
        return $stmt->fetchAll();
    }

    public static function existsBySiteAndWpPostId(int $siteId, int $wpPostId): bool
    {
        $stmt = self::db()->prepare(
            "SELECT id FROM articles
             WHERE site_id = :site_id AND wp_post_id = :wp_post_id
             LIMIT 1"
        );
        $stmt->execute([
            'site_id' => $siteId,
            'wp_post_id' => $wpPostId,
        ]);

        return (bool) $stmt->fetchColumn();
    }

    public static function findNextRetryableBySite(int $siteId): ?array
    {
        $stmt = self::db()->prepare(
            "SELECT a.*
             FROM articles a
             WHERE a.site_id = :site_id
               AND a.wp_post_id IS NULL
               AND a.status IN ('generated', 'failed')
               AND a.content <> ''
             ORDER BY a.created_at ASC, a.id ASC
             LIMIT 1"
        );
        $stmt->execute(['site_id' => $siteId]);
        $result = $stmt->fetch();
        return $result ?: null;
    }

    public static function findNextPublishedWithoutImageBySite(int $siteId): ?array
    {
        $stmt = self::db()->prepare(
            "SELECT a.*
             FROM articles a
             WHERE a.site_id = :site_id
               AND a.status = 'published'
               AND a.wp_post_id IS NOT NULL
               AND a.content <> ''
                             AND COALESCE(a.retry_count, 0) < 5
               AND (a.image_url IS NULL OR a.image_url = '')
             ORDER BY COALESCE(a.published_at, a.created_at) ASC, a.id ASC
             LIMIT 1"
        );
        $stmt->execute(['site_id' => $siteId]);
        $result = $stmt->fetch();
        return $result ?: null;
    }

    public static function markRetryAttempt(int $articleId): void
    {
        $stmt = self::db()->prepare(
            "UPDATE articles
             SET retry_count = COALESCE(retry_count, 0) + 1,
                 last_retry_at = NOW()
             WHERE id = :id"
        );
        $stmt->execute(['id' => $articleId]);
    }

    public static function getStats(): array
    {
        $db = self::db();

        $total = $db->query("SELECT COUNT(*) FROM articles")->fetchColumn();
        $published = $db->query("SELECT COUNT(*) FROM articles WHERE status = 'published'")->fetchColumn();
        $failed = $db->query("SELECT COUNT(*) FROM articles WHERE status = 'failed'")->fetchColumn();

        return [
            'total' => (int) $total,
            'published' => (int) $published,
            'failed' => (int) $failed,
        ];
    }
}
