from flask import Flask, render_template, request, redirect, url_for, send_from_directory, Response
import json
import requests
import pymysql 
import sys

app = Flask(__name__)

# Define field lists to avoid repetition
LASENA_FIELDS = [
    "vchod", "vchod_topi", "obyvak", "obyvak_topi", "loznice", "loznice_topi",
    "pokoj_predni", "pokoj_predni_topi", "koupelna", "koupelna_topi", 
    "pokoj_zadni", "pokoj_zadni_topi", "venku", "rezim", 
    "garaz", "pressure", "full", "bazen", "bazen_cache", "note"
]

LEDTECH_FIELDS = [
    "indoor_temp", "indoor_pressure", "indoor_humidity", "indoor_moisture", 
    "indoor_light", "indoor_gas", "outdoor_temp", "outdoor_pressure", 
    "outdoor_humidity", "outdoor_rain", "cpu_temp", "cpu_load", "memory", 
    "hdd", "updated", "outdoor_image_encoded", "indoor_image_encoded"
]

@app.route("/", methods = ['GET', 'POST', 'PUT'])
def index():

    try:
        conn = pymysql .connect(
            user="michaldb",
            password="JakSeTiDari12",
            host="127.0.0.1",
            port=3306,
            database="skoula"

        )
    except pymysql .Error as e:
        print(f"Error connecting to MariaDB Platform: {e}")
        sys.exit(1)

    # Get Cursor
    cur = conn.cursor(pymysql.cursors.DictCursor)

    args = request.args
    args = args.to_dict()

    # LASENA ----------------------------------------
    if args.get("what") == "lasena":

        # GET latest row
        if request.method == 'GET':
            cur.execute(
                "SELECT * FROM lasena ORDER BY id DESC LIMIT 1"
            )
            result = cur.fetchone()
            return Response(json.dumps( result, indent=4, sort_keys=True, default=str), mimetype='application/json')
            
        # INSERT new row
        elif request.method == 'POST':
            result = request.get_json(force=True)

            # in this post request, bazen is not available
            # bazen_cache is updated directly from Pico using PUT method
            # and we will load latest bazen_cache (not older than 2 hours) and put it into bazen field
            if result.get('bazen') is None and result.get('bazen_cache') is None:
                cur.execute("SELECT bazen_cache FROM lasena WHERE bazen_cache IS NOT NULL AND time >= NOW() - INTERVAL 2 HOUR ORDER BY id DESC LIMIT 1")
                latest_bazen = cur.fetchone()
                if latest_bazen:
                    result['bazen'] = latest_bazen['bazen_cache']

            # Build SQL with field names
            field_names = ', '.join(LASENA_FIELDS)
            placeholders = ', '.join(['%s'] * len(LASENA_FIELDS))
            
            sql = f"""
            INSERT INTO lasena 
            ({field_names})
            VALUES 
            ({placeholders})
            """

            # Build values tuple from field list, using None for missing fields
            val = tuple(result.get(field) for field in LASENA_FIELDS)
            
            cur.execute(sql, val)
            conn.commit()
            conn.commit()

            return Response(json.dumps( {} ), mimetype='application/json')
            
        # UPDATE latest row
        elif request.method == 'PUT':
            result = request.get_json(force=True)

            # First get the latest row ID
            cur.execute("SELECT id FROM lasena ORDER BY id DESC LIMIT 1")
            latest_row = cur.fetchone()
            
            if not latest_row:
                return Response(json.dumps({"error": "No records found to update"}), mimetype='application/json', status=404)
            
            latest_id = latest_row["id"]

            # Build dynamic SQL query based on provided fields
            update_fields = []
            values = []
            
            for field in LASENA_FIELDS:
                if field in result:
                    update_fields.append(f"{field} = %s")
                    values.append(result[field])
            
            if not update_fields:
                return Response(json.dumps({"error": "No valid fields provided for update"}), mimetype='application/json', status=400)
            
            # Update only the latest row
            sql = f"UPDATE lasena SET {', '.join(update_fields)} WHERE id = %s"
            values.append(latest_id)
            
            cur.execute(sql, values)
            conn.commit()

            return Response(json.dumps({"message": "Record updated successfully", "id": latest_id, "updated_fields": list(result.keys()), "rows_affected": 1}), mimetype='application/json')

    # LEDTECH ----------------------------------------
    elif args.get("what") == "ledtech":

        if request.method == 'GET':
            # Get latest record
            cur.execute(
                "SELECT * FROM ledtech ORDER BY id DESC LIMIT 1"
            )
            result = cur.fetchone()
            
            # Get temperature history for last 24 hours
            cur.execute("""
                SELECT indoor_temp, outdoor_temp, updated 
                FROM ledtech 
                WHERE updated >= NOW() - INTERVAL 24 HOUR 
                ORDER BY updated ASC
            """)
            history = cur.fetchall()
            
            # Add history to the result
            result['history'] = history
            
            return Response(json.dumps( result, indent=4, sort_keys=True, default=str), mimetype='application/json')
            
        elif request.method == 'POST':
            result = request.get_json(force=True)

            delete_old_images_sql = """
            UPDATE ledtech
            SET outdoor_image_encoded = NULL, indoor_image_encoded = NULL
            WHERE outdoor_image_encoded IS NOT NULL OR indoor_image_encoded IS NOT NULL
            """
            cur.execute(delete_old_images_sql)

            # Build SQL with field names
            field_names = ', '.join(LEDTECH_FIELDS)
            placeholders = ', '.join(['%s'] * len(LEDTECH_FIELDS))
            
            sql = f"""
            INSERT INTO ledtech 
            ({field_names})
            VALUES 
            ({placeholders})
            """

            # Build values tuple from field list
            val = tuple(result.get(field) for field in LEDTECH_FIELDS)
            
            cur.execute(sql, val)
            conn.commit()
            conn.commit()

            return Response(json.dumps( {} ), mimetype='application/json')
    
    # DIESEL ----------------------------------------------------------
    elif args.get("what") == "diesel":
        
        if request.method == 'GET':
            cur.execute(
                "SELECT * FROM diesel ORDER BY id DESC LIMIT 1"
            )
            result = cur.fetchone()
            return Response(json.dumps( result, indent=4, sort_keys=True, default=str), mimetype='application/json')