from flask import Flask, render_template, request, jsonify
import sqlite3
import json

app = Flask(__name__)

# 汇率缓存（前端传入后保存在内存）
exchange_rate = 7.2  # 默认汇率

def get_db():
    """获取数据库连接"""
    conn = sqlite3.connect('buff_prices.db')
    conn.row_factory = sqlite3.Row
    return conn

@app.route('/')
def index():
    """首页"""
    return render_template('index.html')

@app.route('/api/products')
def get_products():
    """获取商品列表，支持排序和搜索"""
    conn = get_db()
    cursor = conn.cursor()
    
    # 获取参数
    sort_by = request.args.get('sort', 'updated_at')
    sort_order = request.args.get('order', 'desc')
    search = request.args.get('search', '')
    page = int(request.args.get('page', 1))
    per_page = int(request.args.get('per_page', 50))
    
    # 构建搜索条件
    where_clause = ""
    params = []
    if search:
        where_clause = "WHERE p.market_hash_name LIKE ? OR p.name LIKE ?"
        search_pattern = f"%{search}%"
        params = [search_pattern, search_pattern]
    
    # 构建排序
    order_clause = "ORDER BY "
    if sort_by == 'buff_price':
        order_clause += "buff_price"
    elif sort_by == 'steam_price':
        order_clause += "steam_price"
    elif sort_by == 'csfloat_price':
        order_clause += "csfloat_price"
    elif sort_by == 'dmarket_price':
        order_clause += "dmarket_price"
    else:
        order_clause += "p.updated_at"
    
    order_clause += f" {sort_order}"
    
    # 获取总数
    count_query = f"SELECT COUNT(*) as total FROM products p {where_clause}"
    cursor.execute(count_query, params)
    total = cursor.fetchone()['total']
    
    # 分页查询
    offset = (page - 1) * per_page
    query = f'''
        SELECT 
            p.id,
            p.buff_id,
            p.name,
            p.market_hash_name,
            p.type,
            p.rarity,
            p.icon_url,
            p.updated_at,
            pp_buff.min_price as buff_price,
            pp_buff.currency as buff_currency,
            pp_steam.min_price as steam_price,
            pp_steam.currency as steam_currency,
            pp_csfloat.min_price as csfloat_price,
            pp_csfloat.currency as csfloat_currency,
            pp_dmarket.min_price as dmarket_price,
            pp_dmarket.currency as dmarket_currency
        FROM products p
        LEFT JOIN platform_prices pp_buff ON p.id = pp_buff.product_id AND pp_buff.platform_code = 'buff'
        LEFT JOIN platform_prices pp_steam ON p.id = pp_steam.product_id AND pp_steam.platform_code = 'steam'
        LEFT JOIN platform_prices pp_csfloat ON p.id = pp_csfloat.product_id AND pp_csfloat.platform_code = 'csfloat'
        LEFT JOIN platform_prices pp_dmarket ON p.id = pp_dmarket.product_id AND pp_dmarket.platform_code = 'dmarket'
        {where_clause}
        {order_clause}
        LIMIT ? OFFSET ?
    '''
    
    params.extend([per_page, offset])
    cursor.execute(query, params)
    products = cursor.fetchall()
    conn.close()
    
    # 转换为字典列表并应用汇率
    result = []
    for p in products:
        product_dict = dict(p)
        
        # 应用汇率换算
        if product_dict.get('buff_currency') == 'USD' and product_dict.get('buff_price'):
            product_dict['buff_price_cny'] = round(product_dict['buff_price'] * exchange_rate, 2)
        else:
            product_dict['buff_price_cny'] = product_dict.get('buff_price')
        
        if product_dict.get('steam_currency') == 'USD' and product_dict.get('steam_price'):
            product_dict['steam_price_cny'] = round(product_dict['steam_price'] * exchange_rate, 2)
        else:
            product_dict['steam_price_cny'] = product_dict.get('steam_price')
        
        if product_dict.get('csfloat_currency') == 'USD' and product_dict.get('csfloat_price'):
            product_dict['csfloat_price_cny'] = round(product_dict['csfloat_price'] * exchange_rate, 2)
        else:
            product_dict['csfloat_price_cny'] = product_dict.get('csfloat_price')
        
        if product_dict.get('dmarket_currency') == 'USD' and product_dict.get('dmarket_price'):
            product_dict['dmarket_price_cny'] = round(product_dict['dmarket_price'] * exchange_rate, 2)
        else:
            product_dict['dmarket_price_cny'] = product_dict.get('dmarket_price')
        
        result.append(product_dict)
    
    return jsonify({
        'products': result,
        'total': total,
        'page': page,
        'per_page': per_page,
        'total_pages': (total + per_page - 1) // per_page,
        'exchange_rate': exchange_rate
    })

@app.route('/api/set_exchange_rate', methods=['POST'])
def set_exchange_rate():
    """设置汇率"""
    global exchange_rate
    data = request.get_json()
    exchange_rate = float(data.get('rate', 7.2))
    return jsonify({'success': True, 'rate': exchange_rate})

@app.route('/api/statistics')
def get_statistics():
    """获取统计信息"""
    conn = get_db()
    cursor = conn.cursor()
    
    # 商品总数
    cursor.execute('SELECT COUNT(*) as total FROM products')
    total_products = cursor.fetchone()['total']
    
    # 各平台价格数量
    stats = {'total_products': total_products}
    for platform in ['buff', 'steam', 'csfloat', 'dmarket']:
        cursor.execute('''
            SELECT COUNT(DISTINCT product_id) as count 
            FROM platform_prices 
            WHERE platform_code = ?
        ''', (platform,))
        stats[f'{platform}_count'] = cursor.fetchone()['count']
    
    conn.close()
    return jsonify(stats)

if __name__ == '__main__':
    app.run(debug=True, host='0.0.0.0', port=5001)