from unittest.mock import MagicMock, call
import pytest

import etl_to_crawler.writer as writer
from etl_to_crawler.writer import (
    fetch_pending_yinyan_song_records,
    fetch_yinyan_records_missing_platform,
    insert_yinyan_song_records,
    insert_yinyan_song_records2,
    delete_yinyan_song_records2_existing_relations,
    fetch_yinyan_song_ids,
    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,
    replace_qq_song,
    delete_qq_song_and_yinyan_record,
    mark_yinyan_record_resource_failed,
)


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_replace_qq_song_updates_existing_row_by_old_platform_song_id():
    cur = MagicMock()
    replace_qq_song(cur, 100, {
        'platform_song_id': 200, 'mid': 'new-mid', 'album_id': 20,
        'cover': 'https://oss.example/cover.jpg', 'title': '标题', 'name': '歌名',
        'duration': 180, 'singers_json': '[]', 'provider_name': 'yinyan',
        'crawler_source_data': '{}',
    })

    sql, params = cur.execute.call_args[0]
    assert 'UPDATE crawler_qqmusic_songs' in sql
    assert 'platform_song_id = %s' in sql
    assert params[-1] == 100
    assert params[0] == 200


def test_delete_qq_song_and_yinyan_record_cleans_song_relations_and_state():
    cur = MagicMock()
    delete_qq_song_and_yinyan_record(cur, 10, 100, 'song-uuid')

    assert cur.execute.call_count == 3
    calls = cur.execute.call_args_list
    assert 'crawler_qqmusic_singer_songs' in calls[0].args[0]
    assert 'crawler_qqmusic_songs' in calls[1].args[0]
    assert 'yinyan_song_records' in calls[2].args[0]


def test_mark_yinyan_record_resource_failed_keeps_unpushed_state_and_excludes_retry():
    cur = MagicMock()
    mark_yinyan_record_resource_failed(cur, 10, 100, '1')

    sql, params = cur.execute.call_args[0]
    assert f'UPDATE {writer.YINYAN_IMPORT_TABLE}' in sql
    assert 'is_yinyan_push = FALSE' in sql
    assert 'platform_song_id = %s' in sql
    assert params == (-1, 10, 100, '1')


