#!/usr/bin/env python3
"""Extract latest BootNotification info for each charge point in a month.

Usage:
    python services/getBootNotificationsOfMonth.py [YYMM]

If *YYMM* is omitted, the script processes messages for the current month.

Reads BootNotification messages from op_messages_YYMM and writes a CSV file
containing chargePointVendor, chargePointModel, chargePointSerialNumber and
firmwareVersion for each charge point.
"""

import csv
import json
import sys
from datetime import datetime, timezone

import pymysql

CONFIG_FILE = "../config.json"


def load_config(path: str = CONFIG_FILE) -> dict:
    """Load configuration from *path*."""
    with open(path, "r", encoding="utf-8") as f:
        return json.load(f)


def init_db(cfg: dict):
    """Initialise and return a database connection using *cfg*."""
    return pymysql.connect(
        host=cfg["host"],
        user=cfg["user"],
        password=cfg["password"],
        db=cfg["db"],
        charset=cfg.get("charset", "utf8"),
        autocommit=True,
    )


def collect_boot_notifications(cur, month: str):
    """Return latest BootNotification payloads per station for *month* (YYMM)."""
    table = f"op_messages_{month}"
    cur.execute(
        f"""
        SELECT id, source_url, message
        FROM `{table}`
        WHERE message LIKE '%BootNotification%'
        ORDER BY id
        """
    )
    rows = cur.fetchall()

    latest: dict[str, dict] = {}
    for _, source_url, message in rows:
        try:
            data = json.loads(message)
        except json.JSONDecodeError:
            continue
        if not isinstance(data, list) or len(data) < 4:
            continue
        if data[0] != 2 or data[2] != "BootNotification":
            continue
        payload = data[3]
        station_id = source_url.rsplit("/", 1)[-1]
        latest[station_id] = payload
    return latest


def ensure_boot_table(cur) -> None:
    """Ensure that op_broker_bootnotifications table exists."""
    cur.execute(
        """
        CREATE TABLE IF NOT EXISTS op_broker_bootnotifications (
            station_id VARCHAR(255) PRIMARY KEY,
            json_payload TEXT NOT NULL,
            ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        """
    )


def store_boot_notifications(cur, payloads: dict[str, dict]) -> None:
    """Insert or update *payloads* into op_broker_bootnotifications."""
    ensure_boot_table(cur)
    for station_id, payload in payloads.items():
        cur.execute(
            """
            INSERT INTO op_broker_bootnotifications (station_id, json_payload, ts)
            VALUES (%s, %s, NOW()) AS new(station_id, json_payload, ts)
            ON DUPLICATE KEY UPDATE json_payload = new.json_payload, ts = new.ts
            """,
            (station_id, json.dumps(payload)),
        )


def main():
    if len(sys.argv) > 2:
        print("Usage: getBootNotificationsOfMonth.py [YYMM]")
        sys.exit(1)

    if len(sys.argv) == 2:
        month = sys.argv[1]
    else:
        month = datetime.now(timezone.utc).strftime("%y%m")

    config = load_config()
    db_cfg = config.get("mysql", {})
    conn = init_db(db_cfg)
    try:
        with conn.cursor() as cur:
            payloads = collect_boot_notifications(cur, month)
            store_boot_notifications(cur, payloads)
    finally:
        conn.close()

    records = [
        (
            p.get("chargePointVendor"),
            p.get("chargePointModel"),
            p.get("chargePointSerialNumber"),
            p.get("firmwareVersion"),
        )
        for p in payloads.values()
    ]

    out_file = f"boot_notifications_{month}.csv"
    with open(out_file, "w", newline="", encoding="utf-8") as f:
        writer = csv.writer(f)
        writer.writerow(
            [
                "chargePointVendor",
                "chargePointModel",
                "chargePointSerialNumber",
                "firmwareVersion",
            ]
        )
        writer.writerows(records)

    print(f"Wrote {len(records)} records to {out_file}")


if __name__ == "__main__":
    main()