"""Database models and operations for instructor system."""
import json
from datetime import datetime
from typing import Any

from sqlalchemy import (
    Column,
    DateTime,
    Float,
    Integer,
    String,
    Text,
    create_engine,
)
from sqlalchemy.orm import declarative_base, sessionmaker

from shared.config import settings
from shared.logger import setup_logger

logger = setup_logger(__name__)

Base = declarative_base()


class Task(Base):
    """Task records sent to students."""

    __tablename__ = "tasks"

    id = Column(Integer, primary_key=True)
    timestamp = Column(DateTime, default=datetime.utcnow, nullable=False)
    email = Column(String(255), nullable=False, index=True)
    task = Column(String(255), nullable=False, index=True)
    round = Column(Integer, nullable=False)
    nonce = Column(String(255), nullable=False, unique=True)
    brief = Column(Text, nullable=False)
    attachments = Column(Text, nullable=False)  # JSON serialized
    checks = Column(Text, nullable=False)  # JSON serialized
    evaluation_url = Column(String(512), nullable=False)
    endpoint = Column(String(512), nullable=False)
    statuscode = Column(Integer, nullable=True)
    secret = Column(String(255), nullable=False)

    def to_dict(self) -> dict[str, Any]:
        """Convert to dictionary."""
        return {
            "id": self.id,
            "timestamp": self.timestamp.isoformat() if self.timestamp else None,
            "email": self.email,
            "task": self.task,
            "round": self.round,
            "nonce": self.nonce,
            "brief": self.brief,
            "attachments": json.loads(self.attachments) if self.attachments else [],
            "checks": json.loads(self.checks) if self.checks else [],
            "evaluation_url": self.evaluation_url,
            "endpoint": self.endpoint,
            "statuscode": self.statuscode,
        }


class Repo(Base):
    """Repository submissions from students."""

    __tablename__ = "repos"

    id = Column(Integer, primary_key=True)
    timestamp = Column(DateTime, default=datetime.utcnow, nullable=False)
    email = Column(String(255), nullable=False, index=True)
    task = Column(String(255), nullable=False, index=True)
    round = Column(Integer, nullable=False)
    nonce = Column(String(255), nullable=False, unique=True)
    repo_url = Column(String(512), nullable=False)
    commit_sha = Column(String(255), nullable=False)
    pages_url = Column(String(512), nullable=False)

    def to_dict(self) -> dict[str, Any]:
        """Convert to dictionary."""
        return {
            "id": self.id,
            "timestamp": self.timestamp.isoformat() if self.timestamp else None,
            "email": self.email,
            "task": self.task,
            "round": self.round,
            "nonce": self.nonce,
            "repo_url": self.repo_url,
            "commit_sha": self.commit_sha,
            "pages_url": self.pages_url,
        }


class Result(Base):
    """Evaluation results."""

    __tablename__ = "results"

    id = Column(Integer, primary_key=True)
    timestamp = Column(DateTime, default=datetime.utcnow, nullable=False)
    email = Column(String(255), nullable=False, index=True)
    task = Column(String(255), nullable=False, index=True)
    round = Column(Integer, nullable=False)
    repo_url = Column(String(512), nullable=False)
    commit_sha = Column(String(255), nullable=False)
    pages_url = Column(String(512), nullable=False)
    check = Column(String(512), nullable=False)
    score = Column(Float, nullable=False)
    reason = Column(Text, nullable=False)
    logs = Column(Text, nullable=True)

    def to_dict(self) -> dict[str, Any]:
        """Convert to dictionary."""
        return {
            "id": self.id,
            "timestamp": self.timestamp.isoformat() if self.timestamp else None,
            "email": self.email,
            "task": self.task,
            "round": self.round,
            "repo_url": self.repo_url,
            "commit_sha": self.commit_sha,
            "pages_url": self.pages_url,
            "check": self.check,
            "score": self.score,
            "reason": self.reason,
            "logs": self.logs,
        }


