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, song_time
FROM hk_songs_test
WHERE deleted = '0'
  AND id > %s
  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
"""

_HK_SONGS_BY_SOURCE_IDS_QUERY = """
SELECT id, name, lyricist, composer, audio_url, lyrics_url,
       cover_url, singer, issue_time, source_song_id, song_time
FROM hk_songs_test
WHERE deleted = '0'
  AND source_song_id IN ({placeholders})
  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
"""

_PLATFORM_QUERY = """
SELECT
    sar.song_id AS source_song_id,
    mr.id AS record_id,
    mr.platform,
    mr.platform_unique_key,
    mr.platform_mid,
    mr.album_audio_id,
    sar.is_main_version,
    mr.is_high,
    mr.pub_time
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,
         COALESCE(sar.is_main_version, 0) DESC,
         COALESCE(mr.is_high, 0) DESC,
         (mr.pub_time IS NULL) ASC,
         mr.pub_time ASC,
         mr.id ASC
"""


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


def fetch_hk_songs_by_source_ids(conn: pymysql.Connection, source_song_ids: list[int]) -> dict[int, dict]:
    if not source_song_ids:
        return {}
    placeholders = ','.join(['%s'] * len(source_song_ids))
    query = _HK_SONGS_BY_SOURCE_IDS_QUERY.format(placeholders=placeholders)
    with conn.cursor() as cur:
        cur.execute(query, source_song_ids)
        rows = cur.fetchall()
    return {
        int(row['source_song_id']): row
        for row in rows
        if row.get('source_song_id') is not None
    }


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

    return select_platform_records(rows)


def select_platform_records(rows: list[dict]) -> list[dict]:
    # 每个 (source_song_id, platform) 保留一条，用于继续导入多个平台的录音数据。
    seen = {}
    for row in sorted(rows, key=_record_priority):
        key = (row['source_song_id'], row['platform'])
        if key not in seen:
            seen[key] = row
    return list(seen.values())


def select_primary_record(rows: list[dict]) -> dict | None:
    if not rows:
        return None
    return sorted(rows, key=_record_priority)[0]


def _record_priority(row: dict) -> tuple:
    pub_time = row.get('pub_time')
    return (
        row['source_song_id'],
        -(int(row.get('is_main_version') or 0)),
        -(int(row.get('is_high') or 0)),
        pub_time is None,
        pub_time or '',
        int(row.get('record_id') or 0),
    )