#!/usr/bin/env python3

import json
import os
import ujson
import zstd

from collections import defaultdict
from contextlib import contextmanager
from dataclasses import dataclass
from datetime import datetime, timedelta
from enum import IntEnum
from pathlib import Path
from typing import Union, Dict, List

from . import db
from . import utils
from .utils import dprint


class ZstdJson:
    def __init__(self, data, compressed=False):
        if compressed:
            self.zdata = data
        else:
            self.zdata = zstd.compress(ujson.dumps(data).encode())

    def get(self):
        return ujson.loads(zstd.decompress(self.zdata))


class IntegrityError(Exception):
    pass


# fault_reason: "" -> "missing" -> "stat-changed" -> "hash-changed"
class FigDb(db.SqliteDb):
    SCHEMA = """
CREATE TABLE IF NOT EXISTS "meta" (
    last_resultset integer,
    last_vacuum timestamp
);

/* future work
create table if not exists "hash" (
    id integer primary key,
    hash1 varchar,
    hash2 varchar
);

create table if not exists "stat" (
    -- a status for a moment in time for a file, as well as a history
    id integer primary key,
    file integer not null,
    stat blob,  -- null if file was not present, else filesystem stat observed
    -- hash_id? integer,  -- if set, hash observed
    inode_flags integer,  -- if set, inode flags observed
    fault integer,        -- fault code
    createtime timestamp,
    foreign key(file) references file(id)
);

create table if not exists "newfile" (
    id integer primary key,
    locpath_id integer,
    hash_id integer,
    current_stat_id ??
    last_good_stat_id ??
    foreign key(locpath_id) references locpath(id),
    foreign key(hash_id) references hash(id)
);
*/

CREATE TABLE IF NOT EXISTS "file" (
    id integer primary key,
    location integer not null,
    relpath VARCHAR NOT NULL,    -- path relative to location mountpoint

    fault integer not null,      -- 0=ok, 1=missing, 2=hash-changed, MAYBE degraded_reason: 0=ok, 1=insufficient replicas, 2=last checked age too old, ...

    sha256 VARCHAR not null,
    sha256_short varchar not null,
    sha256_checked_on epoch,     -- unixepoch, second resolution

    stat stat,  -- in json format
    stat_checked_on epoch,       -- unixepoch, second resolution

    size INTEGER,
    ctime_ns integer,

    first_seen TIMESTAMP,
    fault_detected_on datetime,  -- TODO: move to fault table, and fault_id
    tags varchar,                -- TODO: normalize into tags db?

    lockss_copies integer,       -- if non-zero, overrides default for location

    pathdeprecated,  -- for legacy, remove eventually

    foreign key(location) references location(id),
    CONSTRAINT location_relpath UNIQUE(location, relpath) ON CONFLICT FAIL
);
CREATE INDEX idx_file_sha256 on file(sha256);
CREATE INDEX idx_file_path_sha256 on file(relpath, sha256); -- may need to fix this
CREATE INDEX idx_file_fault file(fault); -- needed to find missing files quickly

create view fileview as
    select f.*,
    l.hostname as hostname,
    l.label as label,
    l.mountpoint || f.relpath as hostpath,
    l.label || '::' || l.mountpoint || f.relpath as figpath,
    l.backuponly as backuponly
    from file as f
    inner join location as l
    where l.id = f.location;

CREATE TABLE IF NOT EXISTS "history" (
    id INTEGER NOT NULL PRIMARY KEY,
    event INTEGER NOT NULL,
    file_id INTEGER NOT NULL,
    date TIMESTAMP NOT NULL,
    data1 VARCHAR
);
CREATE INDEX idx_history_file_id on history(file_id);

CREATE TABLE IF NOT EXISTS "log" (
    id integer not null primary key,
    started timestamp not null,
    ended timestamp not null,
    argv varchar,
    command varchar,
    hostname varchar,
    cwd varchar,
    return_code integer,
    output zstd_json,
    stats varchar
);

create table if not exists "location" (
    id integer primary key,
    label varchar not null,
    mountpoint varchar not null,

    backuponly boolean,  -- if 1, this location contains only backup data -- can merge with desired_redundancy later
    offline boolean,  -- whether the storage is online or offline (cold storage)
    physical_location varchar,  -- used for differentiating offsite backup locations

    hostname varchar,
    comment varchar,

    desired_redundancy integer, -- desired redundancy for files at this location, set to 0 for backup only location
    constraint unique_location_entry unique(label, mountpoint) on conflict fail
);

"""

    def __init__(self, *args, **kwargs):
        db.sqlite3.register_adapter(os.stat_result, lambda x: ujson.dumps(x))
        db.sqlite3.register_converter("stat", lambda x: os.stat_result(ujson.loads(x)))

        # zstd
        db.sqlite3.register_adapter(ZstdJson, lambda x: x.zdata)
        db.sqlite3.register_converter("zstd_json", lambda x: ZstdJson(x, compressed=True))

        # db.sqlite3.register_converter("timestamp", lambda x: x)  # datetime.fromisoformat(x.decode()))

        super().__init__(*args, **kwargs)


