import hashlib
import json
import os
import re
import secrets
import smtplib
from datetime import date, datetime, timedelta, timezone
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from functools import wraps
from html import escape

import bcrypt
import jwt
import mysql.connector
import requests
from bs4 import BeautifulSoup
from dotenv import load_dotenv
from flask import Flask, g, jsonify, request, Response
from flask_cors import CORS

load_dotenv()
app = Flask(__name__)
configured_origin = os.getenv('CORS_ORIGIN', 'http://localhost:4200')
allowed_origins = list(dict.fromkeys([
    configured_origin,
    'https://veikkaus.frilcon.fi',
    'https://www.veikkaus.frilcon.fi',
]))
CORS(app, origins=allowed_origins)


@app.errorhandler(Exception)
def handle_unexpected_error(error):
    app.logger.exception('Unhandled API error: %s', error)
    return jsonify(error='Palvelinvirhe', detail=str(error)), 500

JWT_ACCESS_SECRET = os.getenv('JWT_ACCESS_SECRET', 'change-me-access-secret')
JWT_REFRESH_SECRET = os.getenv('JWT_REFRESH_SECRET', 'change-me-refresh-secret')
ACCESS_MINUTES = 15
REFRESH_DAYS = int(os.getenv('JWT_REFRESH_EXPIRES_DAYS', '30'))
CEV_HOST = 'https://www-old.cev.eu'
CEV_COMPETITION = f'{CEV_HOST}/Competition-Area/Competition.aspx?ID=1572&PID=2990'
CEV_MATCHES = f'{CEV_HOST}/Competition-Area/CompetitionView.aspx?ID=1572&CID=12864&PID=2990&type=LegList'
WORLD_CUP_SOURCE = 'https://raw.githubusercontent.com/openfootball/worldcup.json/master/2026/worldcup.json'
EUROVOLLEY_ROUND_UTC_OFFSETS = {
    'Pool A': 2,
    'Pool B': 3,
    'Pool C': 3,
    'Pool D': 3,
}


def eurovolley_utc_offset_hours(match):
    round_name = match.get('round')
    if round_name in EUROVOLLEY_ROUND_UTC_OFFSETS:
        return EUROVOLLEY_ROUND_UTC_OFFSETS[round_name]

    venue = (match.get('venue') or '').lower()
    if 'modena' in venue or 'italy' in venue or 'ita' in venue:
        return 2
    if 'sofia' in venue or 'bulgaria' in venue or 'bul' in venue:
        return 3
    if 'tampere' in venue or 'finland' in venue or 'fin' in venue:
        return 3
    if 'cluj' in venue or 'romania' in venue or 'rou' in venue:
        return 3
    return 3


def with_timezone_offset(value, utc_offset_hours):
    if not value:
        return value
    parsed_value = datetime.fromisoformat(value) if isinstance(value, str) else value
    if parsed_value.tzinfo is None:
        parsed_value = parsed_value.replace(tzinfo=timezone(timedelta(hours=utc_offset_hours)))
    return parsed_value.isoformat(timespec='seconds')


def format_eurovolley_match_times(matches):
    for match in matches:
        utc_offset_hours = eurovolley_utc_offset_hours(match)
        match['kickoff_at'] = with_timezone_offset(match.get('kickoff_at'), utc_offset_hours)
        match['betting_locks_at'] = with_timezone_offset(match.get('betting_locks_at'), utc_offset_hours)
    return matches


def parse_cev_set_scores(html):
    text = BeautifulSoup(html, 'html.parser').get_text(' ', strip=True)
    set_scores = []
    for set_number, home_score, away_score, duration in re.findall(r'Set\s+(\d+):\s*(\d+)\s*-\s*(\d+)\s*\((\d+)\s*min\.\)', text):
        set_scores.append({
            'set': int(set_number),
            'home': int(home_score),
            'away': int(away_score),
            'duration': int(duration),
        })
    return set_scores


def ensure_match_set_scores_column():
    existing = query("SHOW COLUMNS FROM matches LIKE 'match_set_scores'", one=True)
    if not existing:
        execute('ALTER TABLE matches ADD COLUMN match_set_scores TEXT NULL AFTER away_penalties')


def db():
    if 'db' not in g:
        g.db = mysql.connector.connect(
            host=os.getenv('DB_HOST', 'localhost'), port=int(os.getenv('DB_PORT', '3306')),
            user=os.getenv('DB_USER', 'root'), password=os.getenv('DB_PASSWORD', ''),
            database=os.getenv('DB_NAME', 'jalkapallo_veikkaus'), autocommit=False,
        )
    return g.db


@app.teardown_appcontext
def close_db(_error):
    connection = g.pop('db', None)
    if connection and connection.is_connected():
        connection.close()


def query(sql, params=(), one=False, commit=False):
    cursor = db().cursor(dictionary=True)
    cursor.execute(sql, params)
    rows = cursor.fetchone() if one else cursor.fetchall()
    if commit:
        db().commit()
    cursor.close()
    return rows


