from flask import Flask, request, jsonify
import numpy as np
import cv2
import os
from ultralytics import YOLO
import tflite_runtime.interpreter as tflite
import mysql.connector
from mysql.connector import Error
from datetime import datetime
from collections import defaultdict
from flask_cors import CORS

app = Flask(__name__)
CORS(app)

# ===== LOAD MODEL =====
yolo = YOLO("best.pt")
interpreter = tflite.Interpreter(model_path="model_telur_cnn.tflite")
interpreter.allocate_tensors()

input_details  = interpreter.get_input_details()
output_details = interpreter.get_output_details()

labels = ['A', 'B', 'C', 'TL']

# ===== KONFIGURASI DB =====
DB_CONFIG = {
    "host":     "10.10.1.112",
    "user":     "admin",
    "password": "Telur@12344321",
    "database": "klasifikasi_telur",
    "init_command": "SET time_zone = '+07:00'"
}

def get_db():
    return mysql.connector.connect(**DB_CONFIG)

# ===== HELPER =====
def map_grade(g: str) -> str:
    if not g:
        return "TL"
    g = g.strip().upper()
    if g == "A": return "A"
    if g == "B": return "B"
    if g == "C": return "C"
    return "TL"

def period_to_sql(period: str):
    if period == "today":
        return "DATE(created_at) = CURDATE()", "24 HOUR"
    elif period == "week":
        return "created_at >= NOW() - INTERVAL 7 DAY", "7 DAY"
    elif period == "month":
        return "created_at >= NOW() - INTERVAL 30 DAY", "30 DAY"
    else:
        return "1=1", "3650 DAY"

# ===== CNN =====
def predict_cnn(img):
    img_r = cv2.resize(img, (224, 224))
    img_r = img_r.astype(np.float32) / 255.0
    img_r = np.expand_dims(img_r, axis=0)

    interpreter.set_tensor(input_details[0]['index'], img_r)
    interpreter.invoke()

    output = interpreter.get_tensor(output_details[0]['index'])[0]
    idx    = int(np.argmax(output))
    conf   = float(np.max(output))
    return labels[idx], conf

# ===== YOLO =====
def predict_yolo(img):
    results = yolo(img)
    for r in results:
        if r.boxes is not None and len(r.boxes) > 0:
            cls  = int(r.boxes.cls[0])
            conf = float(r.boxes.conf[0])
            return labels[cls], conf
    return "TL", 0.0

def fusion(yolo_pred, yolo_conf, cnn_pred, cnn_conf):
    # kalau dua-duanya sama, langsung pakai itu
    if yolo_pred == cnn_pred:
        return yolo_pred

    # pilih berdasarkan confidence tertinggi
    if cnn_conf > yolo_conf:
        return cnn_pred
    else:
        return yolo_pred

# ===== FINAL DECISION (AI + BERAT) =====
# FIX: hanya satu definisi, logika berat yang dipakai
def final_decision(ai_grade, weight):
    # tentukan grade dari berat
    if weight < 40:
        weight_grade = "TL"
    elif weight <= 49:
        weight_grade = "C"
    elif weight <= 59:
        weight_grade = "B"
    else:
        weight_grade = "A"

    # ranking (semakin kecil = semakin jelek)
    priority = {"TL": 0, "C": 1, "B": 2, "A": 3}

    # ambil yang PALING RENDAH
    return ai_grade if priority[ai_grade] < priority[weight_grade] else weight_grade