class FigRowFactory(db.RowFactory):
    defaults: dict = {}

    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)

        # apply default values if not provided
        self.update({k: v for k, v in self.defaults.items() if k not in self.keys()})

        self._dirty = set()

    def __setitem__(self, key, value):
        if key[0] != "_" and self.get(key, None) != value:
            self._dirty.add(key)
        super().__setitem__(key, value)

    def get_columns(self, dirty_only=False):
        "Returns this row's values minus any starting with _"
        all_cols = {x: y for x, y in self.items() if not x.startswith('_')}
        if dirty_only:
            all_cols = {x: y for x, y in all_cols.items() if x in self._dirty}
        return all_cols

    def commit(self, data):
        "Commit this object to the database, either inserting or updating as necessary."
        if self.id is None:
            self.id = data.add(self)
        else:
            data.update(self)
        self._dirty.clear()
        return self.id

    def remove(self, data):
        "Remove this object from the database."
        if self.id:
            data.remove(self)
            self.id = None


class File(FigRowFactory):
    tablename = 'file'
    viewname = 'fileview'
    defaults = dict(id=None, sha256=None, stat=None)

    def __init__(self, *args, **kwargs):
        """Rows created by sqlite iwll have id set to the id number; rows created
        by the user will have id set to None."""
        super().__init__(*args, **kwargs)
        self._pending_history = []

        if self.id is None:  # object created by the user
            self.first_seen = datetime.now()
            self.add_history('first_seen', self.relpath)

    def __repr__(self):
        sha = self.get('sha256', None)
        sha = sha[:10] if sha is not None else 'None'
        tags = f" tags=[{self.tags}]" if self.tags else ""
        return f'<File {self.id} {self.figpath} sha={sha} fault={self.fault}{tags}>'

    @property
    def friendly_status(self) -> str:
        reasons = {0: "ok", 1: "missing", 2: "hash-changed"}
        try:
            return reasons[self.fault]
        except KeyError:
            dprint(f"unexpected fault code: {self.fault} from {self}")
            raise SystemExit(100)

    @property
    def friendly_sha(self) -> str:
        if not self.sha256:
            return "none"
        else:
            return self.sha256[:8]

    @property
    def stat_age(self) -> timedelta:
        last = self.stat_checked_on or self.first_seen
        return datetime.now() - last

    @property
    def sha_age(self) -> timedelta:
        last = self.sha256_checked_on or self.first_seen
        return datetime.now() - last

    def set_stat(self, stat: os.stat_result):
        if self.stat != stat:
            self.add_history('stat-changed', stat)
        self.stat = stat
        self.size = stat.st_size
        self.ctime_ns = stat.st_ctime_ns
        self.mark_stat_checked()

    def set_sha(self, sha256):
        if self.sha256 != sha256:
            self.add_history('hash-changed', sha256)
        self.sha256 = sha256
        self.sha256_short = sha256[:8]
        checktime = datetime.now()
        self.mark_sha_checked(checktime)
        self.mark_stat_checked(checktime)
        self.clear_fault()

    def mark_sha_checked(self, checktime=None):
        checktime = checktime or datetime.now()
        self.sha256_checked_on = int(checktime.timestamp())

    def mark_stat_checked(self, checktime=None):
        checktime = checktime or datetime.now()
        self.stat_checked_on = int(checktime.timestamp())

    def is_image(self):
        for ext in ['.jpg']:
            if ext in self.path.lower():
                return True
        return False

    def set_fault(self, reason):
        reasons = {"missing": 1, "hash-changed": 2}
        self.fault_detected_on = datetime.now()
        self.fault = reasons[reason]

    @property
    def missing(self) -> bool:
        return self.fault == 1

    def clear_fault(self):
        "Use to mark file as verified correct."
        self.fault_detected_on = None
        self.fault = 0

    def add_history(self, event_name: str, data=None):
        # TODO: refactor this into HistoryEvent
        event_name = event_name.replace("-", "_")
        event = HistoryEvent[event_name]
        if event_name == 'lost' and data is None:
            data = self.relpath
        if event.needs_data and not data:
            dprint(self, "error - no data - investigate")
            return
        history = History(event=event, data1=data)
        self._pending_history.append(history)

    def get_history(self, data):  # data: FigData
        self._history = list(data.get_all(History, file_id=self.id, orderby='-id'))
        return self._history

    def get_location(self, data):
        if not self._location:
            try:
                self._location = data.locations[self.location]
            except KeyError:
                self._location = 'unknown'
        return self._location

    @property
    def dirpath(self):
        return os.path.dirname(self.hostpath)

    def get_json(self) -> str:
        "Returns current File object in JSON format."

        def dtconvert(o):
            if isinstance(o, datetime):
                return o.__str__()

        cols = self.get_columns()
        if self._history:
            cols['history'] = [x.get_columns() for x in self._history]
            for h in cols['history']:
                del h['file_id']

        return json.dumps(cols, default=dtconvert)

    def commit(self, data):
        super().commit(data)
        for his in self._pending_history:
            his.file_id = self.id
            his.commit(data)
        self._pending_history.clear()
        return self.id

    def remove(self, data):
        for h in self.get_history(data):
            h.remove(data)
        super().remove(data)


