import datetime as dt
from enum import Enum, auto, StrEnum
from flask import Flask, g, send_from_directory, render_template, redirect, request, url_for, flash
from flask_login import LoginManager, UserMixin, login_user, login_required, logout_user, current_user
from functools import wraps
from libgravatar import Gravatar
import mysql.connector as mysql_con
import sqlite3
from pprint import pprint
import re
import sys
import tomllib
from typing import Union, List
from werkzeug.security import generate_password_hash, check_password_hash
from wtforms import Form, StringField, PasswordField, BooleanField, validators

config = None
with open("config.toml", "rb") as f:
    config = tomllib.load(f)

app = Flask(__name__)
app.config['TEMPLATES_AUTO_RELOAD'] = True
app.secret_key = config['secret_key'].encode()

login_manager = LoginManager()
login_manager.login_view = 'login'
login_manager.init_app(app)


DATABASE = 'dashboard.db'


def get_db():
    db = getattr(g, '_database', None)
    if db is None:
        db = g._database = sqlite3.connect(DATABASE, isolation_level=None)
    return db

def query_db(query, args=(), one=False):
    cur = get_db().execute(query, args)
    rv = cur.fetchall()
    cur.close()
    return (rv[0] if rv else None) if one else rv


def connect_web_db():
    db: mysql_con.MySQLConnection = getattr(g, '_web_database', None)
    if db == None:
        try:
            connection =  mysql_con.connect(
                host=config['web_db_host'],
                user=config['web_db_user'],
                password=config['web_db_pass'],
                database=config['web_db_name']
            )
            app.logger.info(connection)
            app.logger.info('test')
            db = g._web_database = connection
        except mysql_con.Error as e:
            app.logger.error(e)
    # elif not db.is_connected():
    #     db.reconnect()
    
    return db

def query_web_db(query, args=(), one=False):
    cur = connect_web_db().cursor()
    cur.execute(query, args)
    rv = cur.fetchall()
    
    return (rv[0] if rv else None) if one else rv


class LoginForm(Form):
    email = StringField('E-mailadres', validators=[validators.input_required()])
    password = PasswordField('Wachtwoord', validators=[validators.input_required()])
    remember = BooleanField('Ingelogd blijven')


class Permissions(StrEnum):
    VIEW_USERS = auto()
    MANAGE_USERS = auto()



# perm_groups = {
#     'admin': None
# }


