import json
import re
import urllib.request
from pathlib import Path

BASE_URL = 'https://wger.de/api/v2/exerciseinfo/'
OUT_SQL = Path(r'a:/merobody/backend/fitnep_api/exercise/import_rows.sql')


def fetch_page(page: int):
    url = f'{BASE_URL}?language=2&limit=100&page={page}'
    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):
    names = []
    for entry in items or []:
        if isinstance(entry, dict):
            value = str(entry.get('name') or entry.get('name_en') or '').strip()
            if value:
                names.append(value)
    return names


def pick_media(items, preferred='gif'):
    for entry in items or []:
        if not isinstance(entry, dict):
            continue
        if preferred == 'gif':
            value = str(entry.get('image') or entry.get('url') or '').strip()
            if value:
                return value
        if preferred == 'video':
            value = str(entry.get('video') or entry.get('url') or '').strip()
            if value:
                return value
    return ''


def extract_translation(item):
    translations = item.get('translations') or []
    for entry in translations:
        if not isinstance(entry, dict):
            continue
        language = str(entry.get('language') or '').strip()
        if language in {'2', 'en', 'en_US', 'en-us'}:
            name = str(entry.get('name') or '').strip()
            if name:
                return name, str(entry.get('description') or '')
    for entry in translations:
        if not isinstance(entry, dict):
            continue
        name = str(entry.get('name') or '').strip()
        if name:
            return name, str(entry.get('description') or '')
    return '', ''


def plain_text(html: str) -> str:
    if not html:
        return ''
    text = html.replace('<br />', ' ').replace('<br>', ' ')
    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 quote_sql(value: str) -> str:
    return value.replace("'", "''")


rows = []
page = 1
while page <= 5:
    payload = fetch_page(page)
    results = payload.get('results') or []
    if not results:
        break

    for item in results:
        if not isinstance(item, dict):
            continue
        external_id = str(item.get('id') or '').strip()
        name, description = extract_translation(item)
        if not external_id or not name:
            continue
        category = str((item.get('category') or {}).get('name') or '').strip() or 'General'
        muscles = extract_names(item.get('muscles') or []) + extract_names(item.get('muscles_secondary') or [])
        equipment = extract_names(item.get('equipment') or [])
        instruction = plain_text(description or str(item.get('description') or '')) or 'Perform with controlled movement and full range of motion.'
        video_url = pick_media(item.get('video') or [], preferred='video')
        image_url = pick_media(item.get('images') or [], preferred='gif')
        gif_url = pick_media(item.get('images') or [], preferred='gif')
        default_sets = 1 if 'cardio' in category.lower() or any('cardio' in m.lower() for m in muscles) else 3
        default_reps = 20 if default_sets == 1 else 12
        rows.append((external_id, name, ', '.join(dict.fromkeys(muscles)), category, ', '.join(dict.fromkeys(equipment)), instruction, video_url, image_url, gif_url, default_sets, default_reps))

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

with OUT_SQL.open('w', encoding='utf-8') as handle:
    handle.write('USE fitnep;\n')
    handle.write('ALTER TABLE exercises ADD COLUMN IF NOT EXISTS external_id VARCHAR(120) NULL;\n')
    handle.write('ALTER TABLE exercises ADD COLUMN IF NOT EXISTS category VARCHAR(80) NOT NULL DEFAULT "";\n')
    handle.write('ALTER TABLE exercises ADD COLUMN IF NOT EXISTS image_url VARCHAR(255) NOT NULL DEFAULT "";\n')
    handle.write('ALTER TABLE exercises ADD COLUMN IF NOT EXISTS gif_url VARCHAR(255) NOT NULL DEFAULT "";\n')
    handle.write('CREATE UNIQUE INDEX IF NOT EXISTS idx_exercises_external_id ON exercises (external_id);\n')
    for external_id, name, muscles, category, equipment, instruction, video_url, image_url, gif_url, default_sets, default_reps in rows:
        handle.write(
            "INSERT INTO exercises (external_id, name, muscles, category, equipment, instruction, video_url, image_url, gif_url, default_sets, default_reps, created_by) "
            f"VALUES ('{external_id}', '{quote_sql(name)}', '{quote_sql(muscles)}', '{quote_sql(category)}', '{quote_sql(equipment)}', '{quote_sql(instruction)}', '{quote_sql(video_url)}', '{quote_sql(image_url)}', '{quote_sql(gif_url)}', {default_sets}, {default_reps}, 1) "
            "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);\n"
        )

print(f'wrote {len(rows)} rows to {OUT_SQL}')