import pymysql
from flask import Flask, render_template, request, redirect, url_for, flash, send_file, session, Response
from dotenv import load_dotenv
import os
import pandas as pd
from io import BytesIO
import cv2
import threading
import time
from datetime import datetime
from flask import send_from_directory,jsonify
from flask_socketio import SocketIO
import numpy as np

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

# 初始化Flask应用
app = Flask(__name__)
# 加上这两行，模板永远自动更新
app.config['TEMPLATES_AUTO_RELOAD'] = True
app.config['SEND_FILE_MAX_AGE_DEFAULT'] = 0
app.secret_key = "deva_secret_key_2026"  # 自定义密钥
# 自定义登录口令（替换为你的密码）
LOGIN_PASSWORD = "your_pass01"
# MySQL配置
DB_CONFIG = {
    "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", "device_db"),
    "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

# ---------------------- SocketIO初始化 ----------------------
socketio = SocketIO(app, cors_allowed_origins="*")

# ---------------------- 摄像头配置（适配USB/手机/IP摄像头） ----------------------
CAMERA_CONFIG = {
    # USB摄像头（Dell笔记本）：url=1（内置=0，USB=1）
 "usb_cam": {
        "url": "rtmp://127.0.0.1:8082/live/usb_cam",
        "name": "Dell USB摄像头",
        "status": "online"
    },
    # 手机摄像头（替换为你的内网穿透地址）
    "phone_cam": {
        "url": "rtmp://127.0.0.1:8082/live/usb_cam",
        "name": "荣耀手机摄像头",
        "status": "online"
    },
    # IP摄像头（可选）
    "ip_cam": {
        "url": "rtsp://admin:password@192.168.1.100:554/stream1",
        "name": "客厅IP摄像头",
        "status": "online"
    },
}

# ---------------------- 存储配置 ----------------------
CAPTURE_DIR = os.path.join(app.root_path, "static", "captures")
VIDEO_DIR = os.path.join(app.root_path, "static", "videos")
os.makedirs(CAPTURE_DIR, exist_ok=True)
os.makedirs(VIDEO_DIR, exist_ok=True)

# 视频帧缓存（线程安全）
frame_buffers = {cam_id: None for cam_id in CAMERA_CONFIG.keys()}
capture_threads = {}

# ---------------------- 数据库基础操作 ----------------------
def get_db_connection():
    """创建数据库连接"""
    try:
        conn = pymysql.connect(**DB_CONFIG)
        return conn
    except Exception as e:
        print(f"数据库连接失败：{e}")
        return None

