from unittest.mock import MagicMock, call
from etl_to_crawler.writer import (
    fetch_pending_yinyan_song_records,
    fetch_yinyan_records_missing_platform,
    insert_yinyan_song_records,
    update_yinyan_record_platforms,
    upsert_kugou_albums,
    upsert_kugou_singers,
    upsert_kugou_songs,
    upsert_netease_albums,
    upsert_netease_singers,
    upsert_netease_songs,
    upsert_qq_albums,
    upsert_qq_singers,
    upsert_qq_songs,
    upsert_qq_singer_songs,
    upsert_yinyan_song_records,
)


def test_upsert_qq_singers_executes_insert():
    cur = MagicMock()
    singers = [{
        'id': 1, 'mid': 'abc', 'name': '歌手', 'avatar': 'https://archive-dev.oss-cn-beijing.aliyuncs.com/a.jpg',
        'sex': 'M', 'area': '华语', 'index': 'G', 'intro': None, 'home_url': None
    }]
    upsert_qq_singers(cur, singers)
    assert cur.executemany.called
    args = cur.executemany.call_args
    assert 'crawler_qqmusic_singers' in args[0][0]
    assert 'ON CONFLICT' in args[0][0]


def test_upsert_qq_singers_empty_does_nothing():
    cur = MagicMock()
    upsert_qq_singers(cur, [])
    cur.executemany.assert_not_called()


def test_upsert_qq_singer_songs():
    cur = MagicMock()
    upsert_qq_singer_songs(cur, [(1, 'uuid-abc'), (2, 'uuid-def')])
    assert cur.executemany.called
    sql = cur.executemany.call_args[0][0]
    assert 'crawler_qqmusic_singer_songs' in sql


def test_upsert_yinyan_song_records_marks_existing_relation_as_pushed():
    cur = MagicMock()
    upsert_yinyan_song_records(cur, [
        {'song_id': 10, 'record_id': 100, 'platform': '1', 'platform_song_id': 1000},
        {'song_id': 11, 'record_id': 101, 'platform': '2', 'platform_song_id': 1001},
    ])

    sql, params = cur.execute.call_args[0]
    assert 'UPDATE yinyan_song_records' in sql
    assert 'UPDATE yinyan_song_records AS ysr' in sql
    assert 'SET platform = v.platform' in sql
    assert 'platform_song_id = v.platform_song_id' in sql
    assert 'record_id = v.record_id' in sql
    assert 'is_yinyan_push = TRUE' in sql
    assert 'FROM (VALUES (%s::bigint, %s::bigint, %s::varchar, %s::bigint), (%s::bigint, %s::bigint, %s::varchar, %s::bigint))' in sql
    assert 'ysr.song_id = v.song_id' in sql
    assert 'ysr.is_yinyan_push = FALSE' in sql
    assert params == (10, 100, '1', 1000, 11, 101, '2', 1001)


def test_insert_yinyan_song_records_initializes_unpushed_rows():
    cur = MagicMock()
    insert_yinyan_song_records(cur, [
        {'song_id': 10, 'record_id': 100, 'platform': '1'},
        {'song_id': 11, 'record_id': 101, 'platform': '2'},
    ])

    sql, rows = cur.executemany.call_args[0]
    assert 'INSERT INTO yinyan_song_records' in sql
    assert 'platform' in sql
    assert 'is_yinyan_push' in sql
    assert 'FALSE' in sql
    assert 'ON CONFLICT (song_id, record_id) DO UPDATE' in sql
    assert 'WHERE yinyan_song_records.platform IS NULL' in sql
    assert rows == [(10, 100, '1'), (11, 101, '2')]


def test_fetch_yinyan_records_missing_platform_reads_null_platform_rows():
    cur = MagicMock()
    cur.fetchall.return_value = [(10, 100), (11, 101)]

    rows = fetch_yinyan_records_missing_platform(cur, 500)

    sql, params = cur.execute.call_args[0]
    assert 'WHERE platform IS NULL' in sql
    assert params == (500,)
    assert rows == [{'song_id': 10, 'record_id': 100}, {'song_id': 11, 'record_id': 101}]


