"""Conversation history database module.

Manages conversation and conversation_message tables for chat history.
Uses the same database connection as authentication.
"""

import os
import json
from typing import Optional, List
import asyncpg
from datetime import datetime
from loguru import logger as _log


# Use same pool management as auth_database
_pool: Optional[asyncpg.Pool] = None


async def close_pool() -> None:
    """Close the shared pool. Call once at application shutdown."""
    global _pool
    if _pool is not None:
        await _pool.close()
        _pool = None


async def ensure_pool() -> asyncpg.Pool:
    """Ensure the pool is initialized, initializing it lazily if needed.
    
    Returns:
        The connection pool
        
    Raises:
        RuntimeError: If initialization fails
    """
    global _pool
    
    if _pool is None:
        _pool = await asyncpg.create_pool(
            host=os.environ.get("DB_HOST", "localhost"),
            port=int(os.environ.get("DB_PORT", "5432")),
            database=os.environ["DB_NAME"],
            user=os.environ["DB_USER"],
            password=os.environ["DB_PASSWORD"],
            min_size=2,
            max_size=10,
        )
    
    if _pool is None:
        raise RuntimeError(
            "Conversation database pool not initialized. Check your environment variables."
        )
    
    return _pool


# ---------------------------------------------------------------------------
# Conversation CRUD
# ---------------------------------------------------------------------------

async def create_conversation(
    user_id: int,
    title: str = "New Chat",
    created_by: Optional[int] = None
) -> dict:
    """Create a new conversation.
    
    Args:
        user_id: ID of the user creating the conversation
        title: Initial title (defaults to "New Chat")
        created_by: User ID who created it (defaults to user_id)
    
    Returns:
        Created conversation dict with id, user_id, title, created_at, etc.
    """
    pool = await ensure_pool()
    
    if created_by is None:
        created_by = user_id
    
    async with pool.acquire() as conn:
        row = await conn.fetchrow(
            """
            INSERT INTO conversation (user_id, title, status, created_by, updated_by)
            VALUES ($1, $2, 'active', $3, $3)
            RETURNING id, user_id, title, status, created_at, updated_at
            """,
            user_id, title, created_by
        )
    
    return dict(row)


async def get_conversations(user_id: int, include_deleted: bool = False, limit: int = 10, offset: int = 0) -> dict:
    """Get paginated conversations for a user with preview data.

    Each conversation includes ``last_message`` (last assistant reply preview,
    truncated to 200 chars) and ``response_count`` so the caller does NOT need
    to make individual per-conversation requests.

    Args:
        user_id: User ID to get conversations for
        include_deleted: Whether to include deleted conversations
        limit: Maximum number of conversations to return (default 10)
        offset: Number of conversations to skip for pagination (default 0)

    Returns:
        Dict with keys:
            - conversations: list of enriched conversation dicts
            - total: total count of conversations for this user
            - has_more: whether more pages exist after this one
    """
    pool = await ensure_pool()
    
    status_filter = "status IN ('active', 'deleted')" if include_deleted else "status = 'active'"
    
    async with pool.acquire() as conn:
        # Total count (for has_more calculation)
        total: int = await conn.fetchval(
            f"SELECT COUNT(*) FROM conversation WHERE user_id = $1 AND {status_filter}",
            user_id,
        )

        rows = await conn.fetch(
            f"""
            SELECT 
                c.id, c.user_id, c.title, c.status, c.created_at, c.updated_at,
                (
                    SELECT COUNT(*) 
                    FROM conversation_message 
                    WHERE conversation_id = c.id
                      AND message_author = 'assistant'
                ) AS response_count,
                (
                    SELECT LEFT(message, 200)
                    FROM conversation_message
                    WHERE conversation_id = c.id
                      AND message_author = 'assistant'
                    ORDER BY created_at ASC
                    LIMIT 1
                ) AS last_message
            FROM conversation c
            WHERE c.user_id = $1 AND {status_filter}
            ORDER BY c.updated_at DESC
            LIMIT $2 OFFSET $3
            """,
            user_id,
            limit,
            offset,
        )

    conversations = [dict(row) for row in rows]
    return {
        "conversations": conversations,
        "total": total,
        "has_more": (offset + limit) < total,
    }