class HistoryEvent(IntEnum):
    first_seen = 1     # file added to database
    stat_changed = 2   # file stat changed, data1=previous stat
    hash_changed = 3   # file hash changed, data1=previous hash
    lost = 4           # 4 file was lost, data1=None or missing path
    found = 5          # 5 file was found (returned from missing)
    merged = 6         # 6 history merged, data1=new file_id
    moved = 7          # 7 file moved, data1=previous location

    @property
    def needs_data(self):
        return self.value in [1, 2, 3, 4, 6, 7]


class History(FigRowFactory):
    tablename = 'history'
    defaults = dict(id=None, date=datetime.now())
    E = HistoryEvent

    @property
    def event_label(self):
        return ["", "first-seen", "stat-changed-to", "hash-changed-to",
                "lost", "found", "unused", "moved-from"][self.event]


class Event(FigRowFactory):
    tablename = 'event'
    defaults = dict(time=datetime.now())


class Meta(FigRowFactory):
    tablename = 'meta'


class Location(FigRowFactory):
    tablename = 'location'

    def __repr__(self):
        origback = 'original' if self.original else 'backup'
        return f'<Location {self.id} {self.label}::{self.mountpoint} host={self.hostname} {origback}>'


class Search(FigRowFactory):
    tablename = 'search'


class Fragment(FigRowFactory):
    tablename = 'fragment'


@dataclass
class FigData:
    figdir: str
    debug: bool = False
    no_init: bool = False

    DB_FILE = "fig.sqlite"

    def __post_init__(self):
        self.hostname = utils.hostname()
        self.figdir = Path(self.figdir)
        self.dbpath = self.figdir / self.DB_FILE

        if self.no_init:
            return

        try:
            self.db = self.get_db_connection()
        except db.DbError:
            self.db = None
            return

        try:
            self.meta = self.get(Meta)
        except db.sqlite3.OperationalError:
            self.db = None
            return

        self._load_locations()

    def get_db_connection(self):
        return FigDb(self.dbpath, debug=self.debug)

    def _load_locations(self):
        self.locations = {}
        for loc in self.get_all(Location):
            self.locations[loc.id] = loc

    @contextmanager
    def transaction(self):
        try:
            yield self.db.begin_transaction()
            self.db.commit_transaction()
        finally:
            pass

    def periodic_commit(self, max_time_seconds=1):
        self.db.periodic_commit(max_time_seconds)

    def sql(self, statement, parameters=None, factory=FigRowFactory):
        return self.db.query(statement, factory, parameters)

    def get(self, rowcls, **kwargs):
        return self.db.select_one(rowcls, **kwargs)

    def get_or_add(self, rowcls, **kwargs):
        result = self.get(rowcls, **kwargs)
        if not result:
            result = rowcls(**kwargs)
            result.commit(self)
        return result

    def get_all(self, rowcls, columns="*", **kwargs):
        return self.db.select(rowcls, expr=columns, **kwargs)

    def get_count(self, cls, *args, **kwargs):
        x = self.db.select_raw("count(*) as count", cls.tablename, *args, **kwargs)
        return next(x)['count']

    def commit_helper(self, obj: FigRowFactory, columns=None, verbose=False):
        if obj.id:
            return self.update(obj, columns, verbose)
        # Returns id of newly created object
        dbdata = obj.get_columns()
        columns = ', '.join(dbdata.keys())
        values = list(dbdata.values())
        try:
            return self.db.insert(obj.tablename, columns, values)
        except db.IntegrityError:
            raise IntegrityError()

    def add(self, obj: FigRowFactory):
        # Returns id of newly created object.
        dbdata = obj.get_columns()
        columns = ', '.join(dbdata.keys())
        values = list(dbdata.values())
        try:
            return self.db.insert(obj.tablename, columns, values)
        except db.IntegrityError:
            raise IntegrityError()

    def update_ids(self, cls, ids, update):
        self.db.update(cls.tablename, where=f"id in ({', '.join(map(str, ids))})", update=update)

    def update(self, obj: FigRowFactory, columns=None, verbose=False):
        if columns:
            update = {col: obj[col] for col in columns.split()}
        else:
            update = obj.get_columns(dirty_only=True)

        if update:
            self.db.update(obj.tablename, where=f"id = {obj.id}", update=update)

    def remove(self, objs: Union[FigRowFactory, list]):
        tables: Dict[str, List] = defaultdict(list)
        if not isinstance(objs, list):
            objs = [objs]
        for obj in objs:
            tables[obj.tablename].append(obj.id)
        for table, ids in tables.items():
            self.db.delete_by_id(table, ids)

    def remove_ids(self, cls, ids: Union[int, list]):
        "delete one or more ids from table"
        if not isinstance(ids, list):
            ids = [ids, ]
        if ids:
            self.db.delete_by_id(cls.tablename, ids)

    def remove_files_and_histories(self, file_objs=None, file_ids=None):
        if file_objs:
            file_ids = [x.id for x in file_objs]
        if file_ids:
            self.remove_ids(File, file_ids)
            history_ids = [x.id for x in self.get_all(History, file_id=file_ids, columns="id")]
            self.remove_ids(History, history_ids)

    # --- Tag ---

    def Tags(self):
        tags = defaultdict(int)
        for f in self.Files():
            for t in f.tags.split():
                tags[t] += 1
        return tags

    def add_tags(self, dbfile, tags):
        old = set(dbfile.tags.split()) if dbfile.tags else set()
        new = set(tags.split()) - old
        # self.add_history(2, " ".join(sorted(new)))
        old |= new
        tags = " ".join(sorted(old))
        self.db.update(File.tablename, f'id = {dbfile.id}', dict(tags=tags))
        print("tags:", tags)

    def vacuum(self):
        # Vacuum cannot be run in a transaction; don't use a cursor; execute
        # directly with the db connection
        self.db.connection.execute("vacuum")
        self.db.execute("update meta set last_vacuum = ?", [datetime.now()])

    def initial_setup(self):
        os.makedirs(self.figdir, exist_ok=True)

        dbfile = self.figdir / "fig.sqlite"
        dbfile.touch()
        self.db = FigDb(dbfile)
        self.db.create_schema()
        self.add(Meta(last_vacuum=None))

    def copy(self):
        selfcopy = FigData(None, no_init=True)
        for x in "hostname figdir config status meta global_figignore".split():
            setattr(selfcopy, x, getattr(self, x))
        selfcopy.db = self.get_db_connection()
        return selfcopy


