import pymysql
from flask import Flask, render_template, request, redirect, url_for, flash, send_file, session
from dotenv import load_dotenv
import os
import pandas as pd
from io import BytesIO

# 加载环境配置（服务器端创建dev.env，和app.py同目录）
load_dotenv("dev.env")

# 初始化Flask应用
app = Flask(__name__)
app.secret_key = "deva_secret_key_2026"  # 自定义密钥
# 自定义登录口令（你可以改成自己的）
LOGIN_PASSWORD = "your_pass01"  # 替换为你的登录密码
# MySQL配置
DB_CONFIG = {
    "host": os.getenv("DB_HOST"),
    "port": int(os.getenv("DB_PORT", 3306)),
    "user": os.getenv("DB_USER"),
    "password": os.getenv("DB_PASSWORD"),
    "database": os.getenv("DB_NAME"),
    "charset": "utf8mb4"
}

# ---------------------- 登录验证装饰器 ----------------------
def login_required(f):
    """装饰器：验证是否登录"""
    def wrapper(*args, **kwargs):
        if not session.get("logged_in"):
            flash("请先登录！", "error")
            return redirect(url_for("login"))
        return f(*args, **kwargs)
    wrapper.__name__ = f.__name__
    return wrapper
# ---------------------- 数据库操作 ----------------------
def get_db_connection():
    """创建数据库连接"""
    try:
        conn = pymysql.connect(**DB_CONFIG)
        return conn
    except Exception as e:
        print(f"数据库连接失败：{e}")
        return None


def query_all_data():
    """查询所有设备数据"""
    conn = get_db_connection()
    if not conn:
        return []
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    try:
        cursor.execute("SELECT * FROM device_info ORDER BY create_time DESC")
        data = cursor.fetchall()
        return data
    except Exception as e:
        print(f"查询失败：{e}")
        return []
    finally:
        cursor.close()
        conn.close()


def export_data_to_excel():
    """将数据导出为Excel（内存生成，不落地）"""
    data = query_all_data()
    if not data:
        return None

    # 转换为DataFrame
    df = pd.DataFrame(data)
    # 调整列名（可选）
    df.rename(columns={
        "id": "ID",
        "name": "设备名称",
        "create_time": "记录时间",
        "size": "尺寸/规格"
    }, inplace=True)

    # 写入内存缓冲区
    output = BytesIO()
    with pd.ExcelWriter(output, engine="openpyxl") as writer:
        df.to_excel(writer, sheet_name="设备数据", index=False)
    output.seek(0)  # 重置指针到开头
    return output


# ---------------------- Flask路由 ----------------------
# @app.route('/')
# def index():
#     """首页：展示数据+导出按钮"""
#     data = query_all_data()
#     return render_template('index.html', data=data)
#
#
# @app.route('/export')
# def export():
#     """导出Excel"""
#     excel_data = export_data_to_excel()
#     if not excel_data:
#         flash("暂无数据可导出！", "error")
#         return redirect(url_for('index'))
#
#     # 发送Excel文件到浏览器
#     return send_file(
#         excel_data,
#         mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
#         as_attachment=True,
#         download_name="家用设备数据_" + pd.Timestamp.now().strftime("%Y%m%d_%H%M%S") + ".xlsx"
#     )
@app.route('/login', methods=['GET', 'POST'])
def login():
    """登录页面"""
    if request.method == 'POST':
        password = request.form.get("password")
        if password == LOGIN_PASSWORD:
            # 登录成功，设置session
            session["logged_in"] = True
            return redirect(url_for("index"))
        else:
            flash("密码错误！请重新输入", "error")
    return render_template('login.html')

@app.route('/logout')
def logout():
    """退出登录"""
    session.pop("logged_in", None)
    flash("已成功退出登录", "success")
    return redirect(url_for("login"))

@app.route('/')
@login_required  # 需要登录才能访问首页
def index():
    """首页：展示数据+导出按钮"""
    data = query_all_data()
    return render_template('index.html', data=data)

@app.route('/export')
@login_required  # 需要登录才能导出数据
def export():
    """导出Excel"""
    excel_data = export_data_to_excel()
    if not excel_data:
        flash("暂无数据可导出！", "error")
        return redirect(url_for('index'))
    return send_file(
        excel_data,
        mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        as_attachment=True,
        download_name="家用设备数据_" + pd.Timestamp.now().strftime("%Y%m%d_%H%M%S") + ".xlsx"
    )
    CAMERA_CONFIG = {
    "camera1": {
        "url": "http://192.168.226.200:38490/video",  # 👈 就是这个
        "name": "手机摄像头",
        "status": "online"
    }
}

# ---------------------- 启动服务 ----------------------
if __name__ == '__main__':
    # 允许外网访问，端口80（默认网页端口）
    app.run(host='0.0.0.0', port=5000, debug=False)  # 生产环境关闭debug