#!/usr/bin/env python3
"""
MedMan — Self-hosted media manager with tagging & squashr integration.

Source directories: files are ingested on startup / manual scan trigger.
  All immediate subdirectories of /app/srcs are treated as source dirs.

Data directories: files stored under full content-hash prefix paths
  (2 hex chars = 1 byte per directory level, entire hash used).
  All immediate subdirectories of /app/dats are treated as data dirs.

Import directories: mounted under /app/imports. Each subdirectory may
  contain an index.json at its root (MedMan export format) and files in
  the canonical hash-prefix hierarchy. Tags and file registrations are
  imported automatically on startup and re-checked on manual scan.

Tag system (no color coding). Upload via web UI. Move files between data dirs.
"""

import os, sys, io, json, hashlib, sqlite3, subprocess
import shutil, tempfile, secrets
from datetime import datetime, timezone
from typing import Optional

from flask import Flask, render_template, request, jsonify, send_file, abort
from werkzeug.utils import secure_filename

# ============================================================================
# Configuration
# ============================================================================

PORT  = int(os.environ.get("MEDMAN_PORT", "8080"))
HOST  = os.environ.get("MEDMAN_HOST", "0.0.0.0")
DBDIR = os.path.join(os.getcwd(), "db")

# Source directories: all immediate subdirs of /app/srcs
_SRC_ROOT = os.path.join(os.getcwd(), "srcs")
SRC: list[str] = []
if os.path.isdir(_SRC_ROOT):
    SRC = sorted([
        os.path.join(_SRC_ROOT, d) for d in os.listdir(_SRC_ROOT)
        if os.path.isdir(os.path.join(_SRC_ROOT, d))
    ])
if not SRC:
    SRC = [os.path.join(os.getcwd(), "src0"), os.path.join(os.getcwd(), "src1")]

# Data directories: all immediate subdirs of /app/dats
_DAT_ROOT = os.path.join(os.getcwd(), "dats")
DAT: list[str] = []
if os.path.isdir(_DAT_ROOT):
    DAT = sorted([
        os.path.join(_DAT_ROOT, d) for d in os.listdir(_DAT_ROOT)
        if os.path.isdir(os.path.join(_DAT_ROOT, d))
    ])
if not DAT:
    DAT = [os.path.join(os.getcwd(), "data0"), os.path.join(os.getcwd(), "data1")]

# Import directories: all immediate subdirs of /app/imports
# Each may contain an index.json + canonical hash-prefix hierarchy
_IMPORT_ROOT = os.path.join(os.getcwd(), "imports")
IMPORTS: list[str] = []
if os.path.isdir(_IMPORT_ROOT):
    IMPORTS = sorted([
        os.path.join(_IMPORT_ROOT, d) for d in os.listdir(_IMPORT_ROOT)
        if os.path.isdir(os.path.join(_IMPORT_ROOT, d))
    ])

# Weights (comma-separated floats; default equal)
_w = os.environ.get("MEDMAN_W", "")
W: list[float] = []
if _w.strip():
    W = [float(x.strip()) for x in _w.split(",") if x.strip()]
if not W:
    W = [1.0] * len(DAT)
if len(W) != len(DAT):
    raise RuntimeError("MEDMAN_W must have same count as data dirs")
tw = sum(W)
W = [x / tw for x in W]
CUM: list[float] = []
c = 0.0
for x in W:
    c += x
    CUM.append(c)

DB = os.path.join(DBDIR, "medman.db")
TH = os.path.join(DBDIR, "thumbs")

for d in [DBDIR, TH] + DAT + SRC + IMPORTS:
    os.makedirs(d, exist_ok=True)

app = Flask(__name__)

# ============================================================================
# Hash helpers
# ============================================================================

def h_file(ap: str) -> str:
    h = hashlib.sha256()
    with open(ap, "rb") as f:
        while True:
            b = f.read(65536)
            if not b:
                break
            h.update(b)
    return h.hexdigest()


def h2rel(hx: str) -> str:
    """Every 2 hex chars → one directory level (32 levels + filename)."""
    pairs = [hx[i:i + 2] for i in range(0, len(hx), 2)]
    return "/".join(pairs)