# ─────────────────────────────────────────────────────────────────────────────
# POST /upload  — dipanggil ESP32-CAM setelah ambil foto
# Alur: simpan gambar → predict AI → insert telur (berat=0) → insert gambar,
#        cnn_result, yolo_result → kembalikan telur_id ke CAM
# ─────────────────────────────────────────────────────────────────────────────
@app.route('/upload', methods=['POST'])
def upload():
    file = request.files.get('image')

    filename = datetime.now().strftime("%Y%m%d%H%M%S") + ".jpg"
    os.makedirs("uploads", exist_ok=True)
    path = os.path.join("uploads", filename)

    if file:
        file.save(path)
        img = cv2.imread(path)
    else:
        data  = request.get_data()
        npimg = np.frombuffer(data, np.uint8)
        img   = cv2.imdecode(npimg, cv2.IMREAD_COLOR)
        if img is None:
            return jsonify({"error": "Image tidak valid"}), 400
        cv2.imwrite(path, img)

    yolo_pred, yolo_conf = predict_yolo(img)
    cnn_pred,  cnn_conf  = predict_cnn(img)
    ai_grade             = fusion(yolo_pred, yolo_conf, cnn_pred, cnn_conf)

    try:
        conn   = get_db()
        cursor = conn.cursor(dictionary=True)

        # Insert telur dulu dengan berat=0, grade sementara dari AI
        # Berat & grade final akan diupdate oleh DevKit via /updateFinal
        cursor.execute(
            "INSERT INTO telur (grade, berat) VALUES (%s, %s)",
            (ai_grade, 0)
        )
        telur_id = cursor.lastrowid

        cursor.execute(
            "INSERT INTO gambar (telur_id, path) VALUES (%s, %s)",
            (telur_id, path)
        )
        gambar_id = cursor.lastrowid

        cursor.execute(
            "INSERT INTO yolo_result (gambar_id, grade, confidence) VALUES (%s, %s, %s)",
            (gambar_id, yolo_pred, yolo_conf)
        )
        cursor.execute(
            "INSERT INTO cnn_result (gambar_id, grade, confidence) VALUES (%s, %s, %s)",
            (gambar_id, cnn_pred, cnn_conf)
        )

        conn.commit()
        cursor.close()
        conn.close()

    except Error as e:
        return jsonify({"error": str(e)}), 500

    # FIX: kembalikan "telur_id" agar konsisten dengan semua endpoint lain
    return jsonify({"telur_id":telur_id,"grade":ai_grade,"yolo":yolo_pred,"yolo_conf": round(yolo_conf, 3),"cnn":cnn_pred,"cnn_conf":round(cnn_conf, 3),
    })


# ─────────────────────────────────────────────────────────────────────────────
# GET /latest  — dipanggil DevKit untuk polling hasil foto terbaru
# FIX: kembalikan key "telur_id" (bukan "id") agar DevKit bisa parse
# ─────────────────────────────────────────────────────────────────────────────
@app.route('/latest', methods=['GET'])
def latest():
    try:
        conn   = get_db()
        cursor = conn.cursor(dictionary=True)

        cursor.execute("""
            SELECT id, grade, berat, created_at
            FROM telur
            WHERE berat = 0
            ORDER BY id DESC
            LIMIT 1
        """)
        row = cursor.fetchone()
        cursor.close()
        conn.close()

        if not row:
            return jsonify({"telur_id": 0, "berat": 0, "grade": "TL", "timestamp": "-"})

        return jsonify({
            "telur_id":  row["id"],          # FIX: pakai key "telur_id"
            "berat":     float(row["berat"]) if row["berat"] else 0,
            "grade":     map_grade(row["grade"] or "TL"),
            "timestamp": row["created_at"].strftime("%H:%M") if row["created_at"] else "-",
        })

    except Error as e:
        return jsonify({"error": str(e)}), 500


# ─────────────────────────────────────────────────────────────────────────────
# POST /updateFinal  — dipanggil DevKit dengan berat aktual
# Server hitung grade final (AI + berat) lalu update tabel telur
# FIX: indentasi try/except diperbaiki, masuk ke dalam fungsi
# ─────────────────────────────────────────────────────────────────────────────
@app.route('/updateFinal', methods=['POST'])
def update_final():
    data = request.get_json()
    if not data:
        return jsonify({"error": "No data"}), 400

    telur_id = data.get("id")
    berat    = float(data.get("berat", 0))

    if not telur_id:
        return jsonify({"error": "id tidak ada"}), 400

    try:
        conn   = get_db()
        cursor = conn.cursor()

        # Ambil AI grade yang sudah tersimpan saat /upload
        cursor.execute("SELECT grade FROM telur WHERE id=%s", (telur_id,))
        row = cursor.fetchone()

        ai_grade = row[0] if row else "TL"

        # Hitung grade final: kombinasi AI + berat
        final = final_decision(ai_grade, berat)

        cursor.execute(
            "UPDATE telur SET berat=%s, grade=%s WHERE id=%s",
            (berat, final, telur_id)
        )
        conn.commit()
        cursor.close()
        conn.close()

        print(f"[updateFinal] id={telur_id}, berat={berat}, ai={ai_grade}, final={final}")

        return jsonify({"final": final, "berat": round(berat, 2)})

    except Error as e:
        return jsonify({"error": str(e)}), 500