class User(UserMixin):
    def __init__(self, id):
        # self.is_authenticated = False
        # self.is_active = True
        # self.is_anonymous = False

        if not query_db("SELECT id FROM users WHERE id=?", (id,), one=True):
            raise ValueError()

        self.id = id

    # def set_is_authenticated(self, val):
    #     self.is_authenticated = val

    # def set_is_active(self, val):
    #     self.is_active = val

    # def set_is_anonimous(self, val):
    #     self.is_anonimous = val

    def get_id(self) -> int:
        return self.id
    
    def get_permissions(self) -> List[Permissions]:
        pass

    def check_permission(self, required) -> bool:
        pass
    
    def get_name(self, first_only=False) -> str:
        name: str = query_db("SELECT name FROM users WHERE id=?", (self.id,), one=True)[0]
        
        if first_only:
            name = name.split(' ')[0]
            
        return name
    
    def get_email(self) -> str:
        email = query_db("SELECT email FROM users WHERE id=?", (self.id,), one=True)[0]
        return email if email else ""
    
    def get_groups(self, sort_by_name=False, as_name=False, as_id=False) -> Union['Group', List[str]]:
        if sort_by_name:
            rows = query_db("SELECT group_id, name FROM group_membership, groups WHERE user_id=? AND groups.id=group_id ORDER BY name ASC", (self.id,))
        else:
            rows = query_db("SELECT group_id FROM group_membership WHERE user_id=?", (self.id,))
        
        groups = [Group(row[0]) for row in rows] 
        
        if as_name:
            return list(map(str, groups))
        if as_id:
            return [g.get_id() for g in groups]

        return groups if not as_name else list(map(str, groups))

    def get_roles(self, highest=False, as_name=False, as_id=False) -> Union['Role', List[str], List[int]]:
        roles = [Role(row[0]) for row in query_db("SELECT roles.id, roles.priority, user_roles.role_id, user_roles.user_id, users.id FROM roles, user_roles, users WHERE users.id=? AND user_roles.user_id=users.id AND roles.id=user_roles.role_id ORDER BY roles.priority ASC;", (self.id,))]
        
        if as_name:
            roles = [r.get_name() for r in roles]
        if as_id:
            roles = [r.get_id() for r in roles]
            
        return roles[0] if highest else roles

    def get_gravatar(self):
        email: str = self.get_email()
        return Gravatar(email).get_image(default="http://tanuki.nl/wp-content/uploads/2023/06/tanuki.png")
    
    def add_group(group: 'Group'):
        pass

    def remove_group(group: 'Group'):
        pass
    
    def update(self, name=None, email=None, roles=None, groups=None):
        if name == None:
            name = self.get_name()
        if email == None:
            email = self.get_email()
        if roles == None:
            roles = self.get_roles(as_id=True)
        if groups == None:
            groups = self.get_groups(as_id=True)
        
        # app.logger.info(groups)
            
        query_db("UPDATE users SET name=?, email=? WHERE id=?", (name, email, self.get_id()))
        
        new_roles = [id for id in roles if id not in self.get_roles(as_id=True)]
        removed_roles = [id for id in self.get_roles(as_id=True) if id not in roles]
        
        
        for role in new_roles:
            query_db("INSERT INTO user_roles (user_id, role_id) VALUES (?, ?);", (self.get_id(), role))
        for role in removed_roles:
            query_db("DELETE FROM user_roles WHERE user_id=? AND role_id=?;", (self.get_id(), role))
        
        new_groups = [id for id in groups if id not in self.get_groups(as_id=True)]
        removed_groups = [id for id in self.get_groups(as_id=True) if id not in groups]
        
        # app.logger.info(list(new_groups).__str__())
        # app.logger.info(list(removed_groups).__str__())
        
        for group in new_groups:
            query_db("INSERT INTO group_membership (group_id, user_id) VALUES (?, ?);", (group, self.get_id()))
        for group in removed_groups:
            query_db("DELETE FROM group_membership WHERE group_id=? AND user_id=?;", (group, self.get_id()))

    
    def check_password(self, password) -> bool:
        passhash = query_db("SELECT passhash FROM users WHERE id=?", (self.id,), one=True)[0]
        return check_password_hash(passhash, password)
    
    def render_tasklist(self) -> str:
        html = ""

        for group in self.get_groups(sort_by_name=True):
            html += (group.render_tasks())

        return html
    

    def render_meetinglist(self) -> str:
        html = ""

        for group in self.get_groups(sort_by_name=True):
            html += (group.render_meetings())
    
        return html
    
    @staticmethod
    def get_all_users():
        users = []
        
        for user_info in query_db("SELECT id FROM users"):
            users.append(User(user_info[0]))

        return users
    

    def render_table_row(self) -> str:
        return render_template("user_table_row.html", user=self)

    
    @staticmethod
    def search_user(name: str, group_id: int = 0, exact_match:bool = True) -> 'User':
        if exact_match:
            match_cond = "name=?"
        else:
            match_cond = "name LIKE ?"
        
        if group_id:
            query = f"SELECT id, name, group_id, user_id FROM users, group_membership WHERE {match_cond} AND id=user_id AND group_id=?"
            return User(query_db(query, (f"%{name}%", group_id), one=True)[0])
        else:
            query = f"SELECT id, name FROM users WHERE {match_cond}"
            return User(query_db(query, (name,), one=True)[0])


@login_manager.user_loader
def load_user(user_id) -> Union[None, User]:
    data = query_db("SELECT * FROM users WHERE id=?", (user_id,), one=True)
    if data:
        user = g.user = User(data[0])
        return user
    return None

class TaskStatus(Enum):
    TODO = 0
    BUSY = 1
    DONE = 2
    NOTDONE = 3
    ALL = 4

    @staticmethod
    def target_id_to_status(target_id: str) -> Union['TaskStatus', str]:
        status_name = target_id.split('-')[-1]
        match status_name:
            case "todo":
                return TaskStatus.TODO
            case "busy":
                return TaskStatus.BUSY
            case "done":
                return TaskStatus.DONE
            case "notdone":
                return TaskStatus.NOTDONE
            case "archive":
                return "ARCHIVE"


