"""Service-level DB integration tests.

Covers SyncTaskService, SyncLogService, ServiceAccountService, and the
ISRC cache service against a real SQLite in-memory database.
No HTTP calls, no Celery, no platform APIs.
"""

from datetime import datetime, timedelta, timezone

import pytest_asyncio
from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.pool import StaticPool
from sqlmodel import SQLModel

from playlist_sync.models.service_account import ServiceAccount
from playlist_sync.models.sync_log import PlaylistSynchronizationLog
from playlist_sync.models.sync_task import PlaylistSynchronization
from playlist_sync.models.sync_track import SynchronizationTrack
from playlist_sync.services import isrc_cache_service
from playlist_sync.services.sync_service import (
    ServiceAccountService,
    SyncLogService,
    SyncTaskService,
)

TEST_DATABASE_URL = "sqlite+aiosqlite:///:memory:"


# ---------------------------------------------------------------------------
# Shared DB fixtures
# ---------------------------------------------------------------------------


@pytest_asyncio.fixture(scope="function")
async def db_engine():
    engine = create_async_engine(
        TEST_DATABASE_URL,
        connect_args={"check_same_thread": False},
        poolclass=StaticPool,
    )
    async with engine.begin() as conn:
        await conn.run_sync(SQLModel.metadata.create_all)
    yield engine
    async with engine.begin() as conn:
        await conn.run_sync(SQLModel.metadata.drop_all)
    await engine.dispose()


@pytest_asyncio.fixture(scope="function")
async def session(db_engine):
    TestSession = sessionmaker(db_engine, class_=AsyncSession, expire_on_commit=False)
    async with TestSession() as s:
        yield s


def _sync(
    to_playlist_id="t-1", application_id=1, active=True, from_playlist_id="f-1"
) -> PlaylistSynchronization:
    return PlaylistSynchronization(
        application_id=application_id,
        from_playlist_id=from_playlist_id,
        from_service_type=0,
        to_playlist_id=to_playlist_id,
        to_service_account_id=99,
        title="Test",
        active=active,
    )


# ===========================================================================
# SyncTaskService
# ===========================================================================


class TestSyncTaskService:
    async def test_get_all_active_returns_active_only(self, session):
        session.add(_sync("t-active", active=True))
        session.add(_sync("t-inactive", active=False))
        await session.commit()

        svc = SyncTaskService(session)
        results = await svc.get_all_active()
        assert len(results) == 1
        assert results[0].to_playlist_id == "t-active"

    async def test_get_by_application_id_filters_correctly(self, session):
        session.add(_sync("app1-sync", application_id=1))
        session.add(_sync("app2-sync", application_id=2))
        await session.commit()

        svc = SyncTaskService(session)
        results = await svc.get_by_application_id(1)
        assert all(r.application_id == 1 for r in results)
        assert len(results) == 1

    async def test_get_by_id_found(self, session):
        row = _sync("find-me")
        session.add(row)
        await session.commit()
        await session.refresh(row)

        svc = SyncTaskService(session)
        result = await svc.get_by_id(row.id)
        assert result is not None
        assert result.to_playlist_id == "find-me"

    async def test_get_by_id_not_found(self, session):
        svc = SyncTaskService(session)
        result = await svc.get_by_id(9999)
        assert result is None

    async def test_get_by_to_playlist_id(self, session):
        session.add(_sync("needle"))
        await session.commit()

        svc = SyncTaskService(session)
        result = await svc.get_by_to_playlist_id("needle")
        assert result is not None
        assert result.to_playlist_id == "needle"

    async def test_get_by_to_playlist_id_not_found(self, session):
        svc = SyncTaskService(session)
        result = await svc.get_by_to_playlist_id("missing")
        assert result is None

    async def test_create_sync(self, session):
        svc = SyncTaskService(session)
        new = _sync("created")
        result = await svc.create_sync(new)
        assert result.id is not None
        assert result.to_playlist_id == "created"

    async def test_update_sync_updatable_fields(self, session):
        row = _sync("upd")
        session.add(row)
        await session.commit()
        await session.refresh(row)

        svc = SyncTaskService(session)
        updated = await svc.update_sync(row.id, {"title": "New Title", "active": False})
        assert updated is not None
        assert updated.title == "New Title"
        assert updated.active is False

    async def test_update_sync_ignores_non_updatable_fields(self, session):
        """application_id and id must not be overwritten via update_sync."""
        row = _sync("guard")
        session.add(row)
        await session.commit()
        await session.refresh(row)
        original_app_id = row.application_id
        original_id = row.id

        svc = SyncTaskService(session)
        await svc.update_sync(row.id, {"application_id": 999, "id": 999})

        fetched = await svc.get_by_id(original_id)
        assert fetched.application_id == original_app_id
        assert fetched.id == original_id

    async def test_update_sync_not_found_returns_none(self, session):
        svc = SyncTaskService(session)
        result = await svc.update_sync(9999, {"title": "x"})
        assert result is None

    async def test_update_sync_result_writes_only_result_fields(self, session):
        """update_sync_result should touch source/count fields, not title/active."""
        row = _sync("result-check")
        row.title = "Original Title"
        row.active = True
        session.add(row)
        await session.commit()
        await session.refresh(row)
        sync_id = row.id

        # Detach from session so mutations below don't get auto-flushed
        session.expunge(row)
        row.source_title = "Spotify Playlist"
        row.source_track_count = 42
        row.synchronized_track_count = 40
        row.error = False
        row.last_updated = datetime.now(timezone.utc)
        # Intentionally set a field that should NOT be persisted by update_sync_result
        row.title = "SHOULD NOT CHANGE"

        svc = SyncTaskService(session)
        await svc.update_sync_result(row)

        # Re-fetch from DB
        fresh = await svc.get_by_id(sync_id)
        assert fresh.source_title == "Spotify Playlist"
        assert fresh.source_track_count == 42
        assert fresh.synchronized_track_count == 40
        assert fresh.title == "Original Title"  # unchanged

    async def test_delete_sync_returns_true(self, session):
        row = _sync("del-me")
        session.add(row)
        await session.commit()
        await session.refresh(row)

        svc = SyncTaskService(session)
        deleted = await svc.delete_sync(row.id)
        assert deleted is True

        result = await svc.get_by_id(row.id)
        assert result is None

    async def test_delete_sync_unknown_id_returns_false(self, session):
        svc = SyncTaskService(session)
        result = await svc.delete_sync(9999)
        assert result is False


