import os
import requests
import sqlite3
import time
import json
from tqdm import tqdm

API_URL_MAIN = "http://192.168.1.200:3000/api/get"
API_URL_PAGE1 = "http://192.168.1.200:3000/api/get?page=1"
AUDIO_DIR = "analytics/streamlit/radio/uploads/suno/audios"
IMAGE_DIR = "analytics/streamlit/radio/uploads/suno/images"
DB_DIR = "analytics/streamlit/radio/uploads/suno/db"
DB_PATH = os.path.join(DB_DIR, "tracks.sqlite")

os.makedirs(AUDIO_DIR, exist_ok=True)
os.makedirs(IMAGE_DIR, exist_ok=True)
os.makedirs(DB_DIR, exist_ok=True)

def init_db():
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    c.execute("""
        CREATE TABLE IF NOT EXISTS tracks (
            id TEXT PRIMARY KEY,
            title TEXT,
            created_at TEXT,
            model_name TEXT,
            audio_url TEXT,
            file_path TEXT,
            image_url TEXT,
            image_path TEXT,
            tags TEXT,
            status TEXT,
            lyric TEXT,
            meta_json TEXT,
            added_at TEXT
        )
    """)
    conn.commit()
    return conn

def track_exists(conn, track_id):
    c = conn.cursor()
    c.execute("SELECT 1 FROM tracks WHERE id = ?", (track_id,))
    return c.fetchone() is not None

def get_track_file_path(conn, track_id):
    c = conn.cursor()
    c.execute("SELECT file_path FROM tracks WHERE id = ?", (track_id,))
    row = c.fetchone()
    return row[0] if row else None

def download_audio(audio_url, file_name):
    local_path = os.path.join(AUDIO_DIR, file_name)
    if os.path.exists(local_path):
        return local_path
    try:
        with requests.get(audio_url, stream=True, timeout=60) as r:
            r.raise_for_status()
            with open(local_path, "wb") as f:
                for chunk in r.iter_content(chunk_size=8192):
                    if chunk:
                        f.write(chunk)
        return local_path
    except Exception as e:
        print(f"Ошибка при скачивании {audio_url}: {e}")
        return None

def download_image(image_url, file_name):
    local_path = os.path.join(IMAGE_DIR, file_name)
    if os.path.exists(local_path):
        return local_path
    try:
        with requests.get(image_url, stream=True, timeout=30) as r:
            r.raise_for_status()
            with open(local_path, "wb") as f:
                for chunk in r.iter_content(chunk_size=8192):
                    if chunk:
                        f.write(chunk)
        return local_path
    except Exception as e:
        print(f"Ошибка при скачивании картинки {image_url}: {e}")
        return None

def save_track(conn, track, file_path, image_url, image_path):
    c = conn.cursor()
    # Кладём meta_json как валидный JSON!
    meta_json_val = json.dumps(track, ensure_ascii=False)
    c.execute("""
        INSERT OR REPLACE INTO tracks (
            id, title, created_at, model_name,
            audio_url, file_path, image_url, image_path, tags, status, lyric, meta_json, added_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    """, (
        track.get("id"),
        track.get("title"),
        track.get("created_at"),
        track.get("model_name"),
        track.get("audio_url"),
        file_path,
        image_url,
        image_path,
        track.get("tags"),
        track.get("status"),
        track.get("lyric"),
        meta_json_val,  # вот тут!
        time.strftime("%Y-%m-%d %H:%M:%S"),
    ))
    conn.commit()

def sync_tracks():
    print("Запускаем синхронизацию...")
    conn = init_db()
    try:
        # Забираем последние 20
        response_main = requests.get(API_URL_MAIN, timeout=60)
        response_main.raise_for_status()
        tracks_main = response_main.json()

        # Забираем следующие 5
        response_p1 = requests.get(API_URL_PAGE1, timeout=60)
        response_p1.raise_for_status()
        tracks_page1 = response_p1.json()

        # Собираем итоговый список без дублирования по id
        id_set = set()
        all_tracks = []
        for t in tracks_main[:20]:
            tid = t.get("id") or t.get("audio_url")
            if tid and tid not in id_set:
                all_tracks.append(t)
                id_set.add(tid)
        for t in tracks_page1[:5]:
            tid = t.get("id") or t.get("audio_url")
            if tid and tid not in id_set:
                all_tracks.append(t)
                id_set.add(tid)

        new_count = 0
        for track in tqdm(all_tracks, desc="Обработка треков"):
            track_id = track.get("id") or track.get("audio_url")
            if not track_id:
                continue

            needs_download = True
            if track_exists(conn, track_id):
                file_path = get_track_file_path(conn, track_id)
                if file_path and os.path.exists(file_path):
                    tqdm.write(f"Уже есть: {track_id}")
                    needs_download = False
                else:
                    tqdm.write(f"Файл отсутствует для {track_id}, перекачиваем...")

            if needs_download:
                audio_url = track.get("audio_url")
                if not audio_url:
                    tqdm.write(f"Нет audio_url для {track_id}")
                    continue
                mp3_name = os.path.basename(audio_url.split("?")[0])
                local_path = download_audio(audio_url, mp3_name)
                if not local_path:
                    continue

                image_url = (
                    track.get("image_url")
                    or track.get("cover_url")
                    or track.get("preview_url")
                )
                image_path = None
                if image_url:
                    image_name = os.path.basename(image_url.split("?")[0])
                    image_path = download_image(image_url, image_name)

                save_track(conn, track, local_path, image_url, image_path)
                new_count += 1
            else:
                image_url = (
                    track.get("image_url")
                    or track.get("cover_url")
                    or track.get("preview_url")
                )
                image_path = None
                if image_url:
                    image_name = os.path.basename(image_url.split("?")[0])
                    image_path = download_image(image_url, image_name)
                save_track(conn, track, file_path, image_url, image_path)
        print(f"Готово. Новых треков добавлено или восстановлено: {new_count}")
    except Exception as e:
        print(f"Ошибка при синхронизации: {e}")
    finally:
        conn.close()

if __name__ == "__main__":
    sync_tracks()