class Task():
    def __init__(self, id):
        self.id = id

    def __str__(self):
        users = self.get_users()
        if users:
            names = ' ' + ', '.join([x.get_name(first_only=True) for x in self.get_users(sort_by_name=True)])
        else:
            names = ""
        return f"[{self.id}] ACTIE{names}: {self.get_content()}"

    def get_content(self) -> str:
        return query_db("SELECT content FROM tasks WHERE id=?;", (self.id,), one=True)[0]

    def get_status(self) -> TaskStatus:
        return TaskStatus(query_db("SELECT status FROM tasks WHERE id=?;", (self.id,), one=True)[0])
    
    def set_status(self, new_status: TaskStatus):
        query_db("UPDATE tasks SET status=? WHERE id=?;", (new_status.value, self.id))

    def archive(self):
        query_db("UPDATE tasks SET archived=TRUE WHERE id=?;", (self.id,))

    def unarchive(self):
        query_db("UPDATE tasks SET archived=FALSE WHERE id=?;", (self.id,))

    def get_users(self, sort_by_name=False) -> Union[List[User], List[None]]:
        rows = query_db("SELECT user_id FROM task_users WHERE task_id=?;", (self.id,))
        
        users = [User(row[0]) for row in rows]
        
        if sort_by_name:
            users = sorted(users, key=lambda u: u.get_name())
        
        return users

    def get_deadline(self):
        pass

    def get_meeting(self):
        pass

    def render_html(self, archive=False) -> str:
        return render_template("task_item.html" if not archive else "task_item_archive.html", task=self, TaskStatus=TaskStatus)
    
    @staticmethod
    def parse_tasks(text: str, group_id: int, meeting_id: int = 0) -> List['Task']:
        task_re = r"(?:(?P<action>[\+\*\-\!\@])?(?P<id>\[[0-9]+\])?\s)?ACTIE(?P<separate>S)?(?:(?:\s(?P<names>[A-Za-z]+(?:,\s[A-Za-z]+)*))?(?:\s-\s(?P<deadline>\d{2}-\d{2}-\d{4}(?:\s\d{2}:\d{2})?)?)?)?:\s(?P<content>.+)"
        tasks: List[Task] = []

        p = re.compile(task_re)
        substrings = p.findall(text)
        for line in text.splitlines():
            m = p.match(line)
            if m:
                if m.group('action'):
                    pass
                else:
                    if m.group('names'):
                        names = m.group('names').split(", ")
                        if m.group('separate'):
                            for name in names:
                                tasks.append(Task.insert_task(m.group('content'), group_id, name, m.group('deadline'), 0, meeting_id))
                        else:
                            tasks.append(Task.insert_task(m.group('content'), group_id, names, m.group('deadline'), 0, meeting_id))
                    else:
                        names = [u.get_name() for u in Group(group_id).get_members()]
                        tasks.append(Task.insert_task(m.group('content'), group_id, names, m.group('deadline'), 0, meeting_id))        

        return tasks

    @staticmethod
    def insert_task(content: str, group_id: int, names: List[str] = [], deadline: str = "", status: int = 0, meeting_id: int = 0) -> 'Task':
        query_db("INSERT INTO tasks (content, deadline, status, meeting_id, group_id) VALUES (?, ?, ?, ?, ?);", (content, deadline, status, meeting_id, group_id))
        task_id = query_db("SELECT last_insert_rowid()", one=True)[0]
        for name in names:
            user = User.search_user(name, group_id, False)
            query_db("INSERT INTO task_users VALUES (?, ?)", (task_id, user.id))
        return Task(task_id)


