ha-timeRatioScript/export_state_ratio_csv.sh

#!/usr/bin/env bash

set -uo pipefail

TARGET_ENTITY="${1:-}"
TARGET_STATE="${2:-}"
DEPENDENCE_ENTITY="${3:-}"
DEPENDENCE_STATE="${4:-}"
INTERVAL_START="${5:-}"
INTERVAL_STOP="${6:-}"

OUT_BASE_DIR="${OUT_DIR:-/config/data}"
DB_PATH="${DB_PATH:-/config/home-assistant_v2.db}"
HA_TZ="${HA_TZ:-Europe/Berlin}"

HA_URL="${HA_URL:-http://127.0.0.1:8123}"
HA_TOKEN_FILE="${HA_TOKEN_FILE:-/config/scripts/.state_ratio_ha_token}"
HA_TOKEN="${HA_TOKEN:-}"

sanitize_for_filename() {
  printf '%s' "$1" | sed 's/[^A-Za-z0-9_.-]/_/g' | sed 's/^_*//;s/_*$//' | cut -c1-80
}

mkdir -p "$OUT_BASE_DIR"

REQUEST_TS="$(date +"%Y%m%d-%H%M%S")"
SAFE_TARGET_ENTITY="$(sanitize_for_filename "$TARGET_ENTITY")"
SAFE_TARGET_STATE="$(sanitize_for_filename "$TARGET_STATE")"

if [[ -z "$SAFE_TARGET_ENTITY" ]]; then
  SAFE_TARGET_ENTITY="unknown_entity"
fi

if [[ -z "$SAFE_TARGET_STATE" ]]; then
  SAFE_TARGET_STATE="unknown_state"
fi

BASE_FILENAME="${REQUEST_TS}_state_ratio_${SAFE_TARGET_ENTITY}_${SAFE_TARGET_STATE}"
OUTPUT_DIR="$OUT_BASE_DIR/$BASE_FILENAME"

mkdir -p "$OUTPUT_DIR"

CSV_FILE="$OUTPUT_DIR/${BASE_FILENAME}.csv"
SUMMARY_FILE="$OUTPUT_DIR/${BASE_FILENAME}_summary.txt"
LOG_FILE="$OUTPUT_DIR/${BASE_FILENAME}.log"

exec > >(tee -a "$LOG_FILE") 2>&1

echo "[$(date -Iseconds)] Starting state ratio export"
echo "Base filename:      $BASE_FILENAME"
echo "Output folder:      $OUTPUT_DIR"
echo "Target entity:      $TARGET_ENTITY"
echo "Target state:       $TARGET_STATE"
echo "Dependence entity:  $DEPENDENCE_ENTITY"
echo "Dependence state:   $DEPENDENCE_STATE"
echo "Interval start:     $INTERVAL_START"
echo "Interval stop:      $INTERVAL_STOP"
echo "Database path:      $DB_PATH"
echo "Output base dir:    $OUT_BASE_DIR"
echo "CSV file:           $CSV_FILE"
echo "Summary file:       $SUMMARY_FILE"
echo "Log file:           $LOG_FILE"
echo "Timezone:           $HA_TZ"
echo "Home Assistant URL: $HA_URL"
echo "HA token file:      $HA_TOKEN_FILE"

if [[ -z "$TARGET_ENTITY" || -z "$TARGET_STATE" || -z "$INTERVAL_START" || -z "$INTERVAL_STOP" ]]; then
  echo "ERROR: target_entity, target_state, interval_start and interval_stop are required."
  exit 2
fi

if [[ -n "$DEPENDENCE_ENTITY" && -z "$DEPENDENCE_STATE" ]]; then
  echo "ERROR: dependence_state must be set when dependence_entity is set."
  exit 2
fi

if [[ -z "$DEPENDENCE_ENTITY" && -n "$DEPENDENCE_STATE" ]]; then
  echo "ERROR: dependence_entity must be set when dependence_state is set."
  exit 2
fi

