from typing import Iterator
import pymysql
from .config import PLATFORMS


_HK_SONGS_QUERY = """
SELECT id, name, lyricist, composer, audio_url, lyrics_url,
       cover_url, singer, issue_time, source_song_id
FROM hk_songs_test
WHERE deleted = '0'
  AND name IS NOT NULL AND name != ''
  AND audio_url IS NOT NULL AND audio_url != ''
  AND singer IS NOT NULL AND singer != ''
ORDER BY id
LIMIT %s OFFSET %s
"""

_PLATFORM_QUERY = """
SELECT
    sar.song_id AS source_song_id,
    mr.platform,
    mr.platform_unique_key,
    mr.platform_mid,
    mr.album_audio_id,
    sar.is_main_version
FROM hk_song_and_record sar
JOIN hk_music_record mr ON mr.id = sar.record_id
WHERE sar.song_id IN ({placeholders})
  AND mr.platform IN ('1','2','4')
  AND mr.platform_unique_key IS NOT NULL
  AND mr.platform_unique_key != ''
  AND mr.deleted = 0
ORDER BY sar.song_id, mr.platform, sar.is_main_version DESC
"""


def iter_hk_songs_batches(conn: pymysql.Connection, batch_size: int) -> Iterator[list[dict]]:
    offset = 0
    with conn.cursor() as cur:
        while True:
            cur.execute(_HK_SONGS_QUERY, (batch_size, offset))
            rows = cur.fetchall()
            if not rows:
                break
            yield rows
            if len(rows) < batch_size:
                break
            offset += batch_size


def fetch_platform_records(source_conn: pymysql.Connection, song_ids: list[int]) -> list[dict]:
    if not song_ids:
        return []
    placeholders = ','.join(['%s'] * len(song_ids))
    query = _PLATFORM_QUERY.format(placeholders=placeholders)
    with source_conn.cursor() as cur:
        cur.execute(query, song_ids)
        rows = cur.fetchall()

    # 每个 (source_song_id, platform) 只保留 is_main_version 最高的一条
    seen = {}
    for row in rows:
        key = (row['source_song_id'], row['platform'])
        if key not in seen:
            seen[key] = row
    return list(seen.values())