member-career

# -*- coding: utf-8 -*-

# Python 3

import os
import sys
import sqlite3
import bbot.discord


SCRIPT_PATH : str      = os.path.dirname(os.path.realpath(sys.argv[0]))
local_db_path :str     = "/home/pi/Scripts/blastardbot/bot.db"  ## Production
# local_db_path :str     = "/home/pi/Scripts/blastardbot/bot-dev.db"  ## Development


# Define queries

MEMBER_SELECT = '''
SELECT
    rosterID
    clan_id,
    name,
    psn_id,
    status,
    join_date,
    exit_date,
    return_date,
    rec_id
FROM
    member
WHERE
    status IN ('Member','Alumni')
ORDER BY
    name;
'''

MEMBER_CAREER_SELECT = '''
SELECT
    name,
    psn_id,
    start_date,
    event,
    end_date,
    status,
    rosterID
FROM
    member_career
WHERE
    rosterID = :rosterID;
'''

CAREER_INSERT = '''
INSERT INTO
    career (
        rosterID,
        start_date,
        end_date,
        event,
        status
    )
VALUES (
    :rosterID,
    :start_date,
    :end_date,
    :event,
    :status
)
'''

CAREER_UPDATE_EXIT = '''
UPDATE
    career
SET
    end_date = :end_date,
    status   = :status
WHERE
    rosterID = :rosterID
AND
    start_date = :start_date
'''

CAREER_UPDATE_START = '''
UPDATE
    career
SET
    start_date = :new_start_date
WHERE
    rosterID = :rosterID
AND
    start_date = :old_start_date
'''

CAREER_DELETE_RETURN = '''
DELETE FROM
    career
WHERE
    rosterID = :rosterID
AND
    start_date = :return_date
AND
    event = "Return"
'''

CAREER_UPDATE_RETURN = '''
UPDATE
    career
SET
    start_date = :return_date,
    status     = "Active",
    event      = "Return"
WHERE
    rosterID = :rosterID
AND
    start_date = :event_date
'''

CAREER_CREATE_RETURN = '''
INSERT INTO
    career (start_date, event, rosterID, status)
VALUES (
    :return_date,
    'Return',
    :rosterID,
    'Active'
)
'''

# define classes