if [[ ! -f "$DB_PATH" ]]; then
  echo "ERROR: Database file not found: $DB_PATH"
  exit 3
fi

export TARGET_ENTITY TARGET_STATE DEPENDENCE_ENTITY DEPENDENCE_STATE
export INTERVAL_START INTERVAL_STOP DB_PATH OUT_BASE_DIR OUTPUT_DIR HA_TZ REQUEST_TS
export BASE_FILENAME CSV_FILE SUMMARY_FILE LOG_FILE
export HA_URL HA_TOKEN HA_TOKEN_FILE

python3 <<'PY'
import csv
import json
import os
import re
import sqlite3
import urllib.error
import urllib.request
from datetime import datetime, timedelta
from zoneinfo import ZoneInfo

target_entity = os.environ["TARGET_ENTITY"]
target_state = os.environ["TARGET_STATE"]
dependence_entity = os.environ.get("DEPENDENCE_ENTITY", "")
dependence_state = os.environ.get("DEPENDENCE_STATE", "")
interval_start_raw = os.environ["INTERVAL_START"]
interval_stop_raw = os.environ["INTERVAL_STOP"]

db_path = os.environ["DB_PATH"]
out_base_dir = os.environ["OUT_BASE_DIR"]
output_dir = os.environ["OUTPUT_DIR"]
ha_tz_name = os.environ.get("HA_TZ", "Europe/Berlin")
request_ts = os.environ["REQUEST_TS"]

base_filename = os.environ["BASE_FILENAME"]
csv_output_path = os.environ["CSV_FILE"]
summary_output_path = os.environ["SUMMARY_FILE"]
log_file = os.environ["LOG_FILE"]

ha_url_raw = os.environ.get("HA_URL", "http://127.0.0.1:8123").strip()
ha_token_env = os.environ.get("HA_TOKEN", "")
ha_token_file = os.environ.get("HA_TOKEN_FILE", "/config/scripts/.state_ratio_ha_token")

tz = ZoneInfo(ha_tz_name)
dependency_enabled = bool(dependence_entity and dependence_state)

def log(message: str):
    print(f"[PY {datetime.now(tz).isoformat(timespec='seconds')}] {message}")

def normalize_ha_url(value: str) -> str:
    value = value.strip()
    urls = re.findall(r"https?://[^\]\)\s]+", value)

    if urls:
        return urls[-1].rstrip("/")

    return value.rstrip("/")

ha_url = normalize_ha_url(ha_url_raw)

def parse_local_dt(value: str) -> datetime:
    value = value.strip()
    fmt = "%d-%m-%Y %H:%M:%S"

    try:
        return datetime.strptime(value, fmt).replace(tzinfo=tz)
    except ValueError:
        raise ValueError(
            f"Invalid datetime format: {value}. "
            "Use DD-MM-YYYY HH:MM:SS."
        )

def load_ha_token() -> str:
    token = ha_token_env.strip()

    if token:
        log("HA token source: HA_TOKEN environment variable")
        log(f"HA token length: {len(token)} characters")
        return token

    if ha_token_file and os.path.isfile(ha_token_file):
        with open(ha_token_file, "r", encoding="utf-8") as handle:
            token = handle.read().strip()

        log(f"HA token source: {ha_token_file}")
        log(f"HA token length: {len(token)} characters")
        return token

    log("WARNING: No HA token found.")
    log(f"Expected token file: {ha_token_file}")
    return ""

def ha_api_request(path: str, payload=None, method="GET"):
    token = load_ha_token()

    if not token:
        return False, "No token available"

    url = f"{ha_url}{path}"

    headers = {
        "Authorization": f"Bearer {token}",
        "Content-Type": "application/json",
    }

    data = None

    if payload is not None:
        data = json.dumps(payload).encode("utf-8")

    log(f"Calling Home Assistant REST API: {method} {url}")

    request = urllib.request.Request(
        url,
        data=data,
        method=method,
        headers=headers,
    )

    try:
        with urllib.request.urlopen(request, timeout=10) as response:
            body = response.read().decode("utf-8", errors="replace")
            return True, f"HTTP {response.status}: {body}"
    except urllib.error.HTTPError as exc:
        body = exc.read().decode("utf-8", errors="replace")
        return False, f"HTTP {exc.code}: {body}"
    except Exception as exc:
        return False, str(exc)

