"""Fetch Tesla State of Charge via the local /soc endpoint and persist results.

This script queries the Tesla SoC endpoint that is served by ``api/getSOC.py`` and
stores the returned payload in the MySQL table ``bhi_vehicle_data``.  The table is
created automatically on the first run.
"""

from __future__ import annotations

import json
import logging
import os
from datetime import datetime, timezone
from typing import Any, Dict

import pymysql
import requests


LOG_LEVEL = os.getenv("TESLA_SOC_LOG_LEVEL", "INFO").upper()
logging.basicConfig(
    level=getattr(logging, LOG_LEVEL, logging.INFO),
    format="%(asctime)s %(levelname)s %(message)s",
)
logger = logging.getLogger(__name__)


# ---------------------------------------------------------------------------
# Configuration
# ---------------------------------------------------------------------------

SOC_ENDPOINT = os.getenv("TESLA_SOC_URL", "http://127.0.0.1:6600/detailed_charge_state")
VIN = os.getenv("TESLA_VIN", "XP7YGCEK3RB270069")

DB_CONFIG = {
    "host": os.getenv("DB_HOST", "172.211.136.203"),
    "user": os.getenv("DB_USER", "charge"),
    "password": os.getenv("DB_PASSWORD", "zm0dem123"),
    "db": os.getenv("DB_NAME", "op"),
    "charset": os.getenv("DB_CHARSET", "utf8"),
}

TABLE_NAME = "bhi_vehicle_data"

CREATE_TABLE_SQL = f"""
CREATE TABLE IF NOT EXISTS {TABLE_NAME} (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    vin VARCHAR(32) NOT NULL,
    json_payload JSON NOT NULL,
    ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
"""

INSERT_SQL = f"""
INSERT INTO {TABLE_NAME} (vin, json_payload, ts)
VALUES (%s, CAST(%s AS JSON), %s)
"""


# ---------------------------------------------------------------------------
# Database helpers
# ---------------------------------------------------------------------------

def get_connection() -> pymysql.connections.Connection:
    """Return a new pymysql connection using :data:`DB_CONFIG`."""

    return pymysql.connect(cursorclass=pymysql.cursors.Cursor, **DB_CONFIG)


def ensure_table(conn: pymysql.connections.Connection) -> None:
    """Create the destination table when it is missing."""

    with conn.cursor() as cursor:
        logger.debug("Ensuring table %s exists", TABLE_NAME)
        cursor.execute(CREATE_TABLE_SQL)
    conn.commit()


def insert_payload(
    conn: pymysql.connections.Connection, vin: str, payload: Dict[str, Any]
) -> None:
    """Insert a new row containing the SoC payload."""

    json_blob = json.dumps(payload, ensure_ascii=False)
    timestamp = datetime.now(timezone.utc)
    with conn.cursor() as cursor:
        cursor.execute(INSERT_SQL, (vin, json_blob, timestamp))
    conn.commit()


# ---------------------------------------------------------------------------
# API interaction
# ---------------------------------------------------------------------------

def fetch_soc_payload() -> Dict[str, Any]:
    """Query the /soc endpoint and return the decoded JSON payload."""

    logger.info("Requesting SoC from %s", SOC_ENDPOINT)
    response = requests.get(SOC_ENDPOINT, timeout=30)
    response.raise_for_status()
    payload = response.json()
    logger.debug("Received payload: %s", payload)
    return payload


# ---------------------------------------------------------------------------
# Main routine
# ---------------------------------------------------------------------------

def main() -> None:
    try:
        payload = fetch_soc_payload()
    except requests.RequestException as exc:
        logger.error("Failed to fetch SoC data: %s", exc)
        raise SystemExit(1) from exc

    try:
        conn = get_connection()
    except pymysql.MySQLError as exc:
        logger.error("Could not connect to MySQL: %s", exc)
        raise SystemExit(2) from exc

    try:
        ensure_table(conn)
        insert_payload(conn, VIN, payload)
        logger.info("Stored SoC payload for VIN %s", VIN)
    except pymysql.MySQLError as exc:
        logger.error("Database error: %s", exc)
        raise SystemExit(3) from exc
    finally:
        conn.close()


if __name__ == "__main__":
    main()