import sqlite3
from datetime import datetime
from config.settings import DB_NAME


def _connect():
    conn = sqlite3.connect(DB_NAME)
    conn.row_factory = sqlite3.Row
    return conn

def execute(query, params=()):
    conn = _connect(); cur = conn.cursor()
    cur.execute(query, params); conn.commit()
    lid = cur.lastrowid; conn.close(); return lid

def fetch_one(query, params=()):
    conn = _connect(); cur = conn.cursor()
    cur.execute(query, params); row = cur.fetchone()
    conn.close(); return dict(row) if row else None

def fetch_all(query, params=()):
    conn = _connect(); cur = conn.cursor()
    cur.execute(query, params); rows = cur.fetchall()
    conn.close(); return [dict(r) for r in rows]


def initialize():
    stmts = [
        """CREATE TABLE IF NOT EXISTS athletes (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            telegram_id INTEGER, username TEXT, full_name TEXT,
            age INTEGER, height INTEGER, weight REAL, phone TEXT,
            subscription_days INTEGER, created_at TEXT)""",
        """CREATE TABLE IF NOT EXISTS default_exercises (
            id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL,
            gif_url TEXT, muscle_group TEXT,
            photo_file_id TEXT, video_file_id TEXT, description TEXT)""",
        """CREATE TABLE IF NOT EXISTS default_programs (
            id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL)""",
        """CREATE TABLE IF NOT EXISTS default_program_exercises (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            program_id INTEGER NOT NULL, exercise_id INTEGER NOT NULL,
            sets INTEGER NOT NULL, reps TEXT NOT NULL, sort_order INTEGER DEFAULT 0,
            FOREIGN KEY(program_id) REFERENCES default_programs(id),
            FOREIGN KEY(exercise_id) REFERENCES default_exercises(id))""",
        """CREATE TABLE IF NOT EXISTS default_diets (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL, diet_text TEXT NOT NULL)""",
        """CREATE TABLE IF NOT EXISTS athlete_trainings (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            athlete_id INTEGER NOT NULL, day_number INTEGER NOT NULL,
            exercise_id INTEGER NOT NULL, sets INTEGER NOT NULL,
            reps TEXT NOT NULL, sort_order INTEGER DEFAULT 0,
            FOREIGN KEY(athlete_id) REFERENCES athletes(id),
            FOREIGN KEY(exercise_id) REFERENCES default_exercises(id))""",
        """CREATE TABLE IF NOT EXISTS athlete_diets (
            athlete_id INTEGER PRIMARY KEY, diet_text TEXT NOT NULL,
            FOREIGN KEY(athlete_id) REFERENCES athletes(id))""",
        """CREATE TABLE IF NOT EXISTS athlete_supplements (
            athlete_id INTEGER PRIMARY KEY, supplement_text TEXT NOT NULL,
            FOREIGN KEY(athlete_id) REFERENCES athletes(id))""",
        """CREATE TABLE IF NOT EXISTS athlete_photos (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            athlete_id INTEGER NOT NULL, file_id TEXT NOT NULL,
            jalali_date TEXT NOT NULL, created_at TEXT NOT NULL,
            FOREIGN KEY(athlete_id) REFERENCES athletes(id))""",
        """CREATE TABLE IF NOT EXISTS shop_bands (
            id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL)""",
        """CREATE TABLE IF NOT EXISTS shop_items (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            band_id INTEGER NOT NULL, name TEXT NOT NULL,
            price INTEGER NOT NULL DEFAULT 0, description TEXT,
            voice_file_id TEXT, photo_file_id TEXT,
            FOREIGN KEY(band_id) REFERENCES shop_bands(id))""",
        """CREATE TABLE IF NOT EXISTS shop_orders (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            athlete_id INTEGER NOT NULL, item_id INTEGER NOT NULL,
            quantity INTEGER NOT NULL DEFAULT 1,
            status TEXT NOT NULL DEFAULT 'pending',
            created_at TEXT,
            FOREIGN KEY(athlete_id) REFERENCES athletes(id),
            FOREIGN KEY(item_id) REFERENCES shop_items(id))""",
    ]
    for s in stmts:
        execute(s)
    _migrate_exercise_columns()
    _seed_exercises()
    _seed_diets()
    _seed_special_athlete()
    # migration: ستون سوپرست
    try:
        execute("ALTER TABLE athlete_trainings ADD COLUMN superset INTEGER DEFAULT 0")
    except Exception:
        pass
    # migration: حرکت هوازی
    try:
        execute("ALTER TABLE default_exercises ADD COLUMN is_cardio INTEGER DEFAULT 0")
    except Exception:
        pass
    # migration: رمز عبور وب
    try:
        execute("ALTER TABLE athletes ADD COLUMN password_hash TEXT")
    except Exception:
        pass
    execute("""CREATE TABLE IF NOT EXISTS web_otp (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        code TEXT NOT NULL, telegram_id INTEGER NOT NULL,
        username TEXT, created_at TEXT NOT NULL, used INTEGER DEFAULT 0)""")