def rel_for_disk(hx: str, didx: int) -> str:
    """Full on-disk relative path for a given hash and data dir index."""
    return h2rel(hx)


def pick() -> tuple[int, str]:
    r = secrets.randbelow(2**31) / (2**31)
    for i, cu in enumerate(CUM):
        if r <= cu:
            return i, DAT[i]
    return len(DAT) - 1, DAT[-1]


# ============================================================================
# Database
# ============================================================================

def gdb():
    c = sqlite3.connect(DB)
    c.row_factory = sqlite3.Row
    c.execute("PRAGMA journal_mode=WAL")
    c.execute("PRAGMA foreign_keys=ON")
    return c


def idb():
    db = gdb()
    db.executescript("""
        CREATE TABLE IF NOT EXISTS tags (
            id   INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL UNIQUE,
            ts   TEXT DEFAULT (datetime('now'))
        );

        -- rel removed: it is always deterministically h2rel(hash)
        CREATE TABLE IF NOT EXISTS files (
            hash TEXT PRIMARY KEY,
            didx INTEGER NOT NULL,
            size INTEGER,
            mt   TEXT,
            w    INTEGER,
            h    INTEGER,
            dur  REAL,
            ts   TEXT DEFAULT (datetime('now'))
        );

        CREATE TABLE IF NOT EXISTS ftags (
            id   INTEGER PRIMARY KEY AUTOINCREMENT,
            hash TEXT NOT NULL,
            tid  INTEGER NOT NULL,
            FOREIGN KEY (hash) REFERENCES files(hash) ON DELETE CASCADE,
            FOREIGN KEY (tid)  REFERENCES tags(id)   ON DELETE CASCADE,
            UNIQUE(hash, tid)
        );
        CREATE INDEX IF NOT EXISTS ix_ft_h ON ftags(hash);
        CREATE INDEX IF NOT EXISTS ix_ft_t ON ftags(tid);
        CREATE INDEX IF NOT EXISTS ix_ta_n ON tags(name);
    """)
    # Drop 'rel' column if it still exists (migration from older schema)
    cols = [r[1] for r in db.execute("PRAGMA table_info(files)").fetchall()]
    if "rel" in cols:
        db.execute("ALTER TABLE files DROP COLUMN rel")
    db.commit()
    db.close()


idb()

# ============================================================================
# Helpers
# ============================================================================

VE = {".mp4", ".mkv", ".webm", ".avi", ".mov", ".m4v", ".wmv", ".flv", ".ogv"}
IE = {".jpg", ".jpeg", ".png", ".gif", ".bmp", ".webp", ".svg", ".tiff", ".ico"}
AE = {".mp3", ".wav", ".ogg", ".flac", ".aac", ".m4a", ".wma", ".opus"}
DE = {".pdf", ".epub", ".txt", ".md", ".rst"}
TS = (320, 240)


def _mt_from_content(ap: str) -> str:
    """Determine media type from file content (magic bytes / ffprobe)."""
    try:
        r = subprocess.run(
            ["file", "-b", "--mime-type", ap],
            capture_output=True, text=True, timeout=5,
        )
        mt = r.stdout.strip()
        if mt.startswith("video/"): return "video"
        if mt.startswith("image/"): return "image"
        if mt.startswith("audio/"): return "audio"
        if mt.startswith("application/pdf"): return "doc"
        if mt.startswith("text/"): return "doc"
    except Exception:
        pass
    try:
        r = subprocess.run(
            ["ffprobe", "-v", "error", "-show_entries", "format=format_name",
             "-of", "csv=p=0", ap],
            capture_output=True, text=True, timeout=10,
        )
        fmts = r.stdout.strip().lower()
        if any(x in fmts for x in ("mp4", "mov", "matroska", "avi")): return "video"
        if any(x in fmts for x in ("mp3", "aac", "flac", "wav", "ogg")): return "audio"
    except Exception:
        pass
    try:
        from PIL import Image
        Image.open(ap)
        return "image"
    except Exception:
        pass
    return "other"