# ─────────────────────────────────────────────────────────────────────────────
# GET /telur  — list semua telur untuk dashboard
# ─────────────────────────────────────────────────────────────────────────────
@app.route('/telur', methods=['GET'])
def get_telur():
    try:
        conn   = get_db()
        cursor = conn.cursor(dictionary=True)

        cursor.execute("""
            SELECT id, grade, berat, created_at
            FROM telur
            ORDER BY id DESC
            LIMIT 500
        """)
        rows = cursor.fetchall()
        cursor.close()
        conn.close()

        result = []
        for row in rows:
            result.append({
                "id":        row["id"],
                "grade":     map_grade(row["grade"] or "TL"),
                "weight":    float(row["berat"]) if row["berat"] else 0,
                "timestamp": row["created_at"].strftime("%H:%M")    if row["created_at"] else "-",
                "date":      row["created_at"].strftime("%d/%m/%Y") if row["created_at"] else "-",
            })

        return jsonify(result)

    except Error as e:
        return jsonify({"error": str(e)}), 500


# ─────────────────────────────────────────────────────────────────────────────
# GET /summary?period=today|week|month|all
# ─────────────────────────────────────────────────────────────────────────────
@app.route('/summary', methods=['GET'])
def summary():
    period = request.args.get("period", "today")
    if period not in ("today", "week", "month", "all"):
        period = "today"

    where_clause, history_interval = period_to_sql(period)

    try:
        conn   = get_db()
        cursor = conn.cursor(dictionary=True)

        cursor.execute(f"""
            SELECT
                grade,
                COUNT(*)   AS jumlah,
                AVG(berat) AS rata_berat
            FROM telur
            WHERE {where_clause}
            GROUP BY grade
        """)
        grade_rows = cursor.fetchall()

        result = {
            "gradeA":  {"count": 0, "avgWeight": 0},
            "gradeB":  {"count": 0, "avgWeight": 0},
            "gradeC":  {"count": 0, "avgWeight": 0},
            "gradeTL": {"count": 0, "avgWeight": 0},
        }
        for row in grade_rows:
            g   = map_grade(row["grade"] or "TL")
            key = f"grade{g}"
            if key in result:
                result[key]["count"]     = int(row["jumlah"])
                result[key]["avgWeight"] = round(float(row["rata_berat"] or 0), 1)

        if period in ("today", "week"):
            slot_expr = """DATE_FORMAT(
                DATE_SUB(created_at, INTERVAL MOD(MINUTE(created_at), 30) MINUTE),
                '%%H:%%i'
            )"""
        else:
            slot_expr = "DATE_FORMAT(created_at, '%%d %%b')"

        cursor.execute(f"""
            SELECT
                {slot_expr} AS slot,
                grade,
                COUNT(*) AS jumlah
            FROM telur
            WHERE created_at >= NOW() - INTERVAL {history_interval}
            GROUP BY slot, grade
            ORDER BY slot ASC
        """)
        hist_rows = cursor.fetchall()
        cursor.close()
        conn.close()

        hist_map = defaultdict(lambda: {"A": 0, "B": 0, "C": 0, "TL": 0})
        for row in hist_rows:
            g = map_grade(row["grade"] or "TL")
            hist_map[row["slot"]][g] += int(row["jumlah"])

        history = [
            {"time": slot, "A": v["A"], "B": v["B"], "C": v["C"], "TL": v["TL"]}
            for slot, v in sorted(hist_map.items())
        ]

        return jsonify({**result, "history": history})

    except Error as e:
        return jsonify({"error": str(e)}), 500


