#!/usr/bin/env python3
"""Create an isolated PostgreSQL copy of FilyX without touching MariaDB.

The script is run as root only on the VPS. Credentials are read from FilyX's
existing protected .env file and are never printed. The generated staging
credential and pgloader file are private to root/www-data in the backup folder.
"""
from __future__ import annotations

import os
import secrets
import subprocess
import sys
from pathlib import Path
from urllib.parse import quote

ENV_PATH = Path('/srv/filyx/.env')
BACKUP_DIR = Path('/srv/filyx/backups/postgres-migration-20261015')
STAGE_DB = 'filyx_stage'
STAGE_ROLE = 'filyx_stage'


def env_file(path: Path) -> dict[str, str]:
    values: dict[str, str] = {}
    for raw in path.read_text(encoding='utf-8').splitlines():
        line = raw.strip()
        if not line or line.startswith('#') or '=' not in line:
            continue
        key, value = line.split('=', 1)
        value = value.strip()
        if len(value) >= 2 and value[0] == value[-1] and value[0] in {'"', "'"}:
            value = value[1:-1]
        values[key.strip()] = value
    return values


def run(*args: str) -> None:
    subprocess.run(args, check=True, stdout=subprocess.DEVNULL, stderr=subprocess.PIPE)


def psql(sql: str) -> None:
    run('runuser', '-u', 'postgres', '--', 'psql', '-v', 'ON_ERROR_STOP=1', '-d', 'postgres', '-c', sql)


def sql_literal(value: str) -> str:
    return "'" + value.replace("'", "''") + "'"


def main() -> int:
    config = env_file(ENV_PATH)
    required = ('DB_HOST', 'DB_PORT', 'DB_NAME', 'DB_USER', 'DB_PASS')
    if any(not config.get(key) for key in required):
        raise RuntimeError('Configuration MariaDB incomplète.')

    BACKUP_DIR.mkdir(mode=0o750, parents=True, exist_ok=True)
    password = secrets.token_hex(32)
    psql(f'DROP DATABASE IF EXISTS {STAGE_DB}')
    psql(f'DROP ROLE IF EXISTS {STAGE_ROLE}')
    psql(f'CREATE ROLE {STAGE_ROLE} LOGIN PASSWORD {sql_literal(password)}')
    psql(f'CREATE DATABASE {STAGE_DB} OWNER {STAGE_ROLE}')

    source = 'mysql://{user}:{password}@{host}:{port}/{database}'.format(
        user=quote(config['DB_USER'], safe=''),
        password=quote(config['DB_PASS'], safe=''),
        host=config['DB_HOST'],
        port=config['DB_PORT'],
        database=quote(config['DB_NAME'], safe=''),
    )
    target = 'postgresql://{user}:{password}@127.0.0.1:5432/{database}'.format(
        user=quote(STAGE_ROLE, safe=''),
        password=quote(password, safe=''),
        database=STAGE_DB,
    )
    load_file = BACKUP_DIR / 'filyx-stage.load'
    load_file.write_text(
        'LOAD DATABASE\n'
        f'  FROM {source}\n'
        f'  INTO {target}\n\n'
        # A conservative single-worker profile keeps the staging conversion
        # below the VPS memory budget; it does not change the source database.
        ' WITH include no drop, create tables, create indexes, reset sequences,\n'
        '      batch rows = 1000, batch size = 4MB, prefetch rows = 1000,\n'
        '      workers = 1, concurrency = 1\n'
        ' SET work_mem to \'32MB\', maintenance_work_mem to \'256MB\';\n',
        encoding='utf-8',
    )
    os.chmod(load_file, 0o600)
    (BACKUP_DIR / 'filyx-stage.env').write_text(
        f'PGHOST=127.0.0.1\nPGPORT=5432\nPGDATABASE={STAGE_DB}\nPGUSER={STAGE_ROLE}\nPGPASSWORD={password}\n',
        encoding='utf-8',
    )
    os.chmod(BACKUP_DIR / 'filyx-stage.env', 0o600)

    log_file = BACKUP_DIR / 'pgloader-stage.log'
    # Stream the loader output to disk. This lets the isolated copy finish
    # safely even when the administrative SSH session closes.
    with log_file.open('w', encoding='utf-8') as log:
        # pgloader's packaged SBCL heap defaults are too small for FilyX's
        # event tables. Allocate an explicit, bounded heap on the VPS; the
        # loader remains low priority and MariaDB stays online.
        result = subprocess.run(['nice', '-n', '15', 'pgloader', '--dynamic-space-size', '3072', str(load_file)], stdout=log, stderr=subprocess.STDOUT, text=True)
    os.chmod(log_file, 0o640)
    if result.returncode != 0:
        raise RuntimeError('La copie PostgreSQL a échoué ; MariaDB est inchangée.')
    print('STAGING_POSTGRESQL_COPY_OK')
    return 0


if __name__ == '__main__':
    try:
        raise SystemExit(main())
    except Exception as exc:
        print(f'STAGING_POSTGRESQL_COPY_FAILED: {exc}', file=sys.stderr)
        raise SystemExit(1)

