"""
Frank-relay: read-only file relay for Nave ↔ Ufficio cross-machine requests.

Nave scrive richieste in tasks/to-frank/richiesta-<id>.json via Dropbox.
Frank le legge, esegue azioni allowlisted (sola lettura), scrive la risposta
in tasks/from-frank/risposta-<id>.json. Dropbox sincronizza automaticamente.

Allowlist azioni (sola lettura, nessuna scrittura):
  list_dir     — elenca directory
  read_file    — legge file testo (max 200KB)
  glob         — ricerca pattern ricorsiva
  duckdb_query — esegue SQL read-only via DuckDB (solo SELECT/WITH, blocca keyword di
                 scrittura/comando, nessuna query multipla); params: sql, params (lista
                 opzionale di bind), limit (default 1000 righe)

Nessuna restrizione sui path — tutto ciò che è visibile dal PC ufficio è accessibile.
"""

import json
import glob as _glob_module
from datetime import datetime, timezone
from pathlib import Path
from typing import Any

import duckdb
from loguru import logger

# Frank root (daemon/ → Frank/)
_FRANK_ROOT = Path(__file__).parent.parent
# tasks/ è in adrian/tasks/ — due livelli sopra Frank (PYTHON/Frank → PYTHON → adrian)
_TASKS_ROOT = _FRANK_ROOT.parent.parent / 'tasks'
_TO_FRANK = _TASKS_ROOT / 'to-frank'
_FROM_FRANK = _TASKS_ROOT / 'from-frank'

READ_FILE_MAX_BYTES = 200 * 1024  # 200 KB

ALLOWED_ACTIONS = {'list_dir', 'read_file', 'glob', 'duckdb_query'}

_DUCKDB_WRITE_KEYWORDS = {
    'INSERT', 'UPDATE', 'DELETE', 'DROP', 'CREATE', 'ALTER', 'TRUNCATE',
    'REPLACE', 'MERGE', 'COPY', 'EXPORT', 'CALL', 'PRAGMA', 'ATTACH', 'DETACH',
}


def _now_iso() -> str:
    return datetime.now(timezone.utc).astimezone().isoformat()


def _execute(action: str, params: dict) -> Any:
    if action == 'list_dir':
        path = Path(params['path'])
        entries = []
        for entry in sorted(path.iterdir()):
            stat = entry.stat()
            entries.append({
                'name': entry.name,
                'is_dir': entry.is_dir(),
                'size': stat.st_size if not entry.is_dir() else None,
                'mtime': datetime.fromtimestamp(stat.st_mtime).isoformat(),
            })
        return entries

    if action == 'read_file':
        path = Path(params['path'])
        size = path.stat().st_size
        if size > READ_FILE_MAX_BYTES:
            raise ValueError(f"File {path.name}: {size} bytes > limite {READ_FILE_MAX_BYTES} bytes")
        encoding = params.get('encoding', 'utf-8')
        return path.read_text(encoding=encoding, errors='replace')

    if action == 'glob':
        pattern = params['pattern']
        base = params.get('base')
        if base:
            return [str(p) for p in sorted(Path(base).rglob(pattern))]
        return sorted(_glob_module.glob(pattern, recursive=True))

    if action == 'duckdb_query':
        sql = params['sql'].strip()
        limit = int(params.get('limit', 1000))
        query_params = params.get('params', [])

        # Solo SELECT o WITH come prima keyword
        first_word = sql.upper().split()[0] if sql else ''
        if first_word not in ('SELECT', 'WITH'):
            raise ValueError(f"Solo SELECT/WITH ammessi come inizio query (trovato: '{first_word}')")

        # Nessuna query multipla: niente ; prima della fine
        sql_stripped = sql.rstrip().rstrip(';')
        if ';' in sql_stripped:
            raise ValueError("Query multiple non ammesse (';' interno non consentito)")

        # Blocca keyword di scrittura/comando (word boundary)
        import re as _re
        sql_upper = sql_stripped.upper()
        for kw in _DUCKDB_WRITE_KEYWORDS:
            if _re.search(r'\b' + kw + r'\b', sql_upper):
                raise ValueError(f"Keyword non ammessa: '{kw}'")

        rel = duckdb.execute(sql_stripped, query_params)
        cols = [d[0] for d in rel.description]
        rows = rel.fetchmany(limit)
        return {
            'columns': cols,
            'rows': [list(r) for r in rows],
            'truncated': len(rows) == limit,
        }

    raise ValueError(f"Azione non riconosciuta: {action}")


def process_pending_requests() -> int:
    """
    Processa tutte le richieste pendenti in to-frank/ senza risposta in from-frank/.
    Ritorna il numero di richieste processate in questo ciclo.
    """
    if not _TO_FRANK.exists():
        return 0

    _FROM_FRANK.mkdir(parents=True, exist_ok=True)

    processed = 0
    for req_file in sorted(_TO_FRANK.glob('richiesta-*.json')):
        req_id = req_file.stem.removeprefix('richiesta-')
        resp_file = _FROM_FRANK / f'risposta-{req_id}.json'

        if resp_file.exists():
            continue  # già gestita

        try:
            req = json.loads(req_file.read_text(encoding='utf-8'))
        except Exception as e:
            logger.error(f"Frank-relay: impossibile leggere {req_file.name}: {e}")
            continue

        action = req.get('action', '')
        params = req.get('params', {})
        req_id_body = req.get('id', req_id)

        if action not in ALLOWED_ACTIONS:
            resp = {
                'id': req_id_body,
                'timestamp': _now_iso(),
                'status': 'denied',
                'error': f"Azione '{action}' non in allowlist {sorted(ALLOWED_ACTIONS)}",
            }
            logger.warning(f"Frank-relay: azione negata '{action}' (id={req_id_body})")
        else:
            try:
                result = _execute(action, params)
                resp = {
                    'id': req_id_body,
                    'timestamp': _now_iso(),
                    'status': 'ok',
                    'result': result,
                }
                logger.info(f"Frank-relay: OK {action} (id={req_id_body})")
            except Exception as e:
                resp = {
                    'id': req_id_body,
                    'timestamp': _now_iso(),
                    'status': 'error',
                    'error': str(e),
                }
                logger.warning(f"Frank-relay: errore {action} (id={req_id_body}): {e}")

        resp_file.write_text(json.dumps(resp, ensure_ascii=False, indent=2), encoding='utf-8')
        processed += 1

    return processed