def validate_ha_token() -> bool:
    log(f"Validating Home Assistant REST token via {ha_url}/api/")
    ok, response = ha_api_request("/api/", method="GET")

    if ok:
        log(f"HA token validation successful: {response}")
        return True

    log(f"WARNING: HA token validation failed: {response}")
    log("WARNING: Skipping notification to avoid repeated invalid-auth requests.")
    return False

def send_persistent_notification(title: str, message: str, notification_id: str):
    log("Preparing Home Assistant persistent notification.")

    if not validate_ha_token():
        return

    payload = {
        "title": title,
        "message": message,
        "notification_id": notification_id,
    }

    ok, response = ha_api_request(
        "/api/services/persistent_notification/create",
        payload=payload,
        method="POST",
    )

    if ok:
        log(f"Home Assistant notification sent successfully: {response}")
    else:
        log(f"WARNING: Failed to send Home Assistant notification: {response}")

log("Parsing requested interval.")
start_dt = parse_local_dt(interval_start_raw)
stop_dt = parse_local_dt(interval_stop_raw)

if stop_dt <= start_dt:
    raise ValueError("interval_stop must be after interval_start")

request_start_ts = start_dt.timestamp()
request_stop_ts = stop_dt.timestamp()

log(f"Requested interval start epoch: {request_start_ts}")
log(f"Requested interval stop epoch:  {request_stop_ts}")
log(f"Dependency enabled: {dependency_enabled}")
log(f"Normalized Home Assistant URL: {ha_url}")

log("Opening Home Assistant recorder SQLite database in read-only mode.")
conn = sqlite3.connect(f"file:{db_path}?mode=ro", uri=True)
conn.row_factory = sqlite3.Row
log("Database connection opened successfully.")

def entity_exists(entity_id: str) -> bool:
    log(f"Checking whether entity exists in states_meta: {entity_id}")
    sql = "SELECT 1 FROM states_meta WHERE entity_id = ? LIMIT 1"
    log(f"SQL entity_exists: {sql} | params=({entity_id!r},)")

    row = conn.execute(sql, (entity_id,)).fetchone()
    exists = row is not None

    log(f"Entity exists result for {entity_id}: {exists}")
    return exists

def get_first_state_ts(entity_id: str):
    log(f"Querying first known recorder timestamp for entity: {entity_id}")

    sql = """
        SELECT MIN(s.last_updated_ts) AS first_ts
        FROM states s
        JOIN states_meta sm ON sm.metadata_id = s.metadata_id
        WHERE sm.entity_id = ?
    """

    log(f"SQL get_first_state_ts: first known state timestamp | params=({entity_id!r},)")

    row = conn.execute(sql, (entity_id,)).fetchone()

    if row and row["first_ts"] is not None:
        first_ts = float(row["first_ts"])
        first_dt = datetime.fromtimestamp(first_ts, tz)
        log(f"First known state for {entity_id}: {first_ts} / {first_dt.isoformat(timespec='seconds')}")
        return first_ts

    log(f"No state rows found for {entity_id}.")
    return None

def get_friendly_name(entity_id: str) -> str:
    log(f"Querying friendly_name for entity: {entity_id}")

    sql = """
        SELECT sa.shared_attrs
        FROM states s
        JOIN states_meta sm ON sm.metadata_id = s.metadata_id
        LEFT JOIN state_attributes sa ON sa.attributes_id = s.attributes_id
        WHERE sm.entity_id = ?
          AND s.last_updated_ts <= ?
        ORDER BY s.last_updated_ts DESC
        LIMIT 1
    """

    log(
        "SQL get_friendly_name: latest state_attributes before interval stop "
        f"| params=({entity_id!r}, {request_stop_ts!r})"
    )

    try:
        row = conn.execute(sql, (entity_id, request_stop_ts)).fetchone()

        if row and row["shared_attrs"]:
            attrs = json.loads(row["shared_attrs"])
            friendly = attrs.get("friendly_name")

            if friendly:
                log(f"Friendly name found for {entity_id}: {friendly}")
                return friendly

        log(f"No friendly_name found for {entity_id}; using entity_id.")
    except Exception as exc:
        log(f"WARNING: Could not read friendly_name for {entity_id}: {exc}")

    return entity_id