class Group():
    def __init__(self, id):
        self.id = id

    def __str__(self):
        return self.get_name()
    
    def get_id(self):
        return self.id

    def get_name(self) -> str:
        return query_db("SELECT name FROM groups WHERE id=?", (self.id,), one=True)[0]

    def get_members(self):
        return [User(row[0]) for row in query_db("SELECT user_id, group_id FROM group_membership WHERE group_id=?", (self.id,))]

    def get_meetings(self):
        pass

    def get_tasks(self, status=TaskStatus.ALL, archived: int=0) -> Union[List[Task], Task]:
        if status is TaskStatus.ALL:
            rows = query_db("SELECT id FROM tasks WHERE group_id=? AND archived=?", (self.id, archived))
        else:
            rows = query_db("SELECT id FROM tasks WHERE group_id=? AND status=? AND archived=?", (self.id, status.value, archived))
        return [Task(row[0]) for row in rows]

    def get_meeting_categories(self, sort_by_name=True):
        pass

    def render_tasks(self) -> str:
        return render_template("task_group.html", group=self, TaskStatus=TaskStatus, str=str)
    
    def render_meetings(self) -> str:
        return render_template("meeting_group.html", group=self)
    
    def search_user(name: str, exact_match:bool = True):
        pass

    @staticmethod
    def get_all_groups():
        return [Group(row[0]) for row in query_db("SELECT id FROM groups;")]


class Meeting():
    def __init__(self, id):
        self.id = id

    def get_attedees(self):
        pass


class Role():
    def __init__(self, id):
        self.id = id

    def get_id(self):
        return self.id

    def get_name(self):
        return query_db("SELECT name, id FROM roles WHERE id=?", (self.id,), one=True)[0]

    def get_priority(self):
        return query_db("SELECT priority, id FROM roles WHERE id=?", (self.id,), one=True)[0]

    @staticmethod
    def get_all_roles() -> List['Role']:
        return [Role(row[0]) for row in query_db("SELECT id FROM roles;")]


class EventCategory(Enum):
    INTERNAL = 0
    EXTERNAL = 1


class EventOrganisorType(Enum):
    GROUP = 0
    EXTERNAL = 1


class EventStatus(Enum):
    CONCEPT = 0
    DEFINITIVE = 1
    CANCELLED = 2


class Event():
    class Category(Enum):
        INTERNAL = 0
        EXTERNAL = 1
        MEETING = 2
        PROMOTION = 3
        
    class Status(Enum):
        CONCEPT = 0
        DEFINITIVE = 1
        CANCELLED = 2
        
    class OrganisorType(Enum):
        GROUP = 0
        EXTERNAL = 1
        
    years = {
        '2022-2023': (dt.datetime(2022, 9, 5), dt.datetime(2023, 9, 4)),
        '2023-2024': (dt.datetime(2023, 9, 4), dt.datetime(2024, 9, 2))
    }
    
    dt_fmt = "%Y-%m-%d %H-%m"
    
    std_year = "2023-2024"
    
    def __init__(self, id):
        self.id = id

    def get_name(self):
        return query_db("SELECT name, id FROM events WHERE id=?;", (self.id,), one=True)[0]

    def get_category(self) -> 'Event.Category':
        return query_db("SELECT category, id FROM events WHERE id=?;", (self.id,), one=True)[0]

    def get_status(self) -> 'Event.Status':
        return query_db("SELECT status, id FROM events WHERE id=?;", (self.id,), one=True)[0]

    def get_organisor_type(self):
        return query_db("SELECT organisor_type, id FROM events WHERE id=?;", (self.id,), one=True)[0]

    def get_organisor_id(self):
        return query_db("SELECT organisor_id, id FROM events WHERE id=?;", (self.id,), one=True)[0]

    def get_organisor_name(self):
        if self.get_organisor_type() == self.OrganisorType.EXTERNAL.value:
            return query_db("SELECT organisor_name, id FROM events WHERE id=?;", (self.id,), one=True)[0]
        else:
            pass

    def get_location(self):
        return query_db("SELECT location, id FROM events WHERE id=?;", (self.id,), one=True)[0]

    def get_start(self):
        return query_db("SELECT start, id FROM events WHERE id=?;", (self.id,), one=True)[0]

    def get_end(self):
        return query_db("SELECT end, id FROM events WHERE id=?;", (self.id,), one=True)[0]
    
    def render_table_row(self):
        return render_template("event-table-row.html", event=self, Event=Event)
    
    @staticmethod
    def get_events(one_year=False, year=std_year):
        if one_year:
            start = Event.years[year][0].strftime(Event.dt_fmt)
            end = Event.years[year][1].strftime(Event.dt_fmt)
            return [Event(row[0]) for row in query_db("SELECT id, start FROM events WHERE start BETWEEN ? AND ?;", (start, end))]
        else:
            return [Event(row[0]) for row in query_db("SELECT id FROM events;")]
        