async def get_conversation(conversation_id: int) -> Optional[dict]:
    """Get a single conversation by ID.
    
    Args:
        conversation_id: Conversation ID
    
    Returns:
        Conversation dict or None if not found
    """
    pool = await ensure_pool()
    
    async with pool.acquire() as conn:
        row = await conn.fetchrow(
            """
            SELECT id, user_id, title, status, created_at, updated_at
            FROM conversation
            WHERE id = $1
            """,
            conversation_id
        )
    
    return dict(row) if row else None


async def update_conversation_title(
    conversation_id: int,
    title: str,
    updated_by: Optional[int] = None
) -> bool:
    """Update conversation title.
    
    Args:
        conversation_id: Conversation ID
        title: New title
        updated_by: User ID who updated it
    
    Returns:
        True if updated, False if not found
    """
    pool = await ensure_pool()
    
    async with pool.acquire() as conn:
        result = await conn.execute(
            """
            UPDATE conversation
            SET title = $1, updated_at = NOW(), updated_by = $2
            WHERE id = $3
            """,
            title, updated_by, conversation_id
        )
    
    return result.split()[-1] == "1"


async def delete_conversation(
    conversation_id: int,
    deleted_by: Optional[int] = None
) -> bool:
    """Soft delete a conversation.
    
    Args:
        conversation_id: Conversation ID to delete
        deleted_by: User ID who deleted it
    
    Returns:
        True if deleted, False if not found
    """
    pool = await ensure_pool()
    
    async with pool.acquire() as conn:
        result = await conn.execute(
            """
            UPDATE conversation
            SET status = 'deleted', 
                deleted_at = NOW(), 
                updated_at = NOW(), 
                updated_by = $1
            WHERE id = $2
            """,
            deleted_by, conversation_id
        )
    
    return result.split()[-1] == "1"


# ---------------------------------------------------------------------------
# Message CRUD
# ---------------------------------------------------------------------------

async def save_message(
    conversation_id: int,
    message_author: str,  # 'user' or 'assistant'
    message: str,
    message_type: Optional[str] = None,  # 'request' or 'response'
    sql_query: Optional[str] = None,
    metadata: Optional[dict] = None,
    parent_message_id: Optional[int] = None,
    created_by: Optional[int] = None
) -> dict:
    """Save a message to a conversation.
    
    Args:
        conversation_id: Conversation ID
        message_author: 'user' or 'assistant'
        message: Message text content
        message_type: 'request' or 'response'
        sql_query: Optional SQL query (for NL2SQL responses)
        metadata: Optional JSON metadata
        parent_message_id: Optional parent message ID for threading
        created_by: User ID who created the message
    
    Returns:
        Created message dict
    """
    pool = await ensure_pool()
    
    # Auto-determine message_type if not provided
    if message_type is None:
        message_type = 'request' if message_author == 'user' else 'response'
    
    # Serialize metadata to JSON string if present
    metadata_json = json.dumps(metadata) if metadata is not None else None
    
    async with pool.acquire() as conn:
        async with conn.transaction():
            # Guard: verify the conversation row exists before inserting a message.
            # conversation_message.conversation_id has a FK → conversation.id, so
            # inserting without a matching parent row raises a FK violation.
            exists = await conn.fetchval(
                "SELECT 1 FROM conversation WHERE id = $1", conversation_id
            )
            if not exists:
                _log.warning(
                    "[CONV] save_message: conversation_id={} not found in conversation table — skipping save",
                    conversation_id,
                )
                raise ValueError(f"conversation {conversation_id} does not exist")

            # Insert message into conversation_message
            row = await conn.fetchrow(
                """
                INSERT INTO conversation_message (
                    conversation_id, message_type, message_author, message,
                    parent_message_id, sql_query, metadata, created_by, updated_by
                )
                VALUES ($1, $2, $3, $4, $5, $6, $7::jsonb, $8, $8)
                RETURNING id, conversation_id, message_type, message_author,
                          message, created_at, metadata
                """,
                conversation_id, message_type, message_author, message,
                parent_message_id, sql_query, metadata_json, created_by,
            )

            # Keep conversation.updated_at in sync with the latest message
            await conn.execute(
                "UPDATE conversation SET updated_at = NOW() WHERE id = $1",
                conversation_id,
            )
    
    return dict(row)