def test_mark_yinyan_record_resource_failed_writes_specific_failure_code():
    cur = MagicMock()
    mark_yinyan_record_resource_failed(cur, 10, 100, '1', writer.YINYAN_IMPORT_FAILURE_NO_LYRIC)

    assert cur.execute.call_args[0][1] == (-2, 10, 100, '1')


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 f'UPDATE {writer.YINYAN_IMPORT_TABLE}' in sql
    assert f'UPDATE {writer.YINYAN_IMPORT_TABLE} AS ysr' in sql
    assert 'SET platform = v.platform' in sql
    assert 'platform_song_id = v.platform_song_id' in sql
    if writer.YINYAN_IMPORT_TABLE == 'yinyan_song_records2':
        assert 'record_id = v.record_id' not in sql
    else:
        assert 'record_id = v.record_id' in sql
    assert 'is_yinyan_push = TRUE' in sql
    assert 'source_record_id' in sql
    assert 'ysr.song_id = v.song_id' in sql
    assert 'ysr.record_id = v.source_record_id' in sql
    assert 'ysr.platform = v.platform' in sql
    assert 'ysr.is_yinyan_push = FALSE' in sql
    assert params == (10, 100, 100, '1', 1000, 11, 101, 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_insert_yinyan_song_records2_writes_all_candidates_before_deduplication():
    cur = MagicMock()
    insert_yinyan_song_records2(cur, [
        {'song_id': 10, 'record_id': 100, 'platform': '1'},
        {'song_id': 10, 'record_id': 200, 'platform': '2'},
    ])

    sql, params = cur.execute.call_args[0]
    assert 'INSERT INTO yinyan_song_records2' in sql
    assert 'VALUES (%s::bigint, %s::bigint, %s::varchar), (%s::bigint, %s::bigint, %s::varchar)' in sql
    assert 'ON CONFLICT (song_id, record_id) DO UPDATE' in sql
    assert params == (10, 100, '1', 10, 200, '2')


def test_delete_yinyan_song_records2_existing_relations_uses_records1_as_dedup_source():
    cur = MagicMock()
    delete_yinyan_song_records2_existing_relations(cur)

    sql = cur.execute.call_args[0][0]
    assert 'DELETE FROM yinyan_song_records2 AS ysr2' in sql
    assert 'USING yinyan_song_records AS ysr1' in sql
    assert 'ysr2.song_id = ysr1.song_id' in sql
    assert 'ysr2.record_id = ysr1.record_id' in sql
    assert 'is_yinyan_push' not in sql


def test_fetch_yinyan_song_ids_reads_all_records1_song_ids():
    cur = MagicMock()
    cur.fetchall.return_value = [(10,), (11,)]

    song_ids = fetch_yinyan_song_ids(cur)

    sql = cur.execute.call_args[0][0]
    assert 'SELECT DISTINCT song_id' in sql
    assert 'WHERE' not in sql
    assert song_ids == [10, 11]


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 'platform_song_id IS 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_fetch_pending_yinyan_song_records_filters_requested_platforms():
    cur = MagicMock()
    cur.fetchall.return_value = []

    fetch_pending_yinyan_song_records(cur, 10, ['4'])

    sql, params = cur.execute.call_args.args
    assert 'AND platform IN (%s)' in sql
    assert params == ('4', 10)


def test_reset_failed_yinyan_song_records_resets_selected_platforms_once():
    cur = MagicMock()
    cur.fetchall.return_value = [(10,), (10,), (11,)]

    reset_count, song_ids = writer.reset_failed_yinyan_song_records(cur, ['1', '2'])

    sql, params = cur.execute.call_args.args
    assert f'UPDATE {writer.YINYAN_IMPORT_TABLE}' in sql
    assert 'platform_song_id = NULL' in sql
    assert 'platform_song_id < 0' in sql
    assert 'RETURNING song_id' in sql
    assert params == ('1', '2')
    assert reset_count == 3
    assert song_ids == [10, 11]


def test_import_state_queries_can_target_records2(monkeypatch):
    monkeypatch.setattr(writer, 'YINYAN_IMPORT_TABLE', 'yinyan_song_records2')
    cur = MagicMock()
    cur.fetchall.return_value = []

    writer.fetch_pending_yinyan_song_records(cur, 100)
    assert 'FROM yinyan_song_records2' in cur.execute.call_args[0][0]

    writer.upsert_yinyan_song_records(cur, [
        {'song_id': 10, 'record_id': 100, 'platform': '1', 'platform_song_id': 1000},
    ])
    assert 'UPDATE yinyan_song_records2 AS ysr' in cur.execute.call_args[0][0]

    writer.mark_yinyan_record_resource_failed(cur, 10, 100, '1')
    assert 'UPDATE yinyan_song_records2' in cur.execute.call_args[0][0]


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}')


@pytest.mark.parametrize(
    ('upsert', 'table_name', 'extra'),
    [
        (upsert_qq_songs, 'crawler_qqmusic_songs', {'mid': 'song-mid'}),
        (upsert_kugou_songs, 'crawler_kugou_songs', {'hash': 'hash'}),
        (upsert_netease_songs, 'crawler_netease_songs', {}),
    ],
)
def test_song_conflict_only_fills_empty_cover_and_lyric_fields(upsert, table_name, extra):
    cur = MagicMock()
    upsert(cur, [{
        'song_uuid': 'song-uuid',
        'platform_song_id': 200,
        'name': '歌名',
        'cover': 'https://example.com/cover.jpg',
        'lyric': '歌词文本',
        'lyric_url': 'https://example.com/lyric.txt',
        **extra,
    }])

    sql = cur.executemany.call_args.args[0]
    assert 'ON CONFLICT (platform_song_id) DO UPDATE SET' in sql
    assert f"BTRIM(COALESCE({table_name}.cover, '')) = ''" in sql
    assert f"BTRIM(COALESCE({table_name}.lyric, '')) = ''" in sql
    assert f"BTRIM(COALESCE({table_name}.lyric_url, '')) = ''" in sql
    assert 'THEN EXCLUDED.lyric' in sql


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')