def create_web_otp(telegram_id, username):
    """ساخت کد یکبارمصرف ۵ رقمی برای ورود/ثبت‌نام سایت."""
    import random
    from datetime import datetime
    code = f"{random.randint(10000, 99999)}"
    execute("UPDATE web_otp SET used=1 WHERE telegram_id=?", (telegram_id,))
    execute("INSERT INTO web_otp (code, telegram_id, username, created_at) VALUES (?,?,?,?)",
            (code, telegram_id, username or "", datetime.now().isoformat()))
    return code


def _migrate_exercise_columns():
    """اضافه کردن ستون‌های جدید به جدول حرکات اگر دیتابیس قدیمی باشد."""
    conn = _connect()
    cur = conn.cursor()
    cur.execute("PRAGMA table_info(default_exercises)")
    existing_cols = {row[1] for row in cur.fetchall()}
    needed = {
        "gif_url": "TEXT", "muscle_group": "TEXT",
        "photo_file_id": "TEXT", "video_file_id": "TEXT", "description": "TEXT",
    }
    for col, col_type in needed.items():
        if col not in existing_cols:
            cur.execute(f"ALTER TABLE default_exercises ADD COLUMN {col} {col_type}")
    conn.commit()
    conn.close()


def _seed_exercises():
    if fetch_one("SELECT id FROM default_exercises LIMIT 1"):
        return
    exercises = [
        # ── سینه ──────────────────────────────────────────────
        "پرس سینه هالتر","پرس سینه دمبل","پرس سینه دستگاه اسمیت",
        "پرس بالا سینه هالتر","پرس بالا سینه دمبل","پرس زیر سینه هالتر",
        "قفسه سینه دمبل","قفسه بالا سینه دمبل","کراس اور سیم کش",
        "پک دک (فلای دستگاه)","شنا سوئدی","شنا سینه","دیپ سینه",
        "پرس سینه سیم کش","پولاور دمبل","پرس سینه تک دمبل",

        # ── پشت ───────────────────────────────────────────────
        "زیربغل هالتر خم","زیربغل دمبل تک دست","زیربغل سیم کش (لت)",
        "زیربغل قایقی سیم کش","بارفیکس دست باز","بارفیکس دست جمع",
        "زیربغل تی بار","پشت دست باز سیم کش","پشت دست جمع سیم کش",
        "ددلیفت","ددلیفت رومانیایی","ددلیفت سومو","هایپراکستنشن",
        "شراگ هالتر","شراگ دمبل","پول‌اور سیم کش","فیس پول",

        # ── شانه ──────────────────────────────────────────────
        "سرشانه هالتر","سرشانه دمبل","نظامی هالتر","نظامی دستگاه اسمیت",
        "نشر از جانب دمبل","نشر از جلو دمبل","نشر خم دمبل (دلتوئید خلفی)",
        "سرشانه آرنولد","کول دستگاه","پرس سرشانه سیم کش",

        # ── جلو بازو ──────────────────────────────────────────
        "جلو بازو هالتر","جلو بازو دمبل","جلو بازو سیم کش",
        "جلو بازو لاری (هالتر EZ)","جلو بازو چکشی","جلو بازو میز شیب‌دار",
        "جلو بازو کانسانتریشن","جلو بازو دو سر دستگاه",

        # ── پشت بازو ──────────────────────────────────────────
        "پشت بازو هالتر","پشت بازو دمبل","پشت بازو سیم کش",
        "پشت بازو طناب سیم کش","پشت بازو دستگاه","دیپ پشت بازو",
        "پشت بازو خوابیده هالتر (فرنچ پرس)","پشت بازو تک دست سیم کش",

        # ── ساعد ──────────────────────────────────────────────
        "مچ دست هالتر (نشسته)","مچ دست معکوس هالتر","فارمر واک",

        # ── پا (ران) ──────────────────────────────────────────
        "اسکوات هالتر","اسکوات هاگ","پرس پا (لگ پرس)",
        "جلو ران دستگاه (لگ اکستنشن)","پشت ران دستگاه (لگ کرل)",
        "لانج دمبل","لانج هالتر","اسکوات بلغاری","اسکوات سومو",
        "هیپ تراست","پل باسن (گلوت بریج)","اسکوات گابلت",
        "اسمیت اسکوات","پرس تک پا",

        # ── ساق پا ────────────────────────────────────────────
        "ساق پا ایستاده","ساق پا نشسته","ساق پا دستگاه پرس",
        "ساق پا تک پا دمبل",

        # ── شکم ───────────────────────────────────────────────
        "کرانچ","پلانک","دراز و نشست","پا بالا آویزان (لگ ریز)",
        "کرانچ سیم کش","شکم زیر دستگاه","کرانچ کابلی",
        "روسی توییست","کوهنوردی (مانتین کلایمبر)","پلانک پهلو",
        "کرانچ پا دوچرخه","سیت‌آپ دکلاین",

        # ── کاردیو ────────────────────────────────────────────
        "دویدن روی تردمیل","دوچرخه ثابت","الپتیکال",
        "طناب زدن","بورپی","اسکی‌اِرگومتر",

        # ── ترکیبی (وزن بدن) ──────────────────────────────────
        "Pull-up","Chin-up","دیپ موازی","شنا دایموند",
        "بورپی کامل","جامپ اسکوات",
    ]
    for name in exercises:
        execute("INSERT INTO default_exercises (name) VALUES (?)", (name,))