def thumb(ap: str) -> Optional[bytes]:
    """Generate thumbnail."""
    try:
        from PIL import Image
        im = Image.open(ap)
        im.thumbnail(TS)
        b = io.BytesIO()
        im.save(b, format="JPEG", quality=75)
        return b.getvalue()
    except Exception:
        pass
    try:
        r = subprocess.run([
            "ffmpeg", "-y", "-loglevel", "error", "-i", ap,
            "-ss", "5", "-vframes", "1",
            "-vf", f"scale={TS[0]}:{TS[1]}:force_original_aspect_ratio=decrease,"
                   f"pad={TS[0]}:{TS[1]}:(ow-iw)/2:(oh-ih)/2",
            "-f", "image2", "pipe:1",
        ], capture_output=True, timeout=30)
        if r.returncode == 0 and r.stdout:
            return r.stdout
    except Exception:
        pass
    return None


def probe(ap: str) -> dict:
    try:
        r = subprocess.run([
            "ffprobe", "-v", "error", "-select_streams", "v:0",
            "-show_entries", "stream=width,height,duration", "-of", "json", ap,
        ], capture_output=True, timeout=15)
        if r.returncode == 0:
            d = json.loads(r.stdout)
            if d.get("streams"):
                s = d["streams"][0]
                return {"w": s.get("width"), "h": s.get("height"),
                        "dur": s.get("duration")}
    except Exception:
        pass
    return {}


def _resolve_file_path(hx: str, didx: int) -> str:
    """Given a hash and data-dir index, compute the absolute file path."""
    return os.path.join(DAT[didx], h2rel(hx))


def ingest(ap: str) -> Optional[str]:
    if not os.path.isfile(ap):
        return None
    hx = h_file(ap)
    rel = h2rel(hx)

    db = gdb()
    if db.execute("SELECT hash FROM files WHERE hash = ?", (hx,)).fetchone():
        db.close()
        try:
            os.remove(ap)
        except OSError:
            pass
        return hx

    didx, ddir = pick()
    tgt = os.path.join(ddir, rel)
    tdir = os.path.dirname(tgt)
    os.makedirs(tdir, exist_ok=True)
    if not os.path.exists(tgt):
        shutil.copy2(ap, tgt)

    sz = os.path.getsize(tgt)
    mi = probe(tgt)
    mtype = _mt_from_content(tgt)

    db.execute(
        "INSERT OR IGNORE INTO files (hash, didx, size, mt, w, h, dur) "
        "VALUES (?,?,?,?,?,?,?)",
        (hx, didx, sz, mtype, mi.get("w"), mi.get("h"), mi.get("dur")),
    )
    db.commit()
    db.close()

    try:
        os.remove(ap)
    except OSError:
        pass
    return hx


def _import_index_json(index_path: str, parent_dir: str) -> dict:
    """
    Import an index.json file located at *index_path* whose data files
    live under *parent_dir*. Registers tags and file entries for all
    files that actually exist on disk.
    Returns {imported_files: int, imported_tags: int}.
    """
    if not os.path.isfile(index_path):
        return {"imported_files": 0, "imported_tags": 0}

    try:
        with open(index_path, "r", encoding="utf-8") as fh:
            index_data = json.load(fh)
    except Exception:
        return {"imported_files": 0, "imported_tags": 0}

    db = gdb()
    imported_tags = 0
    imported_files = 0

    # Import tags
    for t in index_data.get("tags", []):
        nm = (t.get("name") or "").strip().lower()
        if nm:
            db.execute("INSERT OR IGNORE INTO tags (name) VALUES (?)", (nm,))

    # Determine didx for this import dir (add as data dir if not present)
    global DAT, W, CUM
    parent_dir = os.path.abspath(parent_dir)
    if parent_dir not in DAT:
        DAT.append(parent_dir)
        W.append(0.0)  # zero weight: never used for new ingests
        tw = sum(W)
        CUM = []
        c = 0.0
        for x in W:
            c += x / tw if tw > 0 else 0.0
            CUM.append(c)

    didx = DAT.index(parent_dir)

    # Register files that actually exist on disk
    for h, info in index_data.get("files", {}).items():
        if not isinstance(info, dict):
            continue
        rel = h2rel(h)  # always recompute from hash
        ap = os.path.join(parent_dir, rel)
        if not os.path.isfile(ap):
            continue

        sz = os.path.getsize(ap)
        mi = probe(ap)
        mtype = _mt_from_content(ap)

        db.execute(
            "INSERT OR IGNORE INTO files (hash, didx, size, mt, w, h, dur) "
            "VALUES (?,?,?,?,?,?,?)",
            (h, didx, sz, mtype, mi.get("w"), mi.get("h"), mi.get("dur")),
        )
        imported_files += 1

        file_tags = info.get("tags", []) if isinstance(info, dict) else []
        for tn in file_tags:
            tn = tn.strip().lower()
            tag_row = db.execute(
                "SELECT id FROM tags WHERE name = ?", (tn,)
            ).fetchone()
            if tag_row:
                try:
                    db.execute(
                        "INSERT OR IGNORE INTO ftags (hash, tid) VALUES (?,?)",
                        (h, tag_row["id"]),
                    )
                    imported_tags += 1
                except Exception:
                    pass

    db.commit()
    db.close()
    return {"imported_files": imported_files, "imported_tags": imported_tags}