def serialize_query_result(value):
    if isinstance(value, list):
        return [serialize_query_result(row) for row in value]
    if isinstance(value, dict):
        return {key: serialize_query_result(item) for key, item in value.items()}
    if isinstance(value, datetime):
        return value.isoformat(timespec='seconds')
    if isinstance(value, date):
        return value.isoformat()
    return value


def execute(sql, params=()):
    cursor = db().cursor()
    cursor.execute(sql, params)
    db().commit()
    result = cursor.rowcount
    cursor.close()
    return result


def json_error(message, status):
    return jsonify(error=message), status


def token_hash(value):
    return hashlib.sha256(value.encode()).hexdigest()


def access_token(user):
    return jwt.encode({'userId': user['id'], 'username': user['username'], 'role': user['role'],
                       'exp': datetime.now(timezone.utc) + timedelta(minutes=ACCESS_MINUTES)},
                      JWT_ACCESS_SECRET, algorithm='HS256')


def send_email(to, subject, text, html):
    host = os.getenv('SMTP_HOST')
    if not host:
        app.logger.info('[DEV] Email to %s: %s\n%s', to, subject, text)
        return

    port = int(os.getenv('SMTP_PORT', '587'))
    username = os.getenv('SMTP_USER')
    password = os.getenv('SMTP_PASSWORD')
    sender = os.getenv('SMTP_FROM') or username
    use_ssl = os.getenv('SMTP_SECURE', '').lower() == 'true'

    message = MIMEMultipart('alternative')
    message['Subject'] = subject
    message['From'] = sender
    message['To'] = to
    message.attach(MIMEText(text, 'plain', 'utf-8'))
    message.attach(MIMEText(html, 'html', 'utf-8'))

    smtp_class = smtplib.SMTP_SSL if use_ssl else smtplib.SMTP
    with smtp_class(host, port, timeout=20) as server:
        if not use_ssl:
            server.starttls()
        if username:
            server.login(username, password or '')
        server.sendmail(sender, [to], message.as_string())


def send_activation_email(to, display_name, activation_url):
    minutes = os.getenv('ACCOUNT_ACTIVATION_EXPIRES_MIN', '30')
    safe_display_name = escape(display_name)
    safe_activation_url = escape(activation_url, quote=True)
    text = (f'Hei {display_name}!\n\n'
            f'Aktivoi tunnuksesi klikkaamalla linkkiä (voimassa {minutes} minuuttia):\n'
            f'{activation_url}\n\n'
            'Jos et rekisteröitynyt, voit jättää tämän viestin huomiotta.')
    html = (f'<p>Hei {safe_display_name}!</p>'
            f'<p>Aktivoi tunnuksesi klikkaamalla alla olevaa linkkiä (voimassa {minutes} minuuttia):</p>'
        f'<p><a href="{safe_activation_url}">{safe_activation_url}</a></p>'
            '<p>Jos et rekisteröitynyt, voit jättää tämän viestin huomiotta.</p>')
    send_email(to, 'Aktivoi tunnuksesi - Jalkapallo-veikkaus', text, html)


def send_password_reset_email(to, display_name, reset_url):
    minutes = os.getenv('PASSWORD_RESET_EXPIRES_MIN', '30')
    safe_display_name = escape(display_name)
    safe_reset_url = escape(reset_url, quote=True)
    text = (f'Hei {display_name}!\n\n'
            f'Nollaa salasanasi klikkaamalla linkkiä (voimassa {minutes} minuuttia):\n'
            f'{reset_url}\n\n'
            'Jos et pyytänyt salasanan nollausta, voit jättää tämän viestin huomiotta.')
    html = (f'<p>Hei {safe_display_name}!</p>'
            f'<p>Nollaa salasanasi klikkaamalla alla olevaa linkkiä (voimassa {minutes} minuuttia):</p>'
        f'<p><a href="{safe_reset_url}">{safe_reset_url}</a></p>'
            '<p>Jos et pyytänyt salasanan nollausta, voit jättää tämän viestin huomiotta.</p>')
    send_email(to, 'Salasanan nollaus - Jalkapallo-veikkaus', text, html)


def refresh_token(user_id):
    return jwt.encode({'userId': user_id, 'exp': datetime.now(timezone.utc) + timedelta(days=REFRESH_DAYS)},
                      JWT_REFRESH_SECRET, algorithm='HS256')


def auth_required(role=None):
    def decorator(handler):
        @wraps(handler)
        def wrapped(*args, **kwargs):
            header = request.headers.get('Authorization', '')
            if not header.startswith('Bearer '):
                return json_error('Kirjautuminen vaaditaan', 401)
            try:
                user = jwt.decode(header[7:], JWT_ACCESS_SECRET, algorithms=['HS256'])
            except jwt.InvalidTokenError:
                return json_error('Virheellinen tai vanhentunut token', 401)
            if role and user.get('role') != role:
                return json_error('Ei riittäviä oikeuksia', 403)
            g.user = user
            return handler(*args, **kwargs)
        return wrapped
    return decorator


def user_row(identifier):
    return query("""SELECT u.id, u.username, u.email, u.password_hash, u.display_name,
                         u.is_active, r.name AS role
                  FROM users u JOIN roles r ON r.id = u.role_id
                  WHERE u.username = %s OR u.email = %s LIMIT 1""", (identifier, identifier), one=True)


