"""Data Models for ChefSystem.

Defines Recipe and Output models with CRUD operations.
"""

import sqlite3
import logging
from dataclasses import dataclass, asdict
from datetime import datetime
from typing import Optional, List, Dict, Any

logger = logging.getLogger(__name__)


@dataclass
class Recipe:
    """Recipe model representing a prompt template.

    Attributes:
        id: Unique identifier (auto-generated).
        name: Recipe name (must be unique).
        prompt_text: The actual prompt template text.
        tags: Comma-separated tags for categorization.
        description: Short description of the recipe.
        notes: Additional notes or instructions.
        created_at: Timestamp of creation.
        updated_at: Timestamp of last update.
    """

    id: Optional[int] = None
    name: str = ""
    prompt_text: str = ""
    tags: str = ""
    description: str = ""
    notes: str = ""
    created_at: Optional[datetime] = None
    updated_at: Optional[datetime] = None

    def to_dict(self) -> Dict[str, Any]:
        """Convert recipe to dictionary for serialization.

        Returns:
            dict: Recipe data as dictionary with datetime as ISO strings.
        """
        data = asdict(self)
        # Convert datetime objects to ISO format strings
        if self.created_at:
            data['created_at'] = self.created_at.isoformat()
        if self.updated_at:
            data['updated_at'] = self.updated_at.isoformat()
        return data

    @classmethod
    def from_dict(cls, data: Dict[str, Any]) -> 'Recipe':
        """Create Recipe from dictionary.

        Args:
            data: Dictionary with recipe data.

        Returns:
            Recipe: New recipe instance.
        """
        # Parse datetime strings back to datetime objects
        if 'created_at' in data and isinstance(data['created_at'], str):
            data['created_at'] = datetime.fromisoformat(data['created_at'])
        if 'updated_at' in data and isinstance(data['updated_at'], str):
            data['updated_at'] = datetime.fromisoformat(data['updated_at'])
        return cls(**data)

    @classmethod
    def from_row(cls, row: sqlite3.Row) -> 'Recipe':
        """Create Recipe from database row.

        Args:
            row: SQLite row object.

        Returns:
            Recipe: New recipe instance.
        """
        return cls(
            id=row['id'],
            name=row['name'],
            prompt_text=row['prompt_text'],
            tags=row['tags'] or "",
            description=row['description'] or "",
            notes=row['notes'] or "",
            created_at=datetime.fromisoformat(row['created_at']) if row['created_at'] else None,
            updated_at=datetime.fromisoformat(row['updated_at']) if row['updated_at'] else None
        )

    @classmethod
    def create(cls, db, name: str, prompt_text: str,
               tags: str = "", description: str = "", notes: str = "") -> Optional['Recipe']:
        """Create a new recipe in the database.

        Args:
            db: DatabaseManager instance.
            name: Recipe name (must be unique).
            prompt_text: Prompt template text.
            tags: Comma-separated tags.
            description: Short description.
            notes: Additional notes.

        Returns:
            Recipe: Created recipe with id and timestamps, or None on error.
        """
        sql = """
        INSERT INTO recipes (name, prompt_text, tags, description, notes)
        VALUES (?, ?, ?, ?, ?)
        """
        try:
            conn = db.get_connection()
            cursor = conn.execute(sql, (name, prompt_text, tags, description, notes))
            conn.commit()

            recipe_id = cursor.lastrowid
            logger.info(f"Created recipe with ID {recipe_id}: {name}")

            # Retrieve the created recipe to get timestamps
            return cls.get_by_id(db, recipe_id)

        except sqlite3.IntegrityError as e:
            logger.error(f"Failed to create recipe (duplicate name?): {e}")
            return None
        except sqlite3.Error as e:
            logger.error(f"Database error creating recipe: {e}", exc_info=True)
            return None

    @classmethod
    def get_by_id(cls, db, recipe_id: int) -> Optional['Recipe']:
        """Retrieve a recipe by ID.

        Args:
            db: DatabaseManager instance.
            recipe_id: Recipe ID to retrieve.

        Returns:
            Recipe or None if not found.
        """
        sql = "SELECT * FROM recipes WHERE id = ?"
        try:
            conn = db.get_connection()
            cursor = conn.execute(sql, (recipe_id,))
            row = cursor.fetchone()

            if row:
                return cls.from_row(row)
            return None

        except sqlite3.Error as e:
            logger.error(f"Database error retrieving recipe {recipe_id}: {e}", exc_info=True)
            return None

    @classmethod
    def get_all(cls, db) -> List['Recipe']:
        """Retrieve all recipes.

        Args:
            db: DatabaseManager instance.

        Returns:
            List of all recipes, ordered by creation date (newest first).
        """
        sql = "SELECT * FROM recipes ORDER BY created_at DESC"
        try:
            conn = db.get_connection()
            cursor = conn.execute(sql)
            rows = cursor.fetchall()

            return [cls.from_row(row) for row in rows]

        except sqlite3.Error as e:
            logger.error(f"Database error retrieving all recipes: {e}", exc_info=True)
            return []

    @classmethod
    def update(cls, db, recipe_id: int, **kwargs) -> bool:
        """Update an existing recipe.

        Args:
            db: DatabaseManager instance.
            recipe_id: ID of recipe to update.
            **kwargs: Fields to update (name, prompt_text, tags, description, notes).

        Returns:
            bool: True if successful, False otherwise.
        """
        # Build UPDATE query dynamically based on provided kwargs
        allowed_fields = {'name', 'prompt_text', 'tags', 'description', 'notes'}
        update_fields = {k: v for k, v in kwargs.items() if k in allowed_fields}

        if not update_fields:
            logger.warning("No valid fields provided for update")
            return False

        # Always update updated_at timestamp
        set_clause = ", ".join([f"{field} = ?" for field in update_fields.keys()])
        set_clause += ", updated_at = CURRENT_TIMESTAMP"

        sql = f"UPDATE recipes SET {set_clause} WHERE id = ?"
        values = list(update_fields.values()) + [recipe_id]

        try:
            conn = db.get_connection()
            cursor = conn.execute(sql, values)
            conn.commit()

            if cursor.rowcount > 0:
                logger.info(f"Updated recipe {recipe_id}")
                return True
            else:
                logger.warning(f"No recipe found with ID {recipe_id}")
                return False

        except sqlite3.IntegrityError as e:
            logger.error(f"Failed to update recipe (duplicate name?): {e}")
            return False
        except sqlite3.Error as e:
            logger.error(f"Database error updating recipe {recipe_id}: {e}", exc_info=True)
            return False

    @classmethod
    def delete(cls, db, recipe_id: int) -> bool:
        """Delete a recipe from the database.

        Args:
            db: DatabaseManager instance.
            recipe_id: ID of recipe to delete.

        Returns:
            bool: True if successful, False otherwise.
        """
        sql = "DELETE FROM recipes WHERE id = ?"
        try:
            conn = db.get_connection()
            cursor = conn.execute(sql, (recipe_id,))
            conn.commit()

            if cursor.rowcount > 0:
                logger.info(f"Deleted recipe {recipe_id} (CASCADE will delete outputs)")
                return True
            else:
                logger.warning(f"No recipe found with ID {recipe_id}")
                return False

        except sqlite3.Error as e:
            logger.error(f"Database error deleting recipe {recipe_id}: {e}", exc_info=True)
            return False

    @classmethod
    def search(cls, db, query: str = "", tags: Optional[List[str]] = None) -> List['Recipe']:
        """Search recipes by name and/or tags.

        Args:
            db: DatabaseManager instance.
            query: Search query for recipe name (case-insensitive).
            tags: List of tags to filter by (any match).

        Returns:
            List of matching recipes.
        """
        conditions = []
        params = []

        # Search query across name, description, notes (case-insensitive)
        if query and query.strip():
            query_pattern = f"%{query.strip()}%"
            # Search in name OR description OR notes
            conditions.append("(name LIKE ? OR description LIKE ? OR notes LIKE ?)")
            params.extend([query_pattern, query_pattern, query_pattern])

        # Tags filter (ANY match)
        if tags:
            # Check if any of the provided tags appear in the comma-separated tags field
            tag_conditions = " OR ".join(["tags LIKE ?" for _ in tags])
            if tag_conditions:
                conditions.append(f"({tag_conditions})")
                params.extend([f"%{tag}%" for tag in tags])

        # Build WHERE clause
        where_clause = " AND ".join(conditions) if conditions else "1=1"
        sql = f"SELECT * FROM recipes WHERE {where_clause} ORDER BY created_at DESC"

        try:
            conn = db.get_connection()
            cursor = conn.execute(sql, params)
            rows = cursor.fetchall()

            return [cls.from_row(row) for row in rows]

        except sqlite3.Error as e:
            logger.error(f"Database error searching recipes: {e}", exc_info=True)
            return []