def _scan_imports() -> dict:
    """Scan all import dirs for index.json files and import them."""
    total_files = 0
    total_tags = 0
    for imp_dir in IMPORTS:
        index_path = os.path.join(imp_dir, "index.json")
        if os.path.isfile(index_path):
            r = _import_index_json(index_path, imp_dir)
            total_files += r["imported_files"]
            total_tags += r["imported_tags"]
    return {"imported_files": total_files, "imported_tags": total_tags}


def scan() -> dict:
    r = {"scanned": 0, "ingested": 0, "errors": 0,
         "imported_files": 0, "imported_tags": 0}
    for sd in SRC:
        if not os.path.isdir(sd):
            continue
        for dp, _, fns in os.walk(sd):
            for fn in fns:
                r["scanned"] += 1
                try:
                    if ingest(os.path.join(dp, fn)):
                        r["ingested"] += 1
                except Exception:
                    r["errors"] += 1

    imp = _scan_imports()
    r["imported_files"] = imp["imported_files"]
    r["imported_tags"] = imp["imported_tags"]
    return r


_sr = scan()
print(f"scan: {_sr}", file=sys.stderr)

# Import dirs on startup  (already done via _scan_imports inside scan())
if _sr["imported_files"]:
    print(f"imported {_sr['imported_files']} files, "
          f"{_sr['imported_tags']} tags from import dirs", file=sys.stderr)

# ============================================================================
# Routes — thumbs / raw
# ============================================================================

@app.route("/thumb/<hx>")
def s_thumb(hx: str):
    tp = os.path.join(TH, hx + ".jpg")
    if not os.path.exists(tp):
        db = gdb()
        r = db.execute("SELECT didx FROM files WHERE hash=?", (hx,)).fetchone()
        db.close()
        if r:
            ap = _resolve_file_path(hx, r["didx"])
            if os.path.isfile(ap):
                d = thumb(ap)
                if d:
                    with open(tp, "wb") as f:
                        f.write(d)
                    return send_file(io.BytesIO(d), mimetype="image/jpeg")
        from PIL import Image
        im = Image.new("RGB", TS, "#1e1e2e")
        b = io.BytesIO()
        im.save(b, format="JPEG", quality=75)
        return send_file(io.BytesIO(b.getvalue()), mimetype="image/jpeg")
    return send_file(tp, mimetype="image/jpeg")


@app.route("/raw/<hx>")
def s_raw(hx: str):
    db = gdb()
    r = db.execute("SELECT didx FROM files WHERE hash=?", (hx,)).fetchone()
    db.close()
    if not r:
        abort(404)
    ap = _resolve_file_path(hx, r["didx"])
    if not os.path.isfile(ap):
        abort(404)
    return send_file(
        ap, mimetype="application/octet-stream",
        download_name=os.path.basename(ap),
    )


# ============================================================================
# Routes — tags
# ============================================================================