@app.get('/api/health')
def health():
    return jsonify(status='ok')


@app.post('/api/auth/register')
def register():
    data = request.get_json(silent=True) or {}
    username, email, password, display_name = (data.get('username'), data.get('email'), data.get('password'), data.get('displayName'))
    if not isinstance(username, str) or not (3 <= len(username) <= 50) or not username.replace('_', '').replace('-', '').replace('.', '').isalnum():
        return json_error('Virheellinen syöte', 400)
    if not isinstance(email, str) or '@' not in email or not isinstance(password, str) or not (8 <= len(password) <= 200) or not display_name:
        return json_error('Virheellinen syöte', 400)
    if query('SELECT id FROM users WHERE username = %s OR email = %s LIMIT 1', (username, email), one=True):
        return json_error('Käyttäjätunnus tai sähköposti on jo käytössä', 409)
    password_hash = bcrypt.hashpw(password.encode(), bcrypt.gensalt(12)).decode()
    execute("""INSERT INTO users (username,email,password_hash,display_name,role_id,is_active)
                VALUES (%s,%s,%s,%s,(SELECT id FROM roles WHERE name='user'),0)""",
            (username, email, password_hash, display_name))
    user = query('SELECT id FROM users WHERE username = %s', (username,), one=True)
    raw = secrets.token_hex(32)
    minutes = int(os.getenv('ACCOUNT_ACTIVATION_EXPIRES_MIN', '30'))
    execute('INSERT INTO email_verifications (user_id,token_hash,expires_at) VALUES (%s,%s,%s)',
            (user['id'], token_hash(raw), datetime.now() + timedelta(minutes=minutes)))
    activation_url = f"{os.getenv('FRONTEND_URL', 'http://localhost:4200')}/activate?token={raw}"
    send_activation_email(email, display_name, activation_url)
    return jsonify(message='Tarkista sähköpostisi ja aktivoi tunnuksesi ennen kirjautumista.'), 201


@app.post('/api/auth/activate')
def activate():
    token = (request.get_json(silent=True) or {}).get('token')
    row = query("""SELECT id,user_id FROM email_verifications
                   WHERE token_hash=%s AND used_at IS NULL AND expires_at > NOW() LIMIT 1""", (token_hash(token or ''),), one=True)
    if not row:
        return json_error('Aktivointilinkki on virheellinen tai vanhentunut', 400)
    execute('UPDATE email_verifications SET used_at=NOW() WHERE id=%s', (row['id'],))
    execute('UPDATE users SET is_active=1,email_verified_at=NOW() WHERE id=%s', (row['user_id'],))
    return jsonify(success=True)


@app.post('/api/auth/resend-activation')
def resend_activation():
    email = (request.get_json(silent=True) or {}).get('email', '')
    user = query('SELECT id,email,display_name FROM users WHERE email=%s AND is_active=0 LIMIT 1', (email,), one=True)
    if user:
        raw = secrets.token_hex(32)
        execute('INSERT INTO email_verifications (user_id,token_hash,expires_at) VALUES (%s,%s,%s)',
                (user['id'], token_hash(raw), datetime.now() + timedelta(minutes=int(os.getenv('ACCOUNT_ACTIVATION_EXPIRES_MIN', '30')))))
        activation_url = f"{os.getenv('FRONTEND_URL', 'http://localhost:4200')}/activate?token={raw}"
        send_activation_email(user['email'], user['display_name'], activation_url)
    return jsonify(message='Jos tili odottaa aktivointia, uusi aktivointilinkki on lähetetty.')


@app.post('/api/auth/forgot-password')
def forgot_password():
    email = (request.get_json(silent=True) or {}).get('email', '')
    user = query('SELECT id,email,display_name FROM users WHERE email=%s LIMIT 1', (email,), one=True)
    if user:
        raw = secrets.token_hex(32)
        execute('INSERT INTO password_resets (user_id,token_hash,expires_at) VALUES (%s,%s,%s)',
                (user['id'], token_hash(raw), datetime.now() + timedelta(minutes=int(os.getenv('PASSWORD_RESET_EXPIRES_MIN', '30')))))
        reset_url = f"{os.getenv('FRONTEND_URL', 'http://localhost:4200')}/reset-password?token={raw}"
        send_password_reset_email(user['email'], user['display_name'], reset_url)
    return jsonify(message='Jos sähköpostiosoite löytyy järjestelmästä, salasanan nollauslinkki on lähetetty.')


@app.post('/api/auth/reset-password')
def reset_password():
    data = request.get_json(silent=True) or {}
    token, password = data.get('token', ''), data.get('newPassword', '')
    if not isinstance(password, str) or not 8 <= len(password) <= 200:
        return json_error('Virheellinen syöte', 400)
    reset = query("""SELECT id,user_id FROM password_resets
                    WHERE token_hash=%s AND used_at IS NULL AND expires_at > NOW() LIMIT 1""", (token_hash(token),), one=True)
    if not reset:
        return json_error('Nollauslinkki on virheellinen tai vanhentunut', 400)
    password_hash = bcrypt.hashpw(password.encode(), bcrypt.gensalt(12)).decode()
    execute('UPDATE users SET password_hash=%s WHERE id=%s', (password_hash, reset['user_id']))
    execute('UPDATE password_resets SET used_at=NOW() WHERE id=%s', (reset['id'],))
    execute('UPDATE user_sessions SET revoked_at=NOW() WHERE user_id=%s AND revoked_at IS NULL', (reset['user_id'],))
    return jsonify(success=True)


