from flask import Flask, jsonify, request, make_response
import os

from flask_cors import CORS
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate
import logging
import pandas as pd
from datetime import datetime
import json
from .utils import get_current_data, get_dataset_by_id, get_table_data, get_table_metal_averages

# 先创建SQLAlchemy实例，确保其他模块可以导入
db = SQLAlchemy()

# 导入配置和工具函数（在db定义之后）
from . import config


def create_app():
    app = Flask(__name__)
    
    # 配置应用
    app.config['SECRET_KEY'] = 'abcdef1234567890'
    app.config.from_object(config.Config)
    app.logger.setLevel(logging.DEBUG)
    
    # 初始化CORS（允许所有源，所有方法，带凭证）
    CORS(app, resources={r"/*": {"origins": "*"}}, supports_credentials=True)
    
    # 初始化数据库
    db.init_app(app)
    
    # 初始化迁移工具
    migrate = Migrate(app, db)
    
    # 注册蓝图
    from . import routes, frontend
    app.register_blueprint(routes.bp)
    app.register_blueprint(frontend.bp,url_prefix="/admin")

    # 注册自定义接口
    register_api_routes(app)

    # -------- 新增：统一处理 OPTIONS 预检请求 --------
    @app.before_request
    def handle_options():
        if request.method == "OPTIONS":
            response = make_response()
            response.headers.add("Access-Control-Allow-Origin", "*")
            response.headers.add("Access-Control-Allow-Headers", "Content-Type,Authorization")
            response.headers.add("Access-Control-Allow-Methods", "GET,POST,PUT,DELETE,OPTIONS")
            return response
    # --------------------------------------------

    return app


def safe_data_conversion(df):
    """安全转换DataFrame为可序列化的字典列表，处理特殊数据类型"""
    # 处理日期时间类型
    for col in df.columns:
        if pd.api.types.is_datetime64_any_dtype(df[col]):
            df[col] = df[col].dt.strftime('%Y-%m-%d %H:%M:%S')
    
    # 处理空值
    df = df.fillna('')
    
    # 转换为字典列表
    data_list = df.to_dict(orient='records')
    
    # 处理可能的嵌套结构
    def serialize_item(item):
        if isinstance(item, dict):
            return {k: serialize_item(v) for k, v in item.items()}
        elif isinstance(item, list):
            return [serialize_item(v) for v in item]
        elif isinstance(item, datetime):
            return item.strftime('%Y-%m-%d %H:%M:%S')
        else:
            return item
    
    return [serialize_item(item) for item in data_list]


def register_api_routes(app):
    # 新增：通用表查询接口
    @app.route('/api/table-data', methods=['GET'])
    def api_table_data():
        table_name = request.args.get('table_name')
        if not table_name:
            return jsonify({"error": "请传入 table_name 参数（如 ?table_name=dataset_35）"}), 400

        try:
            df = get_table_data(db.session, table_name)
            data = safe_data_conversion(df)
            return jsonify({"success": True, "data": data})
        except ValueError as e:
            return jsonify({"success": False, "error": str(e)}), 400
        except Exception as e:
            app.logger.error(f"查询表 {table_name} 失败: {str(e)}", exc_info=True)
            return jsonify({"success": False, "error": str(e)}), 500
        
    # 新增接口：查询金属平均值（/api/table-averages）
    @app.route('/api/table-averages', methods=['GET'])
    def api_table_averages():
        table_name = request.args.get('table_name')
        if not table_name:
            return jsonify({"error": "缺少参数：table_name"}), 400

        try:
            averages = get_table_metal_averages(db.session, table_name)
            return jsonify({"success": True, "averages": averages})
        except ValueError as e:
            return jsonify({"success": False, "error": str(e)}), 400
        except Exception as e:
            app.logger.error(f"计算 {table_name} 平均值失败: {str(e)}", exc_info=True)
            return jsonify({"success": False, "error": str(e)}), 500