@app.route("/api/tags")
def a_tags():
    db = gdb()
    r = db.execute("SELECT id, name FROM tags ORDER BY name").fetchall()
    db.close()
    return jsonify([dict(x) for x in r])


@app.route("/api/tags", methods=["POST"])
def a_tag_new():
    d = request.get_json() or {}
    n = (d.get("name") or "").strip().lower()
    if not n:
        return jsonify({"error": "name required"}), 400
    db = gdb()
    db.execute("INSERT OR IGNORE INTO tags (name) VALUES (?)", (n,))
    db.commit()
    t = db.execute("SELECT id, name FROM tags WHERE name = ?", (n,)).fetchone()
    db.close()
    return jsonify(dict(t)), 201


@app.route("/api/tags/<int:tid>", methods=["DELETE"])
def a_tag_del(tid: int):
    db = gdb()
    db.execute("DELETE FROM ftags WHERE tid = ?", (tid,))
    db.execute("DELETE FROM tags WHERE id = ?", (tid,))
    db.commit()
    db.close()
    return jsonify({"ok": True})


# ============================================================================
# Routes — file tags
# ============================================================================

@app.route("/api/files/<hx>/tags")
def a_ftags(hx: str):
    db = gdb()
    r = db.execute(
        "SELECT t.id,t.name FROM ftags ft "
        "JOIN tags t ON t.id=ft.tid WHERE ft.hash=? ORDER BY t.name",
        (hx,),
    ).fetchall()
    db.close()
    return jsonify([dict(x) for x in r])


@app.route("/api/files/<hx>/tags", methods=["POST"])
def a_ftag_add(hx: str):
    d = request.get_json() or {}
    tid = d.get("tid")
    if not tid:
        return jsonify({"error": "tid required"}), 400
    db = gdb()
    db.execute("INSERT OR IGNORE INTO ftags (hash, tid) VALUES (?,?)", (hx, tid))
    db.commit()
    db.close()
    return jsonify({"ok": True})


@app.route("/api/files/<hx>/tags/<int:tid>", methods=["DELETE"])
def a_ftag_rm(hx: str, tid: int):
    db = gdb()
    db.execute("DELETE FROM ftags WHERE hash=? AND tid=?", (hx, tid))
    db.commit()
    db.close()
    return jsonify({"ok": True})


# ============================================================================
# Routes — files
# ============================================================================

@app.route("/api/files")
def a_files():
    tag = request.args.get("tag", "")
    db = gdb()
    if tag:
        rows = db.execute(
            "SELECT f.hash,f.didx,f.size,f.mt,f.w,f.h,f.dur,f.ts "
            "FROM files f JOIN ftags ft ON ft.hash=f.hash "
            "JOIN tags t ON t.id=ft.tid "
            "WHERE t.name=? ORDER BY f.ts DESC",
            (tag.strip().lower(),),
        ).fetchall()
    else:
        rows = db.execute(
            "SELECT hash,didx,size,mt,w,h,dur,ts "
            "FROM files ORDER BY ts DESC"
        ).fetchall()

    out = []
    for r in rows:
        h = r["hash"]
        ft = db.execute(
            "SELECT t.id,t.name FROM ftags ft "
            "JOIN tags t ON t.id=ft.tid WHERE ft.hash=? ORDER BY t.name",
            (h,),
        ).fetchall()
        out.append({
            "hash": h,
            "didx": r["didx"],
            "size": r["size"],
            "mt": r["mt"],
            "w": r["w"],
            "h": r["h"],
            "dur": r["dur"],
            "ts": r["ts"],
            "tags": [dict(t) for t in ft],
        })
    db.close()
    return jsonify(out)