def _seed_diets():
    if fetch_one("SELECT id FROM default_diets LIMIT 1"):
        return
    diets = [
        ("چربی سوز ۱", "صبحانه: ۳ تخم مرغ آبپز + نان سبوس + چای بدون قند\nمیان‌وعده صبح: بادام یا گردو\nناهار: ۲۰۰ گرم مرغ کبابی + سالاد + برنج قهوه‌ای\nمیان‌وعده عصر: ماست کم‌چرب + میوه فصل\nشام: ۱۵۰ گرم ماهی بخارپز + سبزیجات\nقبل خواب: یک لیوان شیر کم‌چرب"),
        ("چربی سوز ۲", "صبحانه: جو دوسر با شیر کم‌چرب + توت فرنگی\nمیان‌وعده صبح: سیب + گردو\nناهار: سینه مرغ گریل + سالاد سبزیجات + لوبیا\nمیان‌وعده عصر: پروتئین وی + موز\nشام: تخم مرغ آبپز + سبزی‌پلو\nقبل خواب: کاتیج چیز"),
        ("حجم گرفتن ۱", "صبحانه: ۵ تخم مرغ + نان تست + کره بادام + شیر\nمیان‌وعده صبح: پروتئین وی + موز + شیر\nناهار: ۳۰۰ گرم گوشت یا مرغ + برنج + سالاد\nمیان‌وعده عصر: ساندویچ تخم مرغ + پنیر\nشام: ماکارونی با گوشت + سالاد\nقبل خواب: پروتئین کازئین یا ماست چکیده"),
        ("حجم گرفتن ۲", "صبحانه: جو دوسر + شیر + عسل + موز + آجیل\nمیان‌وعده صبح: پروتئین وی + میوه خشک\nناهار: ۳۵۰ گرم مرغ + برنج ۲ کاسه + نان\nمیان‌وعده قبل تمرین: موز + خرما + پروتئین وی\nشام: تخم مرغ + حبوبات + سبزی\nقبل خواب: شیر گرم + پروتئین کازئین"),
        ("نگهداری عضله", "صبحانه: ۳ تخم مرغ + جو دوسر + میوه\nمیان‌وعده صبح: ماست یونانی + گردو\nناهار: ۲۵۰ گرم مرغ یا ماهی + برنج + سبزی\nمیان‌وعده عصر: پروتئین وی + موز\nشام: مرغ کبابی + سالاد + نان سبوس\nقبل خواب: ماست چکیده + آجیل"),
    ]
    for name, text in diets:
        execute("INSERT INTO default_diets (name, diet_text) VALUES (?,?)", (name, text))


