"""
Database models and management for Page Asist
Using SQLAlchemy with SQLite for persistent storage
"""
from sqlalchemy import create_engine, Column, Integer, String, Text, DateTime, Boolean, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, Session
from datetime import datetime
import json
from pathlib import Path
from typing import List, Optional, Dict

# Base configuration
BASE_DIR = Path(__file__).parent.parent
DATABASE_URL = f"sqlite:///{BASE_DIR}/conversations.db"

# SQLAlchemy setup
engine = create_engine(DATABASE_URL, echo=False)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()

# Database Models
class DBConversation(Base):
    """Model pentru conversații salvate"""
    __tablename__ = "conversations"
    
    id = Column(String, primary_key=True, index=True)
    subiect = Column(String, nullable=False)
    start_time = Column(DateTime, default=datetime.utcnow)
    end_time = Column(DateTime, nullable=True)
    numar_runde = Column(Integer, nullable=False)
    numar_agenti = Column(Integer, nullable=False)
    total_mesaje = Column(Integer, default=0)
    total_idei_unice = Column(Integer, default=0)
    is_completed = Column(Boolean, default=False)
    config_json = Column(Text)  # JSON serialized configuration
    
    # Relationship with messages
    messages = relationship("DBMessage", back_populates="conversation", cascade="all, delete-orphan")
    agents = relationship("DBAgent", back_populates="conversation", cascade="all, delete-orphan")


class DBAgent(Base):
    """Model pentru agenți AI folosiți în conversație"""
    __tablename__ = "agents"
    
    id = Column(Integer, primary_key=True, index=True)
    conversation_id = Column(String, ForeignKey("conversations.id"))
    ip = Column(String, nullable=False)
    port = Column(String, nullable=False)
    rol = Column(String, nullable=False)
    model = Column(String, nullable=False)
    max_tokens = Column(Integer, nullable=False)
    timeout = Column(Integer, nullable=False)
    
    # Relationship
    conversation = relationship("DBConversation", back_populates="agents")


class DBMessage(Base):
    """Model pentru mesajele din conversații"""
    __tablename__ = "messages"
    
    id = Column(Integer, primary_key=True, index=True)
    conversation_id = Column(String, ForeignKey("conversations.id"))
    runda = Column(Integer, nullable=False)
    agent_url = Column(String, nullable=False)
    agent_role = Column(String, nullable=False)
    content = Column(Text, nullable=False)
    tokens = Column(Integer, nullable=True)
    idei_noi = Column(Integer, default=0)
    timestamp = Column(DateTime, default=datetime.utcnow)
    
    # Relationship
    conversation = relationship("DBConversation", back_populates="messages")


class DBIdea(Base):
    """Model pentru ideile unice extrase din conversații"""
    __tablename__ = "ideas"
    
    id = Column(Integer, primary_key=True, index=True)
    conversation_id = Column(String, ForeignKey("conversations.id"))
    idea_text = Column(String, nullable=False, unique=True)
    first_mentioned_round = Column(Integer, nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)


# Database management functions
def create_tables():
    """Creează tabelele în baza de date"""
    Base.metadata.create_all(bind=engine)


def get_db() -> Session:
    """Dependency pentru obținerea unei sesiuni de bază de date"""
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()


def get_db_session() -> Session:
    """Obține o sesiune de bază de date pentru utilizare directă"""
    return SessionLocal()


# Conversation CRUD operations
class ConversationDB:
    """Manager pentru operații de bază de date cu conversații"""
    
    @staticmethod
    def create_conversation(
        conversation_id: str,
        subiect: str,
        numar_runde: int,
        agenti: List[Dict],
        config: Dict
    ) -> DBConversation:
        """Creează o conversație nouă în baza de date"""
        db = get_db_session()
        try:
            # Creează conversația principală
            conversation = DBConversation(
                id=conversation_id,
                subiect=subiect,
                numar_runde=numar_runde,
                numar_agenti=len(agenti),
                config_json=json.dumps(config)
            )
            db.add(conversation)
            
            # Adaugă agenții
            for agent_data in agenti:
                agent = DBAgent(
                    conversation_id=conversation_id,
                    ip=agent_data["ip"],
                    port=agent_data["port"],
                    rol=agent_data["rol"],
                    model=agent_data["model"],
                    max_tokens=agent_data["max_tokens"],
                    timeout=agent_data["timeout"]
                )
                db.add(agent)
            
            db.commit()
            db.refresh(conversation)
            return conversation
            
        except Exception as e:
            db.rollback()
            raise e
        finally:
            db.close()
    
    @staticmethod
    def add_message(
        conversation_id: str,
        runda: int,
        agent_url: str,
        agent_role: str,
        content: str,
        tokens: Optional[int] = None,
        idei_noi: int = 0
    ) -> DBMessage:
        """Adaugă un mesaj nou în conversație"""
        db = get_db_session()
        try:
            message = DBMessage(
                conversation_id=conversation_id,
                runda=runda,
                agent_url=agent_url,
                agent_role=agent_role,
                content=content,
                tokens=tokens,
                idei_noi=idei_noi
            )
            db.add(message)
            
            # Actualizează contorul de mesaje în conversație
            conversation = db.query(DBConversation).filter(
                DBConversation.id == conversation_id
            ).first()
            if conversation:
                conversation.total_mesaje += 1
            
            db.commit()
            db.refresh(message)
            return message
            
        except Exception as e:
            db.rollback()
            raise e
        finally:
            db.close()
    
    @staticmethod
    def complete_conversation(
        conversation_id: str,
        total_idei_unice: int
    ):
        """Marchează conversația ca fiind finalizată"""
        db = get_db_session()
        try:
            conversation = db.query(DBConversation).filter(
                DBConversation.id == conversation_id
            ).first()
            
            if conversation:
                conversation.end_time = datetime.utcnow()
                conversation.is_completed = True
                conversation.total_idei_unice = total_idei_unice
                db.commit()
                
        except Exception as e:
            db.rollback()
            raise e
        finally:
            db.close()
    
    @staticmethod
    def get_conversation(conversation_id: str) -> Optional[DBConversation]:
        """Obține o conversație după ID"""
        db = get_db_session()
        try:
            return db.query(DBConversation).filter(
                DBConversation.id == conversation_id
            ).first()
        finally:
            db.close()
    
    @staticmethod
    def get_all_conversations() -> List[DBConversation]:
        """Obține toate conversațiile salvate"""
        db = get_db_session()
        try:
            return db.query(DBConversation).order_by(
                DBConversation.start_time.desc()
            ).all()
        finally:
            db.close()
    
    @staticmethod
    def delete_conversation(conversation_id: str) -> bool:
        """Șterge o conversație din baza de date"""
        db = get_db_session()
        try:
            conversation = db.query(DBConversation).filter(
                DBConversation.id == conversation_id
            ).first()
            
            if conversation:
                db.delete(conversation)
                db.commit()
                return True
            return False
            
        except Exception as e:
            db.rollback()
            raise e
        finally:
            db.close()
    
    @staticmethod
    def get_conversation_with_messages(conversation_id: str) -> Optional[DBConversation]:
        """Obține o conversație cu toate mesajele"""
        db = get_db_session()
        try:
            return db.query(DBConversation).filter(
                DBConversation.id == conversation_id
            ).first()
        finally:
            db.close()


# Initialize database on import
def init_database():
    """Inițializează baza de date"""
    try:
        create_tables()
        print("Database initialized successfully")
    except Exception as e:
        print(f"Database initialization error: {e}")


# Auto-initialize when imported
init_database()