class Log(FigRowFactory):
    tablename = 'log'


#    def search(self, table, text: list, limit_columns: list=None):
#        """Search all columns of a table to see which rows contain specified
#        text. All elements of the search text must be present (boolean AND).
#        Any column may match the text (boolean OR). If limit_columns is
#        specified, limit to those columns only."""
#        if limit_columns is None:
#            limit_columns = table.columns
#        cols = [column.key for column in table.__table__.columns]
#        cols = [getattr(table, x) for x in cols]

#        ands = []
#        for t in text:
#            ors = [c.contains(t) for c in cols]
#            ands.append(sa.or_(*ors))
#        return self.query(table, sa.and_(*ands))

#!/usr/bin/env python3

import json
import os
import ujson
import zstd

from collections import defaultdict
from contextlib import contextmanager
from dataclasses import dataclass
from datetime import datetime, timedelta
from enum import IntEnum
from pathlib import Path
from typing import Union, Dict, List

from . import db
from . import utils
from .utils import dprint


class ZstdJson:
    def __init__(self, data, compressed=False):
        if compressed:
            self.zdata = data
        else:
            self.zdata = zstd.compress(ujson.dumps(data).encode())

    def get(self):
        return ujson.loads(zstd.decompress(self.zdata))


class IntegrityError(Exception):
    pass