@app.post('/api/auth/login')
def login():
    data = request.get_json(silent=True) or {}
    user = user_row(data.get('usernameOrEmail', ''))
    if not user or not bcrypt.checkpw(str(data.get('password', '')).encode(), user['password_hash'].encode()):
        return json_error('Väärä käyttäjätunnus tai salasana', 401)
    if not user['is_active']:
        return json_error('Tiliä ei ole vielä aktivoitu. Tarkista sähköpostisi aktivointilinkin varalta.', 403)
    refresh = refresh_token(user['id'])
    execute('INSERT INTO user_sessions (user_id,refresh_token_hash,expires_at) VALUES (%s,%s,%s)',
            (user['id'], token_hash(refresh), datetime.now() + timedelta(days=REFRESH_DAYS)))
    return jsonify(accessToken=access_token(user), refreshToken=refresh,
                   user={'id': user['id'], 'username': user['username'], 'email': user['email'],
                         'displayName': user['display_name'], 'role': user['role']})


@app.post('/api/auth/refresh')
def refresh():
    raw = (request.get_json(silent=True) or {}).get('refreshToken')
    if not raw:
        return json_error('refreshToken puuttuu', 400)
    try:
        payload = jwt.decode(raw, JWT_REFRESH_SECRET, algorithms=['HS256'])
    except jwt.InvalidTokenError:
        return json_error('Virheellinen tai vanhentunut refresh-token', 401)
    session = query("""SELECT id FROM user_sessions WHERE user_id=%s AND refresh_token_hash=%s
                      AND revoked_at IS NULL AND expires_at > NOW()""", (payload['userId'], token_hash(raw)), one=True)
    if not session:
        return json_error('Istunto ei ole enää voimassa', 401)
    user = query("""SELECT u.id,u.username,r.name AS role FROM users u JOIN roles r ON r.id=u.role_id
                   WHERE u.id=%s""", (payload['userId'],), one=True)
    if not user:
        return json_error('Käyttäjää ei löytynyt', 401)
    return jsonify(accessToken=access_token(user))


@app.post('/api/auth/logout')
def logout():
    raw = (request.get_json(silent=True) or {}).get('refreshToken')
    if raw:
        execute('UPDATE user_sessions SET revoked_at=NOW() WHERE refresh_token_hash=%s', (token_hash(raw),))
    return '', 204


@app.get('/api/tournaments')
def tournaments():
    return jsonify(query('SELECT * FROM tournaments ORDER BY season_year DESC'))


@app.get('/api/tournaments/<int:tournament_id>/teams')
def teams(tournament_id):
    return jsonify(query('SELECT * FROM teams WHERE tournament_id=%s ORDER BY group_name,name', (tournament_id,)))


@app.get('/api/tournaments/<int:tournament_id>/matches')
def matches(tournament_id):
    tournament = query('SELECT slug FROM tournaments WHERE id=%s', (tournament_id,), one=True)
    if tournament and tournament['slug'] == 'cev-eurovolley-2026-men':
        sync_eurovolley_results(tournament_id)
    elif tournament and tournament['slug'] == 'world-cup-2026':
        sync_world_cup_results(tournament_id)
    matches = query("""SELECT m.*,ht.name AS home_team_name,ht.flag_url AS home_team_flag_url,
                           at.name AS away_team_name,at.flag_url AS away_team_flag_url
                    FROM matches m LEFT JOIN teams ht ON ht.id=m.home_team_id
                    LEFT JOIN teams at ON at.id=m.away_team_id WHERE m.tournament_id=%s
                    ORDER BY m.kickoff_at ASC""", (tournament_id,))
    if tournament and tournament['slug'] == 'cev-eurovolley-2026-men':
        format_eurovolley_match_times(matches)
    return jsonify(serialize_query_result(matches))


@app.get('/api/tournaments/<int:tournament_id>/players')
def players(tournament_id):
    position = request.args.get('position')
    sql = """SELECT p.*,t.name AS team_name FROM players p JOIN teams t ON t.id=p.team_id
              WHERE t.tournament_id=%s"""
    params = [tournament_id]
    if position:
        sql += ' AND p.position=%s'
        params.append(position)
    sql += ' ORDER BY t.name,p.name'
    return jsonify(query(sql, params))


@app.get('/api/pools/mine')
@auth_required()
def my_pools():
    return jsonify(query("""SELECT p.*,t.name AS tournament_name FROM pools p JOIN tournaments t ON t.id=p.tournament_id
                          JOIN pool_members pm ON pm.pool_id=p.id WHERE pm.user_id=%s""", (g.user['userId'],)))