async def get_messages(conversation_id: int) -> List[dict]:
    """Get all messages for a conversation.

    Args:
        conversation_id: Conversation ID

    Returns:
        List of message dicts, ordered by created_at ASC (chronological)
    """
    pool = await ensure_pool()

    async with pool.acquire() as conn:
        rows = await conn.fetch(
            """
            SELECT
                id, conversation_id, message_type, message_author,
                message, parent_message_id, reference_message_id,
                sql_query, metadata, created_at, bookmarked
            FROM conversation_message
            WHERE conversation_id = $1
            ORDER BY created_at ASC
            """,
            conversation_id
        )

    return [dict(row) for row in rows]


async def toggle_bookmark(message_id: int, bookmarked: bool) -> Optional[dict]:
    """Set or clear the bookmarked flag on a message.

    Args:
        message_id: Message ID
        bookmarked: New bookmark state

    Returns:
        Dict with id, conversation_id, bookmarked — or None if not found
    """
    pool = await ensure_pool()

    async with pool.acquire() as conn:
        row = await conn.fetchrow(
            """
            UPDATE conversation_message
            SET bookmarked = $1, updated_at = NOW()
            WHERE id = $2
            RETURNING id, conversation_id, bookmarked
            """,
            bookmarked,
            message_id,
        )

    return dict(row) if row else None


async def get_user_bookmarks(user_id: int) -> List[dict]:
    """Get all bookmarked assistant messages for a user.

    Only returns messages from active conversations owned by the user.
    Message preview is truncated to 150 characters.

    Args:
        user_id: User ID

    Returns:
        List of bookmark dicts ordered by message created_at DESC
    """
    pool = await ensure_pool()

    async with pool.acquire() as conn:
        rows = await conn.fetch(
            """
            SELECT
                cm.id            AS message_id,
                cm.conversation_id,
                COALESCE(
                    (SELECT message FROM conversation_message
                     WHERE conversation_id = cm.conversation_id
                       AND message_author = 'user'
                       AND id < cm.id
                     ORDER BY id DESC
                     LIMIT 1),
                    c.title
                )                AS conversation_title,
                LEFT(cm.message, 150) AS message_preview,
                cm.created_at
            FROM conversation_message cm
            JOIN conversation c ON c.id = cm.conversation_id
            WHERE cm.bookmarked = TRUE
              AND cm.message_author = 'assistant'
              AND c.status = 'active'
              AND c.user_id = $1
            ORDER BY cm.created_at DESC
            """,
            user_id,
        )

    return [dict(row) for row in rows]


async def get_message(message_id: int) -> Optional[dict]:
    """Get a single message by ID.
    
    Args:
        message_id: Message ID
    
    Returns:
        Message dict or None if not found
    """
    pool = await ensure_pool()
    
    async with pool.acquire() as conn:
        row = await conn.fetchrow(
            """
            SELECT 
                id, conversation_id, message_type, message_author,
                message, parent_message_id, sql_query, metadata, created_at
            FROM conversation_message
            WHERE id = $1
            """,
            message_id
        )
    
    return dict(row) if row else None