"""
backup_service.py — copies de sécurité automatiques de la base MySQL.

Toutes les 24 h (et une fois au démarrage), écrit BACKUPS_DIR/meeconic_YYYYMMDD_HHMM.sql.gz : un dump SQL
(structure + données) fait en Python, donc sans dépendre du binaire `mysqldump` sur le serveur. Il se
restaure avec `gunzip -c fichier.sql.gz | mysql meeconic`. Purge les copies de plus de BACKUP_RETENTION_DAYS
jours. Un échec déclenche une alerte e-mail. (Les photos ne sont volontairement pas sauvegardées : les
originaux restent sur le serveur FTP du photographe.)
"""
import asyncio
import gzip
import logging
import time
from decimal import Decimal
from pathlib import Path

from core.config import BACKUPS_DIR
from core.db import get_db
from core.timeutil import utcnow
from services import alert_service

log = logging.getLogger("meeconic.backup")

_BACKUP_INTERVAL = 86400  # 24 h
BACKUP_RETENTION_DAYS = 14
_BATCH = 500

last_backup_at: str | None = None

# MySQL string-literal escapes (default sql_mode): backslash first, then the rest.
_ESCAPES = (("\\", "\\\\"), ("'", "''"), ("\n", "\\n"), ("\r", "\\r"), ("\x00", "\\0"), ("\x1a", "\\Z"))


def _sql_literal(v) -> str:
    if v is None:
        return "NULL"
    if isinstance(v, bool):
        return "1" if v else "0"
    if isinstance(v, (int, float, Decimal)):
        return str(v)
    if isinstance(v, (bytes, bytearray)):
        return "0x" + bytes(v).hex() if v else "''"
    s = str(v)
    for raw, esc in _ESCAPES:
        s = s.replace(raw, esc)
    return f"'{s}'"


def _backup_once() -> Path | None:
    BACKUPS_DIR.mkdir(parents=True, exist_ok=True)
    dest = BACKUPS_DIR / f"meeconic_{utcnow().strftime('%Y%m%d_%H%M')}.sql.gz"
    tmp = dest.with_name(dest.name + ".tmp")
    with get_db() as conn, gzip.open(tmp, "wt", encoding="utf-8", newline="\n") as out:
        out.write("SET FOREIGN_KEY_CHECKS=0;\nSET NAMES utf8mb4;\n")
        tables = [list(r.values())[0] for r in conn.execute("SHOW TABLES").fetchall()]
        for table in tables:
            create = conn.execute(f"SHOW CREATE TABLE `{table}`").fetchone()
            out.write(f"\nDROP TABLE IF EXISTS `{table}`;\n{list(create.values())[1]};\n")
            rows = conn.execute(f"SELECT * FROM `{table}`").fetchall()
            for k in range(0, len(rows), _BATCH):
                chunk = rows[k:k + _BATCH]
                cols = ", ".join(f"`{c}`" for c in chunk[0].keys())
                values = ",\n".join("(" + ", ".join(_sql_literal(v) for v in r.values()) + ")" for r in chunk)
                out.write(f"INSERT INTO `{table}` ({cols}) VALUES\n{values};\n")
        out.write("\nSET FOREIGN_KEY_CHECKS=1;\n")
    tmp.replace(dest)           # never leaves a half-written file under the final name
    return dest


def _purge_old() -> int:
    if not BACKUPS_DIR.is_dir():
        return 0
    cutoff = time.time() - BACKUP_RETENTION_DAYS * 86400
    deleted = 0
    for f in BACKUPS_DIR.glob("meeconic_*.sql.gz"):
        try:
            if f.stat().st_mtime < cutoff:
                f.unlink()
                deleted += 1
        except OSError as e:
            log.warning("Suppression backup impossible %s: %s", f, e)
    return deleted


def run_once() -> None:
    global last_backup_at
    dest = _backup_once()
    if dest:
        last_backup_at = utcnow().isoformat()
        log.info("DB backup created: %s", dest.name)
    purged = _purge_old()
    if purged:
        log.info("%d old backup(s) purged (> %d days)", purged, BACKUP_RETENTION_DAYS)


async def run_backup_loop() -> None:
    while True:
        try:
            await asyncio.to_thread(run_once)
        except asyncio.CancelledError:
            raise
        except Exception as e:
            log.error("Erreur backup DB: %s", e, exc_info=True)
            alert_service.notify("backup-failed", "Le backup de la base a échoué", str(e))
        await asyncio.sleep(_BACKUP_INTERVAL)