def test_fetch_pending_yinyan_song_records_reads_platform_for_single_platform_import():
    cur = MagicMock()
    cur.fetchall.return_value = [(10, 100, '1'), (11, 101, '2')]

    rows = fetch_pending_yinyan_song_records(cur, 500)

    sql, params = cur.execute.call_args[0]
    assert 'SELECT song_id, record_id, platform' in sql
    assert 'platform IS NOT NULL' in sql
    assert params == (500,)
    assert rows == [
        {'song_id': 10, 'record_id': 100, 'platform': '1'},
        {'song_id': 11, 'record_id': 101, 'platform': '2'},
    ]


def test_update_yinyan_record_platforms_fills_only_null_platform_rows():
    cur = MagicMock()
    update_yinyan_record_platforms(cur, [
        {'song_id': 10, 'record_id': 100, 'platform': '1'},
        {'song_id': 11, 'record_id': 101, 'platform': '2'},
    ])

    sql, params = cur.execute.call_args[0]
    assert 'UPDATE yinyan_song_records AS ysr' in sql
    assert 'FROM (VALUES (%s::bigint, %s::bigint, %s::varchar), (%s::bigint, %s::bigint, %s::varchar))' in sql
    assert 'SET platform = v.platform' in sql
    assert 'AND ysr.platform IS NULL' in sql
    assert params == (10, 100, '1', 11, 101, '2')


def test_upsert_netease_songs_writes_album_json_column():
    cur = MagicMock()
    upsert_netease_songs(cur, [{
        'song_uuid': 'song-uuid',
        'platform_song_id': 300,
        'album_id': 20,
        'album_json': '{"id": 20, "title": "专辑"}',
        'cover': 'https://example.com/cover.jpg',
        'title': '标题',
        'name': '词曲名',
        'duration': 180,
        'lyric': '歌词',
        'composer_name': '曲作者',
        'lyricist_name': '词作者',
        'url': 'https://example.com/audio.mp3',
        'lyric_url': 'https://example.com/lyric.lrc',
        'platform_index_url': 'https://music.163.com/#/song?id=300',
        'published_at': '2020-01-01',
        'singers_json': '[]',
    }])

    sql, rows = cur.executemany.call_args[0]
    assert 'album, singers' in sql
    assert '%s::json, %s::jsonb' in sql
    assert rows[0][14] == '{"id": 20, "title": "专辑"}'


def test_upsert_qq_songs_writes_album_json_column():
    cur = MagicMock()
    upsert_qq_songs(cur, [{
        'song_uuid': 'song-uuid',
        'platform_song_id': 200,
        'mid': 'song-mid',
        'album_id': 20,
        'album_json': '{"id": 20, "title": "专辑"}',
        'cover': 'https://example.com/cover.jpg',
        'title': '标题',
        'name': '词曲名',
        'duration': 180,
        'lyric': '歌词',
        'composer_name': '曲作者',
        'lyricist_name': '词作者',
        'url': 'https://example.com/audio.mp3',
        'lyric_url': 'https://example.com/lyric.lrc',
        'platform_index_url': 'https://y.qq.com/n/ryqq/songDetail/song-mid',
        'published_at': '2020-01-01',
        'singers_json': '[]',
    }])

    sql, rows = cur.executemany.call_args[0]
    assert 'album, singers' in sql
    assert '%s::json, %s::jsonb' in sql
    assert rows[0][15] == '{"id": 20, "title": "专辑"}'


def test_upsert_kugou_songs_writes_provider_and_source_data():
    cur = MagicMock()
    upsert_kugou_songs(cur, [{
        'song_uuid': 'song-uuid',
        'platform_song_id': 200,
        'hash': 'hash',
        'album_audio_id': 300,
        'album_id': 20,
        'cover': 'https://example.com/cover.jpg',
        'title': '标题',
        'name': '词曲名',
        'duration': 180,
        'lyric': '歌词',
        'composer_name': '曲作者',
        'lyricist_name': '词作者',
        'url': 'https://example.com/audio.mp3',
        'lyric_url': 'https://example.com/lyric.lrc',
        'platform_index_url': 'https://www.kugou.com/song/#hash=hash',
        'published_at': '2020-01-01',
        'singers_json': '[]',
        'provider_name': 'yinyan',
        'crawler_source_data': '{"id": 200}',
        'audio_md5': 'md5-200',
        'version': 'DJ默涵版',
    }])

    sql, rows = cur.executemany.call_args[0]
    assert 'audio_md5' in sql
    assert 'version' in sql
    assert 'provider_name, crawler_source_data' in sql
    assert '%s::json' in sql
    assert rows[0][-4:] == ('DJ默涵版', 'md5-200', 'yinyan', '{"id": 200}')


