"""Tests for new database methods: user profile and scheduled medications."""

import pytest

from reachy_mini_conversation_app.database import MiniMinderDB


@pytest.fixture
def db() -> MiniMinderDB:
    """Create an in-memory database for testing."""
    return MiniMinderDB(":memory:")


# -----------------------------------------------------------------------------
# User Profile Tests
# -----------------------------------------------------------------------------


def test_get_or_create_profile(db: MiniMinderDB) -> None:
    """Profile should be created on first access."""
    profile = db.get_or_create_profile()
    assert profile is not None
    assert profile["id"] == 1
    assert profile["onboarding_completed"] == 0
    assert profile["onboarding_step"] == "name"


def test_get_or_create_profile_idempotent(db: MiniMinderDB) -> None:
    """Getting profile multiple times should return same data."""
    profile1 = db.get_or_create_profile()
    profile2 = db.get_or_create_profile()
    assert profile1["id"] == profile2["id"]
    assert profile1["created_at"] == profile2["created_at"]


def test_update_profile(db: MiniMinderDB) -> None:
    """Profile updates should persist."""
    db.update_profile(
        {
            "display_name": "Alice",
            "onboarding_completed": 1,
            "onboarding_step": "done",
        }
    )
    profile = db.get_or_create_profile()
    assert profile["display_name"] == "Alice"
    assert profile["onboarding_completed"] == 1
    assert profile["onboarding_step"] == "done"


def test_is_onboarding_complete_default(db: MiniMinderDB) -> None:
    """Onboarding should not be complete by default."""
    assert db.is_onboarding_complete() is False


def test_is_onboarding_complete_after_update(db: MiniMinderDB) -> None:
    """Onboarding should be complete after marking it."""
    db.update_profile({"onboarding_completed": 1})
    assert db.is_onboarding_complete() is True


# -----------------------------------------------------------------------------
# Scheduled Medications Tests
# -----------------------------------------------------------------------------


def test_add_scheduled_medication(db: MiniMinderDB) -> None:
    """Add and retrieve a scheduled medication."""
    med_id = db.add_scheduled_medication(
        {
            "medication_name": "Ibuprofen",
            "dose": "400mg",
            "frequency": "twice daily",
            "times_of_day": ["morning", "evening"],
        }
    )
    assert med_id == 1

    meds = db.get_scheduled_medications()
    assert len(meds) == 1
    assert meds[0]["medication_name"] == "Ibuprofen"
    assert meds[0]["dose"] == "400mg"
    assert meds[0]["times_of_day"] == ["morning", "evening"]


def test_add_multiple_medications(db: MiniMinderDB) -> None:
    """Add multiple medications."""
    db.add_scheduled_medication({"medication_name": "Med A"})
    db.add_scheduled_medication({"medication_name": "Med B"})
    db.add_scheduled_medication({"medication_name": "Med C"})

    meds = db.get_scheduled_medications()
    assert len(meds) == 3
    names = [m["medication_name"] for m in meds]
    assert "Med A" in names
    assert "Med B" in names
    assert "Med C" in names


def test_scheduled_medication_with_reminders(db: MiniMinderDB) -> None:
    """Medications can have reminder times."""
    db.add_scheduled_medication(
        {
            "medication_name": "Blood Pressure Med",
            "reminder_enabled": 1,
            "reminder_times": ["07:55", "19:55"],
        }
    )

    meds = db.get_scheduled_medications()
    assert len(meds) == 1
    assert meds[0]["reminder_enabled"] == 1
    assert meds[0]["reminder_times"] == ["07:55", "19:55"]


def test_update_scheduled_medication(db: MiniMinderDB) -> None:
    """Update an existing medication."""
    med_id = db.add_scheduled_medication(
        {
            "medication_name": "Aspirin",
            "dose": "100mg",
        }
    )

    success = db.update_scheduled_medication(
        med_id,
        {
            "dose": "200mg",
            "frequency": "once daily",
        },
    )
    assert success is True

    meds = db.get_scheduled_medications()
    assert meds[0]["dose"] == "200mg"
    assert meds[0]["frequency"] == "once daily"


def test_deactivate_scheduled_medication(db: MiniMinderDB) -> None:
    """Deactivated medications should not appear in active list."""
    med_id = db.add_scheduled_medication({"medication_name": "Old Med"})

    success = db.deactivate_scheduled_medication(med_id)
    assert success is True

    # Should not appear in active list
    active_meds = db.get_scheduled_medications(active_only=True)
    assert len(active_meds) == 0

    # Should appear in full list
    all_meds = db.get_scheduled_medications(active_only=False)
    assert len(all_meds) == 1
    assert all_meds[0]["active"] == 0


def test_update_nonexistent_medication(db: MiniMinderDB) -> None:
    """Updating a nonexistent medication should return False."""
    success = db.update_scheduled_medication(999, {"dose": "100mg"})
    # Note: SQLite UPDATE returns 0 rows affected, so this returns False
    assert success is False


# -----------------------------------------------------------------------------
# Upsert Scheduled Medication Tests
# -----------------------------------------------------------------------------


def test_upsert_scheduled_medication_new(db: MiniMinderDB) -> None:
    """Upsert should create a new row when no match exists."""
    med_id, is_new = db.upsert_scheduled_medication(
        {"medication_name": "Aspirin", "dose": "100mg"}
    )
    assert is_new is True
    assert med_id >= 1

    meds = db.get_scheduled_medications()
    assert len(meds) == 1
    assert meds[0]["medication_name"] == "Aspirin"


def test_upsert_scheduled_medication_existing(db: MiniMinderDB) -> None:
    """Upsert should update existing row and not create a duplicate."""
    # First call: creates the row
    med_id_1, is_new_1 = db.upsert_scheduled_medication(
        {"medication_name": "Amitriptyline"}
    )
    assert is_new_1 is True

    # Second call: same name, adds dose — should update
    med_id_2, is_new_2 = db.upsert_scheduled_medication(
        {"medication_name": "Amitriptyline", "dose": "10mg"}
    )
    assert is_new_2 is False
    assert med_id_2 == med_id_1

    # Third call: same name, adds frequency — should update again
    med_id_3, is_new_3 = db.upsert_scheduled_medication(
        {"medication_name": "Amitriptyline", "dose": "10mg", "frequency": "once daily"}
    )
    assert is_new_3 is False
    assert med_id_3 == med_id_1

    # Only one row should exist with all the merged data
    meds = db.get_scheduled_medications()
    assert len(meds) == 1
    assert meds[0]["dose"] == "10mg"
    assert meds[0]["frequency"] == "once daily"


def test_upsert_case_insensitive(db: MiniMinderDB) -> None:
    """Upsert should match medication names case-insensitively."""
    db.upsert_scheduled_medication({"medication_name": "Aspirin"})
    med_id, is_new = db.upsert_scheduled_medication(
        {"medication_name": "aspirin", "dose": "200mg"}
    )
    assert is_new is False

    meds = db.get_scheduled_medications()
    assert len(meds) == 1


def test_schema_includes_new_tables(db: MiniMinderDB) -> None:
    """The new tables should exist."""
    conn = db._get_conn()
    tables = conn.execute(
        "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"
    ).fetchall()
    table_names = [t["name"] for t in tables]
    assert "user_profile" in table_names
    assert "scheduled_medications" in table_names