@app.post('/api/pools')
@auth_required('admin')
def create_pool():
    data = request.get_json(silent=True) or {}
    if not isinstance(data.get('tournamentId'), int) or not data.get('name'):
        return json_error('Virheellinen syöte', 400)
    invite = secrets.token_hex(4).upper()
    cursor = db().cursor()
    cursor.execute('INSERT INTO pools (tournament_id,name,invite_code,owner_user_id) VALUES (%s,%s,%s,%s)',
                   (data['tournamentId'], data['name'].strip(), invite, g.user['userId']))
    pool_id = cursor.lastrowid
    cursor.execute('INSERT INTO pool_members (pool_id,user_id) VALUES (%s,%s)', (pool_id, g.user['userId']))
    db().commit(); cursor.close()
    return jsonify(id=pool_id, name=data['name'].strip(), inviteCode=invite, tournamentId=data['tournamentId']), 201


@app.delete('/api/pools/<int:pool_id>')
@auth_required('admin')
def delete_pool(pool_id):
    if execute('DELETE FROM pools WHERE id=%s', (pool_id,)) == 0:
        return json_error('Porukkaa ei löytynyt', 404)
    return '', 204


@app.post('/api/pools/join')
@auth_required()
def join_pool():
    invite = str((request.get_json(silent=True) or {}).get('inviteCode', '')).strip().upper()
    pool_row = query('SELECT id FROM pools WHERE invite_code=%s', (invite,), one=True)
    if not pool_row:
        return json_error('Porukkaa ei löytynyt', 404)
    execute('INSERT IGNORE INTO pool_members (pool_id,user_id) VALUES (%s,%s)', (pool_row['id'], g.user['userId']))
    return '', 204


@app.get('/api/pools/<int:pool_id>/leaderboard')
@auth_required()
def leaderboard(pool_id):
    if not query('SELECT 1 FROM pool_members WHERE pool_id=%s AND user_id=%s', (pool_id, g.user['userId']), one=True):
        return json_error('Et kuulu tähän porukkaan', 403)
    return jsonify(query('SELECT * FROM pool_leaderboard WHERE pool_id=%s ORDER BY total_points DESC', (pool_id,)))


@app.put('/api/predictions')
@auth_required()
def save_prediction():
    data = request.get_json(silent=True) or {}
    required = ('matchId', 'poolId', 'predictedHome', 'predictedAway')
    if any(not isinstance(data.get(key), int) for key in required):
        return json_error('Virheellinen syöte', 400)
    match = query('SELECT betting_locks_at,kickoff_at,status FROM matches WHERE id=%s', (data['matchId'],), one=True)
    if not match:
        return json_error('Ottelua ei löytynyt', 404)
    lock = match['betting_locks_at'] or match['kickoff_at']
    if match['status'] != 'scheduled' or (lock and lock <= datetime.now()):
        return json_error('Veikkaus on jo lukittu tälle ottelulle', 409)
    execute("""INSERT INTO match_predictions (user_id,match_id,pool_id,predicted_home,predicted_away,predicted_winner_on_penalties)
              VALUES (%s,%s,%s,%s,%s,%s) ON DUPLICATE KEY UPDATE predicted_home=VALUES(predicted_home),
              predicted_away=VALUES(predicted_away),predicted_winner_on_penalties=VALUES(predicted_winner_on_penalties)""",
            (g.user['userId'], data['matchId'], data['poolId'], data['predictedHome'], data['predictedAway'], data.get('predictedWinnerOnPenalties', 'none')))
    return jsonify(success=True)


@app.get('/api/predictions/mine')
@auth_required()
def my_predictions():
    pool_id = request.args.get('poolId', type=int)
    if not pool_id:
        return json_error('poolId puuttuu', 400)
    return jsonify(query('SELECT * FROM match_predictions WHERE user_id=%s AND pool_id=%s', (g.user['userId'], pool_id)))


@app.route('/api/predictions/pool/<int:pool_id>/match/<int:match_id>/comparison', methods=['GET', 'OPTIONS'])
def predictions_comparison(pool_id, match_id):
    # CORS headers
    if request.method == 'OPTIONS':
        response = Response()
        response.headers['Access-Control-Allow-Origin'] = request.headers.get('Origin', '*')
        response.headers['Access-Control-Allow-Methods'] = 'GET, OPTIONS'
        response.headers['Access-Control-Allow-Headers'] = 'Content-Type, Authorization'
        return response
    
    # This endpoint also accepts the short-lived access token as a query value
    # so the browser can make a simple cross-origin GET without an OPTIONS request.
    header = request.headers.get('Authorization', '')
    if not header:
        query_token = request.args.get('access_token', '')
        header = f'Bearer {query_token}' if query_token else ''
    if not header.startswith('Bearer '):
        return json_error('Kirjautuminen vaaditaan', 401)
    try:
        user = jwt.decode(header[7:], JWT_ACCESS_SECRET, algorithms=['HS256'])
    except jwt.InvalidTokenError:
        return json_error('Virheellinen tai vanhentunut token', 401)
    
    if not query('SELECT 1 FROM pool_members WHERE pool_id=%s AND user_id=%s', (pool_id, user['userId']), one=True):
        return json_error('Et kuulu tähän porukkaan', 403)
    match = query('SELECT status,home_score_ft,away_score_ft FROM matches WHERE id=%s', (match_id,), one=True)
    if not match:
        return json_error('Ottelua ei löytynyt', 404)
    predictions = query("""SELECT u.id AS user_id,u.display_name,mp.predicted_home,mp.predicted_away,mp.points_awarded
                          FROM match_predictions mp JOIN users u ON u.id=mp.user_id
                          WHERE mp.pool_id=%s AND mp.match_id=%s
                          ORDER BY mp.points_awarded DESC,u.display_name ASC""", (pool_id, match_id))
    return jsonify(match={'status': match['status'], 'home_score_ft': match['home_score_ft'], 'away_score_ft': match['away_score_ft']},
                   predictions=predictions)


