"""
MIT License

Copyright (C) 2021 ROCKY4546
https://github.com/rocky4546

This file is part of Cabernet

Permission is hereby granted, free of charge, to any person obtaining a copy of this software
and associated documentation files (the "Software"), to deal in the Software without restriction,
including without limitation the rights to use, copy, modify, merge, publish, distribute,
sublicense, and/or sell copies of the Software, and to permit persons to whom the Software
is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or
substantial portions of the Software.
"""

import datetime
import sqlite3
import uuid

from lib.db.db import DB
from lib.common.decorators import Backup
from lib.common.decorators import Restore

DB_TASK_TABLE = 'task'
DB_TRIGGER_TABLE = 'trigger'
DB_CONFIG_NAME = 'db_files-scheduler_db'

sqlcmds = {
    'ct': [
        """
        CREATE TABLE IF NOT EXISTS task (
            taskid      VARCHAR(255) NOT NULL,
            area      VARCHAR(255) NOT NULL,
            title     VARCHAR(255) NOT NULL,
            namespace VARCHAR(255) NOT NULL,
            instance  VARCHAR(255),
            funccall  VARCHAR(255) NOT NULL,
            lastran   TIMESTAMP,
            duration  INTEGER,
            priority  INTEGER,
            threadtype VARCHAR(255) 
                CHECK( threadtype IN ('inline', 'thread', 'process') ) NOT NULL,
            active    BOOLEAN DEFAULT 0,
            description TEXT,
            UNIQUE(area, title)
            )
        """,
        """
        CREATE TABLE IF NOT EXISTS trigger (
            uuid      VARCHAR(255) NOT NULL,
            area      VARCHAR(255) NOT NULL,
            title      VARCHAR(255) NOT NULL,
            timetype      VARCHAR(255)
                CHECK( timetype IN ('daily', 'weekly', 'interval', 'startup') ) NOT NULL,
            timelimit INTEGER,
            timeofday      VARCHAR(255),
            dayofweek VARCHAR(255)
                CHECK( dayofweek IN ('Sunday', 'Monday', 'Tuesday', 'Wednesday',
                'Thursday', 'Friday', 'Saturday') ),
            interval  INTEGER,
            randdur   INTEGER,
            UNIQUE(uuid)
            FOREIGN KEY(area, title) REFERENCES task(area, title)
            )
        """
    ],
    'dt': [
        """
        DROP TABLE IF EXISTS trigger
        """,
        """
        DROP TABLE IF EXISTS task
        """
    ],

    'task_add':
        """
        INSERT INTO task (
            taskid, area, title, namespace, instance, funccall,
            priority, threadtype, description
            ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ? )
        """,
    'task_active_update':
        """
        UPDATE task SET active=?
        WHERE area LIKE ? AND title LIKE ?
        """,
    'task_finish_update':
        """
        UPDATE task SET active=0,
        lastran=?, duration=?
        WHERE area=? AND title=?
        """,
    'task_get':
        """
        SELECT *
        FROM task
        WHERE area LIKE ? AND title LIKE ?
        ORDER BY task.area ASC, task.title ASC
        """,
    'task_name_get':
        """
        SELECT DISTINCT namespace FROM task
        """,
    'task_instance_get':
        """
        SELECT DISTINCT namespace, instance FROM task
        """,
    'task_by_id_get':
        """
        SELECT *
        FROM task
        WHERE taskid LIKE ?
        ORDER BY task.area ASC, task.title ASC
        """,
    'task_by_name_get':
        """
        SELECT *
        FROM task
        WHERE namespace LIKE ?
        ORDER BY task.area ASC, task.title ASC
        """,
    'task_by_instance_get':
        """
        SELECT *
        FROM task
        WHERE namespace LIKE ? AND instance LIKE ?
        ORDER BY task.area ASC, task.title ASC
        """,
    'task_by_active_get':
        """
        SELECT *
        FROM task
        WHERE namespace LIKE ? AND active LIKE ?
        ORDER BY task.area ASC, task.title ASC
        """,
    'task_active_get':
        """
        SELECT active
        FROM task
        WHERE taskid = ?
        """,
    'task_num_active_get':
        """
        SELECT count(*)
        FROM task
        WHERE active='1'
        """,

    'task_del':
        """
        DELETE FROM task WHERE
        area = ? AND title = ?;
        """,
    'trigger_del':
        """
        DELETE FROM trigger WHERE
        area = ? AND title = ?;
        """,

    'trigger_add':
        """
        INSERT OR REPLACE INTO trigger (
        uuid, area, title, timetype, timelimit, timeofday, dayofweek, interval, randdur )
        VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ? )
        """,
    'trigger_by_uuid_get':
        """
        SELECT *
        FROM trigger 
        INNER JOIN task ON task.area = trigger.area
        AND task.title = trigger.title
        WHERE trigger.uuid = ?
        """,
    'trigger_by_taskid_get':
        """
        SELECT *
        FROM trigger 
        INNER JOIN task ON task.area = trigger.area
        AND task.title = trigger.title
        WHERE task.taskid LIKE ?
        ORDER BY task.area ASC, task.title ASC, trigger.timetype DESC
        """,
    'trigger_by_type_get':
        """
        SELECT *
        FROM trigger 
        INNER JOIN task ON task.area = trigger.area
        AND task.title = trigger.title
        WHERE trigger.timetype LIKE ?
        ORDER BY task.priority DESC
        """,
    'trigger_by_uuid_del':
        """
        DELETE FROM trigger WHERE uuid=?
        """
}