@app.route("/api/files/<hx>", methods=["DELETE"])
def a_file_del(hx: str):
    db = gdb()
    r = db.execute("SELECT didx FROM files WHERE hash=?", (hx,)).fetchone()
    if not r:
        db.close()
        return jsonify({"error": "not found"}), 404
    ap = _resolve_file_path(hx, r["didx"])
    if os.path.isfile(ap):
        os.remove(ap)
    db.execute("DELETE FROM ftags WHERE hash=?", (hx,))
    db.execute("DELETE FROM files WHERE hash=?", (hx,))
    db.commit()
    db.close()

    p = os.path.dirname(ap)
    try:
        while (p and p not in DAT and os.path.isdir(p)
               and not os.listdir(p)):
            os.rmdir(p)
            p = os.path.dirname(p)
    except OSError:
        pass

    tp = os.path.join(TH, hx + ".jpg")
    if os.path.isfile(tp):
        os.remove(tp)
    return jsonify({"ok": True})


# ============================================================================
# Routes — move
# ============================================================================

@app.route("/api/files/<hx>/move", methods=["POST"])
def a_file_move(hx: str):
    d = request.get_json() or {}
    ti = d.get("didx")
    if ti is None or ti < 0 or ti >= len(DAT):
        return jsonify({"error": "invalid didx"}), 400
    db = gdb()
    r = db.execute("SELECT didx FROM files WHERE hash=?", (hx,)).fetchone()
    if not r:
        db.close()
        return jsonify({"error": "not found"}), 404
    if r["didx"] == ti:
        db.close()
        return jsonify({"ok": True, "moved": False})

    src_dir = DAT[r["didx"]]
    dst_dir = DAT[ti]
    rel = h2rel(hx)
    sp = os.path.join(src_dir, rel)
    np = os.path.join(dst_dir, rel)

    if not os.path.isfile(sp):
        db.close()
        return jsonify({"error": "src missing"}), 500

    os.makedirs(os.path.dirname(np), exist_ok=True)
    shutil.move(sp, np)
    db.execute("UPDATE files SET didx=? WHERE hash=?", (ti, hx))
    db.commit()
    db.close()

    p = os.path.dirname(sp)
    try:
        while (p and p not in DAT and os.path.isdir(p)
               and not os.listdir(p)):
            os.rmdir(p)
            p = os.path.dirname(p)
    except OSError:
        pass
    return jsonify({"ok": True, "moved": True, "didx": ti})


# ============================================================================
# Routes — upload / scan / dat
# ============================================================================

@app.route("/api/upload", methods=["POST"])
def a_upload():
    if "file" not in request.files:
        return jsonify({"error": "no file"}), 400
    res = []
    for f in request.files.getlist("file"):
        if not f.filename:
            continue
        tmp = tempfile.mkstemp(prefix="mm-")
        os.close(tmp[0])
        try:
            f.save(tmp[1])
            hx = ingest(tmp[1])
            res.append({"hash": hx, "status": "ok"} if hx
                       else {"status": "error"})
        except Exception as e:
            res.append({"status": "error", "error": str(e)})
        finally:
            if os.path.exists(tmp[1]):
                os.remove(tmp[1])
    return jsonify({"results": res})


@app.route("/api/scan", methods=["POST"])
def a_scan():
    return jsonify(scan())


@app.route("/api/dat")
def a_dat():
    ds = []
    for i, d in enumerate(DAT):
        nb = 0
        nf = 0
        if os.path.isdir(d):
            for dp, _, fns in os.walk(d):
                for fn in fns:
                    try:
                        nb += os.path.getsize(os.path.join(dp, fn))
                        nf += 1
                    except OSError:
                        pass
        ds.append({"i": i, "path": d, "w": W[i], "n": nf, "bytes": nb})
    return jsonify({"dat": ds, "src": SRC, "imports": IMPORTS})


# ============================================================================
# Routes — search
# ============================================================================

@app.route("/api/search")
def a_search():
    q = request.args.get("tags", "")
    if not q:
        return jsonify([])
    names = [n.strip().lower() for n in q.split(",") if n.strip()]
    db = gdb()
    ph = ",".join("?" for _ in names)
    rows = db.execute(
        f"SELECT ft.hash,f.didx,f.size,f.mt,f.w,f.h,f.dur,"
        f"COUNT(DISTINCT t.id) AS mc "
        f"FROM ftags ft JOIN tags t ON t.id=ft.tid "
        f"JOIN files f ON f.hash=ft.hash "
        f"WHERE t.name IN ({ph}) GROUP BY ft.hash HAVING mc=? "
        f"ORDER BY f.ts DESC",
        names + [len(names)],
    ).fetchall()

    out = []
    for r in rows:
        h = r["hash"]
        ft = db.execute(
            "SELECT t.id,t.name FROM ftags ft "
            "JOIN tags t ON t.id=ft.tid WHERE ft.hash=? ORDER BY t.name",
            (h,),
        ).fetchall()
        out.append({
            "hash": h,
            "didx": r["didx"],
            "size": r["size"],
            "mt": r["mt"],
            "tags": [dict(t) for t in ft],
        })
    db.close()
    return jsonify(out)