def get_state_events(entity_id: str, effective_start_ts: float, effective_stop_ts: float):
    log(f"Loading state events for entity: {entity_id}")

    before_sql = """
        SELECT s.last_updated_ts, s.state
        FROM states s
        JOIN states_meta sm ON sm.metadata_id = s.metadata_id
        WHERE sm.entity_id = ?
          AND s.last_updated_ts <= ?
        ORDER BY s.last_updated_ts DESC
        LIMIT 1
    """

    log(
        "SQL get_state_events before interval: latest state at or before effective interval start "
        f"| params=({entity_id!r}, {effective_start_ts!r})"
    )

    before = conn.execute(before_sql, (entity_id, effective_start_ts)).fetchone()

    events = {}

    if before:
        events[effective_start_ts] = before["state"]
        log(
            f"Initial state at effective interval start for {entity_id}: "
            f"{before['state']!r} from recorder timestamp {before['last_updated_ts']}"
        )
    else:
        events[effective_start_ts] = None
        log(f"No previous state found for {entity_id}; initial state set to None.")

    rows_sql = """
        SELECT s.last_updated_ts, s.state
        FROM states s
        JOIN states_meta sm ON sm.metadata_id = s.metadata_id
        WHERE sm.entity_id = ?
          AND s.last_updated_ts > ?
          AND s.last_updated_ts < ?
        ORDER BY s.last_updated_ts ASC
    """

    log(
        "SQL get_state_events inside interval: all state changes inside effective interval "
        f"| params=({entity_id!r}, {effective_start_ts!r}, {effective_stop_ts!r})"
    )

    rows = conn.execute(rows_sql, (entity_id, effective_start_ts, effective_stop_ts)).fetchall()
    log(f"State change rows returned for {entity_id}: {len(rows)}")

    for row in rows:
        events[float(row["last_updated_ts"])] = row["state"]

    log(f"Total event points for {entity_id}, including synthetic effective interval start: {len(events)}")
    return events

log("Validating target entity presence in recorder metadata.")

if not entity_exists(target_entity):
    raise ValueError(f"Target entity not found in recorder metadata: {target_entity}")

if dependency_enabled:
    log("Validating dependence entity presence in recorder metadata.")

    if not entity_exists(dependence_entity):
        raise ValueError(f"Dependence entity not found in recorder metadata: {dependence_entity}")

target_first_state_ts = get_first_state_ts(target_entity)
sensor_created_inside_requested_interval = False

if target_first_state_ts is None:
    log("Target entity has no state rows. No CSV data rows will be generated.")
    effective_start_ts = request_stop_ts
elif request_start_ts < target_first_state_ts < request_stop_ts:
    sensor_created_inside_requested_interval = True
    effective_start_ts = target_first_state_ts
    log("Target sensor was created inside the requested interval.")
    log("Hours and days before target sensor creation will be ignored for all averages.")
else:
    effective_start_ts = request_start_ts
    log("Target sensor existed before the requested interval or was not created inside it.")

effective_stop_ts = request_stop_ts
effective_start_dt = datetime.fromtimestamp(effective_start_ts, tz)
effective_stop_dt = stop_dt

log(f"Effective interval start epoch: {effective_start_ts}")
log(f"Effective interval start local: {effective_start_dt.isoformat(timespec='seconds')}")
log(f"Effective interval stop epoch:  {effective_stop_ts}")
log(f"Sensor created inside requested interval: {sensor_created_inside_requested_interval}")