@app.put('/api/special-bets')
@auth_required()
def save_special_bet():
    data = request.get_json(silent=True) or {}
    code = data.get('betTypeCode')
    if code not in ('TOP_SCORER', 'BEST_GOALKEEPER', 'TOURNAMENT_WINNER'):
        return json_error('Virheellinen syöte', 400)
    if code == 'TOURNAMENT_WINNER' and not data.get('teamId'):
        return json_error('teamId vaaditaan turnauksen voittajalle', 400)
    if code != 'TOURNAMENT_WINNER' and not data.get('playerId'):
        return json_error('playerId vaaditaan', 400)
    bet_type = query('SELECT id FROM special_bet_types WHERE code=%s', (code,), one=True)
    if not bet_type:
        return json_error('Tuntematon veikkaustyyppi', 404)
    execute("""INSERT INTO special_bets (user_id,pool_id,bet_type_id,player_id,team_id)
              VALUES (%s,%s,%s,%s,%s) ON DUPLICATE KEY UPDATE player_id=VALUES(player_id),team_id=VALUES(team_id)""",
            (g.user['userId'], data['poolId'], bet_type['id'], data.get('playerId'), data.get('teamId')))
    return jsonify(success=True)


@app.get('/api/special-bets/mine')
@auth_required()
def my_special_bets():
    pool_id = request.args.get('poolId', type=int)
    if not pool_id:
        return json_error('poolId puuttuu', 400)
    return jsonify(query("""SELECT sb.*,t.code AS bet_type_code FROM special_bets sb
                          JOIN special_bet_types t ON t.id=sb.bet_type_id
                          WHERE sb.user_id=%s AND sb.pool_id=%s""", (g.user['userId'], pool_id)))


def score_match(match_id):
    match = query('SELECT home_score_ft,away_score_ft,status FROM matches WHERE id=%s', (match_id,), one=True)
    if not match or match['status'] != 'finished' or match['home_score_ft'] is None:
        return
    predictions = query('SELECT id,predicted_home,predicted_away FROM match_predictions WHERE match_id=%s', (match_id,))
    actual = (match['home_score_ft'] > match['away_score_ft']) - (match['home_score_ft'] < match['away_score_ft'])
    for prediction in predictions:
        predicted = (prediction['predicted_home'] > prediction['predicted_away']) - (prediction['predicted_home'] < prediction['predicted_away'])
        points = 5 if (prediction['predicted_home'], prediction['predicted_away']) == (match['home_score_ft'], match['away_score_ft']) else 2 if predicted == actual else 0
        execute('UPDATE match_predictions SET points_awarded=%s WHERE id=%s', (points, prediction['id']))


def sync_world_cup_results(tournament_id):
    tournament = query('SELECT slug FROM tournaments WHERE id=%s', (tournament_id,), one=True)
    if not tournament or tournament['slug'] != 'world-cup-2026':
        return

    response = requests.get(WORLD_CUP_SOURCE, headers={'User-Agent': 'JalkapalloVeikkaus/1.0'}, timeout=20)
    response.raise_for_status()
    source_matches = response.json().get('matches', [])
    stored_matches = query("""SELECT m.id,m.round,m.match_number,ht.name AS home_team_name,
                             at.name AS away_team_name
                      FROM matches m LEFT JOIN teams ht ON ht.id=m.home_team_id
                      LEFT JOIN teams at ON at.id=m.away_team_id
                      WHERE m.tournament_id=%s""", (tournament_id,))
    by_key = {}
    for match in stored_matches:
        key = (match['round'], match['match_number']) if match['match_number'] is not None else (
            match['round'], match['home_team_name'], match['away_team_name'])
        by_key[key] = match

    for source_match in source_matches:
        score = source_match.get('score') or {}
        full_time = score.get('ft')
        if not full_time:
            continue
        key = (source_match.get('round'), source_match.get('num')) if source_match.get('num') is not None else (
            source_match.get('round'), source_match.get('team1'), source_match.get('team2'))
        stored_match = by_key.get(key)
        if not stored_match:
            continue
        execute("""UPDATE matches SET home_score_ft=%s,away_score_ft=%s,home_score_et=%s,
                 away_score_et=%s,home_penalties=%s,away_penalties=%s,status='finished'
                 WHERE id=%s""", (full_time[0], full_time[1],
                                    (score.get('et') or [None, None])[0], (score.get('et') or [None, None])[1],
                                    (score.get('p') or [None, None])[0], (score.get('p') or [None, None])[1],
                                    stored_match['id']))
        score_match(stored_match['id'])


