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

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
        )
        """
    )
    cur.execute(
        """
        CREATE TABLE IF NOT EXISTS op_bhi_sessions (
            id INT AUTO_INCREMENT PRIMARY KEY,
            station_id VARCHAR(50) NOT NULL,
            message_count INT NOT NULL,
            start_ts TIMESTAMP NULL,
            end_ts TIMESTAMP NULL
        )
        """
    )
    cur.execute(
        """
        CREATE TABLE IF NOT EXISTS op_bhi_session_meta (
            id INT PRIMARY KEY,
            last_msg_id INT NOT NULL
        )
        """
    )
    cur.execute("INSERT IGNORE INTO op_bhi_session_meta (id, last_msg_id) VALUES (1, 0)")
    return conn


def main():
    config = load_config()
    db_cfg = config.get("mysql", {})
    conn = init_db(db_cfg)
    cur = conn.cursor(pymysql.cursors.DictCursor)
    cur.execute("SELECT last_msg_id FROM op_bhi_session_meta WHERE id=1")
    last_id = cur.fetchone()["last_msg_id"]
    cur.execute("SELECT id, station_id, created_at FROM op_bhi_messages WHERE id > %s", (last_id,))
    rows = cur.fetchall()
    if not rows:
        logging.info("No new messages to process")
        return
    grouped = {}
    for r in rows:
        grouped.setdefault(r["station_id"], []).append(r)
    for station, items in grouped.items():
        start = min(i["created_at"] for i in items)
        end = max(i["created_at"] for i in items)
        cur.execute(
            "INSERT INTO op_bhi_sessions (station_id, message_count, start_ts, end_ts) VALUES (%s,%s,%s,%s)",
            (station, len(items), start, end)
        )
    max_id = max(r["id"] for r in rows)
    cur.execute("UPDATE op_bhi_session_meta SET last_msg_id=%s WHERE id=1", (max_id,))
    conn.commit()
    conn.close()
    logging.info("Processed %s messages", len(rows))


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