text: #!/usr/bin/env python3
  
  import click
  import hashlib
  import os
  import pid
  import platform
  import random
  import shutil
  import socket
  import sys
  import time
  import toml
  import ujson
  
  from collections import Counter, OrderedDict, defaultdict
  from dataclasses import dataclass
  from datetime import date, datetime, timedelta
  from datetime import time as dttime
  from typing import Union, Generator, NewType, Optional
  
  from . import utils
  from .data import FigData, File, History, Log, Location, Fragment, ZstdJson
  from .figtypes import parse_expressions, SearchExpression, FsFile
  from .remote import Host
  from .utils import dprint, HashThread, CallableCache, GlobFilter
  
  
  def resolve_figdir(figdir_cli_arg=Union[None, str]) -> str:
      if figdir_cli_arg:
          path = figdir_cli_arg
      elif 'XDG_CONFIG_HOME' in os.environ:
          path = os.path.join(os.environ['XDG_CONFIG_HOME'], 'fig')
      else:
          path = os.path.join(os.environ['HOME'], '.config', 'fig')
      return os.path.abspath(path)
  
  
  @click.group()
  @click.option('--figdir', default=None, envvar='FIG_DIR')
  @click.option('--debug', is_flag=True, default=False, envvar='FIG_DEBUG')
  @click.option('--config', default="config.toml", envvar='FIG_CONFIG')
  @click.pass_context
  def cli(ctx, figdir, debug, config):
      figdir = resolve_figdir(figdir)
      data = FigData(figdir, debug=debug)
      if data.db is None and click.get_current_context().invoked_subcommand != "init":
          print(f"unable to open database at {figdir}; do you need to run `fig init`?")
          raise SystemExit(1)
      work = Work(data, config=load_config_file(figdir, config), debug=debug)
      ctx.obj = work
  
  
  def cli_wrapper():
      "Remove single '-' arguments into a separate array."
      global SINGLE_DASH_ARGS
      SINGLE_DASH_ARGS = tuple([x for x in sys.argv if x[0] == '-' and x[1] != '-' and x not in "-0 -1 -print0 -print1".split()])
      sys.argv = [x for x in sys.argv if x not in SINGLE_DASH_ARGS]
      cli()
  
  
  class OrderedCounter(Counter, OrderedDict):
      'Counter that remembers the order elements are first encountered'
  
      def __repr__(self):
          return '%s(%r)' % (self.__class__.__name__, OrderedDict(self))
  
      def __reduce__(self):
          return self.__class__, (OrderedDict(self),)
  
  
  class AppLog:
      def __init__(self, command):
          self.command = command
          self.argv = sys.argv
          self.cwd = os.getcwd()
          self.hostname = socket.gethostname()
          self.started = datetime.now()
          self.stdout_count = 0
          self.stderr_count = 0
          self.output = []
  
      def __call__(self, stderr, mesg):
          self.output.append((time.time(), stderr, mesg))
          if stderr:
              self.stderr_count += 1
          else:
              self.stdout_count += 1
  
  
  class StatusKeeper:
      """A Counter that displays ephemeral status messages for all counter
      operations as well as permanent messages for certain counter types. In
      printzero mode, only paths are printed and ephemeral messages are not
      shown."""
      def __init__(self, preseed=None, total_files_expected=None, log=None, print0=False):
          self.log = log
          self.print0 = print0
          self.c = OrderedCounter()
          if preseed:
              self.preseed(preseed)
          self.total_files_expected = total_files_expected
          self.progress_count = 0
          self.ephemeral_reasons = "ignored already-known unchanged ok stat-updated".split()
          self.is_tty = sys.stdout.isatty()
          self.tty_cols, _ = shutil.get_terminal_size()
          self.disabled = False
          self.time_start = time.time()
  
      def disable(self):
          self.disabled = True
  
      def preseed(self, preseed: list):
          self.c.update({x: 0 for x in preseed})
  
      def __call__(self, reason, path, highlight=None, sha256=None, flags="", extra: Optional[str] = None, coverage=None):
          self.c[reason] += 1
          try:
              path.encode()
          except UnicodeEncodeError:
              path = path.encode('utf8', 'surrogateescape')
          if self.print0:
              self.print(path, end='\0')
              return
          if '\r' in path:
              path = path.replace('\r', r"$'\r'")
          if '\n' in path:
              path = path.replace('\n', r"$'\n'")
          if highlight and self.is_tty:
              path = utils.highlight(path, highlight)
          sha256 = sha256 or "none"
          extra = f" {extra}" if extra else ""
          cov = f"[{coverage}] " if coverage is not None else ""
          mesg = f"{reason:<16.16} {sha256:<8.8} {cov}{path}{extra}"
          if flags:
              mesg += " " + flags
          if reason in self.ephemeral_reasons:
              self.ephemeral(mesg, log=True)
          else:
              self.ephemeral()
              self.print(mesg)
  
      def print(self, mesg, ephemeral=False, log=True, **nargs):
          if self.disabled:
              return
          if ephemeral:
              print('\r' + mesg + '\033[K\r', end='', flush=True, file=sys.stderr)
              stderr = True
          else:
              print(mesg, **nargs)
              stderr = nargs.get('file', None) == sys.stderr
          if log and self.log:
              self.log(stderr, mesg)
  
      def ephemeral(self, mesg="", log=False):
          if self.is_tty:
              mesg = mesg[:self.tty_cols - 1]
              self.print(mesg, ephemeral=True, log=log)
  
      def progress(self, path, step=1):
          self.progress_count += step
          last = f"/{self.total_files_expected}" if self.total_files_expected else ""
          self.ephemeral(f"[{self.progress_count}{last}] {path}")
  
      def progress_percent(self, step=1, mesg=None):
          self.progress_count += step
          pct = 100 * self.progress_count / self.total_files_expected
          mesg = " " + mesg if mesg else ""
          self.ephemeral(f"[{self.progress_count}/{self.total_files_expected}] {pct:0.1f}%{mesg}", log=False)
  
      def progress_rate(self, mesg=None):
          self.progress_count += 1
          rate = self.progress_count / (time.time() - self.time_start)
          self.ephemeral(f"[{self.progress_count}][{int(rate)} files/sec] {mesg}", log=False)
  
      def print_stats(self):
          if self.total_files_expected:
              remaining = self.total_files_expected - self.progress_count
              if remaining > 0:
                  self.c['unprocessed'] = remaining
  
          for ok_reason in "stat-updated fault-cleared found".split():
              if self.c[ok_reason]:
                  self.c['ok'] += self.c[ok_reason]
  
          if num_faults := self.num_faults():
              self.c['faulted'] = num_faults
  
          s = []
          for label, count in self.c.items():
              s.append(f"{count:,} {label}")
  
          if not sys.stdout.isatty():
              fault_conditions = ['changed' in self.c, 'missing' in self.c]
              if not any(fault_conditions):
                  return
  
          self.ephemeral()
          self.print("; ".join(s), file=sys.stderr)
  
      def __del__(self):
          if self.c:
              self.print_stats()
  
      def num_faults(self, exclude_unknown=False):
          fault_list = "hash-changed missing lost".split()
          if not exclude_unknown:
              fault_list.append("unknown")
          return sum([self.c[x] for x in fault_list])
  
      def print_matches(self, reason, from_path, *to_paths, sha256=None):
          if self.print0:
              self.print(from_path, end='\0')
              sys.stdout.flush()
              return
          if len(to_paths) == 1:
              renamed = utils.format_rename(from_path, to_paths[0])
              self.__call__(reason, renamed.formatted, highlight=renamed.changed, sha256=sha256)
          else:
              self.__call__(reason, from_path, sha256=sha256)
              for idx, to_path in enumerate(to_paths):
                  self.print("{:>25} {}".format("", to_path))
  
  
  def load_config_file(figdir, configfile):
      configpath = os.path.join(figdir, configfile)
      try:
          with open(configpath, 'r') as f:
              return toml.load(f)
      except FileNotFoundError:
          return {}
  
  
  def printcols(*args):
      """Used to print results in N columns; intended for messages that must be
      printed even if there is no TTY."""
      if sys.stdout.isatty():
          print('\r\033[K\r', end='', flush=True, file=sys.stderr)  # clear line
      if len(args) == 1:
          print("{}".format(*args))
      elif len(args) == 2:
          print("{:<20} {}".format(*args))
      else:
          print("{:<20} {:<10} {}".format(*args))
  
  
  @dataclass
  class Work:
      """Convenience class to help keep track of the working files for any given
      operation."""
      data: FigData
      config: dict
      max_stat_age: Optional[int] = None
      debug: bool = False
  
      def __post_init__(self):
          self.cache = CallableCache()
          self.dirs_cached = []
          self.fsfiles = {}  # indexed by (host, hostpath)
          self.dbfiles = list[File]
          self.start_time = time.time()
          self.status = StatusKeeper()  # log=log
          self.localhost = Host(platform.node())
  
          # self.log = AppLog(command=ctx.invoked_subcommand)
          self.log = None
  
          if self.max_stat_age is None:
              self.max_stat_age = int(os.environ.get('FIG_MAX_STAT_AGE', 0))
  
      def __del__(self):
          if self.log:
              stats = self.status.c
              log = Log(output=ZstdJson(self.log.output), started=self.log.started, ended=datetime.now(),
                        argv=ujson.dumps(self.log.argv), cwd=self.log.cwd, hostname=self.log.hostname,
                        stats=ujson.dumps(stats), command=self.log.command)
              log.commit(self)
              self.log = None
  
      def load_figignore(self, abspath):
          """Recursively load figignore files up. and then load the global
          figignore."""
          figignore = GlobFilter(verbose=self.debug)
          for path in utils.find_files_up(".figignore", startdir=abspath):
              anchor = os.path.dirname(path)
              figignore.add_exclude_file(anchor=anchor, path=path)
          try:
              figignore.add_exclude_file(anchor="/", path=os.path.join(self.data.figdir, "figignore"))
          except FileNotFoundError:
              pass
          return figignore
  
      def get_pathspec(self, pathspec: list[str], include_ignored=False) -> Generator[FsFile, None, None]:
          for path in pathspec:
              host, path = separate_host_path(path)
              yield from self.scandir(host, path, include_ignored)
  
      def preload_pathspec(self, *args, **kwargs):
          tuple(_ for _ in self.get_pathspec(*args, **kwargs))
          if (tuple_size := sys.getsizeof(self.fsfiles)) > 500 * 1024:
              dprint(f"warning: preloaded pathspec used {tuple_size} bytes")
  
      def match_dbfiles(self, **kwargs) -> Generator[File, None, None]:
          """Get matching dbfiles from database. Use a dedicated database reader
          to avoid locking the database."""
          data_reader = FigData(self.data.figdir, debug=self.data.debug)
          yield from get_db_files(data_reader, **kwargs)
  
      def get_Files(self, **kwargs) -> Generator[File, None, None]:
          "Returns all files matching given conditions."
          data_reader = FigData(self.data.figdir)
          for dbf in data_reader.get_all(File, **kwargs):
              dbf.get_location(data_reader)
              load_host(dbf)
              yield dbf
  
      def get_sha_Files(self, sha256: str, **kwargs) -> Generator[File, None, None]:
          "Returns all files matching sha256 and other conditions. Uses fast sha search."
          for maybe_dbf in self.get_Files(sha256_short=sha256[:8], **kwargs):
              if maybe_dbf.sha256 == sha256:
                  yield maybe_dbf
  
      def scandir(self, host, path, include_ignored=False) -> Generator[FsFile, None, None]:
          if not host.is_remote:
              path = os.path.abspath(path)
  
          figignore = self.load_figignore(path)
  
          def ignore_fn(abspath):
              if ignore := figignore(abspath):
                  self.status('ignored', abspath)
              return ignore
  
          for fsf in host.scandir(path, is_ignored_fn=ignore_fn, include_ignored=include_ignored):
              # self.fsfiles[(fsf.host, fsf.abspath)] = fsf   # TODO: use this key in the future
              fsf._host = host
              self.fsfiles[fsf.hostpath] = fsf
              yield fsf
  
      def progress(self, obj: Union[FsFile, File]) -> None:
          try:
              path = obj.figpath
          except AttributeError:
              path = obj.hostpath
          self.status.progress(path)
  
      def fstatus(self, obj: Union[FsFile, File], status_message: str, **kwargs) -> None:
          try:
              path = obj.figpath
          except AttributeError:
              path = obj.hostpath
          self.status(status_message, path, sha256=obj.sha256, **kwargs)
  
      def cache_hostdir(self, host, path):
          # TODO: need to bucket by host
          # TODO: add maxdepth for scandir
          if path not in self.dirs_cached:
              for fsf in host.scandir(path):
                  self.fsfiles[fsf.hostpath] = fsf
  
      def check_dbfile_against_fsfile(self, dbf: File):
          return check_File(dbf, self.fsfiles)
  
      @property
      def stat_cutoff(self):
          # If max_stat_age is < 1E9 (epoch for approximately the year
          # 2000), then the value is interpreted as an epoch. Otherwise, is is
          # interpreted as the seconds to subtract from the current epoch.
          if self.max_stat_age > int(1E9):
              return self.max_stat_age
          return time.time() - self.max_stat_age
  
      def update_stat(self, dbf: File):
          # If it's not already in the cache, and if the DB stat hasn't been
          # updated within the cache cutoff, retrieve it from disk.
          if not dbf._host:
              raise Exception("no host!")
          cache = None
          try:
              cache = {dbf.abspath: self.fsfiles[dbf.abspath]}
          except KeyError:
              pass
  
          if dbf.stat_checked_on < self.stat_cutoff:
              check_File(dbf, cache)
              if dbf._dirty:
                  self.cache.add(lambda: dbf.commit(self.data))
  
      def add_fs_to_db(self, force=False, dry_run=False):
          add_fs_to_db(self.data, self.fsfiles, status=self.status, force=force, dry_run=dry_run)
  
      def forget_dbfs(self, dbfs: list[File]) -> int:
          """Remove dbfs and also any history entries. `dbfs` has to have the id,
          path, and sha256 columns."""
          ids = []
          for dbf in dbfs:
              self.fstatus(dbf, 'forgotten')
              ids.append(dbf.id)
          self.data.remove_files_and_histories(file_ids=ids)
          return len(ids)
  
      def commit(self, obj):
          obj.commit(self.data)
  
      def get_or_add(self, obj, **kwargs):
          return self.data.get_or_add(obj, **kwargs)
  
      def hash_and_add(self, fsfs: list[FsFile]):
          "Clears fsfs."
          while fsfs:
              # bucketize files to hash by host
              host = fsfs[0]._host
              host_fsfs = [x for x in fsfs if x._host == host]
  
              # leader = f"[{host.name}]" if host.name != platform.node() else ""
  
              total_bytes = sum([x.size for x in host_fsfs])
              self.status.ephemeral(f"hashing {len(host_fsfs)} files ({total_bytes:,} bytes)")
              hresults = host.hash_files(host_fsfs)
  
              for fsf in host_fsfs:
                  fsf.sha256 = hresults[fsf.hostpath]
                  if result := add_File(self.data, host, fsf):
                      self.status('error-adding', fsf.hostpath, extra=f"({result})")
                      continue
                  self.status('added', fsf.netpath, sha256=fsf.sha256)
                  fsfs.remove(fsf)
  
  
  pass_work = click.make_pass_decorator(Work)
  
  
  FsFiles = NewType('FsFiles', dict[str, FsFile])
  
  
  def get_File(data: FigData, fsf: FsFile) -> Optional[File]:
      if not (location := get_location(data, fsf)):
          raise
      dbf = data.get(File, location=location.id, hostpath=fsf.abspath)
      if dbf:
          dbf._location = location
          dbf._host = Host(location.hostname)
      return dbf
  
  
  def fix_inconsistent_fault(work):
      print("repairing files with inconsistent fault status...")
      for dbf in work.data.sql("select * from file where fault is null or fault not in (0, 1, 2, 3)", factory=File):
          print(dbf)
  
  
  def fix_missing_location(work):
      print("repairing files with missing location...")
      for dbf in work.data.sql("select * from file where location is null", factory=File):
          print(dbf)
  
  
  def fix_missing_stat(data):
      print("repairing files with missing stat...")
      files = data.get_all(File, fault=0, stat=None)
      for dbf in files:
          print('TODO: need to repair missing stat', dbf.path)
  
  
  def fix_missing_mountpoint(data, status):
      files = data.get_all(File, fault=0, mountpoint=None)
      for dbf in files:
          dbf.mountpoint = utils.find_mount_point(dbf.path)
          dbf.commit(data)
          status('fixed-mountpoint', dbf.path)
  
  
  def fix_invalid_timestamps(data, status):
      files = data.get_all(File)
      for f in files:
          if isinstance(f.sha256_checked_on, str):
              print("fixed-invalid-timestamp", f.path)
              f.sha256_checked_on = None
              f.commit(data)
  
  
  def truncate_path(path: str, max=75) -> str:
      """Truncate provided path "s", first by removing leading directories.
      Assumes path is already normalized."""
      need = len(path) - max
      if need <= 0:
          return path
  
      head, tail = os.path.split(path)
  
      # Start by trimming leading paths but not touching the filename
      orig_elems = path.split('/')
      for i in range(len(orig_elems) - 1):
          if orig_elems[i] != '' and need > 0:
              need -= len(orig_elems[i]) - 2
              orig_elems[i] = orig_elems[i][0]
  
      # If we still need space, trim from the front
      result = "/".join(orig_elems)
      if len(result) > max:
          need = len(result) - max + 3
          result = "..." + result[need:]
  
      return result
  
  
  def load_host(dbf: File):
      if dbf._host is None:
          dbf._host = Host(dbf._location.hostname)
      return dbf._host
  
  
  @dataclass
  class StatCheckResult:
      dbf: File
      fsf: FsFile
      status_changed: bool
      status: str
  
  
  def stat_check_dbf(dbf: File, fsf: FsFile) -> str:
      if fsf.os_error:
          # can't check file for some reason
          return 'os-error'
  
      dbf.mark_stat_checked()
  
      if not fsf.is_present or not fsf.stat:
          return 'missing'
  
      ctime_changed = dbf.ctime_ns != fsf.stat.st_ctime_ns
      if not ctime_changed:
          return 'unchanged-ctime'
  
      stat_changed = False or False
      if not stat_changed:
          return 'unchanged-stat'
  
      return 'stat-changed'
  
  
  def check_File(dbf: File, fsfiles: Optional[dict] = None, force=False) -> str:
      # TODO: refactor to return namedtuple - friendly_status, stat_checked, hash_checked ...?
      # does not modify `fsfiles`; modifies dbf but doesn't commit to the database
      try:
          fsf = fsfiles[dbf.hostpath]
      except TypeError:
          fsf = dbf._host.stat(dbf.hostpath)
      except KeyError:
          # caller provided the fsfiles, which is assumed to be current, so if
          # the entry wasn't found, it means the file is missing
          fsf = FsFile(hostname=dbf._host.name, hostpath=dbf.hostpath, stat=None)
  
      if fsf.os_error:
          return 'os-error'
  
      dbf.mark_stat_checked()
  
      if not fsf.is_present:
          # dbfile does not exist on filesystem
          if dbf.mountpoint and not dbf.mountpoint.endswith("/"):
              dbf.mountpoint += "/"
          if not force and dbf.mountpoint and utils.find_mount_point(dbf.path) != dbf.mountpoint:
              return "not-mounted"
          if not dbf.missing:
              dbf.set_fault('missing')
              dbf.add_history('lost')
              return 'lost'
          else:
              assert dbf.friendly_status == 'missing'
              return 'missing'
  
      ctime_changed = dbf.ctime_ns != fsf.stat.st_ctime_ns
      if dbf.fault_detected_on or force or ctime_changed:
          # TODO: only check hash again if ctime changes?
          dbf._host.hash_file(fsf)
          dbf.mark_sha_checked()
          if dbf.sha256 != fsf.sha256:
              dbf.set_fault('hash-changed')
              return 'hash-changed'
              # TODO: do we need history?
          elif dbf.fault_detected_on:
              dbf.clear_fault()
              dbf.add_history('found')  # TODO: not sure if this should be fault-cleared
              return 'fault-cleared'
          elif ctime_changed:
              dbf.set_stat(fsf.stat)
              return 'stat-updated'
  
      return dbf.friendly_status
  
  
  def scrub_files(work: Work, max_stat_days: float, max_hash_days: float, scrub_until=None, verbose=False) -> None:
      """Select files using an algorithm based on the current day and mtime. The
      idea is that this will result in all files being checked every n days. The
      intent is that if 'fig verify' is called every day, then on average, 1/n of
      the files will be checked every day. If we just based on the overdue check
      on date alone, and didn't run for a month, then all of the files would be
      selected for checking; and then tomorrow, none of the files would be
      overdue."""
  
      midnight = datetime.combine(datetime.today(), dttime.min)
      stat_cutoff = midnight - timedelta(days=max_stat_days)
      hash_cutoff = midnight - timedelta(days=max_hash_days)
  
      # first, get all files overdue for a stat check or a hash check
      query = f"{File.viewname} where (stat_checked_on < ? or sha256_checked_on < ?)"
      params = [stat_cutoff.timestamp(), hash_cutoff.timestamp()]
  
      days = min(max_stat_days, max_hash_days)
      if days >= 2:
          random.seed(date.today().toordinal())  # introduce predictable randomness
          int_days = int(days)
          query += f' and id % {int_days} = ?'
          params.append(random.randrange(0, int_days))
  
      file_count = next(work.data.sql(f"select count(id) from {query}", parameters=params))['count(id)']
      if file_count == 0:
          return
  
      status = work.status
      status.total_files_expected = file_count
  
      cache = CallableCache(flush_on=lambda x: len(x) > 100)
      # need to use list() below since query() is a generator; otherwise, the assignment expression will loop forever
      while files := list(work.data.sql(f"select * from {query} limit 100", factory=File, parameters=params)):
          hash_checked_ids = set()
          stat_checked_ids = set()
  
          for f in files:
              f.get_location(work.data)
              load_host(f)
  
              if not f.sha256_checked_on or f.sha256_checked_on < hash_cutoff.timestamp():
                  mode = 'hash'
                  result = check_File(f, force=True)
                  hash_checked_ids.add(f.id)
              else:
                  mode = 'stat'
                  result = check_File(f, force=False)
                  stat_checked_ids.add(f.id)
              status.progress_percent(mesg=f"{mode} {f.figpath}" if verbose else None)
  
              if result not in ['ok', 'missing', 'stat-updated', 'not-mounted']:
                  cache.add(lambda: work.commit(f))
                  work.fstatus(f, result)
  
          now_epoch = int(datetime.now().timestamp())
          work.data.update_ids(File, hash_checked_ids, update={'sha256_checked_on': now_epoch, 'stat_checked_on': now_epoch})
          work.data.update_ids(File, stat_checked_ids, update={'stat_checked_on': now_epoch})
          status.c['hash-scrubbed'] += len(hash_checked_ids)
          status.c['stat-scrubbed'] += len(stat_checked_ids)
  
          if scrub_until and datetime.now() >= scrub_until:
              print(f"exiting scrub due to scrub_until deadline {scrub_until}")
              return
  
  
  def fix_invalid_sha(work):
      print("repairing files with invalid SHAs...")
  
      files = work.get_Files(where={'length(sha256) != ?': 64})
      for dbf in files:
          print('invalid SHA', dbf)  # dbf.path, dbf.sha256)
          # print('clearing invalid SHA', dbf.path, dbf.sha256)
          # dbf.sha256 = ''
          # dbf.commit(data)
  
      for dbf in work.get_Files(where={'sha256_short is ?': None}):
          dbf.sha256_short = dbf.sha256[:8]
          print('fixed missing sha256_short:', dbf)
          work.commit(dbf)
  
      raise SystemExit(100)
  
  
  def update_moved_file(data, missingf, movedf):
      missingf.add_history('merged', movedf.id)
      movedf.add_history('moved', missingf.figpath)
      movedf.commit(data)
      missingf.commit(data)
      data.remove(missingf)  # want to keep history
      return 'moved'
  
  
  def parse_pathspec(pathspec: list[str]) -> list:
      "Parse pathspec and return the host associated with each pathspec."
      result: list[[Host, str]] = []
      for path in pathspec:
          result.append(separate_host_path(path))
      return result
  
  
  def expand_relpath(possible_relpath: str) -> Optional[str]:
      """If possible_relpath starts with . or .., interpret it as a path relative
      to the current directory, return None if the directory doesn't exist."""
      parts = possible_relpath.split('/')
      if parts[0] not in ['.', '..']:
          return None
      if os.path.exists(possible_relpath):
          return os.path.abspath(possible_relpath)
      return None
  
  
  def parse_regexes(regexes) -> dict:
      """Expressions (exp) are parsed as follows:
         - First, the location is determined:
           - Exp contains '::' -> the location label is parsed and searches restricted to that location
           - Exp does not contain '::' -> exp is treated as a search on the local machine:
             - Exp starts with / -> Exp interpreted as an absolute path
             - Exp starts is . or .. or starts with ./ or ../ -> treated as a relative path
      """
      if not regexes:
          return {}
  
      parsed = []
      host = None
      for regex in regexes:
          if maybe_abspath := expand_relpath(regex):
              if maybe_abspath == '/':
                  print(f'ignoring regex {regex} since it maps to /')
                  continue
              parsed.append(['hostpath', 'like', f"{maybe_abspath}/%"])
              parsed.append(['hostname', '=', platform.node()])
              continue
  
          if '::' in regex:
              host, _, remainder = regex.partition('::')
              parsed.append(['host', '=', host])
              column = 'hostpath'
              regex = remainder or '.*'
          elif regex.lower().startswith('sha:'):
              column = 'sha256'
              regex = regex[4:]
          elif regex.lower().startswith('tag:'):
              column = 'tags'
              regex = regex[4:]
          else:
              column = 'hostpath'
  
          if any([c in regex for c in '^*+[]|$']):
              if regex.startswith('-'):
                  parsed.append([column, 'not regexp', regex[1:]])
              else:
                  parsed.append([column, 'regexp', regex])
          else:
              if regex[0] in ('-', '!'):
                  regex = regex[1:]
                  op = "not like"
              else:
                  op = "like"
  
              # if regex starts with a /, only wildcard at the end for subdirs
              # this avoids unintended consequences, such as fig find /mnt/pool/dir1 --forget
              # which should only forget dir1 and not dir11
              if regex.startswith("/"):
                  # TODO: what if it's a file not a dir?
                  if not regex.endswith("/"):
                      # parsed.append(['=', regex])  # cover both file and dir
                      print(f"note: [{regex}] interpreted as [{regex}/]", file=sys.stderr)
                      regex += "/"
                  parsed.append([column, op, regex + "%"])
              else:
                  parsed.append([column, op, "%" + regex + "%"])
  
      key = ' and '.join([f"{column} {op} ?" for column, op, _ in parsed])
      value = [arg for _, _, arg in parsed]
      return {key: value}
  
  
  def get_db_files(data, search: Optional[SearchExpression] = None, args=None, pathspec=None, regexes=None, missing=False, fault=False,
                   changed=False, limit=None, empty=None, columns="*", tags: Optional[list] = None):
      """Be careful when using this generator as it will lock the database the
      entire time it is kept open."""
      since = getattr(args, 'since', False)
  
      where = {}
      extra_args = {}
  
      if search:
          if search.location_label:
              for loc in data.locations.values():
                  if loc.label == search.location_label:
                      extra_args['location'] = loc.id
                      break
  
          regexes = search.search_terms
  
      if fault:
          where['fault != ?'] = 0
      elif changed:
          where['fault = ?'] = 2
      elif missing:
          where['fault = ?'] = 1
  
      if empty is not None:
          where[f'size {"=" if empty else "!="} ?'] = 0
  
      if pathspec:
          # use abspath to not resolve links but resolve all other files to their real paths
          # links are always stored as-is (not followed) in file guardian
          include = [os.path.abspath(x) if os.path.islink(x) else os.path.realpath(x) for x in pathspec]
          extra_args['hostpath'] = [x + "/%" if os.path.isdir(x) and not os.path.islink(x) else x for x in include]
  
      if regexes:
          where.update(parse_regexes(regexes))
  
      files = data.get_all(File, columns=columns, **extra_args, where=where, limit=limit)
  
      # if changed and since:
      #     cutoff = datetime.now() - timedelta(hours=since[0])
      #     files = session.query(File).filter(File.fault_detected_on >= cutoff)
  
      # return files
      for dbf in files:
          dbf.get_location(data)
          load_host(dbf)
          yield dbf
  
  
  def get_location(data: FigData, fsf: FsFile) -> Optional[Location]:
      host_locations = [v for v in data.locations.values() if v.hostname == fsf.hostname]
      host_locations.sort(key=lambda x: len(x.mountpoint), reverse=True)
      for location in host_locations:
          if fsf.hostpath.startswith(location.mountpoint):
              return location
      return None
  
  
  def add_Frags(data, dbf):
      dirname = os.path.dirname(dbf.relpath)
      parts = set(dirname.split('/'))
      for part in parts:
          data.get_or_add(Fragment, value=part, file=dbf.id)
  
  
  def add_File(data, host, fsf: FsFile) -> Optional[str]:
      "Returns None on success, else returns string with error message."
      for attr in "hostname hostpath stat sha256".split():
          if not getattr(fsf, attr):
              return f"required attribute {attr} missing"
  
      if (location := get_location(data, fsf)) is None:
          mountpoint = host.find_mountpoint(fsf.hostpath)
          label = fsf.hostname + "/" + mountpoint
          if not (location := data.get_or_add(Location, label=label, hostname=fsf.hostname, mountpoint=mountpoint)):
              return f"{mountpoint} not found in location database and unable to create"
  
      parts = fsf.hostpath.partition(location.mountpoint)
      if parts[0] != '' or parts[1] != location.mountpoint:
          return f"couldn't parse: {fsf.hostpath} -> {parts}"
      relpath = parts[2]
  
      dbf = File(location=location.id, relpath=relpath, pathdeprecated="")
      dbf.set_stat(fsf.stat)
      dbf.set_sha(fsf.sha256)
  
      dbf.commit(data)
  
      return None
  
  
  # TODO: Rewrite below to take a generator output/list[FsFiles]
  def add_fs_to_db(data: FigData, fs: FsFiles, status, force=False, dry_run=False):
      # Filesystem entries can be any of the following: socket, symbolic link,
      # regular file, block device, directory, character device, or FIFO.
      # We only add symbolic links and regular files.
      shas = HashThread()
      for path, fsf in fs.items():
          if not force and data.figignore_match(fsf.hostpath):
              status('ignored', path)
          elif fsf.is_special_file:
              status('not-a-file', path)
          else:
              shas.add(fsf.hostpath, fsf)  # TODO: add FsFile aware HashThread
  
      status.total_files_expected = len(fs)
      for path, sha, fsf, error_reason in shas.get_results():
          if error_reason:
              status(error_reason, path)
          elif dry_run:
              status('would-add', path)
          else:
              f = File(host=data.hostname, path=path, mountpoint=utils.find_mount_point(path))
              f.set_stat(fsf.stat)
              f.set_sha(sha)
              f.commit(data)
              status('added', path, sha256=sha)
          status.progress_percent()
  
  
  @dataclass
  class CheckPathspecResult:
      pathspec: list
  
  
  # def diff_pathspec_to_db(data: FigData, pathspec, **kwargs):
  #     """Given a pathspec, retrieves the files from disk, checks each file
  #     against the database, and yields the results as a generator."""
  #     for fsf in get_pathspec(pathspec, **kwargs):
  #         # status.progress(fsf.path)
  #         dbf = get_File(data, fsf)
  #         result = check_File(dbf, {fsf.hostpath: fsfs})
  
  
  # x.add_argument('--changed', action='store_true', help='search for changed files only')
  # x.add_argument('--dbonly', action='store_true', help='only check files known to the db')
  
  # # TODO: need to make sure files that don't match the display query aren't
  # # incldued on things like --rm, e.g. --rm hi, and hihi is missing but
  # # hihihi1 is not missing
  # x.add_argument('--force-sha', action='store_true', help='force computation of SHA256')
  # x.add_argument('--since', type=int, nargs=1, help='show only files with fault since last N hours')
  # x.add_argument('--moved', action='store_true', help="find moved files")
  
  @cli.command(help='report status of pathspec')  # TODO: aliases="status sta diff".split())
  @click.argument('pathspec', nargs=-1)  # defaults to "."
  @click.option('--missing/--no-missing', '--miss/--no-miss', default=False, help="limit results to missing files only")
  @click.option('--unknown/--no-unknown', default=False, help="limit results to unknown files only")
  @click.option('--force', is_flag=True, default=False, help="force a full hash check (even if unmounted)")
  @click.option('--locate-missing', '--locate', is_flag=True, help="attempt to locate missing files")
  @click.option('--no-fail-unknown', is_flag=True, default=False)
  @click.option('--verbose/--no-verbose', default=False, help='show reason if a file was skipped')
  # @click.option('--preview/--no-preview', default=False, help='do not update db, preview what would happen only')
  @pass_work
  def check(work, pathspec, missing, unknown, force, locate_missing, no_fail_unknown, verbose):
      """Checks all arguments. Reports any known file in a fault state, changing state
      (went missing, found, etc.), or any unknown file (not in the database).
  
      Returns: 0 if all files are ok
               1 if unknown or faulted files are encountered
  
      Implementation:
      1. Get all files that match the pathspec with their stats from the filesystem.
      2. Get all database files that match the pathspec.
      3. For all files in the database, check database stat against filesystem stat:
         a. If stat matches, unchanged (don't print anything) [expected case]
         b. Handle missing/found cases
            - FS missing DB present -> lost
            - FS missing DB missing -> known missing
            - FS present DB missing -> found [TODO: need to check the hash?]
         c. If stat doesn't match, then check the SHA
            - If the SHA matches, then store the new stat (no confirmation required) and display a message
            - If the SHA doesn't match, mark file as changed (enter fault state)
      4. Print out all files from the filesystem that are not known to the database (and not on the ignore list).
  
      For ignored files, if they are already in the database, then they are
      checked; otherwise they are ignored.
      """
  
      pathspec = pathspec or (".", )
      work.status.preseed(["ok"])
  
      fsfs = {}
      if not missing:
          # first get all fs-files & stats, it is much faster to get them all at
          # once rather than checking one at a time later (for remote hosts)
          work.preload_pathspec(pathspec, include_ignored=True)
          # TODO: need to handle ignores as a special case later
  
      f_checked_ids = []  # save ids and update stat_checked_on all at once for speed
  
      # then walk all db-known files
      for dbf in work.match_dbfiles(pathspec=pathspec, missing=missing):
          result = check_File(dbf, work.fsfiles, force=force)
          if dbf._dirty:
              dbf.commit(work.data)
          else:
              f_checked_ids.append(dbf.id)
  
          if dbf.missing and locate_missing:
              possibles = list(work.data.get_all(File, sha256=dbf.sha256))
              matches = find_closest_match(dbf.path, possibles)
              if len(matches) == 1:
                  foundf = matches[0]
                  work.status('preview-found', f"{dbf.path} -> {foundf.path}")
  
          try:
              del work.fsfiles[dbf.hostpath]
          except KeyError:
              pass
  
          work.fstatus(dbf, result)
  
      work.data.update_ids(File, f_checked_ids, update=dict(stat_checked_on=datetime.now().timestamp()))
  
      # print("TODO: re-implement ignore checking", file=sys.stderr)
      if not missing:
          for fsf in work.fsfiles.values():
              if fsf.is_ignored:
                  reason = 'ignored'
              else:
                  reason = 'not-a-file' if fsf.is_special_file else 'unknown'
              work.fstatus(fsf, reason)
  
      faults = work.status.num_faults(exclude_unknown=no_fail_unknown)
      raise SystemExit(0 if faults == 0 else 1)
  
  
  def show_info(work: Work, f, all_history: bool = False):
      # (1) show file info
      f.status = f.friendly_status
      f.uid_gid = f'{f.uid}/{f.gid}'
      f.location = f"{f.host}:{f.mountpoint}"
      rows = [[x, f[x]] for x in "id location figpath hostpath status fault fault_detected_on sha256 first_seen stat_checked_on sha256_checked_on stat ctime_ns size uid_gid tags".split()]
      for field, value in rows:
          print("{: >20} {}".format(field, value))
  
      # (2) show possible dupes
      dupes = list(work.data.get_all(File, where={'fault = ?': 0, 'sha256 = ?': f.sha256, 'id != ?': f.id, 'size > ?': 0}))
      for dupef in dupes[:5]:
          print("{: >20} {} [{}]".format("same-sha256", dupef.figpath, dupef.friendly_status))
      if len(dupes) > 5:
          print(f"\tonly showing first 5 matching shas, use --all to see all {len(dupes)} entries")
  
      # (3) show file history
      histories = f.get_history(work.data)
      if not all_history:
          orig_history_length = len(histories)
          histories = histories[:5]
      for h in histories:
          data1 = h.data1 if h.data1 is not None else ""
          print(h.date, h.event_label, data1)
      if not all_history and orig_history_length > 5:
          print(f"\tonly showing first 5 history entries, use --all to see all {len(histories)} entries")
  
  
  @cli.command(help='display info about known files')
  @click.argument('pathspec', nargs=-1, required=True)
  @click.option('--json/--no-json', '_json', help='output information in JSON format')
  @click.option('--all/--no-all', 'show_all', help='display all available information')
  @pass_work
  def info(work, pathspec, _json, show_all):
      """Display file attributes and information."""
      work.data.log = None
  
      for dbf in work.match_dbfiles(pathspec=pathspec):
          dbf.get_history(work.data)
          if _json:
              click.echo(dbf.get_json())
          else:
              show_info(work, dbf, show_all)
  
  
  @cli.command(help='export database contents')
  @pass_work
  def export(data):
      data.log = None
  
      last_id = 0
      expected = data.get_count(File)
      actual = 0
      while files := list(data.sql(f"select * from file where id > {last_id} order by id limit 1000", factory=File)):
          for f in files:
              # del f['num_changes']
              # del f['sha256_bin']
              last_id = f.id
              actual += 1
              print(f.id, file=sys.stderr)
              click.echo(f.get_json())
  
      print(f"expected {expected}, actual {actual}, difference {expected - actual}", file=sys.stderr)
  
  
  # TODO: Add command aliases https://click.palletsprojects.com/en/7.x/advanced/
  @cli.command(name='stat', help='show statistics about the database and backup status')
  @pass_work
  def stat_cli(work):
      data =work.data
      data.log = None
  
      stats = []
  
      print(f"FIG_DIR = {data.figdir}")
  
      all_db_known = data.get_count(File)
      stats += [("files in db", all_db_known)]
      stats += [("history entries in db", data.get_count(History))]
      stats += [("files in fault state", data.get_count(File, where={"fault_detected_on is not ?": None}))]
      stats += [("files missing", data.get_count(File, fault=1, where={"fault_detected_on is not ?": None}))]
      # stats += [("files ignored", data.get_count(File, ignore=True))]
  
      if all_db_known > 0:
          midnight = datetime.combine(datetime.today(), dttime.min)
          thresh = midnight - timedelta(days=7)
          old_stat = data.get_count(File, where={'stat_checked_on < ?': thresh.timestamp()})
          stats += [(f"files overdue for stat check ({100 * old_stat / all_db_known:.0f}%)", old_stat)]
  
          thresh = midnight - timedelta(days=28)
          old_stat = data.get_count(File, where={'sha256_checked_on < ?': thresh.timestamp(), 'fault != ?': 1})
          stats += [(f"files overdue for hash check ({100 * old_stat / all_db_known:.0f}%)", old_stat)]
  
      for mesg, num in stats:
          print(f"{num:15,}  {mesg}")
  
      # future: stats on number of dupes
  
  
  @cli.command(help='stop tracking files in database (does not delete filesystem files)')
  @click.argument('pathspec', nargs=-1, required=True)  # help='paths to forget (dirs are recursively added)')
  @pass_work
  def forget(work, pathspec):
      # might try to remove a file from the db that is already gone from the
      # filesystem, for example (a file) /a/b/c/d or (a dir) a/b/c/d/*
  
      def helper(pathspec, regexes):
          files = work.match_dbfiles(pathspec=pathspec, regexes=regexes, columns="id, location, figpath, hostpath, sha256")
          return work.forget_dbfs(files)
  
      pathspec = [os.path.abspath(x) for x in pathspec]
      num_removed = helper(pathspec, [])  # this removes existing files & dirs and missing files
  
      if num_removed == 0:
          missing = [x for x in pathspec if not os.path.exists(x)]
          if missing:  # this removes missing dirs
              regexes = [f"{x}/.+" for x in missing]
              helper([], regexes)
  
  
  @cli.command(help='update database to match filesystem')
  @click.argument('pathspec', nargs=-1, required=True)
  @click.option('--force/--no-force', default=False, help="update files that would otherwise be ignored")
  @click.option('--dry-run', is_flag=True, default=False, help='do not update db, preview what would happen only')
  @pass_work
  def update(work, pathspec, force, dry_run):
      work.preload_pathspec(pathspec)
  
      # Remove any files that are already known to the db
      for dbf in work.match_dbfiles(pathspec=pathspec):  # , ignore_fn=work.data.figignore_match):
          if dbf.hostpath not in work.fsfiles.keys():
              work.fstatus(dbf, 'forgotten')
              dbf.remove(work.data)
              continue
  
          fsf = work.fsfiles[dbf.hostpath]
          result = check_File(dbf, work.fsfiles)  # {fsf.abspath: fsf})
          if result == 'hash-changed':
              result = 'hash-updated'
              dbf.set_sha(fsf.sha256)
              dbf.set_stat(fsf.stat)
          if dbf._dirty:
              dbf.commit(work.data)
          work.fstatus(dbf, result)
  
          del work.fsfiles[dbf.hostpath]
          work.status.progress(dbf)
  
      # fs only has unknown files now, add them to todo and sha
      work.add_fs_to_db(work, dry_run=dry_run)  # force=force
  
  
  def vacuum_if_needed(work, days=30):
      last = work.data.meta.last_vacuum or datetime(1970, 1, 1)
      if (datetime.now() - last) > timedelta(days=days):
          print(f"vacuuming database every {days} days...")
          work.data.vacuum()
  
  
  @cli.command(help='verify data integrity of known files')
  @click.option('--max-seconds', type=int, default=0, help="maximum scrub time in seconds")
  @click.option('--max-stat-days', type=float, default=None, help="maximum days before stat scrub")
  @click.option('--max-hash-days', type=float, default=None, help="maximum days before hash scrub")
  @click.option('--verbose', '-v', is_flag=True, default=False, help='show more details while scrubbing')
  @pass_work
  def scrub(work, max_seconds, max_stat_days, max_hash_days, verbose):
      """Verify mode is intended to be run routinely, e.g., from cron. It will
      ensure that all files in the database are checked every X days. In normal
      operation, `fig verify` only reports changes to a file's state (for
      example, going from present to missing). If a file's state is unchanged
      (for example, it was missing and is still missing), it is not reported."""
  
      config = work.config.get('scrub', {})
      max_stat_days = max_stat_days or config.get('max_stat_days', 6)
      max_hash_days = max_hash_days or config.get('max_hash_days', 28)
  
      if verbose:
          print(f"scrubbing files with stat > {max_stat_days} or hash > {max_hash_days}")
  
      lock = pid.PidFile("fig")
      try:
          lock.check()
      except pid.PidFileAlreadyRunningError as e:
          print(f"fig scrub with process id {e.pid} is already running, not starting another one")
          raise SystemExit(1)
  
      until = datetime.now() + timedelta(seconds=max_seconds) if max_seconds else None
  
      lock.create()
      scrub_files(work, max_stat_days, max_hash_days, scrub_until=until, verbose=verbose)
      vacuum_if_needed(work)
  
  
  # def scandir_r(paths: Union[str, list], is_ignored_fn=None, force=False) -> Generator[FsFile, None, None]:
  #     """Recursively find all files in specified paths. Symlinks are never
  #     followed and are returned as symlinks.
  
  #     This function was really difficult to write because almost all of the
  #     Python path functions raise errors especially when dealing with symlinks.
  #     Because File Guardian never follows symlinks, we have to use only methods
  #     that have the ability to specify not to follow symlinks."""
  #     if not isinstance(paths, (list, tuple)):
  #         paths = [paths]
  #     for p in map(Path, paths):
  #         try:
  #             p_stat = os.stat(p, follow_symlinks=False)
  #         except (FileNotFoundError, NotADirectoryError):
  #             yield FsFile(abspath(p), is_present=False)
  #             continue
  #         except OSError as e:
  #             yield FsFile(abspath(p), os_error=e.errno)
  #             continue
  
  #         if is_ignored_fn and is_ignored_fn(p) and not force:
  #             yield FsFile(abspath(p), is_ignored=True)
  #         elif p.is_symlink():
  #             # abspath doesn't resolve symlinks; realpath does
  #             yield FsFile(abspath(p), stat=p_stat, is_link=True)
  #         elif p.is_file():
  #             # use realpath here, this file may be in a directory tree that is
  #             # itself symlinked (even though the file `p` itself is not a
  #             # symlink), so we want to resolve the symlinks so we have the true path
  #             yield FsFile(realpath(p), stat=p_stat)
  #         else:
  #             try:
  #                 results = os.scandir(p)
  #             except FileNotFoundError:
  #                 continue
  #             except PermissionError as e:
  #                 yield FsFile(abspath(p), os_error=e.errno)
  #                 continue
  #             for i in results:
  #                 # All of the scandir helper methods choke if there is an underlying OSError.
  #                 # So we can't use is_dir(), is_file(), etc.
  #                 # This seems to only happen in a recursive symlink (e.g., a -> a, where a is a symlink).
  #                 try:
  #                     st = i.stat(follow_symlinks=False)
  #                 except OSError as e:
  #                     yield FsFile(i.path, os_error=e.errno)
  #                     continue
  
  #                 if stat.S_ISDIR(st.st_mode) or stat.S_ISLNK(st.st_mode):
  #                     yield from scandir_r([i.path], is_ignored_fn, force)
  #                 else:
  #                     # this is redundant with above but done here again as a
  #                     # special case for efficiency
  #                     yield FsFile(realpath(i.path), stat=st)
  
  
  def find_closest_match(missingpath: str, possibles: list[File]):
      possibles = list(possibles)
      candidates = [x.figpath for x in possibles]
      result = utils.guess_closest_matches(missingpath, candidates)
  
      if result.mismatch:
          printcols("", "[couldn't reliably make a guess as to the best candidate:]")
          printcols("", "[method1] " + result.method1[0])
          printcols("", "[method2] " + result.method2[0][0])
          return None
  
      return sorted(possibles, key=lambda x: result.method1.index(x.figpath))
  
  
  def find_moved_file(missingf, candidates, lpaths=None):
      # See if missingf has been moved to one of the files in candidates
      #
      # To be a match, candidate must a different path, the same size, same hash,
      # and not be missing.
      #
      # If there is only one possible file found, then detection of the moved
      # file is easy.
      #
      # If a single file is found, it's a match based on the above criteria.
      # If multiple files are found with the same hash, but only one has the
      # exact filename, then the one with the filename is the match.
      #
      # TODO: if inode is unchanged, but path is different -> moved file!
  
      lpaths = lpaths or []
      possibles = []
      faulted_possibles = []
      for possiblef in candidates:
          if possiblef.id == missingf.id:
              continue
          if possiblef.sha256 != missingf.sha256:
              continue
          if possiblef.size != missingf.size:
              continue
          if possiblef.missing:
              continue
          if not all([lpath.lower() in possiblef.figpath.lower() for lpath in lpaths]):
              continue
          # make sure possiblef is not faulted
          check_File(possiblef)
          if possiblef.fault != 0:
              faulted_possibles.append(possiblef)
          else:
              possibles.append(possiblef)
  
      # Easy cases - zero or exactly 1 match
      if len(possibles) <= 1:
          return possibles, faulted_possibles
  
      # If 2 or more possibilities, return in order of closest match using difflib library
      return find_closest_match(missingf.figpath, possibles), faulted_possibles
  
  
  def is_moved_file(newf, missingf, use_sha_only=False):
      # See if newf is missingf but moved to a new locatin.
      # If all of the following are true:
      # - newf and missingf have the same hash
      # - missingf is missing (it is assumed newf is present)
      # - Either
      #   - Same parent dirpath but different filenames (file as renamed within same directory)
      #   - Or same filename but different parent dirpath (file moved to different directory)
      #   - Note: Filename changing and path changing out of scope for now!
  
      # TODO:
      # Pass in missingf and list of other files with matching SHA
  
      if newf.path == missingf.path:  # same file!
          return False
  
      if newf.sha256 != missingf.sha256:
          return False
  
      if not missingf.missing:
          return False
  
      if newf.size != missingf.size:
          return False
  
      if use_sha_only:
          return True
  
      newdir, newfile = os.path.split(newf.path)
      missingdir, missingfile = os.path.split(missingf.path)
  
      if newdir == missingdir:
          return newfile != missingfile
      else:
          return newfile == missingfile
  
  
  # def get_pathspec(pathspec, force=False, is_ignored_fn=None, status=None, verbose=False) -> FsFiles:
  #     status = status or StatusKeeper()
  #     if verbose:
  #         status.ephemeral_reasons = []
  #     "Returns list of files and stats found from pathspec."
  #     fs = {}
  #     for idx, fsfile in enumerate(scandir_r(pathspec, is_ignored_fn, force)):
  #         if not fsfile.is_present:
  #             printcols("does-not-exist", fsfile.abspath)
  #         elif fsfile.is_ignored:
  #             status("ignored", fsfile.abspath)
  #         elif fsfile.os_error:
  #             status("os-error", fsfile.abspath)
  #         else:
  #             fs[fsfile.abspath] = fsfile
  #         status.ephemeral(f"building filelist [{idx}]")
  
  #     status.total_files_expected = len(fs)
  #     return fs
  
  
  def get_pathspec_generator(host, pathspec, force=False, is_ignored_fn=None, status=None, verbose=False) -> Generator[FsFile, None, None]:
      status = status or StatusKeeper()
      if verbose:
          status.ephemeral_reasons = []
      "Returns files and stats found from pathspec."
      for path in pathspec:
          for fsfile in host.scandir(path, is_ignored_fn=is_ignored_fn):
              if not fsfile.is_present:
                  printcols("does-not-exist", fsfile.abspath)
              elif fsfile.is_ignored:
                  status("ignored", fsfile.abspath)
              elif fsfile.os_error:
                  status("os-error", fsfile.abspath)
              elif fsfile.is_special_file:
                  status("skipping", fsfile.abspath)
              else:
                  fsfile._host = host
                  yield fsfile
  
  
  def add_helper_progress(data, pathspec, force=False, dry_run=False, verbose=False):
      status = data.status
      if verbose:
          status.ephemeral_reasons.remove("ignored")
      cached_candidates = []
      for idx, fsf in enumerate(get_pathspec_generator(pathspec, force=force, is_ignored_fn=data.figignore_match)):
          status.ephemeral(f"building filelist [{idx}]")
          cached_candidates.append(fsf)
      status.total_files_expected = len(cached_candidates)
      for fsf in cached_candidates:
          if maybe_dbf := get_File(data, fsf):
              status('already-known', maybe_dbf.path, sha256=maybe_dbf.sha256)
          elif dry_run:
              status('would-add', fsf.abspath)
          else:
              status.progress_percent(step=0, mesg=f"hashing {fsf.abspath}")
              fsf.sha256 = HashThread.hash_file(fsf.abspath)
              if fsf.sha256 == "":
                  status('error-adding', fsf.abspath)
              add_File(fsf, data)
              status('added', fsf.abspath, sha256=fsf.sha256)
          status.progress_percent()
  
  
  def separate_host_path(path):
      if ':' in path:
          hostname, _, path = path.partition(':')
          return Host(hostname), path
      else:
          return Host(platform.node()), path
  
  
  @dataclass
  class FsFileQueue:
      work: Work
      max_bytes: int = 128 * 1024 * 1024
      max_length: int = 100
  
      def __post_init__(self):
          self.queue = []
          self.queue_bytes = 0
  
      def __del__(self):
          self.flush()
  
      def add(self, fsf: FsFile):
          self.queue.append(fsf)
          if fsf.stat:
              self.queue_bytes += fsf.stat.st_size
          if self.queue_bytes > self.max_bytes:
              self.flush()
          elif len(self.queue) >= self.max_length:
              self.flush()
  
      def flush(self):
          self.work.hash_and_add(self.queue)
          self.queue_bytes = 0
  
  
  @dataclass
  class Neighborhood:
      work: Work
  
      def __post_init__(self):
          # these are keyed off of hostname
          self.parentpaths: dict[str, list] = defaultdict(list)
          self.hostpaths: dict[str, list] = defaultdict(list)
  
      def has_fsf(self, fsf: FsFile):
          self.add_parentpath(fsf.hostname, fsf.parentpath)
          return self.has_hostpath(fsf.hostname, fsf.hostpath)
  
      def add_parentpath(self, hostname, parentpath):
          if parentpath in self.parentpaths[hostname]:
              return
          self.parentpaths[hostname] += [parentpath]
          dbfs = self.work.data.get_all(File, columns="id, hostname, hostpath", hostname=hostname, where={"hostpath like ?": parentpath + "/%"})
          self.hostpaths[hostname] += [x.hostpath for x in dbfs]
  
      def has_hostpath(self, hostname, hostpath):
          return hostpath in self.hostpaths[hostname]
  
  
  def add_helper(work: Work, pathspec: list[str], force: bool = False, dry_run=False, verbose=False) -> None:
      """Adding files on remote systems introduces overhead that can drastically
      reduce the performance. Files to be added are queued and processed in
      batches. The batch size was empirically determined to be optimal at 128MB.
      Different endpoints may have different optimal values."""
      if verbose:
          work.status.ephemeral_reasons.remove("ignored")
  
      neighborhood = Neighborhood(work)
      queue = FsFileQueue(work)
  
      for count, fsf in enumerate(work.get_pathspec(pathspec)):
          if neighborhood.has_fsf(fsf):
              fsf.sha256 = str(count)
              work.fstatus(fsf, 'already-known')
          elif dry_run:
              work.fstatus(fsf, 'would-add')
          else:
              work.status.ephemeral(f"queued {fsf.hostpath}")
              queue.add(fsf)
  
  
  @cli.command(help='add pathspec to database (recursively descend dirs)')
  @click.argument('pathspec', nargs=-1, required=True)
  @click.option('--force/--no-force', default=False, help="add files that would otherwise be ignored")
  @click.option('--dry-run', is_flag=True, default=False, help='do not update db, preview what would happen only')
  @click.option('--verbose/--no-verbose', default=False, help='show status for each file')
  @click.option('--progress/--no-progress', default=False, help='show progress')
  @pass_work
  def add(work, pathspec, force, dry_run, verbose, progress):
      func = add_helper_progress if progress else add_helper
      func(work, pathspec, force, dry_run, verbose)
  
  
  @cli.command(help='attempt to locate known files in pathspec that are missing')
  @click.argument('pathspec', nargs=-1, required=True)
  @click.option('--path', type=str, help="found path must contain this string")
  @pass_work
  def locate(data, pathspec, path, go):
      status = data.status
  
      # Get shas for db-known files
      missing_shas = {}
      db_ids = {}
      for dbf in get_db_files(data, pathspec=pathspec, missing=True):
          missing_shas[dbf.path] = dbf.sha256
          db_ids[dbf.path] = dbf.id
  
      # get all db-known files with matching shas
      db_shas = defaultdict(list)
  
      path = os.path.realpath(path) if path else None
      all_shas = [f'"{x}"' for x in missing_shas.values()]
      for dbf in data.get_all(File, sha256=all_shas):
          if dbf.missing:
              continue
          if dbf.path in missing_shas:
              continue
          if path and not dbf.path.startswith(path):
              continue
          db_shas[dbf.sha256].append(dbf.path)
          db_ids[dbf.path] = dbf.id
  
      found_paths = {}
  
      for fs_path, fs_sha in missing_shas.items():
          matches = db_shas[fs_sha]
          if len(matches) < 1:
              status('could-not-find', fs_path)
              continue
          assert fs_path not in matches
          if len(matches) == 1:
              found_paths[fs_path] = matches[0]
              status('found', f'{fs_path} -> {matches[0]}')
          else:
              status('ambiguous', fs_path)
              for path in matches:
                  printcols('', '\t' + path)
  
      if go:
          to_delete = []
          for missing_path, found_path in found_paths.items():
              data.add(History(file_id=db_ids[found_path], event=History.E.moved, data1=missing_path))
              to_delete.append(db_ids[missing_path])
          data.remove_ids(File, to_delete)
      else:
          if found_paths:
              print("note: dry run only, re-run with --go to execute for real")
  
  
  def remove_backups(dbfiles: list[File], work: Optional[Work] = None):
      for dbf in dbfiles:
          if dbf.backuponly:
              if work:
                  work.fstatus(dbf, 'backup-ignored')
          else:
              yield dbf
  
  
  def remove_nonmatching(dbfiles: list[File], match_path: Optional[list[str]]) -> Generator[File, None, None]:
      match_path = match_path or []
      for dbf in dbfiles:
          for mpath in match_path:
              if mpath[0] == '!':
                  if mpath[1:].lower() in dbf.hostpath.lower():
                      break
              elif mpath.lower() not in dbf.hostpath.lower():
                  break
          else:
              yield dbf
  
  
  @cli.command(help='find duplicates of known files matching pathspec')
  @click.argument('pathspec', nargs=-1)
  @click.option('--match-path', '--match', multiple=True, help="duplicate path must contain this string")
  @click.option('--verbose/--no-verbose', default=False, help='show reason if a file was skipped')
  @click.option('--num-matches', 'num_matches', type=int, default=None, help='must have exactly N duplicates')
  @click.option('--unique', is_flag=True, default=False, help='must have no duplicates (overrides --num-matches)')
  @click.option('--print0', '-print0', '-0', is_flag=True, help="print matching paths similar to find -print0")
  @click.option('--no-verify', is_flag=True, help="skip verification of duplicate file")
  @click.option('--max-stat-age', type=int, default=None, help="don't stat dbfiles with stat age greater than this", show_default=True)
  @click.option('--all', 'all_', is_flag=True, default=False, help='include backups as duplicate candidates')
  @pass_work
  def dupes(work, pathspec, match_path, verbose, num_matches, unique, print0, no_verify, max_stat_age, all_):
      """Shows duplicate files matching the pathspec. By default, shows files
      with any matches. Use --unique (same as --num-matches=0) to display unique
      files."""
      # work.max_stat_age=max_stat_age  # TODO: need to fix
      work.status.ephemeral_reasons += "stat-updated backup-ignored".split()
      work.data.log = None
  
      pathspec = pathspec or (".", )
  
      if print0:
          work.status.disable()
      if unique:
          num_matches = 0
  
      # if match_path:
      #     # May be faster to preload all possible matching files
      #     # where = {'abspath like ?': x for x in match_path}
      #     wild_match_path = [f'%{x}%' for x in match_path]
      #     match_path_dbfs = list(work.get_Files(abspath__and=wild_match_path))
      #     # TODO: below is broken need to fix, it matches all
      #     # match_path_dbfs = list(work.match_dbfiles(pathspec=match_path))
      #     # match_path_dbfs = []
      #     # for x in work.get_Files(abspath=match_path):
      #     #     if all([y in x.abspath for y in match_path]):
      #     #         match_path_dbfs.append(x)
      #     pass
  
      # TODO: need to run stat check on all subjects and candidates
  
      maybe_pathspec_dbfs = {}
      for x in work.match_dbfiles(pathspec=pathspec):
          maybe_pathspec_dbfs[x.abspath] = x
  
      for fsf in work.get_pathspec(pathspec, include_ignored=True):
          work.progress(fsf)
  
          # FSF stat is guaranteed to be current since we just retrieved it
          if fsf.is_special_file:
              work.status('not-a-file', fsf.hostpath)
              continue
  
          try:
              fsf.sha256 = maybe_pathspec_dbfs[fsf.hostpath].sha256
          except KeyError:
              work.status.ephemeral(f"hashing {fsf.hostpath}")
              work.localhost.hash_file(fsf)
  
          # not working but should be ?
          # if match_path:
          #     dbf_matches = [x for x in match_path_dbfs if x.sha256 == fsf.sha256]
  
          dbf_matches = work.get_sha_Files(sha256=fsf.sha256, fault=0, where={'hostpath != ?': fsf.hostpath})
          dbf_matches = remove_nonmatching(dbf_matches, match_path)
          dbf_matches = list(dbf_matches)
  
          if not all_:
              new_dbf_matches = []
              for _ in dbf_matches:
                  if _.backuponly:
                      work.fstatus(_, 'backup-ignored')
                  else:
                      new_dbf_matches.append(_)
              dbf_matches = new_dbf_matches
  
          actual_matches = len(dbf_matches)
  
          # for _ in dbf_matches:
          #     work.update_stat(_)
  
          if num_matches is not None:
              if actual_matches != num_matches:
                  # user requested a specific number of matches and this didn't match
                  # --unique is handled here, since it sets num_matches = 0
                  continue
          else:
              # if user didn't set --num-matches or --unique, display all files with 1 or more matches
              if actual_matches == 0:
                  continue
  
          if print0:
              print(fsf.hostpath, end='\0', flush=True)
          elif actual_matches == 0:
              work.status('unique', fsf.hostpath, sha256=fsf.sha256)
          elif actual_matches == 1:
              renamed = utils.format_rename(fsf.hostpath, dbf_matches[0].hostpath)
              work.status('has-dupe', renamed.formatted, highlight=renamed.changed, sha256=fsf.sha256)
          else:
              work.status.print_matches('has-dupe', fsf.hostpath, *[x.hostpath for x in dbf_matches], sha256=fsf.sha256)
              if len(dbf_matches) >= 2:
                  print()
  
  
  def parse_match_path(match_paths: list[str]) -> dict[str, list[str]]:
      "Returns a `where`."""
      if not match_paths:
          return {}
      paths = []
      hosts = []
      for path in match_paths:
          if '::' in path:
              host, _, remainder = path.partition('::')
              hosts.append(host)
              if remainder:
                  paths.append(remainder)
          else:
              paths.append(path)
      result = {}
      if paths:
          ands = " and ".join(["hostpath like ?" for _ in paths])
          result[ands] = [f"%{x}%" for x in paths]
      if hosts:
          ands = " and ".join(["hostpath like ?" for _ in paths])
          result[ands] = [f"{x}" for x in hosts]
      return result
  
  
  def coverage_status_flag(dbf: File, copies: int) -> [str, str]:
      status = dbf.friendly_status
      flag = str(copies)
  
      if dbf.backuponly:
          return f"b/{status}", 'b'
  
      if dbf.fault == 0:
          # There's at least 1 copy (`dbf`)
          if copies == 1:
              status = 'CRITICAL'
          elif copies == 2:
              status = 'DEGRADED'
          else:
              status = 'healthy'
      elif dbf.fault == 1:
          flag = '!'
          status = 'MISSING'
      elif dbf.fault == 2:
          flag = '~'
          status = 'CHANGED'
      else:
          flag = '?'
          status = 'UNKNOWN-FAULT'
  
      return status, flag
  
  
  def coverage_helper(work: Work, dbfiles: list[File], verbose=False, coverage='all'):
      if not verbose and coverage != 'all':
          work.status.ephemeral_reasons += ["healthy"]
  
      for dbf in dbfiles:
          columns = "id, location, sha256, fault, figpath, backuponly"
          sha_matches = list(work.get_Files(columns=columns, sha256=dbf.sha256, fault=0))
          copies = len(set([x.location for x in sha_matches]))
          status, flag = coverage_status_flag(dbf, copies)
  
          if verbose:
              work.status.print_matches(status, dbf.figpath, *[x.figpath for x in sha_matches if x.figpath != dbf.figpath], sha256=dbf.sha256)
          else:
              work.fstatus(dbf, status, coverage=flag)
  
  
  def locate_helper(work: Work, missing_dbfiles: list[File], match_regexes: list[str], locate_method):
      # `files` needs to have columns: id, path, sha256, size, fault
      # NB: may need to revisit below approach if memory is a problem
      data = work.data
      # work.data.status.ephemeral_reasons = ["ambiguous"]
      work.status.ephemeral_reasons += ["backup-ignored"]
  
      for dbf in missing_dbfiles:
          work.progress(dbf)
  
          if not dbf.missing:
              work.fstatus(dbf, 'not-missing')
              continue
  
          # see if file came back to its original location
          result = check_File(dbf, force=True)
          if result == 'found':
              work.fstatus(dbf, 'found')
              dbf.commit(data)
              continue
  
          # see if database has any hash/path matches
          where = {"id != ?": dbf.id}
          if match_regexes:
              where.update(parse_regexes(match_regexes))
          columns = "id, location, hostpath, figpath, sha256, sha256_short, size, fault, ctime_ns, backuponly"
          maybe_candidates = work.get_Files(columns=columns, sha256_short=dbf.sha256_short, fault_detected_on=None, where=where)
          candidates = [x for x in maybe_candidates if x.sha256 == dbf.sha256]
          without_backups = remove_backups(candidates, work)
          foundf, faultedf = find_moved_file(dbf, without_backups)
  
          if not foundf:
              work.fstatus(dbf, 'not-found')
          elif len(foundf) == 1:
              foundf = foundf[0]
              work.status.print_matches('found', dbf.figpath, foundf.figpath, sha256=dbf.sha256)
              update_moved_file(data, missingf=dbf, movedf=foundf)
              for _ in faultedf:
                  printcols('', f'\t\t[skipped faulted] {_.figpath}')
                  # _.commit(data)
          else:  # multiple matches
              if locate_method == 'guess':  # user wants us to use the first guess
                  work.status.print_matches('found-guessed', dbf.figpath, foundf[0].figpath, sha256=dbf.sha256)
                  update_moved_file(data, missingf=dbf, movedf=foundf[0])
              else:
                  printcols('ambiguous', dbf.abspath)
                  # toshow = foundf[:4] if not args.all else foundf
                  toshow = foundf[:4]
                  for i, _ in enumerate(toshow):
                      printcols('', f'\t[guess {i + 1}] {_.figpath}')
                  if len(foundf) > len(toshow):
                      printcols('', f'\t[{len(foundf) - len(toshow)} more matches not shown, run --all to see them]')
                  work.fstatus(dbf, 'ambiguous')
  
  
  def dupe_helper(work: Work, files: list[Union[File, FsFile]], match_path: Optional[list[str]] = None, num_matches: Optional[int] = None):
      for f in files:
          matches = work.get_Files(sha256=f.sha256, fault=0, original=1, where={'abspath != ?': f.abspath})
          matches = list(remove_backups(matches, work))
          matches = list(remove_nonmatching(matches, match_path))
          # if match_path:
          #     matches = [y for y in matches if all([x.lower() in y.path.lower() for x in match_path])]
          actual_matches = len(matches)
          if num_matches is not None:
              if actual_matches != num_matches:
                  # user requested a specific number of matches and this didn't match
                  # --unique is handled here, since it sets num_matches = 0
                  continue
          else:
              # if user didn't set --num-matches or --unique, display all files with 1 or more matches
              if actual_matches == 0:
                  continue
          if actual_matches == 0:
              work.status('unique', f.abspath, sha256=f.sha256)
          elif actual_matches == 1:
              renamed = utils.format_rename(f.abspath, matches[0].abspath)
              work.status('has-dupe', renamed.formatted, highlight=renamed.changed, sha256=f.sha256)
          else:
              work.status.print_matches('has-dupe', f.abspath, *[x.abspath for x in matches], sha256=f.sha256)
              if len(matches) >= 2:
                  print()
          print()
  
  
  def had_element(l: list, *args):
      found = False
      for arg in args:
          try:
              l.remove(arg)
              found = True
          except ValueError:
              pass
      return found
  
  
  class CommandWithOptionalFlagValues(click.Command):
      "From https://stackoverflow.com/questions/40753999/python-click-make-option-value-optional"
      def parse_args(self, ctx, args):
          """ Translate any flag `--opt=value` as flag `--opt` with changed flag_value=value """
          # filter flags
          options = [o for o in self.params if isinstance(o, click.Option)]
          flags = [o for o in options if o.is_flag and not isinstance(o.flag_value, bool)]
          prefixes = {p: o for o in flags for p in o.opts if p.startswith('--')}
          for i, flag in enumerate(args):
              flag = flag.split('=')
              if flag[0] in prefixes and len(flag) > 1:
                  prefixes[flag[0]].flag_value = flag[1]
                  args[i] = flag[0]
  
          return super(CommandWithOptionalFlagValues, self).parse_args(ctx, args)
  
  
  # TODO: custom handle --locate
  # Also flag invalid combinations
  class FindCommandParser(click.Command):
      "From https://stackoverflow.com/questions/40753999/python-click-make-option-value-optional"
      def parse_args(self, ctx, args):
          """ Translate any flag `--opt=value` as flag `--opt` with changed flag_value=value """
          # filter flags
          options = [o for o in self.params if isinstance(o, click.Option)]
          flags = [o for o in options if o.is_flag and not isinstance(o.flag_value, bool)]
          prefixes = {p: o for o in flags for p in o.opts if p.startswith('--')}
          for i, flag in enumerate(args):
              flag = flag.split('=')
              if flag[0] in prefixes and len(flag) > 1:
                  prefixes[flag[0]].flag_value = flag[1]
                  args[i] = flag[0]
  
          return super(CommandWithOptionalFlagValues, self).parse_args(ctx, args)
  
  
  @cli.command(help='find known files matching regexes', cls=CommandWithOptionalFlagValues)
  @click.argument('regexes', nargs=-1, required=True)
  @click.option('--missing/--no-missing', '--miss/--no-miss', default=False, help="limit results to missing files only")
  @click.option('--fault', is_flag=True, default=False, help="limit results to files in fault state only")
  @click.option('--changed', is_flag=True, default=False, help="limit results to files in hash-changed state only")
  @click.option('--ok', is_flag=True, default=False, help="limit results to ok files only")
  @click.option('--info', is_flag=True, help='show all known info for each file')
  @click.option('--forget', '--for', is_flag=True, help='forget matching files (remove from the database)')
  @click.option('--check', is_flag=True, help='check matching files')
  @click.option('--dupes', is_flag=True, help='find duplicates')
  @click.option('--limit', type=int, default=None, help='return at most this many results')
  # TODO: combine the two below:
  # --locate shastat --locate /mnt/foo --locate etc.
  @click.option('--locate-missing', '--locate', '--loc', is_flag=True, flag_value='shastat')
  @click.option('--match-path', '--match', multiple=True, help='when locating missing files, the found path must contain this text')
  @click.option('--print', '-print', '-1', 'print1', is_flag=True, help="print matching paths similar to find -print")
  @click.option('--print0', '-print0', '-0', is_flag=True, help="print matching paths similar to find -print0")
  @click.option('--empty/--no-empty', default=None, help="select only empty or only non-empty files")
  @click.option('--force', is_flag=True, help="force check")
  @click.option('--coverage', is_flag=True, help="coverage report", flag_value='fault')
  @click.option('--verbose', is_flag=True, help="show additional information")
  # @click.option('--unknown/--no-unknown', default=False, help="limit results to unknown files only")
  # @click.option('--preview/--no-preview', default=False, help='do not update db, preview what would happen only')
  # TODO: Path has to be realpath, e.g. resolve symlinks
  @pass_work
  def find(work, regexes, missing, fault, changed, ok, info, forget, check, dupes, limit, locate_missing, match_path: list[str], print1, print0, empty, force, coverage, verbose):
      if [forget, check, locate_missing].count(True) > 1:
          print("error: can only specify one of the following at a time: --forget, --check, --locate-missing")
          return 1
  
      if not (forget or check or locate_missing):
          work.data.log = None
  
      if info or check:
          columns = "*"
      else:
          columns = "id, location, relpath, figpath, sha256, sha256_short, fault, tags"
          if empty or locate_missing:
              columns += ", size"
  
      work.status.ephemeral_reasons = []
  
      regexes += SINGLE_DASH_ARGS
  
      search = parse_expressions(regexes)
      dbfs = work.match_dbfiles(search=search, limit=limit, missing=missing, fault=fault, changed=changed, empty=empty, columns=columns)
  
      if match_path and not (locate_missing or dupes):
          print("must specify --locate-missing or --dupes to use --match-path")
          return 1
  
      # start_time = time.time()
      # cache = CallableCache(flush_on=lambda x: time.time() - start_time > 5)
  
      if locate_missing:
          locate_helper(work, dbfs, match_path, locate_missing)
      elif forget:
          work.forget_dbfs(dbfs)
      elif dupes:
          dupe_helper(work, dbfs, match_path)
      elif coverage:
          coverage_helper(work, dbfs, verbose=verbose, coverage=coverage)
      elif check:
          dbflist = list(dbfs)  # may need to find a different solution if this causes memory pressure
          work.status.total_files_expected = len(dbflist)
          dircache: dict[str, FsFiles] = defaultdict(dict)
          for dbf in dbflist:
              work.progress(dbf)
  
              # check dircache
              if dbf.dirpath not in dircache:
                  for fsf in dbf._host.scandir(dbf.dirpath):
                      dircache[dbf.dirpath][fsf.hostpath] = fsf
  
              result = check_File(dbf, dircache[dbf.dirpath], force=force)
              if result not in ['ok', 'missing']:
                  # dbf.commit(work.data)
                  work.commit(dbf)
              work.fstatus(dbf, result)
      else:
          # display modes
          for f in dbfs:
              if print1:
                  print(f.hostpath)
              elif print0:
                  print(f.hostpath, end='\0')
              elif info:
                  show_info(work, f)
                  print()
              else:
                  # sha256 = "[empty]" if f.size == 0 else f.sha256
                  extra = f"[{f.tags}]" if f.tags else None
                  work.fstatus(f, f.friendly_status, highlight=regexes, extra=extra)
  
  
  @cli.command(help='find files with matching SHA256')
  @click.argument('pathspec', nargs=-1, required=True)  # help='filename/SHA to search for')
  @pass_work
  def find_sha(work, pathspec):
      """If argument is a file, find shas matching that file; else find files
      matching sha fragments."""
      if any([os.path.isfile(x) for x in pathspec]):
          shas = [f'{HashThread.hash_file(path)}' for path in pathspec if os.path.isfile(path)]
          q_args = dict(sha256=shas)
      else:
          if all([len(x) == 8 for x in pathspec]):
              key = " and ".join(["sha256_short = ?" for _ in pathspec])
          else:
              key = " and ".join(["instr(sha256, ?)" for _ in pathspec])
          where = {key: list(pathspec)}
          q_args = dict(where=where)
  
      matches = list(work.data.get_all(File, **q_args))
      for f in matches:
          print(f.sha256, f.figpath)
  
      raise SystemExit(0 if len(matches) > 0 else 1)
  
  
  @cli.command(help='show disk usage for known files in pathspec')
  @click.argument('pathspec', nargs=-1)  # defaults to "."
  @click.option('--si/--no-si', default=False, help="same as du")
  @click.option('-h', '--human-readable', default=False, is_flag=True, help="same as du")
  @pass_work
  def du(data, pathspec, si, human_readable):
      data.log = None
      pathspec = pathspec or (".", )
      work = Work(data, pathspec=pathspec)
  
      for path in pathspec:
          size = 0
          for f in work.match_dbfiles(pathspec=[path, ], columns="id, size, path"):
              size += f.size
  
          if human_readable or si:
              scale = 1000.0 if si else 1024.0
              size = size / (scale ** 3)
              print(f"{size:10.3f}{'G' if si else 'Gi'}  {path}")
          else:
              print(f"{size:20,}  {path}")
  
  
  @cli.command(help='perform cleanup and error checking of database')
  @pass_work
  def lint(work):
      # fix_invalid_sha(work)
      fix_inconsistent_fault(work)
      fix_missing_location(work)
      # fix_missing_stat(data)
      # fix_missing_mountpoint(data, status)
      # fix_invalid_timestamps(data, status)
  
  
  @cli.command(help='initialize fig database')
  @pass_work
  def init(work):
      if work.data.db:
          print(f"fig is already initialized at {work.data.figdir}")
          exit(1)
  
      work.data.initial_setup()
      print(f"fig successfully initialized at {work.data.figdir}")
  
  
  @cli.command(help='collect new files')
  @click.argument('pathspec', nargs=-1)
  @pass_work
  def collect(work, pathspec):
      paths = pathspec or work.config.get('collect', [])
      for path in paths:
          print(f"collecting from: {path}")
          add_helper(work, (path,))
  
  
  # report format:
  # columns - id, command, started, elapsed, rc, # stdout lines
  @cli.command(help='show command history and results')
  @click.argument('id', default=None, required=False)
  @click.option('--stderr/--no-stderr', default=False, help='show stderr')
  @click.option('--command', help='only show log entries of this command')
  @click.option('--delete', '--del', '--rm', is_flag=True, help="delete matching log entries")
  @pass_work
  def log(work, id, stderr, command, delete):
      # don't log "log" commands!
      work.data.log = None
  
      output = []
  
      qargs = {}
      if command:
          qargs['command'] = command
  
      if id is not None:
          if id == 'last':
              log = next(work.data.get_all(Log, orderby='-id', **qargs))
          else:
              log = work.data.get(Log, id=id)
          if not log:
              print(f"log id {id} does not exist")
              raise SystemExit(1)
  
          if delete:
              click.echo(f"log {log.id} removed")
              log.remove(work.data)
              return
  
          output.append(f"{log.id} {log.started} {log.ended}")
          if log.argv and log.cwd:
              log.argv = ujson.loads(log.argv)
              output.append(f"argv: {log.argv} in {log.cwd} on {log.hostname}")
          output += [mesg for _, log_stderr, mesg in log.output.get() if not log_stderr or stderr]
          output.append(f"stats: {log.stats}")
      else:
          logs = work.data.get_all(Log, columns="id, started, ended, stats, command", orderby='-ended', **qargs)
          for log in logs:
              started = log.started.replace(microsecond=0)
              elapsed = utils.friendly_time(log.ended - log.started)
              if log.stats:
                  s = []
                  for label, count in ujson.loads(log.stats).items():
                      s.append(f"{count:,} {label}")
                  stats = "; ".join(s)
              else:
                  stats = "n/a"
              output.append(f"{log.id:<5} {log.command:<10} {started}  {elapsed:>6}  {stats}")
  
      utils.output_to_pager(output)
  
  
  @cli.command(help='compute hashdir of a directory')
  @click.argument('pathspec', nargs=-1, required=True)
  @click.option('--verbose/--no-verbose', default=False, help='show hash values of all files')
  @pass_work
  def hashdir(work, pathspec, verbose):
      for path in pathspec:
          results = []
          abspath = os.path.abspath(path)
          for f in work.match_dbfiles(pathspec=[path]):
              relpath = f.hostpath[len(abspath) + 1:]
              results += [f"{f.sha256}  ./{relpath}"]
          content = "\n".join(sorted(results)) + "\n"
          if verbose:
              print(content)
          h = hashlib.new("sha256")
          h.update(content.encode())
          finalhash = h.hexdigest()[:8]
          print(f"{finalhash}  {abspath}")
  
  
  @cli.command(help='perform tag operations on regexes')
  @click.argument('pathspec', nargs=-1, required=True)
  @click.option('--set', 'set_', default=None, multiple=True, help="set these tags")
  @click.option('--unset', default=None, multiple=True, help="unset these tags")
  @pass_work
  def tag(work, pathspec, set_, unset):
      """Short form: fig tag /paths +newtag1 +tag2
         Long form:  fig tag /paths --set newtag --set tag2, in this mode, short
         form is ignored, useful if filename starts with '+'
         Operation is very similar to `fig find` except that tags are searched as
         well and files matching the regexe and the tag will be shown."""
      if not set_ and not unset:
          set_ = [x[1:] for x in pathspec if x[0] == '+']
          unset = [x[1:] for x in SINGLE_DASH_ARGS]
          pathspec = [x for x in pathspec if x[0] != '+']
  
      files = work.match_dbfiles(columns="id, location, figpath, tags", pathspec=pathspec)
  
      # If no tags are set or unset, then just display existing tags for pathspec
      if not set_ and not unset:
          for f in files:
              tags = f.tags or ""
              print(f"{tags:<40} {f.figpath}")
          return
  
      for f in files:
          ftags = f.tags.split() if f.tags else []
          for t in set_:
              if t not in ftags:
                  ftags.append(t)
          for t in unset:
              try:
                  ftags.remove(t)
              except ValueError:
                  pass  # tag wasn't in list
          f.tags = " ".join(ftags)
          work.commit(f)
          # data.update(f)
  
  
  @cli.command(help='perform location operations')
  @click.option('--add', type=str, default=None, help="add a location")
  @click.option('--host', type=str, default=None)
  @pass_work
  def location(work, add, host):
      locations = list(work.data.get_all(Location))
  
      if add:
          label, _, mountpoint = add.partition('::')
          if not mountpoint.endswith("/"):
              mountpoint += "/"
  
          if work.data.get(Location, label=label, mountpoint=mountpoint, hostname=host):
              print("already exists")
              return 0
  
          loc = Location(label=label, hostname=host, mountpoint=mountpoint)
          work.commit(loc)
          print(f"added: {loc}")
      else:  # default, list all locations
          max_label = max([len(x.label) for x in locations])
          max_mountpoint = max([len(x.mountpoint) for x in locations])
          fmt = f"{{id:>3}}  {{label:<{max_label}}}  {{mountpoint:<{max_mountpoint}}}  {{desired_redundancy:<18}}  {{offline}}"
          header = dict(id='id', label='label', mountpoint='mountpoint', desired_redundancy='desired_redundancy', offline='offline')
          print(fmt.format(**header))
          for location in locations:
              if location.desired_redundancy is None:
                  location.desired_redundancy = ""
              print(fmt.format(**location))
  
  
  @cli.command(help='iterate over all rows')
  @pass_work
  def iterate(data):
      """Sample function showing how to iterate all rows and do something useful."""
  
      locations = dict()
      for row in data.sql("select id, mountpoint from location"):
          locations[row.id] = row.mountpoint
      print(locations)
  
      total = next(data.sql("select count(id) from file"))['count(id)']
      data.status.total_files_expected = total
  
      # cache = data.start_cache()
      for f in data.get_all(File, limit=10):
          mountpoint = locations[f.location]
          if not f.path.startswith(mountpoint):
              raise
          f.relpath = f.path[len(mountpoint):]
          # f.commit(cache)
          data.status.print(f"{f.id}: {f.path}")
  
  
  @cli.command(help='iterate over all rows')
  @pass_work
  def frag(work):
      cache = CallableCache(flush_on = lambda x: len(x) > 100)
  
      from copy import copy
  
      for dbf in work.data.sql("select * from file where id not in (select distinct file from fragment)", factory=File):
          dirname = os.path.dirname(dbf.relpath)
          parts = set(dirname.split('/'))
          work.status.progress_rate(mesg=":".join(parts))
          # dprint(parts)
          for part in parts:
              work.data.get_or_add(Fragment, value=part, file=dbf.id)
              # dprint(part, dbf.id)
              # if not work.data.get(Fragment, value=part, file=dbf.id):
              #     work.commit(Fragment(value=copy(part), file=copy(dbf.id)))
  
  
  @cli.command(help='iterate over all rows')
  @pass_work
  def test1(work):
      for row in work.data.sql("select * from file"):
          row2 = list(work.data.sql("select * from file2 where id = {}".format(row['id'])))
          if row2:
              continue
          print(row)
          break
  
  
  @cli.command(help='iterate over all rows')
  @pass_work
  def test2(work):
      for row in work.data.sql("select location, relpath, count(*) from file group by 1,2 having count(*) >= 2"):
          if not row['relpath'].startswith('takeout/Takeout/Google Photos/'):
              dprint(row)
              work.data.remove_ids(File, [row['id']])
          # for row2 in work.data.sql("select * from file where location=? and relpath=?", [row['location'], row['relpath']]):
          #     if row2['fault_detected_on']:
          #         print(row2)
          #         work.data.remove_ids(File, [row2['id']])
          # print(row)
  
  
  # Developer notes
  """
  Coding conventions
  ==================
  Internally, File Guardian always uses absolute paths. They are converted from
  relative paths as necessary on accepting input from the user and displaying
  output to the user.
  
  Paths are stored internally in Python3 str/unicode format. When they are read
  from the filesystem, they are interpretted as UTF-8. When they are shown to the
  user, they are encoded as UTF-8.
  """
  