"""
Database models for the MAB Recommendation System.
Defines the structure of all database tables.
"""

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


@dataclass
class FeedbackDB:
    """User interaction feedback database model."""
    user_id: int
    product_id: str
    event_type: str  # click, view, purchase, cart, etc.
    session: datetime
    active_user: bool = True
    
    def to_dict(self) -> Dict[str, Any]:
        return {
            'user_id': self.user_id,
            'product_id': self.product_id,
            'event_type': self.event_type,
            'session': self.session.isoformat() if self.session else None,
            'active_user': self.active_user
        }


@dataclass
class ProductCatalog:
    """Product catalog database model."""
    product_id: int
    product_name: str
    brand_name: str
    category: str
    price: float
    description: Optional[str] = None
    image_url: Optional[str] = None
    
    def to_dict(self) -> Dict[str, Any]:
        return {
            'product_id': self.product_id,
            'product_name': self.product_name,
            'brand_name': self.brand_name,
            'category': self.category,
            'price': self.price,
            'description': self.description,
            'image_url': self.image_url
        }


@dataclass
class UserDemographics:
    """User demographics and profile database model."""
    user_id: int
    name: str
    gender: Optional[str] = None
    age: Optional[str] = None
    user_cluster_id: Optional[int] = None
    created_at: Optional[datetime] = None
    last_active: Optional[datetime] = None
    
    def to_dict(self) -> Dict[str, Any]:
        return {
            'user_id': self.user_id,
            'name': self.name,
            'gender': self.gender,
            'age': self.age,
            'user_cluster_id': self.user_cluster_id,
            'created_at': self.created_at.isoformat() if self.created_at else None,
            'last_active': self.last_active.isoformat() if self.last_active else None
        }


@dataclass
class SearchHistory:
    """User search history database model."""
    user_id: int
    query: str
    session_id: int
    product_id: Optional[int] = None
    timestamp: Optional[datetime] = None
    
    def to_dict(self) -> Dict[str, Any]:
        return {
            'user_id': self.user_id,
            'query': self.query,
            'product_id': self.product_id,
            'session_id': self.session_id,
            'timestamp': self.timestamp.isoformat() if self.timestamp else None
        }


class DatabaseManager:
    """Manages SQLite database connections and initialization."""
    
    def __init__(self, db_path: str = "recommender_system.db"):
        self.db_path = db_path
        
    def connect(self) -> sqlite3.Connection:
        """Create a new database connection for thread safety."""
        connection = sqlite3.connect(self.db_path)
        connection.row_factory = sqlite3.Row
        return connection
    
    def close(self, connection: sqlite3.Connection):
        """Close a specific database connection."""
        if connection:
            connection.close()
    
    def init_database(self):
        """Initialize database tables."""
        conn = self.connect()
        cursor = conn.cursor()
        
        # Create feedback_db table
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS feedback_db (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                product_id TEXT NOT NULL,
                event_type TEXT NOT NULL,
                session DATETIME NOT NULL,
                active_user BOOLEAN DEFAULT 1,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES user_demographics (user_id),
                FOREIGN KEY (product_id) REFERENCES product_catalog (product_id)
            )
        ''')
        
        # Create product_catalog table
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS product_catalog (
                product_id INTEGER PRIMARY KEY,
                product_name TEXT NOT NULL,
                brand_name TEXT NOT NULL,
                category TEXT NOT NULL,
                price REAL NOT NULL,
                description TEXT,
                image_url TEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        # Create user_demographics table
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS user_demographics (
                user_id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                gender TEXT,
                age TEXT,
                user_cluster_id INTEGER,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                last_active DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        # Create search_history table
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS search_history (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                query TEXT NOT NULL,
                product_id INTEGER,
                session_id INTEGER NOT NULL,
                timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES user_demographics (user_id),
                FOREIGN KEY (product_id) REFERENCES product_catalog (product_id)
            )
        ''')
        
        # Create indexes for better performance
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_feedback_user_id ON feedback_db (user_id)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_feedback_product_id ON feedback_db (product_id)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_feedback_event_type ON feedback_db (event_type)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_feedback_session ON feedback_db (session)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_product_category ON product_catalog (category)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_product_brand ON product_catalog (brand_name)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_user_cluster ON user_demographics (user_cluster_id)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_search_user_id ON search_history (user_id)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_search_session_id ON search_history (session_id)')
        
        conn.commit()
        print("✅ Database tables initialized successfully")
    
    def __enter__(self):
        self._current_connection = self.connect()
        return self._current_connection
    
    def __exit__(self, exc_type, exc_val, exc_tb):
        if hasattr(self, '_current_connection'):
            self.close(self._current_connection)