"""
Database models and CRUD operations for content pipeline.
"""

import logging
from dataclasses import dataclass
from datetime import datetime
from enum import Enum
from typing import Any, Optional

from database.connection import execute_query, get_cursor

logger = logging.getLogger(__name__)


class TopicStatus(str, Enum):
    PENDING = "pending"
    IN_PROGRESS = "in_progress"
    PUBLISHED = "published"
    SKIPPED = "skipped"
    FAILED = "failed"


class TopicPriority(str, Enum):
    A = "A"  # 80-100 points
    B = "B"  # 50-79 points
    C = "C"  # 20-49 points


class ContentCluster(str, Enum):
    KOSTEN = "kosten"
    RECHT = "recht"
    TECHNIK = "technik"
    BRANCHEN = "branchen"
    VERGLEICH = "vergleich"
    HOWTO = "howto"
    TRENDS = "trends"
    PROBLEME = "probleme"


@dataclass
class Topic:
    id: int
    title: str
    slug: str
    content_cluster: str
    priority: str
    priority_score: int
    target_keywords: str
    secondary_keywords: Optional[str]
    search_intent: str
    status: str
    skip_reason: Optional[str]
    article_id: Optional[int]
    created_at: datetime
    updated_at: datetime

    @classmethod
    def from_row(cls, row: dict[str, Any]) -> "Topic":
        return cls(**row)


@dataclass
class Article:
    id: int
    topic_id: int
    title: str
    slug: str
    content_markdown: str
    content_html: str
    meta_description: str
    word_count: int
    fact_check_passed: bool
    fact_check_log: Optional[str]
    fact_check_attempts: int
    generation_cost_usd: float
    thumbnail_path: Optional[str]
    wp_post_id: Optional[int]
    wp_media_id: Optional[int]
    author_id: Optional[int]
    created_at: datetime
    published_at: Optional[datetime]

    @classmethod
    def from_row(cls, row: dict[str, Any]) -> "Article":
        return cls(**row)


@dataclass
class PublishedContent:
    id: int
    source: str
    title: str
    slug: str
    summary: str
    main_keywords: str
    content_cluster: Optional[str]
    wp_post_id: Optional[int]
    published_at: datetime
    created_at: datetime

    @classmethod
    def from_row(cls, row: dict[str, Any]) -> "PublishedContent":
        return cls(**row)


class TopicRepository:
    """CRUD operations for parketry_content_topics table."""

    TABLE = "parketry_content_topics"

    @classmethod
    def get_by_id(cls, topic_id: int) -> Optional[Topic]:
        query = f"SELECT * FROM {cls.TABLE} WHERE id = %s"
        rows = execute_query(query, (topic_id,))
        return Topic.from_row(rows[0]) if rows else None

    @classmethod
    def get_by_slug(cls, slug: str) -> Optional[Topic]:
        query = f"SELECT * FROM {cls.TABLE} WHERE slug = %s"
        rows = execute_query(query, (slug,))
        return Topic.from_row(rows[0]) if rows else None

    @classmethod
    def get_next_pending(cls) -> Optional[Topic]:
        """Get highest priority pending topic."""
        query = f"""
            SELECT * FROM {cls.TABLE}
            WHERE status = 'pending'
            ORDER BY priority_score DESC, created_at ASC
            LIMIT 1
        """
        rows = execute_query(query)
        return Topic.from_row(rows[0]) if rows else None

    @classmethod
    def get_all_pending(cls) -> list[Topic]:
        query = f"""
            SELECT * FROM {cls.TABLE}
            WHERE status = 'pending'
            ORDER BY priority_score DESC, created_at ASC
        """
        rows = execute_query(query) or []
        return [Topic.from_row(row) for row in rows]

    @classmethod
    def update_status(
        cls,
        topic_id: int,
        status: TopicStatus,
        skip_reason: Optional[str] = None,
        article_id: Optional[int] = None,
    ) -> bool:
        with get_cursor() as cursor:
            query = f"""
                UPDATE {cls.TABLE}
                SET status = %s, skip_reason = %s, article_id = %s, updated_at = NOW()
                WHERE id = %s
            """
            cursor.execute(query, (status.value, skip_reason, article_id, topic_id))
            return cursor.rowcount > 0

    @classmethod
    def count_by_status(cls) -> dict[str, int]:
        query = f"""
            SELECT status, COUNT(*) as count
            FROM {cls.TABLE}
            GROUP BY status
        """
        rows = execute_query(query) or []
        return {row["status"]: row["count"] for row in rows}

    @classmethod
    def insert(
        cls,
        title: str,
        slug: str,
        content_cluster: str,
        priority: str,
        priority_score: int,
        target_keywords: str,
        secondary_keywords: Optional[str],
        search_intent: str,
    ) -> int:
        with get_cursor() as cursor:
            query = f"""
                INSERT INTO {cls.TABLE}
                (title, slug, content_cluster, priority, priority_score,
                 target_keywords, secondary_keywords, search_intent, status)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, 'pending')
            """
            cursor.execute(
                query,
                (
                    title,
                    slug,
                    content_cluster,
                    priority,
                    priority_score,
                    target_keywords,
                    secondary_keywords,
                    search_intent,
                ),
            )
            return cursor.lastrowid


