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
"""

_PLATFORM_BY_RECORD_IDS_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 mr.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
"""

# 同 _PLATFORM_QUERY，但不做 per-platform 去重，保留同平台全部录音供 singer fallback 遍历
_ALL_PLATFORM_RECORDS_QUERY = _PLATFORM_QUERY


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 fetch_all_platform_records(source_conn: pymysql.Connection, song_ids: list[int]) -> list[dict]:
    """返回每首歌的全部录音（不做 per-platform 去重），供 singer fallback 遍历。"""
    if not song_ids:
        return []
    placeholders = ','.join(['%s'] * len(song_ids))
    query = _ALL_PLATFORM_RECORDS_QUERY.format(placeholders=placeholders)
    with source_conn.cursor() as cur:
        cur.execute(query, song_ids)
        rows = cur.fetchall()
    # 按优先级排序后按 record_id 去重（同一录音可能关联多次）
    seen_record_ids: set = set()
    result = []
    for row in sorted(rows, key=_record_priority):
        rid = row['record_id']
        if rid not in seen_record_ids:
            seen_record_ids.add(rid)
            result.append(row)
    return result


def fetch_platform_records_by_record_ids(source_conn: pymysql.Connection, record_ids: list[int]) -> list[dict]:
    """按状态表指定的录音 id 批量查询录音，不再按 song_id 拉取全平台候选。"""
    if not record_ids:
        return []
    unique_record_ids = list(dict.fromkeys(int(record_id) for record_id in record_ids))
    placeholders = ','.join(['%s'] * len(unique_record_ids))
    query = _PLATFORM_BY_RECORD_IDS_QUERY.format(placeholders=placeholders)
    with source_conn.cursor() as cur:
        cur.execute(query, unique_record_ids)
        rows = cur.fetchall()

    seen_record_ids: set = set()
    result = []
    for row in rows:
        rid = int(row['record_id'])
        if rid not in seen_record_ids:
            seen_record_ids.add(rid)
            result.append(row)
    return result


def fetch_record_platforms(source_conn: pymysql.Connection, record_ids: list[int]) -> dict[int, str]:
    """按录音 id 查询平台代码，用于回填 yinyan_song_records.platform。"""
    if not record_ids:
        return {}
    placeholders = ','.join(['%s'] * len(record_ids))
    query = f"""
        SELECT id, platform
        FROM hk_music_record
        WHERE id IN ({placeholders})
          AND platform IN ('1','2','4')
          AND deleted = 0
    """
    with source_conn.cursor() as cur:
        cur.execute(query, record_ids)
        rows = cur.fetchall()
    return {int(row['id']): str(row['platform']) for row in 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),
    )