def test_upsert_kugou_singers_writes_provider_and_source_data():
    cur = MagicMock()
    upsert_kugou_singers(cur, [{
        'id': 1,
        'name': '歌手',
        'avatar': 'https://example.com/avatar.jpg',
        'sex': 'M',
        'area': '华语',
        'index': 'G',
        'intro': '简介',
        'home_url': 'https://example.com/singer',
        'provider_name': 'yinyan',
        'crawler_source_data': '{"id": 1}',
    }])

    sql, rows = cur.executemany.call_args[0]
    assert 'provider_name, crawler_source_data' in sql
    assert rows[0][-2:] == ('yinyan', '{"id": 1}')


def test_upsert_kugou_albums_writes_provider_and_source_data():
    cur = MagicMock()
    upsert_kugou_albums(cur, [{
        'id': 20,
        'cover': 'https://example.com/album.jpg',
        'title': '专辑',
        'intro': '简介',
        'type': '专辑类型',
        'company_id': 7,
        'company': '唱片公司',
        'is_owner': 1,
        'published_at': '2020-01-01',
        'provider_name': 'yinyan',
        'crawler_source_data': '{"id": 20}',
    }])

    sql, rows = cur.executemany.call_args[0]
    assert 'provider_name, crawler_source_data' in sql
    assert rows[0][-2:] == ('yinyan', '{"id": 20}')


def test_upsert_qq_entities_write_provider_and_source_data():
    cur = MagicMock()
    upsert_qq_singers(cur, [{
        'id': 1,
        'mid': 'singer-mid',
        'name': '歌手',
        'avatar': 'https://example.com/avatar.jpg',
        'sex': 'M',
        'area': '华语',
        'index': 'G',
        'intro': '简介',
        'home_url': 'https://example.com/singer',
        'provider_name': 'yinyan',
        'crawler_source_data': '{"id": 1}',
    }])
    sql, rows = cur.executemany.call_args[0]
    assert 'provider_name, crawler_source_data' in sql
    assert rows[0][-2:] == ('yinyan', '{"id": 1}')

    cur = MagicMock()
    upsert_qq_albums(cur, [{
        'id': 20,
        'mid': 'album-mid',
        'cover': 'https://example.com/album.jpg',
        'title': '专辑',
        'intro': '简介',
        'type': '专辑类型',
        'company_id': 7,
        'company': '唱片公司',
        'is_owner': 1,
        'published_at': '2020-01-01',
        'provider_name': 'yinyan',
        'crawler_source_data': '{"id": 20}',
    }])
    sql, rows = cur.executemany.call_args[0]
    assert 'provider_name, crawler_source_data' in sql
    assert rows[0][-2:] == ('yinyan', '{"id": 20}')

    cur = MagicMock()
    upsert_qq_songs(cur, [{
        'song_uuid': 'song-uuid',
        'platform_song_id': 200,
        'mid': 'song-mid',
        'album_id': 20,
        'cover': 'https://example.com/cover.jpg',
        'title': '标题',
        'name': '词曲名',
        'duration': 180,
        'lyric': '歌词',
        'composer_name': '曲作者',
        'lyricist_name': '词作者',
        'url': 'https://example.com/audio.mp3',
        'lyric_url': 'https://example.com/lyric.lrc',
        'platform_index_url': 'https://y.qq.com/n/ryqq/songDetail/song-mid',
        'published_at': '2020-01-01',
        'singers_json': '[]',
        'provider_name': 'yinyan',
        'crawler_source_data': '{"id": 200}',
        'audio_md5': 'md5-200',
        'version': 'DJ默涵版',
    }])
    sql, rows = cur.executemany.call_args[0]
    assert 'audio_md5' in sql
    assert 'version' in sql
    assert 'provider_name, crawler_source_data' in sql
    assert rows[0][-4:] == ('DJ默涵版', 'md5-200', 'yinyan', '{"id": 200}')