class Member():
    cache = {}
    
    def __init__(self, id):
        if self.cache.get(id):
            return
        self.cache[id] = self
        
        # app.logger.info(f"constructing member {id}")
        
        self.id = id
        
    @classmethod
    def __get_cache(self, id):
        return self.cache.get(id)
    
    def __new__(self, id, *args, **kwargs):
        existing = self.__get_cache(id)
        if existing:
            return existing
        member = super(Member, self).__new__(self)
        return member
        
    def get_id(self):
        return self.id
    
    def get_name(self):
        return query_web_db("SELECT display_name, ID FROM wp_users WHERE ID=%s;", (self.id,), one=True)[0]
    
    def get_email(self):
        return query_web_db("SELECT user_email, ID FROM wp_users WHERE ID=%s;", (self.id,), one=True)[0]
    
    def get_gravatar(self):
        email: str = self.get_email()
        return Gravatar(email).get_image(default="http://tanuki.nl/wp-content/uploads/2023/06/tanuki.png")
        
    @staticmethod
    def get_all_members():
        app.logger.info('fetch id\'s')
        rows = query_web_db("SELECT ID FROM wp_users;")
        
        app.logger.info('construct members')
        return [Member(row[0]) for row in rows]
    
    @staticmethod
    def get_bulk_data(meta_fields: List[str]=[]):
        return query_web_db("SELECT display_name, user_email, ID FROM wp_users;")
        
    


@app.teardown_appcontext
def close_connection(exception):
    db = getattr(g, '_database', None)
    if db is not None:
        db.close()

# TODO: weghalen in prod
@app.route('/assets/<path:path>')
def assets(path):
    return send_from_directory('assets', path)
# TODO: weghalen in prod
@app.route('/dist/assets/<path:path>')
def assets_dist(path):
    return send_from_directory('assets', path)
# TODO: weghalen in prod
@app.route('/dist/<path:path>')
def dist(path):
    return send_from_directory('dist', path)


def permission_required(func):
    @wraps(func)
    def decorated_view(*args, **kwargs):
        return func(*args, **kwargs)
    
    return decorated_view


@app.route("/")
@login_required
def home():
    return render_template("main.html", title="Home")

# @app.route("/users")
# @login_required
# def users():
#     return query_db("select * from group_membership;")

@app.route("/tasks")
@login_required
def tasks():
    return render_template("tasks.html", title="Actiepunten")

@app.route("/tasks-parse", methods=["POST"])
@login_required
def tasks_parse():
    text = request.form['text']
    group_id = request.form['group_id']

    Task.parse_tasks(text, group_id)

    return "success"
        

@app.route("/task-move", methods=["POST"])
@login_required
def task_move():
    task = Task(request.form['task_id'])
    target_id = request.form['target_id']
    source_id = request.form['source_id']

    old_status = TaskStatus.target_id_to_status(source_id)
    new_status = TaskStatus.target_id_to_status(target_id)

    if old_status == "ARCHIVE":
        task.unarchive()

    if new_status == "ARCHIVE":
        task.archive()
    else:
        task.set_status(new_status)

    return "success"

@app.route("/tasks-archive", methods=["POST"])
@login_required
def tasks_archive():
    text = request.form['text']
    group_id = request.form['group_id']
    return "success"

@app.route("/meeting/<int:id>")
@login_required
def meeting():
    return render_template("meeting.html", title="Vergadering")

@app.route("/meetings", defaults={'group_id': None, 'category_id': None})
@app.route("/meetings/<int:group_id>", defaults={'category_id': None})
@app.route("/meetings/<int:group_id>/<int:category_id>")
@login_required
def meetings(group_id, category_id):
    return render_template("meetings.html", title="Vergaderingen")

