"""Backups + retention / downsampling for farm.db (session 20).

python backup.py                    # hot backup to backups/farm-YYYYmmdd-HHMM.db, keep the last 14
python backup.py --downsample 90    # move raw readings older than 90 days into hourly averages
python backup.py --verify FILE      # open a backup and print its row counts (restore test)
Schedule it daily (cron / Task Scheduler). Copy the backups folder to a second disk or USB weekly.
"""
import argparse
import sqlite3
from datetime import datetime, timedelta
from pathlib import Path

import config

BACKUP_DIR = config.BASE_DIR / "backups"
KEEP = 14


def hot_backup():
    """SQLite online backup API: consistent copy even while app.py / mqtt_ingest.py are writing."""
    BACKUP_DIR.mkdir(exist_ok=True)
    dest = BACKUP_DIR / f"farm-{datetime.now():%Y%m%d-%H%M%S}.db"
    src = sqlite3.connect(config.DB_PATH)
    dst = sqlite3.connect(dest)
    with dst:
        src.backup(dst)
    src.close(); dst.close()
    old = sorted(BACKUP_DIR.glob("farm-*.db"))[:-KEEP]
    for f in old:
        f.unlink()
    print(f"backup -> {dest.name} ({dest.stat().st_size / 1024:.0f} KB), removed {len(old)} old file(s)")
    return dest


def downsample(days):
    """Keep full resolution for recent data, hourly averages for the archive (smaller DB, same trends)."""
    cutoff = (datetime.now() - timedelta(days=days)).isoformat(timespec="seconds")
    con = sqlite3.connect(config.DB_PATH)
    con.executescript("""CREATE TABLE IF NOT EXISTS readings_hourly (
        node_id TEXT, hour TEXT, air_temp_c REAL, air_humidity_pct REAL, soil_moisture_pct REAL,
        light REAL, battery_v REAL, n INTEGER, PRIMARY KEY (node_id, hour));""")
    with con:
        con.execute("""INSERT OR REPLACE INTO readings_hourly
            SELECT node_id, substr(ts, 1, 13) || ':00:00', AVG(air_temp_c), AVG(air_humidity_pct),
                   AVG(soil_moisture_pct), AVG(light), AVG(battery_v), COUNT(*)
            FROM readings WHERE ts < ? GROUP BY node_id, substr(ts, 1, 13)""", (cutoff,))
        moved = con.execute("DELETE FROM readings WHERE ts < ?", (cutoff,)).rowcount
    con.execute("VACUUM")
    con.close()
    print(f"downsampled readings older than {days} days: {moved} raw rows -> hourly archive")


def verify(path):
    con = sqlite3.connect(path)
    for t in ("nodes", "readings", "commands", "decisions"):
        n = con.execute(f"SELECT COUNT(*) FROM {t}").fetchone()[0]
        print(f"{t:10s} {n:>8d} rows")
    print("integrity:", con.execute("PRAGMA integrity_check").fetchone()[0])
    con.close()


if __name__ == "__main__":
    ap = argparse.ArgumentParser()
    ap.add_argument("--downsample", type=int)
    ap.add_argument("--verify")
    a = ap.parse_args()
    if a.verify:
        verify(a.verify)
    elif a.downsample:
        hot_backup()                 # always back up before deleting anything
        downsample(a.downsample)
    else:
        hot_backup()