# ============================================================================
# Routes — export / import
# ============================================================================

def _import_tags_into_db(db: sqlite3.Connection, d: dict) -> int:
    """Import tags and tag associations from an export dict. Returns count."""
    n = 0
    for t in d.get("tags", []):
        nm = (t.get("name") or "").strip().lower()
        if nm:
            db.execute("INSERT OR IGNORE INTO tags (name) VALUES (?)", (nm,))
    for h, info in d.get("files", {}).items():
        file_tags = info.get("tags", []) if isinstance(info, dict) else []
        for tn in file_tags:
            tn = tn.strip().lower()
            tag = db.execute(
                "SELECT id FROM tags WHERE name=?", (tn,)
            ).fetchone()
            if tag:
                try:
                    db.execute(
                        "INSERT OR IGNORE INTO ftags (hash,tid) VALUES (?,?)",
                        (h, tag["id"]),
                    )
                    n += 1
                except Exception:
                    pass
    return n


@app.route("/api/tags/export")
def a_texport():
    """Return all files with tags (full export).  No ``rel`` field
    — the canonical path is deterministically computable from the hash."""
    tag_filter = request.args.get("tags", "").strip()
    tag_names = (
        [n.strip().lower() for n in tag_filter.split(",") if n.strip()]
        if tag_filter else []
    )

    db = gdb()

    if tag_names:
        ph = ",".join("?" for _ in tag_names)
        rows = db.execute(
            f"SELECT ft.hash,f.didx,t.name AS tn "
            f"FROM ftags ft "
            f"JOIN tags t ON t.id = ft.tid "
            f"JOIN files f ON f.hash = ft.hash "
            f"WHERE ft.hash IN ("
            f"  SELECT ft2.hash FROM ftags ft2 "
            f"  JOIN tags t2 ON t2.id = ft2.tid "
            f"  WHERE t2.name IN ({ph}) "
            f"  GROUP BY ft2.hash "
            f"  HAVING COUNT(DISTINCT t2.id) = ?"
            f") ORDER BY ft.hash, t.name",
            tag_names + [len(tag_names)],
        ).fetchall()
    else:
        rows = db.execute(
            "SELECT ft.hash,f.didx,t.name AS tn "
            "FROM ftags ft "
            "JOIN tags t ON t.id = ft.tid "
            "JOIN files f ON f.hash = ft.hash "
            "ORDER BY ft.hash, t.name"
        ).fetchall()
        seen_hashes = set(r["hash"] for r in rows)
        all_files = db.execute(
            "SELECT hash, didx FROM files ORDER BY hash"
        ).fetchall()
        for frow in all_files:
            if frow["hash"] not in seen_hashes:
                rows.append(frow)

    atags = db.execute("SELECT id,name FROM tags ORDER BY name").fetchall()
    db.close()

    ft = {}
    for r in rows:
        h = r["hash"]
        entry = ft.setdefault(h, {"didx": r["didx"], "tags": []})
        if "tn" in r.keys() and r["tn"]:
            entry["tags"].append(r["tn"])

    return jsonify({
        "ts": datetime.now(timezone.utc).isoformat(),
        "tags": [dict(t) for t in atags],
        "files": ft,
    })


@app.route("/api/tags/import", methods=["POST"])
def a_timport():
    d = request.get_json(force=True) or {}
    db = gdb()
    n = _import_tags_into_db(db, d)
    db.commit()
    db.close()
    return jsonify({"imported": n})


@app.route("/api/tags/export/archival")
def a_texport_archival():
    """Like /api/tags/export but accepts optional filter query params."""
    return a_texport()