def sync_eurovolley_results(tournament_id):
    ensure_match_set_scores_column()
    response = requests.get(CEV_MATCHES, headers={'User-Agent': 'JalkapalloVeikkaus/1.0'}, timeout=20)
    response.raise_for_status()
    soup = BeautifulSoup(response.text, 'html.parser')
    stored_matches = query("""SELECT m.id,m.round,m.match_number
                      FROM matches m WHERE m.tournament_id=%s""", (tournament_id,))
    by_key = {(match['round'], match['match_number']): match for match in stored_matches}
    seen_match_urls = set()

    for link in soup.select('a[href*="MatchPage.aspx?mID="]'):
        match_url = link.get('href') or ''
        if match_url in seen_match_urls:
            continue
        seen_match_urls.add(match_url)
        match_table = link.find_parent('table')
        text = ' '.join(match_table.stripped_strings) if match_table else link.get_text(' ', strip=True)
        code_match = re.search(r'MF([A-D])-([0-9]{2})', text)
        score_match_result = re.search(r'\b([0-3])\s*-\s*([0-3])\b', text)
        if not code_match or not score_match_result:
            continue
        round_name = f'Pool {code_match.group(1)}'
        match_number = int(code_match.group(2))
        stored_match = by_key.get((round_name, match_number))
        if not stored_match:
            continue
        home_sets, away_sets = (int(score_match_result.group(1)), int(score_match_result.group(2)))
        if home_sets == 0 and away_sets == 0:
            continue
        if match_url.startswith('/'):
            match_url = f'{CEV_HOST}{match_url}'
        elif match_url.startswith('MatchPage.aspx'):
            match_url = f'{CEV_HOST}/Competition-Area/{match_url}'
        elif match_url.startswith('Competition-Area/'):
            match_url = f'{CEV_HOST}/{match_url}'
        set_scores = []
        if match_url.startswith(CEV_HOST):
            details = requests.get(match_url, headers={'User-Agent': 'JalkapalloVeikkaus/1.0'}, timeout=20)
            details.raise_for_status()
            set_scores = parse_cev_set_scores(details.text)
        status = 'finished' if max(home_sets, away_sets) >= 3 else 'live'
        execute("""UPDATE matches SET home_score_ft=%s,away_score_ft=%s,status=%s,match_set_scores=%s
                 WHERE id=%s""", (home_sets, away_sets, status, json.dumps(set_scores) if set_scores else None, stored_match['id']))
        if status == 'finished':
            score_match(stored_match['id'])


@app.put('/api/admin/matches/<int:match_id>/result')
@auth_required('admin')
def admin_match_result(match_id):
    data = request.get_json(silent=True) or {}
    if not isinstance(data.get('homeScoreFt'), int) or not isinstance(data.get('awayScoreFt'), int):
        return json_error('Virheellinen syöte', 400)
    status = data.get('status', 'finished')
    if status not in ('scheduled', 'live', 'finished'):
        return json_error('Virheellinen syöte', 400)
    execute('UPDATE matches SET home_score_ft=%s,away_score_ft=%s,status=%s WHERE id=%s',
            (data['homeScoreFt'], data['awayScoreFt'], status, match_id))
    if status == 'finished':
        score_match(match_id)
    return jsonify(success=True)


@app.put('/api/admin/pools/<int:pool_id>/special-result')
@auth_required('admin')
def admin_special_result(pool_id):
    data = request.get_json(silent=True) or {}
    bet_type = query('SELECT id,points FROM special_bet_types WHERE code=%s', (data.get('betTypeCode'),), one=True)
    if not bet_type:
        return json_error('Virheellinen syöte', 400)
    bets = query('SELECT id,player_id,team_id FROM special_bets WHERE pool_id=%s AND bet_type_id=%s', (pool_id, bet_type['id']))
    for bet in bets:
        correct = ((data.get('correctPlayerId') and bet['player_id'] == data['correctPlayerId']) or
                   (data.get('correctTeamId') and bet['team_id'] == data['correctTeamId']))
        execute('UPDATE special_bets SET points_awarded=%s WHERE id=%s', (bet_type['points'] if correct else 0, bet['id']))
    return jsonify(success=True)


