from datetime import datetime, timezone
import json
from pathlib import Path
import shutil
import sqlite3
from typing import Dict

from database.paths import BACKUP_ROOT, L1_DB, L2_DB, L3_DB


MANAGED_DATABASES = {
    'l1': L1_DB,
    'l2': L2_DB,
    'l3': L3_DB,
}


def quick_check(path: Path) -> str:
    if not path.exists():
        return 'missing'
    db = sqlite3.connect(str(path))
    try:
        return str(db.execute('PRAGMA quick_check').fetchone()[0])
    finally:
        db.close()


def backup_database(source_path: Path, backup_path: Path):
    if not source_path.exists():
        raise FileNotFoundError(f'Database does not exist: {source_path}')

    backup_path.parent.mkdir(parents=True, exist_ok=True)
    source = sqlite3.connect(str(source_path))
    destination = sqlite3.connect(str(backup_path))
    try:
        source.backup(destination)
        result = destination.execute('PRAGMA quick_check').fetchone()[0]
        if result != 'ok':
            raise RuntimeError(
                f'Backup quick_check failed for {source_path.name}: {result}'
            )
    finally:
        source.close()
        destination.close()


def restore_database(backup_path: Path, target_path: Path):
    if not backup_path.exists():
        raise FileNotFoundError(f'Backup does not exist: {backup_path}')

    source = sqlite3.connect(str(backup_path))
    target = sqlite3.connect(str(target_path), timeout=30)
    try:
        source.backup(target)
        result = target.execute('PRAGMA quick_check').fetchone()[0]
        if result != 'ok':
            raise RuntimeError(
                f'Restored quick_check failed for {target_path.name}: {result}'
            )
    finally:
        source.close()
        target.close()


def create_backup_set(label: str) -> Path:
    safe_label = ''.join(
        character for character in str(label)
        if character.isalnum() or character in {'-', '_'}
    )
    if not safe_label:
        raise ValueError('Backup label is empty after sanitization')

    backup_dir = BACKUP_ROOT / safe_label
    backup_dir.mkdir(parents=True, exist_ok=True)
    manifest: Dict[str, object] = {
        'label': safe_label,
        'created_at': datetime.now(timezone.utc).isoformat(),
        'databases': {},
    }

    for name, source_path in MANAGED_DATABASES.items():
        backup_path = backup_dir / source_path.name
        backup_database(source_path, backup_path)
        manifest['databases'][name] = {
            'source': str(source_path),
            'backup': str(backup_path),
            'size_bytes': backup_path.stat().st_size,
            'quick_check': quick_check(backup_path),
        }

    with (backup_dir / 'manifest.json').open('w', encoding='utf-8') as file:
        json.dump(manifest, file, ensure_ascii=True, indent=2)
    return backup_dir


def restore_backup_set(backup_dir: Path):
    for name, target_path in MANAGED_DATABASES.items():
        backup_path = backup_dir / target_path.name
        restore_database(backup_path, target_path)


def prune_backup_sets(keep: int = 3):
    BACKUP_ROOT.mkdir(parents=True, exist_ok=True)
    backup_dirs = sorted(
        [path for path in BACKUP_ROOT.iterdir() if path.is_dir()],
        key=lambda path: path.stat().st_mtime,
        reverse=True,
    )
    removed = []
    for path in backup_dirs[max(int(keep), 0):]:
        shutil.rmtree(path)
        removed.append(str(path))
    return removed


def backup_storage_status():
    BACKUP_ROOT.mkdir(parents=True, exist_ok=True)
    backup_dirs = [path for path in BACKUP_ROOT.iterdir() if path.is_dir()]
    total_bytes = sum(
        file.stat().st_size
        for directory in backup_dirs
        for file in directory.rglob('*')
        if file.is_file()
    )
    return {
        'sets': len(backup_dirs),
        'total_bytes': total_bytes,
    }


def check_managed_databases():
    return {
        name: {
            'path': str(path),
            'exists': path.exists(),
            'size_bytes': path.stat().st_size if path.exists() else 0,
            'quick_check': quick_check(path),
        }
        for name, path in MANAGED_DATABASES.items()
    }