import time
from functools import partialmethod

from clarabot_nano.utility.aio_sqlite import AbstractAioSQLiteDatabase


class TemporaryFileDatabase(AbstractAioSQLiteDatabase):
    """
    For writing files by remote requests, this creates a generic storage interface so the data parts can be written
    near the target location and be tracked until finished.
    The database or the relevant rows are not held during related handler operations. This means that parallel handler
    execution that would work on the same rows could pose race-conditions. This is not a problem, because we wish to
    serialize competing operations at a high level (before the handler invocations).
    """

    def _initialise(self):
        """
        This database holds the meta and state data of file uploads. It needs to be persisted so a server restart can
        continue without losing the upload sessions.
        There may be no need for it to be durable against a system crash. At that point most uploads will time-out or
        get abandoned as the system may not restart for a while anyways. The only operation that has to be durable is
        the insertion of a new temporary file tracking row, so those are guaranteed to get cleaned up by the handler.
        """
        self._con.execute("PRAGMA journal_mode = WAL;")
        self._con.execute("PRAGMA synchronous = NORMAL;")
        # Use foreign-key constructs to automatically delete orphan rows.
        self._con.execute("PRAGMA foreign_keys = ON;")
        # Recursive triggers are supposedly required for REPLACE to trigger ON DELETE CASCADE.
        self._con.execute("PRAGMA recursive_triggers = ON;")

    def _setup(self):
        self._con.execute(
            "CREATE TABLE IF NOT EXISTS TargetTempFile0 ("
            " id INTEGER PRIMARY KEY,"
            " account_id INTEGER,"
            " target_path TEXT,"  # final target path
            " tmp_name TEXT,"  # name of the tmp file in the directory of the target_path
            " meta BLOB,"  # arbitrary data to conflict/session manage the temporary file
            " last_used REAL"  # UTC timestamp when this temporary entry was used, cleanup is started by this
            ")"
        )
        self._con.execute(
            "CREATE UNIQUE INDEX IF NOT EXISTS TargetTempFile0__account_id__target_path ON "
            "TargetTempFile0(account_id, target_path)"
        )
        self._con.execute(
            "CREATE TABLE IF NOT EXISTS TargetCMACParts0 ("
            " id INTEGER,"
            " offset INTEGER,"
            " size INTEGER,"
            " mac BLOB,"
            " PRIMARY KEY (id, offset),"
            " FOREIGN KEY (id) REFERENCES TargetTempFile0 (id) ON DELETE CASCADE"
            ")"
        )
        self._con.execute("CREATE INDEX IF NOT EXISTS TargetCMACParts0__id ON  TargetCMACParts0(id)")

    def _get_tmp_file(self, account_id, target_path, update_last_used=True):
        """
        Get data for use of the temporary file. Update the last_used timestamp unless undesired.
        """
        rows = self._con.execute(
            "SELECT id, tmp_name, meta FROM TargetTempFile0 WHERE account_id = ? AND target_path = ?",
            (account_id, target_path),
        ).fetchall()
        if rows:
            if update_last_used:
                self._con.execute(
                    "UPDATE TargetTempFile0 SET last_used = ? WHERE account_id = ? AND target_path = ?",
                    (time.time(), account_id, target_path),
                )
            return rows[0]

    def _create_tmp_file(self, account_id, target_path, tmp_name, meta):
        """
        Create or reset a tmp file row and clear the part CMACs.
        """
        c = self._con.cursor()
        c.execute(
            "REPLACE INTO TargetTempFile0(account_id, target_path, tmp_name, meta, last_used) VALUES (?, ?, ?, ?, ?)",
            (account_id, target_path, tmp_name, meta, time.time()),
        )
        id_ = c.lastrowid
        c.execute("DELETE FROM TargetCMACParts0 WHERE id = ?", (id_,))
        return id_

    def _add_part_mac(self, id_, offset, size, mac):
        """
        Add a part-range and MAC value to the tmp file's collection.
        """
        self._con.execute(
            "REPLACE INTO TargetCMACParts0(id, offset, size, mac) VALUES (?, ?, ?, ?)", (id_, offset, size, mac)
        )

    def _get_all_part_mac(self, id_):
        return self._con.execute("SELECT offset, size, mac FROM TargetCMACParts0 WHERE id = ?", (id_,)).fetchall()

    def _drop_tmp_file(self, id_):
        self._con.execute("DELETE FROM TargetTempFile0 WHERE id = ?", (id_,))

    def _get_old_tmp_files(self, age, limit):
        # Age less then *or equals* helps with sanity when the system clock's resolution is low. (Windows)
        return self._con.execute(
            "SELECT id, account_id, target_path, tmp_name, meta, last_used FROM TargetTempFile0 "
            "WHERE last_used <= ? ORDER BY last_used ASC LIMIT ?",
            (time.time() - age, limit),
        ).fetchall()

    def _checkpoint(self):
        """
        We treat SQLITE_BUSY (1) result as an error, since this process shall be the sole one using the database. It
        can't be busy with anything else.
        """
        if self._con.execute("PRAGMA wal_checkpoint(TRUNCATE);").fetchall()[0][0] != 0:
            raise Exception("WAL checkpoint failed")

    get_tmp_file = partialmethod(AbstractAioSQLiteDatabase._run_job, _get_tmp_file)
    create_tmp_file = partialmethod(AbstractAioSQLiteDatabase._run_job, _create_tmp_file, post_process_fn=_checkpoint)
    add_part_mac = partialmethod(AbstractAioSQLiteDatabase._run_job, _add_part_mac)
    get_all_part_mac = partialmethod(AbstractAioSQLiteDatabase._run_job, _get_all_part_mac, begin_immediate=False)
    drop_tmp_file = partialmethod(AbstractAioSQLiteDatabase._run_job, _drop_tmp_file)
    get_old_tmp_files = partialmethod(AbstractAioSQLiteDatabase._run_job, _get_old_tmp_files, begin_immediate=False)
