import mysql.connector
from mysql.connector import pooling
from flask import Flask, request, jsonify  
from flask_socketio import SocketIO
from flask_cors import CORS 
import joblib
import pandas as pd
from datetime import datetime, timedelta
import csv
import os

app = Flask(__name__)

# Mengaktifkan izin CORS untuk semua endpoint HTTP (Termasuk /api/login)
CORS(app, resources={r"/api/*": {"origins": "*"}})

# Optimasi Socket Connection dengan akses CORS penuh
socketio = SocketIO(app, cors_allowed_origins="*", async_mode='threading')

# 1. KONEKSI POOLING DATABASE (Port 3307 XAMPP Lokal Regina)
db_config = {
    "host": "127.0.0.1",
    "port": 3307, 
    "user": "root",
    "password": "",
    "database": "db_monitoring_pompa"
}

try:
    connection_pool = pooling.MySQLConnectionPool(pool_name="mypool", pool_size=5, **db_config)
    print(">>> [DEBUG] Database Connection Pool Created Successfully!")
except mysql.connector.Error as err:
    print(f">>> [ERROR] Gagal inisialisasi Database: {err}")

# 2. LOAD DUA MODEL AI ASLI (.PKL HASIL TRAINING GOOGLE COLAB)
try:
    model_iso = joblib.load('model_kondisi_iso.pkl')
    print(">>> [DEBUG] AI Model Klasifikasi ISO Loaded Successfully")
except Exception as e:
    model_iso = None
    print(">>> [WARNING] model_kondisi_iso.pkl gagal dimuat. Menggunakan simulasi standar.")

try:
    model_ttf = joblib.load('model_time_to_failure.pkl')
    print(">>> [DEBUG] AI Model Time to Failure Loaded Successfully")
except Exception as e:
    model_ttf = None
    print(">>> [WARNING] model_time_to_failure.pkl gagal dimuat. Menggunakan simulasi standar.")


# 3. FUNGSI LOGGING PREDIKSI PINTAR KE CSV
def simpan_ke_csv(nama_node, temp, rms, status_ai, rul_hours):
    nama_file = "log_prediksi_maintenance.csv"
    waktu_sekarang = datetime.now()
    waktu_sekarang_str = waktu_sekarang.strftime('%Y-%m-%d %H:%M:%S')
    
    waktu_kegagalan = waktu_sekarang + timedelta(hours=rul_hours)
    waktu_kegagalan_clean = waktu_kegagalan.replace(minute=0, second=0, microsecond=0)
    waktu_kegagalan_str = waktu_kegagalan_clean.strftime('%Y-%m-%d %H:00')
    
    file_baru = not os.path.exists(nama_file)
    try:
        with open(nama_file, mode='a', newline='') as file:
            writer = csv.writer(file)
            if file_baru:
                writer.writerow([
                    'Timestamp_Prediksi', 'Nama_Alat', 'Suhu_C', 'V_RMS_mms', 
                    'Status_AI', 'Time_To_Failure_Hours', 'Estimasi_Waktu_Gagal'
                ])
            writer.writerow([
                waktu_sekarang_str, nama_node, temp, rms, status_ai,
                round(rul_hours, 2), waktu_kegagalan_str
            ])
    except Exception as e:
        print(f"\n>>> [CSV ERROR] Gagal menulis log: {e}")


# 4. FUNGSI QUERY DATABASE MYSQL DENGAN FILTER STRICT LOGIKA REGINA
def get_data_by_node(nama_node):
    connection = None
    try:
        connection = connection_pool.get_connection()
        cursor = connection.cursor(dictionary=True)
        
        # Pompa_01 KETAT hanya mengambil data kondisi GOOD & SATISFACTORY
        if nama_node == "Pompa_01":
            query = """
                SELECT * FROM tb_sensor_getaran 
                WHERE LOWER(TRIM(Nama_Alat)) = LOWER(TRIM(%s)) 
                AND V_RMS < 2.8
                ORDER BY id DESC LIMIT 1
            """
        # Pompa_02 KETAT hanya mengambil data kondisi UNSATISFACTORY & DANGER
        else:  
            query = """
                SELECT * FROM tb_sensor_getaran 
                WHERE LOWER(TRIM(Nama_Alat)) = LOWER(TRIM(%s)) 
                AND V_RMS >= 2.8
                ORDER BY id DESC LIMIT 1
            """
            
        cursor.execute(query, (nama_node,))
        result = cursor.fetchone()
        return result
    except Exception as err:
        print(f"\n>>> [DATABASE ERROR] Gagal mengambil data untuk {nama_node}: {err}")
        return None
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()