# ─────────────────────────────────────────────────────────────────────────────
# GET /getSpeed  &  POST /setSpeed
# ─────────────────────────────────────────────────────────────────────────────
@app.route('/getSpeed', methods=['GET'])
def get_speed():
    try:
        conn   = get_db()
        cursor = conn.cursor(dictionary=True)
        cursor.execute("SELECT conveyor_speed FROM settings LIMIT 1")
        row = cursor.fetchone()
        cursor.close()
        conn.close()
        return jsonify({"speed": row["conveyor_speed"] if row else 200})
    except Error as e:
        return jsonify({"error": str(e)}), 500

@app.route('/setSpeed', methods=['POST'])
def set_speed():
    data  = request.get_json()
    speed = int(data["speed"])
    try:
        conn   = get_db()
        cursor = conn.cursor()
        cursor.execute("UPDATE settings SET conveyor_speed=%s WHERE id=1", (speed,))
        conn.commit()
        cursor.close()
        conn.close()
        return jsonify({"status": "ok", "speed": speed})
    except Error as e:
        return jsonify({"error": str(e)}), 500


# ─────────────────────────────────────────────────────────────────────────────
# POST /login  &  POST /register
# ─────────────────────────────────────────────────────────────────────────────
@app.route('/login', methods=['POST'])
def login():
    data     = request.get_json()
    username = data.get('username')
    password = data.get('password')

    try:
        conn   = get_db()
        cursor = conn.cursor(dictionary=True)
        cursor.execute(
            "SELECT * FROM users WHERE username = %s AND password = %s",
            (username, password)
        )
        user = cursor.fetchone()
        cursor.close()
        conn.close()

        if user:
            return jsonify({"success": True, "username": user["username"], "nama": user["nama"], "role": user["role"]})
        return jsonify({"success": False, "message": "Username atau password salah"}), 401

    except Error as e:
        return jsonify({"error": str(e)}), 500


@app.route('/register', methods=['POST'])
def register():
    data     = request.get_json()
    nama     = data.get('nama')
    username = data.get('username')
    password = data.get('password')
    role     = data.get('role', 'user')

    try:
        conn   = get_db()
        cursor = conn.cursor(dictionary=True)

        cursor.execute("SELECT * FROM users WHERE username = %s", (username,))
        existing = cursor.fetchone()
        if existing:
            cursor.close()
            conn.close()
            return jsonify({"success": False, "message": "Username sudah dipakai"}), 400

        cursor.execute(
            "INSERT INTO users (nama, username, password, role) VALUES (%s, %s, %s, %s)",
            (nama, username, password, role)
        )
        conn.commit()
        cursor.close()
        conn.close()

        return jsonify({"success": True, "message": "Registrasi berhasil"})

    except Error as e:
        return jsonify({"error": str(e)}), 500
        
