"""Database Manager for ChefSystem.

Handles SQLite database connections, schema initialization, and migrations.
"""

import sqlite3
import logging
from pathlib import Path
from typing import Optional

logger = logging.getLogger(__name__)


class DatabaseManager:
    """Manages SQLite database connection and schema operations.

    Implements singleton pattern to ensure only one database connection exists.
    Supports context manager for transaction handling.
    """

    _instance: Optional['DatabaseManager'] = None
    _initialized: bool = False

    def __new__(cls, db_path: str) -> 'DatabaseManager':
        """Create or return the singleton instance.

        Args:
            db_path: Path to the SQLite database file.

        Returns:
            DatabaseManager: The singleton instance.
        """
        if cls._instance is None:
            cls._instance = super().__new__(cls)
        return cls._instance

    def __init__(self, db_path: str):
        """Initialize the database manager.

        Args:
            db_path: Path to the SQLite database file.
        """
        # Only initialize once (singleton pattern)
        if not DatabaseManager._initialized:
            self.db_path = Path(db_path)
            self.connection: Optional[sqlite3.Connection] = None
            DatabaseManager._initialized = True
            logger.info(f"DatabaseManager initialized with path: {self.db_path}")

    def get_connection(self) -> sqlite3.Connection:
        """Establish connection to the database.

        Creates a new connection if one doesn't exist, or returns the existing one.
        Enables foreign key constraints and sets row factory for dict-like access.

        Returns:
            sqlite3.Connection: Database connection object.
        """
        if self.connection is None:
            try:
                # Ensure parent directory exists
                self.db_path.parent.mkdir(parents=True, exist_ok=True)

                # Create connection
                self.connection = sqlite3.connect(str(self.db_path))

                # Enable foreign key constraints
                self.connection.execute("PRAGMA foreign_keys = ON")

                # Use Row factory for dict-like access
                self.connection.row_factory = sqlite3.Row

                logger.info(f"Database connection established: {self.db_path}")
            except sqlite3.Error as e:
                logger.error(f"Failed to connect to database: {e}", exc_info=True)
                raise

        return self.connection

    def close(self) -> None:
        """Close the database connection."""
        if self.connection is not None:
            try:
                self.connection.close()
                self.connection = None
                logger.info("Database connection closed")
            except sqlite3.Error as e:
                logger.error(f"Error closing database connection: {e}", exc_info=True)
                raise

    def __enter__(self) -> sqlite3.Connection:
        """Context manager entry.

        Returns:
            sqlite3.Connection: Database connection.
        """
        return self.get_connection()

    def __exit__(self, exc_type, exc_val, exc_tb) -> None:
        """Context manager exit.

        Commits transaction on success, rolls back on error.

        Args:
            exc_type: Exception type if an error occurred.
            exc_val: Exception value if an error occurred.
            exc_tb: Exception traceback if an error occurred.
        """
        if exc_type is None:
            # No exception, commit the transaction
            try:
                self.connection.commit()
                logger.debug("Transaction committed")
            except sqlite3.Error as e:
                logger.error(f"Error committing transaction: {e}", exc_info=True)
                raise
        else:
            # Exception occurred, rollback the transaction
            try:
                self.connection.rollback()
                logger.warning(f"Transaction rolled back due to {exc_type.__name__}")
            except sqlite3.Error as e:
                logger.error(f"Error rolling back transaction: {e}", exc_info=True)

    def init_database(self) -> None:
        """Create database tables if they don't exist.

        Creates the recipes and outputs tables according to the schema
        defined in Bootstrap.md.
        """
        try:
            conn = self.get_connection()
            self._create_recipes_table(conn)
            self._create_outputs_table(conn)
            conn.commit()
            logger.info("Database schema initialized successfully")
        except sqlite3.Error as e:
            logger.error(f"Failed to initialize database schema: {e}", exc_info=True)
            raise

    def _create_recipes_table(self, conn: sqlite3.Connection) -> None:
        """Create the recipes table.

        Args:
            conn: Database connection.
        """
        sql = """
        CREATE TABLE IF NOT EXISTS recipes (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL UNIQUE,
            prompt_text TEXT NOT NULL,
            tags TEXT,
            description TEXT,
            notes TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        """
        try:
            conn.execute(sql)
            logger.debug("Recipes table created or verified")
        except sqlite3.Error as e:
            logger.error(f"Failed to create recipes table: {e}", exc_info=True)
            raise

    def _create_outputs_table(self, conn: sqlite3.Connection) -> None:
        """Create the outputs table.

        Args:
            conn: Database connection.
        """
        sql = """
        CREATE TABLE IF NOT EXISTS outputs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            recipe_id INTEGER NOT NULL,
            filename TEXT NOT NULL,
            filepath TEXT NOT NULL,
            file_type TEXT,
            file_size INTEGER,
            execution_notes TEXT,
            generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (recipe_id) REFERENCES recipes(id) ON DELETE CASCADE
        )
        """
        try:
            conn.execute(sql)
            logger.debug("Outputs table created or verified")
        except sqlite3.Error as e:
            logger.error(f"Failed to create outputs table: {e}", exc_info=True)
            raise

    def migrate(self) -> None:
        """Run database migrations if needed.

        Placeholder for future migration logic when schema changes are required.
        """
        # Future migrations will be implemented here
        logger.debug("No migrations to run")
        pass