#!/usr/bin/env python3
import json
import threading
import paho.mqtt.client as mqtt
import os
import sqlite3
from datetime import datetime, timedelta

# =====================
# SITE CONFIG
# =====================
TOP_TOPIC = "cpsp"
SITE_NAME = "sukawarna"
SITE_TOPIC = f"{TOP_TOPIC}/counter/{SITE_NAME}"

# =====================
# KONFIGURASI MQTT
# =====================
BROKER = "localhost"  # ganti sesuai broker
PORT = 1883
USERNAME = ""  # ganti user
PASSWORD = ""  # ganti password
TOPIC = "frigate/events"

# =====================
# MQTT PUBLISH
# =====================
#PUBLISH_BROKER = "localhost"
PUBLISH_BROKER = "mqtt.backone.cloud"
PUBLISH_PORT = 1883
USERNAME = ""  # ganti user
PASSWORD = ""  # ganti password

# =====================
# FILE COUNTER
# =====================
DATA_FILE = "/etc/frigate-counter/karung-tuang/karung_tuang.json"

# =====================
# DATABASE INITIALIZATION
# =====================
DB_FILE = "/etc/frigate-counter/karung-tuang/karung_tuang.db"


def init_database():
    """Initialize the SQLite database with the required table"""
    conn = sqlite3.connect(DB_FILE)
    cursor = conn.cursor()

    # Create table if it doesn't exist
    cursor.execute(
        """
        CREATE TABLE IF NOT EXISTS counter_data (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            camera_name TEXT NOT NULL,
            date TIMESTAMP NOT NULL,
            counter_value INTEGER NOT NULL
        )
    """
    )

    conn.commit()
    conn.close()


# Initialize the database
init_database()

# =====================
# INISIALISASI COUNTER
# =====================
if os.path.exists(DATA_FILE):
    with open(DATA_FILE, "r") as f:
        counter = json.load(f)
else:
    counter = {
        "kandang_atas_feeder_kiri": {"karung": 0},
        "kandang_bawah_feeder_kanan": {"karung": 0},
    }
    with open(DATA_FILE, "w") as f:
        json.dump(counter, f, indent=2)

camera_feeder_no_duplicate = [
        "kandang_atas_feeder_kiri",
        "kandang_bawah_feeder_kanan"
        ]

camera_feeder = [
        "kandang_atas_feeder_kiri",
        "kandang_bawah_feeder_kanan"
        ]

camera_masuk = ["kandang_1_karung_masuk"]

# Untuk debounce object per track_id
seen_objects = {}  # dict per kamera: {track_id: last_seen}


# =====================
# FUNGSI SAVE COUNTER
# =====================
def save_counter():
    with open(DATA_FILE, "w") as f:
        json.dump(counter, f, indent=2)


def save_counter_to_database(camera_list_to_save=[]):
    """Save current counter values to SQLite database"""
    conn = sqlite3.connect(DB_FILE)
    cursor = conn.cursor()

    # Get current timestamp
    current_time = datetime.now()

    # Save each counter value to database
    for camera_name, camera_data in counter.items():
        for label, value in camera_data.items():
            if label == "karung" and camera_name in camera_list_to_save:  # Only save karung counters
                cursor.execute(
                    """
                    INSERT INTO counter_data (camera_name, date, counter_value)
                    VALUES (?, ?, ?)
                """,
                    (camera_name, current_time, value),
                )

    conn.commit()
    conn.close()


def republish_counter():
    # Publish current counter
    for camera, v in counter.items():
        for label, value in v.items():
            if label == "karung":
                print(f"{SITE_TOPIC}/{camera}/{label}", value)
                client_publish.publish(
                    f"{SITE_TOPIC}/{camera}/{label}", value, qos=1, retain=True
                )


# =====================
# RESET HARIAN
# =====================
def reset_counter():
    global counter, seen_objects
    print(f"[{datetime.now()}] Reset counter otomatis")
    # Save current counter values to database before reset
    save_counter_to_database(camera_feeder)
    # reset semua
    # for cam in counter:
    #    for obj in counter[cam]:
    #        counter[cam][obj] = 0
    for camera_name in camera_feeder:
        counter[camera_name]["karung"] = 0

    """
    counter["kandang_1_karung_pakan"]["karung"] = 0
    counter["kandang_2_karung_pakan"]["karung"] = 0
    counter["kandang_atas_feeder_kiri"]["karung"] = 0
    counter["kandang_bawah_feeder_kanan"]["karung"] = 0
    """
    seen_objects = {}
    save_counter()
    schedule_reset()  # jadwalkan besok
    republish_counter()


def reset_counter_masuk():
    global counter, seen_objects
    print(f"[{datetime.now()}] Reset counter otomatis")
    # Save current counter values to database before reset
    save_counter_to_database(camera_masuk)
    # reset semua
    # for cam in counter:
    #    for obj in counter[cam]:
    #        counter[cam][obj] = 0
    for camera_name in camera_masuk:
        counter[camera_name]["karung"] = 0

    counter["kandang_1_karung_masuk"]["karung"] = 0

    seen_objects = {}
    save_counter()
    schedule_reset_masuk()  # jadwalkan besok
    republish_counter()