class DBScheduler(DB):

    def __init__(self, _config):
        super().__init__(_config, _config['datamgmt'][DB_CONFIG_NAME], sqlcmds)

    def save_task(self, _area, _title, _namespace, _instance, _funccall,
                  _priority, _threadtype, _description):
        """
        Returns true if the record was saved.  If the record already exists,
        it will return false.
        """
        try:
            id_ = str(uuid.uuid1()).upper()
            self.add(DB_TASK_TABLE, (
                id_,
                _area,
                _title,
                _namespace,
                _instance,
                _funccall,
                _priority,
                _threadtype,
                _description
            ))
            return True
        except sqlite3.IntegrityError:
            return False

    def del_task(self, _area=None, _title=None):
        """
        Deletes the task and associated triggers
        """
        if not _area:
            _area = '%'
        if not _title:
            _title = '%'
        self.delete(DB_TRIGGER_TABLE, (_area, _title,))
        return self.delete(DB_TASK_TABLE, (_area, _title,))

    def get_tasks(self, _area=None, _title=None):
        if not _area:
            _area = '%'
        if not _title:
            _title = '%'
        return self.get_dict(DB_TASK_TABLE, (
            _area,
            _title,
        ))

    def get_tasks_by_name(self, _name=None, _instance=None):
        if not _name:
            _name = '%'
        if not _instance:
            return self.get_dict(DB_TASK_TABLE + '_by_name', (
                _name,
            ))
        else:
            return self.get_dict(DB_TASK_TABLE + '_by_instance', (
                _name, _instance,
            ))

    def get_tasks_by_active(self, _active='1', _name=None):
        if not _name:
            _name = '%'
        return self.get_dict(DB_TASK_TABLE + '_by_active', (
            _name, _active,
        ))

    def get_task(self, _id):
        task = self.get_dict(DB_TASK_TABLE + '_by_id', (
            _id,
        ))
        if len(task) == 1:
            return task[0]
        else:
            return None

    def get_task_names(self):
        return self.get_dict(DB_TASK_TABLE + '_name')

    def get_task_instances(self):
        return self.get_dict(DB_TASK_TABLE + '_instance')

    def start_task(self, _area, _title):
        self.update(DB_TASK_TABLE + '_active', (
            1,
            _area,
            _title,
        ))

    def finish_task(self, _area, _title, _duration):
        self.update(DB_TASK_TABLE + '_finish', (
            datetime.datetime.utcnow(),
            _duration,
            _area,
            _title,
        ))

    def reset_activity(self, _activity=False, _area=None, _title=None):
        if not _area:
            _area = '%'
        if not _title:
            _title = '%'
        self.update(DB_TASK_TABLE + '_active', (
            _activity,
            _area,
            _title,
        ))

    def get_active_status(self, _taskid):
        res = self.get_dict(DB_TASK_TABLE + '_active', (_taskid,))
        if res:
            return res[0]['active']
        else:
            return None

    def get_num_active(self):
        return self.get(DB_TASK_TABLE + '_num_active')[0][0]

    def save_trigger(self, _area, _title, _timetype, timeofday=None,
                     dayofweek=None, interval=-1, timelimit=-1, randdur=-1):
        """
        timetype: daily, weekly, interval, startup
        timelimit: maximum time it can run before terminating. -1 is not used
        timeofday: used with daily and weekly. defines the time of day it runs
        dayofweek: string for the day of the week. ex: Wednesday
        interval: used with timetype: interval in minutes. task will run every x minutes
        randdur: maximum in minutes. Interval only. Will add a randum amount 
        to the event start time up to the maximum minutes.  -1 is not used.
        """
        id_ = str(uuid.uuid1()).upper()
        self.add(DB_TRIGGER_TABLE, (
            id_,
            _area,
            _title,
            _timetype,
            timelimit,
            timeofday,
            dayofweek,
            interval,
            randdur,
        ))
        return id_

    def get_triggers_by_type(self, _timetype):
        """
        Returns the list of triggers based on timetype and ordered 
        by priority
        """
        if not _timetype:
            _timetype = '%'
        return self.get_dict(DB_TRIGGER_TABLE + '_by_type', (_timetype,))

    def get_trigger(self, _uuid):
        trigger = self.get_dict(DB_TRIGGER_TABLE + '_by_uuid', (_uuid,))
        if len(trigger) == 1:
            return trigger[0]
        else:
            return None

    def get_triggers(self, _taskid=None):
        """
        Returns all triggers ordered by area, name, timetype and
        also provides the task information on each trigger
        """
        if not _taskid:
            _taskid = '%'
        return self.get_dict(DB_TRIGGER_TABLE + '_by_taskid', (_taskid,))

    def del_trigger(self, _uuid):
        self.delete(DB_TRIGGER_TABLE + '_by_uuid', (_uuid,))

    @Backup(DB_CONFIG_NAME)
    def backup(self, backup_folder):
        self.export_sql(backup_folder)

    @Restore(DB_CONFIG_NAME)
    def restore(self, backup_folder):
        msg = self.import_sql(backup_folder)
        if msg is None:
            msg = 'Scheduler Database Restored'
        self.reset_activity()
        return msg