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

app = Flask(__name__)
DB_NAME = "health_data.db"

def init_db():
    with sqlite3.connect(DB_NAME) as conn:
        cursor = conn.cursor()
        # Added avg_bpm column
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS measurements (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                timestamp DATETIME,
                avg_bpm INTEGER, 
                avg_spo2 REAL,
                avg_temp_f REAL,
                bpm_graph TEXT,
                valid INTEGER,
                reason TEXT
            )
        ''')
        conn.commit()

init_db()

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

# --- 1. UPLOAD (Received from ESP32) ---
@app.route('/api/upload', methods=['POST'])
def upload_data():
    try:
        data = request.json
        
        # Parse Graph to calculate Avg BPM
        bpm_list = data.get('bpm_graph', [])
        
        # --- FIX: Filter out zeros before averaging ---
        valid_bpm_readings = [b for b in bpm_list if b > 0]
        
        avg_bpm = 0
        if len(valid_bpm_readings) > 0:
            avg_bpm = int(sum(valid_bpm_readings) / len(valid_bpm_readings))
        # -----------------------------------------------
        
        bpm_graph_json = json.dumps(bpm_list)
        timestamp = datetime.now().strftime("%Y-%m-%d %H:%M:%S")

        with sqlite3.connect(DB_NAME) as conn:
            cursor = conn.cursor()
            cursor.execute('''
                INSERT INTO measurements (timestamp, avg_bpm, avg_spo2, avg_temp_f, bpm_graph, valid, reason)
                VALUES (?, ?, ?, ?, ?, ?, ?)
            ''', (timestamp, avg_bpm, data.get('avg_spo2', 0), data.get('avg_temp_f', 0), 
                  bpm_graph_json, 1 if data.get('valid') else 0, data.get('reason', '')))
            conn.commit()
            
        return jsonify({"status": "saved"}), 200
    except Exception as e:
        return jsonify({"error": str(e)}), 500

# --- 2. HISTORY LIST (Table Data - Lightweight) ---
@app.route('/api/history', methods=['GET'])
def get_history():
    with sqlite3.connect(DB_NAME) as conn:
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        # specific columns only (no heavy graph data)
        cursor.execute('''
            SELECT id, timestamp, avg_bpm, avg_spo2, avg_temp_f, valid, reason 
            FROM measurements ORDER BY id DESC
        ''')
        rows = cursor.fetchall()
        
        results = []
        for row in rows:
            results.append({
                "id": row['id'],
                "timestamp": row['timestamp'],
                "avg_bpm": row['avg_bpm'],
                "avg_spo2": row['avg_spo2'],
                "avg_temp_f": row['avg_temp_f'],
                "valid": bool(row['valid']),
                "reason": row['reason']
            })
        return jsonify(results)

# --- 3. DETAILS (Modal Data - Heavy) ---
@app.route('/api/details/<int:id>', methods=['GET'])
def get_details(id):
    with sqlite3.connect(DB_NAME) as conn:
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute('SELECT * FROM measurements WHERE id = ?', (id,))
        row = cursor.fetchone()
        
        if row:
            return jsonify({
                "id": row['id'],
                "timestamp": row['timestamp'],
                "avg_bpm": row['avg_bpm'],
                "avg_spo2": row['avg_spo2'],
                "avg_temp_f": row['avg_temp_f'],
                "bpm_graph": json.loads(row['bpm_graph']), # Heavy data
                "valid": bool(row['valid']),
                "reason": row['reason']
            })
        else:
            return jsonify({"error": "Not found"}), 404

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