def schedule_reset():
    now = datetime.now()
    reset_time = now.replace(hour=17, minute=0, second=0, microsecond=0)
    if now >= reset_time:
        reset_time += timedelta(days=1)
    delay = (reset_time - now).total_seconds()
    threading.Timer(delay, reset_counter).start()
    print(f"Reset counter dijadwalkan pada: {reset_time}")


def schedule_reset_masuk():
    now = datetime.now()
    reset_time = now.replace(hour=23, minute=59, second=55, microsecond=0)
    if now >= reset_time:
        reset_time += timedelta(days=1)
    delay = (reset_time - now).total_seconds()
    threading.Timer(delay, reset_counter_masuk).start()
    print(f"Reset counter dijadwalkan pada: {reset_time}")


# =====================
# CALLBACK MQTT
# =====================
def on_connect(client, userdata, flags, rc):
    print("Connected to MQTT with result code " + str(rc))
    client.subscribe(TOPIC)


def on_connect_publish(client, userdata, flags, rc):
    print("Connected to MQTT PUBLISH with result code " + str(rc))
    # Publish current counter
    republish_counter()


def on_message(client, userdata, msg):
    try:
        payload = json.loads(msg.payload.decode())
        if "after" not in payload:
            return

        event_after = payload["after"]
        event_before = payload.get("before", {})

        camera = event_after.get("camera")
        label = event_after.get("label")
        track_id = event_after.get("id")
        zones_after = event_after.get("entered_zones", [])
        zones_before = event_before.get("entered_zones", [])

        # Dont detect stationary
        stationary = event_after.get("stationary")
        if stationary:
            return

        # ambil zona baru yg sebelumnya belum ada
        new_zones = [z for z in zones_after if z not in zones_before]

        # DEDY Don't count camera_masuk. Only count camera feeder
        #if camera not in camera_feeder:
        #    return

        if not camera or not label:
            return
        if camera not in counter or label not in counter[camera]:
            return
        if not new_zones:  # hanya hitung kalau masuk zona baru
            return

        # debounce per track_id
        if camera not in seen_objects:
            seen_objects[camera] = {}
        if track_id in seen_objects[camera]:
            return  # sudah dihitung

        # Check if timestamp is more than 30 seconds
        # if camera == "kandang_1_karung_pakan" or camera == "kandang_2_karung_pakan":
        """
        if (
            camera == "kandang_1_karung_pakan"
            or camera == "kandang_2_karung_pakan"
            or camera == "kandang_atas_feeder_kiri"
            or camera == "kandang_bawah_feeder_kanan"
        ):
        """
        if camera in camera_feeder:
            DETIK_ANTAR_KARUNG = 35
            current_time = datetime.now()
            for ts in seen_objects[camera].values():
                delta = current_time - ts
                if delta.seconds < DETIK_ANTAR_KARUNG:
                    return

        # hitung
        counter[camera][label] += 1
        seen_objects[camera][track_id] = datetime.now()

        print(
            f"[{datetime.now()}] Kamera {camera}: Object {label} masuk zona {new_zones}"
        )
        print(f"Total {label} di {camera}: {counter[camera][label]}")

        print(f"{TOP_TOPIC}/counter/{SITE_NAME}/{camera}/{label}")

        # simpan dan publish
        save_counter()
        #save_counter_to_database(camera_feeder)
        # client_publish.publish(f"{TOP_TOPIC}/counter/{SITE_NAME}/{camera}/{label}", counter[camera][label], qos=1, retain=True)
        client_publish.publish(
            f"{SITE_TOPIC}/{camera}/{label}", counter[camera][label], qos=1, retain=True
        )

    except Exception as e:
        print("Error parsing message:", e)


def view_database():
    """View all records in the database"""
    conn = sqlite3.connect(DB_FILE)
    cursor = conn.cursor()

    cursor.execute("SELECT * FROM counter_data ORDER BY date DESC LIMIT 10")
    rows = cursor.fetchall()

    print("Last 10 records in database:")
    for row in rows:
        print(f"ID: {row[0]}, Camera: {row[1]}, Date: {row[2]}, Counter: {row[3]}")

    conn.close()

# =====================
# MAIN
# =====================
client = mqtt.Client()
client.username_pw_set(USERNAME, PASSWORD)
client.on_connect = on_connect
client.on_message = on_message

client.connect(BROKER, PORT, 60)

client_publish = mqtt.Client()
client_publish.on_connect = on_connect_publish
client_publish.connect(PUBLISH_BROKER, PUBLISH_PORT, 60)


# jadwalkan reset pertama
schedule_reset()
#schedule_reset_masuk()

client_publish.loop_start()
print(f"MQTT Publish Counter berjalan di {PUBLISH_BROKER}")
print(f"MQTT Counter berjalan di {BROKER}")
client.loop_forever()