target_friendly_name = get_friendly_name(target_entity)

if effective_start_ts >= effective_stop_ts:
    log("Effective interval is empty. Output files will be created with headers and empty average values.")
    target_events = {}
    dependence_events = {}
    segments = []
else:
    log("Querying target entity state events for effective interval.")
    target_events = get_state_events(target_entity, effective_start_ts, effective_stop_ts)

    if dependency_enabled:
        log("Querying dependence entity state events for effective interval.")
        dependence_events = get_state_events(dependence_entity, effective_start_ts, effective_stop_ts)
    else:
        log("Dependency disabled; skipping dependence entity state query.")
        dependence_events = {}

    log("Combining target and dependence event timestamps into segment boundary points.")
    event_points = {effective_start_ts, effective_stop_ts}
    event_points.update(target_events.keys())
    event_points.update(dependence_events.keys())
    event_points = sorted(p for p in event_points if effective_start_ts <= p <= effective_stop_ts)

    log(f"Combined event boundary count: {len(event_points)}")

    segments = []
    current_target_state = None
    current_dependence_state = None

    log("Constructing continuous state segments from recorder event points.")

    for idx, point in enumerate(event_points[:-1]):
        if point in target_events:
            current_target_state = target_events[point]

        if dependency_enabled and point in dependence_events:
            current_dependence_state = dependence_events[point]

        next_point = event_points[idx + 1]

        if next_point > point:
            segments.append({
                "start": point,
                "stop": next_point,
                "target_state": current_target_state,
                "dependence_state": current_dependence_state,
            })

    log(f"Target events loaded: {len(target_events)}")

    if dependency_enabled:
        log(f"Dependence events loaded: {len(dependence_events)}")

    log(f"Segments created: {len(segments)}")

rows_out = []

if effective_start_ts < effective_stop_ts:
    hour_start_dt = effective_start_dt.replace(minute=0, second=0, microsecond=0)

    log("Generating hourly ratio rows.")

    while hour_start_dt < effective_stop_dt:
        hour_stop_dt = hour_start_dt + timedelta(hours=1)

        hour_start_ts = max(hour_start_dt.timestamp(), effective_start_ts)
        hour_stop_ts = min(hour_stop_dt.timestamp(), effective_stop_ts)

        numerator_seconds = 0.0
        denominator_seconds = 3600.0

        if hour_stop_ts > hour_start_ts:
            for segment in segments:
                overlap_start = max(hour_start_ts, segment["start"])
                overlap_stop = min(hour_stop_ts, segment["stop"])

                if overlap_stop <= overlap_start:
                    continue

                overlap_seconds = overlap_stop - overlap_start

                if dependency_enabled:
                    if (
                        segment["target_state"] == target_state
                        and segment["dependence_state"] == dependence_state
                    ):
                        numerator_seconds += overlap_seconds
                else:
                    if segment["target_state"] == target_state:
                        numerator_seconds += overlap_seconds

        ratio = round(numerator_seconds / denominator_seconds, 6)

        rows_out.append({
            "request_time": datetime.now(tz).isoformat(timespec="seconds"),
            "target_friendly_name": target_friendly_name,
            "target_entity": target_entity,
            "target_state": target_state,
            "dependence_entity": dependence_entity,
            "dependence_state": dependence_state,
            "requested_interval_start": interval_start_raw,
            "requested_interval_stop": interval_stop_raw,
            "effective_interval_start": effective_start_dt.strftime("%Y-%m-%d %H:%M:%S"),
            "sensor_created_inside_requested_interval": sensor_created_inside_requested_interval,
            "date": hour_start_dt.strftime("%Y-%m-%d"),
            "day_name": hour_start_dt.strftime("%A"),
            "hour_of_day": hour_start_dt.strftime("%H:00"),
            "hour_start": hour_start_dt.strftime("%Y-%m-%d %H:%M:%S"),
            "hour_stop": hour_stop_dt.strftime("%Y-%m-%d %H:%M:%S"),
            "numerator_seconds": round(numerator_seconds, 3),
            "denominator_seconds": 3600.0,
            "time_ratio": ratio,
            "daily_average_time_ratio": "",
        })

        log(
            f"Hourly row generated: {hour_start_dt.strftime('%Y-%m-%d %H:%M:%S')} "
            f"to {hour_stop_dt.strftime('%Y-%m-%d %H:%M:%S')}, "
            f"evaluated_from_epoch={hour_start_ts}, "
            f"evaluated_to_epoch={hour_stop_ts}, "
            f"numerator_seconds={round(numerator_seconds, 3)}, "
            f"ratio={ratio}"
        )

        hour_start_dt = hour_stop_dt
