"""Database operations for fetching hook data"""

import logging
from typing import Dict, Any

import psycopg2
from psycopg2.extras import RealDictCursor

logger = logging.getLogger(__name__)


class DatabaseClient:
    """Client for connecting to PostgreSQL and fetching hook data"""

    def __init__(self):
        self.connection = None
        self.cursor = None

    def connect(self, database_url: str, db_env: str = "staging"):
        """Connect to PostgreSQL database

        Args:
            database_url: PostgreSQL connection URL
            db_env: Environment name for logging
        """
        try:
            self.connection = psycopg2.connect(
                database_url,
                cursor_factory=RealDictCursor
            )
            self.cursor = self.connection.cursor()
            logger.info(f"Connected to {db_env.upper()} database")
        except Exception as e:
            logger.error(f"Failed to connect to {db_env} database: {e}")
            raise

    def close(self):
        """Close database connection"""
        if self.cursor:
            self.cursor.close()
        if self.connection:
            self.connection.close()
        logger.info("Database connection closed")

    def get_hook_data(self, hook_id: str) -> Dict[str, Any]:
        """Fetch hook data from database

        Args:
            hook_id: UUID of the video hook

        Returns:
            Dictionary containing hook and video upload data
        """
        logger.info(f"Fetching hook data for hook_id: {hook_id}")

        # Query to get hook and related video upload data
        # Note: VideoHook has raw_video_upload_id which points to VideoUpload
        # The VideoHookMetadata contains the render_schema
        query = """
            SELECT
                vh.id as hook_id,
                vh.raw_video_upload_id,
                vh.video_upload_ids,
                vh.rendered_video_s3_id,
                vh.rendered_watermarked_video_s3_id,
                vh.download_video_status,
                vh.video_duration,
                vh.creation_source,
                vh.status,
                vh.created_at,
                vh.updated_at,
                vhm.render_schema,
                vu.id as upload_id,
                vu.video_s3_id,
                vu.original_file_ext,
                vu.duration as upload_duration,
                vu.width,
                vu.height
            FROM video_videohook vh
            LEFT JOIN video_videohookmetadata vhm ON vhm.video_hook_id = vh.id
            LEFT JOIN bots_videoupload vu ON vh.raw_video_upload_id = vu.id
            WHERE vh.id = %s
        """

        self.cursor.execute(query, (hook_id,))
        result = self.cursor.fetchone()

        if not result:
            raise ValueError(f"Hook with id {hook_id} not found")

        logger.info(f"Found hook data: {result.get('hook_id')}")
        logger.info(f"Raw video upload ID: {result.get('raw_video_upload_id')}")
        logger.debug(f"Render schema: {result.get('render_schema')}")

        return result