class CareerSummary():

    rosterID        = None
    name            = None
    missing         = True
    prev_start      = None
    join_date       = None
    exit_date       = None
    return_date     = None
    status          = None
    last_event      = None
    event_count     = 0
    last_event_date = None
    career_data     = None
    db              = None

    def __init__(self, db, rosterID, name):

        self.rosterID    = rosterID
        self.name        = name
        self.db          = db

        self.career_load()


    def career_load(self):

        self.missing         = True
        self.prev_start      = None
        self.join_date       = None
        self.exit_date       = None
        self.return_date     = None
        self.status          = None
        self.last_event      = None
        self.event_count     = 0
        self.last_event_date = None

        self.career_data =  db_select(self.db, MEMBER_CAREER_SELECT, {'rosterID' : self.rosterID})

        if len(self.career_data) > 0:

            self.missing = False
            self.event_count = len(self.career_data)
            self.last_event = self.career_data[self.event_count-1]['event']
            self.last_event_date = self.career_data[self.event_count-1]['start_date']
            self.join_date = self.career_data[0]['start_date']
            self.exit_date = self.career_data[0]['end_date']
            self.status = self.career_data[self.event_count-1]['status']

            if self.event_count > 1:
                self.prev_start = self.career_data[self.event_count-2]['start_date']
            else:
                self.prev_start = self.career_data[0]['start_date']

        else:
            print("No career events found.")

        for career_event in self.career_data:

            if not self.exit_date:
                self.exit_date = career_event['end_date']
            else:
                if career_event['end_date'] and self.exit_date < career_event['end_date']:
                    self.exit_date = career_event['end_date']

            if career_event['start_date']>self.join_date and career_event['start_date']>noneToEmpty(self.return_date):
                self.return_date = career_event['start_date']

        print(self)

    def career_create(self, join_date):

        options = {
            'rosterID' : self.rosterID,
            'start_date' : join_date,
            'end_date' : None,
            'event' : 'Join',
            'status' : 'Active'
        }

        db_execute(self.db, CAREER_INSERT, options)
        print(f"Join event on {join_date} created for {self.name} ({self.rosterID}).")

        self.career_load()


    def join_update(self, join_date):

        options = {
            'rosterID' : self.rosterID,
            'old_start_date' : self.join_date,
            'new_start_date' : join_date
        }
        db_execute(self.db, CAREER_UPDATE_START, options)
        print(f"Join event update from {self.join_date} to {join_date} for {self.name} ({self.rosterID})")

        self.career_load()


    def exit_update(self, exit_date, return_date):

        print(f"Current exit date is: {self.exit_date}")
        print(f"    New exit will be: {exit_date}, return date: {return_date}")
        event_date = self.last_event_date
        event_status = "Alumni"

        # It's NOT possible to have a return date and NO exit date
        if return_date and not exit_date:
            print("Error! Return without exit!")
            return

        # Possible that exit date was reverted to None
        if not exit_date:
            event_status = "Active"
            print("Updating exit with ACTIVE status.")


        if return_date and return_date > exit_date:
            # If the return date is AFTER the exit date, the end date should be on last career event
            event_date = self.prev_start

        options = {
            'rosterID'   : self.rosterID,
            'start_date' : event_date,
            'end_date'   : exit_date,
            'status'     : event_status
        }

        db_execute(self.db, CAREER_UPDATE_EXIT, options)
        print(f"Updating exit date to {exit_date} and status {event_status} for event starting on {event_date} for {self.name} ({self.rosterID}).")

        self.career_load()


    def return_update(self, return_date, exit_date):

        if not return_date:

            # This will be a deletion of the last event in the career
            options = {
                'rosterID' : self.rosterID,
                'return_date' : self.return_date
            }

            print(f"Removing return event from {self.name} ({self.rosterID})")
            db_execute(self.db, CAREER_DELETE_RETURN, options)

        else:

            # We need to figure out if we are UPDATING a return date
            # or creating a new returning event

            # If the return_date is BEFORE the exit_date, it's an update

            if (return_date < exit_date):

                # We are updating
                options = {
                    'rosterID' : self.rosterID,
                    'return_date' : self.return_date,
                    'event_date'  : self.last_event_date
                }
                db_execute(self.db, CAREER_UPDATE_RETURN, options)
                print(f"Updating return date {self.return_date} for {self.name} ({self.rosterID}).")

            else:

                # If the return date is AFTER the exit date, it's a new return event

                # Just check status to be safe. Only an alumni can return

                if self.status == "Alumni":
                    # We are creating a new event
                    options = {
                        'rosterID' : self.rosterID,
                        'return_date' : return_date
                    }
                    db_execute(self.db, CAREER_CREATE_RETURN, options)
                    print(f"Creating return event on {return_date} for {self.name} ({self.rosterID}).")
                else:
                    print(f"Can't add an exit event as {self.name} (self.rosterID) isn't alumni.")

        self.career_load()

    def event_sync(self, join_date, exit_date, return_date):

        if self.missing:

            # Create career if missing

            print("* No career data found. Creating...")
            self.career_create(join_date)

        else:

            # Otherwise update join date if there's a difference

            if join_date != self.join_date:
                print("* Join date mismatch. Updating join date...")
                self.join_update(join_date)

        if exit_date != self.exit_date:
            print("* Exit data mismatch. Updating exit date...")
            self.exit_update(exit_date, return_date)

        if return_date != self.return_date:
            print("* Return date mismatch. Updating return date...")
            self.return_update(return_date, exit_date)


    def __str__(self):
        return f"Career for {self.name} ({self.rosterID}) status {self.status} - join: {self.join_date}, exit: {self.exit_date}, return: {self.return_date}, events: {self.event_count}"


# Define functions

def noneOrNull(v):

    if v is None:
        return True
    if len(v) is 0:
        return True
    return False

def noneToEmpty(v):
    if v is None:
        return ""
    else:
        return v

def emptyToNone(v):

    if v == "":
        return None
    return v


def db_open():

    print("Opening database: ",local_db_path)

    db = sqlite3.connect(local_db_path)
    db.row_factory = sqlite3.Row

    return db


def db_close(db):

    print("Closing data base.")
    db.commit()
    db.close()


def db_select(db, query, options = None):

    cur = db.cursor()

    if options is not None:
        rows = cur.execute(query, options).fetchall()
    else:
        rows = cur.execute(query).fetchall()

    data=[{k: item[k] for k in item.keys()} for item in rows]

    return data


def db_execute(db, query, options):

    cur = db.cursor()

    cur.execute(query, options)

    db.commit()


def members_fetch(db):

    print("Loading all current and previous members.")

    members=db_select(db, MEMBER_SELECT)

    return members

def member_check(db, members):

    member_count = 0

    for m in members:

        member_count = member_count + 1

        # Career rows are
        #   rosterID
        #   start_date
        #   event (Join, Return)
        #   end_date
        #   status (Active, Alumni)

        # Rules for career updates
        #   Info in members must have:
                # join_date must be the earliest date
                # When return_date is defined:
                #   If return date is > exit_date, member is active
                #   If return date is < exit_date, member is alumni
                # exit_date must come after join_date (if it exists)
        #   If the member row doesn't follow this,
        #   it won't be processed as it is considered invalid.

        member_name = m['name']
        rosterID    = m['clan_id']
        join_date   = emptyToNone(m['join_date'])
        exit_date   = emptyToNone(m['exit_date'])
        return_date = emptyToNone(m['return_date'])

        print(f"===== Roster data for #{member_count}, {member_name} ({rosterID}) join: {join_date} exit: {exit_date} return: {return_date} =====")

        summary = CareerSummary(db, rosterID, member_name)
        summary.event_sync(join_date, exit_date, return_date)


#
# MAIN PROCEDURE
#

db = db_open()

members = members_fetch(db)

member_check(db, members)

print("\n----\n")
db_close(db)