else:
    log("Skipping hourly row generation because effective interval is empty.")

log(f"Hourly rows generated: {len(rows_out)}")

log("Calculating daily average time ratios.")
daily_values = {}

for row in rows_out:
    date_key = row["date"]
    daily_values.setdefault(date_key, []).append(row["time_ratio"])

daily_averages = {}

for date_key, values in daily_values.items():
    if values:
        daily_averages[date_key] = round(sum(values) / len(values), 6)
    else:
        daily_averages[date_key] = ""

for row in rows_out:
    row["daily_average_time_ratio"] = daily_averages.get(row["date"], "")

for date_key in sorted(daily_averages):
    log(f"Daily average for {date_key}: {daily_averages[date_key]}")

all_ratios = [row["time_ratio"] for row in rows_out]

if all_ratios:
    overall_average_time_ratio = round(sum(all_ratios) / len(all_ratios), 6)
else:
    overall_average_time_ratio = ""

log(f"Overall average time ratio: {overall_average_time_ratio}")

fieldnames = [
    "request_time",
    "target_friendly_name",
    "target_entity",
    "target_state",
    "dependence_entity",
    "dependence_state",
    "requested_interval_start",
    "requested_interval_stop",
    "effective_interval_start",
    "sensor_created_inside_requested_interval",
    "date",
    "day_name",
    "hour_of_day",
    "hour_start",
    "hour_stop",
    "numerator_seconds",
    "denominator_seconds",
    "time_ratio",
    "daily_average_time_ratio",
]

log(f"Writing CSV output to: {csv_output_path}")
log(f"CSV columns: {', '.join(fieldnames)}")

with open(csv_output_path, "w", newline="", encoding="utf-8") as handle:
    writer = csv.DictWriter(handle, fieldnames=fieldnames)
    writer.writeheader()
    writer.writerows(rows_out)

log(f"CSV rows written: {len(rows_out)}")
log("Preparing summary data structures.")

dates = sorted({row["date"] for row in rows_out})
hours = [f"{hour:02d}:00" for hour in range(24)]

hour_day_matrix = {
    hour: {date: "" for date in dates}
    for hour in hours
}

for row in rows_out:
    hour_day_matrix[row["hour_of_day"]][row["date"]] = row["time_ratio"]

day_name_by_date = {}

for row in rows_out:
    day_name_by_date[row["date"]] = row["day_name"]

log(f"Summary dates: {dates}")
log("Writing summary text file.")

