from typing import Iterator
import pymysql
from .config import PLATFORMS, TARGET_TABLE_NAME


_HK_SONGS_QUERY = f"""
SELECT id, name, lyricist, composer, audio_url, lyrics_url,
       cover_url, singer, issue_time, source_song_id, song_time
FROM {TARGET_TABLE_NAME}
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 = f"""
SELECT id, name, lyricist, composer, audio_url, lyrics_url,
       cover_url, singer, issue_time, source_song_id, song_time
FROM {TARGET_TABLE_NAME}
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

# records2 只接收具备完整录音元数据的关联关系。
_RECORDS2_RELATIONS_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 TRIM(mr.platform_unique_key) != ''
  AND mr.deleted = 0
  AND mr.record_name IS NOT NULL AND TRIM(mr.record_name) != ''
  AND mr.duration IS NOT NULL AND mr.duration > 0
  AND mr.singer_name IS NOT NULL AND TRIM(mr.singer_name) != ''
  AND mr.platform_index_url IS NOT NULL AND TRIM(mr.platform_index_url) != ''
  AND (
      (mr.storage_url IS NOT NULL AND TRIM(mr.storage_url) != '')
      OR (mr.platform_play_url IS NOT NULL AND TRIM(mr.platform_play_url) != '')
  )
  AND mr.pub_time IS NOT NULL
ORDER BY sar.song_id, mr.id
"""


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],
    include_deleted: bool = False,
) -> 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)
    if include_deleted:
        query = query.replace("WHERE deleted = '0'", 'WHERE 1 = 1', 1)
    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 mark_hk_songs_deleted(conn: pymysql.Connection, source_song_ids: list[int]) -> int:
    """将无法导入的源歌曲在目标表中软删除。"""
    if not source_song_ids:
        return 0
    unique_ids = list(dict.fromkeys(int(song_id) for song_id in source_song_ids))
    placeholders = ','.join(['%s'] * len(unique_ids))
    with conn.cursor() as cur:
        cur.execute(
            f"UPDATE {TARGET_TABLE_NAME} SET deleted = '1' "
            f"WHERE source_song_id IN ({placeholders}) AND deleted = '0'",
            unique_ids,
        )
        return cur.rowcount


def mark_hk_songs_active(conn: pymysql.Connection, source_song_ids: list[int]) -> int:
    """恢复需要重新导入的 HK 源歌曲。"""
    if not source_song_ids:
        return 0
    unique_ids = list(dict.fromkeys(int(song_id) for song_id in source_song_ids))
    placeholders = ','.join(['%s'] * len(unique_ids))
    with conn.cursor() as cur:
        cur.execute(
            f"UPDATE {TARGET_TABLE_NAME} SET deleted = '0' "
            f"WHERE source_song_id IN ({placeholders}) AND deleted = '1'",
            unique_ids,
        )
        return cur.rowcount


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_all_song_record_relations(source_conn: pymysql.Connection, song_ids: list[int]) -> list[dict]:
    """返回指定 song_id 的全部必填字段完整的 (song_id, record_id, platform) 关联。"""
    if not song_ids:
        return []
    placeholders = ','.join(['%s'] * len(song_ids))
    query = _RECORDS2_RELATIONS_QUERY.format(placeholders=placeholders)
    with source_conn.cursor() as cur:
        cur.execute(query, song_ids)
        rows = cur.fetchall()

    # 同一关联可能因源表脏数据重复出现；保留一条即可。
    seen_relations: set[tuple[int, int]] = set()
    result = []
    for row in rows:
        key = (int(row['source_song_id']), int(row['record_id']))
        if key not in seen_relations:
            seen_relations.add(key)
            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),
    )