from pathlib import Path
from datetime import datetime
import atexit
from openpyxl import Workbook, load_workbook

EXCEL_PATH = "autopoll_log.xlsx"

# Keep workbook open for performance
_wb = None
_ws_map = {}
_pending_writes = 0

# Per-sheet headers (include units in the Value column)
HEADER_MAP = {
    "Conductivity": ["Number", "Date", "Time", "Value (S/cm)", "Read Count", "Error"],
    "Temperature":  ["Number", "Date", "Time", "Value (°C)", "Read Count", "Error"],
}

########################################################################################################################

def _headers_match(ws, expected_headers):
    """Check if the first row in ws matches the expected headers exactly."""
    row = [ws.cell(row=1, column=c).value for c in range(1, len(expected_headers) + 1)]
    return row == expected_headers

def _write_headers(ws, headers):
    """Write headers to the first row."""
    for col, h in enumerate(headers, start=1):
        ws.cell(row=1, column=col).value = h

def _get_or_create_workbook(path: str):
    """Open workbook if exists, otherwise create it. Ensure required sheets and headers."""
    global _wb, _ws_map

    if _wb is not None:
        return _wb

    p = Path(path)

    if p.exists():
        _wb = load_workbook(p)
    else:
        _wb = Workbook()  # default contains one sheet; we’ll clean it up later

    def ensure_sheet(name: str, headers: list[str]):
        # Create or get the sheet
        if name in _wb.sheetnames:
            ws = _wb[name]
        else:
            ws = _wb.create_sheet(title=name)

        # If empty or headers mismatch, (re)write headers
        # "Empty" heuristic: either only header row exists and it's blank, or sheet is brand new
        is_probably_empty = (
            ws.max_row == 1 and
            all(ws.cell(row=1, column=c).value is None for c in range(1, len(headers) + 1))
        )
        if is_probably_empty or not _headers_match(ws, headers):
            _write_headers(ws, headers)

        return ws

    # Ensure both sheets with their respective headers
    _ws_map["Conductivity"] = ensure_sheet("Conductivity", HEADER_MAP["Conductivity"])
    _ws_map["Temperature"]  = ensure_sheet("Temperature",  HEADER_MAP["Temperature"])

    # Remove default "Sheet" if unused and not one of our targets
    if "Sheet" in _wb.sheetnames and len(_wb.sheetnames) > 2:
        ws_default = _wb["Sheet"]
        if (
            ws_default.max_row == 1 and
            ws_default.max_column == 1 and
            ws_default["A1"].value is None
        ):
            _wb.remove(ws_default)

    return _wb


def _next_index(ws):
    """Return next sequential Number for this sheet, based on the last used row."""
    # Row 1 is header; data starts at row 2
    if ws.max_row < 2:
        return 1
    last_num = ws.cell(row=ws.max_row, column=1).value
    try:
        return int(last_num) + 1
    except (TypeError, ValueError):
        # Fallback based on row count (data rows = max_row - 1)
        return (ws.max_row - 1) + 1


def log_to_excel(kind: str, ts: datetime, value: float, read_count: int, error: int, path: str = EXCEL_PATH):
    """
    Append a row to the Excel workbook.

    kind: "Conductivity" or "Temperature"
    ts: datetime timestamp
    value: float
    read_count: int
    error: int
    """
    global _pending_writes

    if kind not in HEADER_MAP:
        raise ValueError(f"Unsupported kind '{kind}'. Expected one of: {list(HEADER_MAP.keys())}")

    wb = _get_or_create_workbook(path)
    ws = _ws_map[kind]

    n = _next_index(ws)
    date_str = ts.date().isoformat()
    time_str = ts.time().isoformat()  # includes microseconds if present

    ws.append([n, date_str, time_str, float(value), int(read_count), int(error)])

    _pending_writes += 1

    # Save periodically to avoid losing data if the script stops unexpectedly
    if _pending_writes >= 25:   # adjust as you like
        wb.save(path)
        _pending_writes = 0


def flush_excel(path: str = EXCEL_PATH):
    """Force-save any pending writes."""
    global _wb, _pending_writes
    if _wb is not None:
        _wb.save(path)
        _pending_writes = 0


# Auto-save when program exits
atexit.register(flush_excel)