# src/utils.py
import os, sqlite3, shutil, logging, sys
from pathlib import Path

# ---- logging (goes to HF Runtime logs) ----
LOG_LEVEL = os.getenv("LOG_LEVEL", "INFO").upper()
logging.basicConfig(
    stream=sys.stdout,
    level=getattr(logging, LOG_LEVEL, logging.INFO),
    format="%(asctime)s %(levelname)s [utils] %(message)s",
    force=True,
)
ulog = logging.getLogger("utils")

# Repo root (/home/user/app)
APP_DIR = Path(__file__).resolve().parents[1]
PERSIST = Path("/data/olist.sqlite")                   # preferred persistent path
FALLBACK = APP_DIR / "olist.sqlite"                    # non-persistent fallback
ENV_DB = os.environ.get("DB_PATH")  

# Single source of truth for DB location:
# - Default to Hugging Face persistent volume at /data
# - Can be overridden via env var DB_PATH if needed
#DB_PATH = Path(os.environ.get("DB_PATH", "/data/olist.sqlite"))

def _can_write_dir(d: Path) -> bool:
    try:
        d.mkdir(parents=True, exist_ok=True)
        test = d / ".perm_test"
        with open(test, "wb") as f:
            f.write(b"ok")
        test.unlink()
        return True
    except Exception as e:
        ulog.warning("perm: cannot write to %s: %s", d, e)
        return False

def _choose_path() -> Path:
    if ENV_DB:
        p = Path(ENV_DB).resolve()
        # if file exists OR parent is writable, we’ll use it
        if p.exists() or _can_write_dir(p.parent):
            return p
        ulog.warning("db: DB_PATH=%s not writable; falling back…", p)
    # prefer /data if writable or already exists
    if PERSIST.exists() or _can_write_dir(PERSIST.parent):
        return PERSIST
    # last resort: fallback in app dir
    ulog.warning("db: /data not writable; using non-persistent %s", FALLBACK)
    return FALLBACK

def _seed_if_missing(path: Path):
    if path.exists():
        return
    seed = APP_DIR / "olist.sqlite"    # optional repo copy
    try:
        path.parent.mkdir(parents=True, exist_ok=True)
    except Exception as e:
        ulog.exception("db: mkdir failed for %s: %s", path.parent, e)
    if seed.exists():
        try:
            shutil.copy2(seed, path)
            ulog.info("db: seeded %s from %s", path, seed)
        except Exception as e:
            ulog.exception("db: seed copy failed %s -> %s: %s", seed, path, e)
    else:
        ulog.warning("db: no seed found at %s; creating empty DB at %s", seed, path)

def get_connection():
    path = _choose_path()
    _seed_if_missing(path)
    try:
        conn = sqlite3.connect(path, check_same_thread=False, timeout=30)
        conn.row_factory = sqlite3.Row #added on 23 Aug 25 to fix the admin login issue
        conn.execute("PRAGMA foreign_keys = ON;")
        try:
            size = path.stat().st_size if path.exists() else 0
        except Exception:
            size = -1
        ulog.debug("db: open path=%s exists=%s size=%s bytes", path, path.exists(), size)
        try:
            dblist = conn.execute("PRAGMA database_list").fetchall()
            ulog.debug("db: PRAGMA database_list => %s", dblist)
        except Exception as e:
            ulog.debug("db: PRAGMA database_list failed: %s", e)
        return conn
    except Exception as e:
        ulog.exception("db: connect failed for %s: %s", path, e)
        raise

def _ensure_db_file():
    """
    Ensure the DB file exists in the persistent volume.
    If missing, seed from the repo copy (/app/olist.sqlite) if present,
    otherwise create an empty DB (schemas will be created by init_auth_schema()).
    """
    try:
        DB_PATH.parent.mkdir(parents=True, exist_ok=True)
    except Exception as e:
        ulog.exception("db: could not create parent dir for %s: %s", DB_PATH, e)

    if DB_PATH.exists():
        return

    seed = APP_DIR / "olist.sqlite"   # committed file in your repo (if any)
    if seed.exists():
        try:
            shutil.copy2(seed, DB_PATH)
            ulog.info("db: seeded %s from %s", DB_PATH, seed)
        except Exception as e:
            ulog.exception("db: failed to seed from %s → %s: %s", seed, DB_PATH, e)
    else:
        ulog.warning("db: no seed DB found at %s; will create empty DB at %s", seed, DB_PATH)