"""Consistent SQLite backups with SHA-256 manifests, verification and retention.

Backups use SQLite's online backup API, so they are safe while the app runs.
Each ``<name>.db`` has a sidecar ``<name>.json`` manifest recording the hash,
size, schema version, app version and reason. Restore is CLI-only
(``python manage.py restore``) and must run while the server is stopped.
"""
import hashlib
import json
import os
import re
import shutil
import sqlite3
from datetime import datetime, timezone

NAME_PATTERN = re.compile(r'^[A-Za-z0-9_.-]+\.db$')


def backup_dir(data_dir):
    path = os.path.join(data_dir, 'backups')
    os.makedirs(path, exist_ok=True)
    return path


def file_sha256(path):
    digest = hashlib.sha256()
    with open(path, 'rb') as handle:
        for chunk in iter(lambda: handle.read(1024 * 1024), b''):
            digest.update(chunk)
    return digest.hexdigest()


def _schema_version(path):
    conn = sqlite3.connect(f'file:{path}?mode=ro', uri=True)
    try:
        return conn.execute('SELECT COALESCE(MAX(version),0) FROM schema_migrations').fetchone()[0]
    except sqlite3.DatabaseError:
        return 0
    finally:
        conn.close()


def create_backup(db_path, data_dir, reason='manual', app_version='', actor=''):
    """Create a consistent copy of ``db_path`` and its manifest. Returns the manifest."""
    if not os.path.exists(db_path):
        raise FileNotFoundError(db_path)
    stamp = datetime.now(timezone.utc).strftime('%Y%m%d_%H%M%S_%fZ')
    base = os.path.splitext(os.path.basename(db_path))[0]
    safe_reason = re.sub(r'[^a-z0-9-]+', '-', reason.lower()).strip('-') or 'manual'
    name = f'{base}_{stamp}_{safe_reason}.db'
    target = os.path.join(backup_dir(data_dir), name)
    source = sqlite3.connect(db_path)
    destination = sqlite3.connect(target)
    try:
        source.backup(destination)
    finally:
        destination.close()
        source.close()
    manifest = {
        'file': name,
        'source': os.path.basename(db_path),
        'created_at': datetime.now(timezone.utc).strftime('%Y-%m-%d %H:%M:%S'),
        'reason': safe_reason,
        'actor': actor or 'system',
        'app_version': app_version,
        'schema_version': _schema_version(target),
        'size_bytes': os.path.getsize(target),
        'sha256': file_sha256(target),
    }
    with open(target[:-3] + '.json', 'w', encoding='utf-8') as handle:
        json.dump(manifest, handle, indent=2)
    return manifest


def list_backups(data_dir):
    folder = backup_dir(data_dir)
    items = []
    for name in sorted(os.listdir(folder), reverse=True):
        if not NAME_PATTERN.match(name):
            continue
        manifest_path = os.path.join(folder, name[:-3] + '.json')
        manifest = {'file': name, 'size_bytes': os.path.getsize(os.path.join(folder, name))}
        if os.path.exists(manifest_path):
            with open(manifest_path, encoding='utf-8') as handle:
                manifest.update(json.load(handle))
        items.append(manifest)
    return items


def resolve_backup(data_dir, name):
    """Map a user-supplied backup name to a path inside the backup folder only."""
    if not NAME_PATTERN.match(name or ''):
        raise ValueError('Invalid backup name.')
    path = os.path.join(backup_dir(data_dir), name)
    if not os.path.exists(path):
        raise FileNotFoundError(name)
    return path


def verify_backup(data_dir, name):
    """Recompute the hash and run SQLite's integrity check on the backup copy."""
    path = resolve_backup(data_dir, name)
    manifest_path = path[:-3] + '.json'
    expected = None
    if os.path.exists(manifest_path):
        with open(manifest_path, encoding='utf-8') as handle:
            expected = json.load(handle).get('sha256')
    actual = file_sha256(path)
    conn = sqlite3.connect(f'file:{path}?mode=ro', uri=True)
    try:
        integrity = conn.execute('PRAGMA integrity_check').fetchone()[0]
    finally:
        conn.close()
    return {'file': name, 'hash_matches': expected == actual, 'expected_sha256': expected,
            'actual_sha256': actual, 'integrity': integrity,
            'ok': expected == actual and integrity == 'ok'}


def prune_backups(data_dir, keep):
    """Retain the newest ``keep`` backups; manual-reason backups are never pruned."""
    removed = []
    automatic = [b for b in list_backups(data_dir) if b.get('reason') != 'manual']
    for item in automatic[keep:]:
        path = os.path.join(backup_dir(data_dir), item['file'])
        for target in (path, path[:-3] + '.json'):
            if os.path.exists(target):
                os.remove(target)
        removed.append(item['file'])
    return removed


def restore_backup(db_path, data_dir, name, app_version=''):
    """Verify, snapshot the current DB, then replace it. Server must be stopped."""
    check = verify_backup(data_dir, name)
    if not check['ok']:
        raise RuntimeError(f'Backup failed verification: {check}')
    safety = None
    if os.path.exists(db_path):
        safety = create_backup(db_path, data_dir, reason='pre-restore', app_version=app_version)
    for suffix in ('-wal', '-shm'):
        if os.path.exists(db_path + suffix):
            os.remove(db_path + suffix)
    shutil.copy2(resolve_backup(data_dir, name), db_path)
    return {'restored': name, 'safety_backup': safety['file'] if safety else None}