class ArticleRepository:
    """CRUD operations for parketry_articles table."""

    TABLE = "parketry_articles"

    @classmethod
    def get_by_id(cls, article_id: int) -> Optional[Article]:
        query = f"SELECT * FROM {cls.TABLE} WHERE id = %s"
        rows = execute_query(query, (article_id,))
        return Article.from_row(rows[0]) if rows else None

    @classmethod
    def get_last_published(cls) -> Optional[Article]:
        query = f"""
            SELECT * FROM {cls.TABLE}
            WHERE wp_post_id IS NOT NULL
            ORDER BY published_at DESC
            LIMIT 1
        """
        rows = execute_query(query)
        return Article.from_row(rows[0]) if rows else None

    @classmethod
    def insert(
        cls,
        topic_id: int,
        title: str,
        slug: str,
        content_markdown: str,
        content_html: str,
        meta_description: str,
        word_count: int,
        fact_check_passed: bool,
        fact_check_log: Optional[str],
        fact_check_attempts: int,
        generation_cost_usd: float,
    ) -> int:
        with get_cursor() as cursor:
            query = f"""
                INSERT INTO {cls.TABLE}
                (topic_id, title, slug, content_markdown, content_html,
                 meta_description, word_count, fact_check_passed, fact_check_log,
                 fact_check_attempts, generation_cost_usd)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
            cursor.execute(
                query,
                (
                    topic_id,
                    title,
                    slug,
                    content_markdown,
                    content_html,
                    meta_description,
                    word_count,
                    fact_check_passed,
                    fact_check_log,
                    fact_check_attempts,
                    generation_cost_usd,
                ),
            )
            return cursor.lastrowid

    @classmethod
    def update_wordpress_ids(
        cls,
        article_id: int,
        wp_post_id: int,
        wp_media_id: Optional[int],
        author_id: int,
    ) -> bool:
        with get_cursor() as cursor:
            query = f"""
                UPDATE {cls.TABLE}
                SET wp_post_id = %s, wp_media_id = %s, author_id = %s,
                    published_at = NOW()
                WHERE id = %s
            """
            cursor.execute(query, (wp_post_id, wp_media_id, author_id, article_id))
            return cursor.rowcount > 0

    @classmethod
    def update_thumbnail(cls, article_id: int, thumbnail_path: str) -> bool:
        with get_cursor() as cursor:
            query = f"""
                UPDATE {cls.TABLE}
                SET thumbnail_path = %s
                WHERE id = %s
            """
            cursor.execute(query, (thumbnail_path, article_id))
            return cursor.rowcount > 0


class PublishedContentRepository:
    """CRUD operations for parketry_published_content table."""

    TABLE = "parketry_published_content"

    @classmethod
    def get_all(cls) -> list[PublishedContent]:
        query = f"SELECT * FROM {cls.TABLE} ORDER BY published_at DESC"
        rows = execute_query(query) or []
        return [PublishedContent.from_row(row) for row in rows]

    @classmethod
    def search_fulltext(cls, search_term: str) -> list[PublishedContent]:
        """Search using FULLTEXT index on title and summary."""
        query = f"""
            SELECT *, MATCH(title, summary) AGAINST(%s) as relevance
            FROM {cls.TABLE}
            WHERE MATCH(title, summary) AGAINST(%s)
            ORDER BY relevance DESC
        """
        rows = execute_query(query, (search_term, search_term)) or []
        return [PublishedContent.from_row(row) for row in rows]

    @classmethod
    def insert(
        cls,
        source: str,
        title: str,
        slug: str,
        summary: str,
        main_keywords: str,
        wp_post_id: Optional[int],
        published_at: datetime,
        content_cluster: Optional[str] = None,
    ) -> int:
        with get_cursor() as cursor:
            query = f"""
                INSERT INTO {cls.TABLE}
                (source, title, slug, summary, main_keywords, content_cluster, wp_post_id, published_at)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
            """
            cursor.execute(
                query,
                (source, title, slug, summary, main_keywords, content_cluster, wp_post_id, published_at),
            )
            return cursor.lastrowid

    @classmethod
    def insert_from_article(cls, article: Article) -> int:
        """Insert into published_content from a completed article."""
        return cls.insert(
            source="pipeline",
            title=article.title,
            slug=article.slug,
            summary=article.meta_description,
            main_keywords="",  # Will be extracted from topic
            wp_post_id=article.wp_post_id,
            published_at=article.published_at or datetime.now(),
        )

    @classmethod
    def get_link_targets(
        cls,
        cluster: Optional[str] = None,
        exclude_wp_id: Optional[int] = None,
        limit: int = 15,
    ) -> list[dict]:
        """
        Find published articles suitable for internal linking.

        Prioritizes articles in the same content cluster, then by recency.

        Args:
            cluster: Content cluster to prioritize
            exclude_wp_id: WordPress post ID to exclude
            limit: Maximum results to return

        Returns:
            List of dicts with wp_post_id, slug, title, summary
        """
        query = f"""
            SELECT wp_post_id, slug, title, summary, content_cluster
            FROM {cls.TABLE}
            WHERE slug IS NOT NULL AND slug != ''
            AND (%s IS NULL OR wp_post_id != %s)
            ORDER BY
                CASE WHEN content_cluster = %s THEN 0 ELSE 1 END,
                published_at DESC
            LIMIT %s
        """
        rows = execute_query(query, (exclude_wp_id, exclude_wp_id, cluster, limit))
        if not rows:
            return []

        return [
            {
                "wp_post_id": row["wp_post_id"],
                "slug": row["slug"],
                "title": row["title"],
                "summary": row.get("summary", ""),
            }
            for row in rows
        ]

    @classmethod
    def delete_by_wp_post_id(cls, wp_post_id: int) -> bool:
        """Delete a published content entry by WordPress post ID."""
        with get_cursor() as cursor:
            query = f"DELETE FROM {cls.TABLE} WHERE wp_post_id = %s"
            cursor.execute(query, (wp_post_id,))
            return cursor.rowcount > 0