@app.route("/annual-planning", defaults={'year': Event.std_year})
@app.route("/annual-planning/<string:year>")
@login_required
def annual_planning(year):
    if not year in list(Event.years.keys()):
        year = Event.std_year
    return render_template("annual-planning.html", title="Jaarplanning", Event=Event, year=year)

@app.route("/event-add")
@login_required
def event_add():
    pass


@app.route("/login")
def login():        
    return render_template("login.html", title="Login")

@app.route('/login', methods=['POST'])
def login_post():
    # return redirect(url_for("home"))
    # Here we use a class of some kind to represent and validate our
    # client-side form data. For example, WTForms is a library that will
    # handle this for us, and we use a custom LoginForm to validate.
    form = LoginForm(request.form)
    # return form.email.data
    if form.validate():
        user_info = query_db("SELECT id FROM users WHERE email=?;", (form.email.data,), one=True)
        if not user_info:
            flash('Ongelding e-mailadres.')
            return render_template('login.html')

        user = User(user_info[0])
        if not user.check_password(form.password.data):
            return render_template('login.html')

        # Login and validate the user.
        # user should be an instance of your `User` class
        login_user(user, remember=form.remember.data)

        flash('Logged in successfully.')

        # next = request.args.get('next')
        # # url_has_allowed_host_and_scheme should check if the url is safe
        # # for redirects, meaning it matches the request host.
        # # See Django's url_has_allowed_host_and_scheme for an example.
        # if not url_has_allowed_host_and_scheme(next, request.host):
        #     return flask.abort(400)

        return redirect(url_for('home'))
    flash('Ongeldige invoer')
    return render_template('login.html')

@app.route("/logout")
def logout():
    logout_user()       
    return redirect(url_for("login"))


@app.route("/user")
@login_required
def user():
    if current_user.get_id() != 1: #TODO
        return redirect(url_for('home'))
    return render_template("user.html", title="Gebruiker", user=current_user, Group=Group, Role=Role)
    

@app.route("/user/<int:user_id>")
@login_required
def user_specific(user_id):
    if current_user.get_id() != 1: #TODO
        return redirect(url_for('home'))
    try:
        u = User(user_id) if user_id else current_user
        return render_template("user.html", title="Gebruiker", user=u, Group=Group, Role=Role)
    except ValueError:
        return redirect(url_for('home'))


@app.route("/user-update", methods=['POST'])
def user_update():
    if current_user.get_id() != 1: #TODO
        return redirect(url_for('home'))
    # return request.form.getlist('input-groups')
    data = request.form
    user = User(data['id'])
    
    name = data.get('input-name') if data.get('input-name') else ""
    email = data.get('input-email') if data.get('input-email') else ""
    
    roles = list(map(int, data.getlist('input-roles')))
    groups = list(map(int, data.getlist('input-groups')))
    
    user.update(name=name, email=email, roles=roles, groups=groups)

    return "success"


@app.route("/user-set-password", methods=['POST'])
def user_set_password():
    pass


@app.route("/users")
@login_required
def users():
    if current_user.get_id() != 1: #TODO
        return redirect(url_for('home'))
    return render_template("users.html", title="Gebruikers", User=User, Group=Group, Role=Role)


@app.route("/members")
@login_required
def members():
    if current_user.get_id() != 1: #TODO
        return redirect(url_for('home'))
    return render_template("members.html", title="Leden", Member=Member, app=app)

@app.route("/member/<int:id>")
@login_required
def member_page(id):
    if current_user.get_id() != 1: #TODO
        return redirect(url_for('home'))
    try:
        m = Member(id)
        return render_template("member.html", title="Lid", member=m)
    except ValueError:
        return redirect(url_for('home'))

@app.route("/api/members")
@login_required
def api_members():
    if current_user.get_id() != 1: #TODO
        return redirect(url_for('home'))
    data = []
    
    app.logger.info("get members")
    members = Member.get_all_members()
    app.logger.info("loop")
    for member in Member.get_bulk_data():
        data.append({
            'naam': member[0],
            'email': member[1],
            'view': f'<a href="/member/{ member[2] }"><button type="button" class="btn rounded-pill btn-outline-primary"><i class="bi bi-eye"></i></button></a>'
        })
    app.logger.info("loop finished")
    
    return {'data': data}