# fault_reason: "" -> "missing" -> "stat-changed" -> "hash-changed"
class FigDb(db.SqliteDb):
    SCHEMA = """
CREATE TABLE IF NOT EXISTS "meta" (
    last_resultset integer,
    last_vacuum timestamp
);

/* future work
create table if not exists "hash" (
    id integer primary key,
    hash1 varchar,
    hash2 varchar
);

create table if not exists "stat" (
    -- a status for a moment in time for a file, as well as a history
    id integer primary key,
    file integer not null,
    stat blob,  -- null if file was not present, else filesystem stat observed
    -- hash_id? integer,  -- if set, hash observed
    inode_flags integer,  -- if set, inode flags observed
    fault integer,        -- fault code
    createtime timestamp,
    foreign key(file) references file(id)
);

create table if not exists "newfile" (
    id integer primary key,
    locpath_id integer,
    hash_id integer,
    current_stat_id ??
    last_good_stat_id ??
    foreign key(locpath_id) references locpath(id),
    foreign key(hash_id) references hash(id)
);
*/

CREATE TABLE IF NOT EXISTS "file" (
    id integer primary key,
    location integer not null,
    relpath VARCHAR NOT NULL,    -- path relative to location mountpoint

    fault integer not null,      -- 0=ok, 1=missing, 2=hash-changed, MAYBE degraded_reason: 0=ok, 1=insufficient replicas, 2=last checked age too old, ...

    sha256 VARCHAR not null,
    sha256_short varchar not null,
    sha256_checked_on epoch,     -- unixepoch, second resolution

    stat stat,  -- in json format
    stat_checked_on epoch,       -- unixepoch, second resolution

    size INTEGER,
    ctime_ns integer,

    first_seen TIMESTAMP,
    fault_detected_on datetime,  -- TODO: move to fault table, and fault_id
    tags varchar,                -- TODO: normalize into tags db?

    lockss_copies integer,       -- if non-zero, overrides default for location

    pathdeprecated,  -- for legacy, remove eventually

    foreign key(location) references location(id),
    CONSTRAINT location_relpath UNIQUE(location, relpath) ON CONFLICT FAIL
);
CREATE INDEX idx_file_sha256 on file(sha256);
CREATE INDEX idx_file_path_sha256 on file(relpath, sha256); -- may need to fix this
CREATE INDEX idx_file_fault file(fault); -- needed to find missing files quickly

create view fileview as
    select f.*,
    l.hostname as hostname,
    l.label as label,
    l.mountpoint || f.relpath as hostpath,
    l.label || '::' || l.mountpoint || f.relpath as figpath,
    l.backuponly as backuponly
    from file as f
    inner join location as l
    where l.id = f.location;

CREATE TABLE IF NOT EXISTS "history" (
    id INTEGER NOT NULL PRIMARY KEY,
    event INTEGER NOT NULL,
    file_id INTEGER NOT NULL,
    date TIMESTAMP NOT NULL,
    data1 VARCHAR
);
CREATE INDEX idx_history_file_id on history(file_id);

CREATE TABLE IF NOT EXISTS "log" (
    id integer not null primary key,
    started timestamp not null,
    ended timestamp not null,
    argv varchar,
    command varchar,
    hostname varchar,
    cwd varchar,
    return_code integer,
    output zstd_json,
    stats varchar
);

create table if not exists "location" (
    id integer primary key,
    label varchar not null,
    mountpoint varchar not null,

    backuponly boolean,  -- if 1, this location contains only backup data -- can merge with desired_redundancy later
    offline boolean,  -- whether the storage is online or offline (cold storage)
    physical_location varchar,  -- used for differentiating offsite backup locations

    hostname varchar,
    comment varchar,

    desired_redundancy integer, -- desired redundancy for files at this location, set to 0 for backup only location
    constraint unique_location_entry unique(label, mountpoint) on conflict fail
);

"""

    def __init__(self, *args, **kwargs):
        db.sqlite3.register_adapter(os.stat_result, lambda x: ujson.dumps(x))
        db.sqlite3.register_converter("stat", lambda x: os.stat_result(ujson.loads(x)))

        # zstd
        db.sqlite3.register_adapter(ZstdJson, lambda x: x.zdata)
        db.sqlite3.register_converter("zstd_json", lambda x: ZstdJson(x, compressed=True))

        # db.sqlite3.register_converter("timestamp", lambda x: x)  # datetime.fromisoformat(x.decode()))

        super().__init__(*args, **kwargs)


class FigRowFactory(db.RowFactory):
    defaults: dict = {}

    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)

        # apply default values if not provided
        self.update({k: v for k, v in self.defaults.items() if k not in self.keys()})

        self._dirty = set()

    def __setitem__(self, key, value):
        if key[0] != "_" and self.get(key, None) != value:
            self._dirty.add(key)
        super().__setitem__(key, value)

    def get_columns(self, dirty_only=False):
        "Returns this row's values minus any starting with _"
        all_cols = {x: y for x, y in self.items() if not x.startswith('_')}
        if dirty_only:
            all_cols = {x: y for x, y in all_cols.items() if x in self._dirty}
        return all_cols

    def commit(self, data):
        "Commit this object to the database, either inserting or updating as necessary."
        if self.id is None:
            self.id = data.add(self)
        else:
            data.update(self)
        self._dirty.clear()
        return self.id

    def remove(self, data):
        "Remove this object from the database."
        if self.id:
            data.remove(self)
            self.id = None


