#!/usr/bin/env python3
import json
import logging
from datetime import datetime

import paho.mqtt.client as mqtt
import pymysql

CONFIG_FILE = "config.json"


def load_config(path=CONFIG_FILE):
    with open(path, "r", encoding="utf-8") as f:
        return json.load(f)


def init_db(cfg):
    conn = pymysql.connect(
        host=cfg["host"],
        user=cfg["user"],
        password=cfg["password"],
        db=cfg["db"],
        charset=cfg.get("charset", "utf8"),
        autocommit=True,
    )
    cur = conn.cursor()
    cur.execute(
        """
        CREATE TABLE IF NOT EXISTS op_bhi_messages (
            id INT AUTO_INCREMENT PRIMARY KEY,
            station_id VARCHAR(50) NOT NULL,
            direction VARCHAR(10) NOT NULL,
            action VARCHAR(100) DEFAULT '',
            payload TEXT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        """
    )
    return conn


def on_connect(client, userdata, flags, rc):
    logging.info("Connected to MQTT with result code %s", rc)
    prefix = userdata.get("prefix", "")
    client.subscribe(f"{prefix}wallbox/+/+")


def on_message(client, userdata, msg):
    prefix = userdata.get("prefix", "")
    topic = msg.topic[len(prefix):] if prefix and msg.topic.startswith(prefix) else msg.topic
    parts = topic.split('/')
    if len(parts) < 3:
        return
    _, station_id, direction = parts[:3]
    payload = msg.payload.decode('utf-8')
    try:
        data = json.loads(payload)
        action = data[2] if isinstance(data, list) and len(data) > 2 else ""
    except Exception:
        action = ""
    conn = userdata["db_conn"]
    with conn.cursor() as cur:
        cur.execute(
            "INSERT INTO op_bhi_messages (station_id, direction, action, payload) VALUES (%s,%s,%s,%s)",
            (station_id, direction, action, payload)
        )


def main():
    config = load_config()
    db_cfg = config.get("mysql", {})
    mqtt_cfg = config.get("mqtt", {})

    conn = init_db(db_cfg)

    client = mqtt.Client(userdata={"db_conn": conn, "prefix": mqtt_cfg.get("topic_prefix", "")})
    if mqtt_cfg.get("user"):
        client.username_pw_set(mqtt_cfg.get("user"), mqtt_cfg.get("password"))
    client.on_connect = on_connect
    client.on_message = on_message

    broker = mqtt_cfg.get("broker", "127.0.0.1")
    port = mqtt_cfg.get("port", 1883)
    logging.info("Connecting to MQTT broker %s:%s", broker, port)
    client.connect(broker, port)
    client.loop_forever()


if __name__ == "__main__":
    logging.basicConfig(level=logging.INFO)
    main()