import sqlite3


def valid(username, password):
    db_connection = sqlite3.connect('sports-registration.sqlite')
    base_cursor = db_connection.cursor()
    password_cursor = base_cursor.execute(f'SELECT password FROM user WHERE username=="{username}";')
    db_password = password_cursor.fetchone()
    if db_password is None:
        db_connection.close()
        return False
    if db_password[0] == password:
        db_connection.close()
        return True


def get_list():
    sports_list = [{'type': '男子', 'list': []},
                   {'type': '女子', 'list': []},
                   {'type': '团体', 'list': []}]
    sports_types = {'男子': ['100', '200', '400', '800', '1500', '跳高', '跳远', '三级跳', '铅球'],
                    '女子': ['100', '200', '400', '800', '跳高', '跳远', '三级跳', '铅球'],
                    '团体': ['男子4*100', '女子4*100', '男子引体向上', '女子仰卧起坐', '长绳']}
    transistor = {'男子': 0, '女子': 1, '团体': 2}
    db_connection = sqlite3.connect('sports-registration.sqlite')
    base_cursor = db_connection.cursor()
    for sports_type in sports_types:
        for sports_name in sports_types[sports_type]:
            type_cursor = base_cursor.execute(f'SELECT DISTINCT username FROM list '
                                              f'WHERE program_type="{sports_type}" '
                                              f'AND program_name="{sports_name}";')
            current_list = type_cursor.fetchall()
            if current_list == []:
                sports_list[transistor[sports_type]]['list'].extend([[sports_name, '暂无']])
            else:
                head_list = [i for i in range(len(current_list))]
                head_list[0] = sports_name
                for i in range(len(head_list) - 1):
                    head_list[i + 1] = ''
                sports_list[transistor[sports_type]]['list'].extend([list(line) for line in
                                                                     zip(head_list,
                                                                         [name[0] for name in current_list])])
    return sports_list


def register(username, program_type, program_name):
    db_connection = sqlite3.connect('sports-registration.sqlite')
    base_cursor = db_connection.cursor()
    base_cursor.execute(f'INSERT INTO list (username, program_type, program_name) '
                        f'VALUES ("{username}", "{program_type}", "{program_name}");')
    db_connection.commit()
    db_connection.close()
    return True