# ===========================================================================
# SyncLogService
# ===========================================================================


class TestSyncLogService:
    async def test_add_log_persists(self, session, db_engine):
        # Need a sync row for the FK
        sync = _sync("log-owner")
        session.add(sync)
        await session.commit()
        await session.refresh(sync)

        svc = SyncLogService(session)
        log = PlaylistSynchronizationLog(
            sync_id=sync.id,
            added_tracks=5,
            deleted_tracks=2,
            made_changes=True,
        )
        result = await svc.add_log(log)
        assert result.sync_id is not None
        assert result.added_tracks == 5

    async def test_get_logs_ordered_newest_first(self, session):
        sync = _sync("log-order")
        session.add(sync)
        await session.commit()
        await session.refresh(sync)

        now = datetime.now(timezone.utc)
        for delta in [timedelta(hours=1), timedelta(hours=2), timedelta(hours=3)]:
            session.add(PlaylistSynchronizationLog(sync_id=sync.id, time=now - delta))
        await session.commit()

        svc = SyncLogService(session)
        logs = await svc.get_logs_by_sync_id(sync.id)
        times = [log.time for log in logs]
        assert times == sorted(times, reverse=True)

    async def test_get_logs_limit_and_offset(self, session):
        sync = _sync("log-page")
        session.add(sync)
        await session.commit()
        await session.refresh(sync)

        now = datetime.now(timezone.utc)
        for i in range(6):
            session.add(
                PlaylistSynchronizationLog(
                    sync_id=sync.id, time=now - timedelta(seconds=i)
                )
            )
        await session.commit()

        svc = SyncLogService(session)
        page1 = await svc.get_logs_by_sync_id(sync.id, limit=3, offset=0)
        page2 = await svc.get_logs_by_sync_id(sync.id, limit=3, offset=3)
        assert len(page1) == 3
        assert len(page2) == 3
        # No overlap
        times1 = {l.time for l in page1}
        times2 = {l.time for l in page2}
        assert times1.isdisjoint(times2)

    async def test_get_logs_empty_for_unknown_sync(self, session):
        svc = SyncLogService(session)
        logs = await svc.get_logs_by_sync_id(9999)
        assert logs == []


# ===========================================================================
# ServiceAccountService
# ===========================================================================


