import json
import os
import sys
import urllib.request
from urllib.error import URLError

import mysql.connector

BASE_URL = 'https://wger.de/api/v2/exerciseinfo/'


def fetch_json(url: str):
    req = urllib.request.Request(
        url,
        headers={
            'User-Agent': 'MerobodyBot/1.0',
            'Accept': 'application/json',
        },
    )
    with urllib.request.urlopen(req, timeout=30) as response:
        return json.load(response)


def extract_names(items, field='name'):
    if not isinstance(items, list):
        return []
    names = []
    for item in items:
        if isinstance(item, dict):
            name = str(item.get(field, '') or '').strip()
            if name:
                names.append(name)
    return names


def pick_media_url(items, preferred='gif'):
    if not isinstance(items, list):
        return ''
    for item in items:
        if not isinstance(item, dict):
            continue
        if preferred == 'gif':
            value = str(item.get('image') or item.get('url') or '').strip()
            if value:
                return value
        elif preferred == 'video':
            value = str(item.get('video') or item.get('url') or '').strip()
            if value:
                return value
    return ''


def to_plain_text(html: str) -> str:
    if not html:
        return ''
    text = html.replace('<br />', ' ').replace('<br>', ' ')
    import re
    text = re.sub(r'<[^>]+>', ' ', text)
    text = text.replace('&nbsp;', ' ').replace('&amp;', '&').replace('&quot;', '"').replace('&#39;', "'")
    text = re.sub(r'\s+', ' ', text)
    return text.strip()


def is_cardio(muscles: list[str], category: str) -> bool:
    if 'cardio' in category.lower():
        return True
    return any('cardio' in muscle.lower() for muscle in muscles)


def main():
    host = os.getenv('FITNEP_DB_HOST', '127.0.0.1')
    db_name = os.getenv('FITNEP_DB_NAME', 'drsubashpandeynp_fitnep')
    user = os.getenv('FITNEP_DB_USER', 'drsubashpandeynp_fitnep')
    password = os.getenv('FITNEP_DB_PASS', 'JA[q!6GHBa(&e+sj')

    try:
        conn = mysql.connector.connect(
            host=host,
            database=db_name,
            user=user,
            password=password,
            unix_socket='/tmp/mysql.sock' if os.name != 'nt' else None,
            charset='utf8mb4',
            autocommit=True,
        )
    except Exception as exc:
        print(f'DATABASE_CONNECT_FAILED: {exc}')
        sys.exit(1)

    cursor = conn.cursor(dictionary=True)

    cursor.execute("ALTER TABLE exercises ADD COLUMN IF NOT EXISTS external_id VARCHAR(120) NULL")
    cursor.execute("ALTER TABLE exercises ADD COLUMN IF NOT EXISTS category VARCHAR(80) NOT NULL DEFAULT ''")
    cursor.execute("ALTER TABLE exercises ADD COLUMN IF NOT EXISTS image_url VARCHAR(255) NOT NULL DEFAULT ''")
    cursor.execute("ALTER TABLE exercises ADD COLUMN IF NOT EXISTS gif_url VARCHAR(255) NOT NULL DEFAULT ''")
    cursor.execute("CREATE UNIQUE INDEX IF NOT EXISTS idx_exercises_external_id ON exercises (external_id)")

    cursor.execute("SELECT id FROM users WHERE role = 'admin' ORDER BY id LIMIT 1")
    admin_row = cursor.fetchone()
    created_by = admin_row['id'] if admin_row else 1

    imported = 0
    updated = 0
    page = 1

    while True:
        url = f'{BASE_URL}?language=2&limit=100&page={page}'
        try:
            payload = fetch_json(url)
        except URLError as exc:
            print(f'FETCH_FAILED_PAGE_{page}: {exc}')
            break

        results = payload.get('results', []) if isinstance(payload, dict) else []
        if not isinstance(results, list) or not results:
            break

        for item in results:
            if not isinstance(item, dict):
                continue
            external_id = str(item.get('id') or '').strip()
            name = str(item.get('name') or '').strip()
            if not external_id or not name:
                continue

            category_name = str((item.get('category') or {}).get('name') or '').strip() or 'General'
            muscle_names = extract_names(item.get('muscles') or [])
            secondary_names = extract_names(item.get('muscles_secondary') or [])
            muscles = ', '.join(dict.fromkeys([*muscle_names, *secondary_names]))
            equipment = ', '.join(dict.fromkeys(extract_names(item.get('equipment') or [])))
            instruction = to_plain_text(str(item.get('description') or '')) or 'Perform with controlled movement and full range of motion.'
            video_url = pick_media_url(item.get('video') or [], preferred='video')
            image_url = pick_media_url(item.get('images') or [], preferred='gif')
            gif_url = pick_media_url(item.get('images') or [], preferred='gif')
            default_sets = 1 if is_cardio(muscle_names + secondary_names, category_name) else 3
            default_reps = 20 if default_sets == 1 else 12

            cursor.execute(
                """
                INSERT INTO exercises (
                    external_id, name, muscles, category, equipment, instruction, video_url, image_url, gif_url, default_sets, default_reps, created_by
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                ON DUPLICATE KEY UPDATE
                    name = VALUES(name),
                    muscles = VALUES(muscles),
                    category = VALUES(category),
                    equipment = VALUES(equipment),
                    instruction = VALUES(instruction),
                    video_url = VALUES(video_url),
                    image_url = VALUES(image_url),
                    gif_url = VALUES(gif_url),
                    default_sets = VALUES(default_sets),
                    default_reps = VALUES(default_reps)
                """,
                (
                    external_id,
                    name,
                    muscles,
                    category_name,
                    equipment,
                    instruction,
                    video_url,
                    image_url,
                    gif_url,
                    default_sets,
                    default_reps,
                    created_by,
                ),
            )
            if cursor.rowcount > 0:
                if cursor.lastrowid and cursor.lastrowid > 0:
                    imported += 1
                else:
                    updated += 1

        if not payload.get('next'):
            break
        page += 1
        if page > 20:
            break

    print(json.dumps({'success': True, 'imported': imported, 'updated': updated, 'pages': page - 1}, ensure_ascii=False))


if __name__ == '__main__':
    main()