class File(FigRowFactory):
    tablename = 'file'
    viewname = 'fileview'
    defaults = dict(id=None, sha256=None, stat=None)

    def __init__(self, *args, **kwargs):
        """Rows created by sqlite iwll have id set to the id number; rows created
        by the user will have id set to None."""
        super().__init__(*args, **kwargs)
        self._pending_history = []

        if self.id is None:  # object created by the user
            self.first_seen = datetime.now()
            self.add_history('first_seen', self.relpath)

    def __repr__(self):
        sha = self.get('sha256', None)
        sha = sha[:10] if sha is not None else 'None'
        tags = f" tags=[{self.tags}]" if self.tags else ""
        return f'<File {self.id} {self.figpath} sha={sha} fault={self.fault}{tags}>'

    @property
    def friendly_status(self) -> str:
        reasons = {0: "ok", 1: "missing", 2: "hash-changed"}
        try:
            return reasons[self.fault]
        except KeyError:
            dprint(f"unexpected fault code: {self.fault} from {self}")
            raise SystemExit(100)

    @property
    def friendly_sha(self) -> str:
        if not self.sha256:
            return "none"
        else:
            return self.sha256[:8]

    @property
    def stat_age(self) -> timedelta:
        last = self.stat_checked_on or self.first_seen
        return datetime.now() - last

    @property
    def sha_age(self) -> timedelta:
        last = self.sha256_checked_on or self.first_seen
        return datetime.now() - last

    def set_stat(self, stat: os.stat_result):
        if self.stat != stat:
            self.add_history('stat-changed', stat)
        self.stat = stat
        self.size = stat.st_size
        self.ctime_ns = stat.st_ctime_ns
        self.mark_stat_checked()

    def set_sha(self, sha256):
        if self.sha256 != sha256:
            self.add_history('hash-changed', sha256)
        self.sha256 = sha256
        self.sha256_short = sha256[:8]
        checktime = datetime.now()
        self.mark_sha_checked(checktime)
        self.mark_stat_checked(checktime)
        self.clear_fault()

    def mark_sha_checked(self, checktime=None):
        checktime = checktime or datetime.now()
        self.sha256_checked_on = int(checktime.timestamp())

    def mark_stat_checked(self, checktime=None):
        checktime = checktime or datetime.now()
        self.stat_checked_on = int(checktime.timestamp())

    def is_image(self):
        for ext in ['.jpg']:
            if ext in self.path.lower():
                return True
        return False

    def set_fault(self, reason):
        reasons = {"missing": 1, "hash-changed": 2}
        self.fault_detected_on = datetime.now()
        self.fault = reasons[reason]

    @property
    def missing(self) -> bool:
        return self.fault == 1

    def clear_fault(self):
        "Use to mark file as verified correct."
        self.fault_detected_on = None
        self.fault = 0

    def add_history(self, event_name: str, data=None):
        # TODO: refactor this into HistoryEvent
        event_name = event_name.replace("-", "_")
        event = HistoryEvent[event_name]
        if event_name == 'lost' and data is None:
            data = self.relpath
        if event.needs_data and not data:
            dprint(self, "error - no data - investigate")
            return
        history = History(event=event, data1=data)
        self._pending_history.append(history)

    def get_history(self, data):  # data: FigData
        self._history = list(data.get_all(History, file_id=self.id, orderby='-id'))
        return self._history

    def get_location(self, data):
        if not self._location:
            try:
                self._location = data.locations[self.location]
            except KeyError:
                self._location = 'unknown'
        return self._location

    @property
    def dirpath(self):
        return os.path.dirname(self.hostpath)

    def get_json(self) -> str:
        "Returns current File object in JSON format."

        def dtconvert(o):
            if isinstance(o, datetime):
                return o.__str__()

        cols = self.get_columns()
        if self._history:
            cols['history'] = [x.get_columns() for x in self._history]
            for h in cols['history']:
                del h['file_id']

        return json.dumps(cols, default=dtconvert)

    def commit(self, data):
        super().commit(data)
        for his in self._pending_history:
            his.file_id = self.id
            his.commit(data)
        self._pending_history.clear()
        return self.id

    def remove(self, data):
        for h in self.get_history(data):
            h.remove(data)
        super().remove(data)


class HistoryEvent(IntEnum):
    first_seen = 1     # file added to database
    stat_changed = 2   # file stat changed, data1=previous stat
    hash_changed = 3   # file hash changed, data1=previous hash
    lost = 4           # 4 file was lost, data1=None or missing path
    found = 5          # 5 file was found (returned from missing)
    merged = 6         # 6 history merged, data1=new file_id
    moved = 7          # 7 file moved, data1=previous location

    @property
    def needs_data(self):
        return self.value in [1, 2, 3, 4, 6, 7]


class History(FigRowFactory):
    tablename = 'history'
    defaults = dict(id=None, date=datetime.now())
    E = HistoryEvent

    @property
    def event_label(self):
        return ["", "first-seen", "stat-changed-to", "hash-changed-to",
                "lost", "found", "unused", "moved-from"][self.event]


class Event(FigRowFactory):
    tablename = 'event'
    defaults = dict(time=datetime.now())


class Meta(FigRowFactory):
    tablename = 'meta'


class Location(FigRowFactory):
    tablename = 'location'

    def __repr__(self):
        origback = 'original' if self.original else 'backup'
        return f'<Location {self.id} {self.label}::{self.mountpoint} host={self.hostname} {origback}>'


class Search(FigRowFactory):
    tablename = 'search'


class Fragment(FigRowFactory):
    tablename = 'fragment'