class Database:
    """Database manager for instructor system."""

    def __init__(self, database_url: str | None = None) -> None:
        """Initialize database connection.

        Args:
            database_url: Database URL (uses settings if not provided)
        """
        self.database_url = database_url or settings.database_url
        self.engine = create_engine(self.database_url, echo=False)
        self.SessionLocal = sessionmaker(bind=self.engine)
        logger.info(f"Initialized database: {self.database_url}")

    def create_tables(self) -> None:
        """Create all tables."""
        Base.metadata.create_all(self.engine)
        logger.info("Created database tables")

    def drop_tables(self) -> None:
        """Drop all tables (use with caution)."""
        Base.metadata.drop_all(self.engine)
        logger.warning("Dropped all database tables")

    def get_session(self):
        """Get database session."""
        return self.SessionLocal()

    # Task operations
    def add_task(self, task_data: dict[str, Any]) -> Task:
        """Add a task record.

        Args:
            task_data: Task data dictionary

        Returns:
            Created task record
        """
        session = self.get_session()
        try:
            task = Task(
                email=task_data["email"],
                task=task_data["task"],
                round=task_data["round"],
                nonce=task_data["nonce"],
                brief=task_data["brief"],
                attachments=json.dumps(task_data.get("attachments", [])),
                checks=json.dumps(task_data.get("checks", [])),
                evaluation_url=task_data["evaluation_url"],
                endpoint=task_data["endpoint"],
                statuscode=task_data.get("statuscode"),
                secret=task_data["secret"],
            )
            session.add(task)
            session.commit()
            session.refresh(task)
            logger.info(f"Added task: {task.task}, round {task.round}")
            return task
        finally:
            session.close()

    def get_task_by_nonce(self, nonce: str) -> Task | None:
        """Get task by nonce.

        Args:
            nonce: Task nonce

        Returns:
            Task or None
        """
        session = self.get_session()
        try:
            return session.query(Task).filter(Task.nonce == nonce).first()
        finally:
            session.close()

    def task_exists(self, email: str, task: str, round: int) -> bool:
        """Check if task exists.

        Args:
            email: Student email
            task: Task ID
            round: Round number

        Returns:
            True if exists
        """
        session = self.get_session()
        try:
            return (
                session.query(Task)
                .filter(Task.email == email, Task.task == task, Task.round == round)
                .first()
                is not None
            )
        finally:
            session.close()

    # Repo operations
    def add_repo(self, repo_data: dict[str, Any]) -> Repo:
        """Add a repo submission.

        Args:
            repo_data: Repo data dictionary

        Returns:
            Created repo record
        """
        session = self.get_session()
        try:
            repo = Repo(**repo_data)
            session.add(repo)
            session.commit()
            session.refresh(repo)
            logger.info(f"Added repo: {repo.task}, round {repo.round}")
            return repo
        finally:
            session.close()

    def get_repos(self, email: str | None = None) -> list[Repo]:
        """Get repo submissions.

        Args:
            email: Filter by email (optional)

        Returns:
            List of repos
        """
        session = self.get_session()
        try:
            query = session.query(Repo)
            if email:
                query = query.filter(Repo.email == email)
            return query.all()
        finally:
            session.close()

    # Result operations
    def add_result(self, result_data: dict[str, Any]) -> Result:
        """Add an evaluation result.

        Args:
            result_data: Result data dictionary

        Returns:
            Created result record
        """
        session = self.get_session()
        try:
            result = Result(**result_data)
            session.add(result)
            session.commit()
            session.refresh(result)
            logger.debug(f"Added result: {result.task}, {result.check}")
            return result
        finally:
            session.close()

    def get_results(
        self, email: str | None = None, task: str | None = None
    ) -> list[Result]:
        """Get evaluation results.

        Args:
            email: Filter by email (optional)
            task: Filter by task (optional)

        Returns:
            List of results
        """
        session = self.get_session()
        try:
            query = session.query(Result)
            if email:
                query = query.filter(Result.email == email)
            if task:
                query = query.filter(Result.task == task)
            return query.all()
        finally:
            session.close()