with open(summary_output_path, "w", encoding="utf-8") as handle:
    handle.write("Home Assistant State Ratio Export Summary\n")
    handle.write("========================================\n\n")

    handle.write("Request Metadata\n")
    handle.write("----------------\n")
    handle.write(f"Request time: {datetime.now(tz).isoformat(timespec='seconds')}\n")
    handle.write(f"Target friendly name: {target_friendly_name}\n")
    handle.write(f"Target entity: {target_entity}\n")
    handle.write(f"Target state: {target_state}\n")
    handle.write(f"Dependence entity: {dependence_entity}\n")
    handle.write(f"Dependence state: {dependence_state}\n")
    handle.write(f"Dependency enabled: {dependency_enabled}\n")
    handle.write(f"Requested interval start: {start_dt.strftime('%Y-%m-%d %H:%M:%S %Z')}\n")
    handle.write(f"Requested interval stop: {stop_dt.strftime('%Y-%m-%d %H:%M:%S %Z')}\n")
    handle.write(f"Effective interval start: {effective_start_dt.strftime('%Y-%m-%d %H:%M:%S %Z')}\n")
    handle.write(f"Effective interval stop: {effective_stop_dt.strftime('%Y-%m-%d %H:%M:%S %Z')}\n")
    handle.write(f"Target first recorder timestamp: {target_first_state_ts}\n")
    handle.write(f"Sensor created inside requested interval: {sensor_created_inside_requested_interval}\n")
    handle.write(f"Database path: {db_path}\n")
    handle.write(f"Output folder: {output_dir}\n\n")

    handle.write("Overall Average Time Ratio\n")
    handle.write("--------------------------\n")
    handle.write(f"{overall_average_time_ratio}\n\n")

    handle.write("Daily Average Time Ratio\n")
    handle.write("------------------------\n")
    handle.write(f"{'Date':<12} {'Day':<12} {'Average Time Ratio':>20}\n")
    handle.write(f"{'-' * 12} {'-' * 12} {'-' * 20}\n")

    for date_key in dates:
        handle.write(
            f"{date_key:<12} "
            f"{day_name_by_date.get(date_key, ''):<12} "
            f"{daily_averages.get(date_key, ''):>20}\n"
        )

    handle.write("\n")

    handle.write("Hourly Time Ratio Matrix\n")
    handle.write("------------------------\n")
    handle.write("Rows are hours of the day. Columns are dates.\n")
    handle.write("Hours before target sensor creation are omitted.\n")
    handle.write("The second header line contains the weekday name for each date.\n\n")

    first_col_width = 8
    col_width = 14

    date_header = f"{'Hour':<{first_col_width}}"
    day_header = f"{'':<{first_col_width}}"

    for date_key in dates:
        date_header += f"{date_key:^{col_width}}"
        day_header += f"{day_name_by_date.get(date_key, ''):^{col_width}}"

    handle.write(date_header + "\n")
    handle.write(day_header + "\n")

    separator = f"{'-' * first_col_width}"

    for _ in dates:
        separator += f"{'-' * col_width}"

    handle.write(separator + "\n")

    for hour in hours:
        line = f"{hour:<{first_col_width}}"

        for date_key in dates:
            value = hour_day_matrix[hour][date_key]
            line += f"{str(value):>{col_width}}"

        handle.write(line + "\n")

log(f"Summary file written: {summary_output_path}")

log("Output generation completed.")
log(f"Output folder: {output_dir}")
log(f"CSV output path: {csv_output_path}")
log(f"Summary output path: {summary_output_path}")
log(f"Log output path: {log_file}")

notification_title = "State ratio export finished"

notification_message = (
    f"State ratio export finished successfully.\n\n"
    f"Target: {target_friendly_name} ({target_entity})\n"
    f"Target state: {target_state}\n"
    f"Interval: {interval_start_raw} to {interval_stop_raw}\n"
    f"Effective start: {effective_start_dt.strftime('%Y-%m-%d %H:%M:%S')}\n"
    f"Sensor created inside interval: {sensor_created_inside_requested_interval}\n"
    f"Overall average time ratio: {overall_average_time_ratio}\n\n"
    f"Output folder:\n{output_dir}\n\n"
    f"CSV file:\n{csv_output_path}\n\n"
    f"Summary file:\n{summary_output_path}\n\n"
    f"Log file:\n{log_file}"
)

notification_id = f"state_ratio_export_{request_ts}"

send_persistent_notification(
    title=notification_title,
    message=notification_message,
    notification_id=notification_id,
)

log("Closing database connection.")
conn.close()
log("Database connection closed.")
PY

STATUS=$?

if [[ "$STATUS" -eq 0 ]]; then
  echo "[$(date -Iseconds)] Export finished successfully."
else
  echo "[$(date -Iseconds)] Export failed with status $STATUS."
fi

exit "$STATUS"