# ============================================================================
# Routes — mount container (index.json import as additional data directory)
# ============================================================================

@app.route("/api/containers/mount", methods=["POST"])
def a_container_mount():
    """
    Mount a directory (e.g. SquashFS container root) as additional data dir.
    The directory must contain an index.json at its root.
    """
    d = request.get_json(force=True) or {}
    mnt_path = os.path.abspath((d.get("path") or "").strip())

    if not mnt_path or not os.path.isdir(mnt_path):
        return jsonify({"error": "path must be an existing directory"}), 400

    index_path = os.path.join(mnt_path, "index.json")
    if not os.path.isfile(index_path):
        return jsonify({"error": f"index.json not found in {mnt_path}"}), 400

    try:
        with open(index_path, "r", encoding="utf-8") as fh:
            index_data = json.load(fh)
    except Exception as exc:
        return jsonify({"error": f"Failed to parse index.json: {exc}"}), 400

    # Add as data directory
    global DAT, W, CUM
    if mnt_path not in DAT:
        DAT.append(mnt_path)
        W.append(0.0)
        tw = sum(W)
        CUM = []
        c = 0.0
        for x in W:
            c += x / tw if tw > 0 else 0.0
            CUM.append(c)

    db = gdb()
    imported_tags = 0
    imported_files = 0

    _import_tags_into_db(db, index_data)

    didx = DAT.index(mnt_path)
    for h, info in index_data.get("files", {}).items():
        if not isinstance(info, dict):
            continue

        rel = h2rel(h)
        ap = os.path.join(mnt_path, rel)
        if not os.path.isfile(ap):
            continue

        sz = os.path.getsize(ap)
        mi = probe(ap)
        mtype = _mt_from_content(ap)

        db.execute(
            "INSERT OR IGNORE INTO files (hash, didx, size, mt, w, h, dur) "
            "VALUES (?,?,?,?,?,?,?)",
            (h, didx, sz, mtype, mi.get("w"), mi.get("h"), mi.get("dur")),
        )
        imported_files += 1

        file_tags = info.get("tags", []) if isinstance(info, dict) else []
        for tn in file_tags:
            tn = tn.strip().lower()
            tag_row = db.execute(
                "SELECT id FROM tags WHERE name = ?", (tn,)
            ).fetchone()
            if tag_row:
                db.execute(
                    "INSERT OR IGNORE INTO ftags (hash, tid) VALUES (?,?)",
                    (h, tag_row["id"]),
                )
                imported_tags += 1

    db.commit()
    db.close()

    return jsonify({
        "ok": True,
        "path": mnt_path,
        "didx": didx,
        "files_imported": imported_files,
        "tags_imported": imported_tags,
        "dat_count": len(DAT),
    })


@app.route("/api/containers/mount", methods=["DELETE"])
def a_container_umount():
    """Remove a previously mounted container from the data directory list."""
    d = request.get_json(force=True) or {}
    global DAT, W, CUM

    if "didx" in d:
        didx = int(d["didx"])
        if didx < 0 or didx >= len(DAT):
            return jsonify({"error": "didx out of range"}), 400
        removed_path = DAT.pop(didx)
        W.pop(didx)
    elif "path" in d:
        mnt_path = os.path.abspath((d.get("path") or "").strip())
        if mnt_path not in DAT:
            return jsonify({"error": "path not in data directory list"}), 400
        didx = DAT.index(mnt_path)
        DAT.pop(didx)
        W.pop(didx)
        removed_path = mnt_path
    else:
        return jsonify({"error": "specify path or didx"}), 400

    tw = sum(W)
    CUM = []
    c = 0.0
    for x in W:
        c += x / tw if tw > 0 else 0.0
        CUM.append(c)

    return jsonify({
        "ok": True,
        "removed": removed_path,
        "dat_count": len(DAT),
    })


# ============================================================================
# Frontend
# ============================================================================

@app.route("/")
def index():
    return render_template("index.html")


def main():
    app.run(host=HOST, port=PORT, debug=False)


if __name__ == "__main__":
    main()