# Athletes
def create_athlete(username, full_name, age, height, weight, phone, subscription_days):
    return execute(
        "INSERT INTO athletes (username,full_name,age,height,weight,phone,subscription_days,created_at) VALUES (?,?,?,?,?,?,?,?)",
        (username, full_name, age, height, weight, phone, subscription_days,
         datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
    )

def _seed_special_athlete():
    """ورزشکار همیشگی - اگر وجود نداشت اضافه می‌شود، اگر وجود داشت دست نمی‌خورد."""
    existing = fetch_one("SELECT id FROM athletes WHERE UPPER(username)=UPPER(?)", ("Praxissignall",))
    if existing:
        return
    create_athlete("Praxissignall", "میلاد کبیر", 30, 187, 94.0, None, 100000)

def get_all_athletes():
    return fetch_all("SELECT id, full_name, username FROM athletes ORDER BY full_name")

def get_athlete(athlete_id):
    return fetch_one("SELECT * FROM athletes WHERE id=?", (athlete_id,))

def get_athlete_by_username(username):
    return fetch_one("SELECT * FROM athletes WHERE UPPER(username)=UPPER(?)", (username,))

def get_athlete_by_name(full_name):
    return fetch_one("SELECT * FROM athletes WHERE full_name=?", (full_name,))

def save_athlete_chat_id(athlete_id, chat_id):
    execute("UPDATE athletes SET telegram_id=? WHERE id=?", (chat_id, athlete_id))

def update_athlete_field(athlete_id, field, value):
    execute(f"UPDATE athletes SET {field}=? WHERE id=?", (value, athlete_id))

def delete_athlete(athlete_id):
    for tbl in ("athlete_trainings","athlete_diets","athlete_supplements","athlete_photos","shop_orders"):
        execute(f"DELETE FROM {tbl} WHERE athlete_id=?", (athlete_id,))
    execute("DELETE FROM athletes WHERE id=?", (athlete_id,))

def search_athletes(query):
    q = f"%{query}%"
    return fetch_all(
        "SELECT id,full_name,username FROM athletes WHERE full_name LIKE ? OR username LIKE ? ORDER BY full_name",
        (q, q)
    )


# Default Exercises
def create_exercise(name):
    return execute("INSERT INTO default_exercises (name) VALUES (?)", (name,))

def get_all_exercises():
    return fetch_all("SELECT * FROM default_exercises ORDER BY name")

def get_exercise(exercise_id):
    return fetch_one("SELECT * FROM default_exercises WHERE id=?", (exercise_id,))

def search_exercises(query):
    q = f"%{query}%"
    return fetch_all("SELECT * FROM default_exercises WHERE name LIKE ? ORDER BY name", (q,))

def set_exercise_gif(exercise_id, gif_url):
    """ذخیره لینک گیف/ویدیوی آموزشی برای یک حرکت."""
    execute("UPDATE default_exercises SET gif_url=? WHERE id=?", (gif_url, exercise_id))

def get_exercise_gif(exercise_id):
    row = fetch_one("SELECT gif_url FROM default_exercises WHERE id=?", (exercise_id,))
    return row["gif_url"] if row else None

def set_exercise_photo(exercise_id, file_id):
    execute("UPDATE default_exercises SET photo_file_id=? WHERE id=?", (file_id, exercise_id))

def set_exercise_video(exercise_id, file_id):
    execute("UPDATE default_exercises SET video_file_id=? WHERE id=?", (file_id, exercise_id))

def set_exercise_description(exercise_id, text):
    execute("UPDATE default_exercises SET description=? WHERE id=?", (text, exercise_id))

def exercise_has_content(exercise):
    """آیا این حرکت عکس/فیلم/توضیحات دارد؟"""
    return bool(
        exercise.get("photo_file_id") or
        exercise.get("video_file_id") or
        exercise.get("description")
    )


# Default Programs
def create_default_program(name):
    return execute("INSERT INTO default_programs (name) VALUES (?)", (name,))

def get_all_default_programs():
    return fetch_all("SELECT id, name FROM default_programs ORDER BY name")

def get_default_program(program_id):
    return fetch_one("SELECT * FROM default_programs WHERE id=?", (program_id,))

def add_exercise_to_default_program(program_id, exercise_id, sets, reps):
    mo = fetch_one("SELECT MAX(sort_order) as mo FROM default_program_exercises WHERE program_id=?", (program_id,))
    order = (mo["mo"] or 0) + 1
    execute("INSERT INTO default_program_exercises (program_id,exercise_id,sets,reps,sort_order) VALUES (?,?,?,?,?)",
            (program_id, exercise_id, sets, reps, order))

def get_default_program_exercises(program_id):
    return fetch_all(
        """SELECT dpe.id, de.name AS exercise_name, dpe.sets, dpe.reps, dpe.sort_order
           FROM default_program_exercises dpe
           JOIN default_exercises de ON de.id=dpe.exercise_id
           WHERE dpe.program_id=? ORDER BY dpe.sort_order""", (program_id,))

def delete_default_program(program_id):
    execute("DELETE FROM default_program_exercises WHERE program_id=?", (program_id,))
    execute("DELETE FROM default_programs WHERE id=?", (program_id,))


# Default Diets
def create_default_diet(name, diet_text):
    return execute("INSERT INTO default_diets (name,diet_text) VALUES (?,?)", (name, diet_text))

def get_all_default_diets():
    return fetch_all("SELECT id, name FROM default_diets ORDER BY name")

def get_default_diet(diet_id):
    return fetch_one("SELECT * FROM default_diets WHERE id=?", (diet_id,))

def delete_default_diet(diet_id):
    execute("DELETE FROM default_diets WHERE id=?", (diet_id,))


# Athlete Trainings
def get_athlete_day_count(athlete_id):
    row = fetch_one("SELECT MAX(day_number) as md FROM athlete_trainings WHERE athlete_id=?", (athlete_id,))
    return row["md"] or 0

def add_exercise_to_athlete_day(athlete_id, day_number, exercise_id, sets, reps):
    mo = fetch_one("SELECT MAX(sort_order) as mo FROM athlete_trainings WHERE athlete_id=? AND day_number=?",
                   (athlete_id, day_number))
    order = (mo["mo"] or 0) + 1
    conn = _connect(); cur = conn.cursor()
    cur.execute("INSERT INTO athlete_trainings (athlete_id,day_number,exercise_id,sets,reps,sort_order) VALUES (?,?,?,?,?,?)",
                (athlete_id, day_number, exercise_id, sets, reps, order))
    row_id = cur.lastrowid
    conn.commit(); conn.close()
    return row_id


def set_training_superset(row_id):
    """علامت‌گذاری حرکت به عنوان سوپرست با حرکت بعدی."""
    execute("UPDATE athlete_trainings SET superset=1 WHERE id=?", (row_id,))

def copy_default_program_to_athlete(athlete_id, program_id):
    day_number = get_athlete_day_count(athlete_id) + 1
    for ex in get_default_program_exercises(program_id):
        row = fetch_one("SELECT id FROM default_exercises WHERE name=?", (ex["exercise_name"],))
        if row:
            add_exercise_to_athlete_day(athlete_id, day_number, row["id"], ex["sets"], ex["reps"])
    return day_number

def get_athlete_training_days(athlete_id):
    rows = fetch_all("SELECT DISTINCT day_number FROM athlete_trainings WHERE athlete_id=? ORDER BY day_number", (athlete_id,))
    return [r["day_number"] for r in rows]

def get_athlete_day_exercises(athlete_id, day_number):
    return fetch_all(
        """SELECT at.id, at.exercise_id, de.name AS exercise_name, at.sets, at.reps, at.sort_order, at.superset
           FROM athlete_trainings at JOIN default_exercises de ON de.id=at.exercise_id
           WHERE at.athlete_id=? AND at.day_number=? ORDER BY at.sort_order""",
        (athlete_id, day_number))

def delete_athlete_training_day(athlete_id, day_number):
    execute("DELETE FROM athlete_trainings WHERE athlete_id=? AND day_number=?", (athlete_id, day_number))
    # روزهای بعدی را یکی به عقب شیفت بده (روز ۲ → ۱، روز ۳ → ۲، ...)
    execute("UPDATE athlete_trainings SET day_number = day_number - 1 WHERE athlete_id=? AND day_number > ?",
            (athlete_id, day_number))


# Athlete Diet / Supplement
def set_athlete_diet(athlete_id, diet_text):
    execute("DELETE FROM athlete_diets WHERE athlete_id=?", (athlete_id,))
    execute("INSERT INTO athlete_diets (athlete_id,diet_text) VALUES (?,?)", (athlete_id, diet_text))

def get_athlete_diet(athlete_id):
    return fetch_one("SELECT diet_text FROM athlete_diets WHERE athlete_id=?", (athlete_id,))

def set_athlete_supplement(athlete_id, supplement_text):
    execute("DELETE FROM athlete_supplements WHERE athlete_id=?", (athlete_id,))
    execute("INSERT INTO athlete_supplements (athlete_id,supplement_text) VALUES (?,?)", (athlete_id, supplement_text))

def get_athlete_supplement(athlete_id):
    return fetch_one("SELECT supplement_text FROM athlete_supplements WHERE athlete_id=?", (athlete_id,))


# Athlete Photos (Gallery)
def add_athlete_photo(athlete_id, file_id, jalali_date):
    return execute(
        "INSERT INTO athlete_photos (athlete_id,file_id,jalali_date,created_at) VALUES (?,?,?,?)",
        (athlete_id, file_id, jalali_date, datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
    )

def get_athlete_photos(athlete_id):
    return fetch_all(
        "SELECT * FROM athlete_photos WHERE athlete_id=? ORDER BY created_at DESC",
        (athlete_id,)
    )

def delete_athlete_photo(photo_id):
    execute("DELETE FROM athlete_photos WHERE id=?", (photo_id,))


# Shop
def create_shop_band(name):
    return execute("INSERT INTO shop_bands (name) VALUES (?)", (name,))

def get_all_shop_bands():
    return fetch_all("SELECT * FROM shop_bands ORDER BY name")

def get_shop_band(band_id):
    return fetch_one("SELECT * FROM shop_bands WHERE id=?", (band_id,))

def delete_shop_band(band_id):
    execute("DELETE FROM shop_items WHERE band_id=?", (band_id,))
    execute("DELETE FROM shop_bands WHERE id=?", (band_id,))

def create_shop_item(band_id, name, price, description, voice_file_id=None, photo_file_id=None):
    return execute(
        "INSERT INTO shop_items (band_id,name,price,description,voice_file_id,photo_file_id) VALUES (?,?,?,?,?,?)",
        (band_id, name, price, description, voice_file_id, photo_file_id)
    )

def get_items_by_band(band_id):
    return fetch_all("SELECT * FROM shop_items WHERE band_id=? ORDER BY name", (band_id,))

def get_shop_item(item_id):
    return fetch_one("SELECT * FROM shop_items WHERE id=?", (item_id,))

def delete_shop_item(item_id):
    execute("DELETE FROM shop_items WHERE id=?", (item_id,))

def create_order(athlete_id, item_id, quantity):
    return execute(
        "INSERT INTO shop_orders (athlete_id,item_id,quantity,status,created_at) VALUES (?,?,?,'pending',?)",
        (athlete_id, item_id, quantity, datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
    )

def get_all_orders():
    return fetch_all(
        """SELECT o.id, a.full_name as athlete_name, i.name as item_name,
                  i.price, o.quantity, o.status, o.created_at
           FROM shop_orders o
           JOIN athletes a ON a.id=o.athlete_id
           JOIN shop_items i ON i.id=o.item_id
           ORDER BY o.created_at DESC""")

def get_orders_by_athlete(athlete_id):
    return fetch_all(
        """SELECT o.id, i.name as item_name, i.price, i.photo_file_id,
                  o.quantity, o.status, o.created_at
           FROM shop_orders o JOIN shop_items i ON i.id=o.item_id
           WHERE o.athlete_id=? ORDER BY o.created_at DESC""",
        (athlete_id,))

def update_order_status(order_id, status):
    execute("UPDATE shop_orders SET status=? WHERE id=?", (status, order_id))