# ─────────────────────────────────────────────────────────────
# POST /order  — simpan transaksi (orders + order_items)
# ─────────────────────────────────────────────────────────────
@app.route('/order', methods=['POST'])
def create_order():
    data = request.get_json()
    cart = data.get("cart", [])
    nama = data.get("nama", "guest")

    harga_map = {
        "A": 1500,
        "B": 1200,
        "C": 900
    }

    try:
        conn   = get_db()
        cursor = conn.cursor()

        total = 0

        # 🔥 CEK STOK DULU (BIAR GA MINUS)
        for item in cart:
            cursor.execute(
                "SELECT COUNT(*) FROM telur WHERE grade = %s",
                (item["grade"],)
            )
            stok = cursor.fetchone()[0]

            if stok < item["qty"]:
                return jsonify({
                    "success": False,
                    "message": f"Stok grade {item['grade']} tidak cukup"
                })

        # 🔥 HITUNG TOTAL
        for item in cart:
            total += harga_map[item["grade"]] * item["qty"]

        # 🔥 INSERT KE ORDERS
        cursor.execute(
            "INSERT INTO orders (nama, total) VALUES (%s, %s)",
            (nama, total)
        )

        order_id = cursor.lastrowid

        # 🔥 INSERT ITEMS + HAPUS STOK
        for item in cart:
            grade = item["grade"]
            qty   = item["qty"]
            harga = harga_map[grade]

            # insert item
            cursor.execute(
                "INSERT INTO order_items (order_id, grade, qty, harga) VALUES (%s,%s,%s,%s)",
                (order_id, grade, qty, harga)
            )

            # 🔥 HAPUS STOK (INI INTI NYA)
            cursor.execute("""
                DELETE FROM telur
                WHERE grade = %s
                ORDER BY id ASC
                LIMIT %s
            """, (grade, qty))

        conn.commit()
        cursor.close()
        conn.close()

        print(f"[ORDER] sukses id={order_id}, total={total}")

        return jsonify({
            "success": True,
            "order_id": order_id
        })

    except Error as e:
        return jsonify({"error": str(e)}), 500
        
# ─────────────────────────────────────────────
# 📦 GET ALL ORDERS (ADMIN / HISTORY)
# ─────────────────────────────────────────────
@app.route('/orders', methods=['GET'])
def get_orders():
    try:
        conn = get_db()
        cursor = conn.cursor(dictionary=True)

        cursor.execute("""
            SELECT o.id, o.nama, o.total, o.status, o.bukti_pembayaran, o.created_at
            FROM orders o
            ORDER BY o.id DESC
        """)

        orders = cursor.fetchall()

        cursor.close()
        conn.close()

        return jsonify(orders)

    except Error as e:
        return jsonify({"error": str(e)}), 500


# ─────────────────────────────────────────────
# 🔄 UPDATE STATUS ORDER (ADMIN APPROVE)
# ─────────────────────────────────────────────
@app.route('/order/status', methods=['POST'])
def update_status():
    data = request.json
    order_id = data.get("order_id")
    status = data.get("status")

    try:
        conn = get_db()
        cursor = conn.cursor()

        cursor.execute(
            "UPDATE orders SET status=%s WHERE id=%s",
            (status, order_id)
        )

        conn.commit()
        cursor.close()
        conn.close()

        return jsonify({"success": True})

    except Error as e:
        return jsonify({"error": str(e)}), 500


# ─────────────────────────────────────────────
# 📤 UPLOAD BUKTI PEMBAYARAN
# ─────────────────────────────────────────────
import os
from werkzeug.utils import secure_filename
from datetime import datetime
from flask import send_from_directory

UPLOAD_FOLDER_BUKTI = 'uploads_bukti'
os.makedirs(UPLOAD_FOLDER_BUKTI, exist_ok=True)

@app.route('/upload-bukti', methods=['POST'])
def upload_bukti():
    file = request.files.get('file')
    order_id = request.form.get('order_id')

    if not file or not order_id:
        return jsonify({"success": False, "message": "File / order_id kosong"})

    filename = datetime.now().strftime("%Y%m%d%H%M%S_") + secure_filename(file.filename)
    filepath = os.path.join(UPLOAD_FOLDER_BUKTI, filename)

    file.save(filepath)

    try:
        conn = get_db()
        cursor = conn.cursor()

        cursor.execute(
            "UPDATE orders SET bukti_pembayaran=%s, status='menunggu_verifikasi' WHERE id=%s",
            (filename, order_id)
        )

        conn.commit()
        cursor.close()
        conn.close()

        print(f"[UPLOAD BUKTI] order_id={order_id}, file={filename}")

        return jsonify({"success": True})

    except Error as e:
        return jsonify({"error": str(e)}), 500


# ─────────────────────────────────────────────
# 🖼️ AKSES FILE BUKTI PEMBAYARAN
# ─────────────────────────────────────────────
@app.route('/uploads-bukti/<filename>')
def get_bukti(filename):
    return send_from_directory(UPLOAD_FOLDER_BUKTI, filename)


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