@dataclass
class FigData:
    figdir: str
    debug: bool = False
    no_init: bool = False

    DB_FILE = "fig.sqlite"

    def __post_init__(self):
        self.hostname = utils.hostname()
        self.figdir = Path(self.figdir)
        self.dbpath = self.figdir / self.DB_FILE

        if self.no_init:
            return

        try:
            self.db = self.get_db_connection()
        except db.DbError:
            self.db = None
            return

        try:
            self.meta = self.get(Meta)
        except db.sqlite3.OperationalError:
            self.db = None
            return

        self._load_locations()

    def get_db_connection(self):
        return FigDb(self.dbpath, debug=self.debug)

    def _load_locations(self):
        self.locations = {}
        for loc in self.get_all(Location):
            self.locations[loc.id] = loc

    @contextmanager
    def transaction(self):
        try:
            yield self.db.begin_transaction()
            self.db.commit_transaction()
        finally:
            pass

    def periodic_commit(self, max_time_seconds=1):
        self.db.periodic_commit(max_time_seconds)

    def sql(self, statement, parameters=None, factory=FigRowFactory):
        return self.db.query(statement, factory, parameters)

    def get(self, rowcls, **kwargs):
        return self.db.select_one(rowcls, **kwargs)

    def get_or_add(self, rowcls, **kwargs):
        result = self.get(rowcls, **kwargs)
        if not result:
            result = rowcls(**kwargs)
            result.commit(self)
        return result

    def get_all(self, rowcls, columns="*", **kwargs):
        return self.db.select(rowcls, expr=columns, **kwargs)

    def get_count(self, cls, *args, **kwargs):
        x = self.db.select_raw("count(*) as count", cls.tablename, *args, **kwargs)
        return next(x)['count']

    def commit_helper(self, obj: FigRowFactory, columns=None, verbose=False):
        if obj.id:
            return self.update(obj, columns, verbose)
        # Returns id of newly created object
        dbdata = obj.get_columns()
        columns = ', '.join(dbdata.keys())
        values = list(dbdata.values())
        try:
            return self.db.insert(obj.tablename, columns, values)
        except db.IntegrityError:
            raise IntegrityError()

    def add(self, obj: FigRowFactory):
        # Returns id of newly created object.
        dbdata = obj.get_columns()
        columns = ', '.join(dbdata.keys())
        values = list(dbdata.values())
        try:
            return self.db.insert(obj.tablename, columns, values)
        except db.IntegrityError:
            raise IntegrityError()

    def update_ids(self, cls, ids, update):
        self.db.update(cls.tablename, where=f"id in ({', '.join(map(str, ids))})", update=update)

    def update(self, obj: FigRowFactory, columns=None, verbose=False):
        if columns:
            update = {col: obj[col] for col in columns.split()}
        else:
            update = obj.get_columns(dirty_only=True)

        if update:
            self.db.update(obj.tablename, where=f"id = {obj.id}", update=update)

    def remove(self, objs: Union[FigRowFactory, list]):
        tables: Dict[str, List] = defaultdict(list)
        if not isinstance(objs, list):
            objs = [objs]
        for obj in objs:
            tables[obj.tablename].append(obj.id)
        for table, ids in tables.items():
            self.db.delete_by_id(table, ids)

    def remove_ids(self, cls, ids: Union[int, list]):
        "delete one or more ids from table"
        if not isinstance(ids, list):
            ids = [ids, ]
        if ids:
            self.db.delete_by_id(cls.tablename, ids)

    def remove_files_and_histories(self, file_objs=None, file_ids=None):
        if file_objs:
            file_ids = [x.id for x in file_objs]
        if file_ids:
            self.remove_ids(File, file_ids)
            history_ids = [x.id for x in self.get_all(History, file_id=file_ids, columns="id")]
            self.remove_ids(History, history_ids)

    # --- Tag ---

    def Tags(self):
        tags = defaultdict(int)
        for f in self.Files():
            for t in f.tags.split():
                tags[t] += 1
        return tags

    def add_tags(self, dbfile, tags):
        old = set(dbfile.tags.split()) if dbfile.tags else set()
        new = set(tags.split()) - old
        # self.add_history(2, " ".join(sorted(new)))
        old |= new
        tags = " ".join(sorted(old))
        self.db.update(File.tablename, f'id = {dbfile.id}', dict(tags=tags))
        print("tags:", tags)

    def vacuum(self):
        # Vacuum cannot be run in a transaction; don't use a cursor; execute
        # directly with the db connection
        self.db.connection.execute("vacuum")
        self.db.execute("update meta set last_vacuum = ?", [datetime.now()])

    def initial_setup(self):
        os.makedirs(self.figdir, exist_ok=True)

        dbfile = self.figdir / "fig.sqlite"
        dbfile.touch()
        self.db = FigDb(dbfile)
        self.db.create_schema()
        self.add(Meta(last_vacuum=None))

    def copy(self):
        selfcopy = FigData(None, no_init=True)
        for x in "hostname figdir config status meta global_figignore".split():
            setattr(selfcopy, x, getattr(self, x))
        selfcopy.db = self.get_db_connection()
        return selfcopy


class Log(FigRowFactory):
    tablename = 'log'


#    def search(self, table, text: list, limit_columns: list=None):
#        """Search all columns of a table to see which rows contain specified
#        text. All elements of the search text must be present (boolean AND).
#        Any column may match the text (boolean OR). If limit_columns is
#        specified, limit to those columns only."""
#        if limit_columns is None:
#            limit_columns = table.columns
#        cols = [column.key for column in table.__table__.columns]
#        cols = [getattr(table, x) for x in cols]

#        ands = []
#        for t in text:
#            ors = [c.contains(t) for c in cols]
#            ands.append(sa.or_(*ors))
#        return self.query(table, sa.and_(*ands))