def test_upsert_netease_entities_write_provider_and_source_data():
    cur = MagicMock()
    upsert_netease_singers(cur, [{
        'id': 1,
        'name': '歌手',
        'avatar': 'https://example.com/avatar.jpg',
        'sex': 'M',
        'area': '华语',
        'index': 'G',
        'intro': '简介',
        'home_url': 'https://example.com/singer',
        'provider_name': 'yinyan',
        'crawler_source_data': '{"id": 1}',
    }])
    sql, rows = cur.executemany.call_args[0]
    assert 'provider_name, crawler_source_data' in sql
    assert rows[0][-2:] == ('yinyan', '{"id": 1}')

    cur = MagicMock()
    upsert_netease_albums(cur, [{
        'id': 20,
        'cover': 'https://example.com/album.jpg',
        'title': '专辑',
        'intro': '简介',
        'type': '专辑类型',
        'company_id': 7,
        'company': '唱片公司',
        'is_owner': 1,
        'published_at': '2020-01-01',
        'provider_name': 'yinyan',
        'crawler_source_data': '{"id": 20}',
    }])
    sql, rows = cur.executemany.call_args[0]
    assert 'provider_name, crawler_source_data' in sql
    assert rows[0][-2:] == ('yinyan', '{"id": 20}')

    cur = MagicMock()
    upsert_netease_songs(cur, [{
        'song_uuid': 'song-uuid',
        'platform_song_id': 300,
        'album_id': 20,
        'album_json': '{"id": 20, "title": "专辑"}',
        'cover': 'https://example.com/cover.jpg',
        'title': '标题',
        'name': '词曲名',
        'duration': 180,
        'lyric': '歌词',
        'composer_name': '曲作者',
        'lyricist_name': '词作者',
        'url': 'https://example.com/audio.mp3',
        'lyric_url': 'https://example.com/lyric.lrc',
        'platform_index_url': 'https://music.163.com/#/song?id=300',
        'published_at': '2020-01-01',
        'singers_json': '[]',
        'provider_name': 'yinyan',
        'crawler_source_data': '{"id": 300}',
        'audio_md5': 'md5-300',
        'version': 'DJ默涵版',
    }])
    sql, rows = cur.executemany.call_args[0]
    assert 'audio_md5' in sql
    assert 'version' in sql
    assert 'provider_name, crawler_source_data' in sql
    assert rows[0][-4:] == ('DJ默涵版', 'md5-300', 'yinyan', '{"id": 300}')


def test_upsert_singers_normalizes_invalid_enum_values():
    cur = MagicMock()
    upsert_netease_singers(cur, [{
        'id': 1,
        'name': '歌手',
        'avatar': 'https://example.com/avatar.jpg',
        'sex': '0',
        'area': '未知地区',
        'index': '0',
        'intro': '简介',
        'home_url': 'https://example.com/singer',
        'provider_name': 'yinyan',
        'crawler_source_data': '{"id": 1}',
    }])

    _, rows = cur.executemany.call_args[0]
    assert rows[0][3:6] == ('U', '其他', '#')


def test_upsert_singers_preserves_valid_group_sex_enum_value():
    cur = MagicMock()
    upsert_qq_singers(cur, [{
        'id': 1,
        'mid': 'singer-mid',
        'name': '组合',
        'avatar': 'https://example.com/avatar.jpg',
        'sex': 'C',
        'area': '华语',
        'index': 'z',
        'intro': '简介',
        'home_url': 'https://example.com/singer',
        'provider_name': 'yinyan',
        'crawler_source_data': '{"id": 1}',
    }])

    _, rows = cur.executemany.call_args[0]
    assert rows[0][4:7] == ('C', '华语', 'Z')