def init_monitor_tables():
    """初始化监控记录表（首次运行创建）"""
    conn = get_db_connection()
    if not conn:
        return
    cursor = conn.cursor()
    # 抓拍记录表
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS monitor_captures (
            id INT AUTO_INCREMENT PRIMARY KEY,
            cam_id VARCHAR(50) NOT NULL,
            cam_name VARCHAR(100) NOT NULL,
            capture_time DATETIME NOT NULL,
            capture_type ENUM('timing', 'alarm', 'manual') NOT NULL,
            file_path VARCHAR(255) NOT NULL,
            description VARCHAR(255) DEFAULT ''
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    """)
    # 录像记录表
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS monitor_videos (
            id INT AUTO_INCREMENT PRIMARY KEY,
            cam_id VARCHAR(50) NOT NULL,
            cam_name VARCHAR(100) NOT NULL,
            start_time DATETIME NOT NULL,
            end_time DATETIME NOT NULL,
            video_type ENUM('timing', 'alarm', 'manual') NOT NULL,
            file_path VARCHAR(255) NOT NULL,
            duration INT DEFAULT 0,
            file_size BIGINT DEFAULT 0
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    """)
    # 设备信息表（兼容原有逻辑）
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS device_info (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
            size VARCHAR(50) DEFAULT ''
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    """)
    conn.commit()
    cursor.close()
    conn.close()

# 初始化数据表
init_monitor_tables()

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
    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

# ---------------------- 实时视频流核心函数 ----------------------
def capture_frames(cam_id, cam_url):
    """后台线程：持续抓取摄像头帧"""
    # 处理USB摄像头（url为数字）
    if isinstance(cam_url, int):
        # 服务器是Linux，移除Windows专用的CAP_DSHOW
        cap = cv2.VideoCapture(cam_url)
    else:
        cap = cv2.VideoCapture(cam_url)

    cap.set(cv2.CAP_PROP_BUFFERSIZE, 1)  # 减少缓存延迟
    cap.set(cv2.CAP_PROP_FRAME_WIDTH, 640)
    cap.set(cv2.CAP_PROP_FRAME_HEIGHT, 480)

    while True:
        ret, frame = cap.read()
        if not ret:
            frame_buffers[cam_id] = None
            CAMERA_CONFIG[cam_id]["status"] = "offline"
            time.sleep(5)
            # 重连摄像头
            if isinstance(cam_url, int):
                cap = cv2.VideoCapture(cam_url)
            else:
                cap = cv2.VideoCapture(cam_url)
            continue

        # 压缩帧，降低带宽
        ret, buffer = cv2.imencode('.jpg', frame, [cv2.IMWRITE_JPEG_QUALITY, 70])
        frame_buffers[cam_id] = buffer.tobytes()
        CAMERA_CONFIG[cam_id]["status"] = "online"
        time.sleep(0.05)  # 20帧/秒

def generate_frames(cam_id):
    """生成MJPEG流，供浏览器播放"""
    # 生成离线占位图（如果不存在则创建）
    offline_img_path = os.path.join(app.root_path, "templates", "offline.jpg")
    if not os.path.exists(offline_img_path):
        # 创建默认离线图
        offline_frame = cv2.imencode('.jpg', cv2.putText(
            np.zeros((480, 640, 3), dtype=np.uint8),
            "摄像头离线", (200, 240), cv2.FONT_HERSHEY_SIMPLEX,
            1, (0, 0, 255), 2
        ), [cv2.IMWRITE_JPEG_QUALITY, 70])[1].tobytes()
        with open(offline_img_path, "wb") as f:
            f.write(offline_frame)

    while True:
        frame = frame_buffers.get(cam_id)
        if frame is None:
            with open(offline_img_path, "rb") as f:
                frame = f.read()
        yield (b'--frame\r\n'
               b'Content-Type: image/jpeg\r\n\r\n' + frame + b'\r\n')

# ---------------------- 抓拍/录像功能 ----------------------
def capture_image(cam_id, capture_type="timing", description=""):
    """抓拍图片并保存到数据库"""
    if cam_id not in CAMERA_CONFIG:
        return False, "摄像头不存在"
    frame = frame_buffers.get(cam_id)
    if frame is None:
        return False, "摄像头离线"

    # 生成文件名
    timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
    filename = f"{cam_id}_{capture_type}_{timestamp}.jpg"
    file_path = os.path.join(CAPTURE_DIR, filename)

    # 保存图片
    with open(file_path, "wb") as f:
        f.write(frame)

    # 保存到数据库
    conn = get_db_connection()
    if conn:
        cursor = conn.cursor()
        cursor.execute("""
            INSERT INTO monitor_captures 
            (cam_id, cam_name, capture_time, capture_type, file_path, description)
            VALUES (%s, %s, %s, %s, %s, %s)
        """, (
            cam_id, CAMERA_CONFIG[cam_id]["name"],
            datetime.now(), capture_type, file_path, description
        ))
        conn.commit()
        cursor.close()
        conn.close()
    return True, f"抓拍成功：{filename}"

def timing_capture_task():
    """定时抓拍（每5分钟）"""
    while True:
        for cam_id in CAMERA_CONFIG.keys():
            capture_image(cam_id, "timing", "定时抓拍")
        time.sleep(300)  # 5分钟



# 录像存储路径
BASE_PATH = "/home/deva/video_data"
CAPTURE_PATH = os.path.join(BASE_PATH, "capture")
RECORD_PATH = os.path.join(BASE_PATH, "record")

# ====================== 查看抓拍记录 ======================
@app.route("/captures")
def capture_list():
    files = []
    for f in os.listdir(CAPTURE_PATH):
        path = os.path.join(CAPTURE_PATH, f)
        ctime = os.path.getctime(path)
        files.append({
            "name": f,
            "time": time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(ctime)),
            "url": f"/capture/{f}"
        })
    return jsonify(files)

# 浏览抓拍图片
@app.route("/capture/<filename>")
def get_capture(filename):
    return send_from_directory(CAPTURE_PATH, filename)

# ====================== 查看录像列表 ======================
@app.route("/records")
def record_list():
    files = []
    for f in os.listdir(RECORD_PATH):
        path = os.path.join(RECORD_PATH, f)
        ctime = os.path.getctime(path)
        size = os.path.getsize(path) // 1024 // 1024
        files.append({
            "name": f,
            "time": time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(ctime)),
            "size": f"{size}MB",
            "url": f"/record/{f}"
        })
    return jsonify(files)

# 播放/下载录像
@app.route("/record/<filename>")
def get_record(filename):
    return send_from_directory(RECORD_PATH, filename)       

# ---------------------- Flask路由 ----------------------
@app.route('/login', methods=['GET', 'POST'])
def login():
    """登录页面"""
    if request.method == 'POST':
        password = request.form.get("password")
        if password == LOGIN_PASSWORD:
            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"))

# ✅ 修复：恢复首页路由，确保传递cameras参数
@app.route('/')
@login_required
def index():
    """首页：设备数据+监控画面"""
    data = query_all_data()
    cameras = CAMERA_CONFIG  # 关键：传递摄像头配置到模板
    return render_template('index.html', data=data, cameras=cameras)

# ✅ 修复：恢复导出路由装饰器
@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=f"家用设备数据_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx"
    )

@app.route('/video_feed/<cam_id>')
@login_required
def video_feed(cam_id):
    """视频流接口"""
    if cam_id not in CAMERA_CONFIG:
        return "摄像头不存在", 404
    return Response(generate_frames(cam_id),
                    mimetype='multipart/x-mixed-replace; boundary=frame')

@app.route('/manual_capture/<cam_id>', methods=['POST'])
@login_required
def manual_capture(cam_id):
    """手动抓拍"""
    success, msg = capture_image(cam_id, "manual", "手动抓拍")
    flash(msg, "success" if success else "error")
    return redirect(url_for('index'))

# ---------------------- 启动服务 ----------------------
if __name__ == '__main__':
    # 启动摄像头抓取线程
    for cam_id, config in CAMERA_CONFIG.items():
        t = threading.Thread(target=capture_frames, args=(cam_id, config["url"]), daemon=True)
        capture_threads[cam_id] = t
        t.start()

    # 启动定时抓拍线程
    timing_thread = threading.Thread(target=timing_capture_task, daemon=True)
    timing_thread.start()

    # 启动Flask服务（生产环境建议用gunicorn+gevent）
    app.run(host='0.0.0.0', port=5000, debug=False)

    from flask import render_template, jsonify, send_from_directory
import os
import time

# 录像存储路径
BASE_PATH = "/video_data"
CAPTURE_PATH = os.path.join(BASE_PATH, "capture")
RECORD_PATH = os.path.join(BASE_PATH, "record")

os.makedirs(CAPTURE_PATH, exist_ok=True)
os.makedirs(RECORD_PATH, exist_ok=True)

# 监控首页
@app.route('/')
def index():
    return render_template('dashboard.html')

# 抓拍列表接口
@app.route('/captures')
def capture_list():
    files = []
    try:
        for f in os.listdir(CAPTURE_PATH):
            p = os.path.join(CAPTURE_PATH, f)
            ctime = os.path.getctime(p)
            files.append({
                "name": f,
                "time": time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(ctime)),
                "url": f"/capture/{f}"
            })
    except: pass
    files.sort(key=lambda x: x["time"], reverse=True)
    return jsonify(files)

# 查看抓拍
@app.route('/capture/<filename>')
def get_capture(filename):
    return send_from_directory(CAPTURE_PATH, filename)

# 录像列表接口
@app.route('/records')
def record_list():
    files = []
    try:
        for f in os.listdir(RECORD_PATH):
            p = os.path.join(RECORD_PATH, f)
            ctime = os.path.getctime(p)
            size = os.path.getsize(p) // 1048576
            files.append({
                "name": f,
                "time": time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(ctime)),
                "size": f"{size}MB",
                "url": f"/record/{f}"
            })
    except: pass
    files.sort(key=lambda x: x["time"], reverse=True)
    return jsonify(files)

# 播放/下载录像
@app.route('/record/<filename>')
def get_record(filename):
    return send_from_directory(RECORD_PATH, filename)