#!/usr/bin/env python3 # fault_reason: "" -> "missing" -> "stat-changed" -> "hash-changed" CREATE TABLE IF NOT EXISTS "meta" (     last_resultset integer,     last_vacuum timestamp );  /* future work create table if not exists "hash" (     id integer primary key,     hash1 varchar,     hash2 varchar );  create table if not exists "stat" (     -- a status for a moment in time for a file, as well as a history     id integer primary key,     file integer not null,     stat blob,  -- null if file was not present, else filesystem stat observed     -- hash_id? integer,  -- if set, hash observed     inode_flags integer,  -- if set, inode flags observed     fault integer,        -- fault code     createtime timestamp,     foreign key(file) references file(id) );  create table if not exists "newfile" (     id integer primary key,     locpath_id integer,     hash_id integer,     current_stat_id ??     last_good_stat_id ??     foreign key(locpath_id) references locpath(id),     foreign key(hash_id) references hash(id) ); */  CREATE TABLE IF NOT EXISTS "file" (     id integer primary key,     location integer not null,     relpath VARCHAR NOT NULL,    -- path relative to location mountpoint      fault integer not null,      -- 0=ok, 1=missing, 2=hash-changed, MAYBE degraded_reason: 0=ok, 1=insufficient replicas, 2=last checked age too old, ...      sha256 VARCHAR not null,     sha256_short varchar not null,     sha256_checked_on epoch,     -- unixepoch, second resolution      stat stat,  -- in json format     stat_checked_on epoch,       -- unixepoch, second resolution      size INTEGER,     ctime_ns integer,      first_seen TIMESTAMP,     fault_detected_on datetime,  -- TODO: move to fault table, and fault_id     tags varchar,                -- TODO: normalize into tags db?      lockss_copies integer,       -- if non-zero, overrides default for location      pathdeprecated,  -- for legacy, remove eventually      foreign key(location) references location(id),     CONSTRAINT location_relpath UNIQUE(location, relpath) ON CONFLICT FAIL ); CREATE INDEX idx_file_sha256 on file(sha256); CREATE INDEX idx_file_path_sha256 on file(relpath, sha256); -- may need to fix this CREATE INDEX idx_file_fault file(fault); -- needed to find missing files quickly  create view fileview as     select f.*,     l.hostname as hostname,     l.label as label,     l.mountpoint || f.relpath as hostpath,     l.label || '::' || l.mountpoint || f.relpath as figpath,     l.backuponly as backuponly     from file as f     inner join location as l     where l.id = f.location;  CREATE TABLE IF NOT EXISTS "history" (     id INTEGER NOT NULL PRIMARY KEY,     event INTEGER NOT NULL,     file_id INTEGER NOT NULL,     date TIMESTAMP NOT NULL,     data1 VARCHAR ); CREATE INDEX idx_history_file_id on history(file_id);  CREATE TABLE IF NOT EXISTS "log" (     id integer not null primary key,     started timestamp not null,     ended timestamp not null,     argv varchar,     command varchar,     hostname varchar,     cwd varchar,     return_code integer,     output zstd_json,     stats varchar );  create table if not exists "location" (     id integer primary key,     label varchar not null,     mountpoint varchar not null,      backuponly boolean,  -- if 1, this location contains only backup data -- can merge with desired_redundancy later     offline boolean,  -- whether the storage is online or offline (cold storage)     physical_location varchar,  -- used for differentiating offsite backup locations      hostname varchar,     comment varchar,      desired_redundancy integer, -- desired redundancy for files at this location, set to 0 for backup only location     constraint unique_location_entry unique(label, mountpoint) on conflict fail ); # zstd # db.sqlite3.register_converter("timestamp", lambda x: x)  # datetime.fromisoformat(x.decode())) # apply default values if not provided Rows created by sqlite iwll have id set to the id number; rows created         by the user will have id set to None. # object created by the user # TODO: refactor this into HistoryEvent # data: FigData # file added to database # file stat changed, data1=previous stat # file hash changed, data1=previous hash # 4 file was lost, data1=None or missing path # 5 file was found (returned from missing) # 6 history merged, data1=new file_id # 7 file moved, data1=previous location # Returns id of newly created object # Returns id of newly created object. # --- Tag --- # self.add_history(2, " ".join(sorted(new))) # Vacuum cannot be run in a transaction; don't use a cursor; execute # directly with the db connection #    def search(self, table, text: list, limit_columns: list=None): #        """Search all columns of a table to see which rows contain specified #        text. All elements of the search text must be present (boolean AND). #        Any column may match the text (boolean OR). If limit_columns is #        specified, limit to those columns only.""" #        if limit_columns is None: #            limit_columns = table.columns #        cols = [column.key for column in table.__table__.columns] #        cols = [getattr(table, x) for x in cols] #        ands = [] #        for t in text: #            ors = [c.contains(t) for c in cols] #            ands.append(sa.or_(*ors)) #        return self.query(table, sa.and_(*ands))