@app.get('/api/pools/<int:pool_id>/insights')
@auth_required()
def pool_insights(pool_id):
    if not query('SELECT 1 FROM pool_members WHERE pool_id=%s AND user_id=%s', (pool_id, g.user['userId']), one=True):
        return json_error('Et kuulu tähän porukkaan', 403)
    rows = query("""SELECT pm.user_id,u.display_name,m.id AS match_id,m.kickoff_at,mp.points_awarded
                   FROM pool_members pm JOIN users u ON u.id=pm.user_id
                   LEFT JOIN match_predictions mp ON mp.pool_id=pm.pool_id AND mp.user_id=pm.user_id
                   LEFT JOIN matches m ON m.id=mp.match_id AND m.status='finished'
                   WHERE pm.pool_id=%s ORDER BY m.kickoff_at ASC,m.id ASC""", (pool_id,))
    members = {}
    dates = {}
    for row in rows:
        member = members.setdefault(row['user_id'], {'userId': row['user_id'], 'displayName': row['display_name'], 'points': 0, 'exact': 0, 'outcomes': 0, 'predictions': 0})
        if row['match_id'] is None:
            continue
        points = int(row['points_awarded'] or 0); member['points'] += points; member['predictions'] += 1
        member['exact'] += points == 5; member['outcomes'] += points == 2
        label = row['kickoff_at'].strftime('%-d.%-m.') if hasattr(row['kickoff_at'], 'strftime') else str(row['kickoff_at'])[:10]
        day = dates.setdefault(label, {'label': label, 'userPoints': 0, 'totalPoints': 0, 'entries': 0})
        day['userPoints'] += points if row['user_id'] == g.user['userId'] else 0; day['totalPoints'] += points; day['entries'] += 1
    stats = list(members.values())
    for member in stats: member['average'] = member['points'] / member['predictions'] if member['predictions'] else 0
    current = next((x for x in stats if x['userId'] == g.user['userId']), {'points': 0, 'exact': 0, 'outcomes': 0, 'predictions': 0, 'average': 0})
    total_matches = len({row['match_id'] for row in rows if row['match_id'] is not None})
    curve = [{'label': x['label'], 'userPoints': x['userPoints'], 'averagePoints': x['totalPoints'] / x['entries'] if x['entries'] else 0} for x in dates.values()]
    return jsonify(current=current, completion=round(current['predictions'] / total_matches * 100) if total_matches else 0,
                   curve=curve)


@app.get('/api/online/volleyball/teams/<path:team_name>')
@auth_required()
def online_team(team_name):
    # CEV-rosterin online-haku pidetään Python-portissa mukana; tietoja ei tallenneta kantaan.
    response = requests.get(CEV_COMPETITION, headers={'User-Agent': 'JalkapalloVeikkaus/1.0'}, timeout=20)
    competition = BeautifulSoup(response.text, 'html.parser')
    team_id = None
    for element in competition.select('[onclick*="CompetitionTeamDetails.aspx"]'):
        match = __import__('re').search(r'TeamID=(\d+)', element.get('onclick', ''))
        if match and team_name.lower() in element.get_text(' ', strip=True).lower():
            team_id = match.group(1); break
    if not team_id:
        return json_error('Joukkuetta ei löytynyt CEV:n kilpailutiedoista', 404)
    profile_url = f'{CEV_HOST}/Competition-Area/CompetitionTeamDetails.aspx?TeamID={team_id}&ID=1572'
    profile_response = requests.get(profile_url, headers={'User-Agent': 'JalkapalloVeikkaus/1.0'}, timeout=20)
    soup = BeautifulSoup(profile_response.text, 'html.parser')
    roster = next((table for table in soup.find_all('table') if 'team roster' in table.get_text(' ', strip=True).lower()), None)
    players = []
    photos = []
    if roster:
        rows = roster.find_all('tr')
        header = next((r for r in rows if '#' in r.get_text() and 'Name' in r.get_text() and 'Position' in r.get_text()), None)
        headers = [x.get_text(' ', strip=True) for x in header.find_all(['th','td'])] if header else []
        headers = headers[:9]
        start = rows.index(header) + 1 if header else 0
        translations = {'Name':'Nimi','Position':'Pelipaikka','Height':'Pituus (cm)','Weight':'Paino (kg)','Birth Year':'Syntymävuosi','Spike reach':'Ulottuvuus iskussa (cm)','Block reach':'Ulottuvuus torjunnassa (cm)','Nationality':'Kansalaisuus','Outside spiker':'Yleispelaaja','Setter':'Passari','Middle blocker':'Keskitorjuja','Opposite':'Hakkuri'}
        headers = [translations.get(x, x) for x in headers]
        for row in rows[start:]:
            cells = [x.get_text(' ', strip=True) for x in row.find_all(['th','td'])]
            cells = [x for x in cells if not x.lower().startswith('license revoked')]
            if len(cells) >= 9 and cells[0].isdigit():
                players.append(cells[:9]); image = row.find('img')
                photos.append(f"/api/online/volleyball/player-photo?url={requests.compat.urljoin(profile_url, image.get('src'))}" if image else '')
    return jsonify(source=profile_url, team=team_name, playerHeaders=headers, players=players, playerPhotos=photos, statistics=[])


@app.get('/api/online/volleyball/player-photo')
def player_photo():
    url = request.args.get('url', '')
    if not url.startswith(f'{CEV_HOST}/Modules/PlayerPhoto/'):
        return 'Kuvaosoitetta ei sallita', 400
    image = requests.get(url, headers={'User-Agent': 'JalkapalloVeikkaus/1.0'}, timeout=20)
    content_type = 'image/png' if image.content[:4] == b'\x89PNG' else 'image/jpeg'
    return Response(image.content, content_type=content_type, headers={'Cache-Control': 'public, max-age=3600'})


if __name__ == '__main__':
    app.run(host='0.0.0.0', port=int(os.getenv('PORT', '3000')))