class TestServiceAccountService:
    def _account(
        self,
        application_id=1,
        service_type=0,
        music_service_id=1,
        user_identifier="user",
    ) -> ServiceAccount:
        return ServiceAccount(
            application_id=application_id,
            service_type=service_type,
            music_service_id=music_service_id,
            user_identifier=user_identifier,
        )

    async def test_get_by_id_found(self, session):
        acc = self._account()
        session.add(acc)
        await session.commit()
        await session.refresh(acc)

        svc = ServiceAccountService(session)
        result = await svc.get_by_id(acc.id)
        assert result is not None
        assert result.id == acc.id

    async def test_get_by_id_not_found(self, session):
        svc = ServiceAccountService(session)
        result = await svc.get_by_id(9999)
        assert result is None

    async def test_get_by_application_id_filters(self, session):
        session.add(self._account(application_id=1, user_identifier="a"))
        session.add(self._account(application_id=2, user_identifier="b"))
        session.add(self._account(application_id=1, user_identifier="c"))
        await session.commit()

        svc = ServiceAccountService(session)
        results = await svc.get_by_application_id(1)
        assert len(results) == 2
        assert all(r.application_id == 1 for r in results)

    async def test_get_all(self, session):
        session.add(self._account(application_id=1, user_identifier="x"))
        session.add(self._account(application_id=2, user_identifier="y"))
        await session.commit()

        svc = ServiceAccountService(session)
        results = await svc.get_all()
        assert len(results) == 2


# ===========================================================================
# ISRC cache service
# ===========================================================================


class TestIsrcCacheService:
    async def test_get_synchronized_tracks_empty(self, session):
        result = await isrc_cache_service.get_synchronized_tracks(
            ["US-X1Y-23-45678"], 1, session
        )
        assert result == []

    async def test_get_synchronized_tracks_empty_input(self, session):
        result = await isrc_cache_service.get_synchronized_tracks([], 1, session)
        assert result == []

    async def test_save_and_get_track(self, session):
        await isrc_cache_service.save_synchronized_track(
            "ISO-001", 1, "track-abc", session
        )
        results = await isrc_cache_service.get_synchronized_tracks(
            ["ISO-001"], 1, session
        )
        assert len(results) == 1
        assert results[0].isrc == "ISO-001"
        assert results[0].track_id == "track-abc"

    async def test_save_updates_existing_entry(self, session):
        """Second save should update track_id and MatchDate, not create a duplicate."""
        await isrc_cache_service.save_synchronized_track(
            "ISO-002", 1, "old-id", session
        )
        await isrc_cache_service.save_synchronized_track(
            "ISO-002", 1, "new-id", session
        )

        results = await isrc_cache_service.get_synchronized_tracks(
            ["ISO-002"], 1, session
        )
        assert len(results) == 1
        assert results[0].track_id == "new-id"

    async def test_save_deduplicates_multiple_existing(self, session):
        """If duplicate rows exist in DB (edge case), save should collapse to one."""
        session.add(
            SynchronizationTrack(
                isrc="ISO-003",
                service_type=1,
                track_id="old1",
                match_date=datetime.now(timezone.utc) - timedelta(days=2),
            )
        )
        session.add(
            SynchronizationTrack(
                isrc="ISO-003",
                service_type=1,
                track_id="old2",
                match_date=datetime.now(timezone.utc) - timedelta(days=1),
            )
        )
        await session.commit()

        await isrc_cache_service.save_synchronized_track(
            "ISO-003", 1, "updated", session
        )
        results = await isrc_cache_service.get_synchronized_tracks(
            ["ISO-003"], 1, session
        )
        assert len(results) == 1
        assert results[0].track_id == "updated"

    async def test_service_type_isolation(self, session):
        """Same ISRC for different service types should be stored independently."""
        await isrc_cache_service.save_synchronized_track(
            "ISO-004", 1, "spotify-id", session
        )
        await isrc_cache_service.save_synchronized_track(
            "ISO-004", 2, "deezer-id", session
        )

        spotify_results = await isrc_cache_service.get_synchronized_tracks(
            ["ISO-004"], 1, session
        )
        deezer_results = await isrc_cache_service.get_synchronized_tracks(
            ["ISO-004"], 2, session
        )
        assert spotify_results[0].track_id == "spotify-id"
        assert deezer_results[0].track_id == "deezer-id"

    async def test_batch_query_multiple_isrcs(self, session):
        for i in range(5):
            await isrc_cache_service.save_synchronized_track(
                f"ISO-{i:03}", 1, f"track-{i}", session
            )

        results = await isrc_cache_service.get_synchronized_tracks(
            ["ISO-000", "ISO-002", "ISO-004"], 1, session
        )
        assert len(results) == 3
        found_isrcs = {r.isrc for r in results}
        assert found_isrcs == {"ISO-000", "ISO-002", "ISO-004"}