"""
Tests for SQLite database initialization and models.
"""

import os
from sqlalchemy import create_engine, inspect
from sqlalchemy.orm import sessionmaker

from backend.db.database import Base
from backend.db.models import User, Workflow, Task, Attempt, Event


def test_tables_are_created():
    """Verify all expected tables exist after init."""
    engine = create_engine(
        "sqlite:///data/test_autoforge.db",
        connect_args={"check_same_thread": False},
    )
    Base.metadata.create_all(bind=engine)

    inspector = inspect(engine)
    tables = inspector.get_table_names()

    assert "users" in tables
    assert "workflows" in tables
    assert "tasks" in tables
    assert "attempts" in tables
    assert "events" in tables

    Base.metadata.drop_all(bind=engine)


def test_user_model_creation():
    """Create a user and verify fields."""
    engine = create_engine(
        "sqlite:///data/test_autoforge.db",
        connect_args={"check_same_thread": False},
    )
    Base.metadata.create_all(bind=engine)
    Session = sessionmaker(bind=engine)
    session = Session()

    user = User(username="dbuser", email="db@test.com", password_hash="fakehash")
    session.add(user)
    session.commit()
    session.refresh(user)

    assert user.id is not None
    assert user.username == "dbuser"
    assert user.created_at is not None

    session.close()
    Base.metadata.drop_all(bind=engine)


def test_workflow_task_relationship():
    """Verify workflow → task → attempt relationships."""
    engine = create_engine(
        "sqlite:///data/test_autoforge.db",
        connect_args={"check_same_thread": False},
    )
    Base.metadata.create_all(bind=engine)
    Session = sessionmaker(bind=engine)
    session = Session()

    user = User(username="reluser", email="rel@test.com", password_hash="hash")
    session.add(user)
    session.commit()

    wf = Workflow(user_id=user.id, prompt="test prompt")
    session.add(wf)
    session.commit()

    task = Task(workflow_id=wf.id, name="code_gen", type="code")
    session.add(task)
    session.commit()

    attempt = Attempt(task_id=task.id, attempt_number=1, status="pending")
    session.add(attempt)
    session.commit()

    event = Event(workflow_id=wf.id, event_type="test", payload={"key": "value"})
    session.add(event)
    session.commit()

    # Verify relationships
    session.refresh(wf)
    assert len(wf.tasks) == 1
    assert len(wf.events) == 1
    assert wf.tasks[0].name == "code_gen"
    assert wf.tasks[0].attempts[0].attempt_number == 1

    session.close()
    Base.metadata.drop_all(bind=engine)