@dataclass
class Output:
    """Output model representing a generated output file.

    Attributes:
        id: Unique identifier (auto-generated).
        recipe_id: Foreign key to the recipe.
        filename: Name of the output file.
        filepath: Relative path from outputs/ directory.
        file_type: File extension (.pdf, .docx, etc.).
        file_size: Size in bytes.
        execution_notes: Notes about this execution.
        generated_at: Timestamp of generation.
    """

    id: Optional[int] = None
    recipe_id: int = 0
    filename: str = ""
    filepath: str = ""
    file_type: str = ""
    file_size: int = 0
    execution_notes: str = ""
    generated_at: Optional[datetime] = None

    def to_dict(self) -> Dict[str, Any]:
        """Convert output to dictionary for serialization.

        Returns:
            dict: Output data as dictionary with datetime as ISO string.
        """
        data = asdict(self)
        if self.generated_at:
            data['generated_at'] = self.generated_at.isoformat()
        return data

    @classmethod
    def from_dict(cls, data: Dict[str, Any]) -> 'Output':
        """Create Output from dictionary.

        Args:
            data: Dictionary with output data.

        Returns:
            Output: New output instance.
        """
        if 'generated_at' in data and isinstance(data['generated_at'], str):
            data['generated_at'] = datetime.fromisoformat(data['generated_at'])
        return cls(**data)

    @classmethod
    def from_row(cls, row: sqlite3.Row) -> 'Output':
        """Create Output from database row.

        Args:
            row: SQLite row object.

        Returns:
            Output: New output instance.
        """
        return cls(
            id=row['id'],
            recipe_id=row['recipe_id'],
            filename=row['filename'],
            filepath=row['filepath'],
            file_type=row['file_type'] or "",
            file_size=row['file_size'] or 0,
            execution_notes=row['execution_notes'] or "",
            generated_at=datetime.fromisoformat(row['generated_at']) if row['generated_at'] else None
        )

    @classmethod
    def create(cls, db, recipe_id: int, filename: str, filepath: str,
               file_type: str = "", file_size: int = 0,
               execution_notes: str = "") -> Optional['Output']:
        """Create a new output record in the database.

        Args:
            db: DatabaseManager instance.
            recipe_id: ID of the associated recipe.
            filename: Output filename.
            filepath: Relative filepath from outputs/.
            file_type: File extension.
            file_size: File size in bytes.
            execution_notes: Notes about this execution.

        Returns:
            Output: Created output with id and timestamp, or None on error.
        """
        sql = """
        INSERT INTO outputs (recipe_id, filename, filepath, file_type, file_size, execution_notes)
        VALUES (?, ?, ?, ?, ?, ?)
        """
        try:
            conn = db.get_connection()
            cursor = conn.execute(sql, (recipe_id, filename, filepath, file_type, file_size, execution_notes))
            conn.commit()

            output_id = cursor.lastrowid
            logger.info(f"Created output with ID {output_id} for recipe {recipe_id}: {filename}")

            return cls.get_by_id(db, output_id)

        except sqlite3.IntegrityError as e:
            logger.error(f"Failed to create output (foreign key violation?): {e}")
            return None
        except sqlite3.Error as e:
            logger.error(f"Database error creating output: {e}", exc_info=True)
            return None

    @classmethod
    def get_by_id(cls, db, output_id: int) -> Optional['Output']:
        """Retrieve an output by ID.

        Args:
            db: DatabaseManager instance.
            output_id: Output ID to retrieve.

        Returns:
            Output or None if not found.
        """
        sql = "SELECT * FROM outputs WHERE id = ?"
        try:
            conn = db.get_connection()
            cursor = conn.execute(sql, (output_id,))
            row = cursor.fetchone()

            if row:
                return cls.from_row(row)
            return None

        except sqlite3.Error as e:
            logger.error(f"Database error retrieving output {output_id}: {e}", exc_info=True)
            return None

    @classmethod
    def get_by_recipe(cls, db, recipe_id: int) -> List['Output']:
        """Retrieve all outputs for a specific recipe.

        Args:
            db: DatabaseManager instance.
            recipe_id: Recipe ID to filter by.

        Returns:
            List of outputs for the recipe, ordered by generation date (newest first).
        """
        sql = "SELECT * FROM outputs WHERE recipe_id = ? ORDER BY generated_at DESC"
        try:
            conn = db.get_connection()
            cursor = conn.execute(sql, (recipe_id,))
            rows = cursor.fetchall()

            return [cls.from_row(row) for row in rows]

        except sqlite3.Error as e:
            logger.error(f"Database error retrieving outputs for recipe {recipe_id}: {e}", exc_info=True)
            return []

    @classmethod
    def get_all(cls, db) -> List['Output']:
        """Retrieve all outputs.

        Args:
            db: DatabaseManager instance.

        Returns:
            List of all outputs, ordered by generation date (newest first).
        """
        sql = "SELECT * FROM outputs ORDER BY generated_at DESC"
        try:
            conn = db.get_connection()
            cursor = conn.execute(sql)
            rows = cursor.fetchall()

            return [cls.from_row(row) for row in rows]

        except sqlite3.Error as e:
            logger.error(f"Database error retrieving all outputs: {e}", exc_info=True)
            return []

    @classmethod
    def update(cls, db, output_id: int, **kwargs) -> bool:
        """Update an existing output.

        Args:
            db: DatabaseManager instance.
            output_id: ID of output to update.
            **kwargs: Fields to update.

        Returns:
            bool: True if successful, False otherwise.
        """
        allowed_fields = {'filename', 'filepath', 'file_type', 'file_size', 'execution_notes'}
        update_fields = {k: v for k, v in kwargs.items() if k in allowed_fields}

        if not update_fields:
            logger.warning("No valid fields provided for update")
            return False

        set_clause = ", ".join([f"{field} = ?" for field in update_fields.keys()])
        sql = f"UPDATE outputs SET {set_clause} WHERE id = ?"
        values = list(update_fields.values()) + [output_id]

        try:
            conn = db.get_connection()
            cursor = conn.execute(sql, values)
            conn.commit()

            if cursor.rowcount > 0:
                logger.info(f"Updated output {output_id}")
                return True
            else:
                logger.warning(f"No output found with ID {output_id}")
                return False

        except sqlite3.Error as e:
            logger.error(f"Database error updating output {output_id}: {e}", exc_info=True)
            return False

    @classmethod
    def delete(cls, db, output_id: int) -> bool:
        """Delete an output from the database.

        Args:
            db: DatabaseManager instance.
            output_id: ID of output to delete.

        Returns:
            bool: True if successful, False otherwise.
        """
        sql = "DELETE FROM outputs WHERE id = ?"
        try:
            conn = db.get_connection()
            cursor = conn.execute(sql, (output_id,))
            conn.commit()

            if cursor.rowcount > 0:
                logger.info(f"Deleted output {output_id}")
                return True
            else:
                logger.warning(f"No output found with ID {output_id}")
                return False

        except sqlite3.Error as e:
            logger.error(f"Database error deleting output {output_id}: {e}", exc_info=True)
            return False