import MySQLdb
import hashlib

from .member import Member as member
from .group import Group as group
from .event import Event as event
from .exceptions import MemberNotFoundException
from datetime import datetime
from dateutil.relativedelta import relativedelta


class Admidio:
    """Admidio Class"""

    def __init__(self, mysql_host, mysql_user, mysql_pass, mysql_db,
                 db_prefix):
        self.mysql_host = mysql_host
        self.mysql_user = mysql_user
        self.mysql_pass = mysql_pass
        self.mysql_db = mysql_db

        self.prefix = db_prefix

        self.members = dict()
        self.groups = dict()
        self.events = dict()

        self.event_confirmation_id = None

        self.connect_db()

        sql = f"SELECT usf_id,usf_name_intern FROM {self.prefix}_user_fields"
        self.cursor.execute(sql)
        self.userfields = dict(self.cursor.fetchall())

        # Get the id for event confirmation role.
        # So it can exclude them from Groups

        sql = f"""SELECT cat_id
                 FROM {self.prefix}_categories
                 WHERE cat_name_intern = 'EVENTS'"""
        self.cursor.execute(sql)
        self.event_confirmation_id = self.cursor.fetchone()[0]

        self.db.close()

        self.refresh()

    def __del__(self):
        pass

    def __eq__(self, other):
        if isinstance(other, Admidio):
            return self.hash() == other.hash()

    def hash(self):
        s = ''
        for _, x in self.members.items():
            s += x.hash()
        for _, x in self.groups.items():
            s += x.hash()
        for _, x in self.events.items():
            s += x.hash()
        return hashlib.md5(s.encode('utf-8')).hexdigest()

    def connect_db(self):
        self.db = MySQLdb.connect(
            host=self.mysql_host,
            user=self.mysql_user,
            passwd=self.mysql_pass,
            db=self.mysql_db)

        self.cursor = self.db.cursor()

    def refresh(self):
        """Refresh all users, groups and events that are stored"""
        self.connect_db()

        self.ready = False
        self.members.clear()
        self.groups.clear()
        self.events.clear()
        self.initMembers()
        self.initGroups()
        self.initEvents()
        self.ready = True

        self.db.close()

    def initMembers(self):
        """Initialises all members in the Admidio installation"""

        sql = f"""SELECT usr_id, usr_login_name, usr_password
                 FROM {self.prefix}_users WHERE usr_valid = 1"""
        self.cursor.execute(sql)

        for row in self.cursor.fetchall():
            self.members[row[0]] = member(
                self.db, row[0], row[1],
                row[2], self.userfields, self)

    def initGroups(self):
        """Initialises all groups in the Admidio installation"""

        sql = f"""SELECT rol_id, rol_name, rol_description
                 FROM {self.prefix}_roles
                 WHERE rol_valid = 1
                 AND rol_cat_id != {self.event_confirmation_id}"""
        self.cursor.execute(sql)

        for row in self.cursor.fetchall():
            self.groups[row[0]] = group(row[0], row[1], self)

    def initEvents(self):
        """Initialises all comming events and passed events <3 months"""

        datestring = datetime.now() + relativedelta(months=-6)
        datestring = datestring.strftime("%Y-%m-%d %H:%M:%S")

        sql = f"""SELECT dat_id, dat_cat_id, dat_begin, dat_end,
                 dat_headline, dat_description,
                 dat_location, dat_country, dat_rol_id
                 FROM {self.prefix}_dates
                 WHERE dat_begin >'{datestring}'
                 ORDER BY dat_begin"""
        self.cursor.execute(sql)

        for row in self.cursor.fetchall():
            self.events[row[0]] = event(
                row[0], row[8], row[4], row[2], row[3], row[5],
                row[6], row[7], row[1], self)

    def getMemberFromID(self, user_id):
        """
        Returns Member object if user_id exists
        otherwise throws MemberNotFoundException
        """
        if user_id in self.members:
            return self.members[user_id]
        else:
            raise MemberNotFoundException(
                f"Member with id {user_id} not found")