# 5. PIPELINE PROCESSING DATA REAL-TIME & SINKRONISASI 4 KONDISI ISO DINAMIS
def send_realtime_data():
    print(">>> [SYSTEM] Sinkronisasi Data Real-time & Pemrosesan ML Dimulai.")
    while True:
        try:
            payload = []
            nodes = ['Pompa_01', 'Pompa_02'] 
            
            for node_name in nodes:
                row = get_data_by_node(node_name)
                
                if row:
                    val_temp = float(row.get('Suhu', row.get('suhu', 0)))
                    val_rms = float(row.get('V_RMS', row.get('v_rms', 0)))
                    val_hours = float(row.get('operating_hours', row.get('Operating_Hours', 12.0)))
                    
                    waktu_pembacaan = row.get('Waktu', row.get('waktu', '--:--'))
                    if " " in str(waktu_pembacaan):
                        waktu_pembacaan = str(waktu_pembacaan).split(" ")[1]
                    
                    # --- DETEKSI NILAI GETARAN REAL-TIME UNTUK MENGGANTI STATUS KOTAK SECARA DINAMIS ---
                    if val_rms < 0.1:
                        status_ai = "GOOD"
                        rekomendasi_teks = "(OPTIMAL)"
                        sisa_jam = 1000.0 - (val_hours * 0.1)
                        
                    elif val_rms < 1.12:
                        status_ai = "GOOD"
                        rekomendasi_teks = "(OPTIMAL)"
                        sisa_jam = 1000.0 - (val_rms * 250)
                        
                    elif val_rms < 2.8:
                        status_ai = "SATISFACTORY"
                        rekomendasi_teks = "(MONITOR CLOSELY)"
                        sisa_jam = 720.0 - ((val_rms - 1.12) * 328.5)
                        
                    elif val_rms < 7.1:
                        status_ai = "UNSATISFACTORY"
                        rekomendasi_teks = "(REPAIR NOW)"
                        sisa_jam = 168.0 - ((val_rms - 2.8) * 33.4)
                        
                    else:
                        status_ai = "DANGER"
                        rekomendasi_teks = "(UNACCEPTABLE)"
                        sisa_jam = 24.0 - ((val_rms - 7.1) * 1.5)

                    # Batas pengaman absolut agar angka prediksi tidak bernilai minus
                    if sisa_jam < 0: sisa_jam = 0.0

                    # --- 🚀 PROSES KONVERSI JAM KE HARI (1 HARI = 24 JAM) 🚀 ---
                    sisa_hari = sisa_jam / 24.0

                    # Simpan data kombinasi ke file log CSV
                    simpan_ke_csv(node_name, val_temp, val_rms, status_ai, sisa_jam)
                    
                    id_frontend = "1" if node_name == "Pompa_01" else "2"
                    name_frontend = "Pompa 01" if node_name == "Pompa_01" else "Pompa 02"

                    # Pengiriman parameter data terpisah agar komponen Web UI React bisa memetakan posisi teksnya
                    payload.append({
                        "id": id_frontend,
                        "name": name_frontend,
                        "temp": val_temp,
                        "rms": val_rms,
                        "status": str(round(sisa_jam, 2)),          # Tetap mengirim Angka Jam Utama (Contoh: 168.00)
                        "sisa_hari": str(round(sisa_hari, 1)),       # BARU: Mengirim Angka Hari Pelengkap (Contoh: 7.0)
                        "iso_status": status_ai,                    # Teks status utama (GOOD/SATISFACTORY/dst) di kotak hijau
                        "rekomendasi": rekomendasi_teks,            # Teks tindakan dalam kurung di baris bawah kotak hijau
                        "time": str(waktu_pembacaan)
                    })

            if payload:
                socketio.emit('pumps:update', payload)
                print(f"[LIVE] {datetime.now().strftime('%H:%M:%S')} - Data Sukses Dikirim ke Web Dashboard", end='\r')
                
        except Exception as e:
            print(f"\n>>> [CRITICAL ERROR PIPELINE]: {e}")
        
        socketio.sleep(2)


# 6. ENDPOINT LOGIN DASHBOARD
@app.route('/api/login', methods=['POST'])
def login_teknisi():
    try:
        data = request.get_json()
        email_input = data.get('username')
        password_input = data.get('password')
        
        if email_input == "teknisi@pompa.com" and password_input == "teknisi123":
            return jsonify({
                "success": True, "status": "success", "message": "Login Berhasil!",
                "user": {"role": "Teknisi", "email": email_input}
            }), 200
        else:
            return jsonify({"success": False, "status": "error", "message": "Email/Password Salah!"}), 401
    except Exception as e:
        return jsonify({"success": False, "status": "error", "message": str(e)}), 500


@socketio.on('connect')
def handle_connect():
    print("\n>>> [INFO] Dashboard Web Terhubung ke Backend!")
    if not hasattr(handle_connect, "thread_started"):
        socketio.start_background_task(send_realtime_data)
        handle_connect.thread_started = True


if __name__ == '__main__':
    socketio.run(app, host='0.0.0.0', port=5001, debug=False, use_reloader=False)