embybot.py

import asyncio
from pyrogram import Client, filters
import requests
import json
import string
import pandas as pd
from sqlalchemy import create_engine
import time
import uuid
import random
from datetime import datetime, timedelta
from config import *


app = Client("my_bot", api_id=api_id, api_hash=api_hash, bot_token=bot_token)  # create tg bot
engine = create_engine(
    f'mysql+pymysql://{db_user}:{db_password}@{db_host}:{db_port}/{db_name}',
    pool_size=20,
    pool_recycle=3600
)
pd_invite_code = None
pd_config = None
pd_user = None
pd_admin = None

def db_execute(raw=''):
    if raw == '':
        return

    with engine.connect() as connection:
        result = connection.execute(raw)
        return result


def pd_read_sql_query(raw=''):
    # https://docs.sqlalchemy.org/en/20/core/connections.html#basic-usage
    with engine.connect() as conn:
        return pd.read_sql_query(raw, conn)


def pd_to_sql(df_write, table, **kwargs):
    df_write.to_sql(table, engine, **kwargs)


def IsAdmin(tgid=0):  # TODO change it in database
    global pd_admin
    pd_admin = pd.read_sql_query('select * from admin;', engine)

    tgid_find = (pd_admin['tgid'] == tgid)
    tgid_a = (pd_admin[tgid_find]['tgid'])
    tgid_a = tgid_a.to_list()
    try:
        tgid = tgid_a[-1]
        return True
    except IndexError:
        return False


def LocalTime(time=''):
    n_LastLogin = time[0:19]
    UTC_FORMAT = "%Y-%m-%dT%H:%M:%S"
    utcTime_LastLogin = datetime.strptime(n_LastLogin, UTC_FORMAT)
    localtime_LastLogin = utcTime_LastLogin + timedelta(hours=8)
    return localtime_LastLogin  # change emby time to Asia/Shanghai time


def IsReply(message=''):
    try:
        tgid = message.reply_to_message.from_user.id
    except AttributeError:
        return False
    return tgid


async def CreateCode(tgid=0):
    if IsAdmin(tgid=tgid):  # If you are a admin, you can create a code
        code = f'yqm-register-{str(uuid.uuid4())}'
        df_write = pd.DataFrame({'code': code, 'tgid': tgid, 'time': int(time.time()), 'used': 'F'}, index=[0])
        pd_to_sql(df_write, 'invite_code', index=False, if_exists='append')
        return code
    else:
        return 'A'  # not an admin that cannot use this command


async def invite(tgid=0, message=''):
    global pd_user
    global pd_invite_code
    pd_invite_code = pd_read_sql_query('select * from invite_code;')
    pd_user = pd_read_sql_query('select * from user;')
    if canrig(tgid=tgid) == 'B' or hadname(tgid=tgid) == 'B':
        return 'D'  # have an account or have the chance to register
    pd_invite_code = pd_read_sql_query('select * from invite_code;')
    pd_user = pd_read_sql_query('select * from user;')
    message = message.split(' ')
    code = message[-1]  # get the code
    code_find = (pd_invite_code['code'] == code)
    code = (pd_invite_code[code_find]['code'])
    owner_tgid = (pd_invite_code[code_find]['tgid'])
    code = code.to_list()
    try:
        code = code[-1]  # find the code if it is in the database
    except IndexError:
        return 'A'
    used = (pd_invite_code[code_find]['used'])
    used = used.to_list()
    used = used[-1]
    if used == 'T':
        return 'B'  # the code has been used
    else:
        code_used = f"UPDATE `{db_name}`.`invite_code` SET `used`='T' WHERE  `code`='{code}';"
        db_execute(code_used)  # set the code has been used
        pd_invite_code = pd_read_sql_query('select * from invite_code;')
        pd_user = pd_read_sql_query('select * from user;')
        tgid_find = (pd_user['tgid'] == tgid)
        try:
            tgid = int(pd_user[tgid_find]['tgid'])  # find the tgid if the user is in the databse
        except TypeError:
            df_write = pd.DataFrame({'tgid': tgid, 'admin': 'F', 'canrig': 'T'}, index=[0])
            pd_to_sql(df_write, 'user', index=False, if_exists='append')  # add the user info
            pd_invite_code = pd_read_sql_query('select * from invite_code;')
            pd_user = pd_read_sql_query('select * from user;')
            return 'C',owner_tgid
        setcanrig = f"UPDATE `{db_name}`.`user` SET `canrig`='T' WHERE  `tgid`='{tgid}';"
        db_execute(setcanrig)  # update the status that can register
        pd_invite_code = pd_read_sql_query('select * from invite_code;')
        pd_user = pd_read_sql_query('select * from user;')
        return 'C', owner_tgid  # done


async def remove(message=''):
    global pd_invite_code
    pd_invite_code = pd_read_sql_query('select * from invite_code;')
    message = message.split(' ')
    code = message[-1]  # get the code
    code_find = (pd_invite_code['code'] == code)
    code = (pd_invite_code[code_find]['code'])
    code = code.to_list()
    try:
        code = code[-1]  # find the code if it is in the database
    except IndexError:
        return 'A'
    used = (pd_invite_code[code_find]['used'])
    used = used.to_list()
    used = used[-1]
    if used == 'T':
        return 'B'  # the code has been used
    else:
        code_used = f"UPDATE `{db_name}`.`invite_code` SET `used`='T' WHERE  `code`='{code}';"
        db_execute(code_used)  # set the code has been used
        return 'C'  # done


def canrig(tgid=0):
    global pd_user
    global pd_invite_code
    pd_invite_code = pd_read_sql_query('select * from invite_code;')
    pd_user = pd_read_sql_query('select * from user;')
    tgid_find = (pd_user['tgid'] == tgid)
    tgid = (pd_user[tgid_find]['tgid'])
    tgid = tgid.to_list()
    try:
        tgid = tgid[-1]
    except IndexError:
        return 'A'  # not in the database
    sqlcanrig = (pd_user[tgid_find]['canrig'])
    sqlcanrig = sqlcanrig.to_list()
    sqlcanrig = sqlcanrig[-1]
    sqlemby_name = (pd_user[tgid_find]['emby_name'])
    sqlemby_name = sqlemby_name.to_list()
    sqlemby_name = sqlemby_name[-1]
    if sqlcanrig == 'T':
        return 'B'  # can register
    else:
        return 'C'  # cannot register


def hadname(tgid=0):
    global pd_user
    global pd_invite_code
    pd_invite_code = pd_read_sql_query('select * from invite_code;')
    pd_user = pd_read_sql_query('select * from user;')
    tgid_find = (pd_user['tgid'] == tgid)
    tgid = (pd_user[tgid_find]['tgid'])
    tgid = tgid.to_list()
    try:
        tgid = tgid[-1]
    except IndexError:
        return 'A'  # not in the database
    sqlemby_name = (pd_user[tgid_find]['emby_name'])
    sqlemby_name = sqlemby_name.to_list()
    sqlemby_name = sqlemby_name[-1]
    if sqlemby_name != 'None':
        return 'B'  # have an account
    else:
        return 'C'  # does not have an account

# TODO put the time into the database
async def register_all_time(tgid=0, message=''):  # public register

    if IsAdmin(tgid=tgid):
        message = message.split(' ')
        message = message[-1]
        write_conofig(config='register_public',parms='True')
        write_conofig(config='register_public_time',parms=int(time.time())+(int(message)*60))
        write_conofig(config='register_method', parms='Time')
        return int(time.time())+(int(message)*60)
    else:
        return 'A'  # not an admin

#TODO put the user into the database
async def register_all_user(tgid=0, message=''):
    if IsAdmin(tgid=tgid):
        message = message.split(' ')
        message = message[-1]
        write_conofig(config='register_public', parms='True')
        write_conofig(config='register_public_user',parms=int(message))
        write_conofig(config='register_method', parms='User')
        return int(message)
    else:
        return 'A'  # not an admin


def userinfo(tgid=0):
    global pd_user
    global pd_invite_code
    pd_invite_code = pd_read_sql_query('select * from invite_code;')
    pd_user = pd_read_sql_query('select * from user;')
    tgid_find = (pd_user['tgid'] == tgid)
    tgid_a = (pd_user[tgid_find]['tgid'])
    tgid_a = tgid_a.to_list()
    try:
        tgid = tgid_a[-1]
    except IndexError:
        return 'NotInTheDatabase'
    emby_name = (pd_user[tgid_find]['emby_name'])
    emby_name = emby_name.to_list()
    emby_name = emby_name[-1]
    emby_id = (pd_user[tgid_find]['emby_id'])
    emby_id = emby_id.to_list()
    emby_id = emby_id[-1]
    canrig = (pd_user[tgid_find]['canrig'])
    canrig = canrig.to_list()
    canrig = canrig[-1]
    bantime = (pd_user[tgid_find]['bantime'])
    bantime = bantime.to_list()
    bantime = bantime[-1]
    if bantime == 0:
        bantime = 'None'
    else:
        expired = time.localtime(bantime)
        expired = time.strftime("%Y/%m/%d %H:%M:%S", expired)  # change the time format
        bantime = expired
    if emby_name != 'None':
        r = requests.get(f"{embyurl}/emby/users/{emby_id}?api_key={embyapi}").text
        try:
            r = json.loads(r)
            lastacttime = r['LastActivityDate']
            createdtime = r['DateCreated']
            lastacttime = LocalTime(time=lastacttime)
            createdtime = LocalTime(time=createdtime)
        except json.decoder.JSONDecodeError:
            return 'NotInTheDatabase'
        except KeyError:
            lastacttime = 'None'
            createdtime = 'None'
        return 'HaveAnEmby', emby_name, emby_id, lastacttime, createdtime, bantime
    else:
        return 'NotHaveAnEmby', canrig


def prichat(message=''):
    if str(message.chat.type) == 'ChatType.PRIVATE':
        return True
    else:
        return False


async def BanEmby(bot_ban, tgid=0, message='', replyid=0):
    is_admin = False
    if bot_ban: # 如果是bot自动ban的，不用检查权限
        is_admin = True
    else:
        is_admin = isAdmin(tgid=tgid)
    if is_admin:
        if hadname(tgid=replyid) == 'B':
            global pd_user
            global pd_invite_code
            pd_invite_code = pd_read_sql_query('select * from invite_code;')
            pd_user = pd_read_sql_query('select * from user;')
            tgid_find = (pd_user['tgid'] == replyid)
            tgid_a = (pd_user[tgid_find]['tgid'])
            tgid_a = tgid_a.to_list()
            try:
                tgid = tgid_a[-1]
            except IndexError:
                return 'NotInTheDatabase'
            emby_name = (pd_user[tgid_find]['emby_name'])
            emby_name = emby_name.to_list()
            emby_name = emby_name[-1]
            emby_id = (pd_user[tgid_find]['emby_id'])
            emby_id = emby_id.to_list()
            emby_id = emby_id[-1]
            params = (('api_key', embyapi),
                    )
            headers = {
                'accept': 'application/json',
                'Content-Type': 'application/json',
            }
            data = '{"IsAdministrator":false,"IsHidden":true,"IsHiddenRemotely":true,"IsDisabled":true,"EnableRemoteControlOfOtherUsers":false,"EnableSharedDeviceControl":false,"EnableRemoteAccess":true,"EnableLiveTvManagement":false,"EnableLiveTvAccess":true,"EnableMediaPlayback":true,"EnableAudioPlaybackTranscoding":false,"EnableVideoPlaybackTranscoding":false,"EnablePlaybackRemuxing":false,"EnableContentDeletion":false,"EnableContentDownloading":false,"EnableSubtitleDownloading":false,"EnableSubtitleManagement":false,"EnableSyncTranscoding":false,"EnableMediaConversion":false,"EnableAllDevices":true,"SimultaneousStreamLimit":3}'
            requests.post(embyurl + '/emby/Users/' + emby_id + '/Policy',
                        headers=headers,
                        params=params, data=data)  # update policy
            setbantime = f"UPDATE `{db_name}`.`user` SET `bantime`={int(time.time())} WHERE  `tgid`='{tgid}';"
            db_execute(setbantime)  # update the status that cannot register
            return 'A', emby_name  # Ban the user's emby account
        else:
            if canrig(tgid=replyid):
                setcanrig = f"UPDATE `{db_name}`.`user` SET `canrig`='F' WHERE  `tgid`='{replyid}';"
                db_execute(setcanrig)  # update the status that cannot register
                return 'C', 'CannotReg'  # set cannot register
            else:
                return 'D', 'DoNothing'  # do nothing
    else:
        return 'B', 'NotAnAdmin'  # Not an admin


async def UnbanEmby(tgid=0, message='', replyid=0):
    if IsAdmin(tgid=tgid):
        if hadname(tgid=replyid) == 'B':
            global pd_user
            global pd_invite_code
            pd_invite_code = pd_read_sql_query('select * from invite_code;')
            pd_user = pd_read_sql_query('select * from user;')
            tgid_find = (pd_user['tgid'] == replyid)
            tgid_a = (pd_user[tgid_find]['tgid'])
            tgid_a = tgid_a.to_list()
            try:
                tgid = tgid_a[-1]
            except IndexError:
                return 'NotInTheDatabase'
            emby_id = (pd_user[tgid_find]['emby_id'])
            emby_id = emby_id.to_list()
            emby_id = emby_id[-1]
            emby_name = (pd_user[tgid_find]['emby_name'])
            emby_name = emby_name.to_list()
            emby_name = emby_name[-1]
            params = (('api_key', embyapi),
                      )
            headers = {
                'accept': 'application/json',
                'Content-Type': 'application/json',
            }
            data = '{"IsAdministrator":false,"IsHidden":true,"IsHiddenRemotely":true,"IsDisabled":false,"EnableRemoteControlOfOtherUsers":false,"EnableSharedDeviceControl":false,"EnableRemoteAccess":true,"EnableLiveTvManagement":false,"EnableLiveTvAccess":true,"EnableMediaPlayback":true,"EnableAudioPlaybackTranscoding":false,"EnableVideoPlaybackTranscoding":false,"EnablePlaybackRemuxing":false,"EnableContentDeletion":false,"EnableContentDownloading":false,"EnableSubtitleDownloading":false,"EnableSubtitleManagement":false,"EnableSyncTranscoding":false,"EnableMediaConversion":false,"EnableAllDevices":true,"SimultaneousStreamLimit":3}'
            requests.post(embyurl + '/emby/Users/' + emby_id + '/Policy',
                          headers=headers,
                          params=params, data=data)  # update policy
            setbantime = f"UPDATE `{db_name}`.`user` SET `bantime`={0} WHERE  `tgid`='{tgid}';"
            db_execute(setbantime)  # update the status that cannot register
            return 'A', emby_name  # Unban the user's emby account
        else:
            return 'C', 'DoNothing'  # do nothing
    else:
        return 'B', 'NotAnAdmin'  # Not an admin


async def create(tgid=0, message=''):  # register with invite code
    global pd_user
    global pd_invite_code
    pd_invite_code = pd_read_sql_query('select * from invite_code;')
    pd_user = pd_read_sql_query('select * from user;')
    if hadname(tgid=tgid) == 'B':
        return 'A'  # already have an account
    if canrig(tgid=tgid) != 'B':
        return 'C'  # cannot register
    message = message.split(' ')
    name = message[-1]
    if name == '' or name == ' ':
        return 'B'  # do not input a name
    data = '{"Name":"'+name+'","HasPassword":true}'
    params = (('api_key', embyapi),
              )
    headers = {
        'accept': 'application/json',
        'Content-Type': 'application/json',
    }
    r = requests.post(url=embyurl+'/emby/Users/New', headers=headers, params=params, data=data).text
    try:
        r = json.loads(r)  # create a new user
    except json.decoder.JSONDecodeError:
        if r.find('already exists.'):
            return 'D'  # already exists
    data1 = '{"IsAdministrator":false,"IsHidden":true,"IsHiddenRemotely":true,"IsDisabled":false,"EnableRemoteControlOfOtherUsers":false,"EnableSharedDeviceControl":false,"EnableRemoteAccess":true,"EnableLiveTvManagement":false,"EnableLiveTvAccess":true,"EnableMediaPlayback":true,"EnableAudioPlaybackTranscoding":false,"EnableVideoPlaybackTranscoding":false,"EnablePlaybackRemuxing":false,"EnableContentDeletion":false,"EnableContentDownloading":false,"EnableSubtitleDownloading":false,"EnableSubtitleManagement":false,"EnableSyncTranscoding":false,"EnableMediaConversion":false,"EnableAllDevices":true,"SimultaneousStreamLimit":3}'
    requests.post(embyurl + '/emby/Users/' + r['Id'] + '/Policy', headers=headers,
                  params=params, data=data1)  # update policy
    NewPw = ''.join(random.sample(string.ascii_letters + string.digits, 8))
    data = '{"CurrentPw":"" , "NewPw":"'+NewPw+'","ResetPassword" : false}'
    requests.post(f"{embyurl}/emby/users/{r['Id']}/Password?api_key={embyapi}", headers=headers, data=data)
    pd_invite_code = pd_read_sql_query('select * from invite_code;')
    pd_user = pd_read_sql_query('select * from user;')
    tgid_find = (pd_user['tgid'] == tgid)
    tgid_a = (pd_user[tgid_find]['tgid'])
    tgid_a = tgid_a.to_list()
    tgid_find = (pd_user['tgid'] == tgid)
    try:
        tgid_a = int(pd_user[tgid_find][
                         'tgid'])  # find the tgid if the user is in the databse
    except TypeError:
        df_write = pd.DataFrame(
            {'tgid': tgid, 'admin': 'F', 'emby_name': str(r['Name']),
             'emby_id': str(r['Id']), 'canrig': 'F'}, index=[0])
        pd_to_sql(df_write, 'user', index=False, if_exists='append')  # add the user info
        return r['Name'], NewPw
    sqlemby_name = f"UPDATE `{db_name}`.`user` SET `emby_name`='{r['Name']}' WHERE  `tgid`='{tgid}';"
    sqlcanrig = f"UPDATE `{db_name}`.`user` SET `canrig`='F' WHERE  `tgid`={tgid};"
    sqlemby_id = f"UPDATE `{db_name}`.`user` SET `emby_id`='{r['Id']}' WHERE  `tgid`='{tgid}';"
    db_execute(sqlcanrig)
    db_execute(sqlemby_name)
    db_execute(sqlemby_id)
    pd_invite_code = pd_read_sql_query('select * from invite_code;')
    pd_user = pd_read_sql_query('select * from user;')
    return r['Name'], NewPw


async def create_time(tgid=0, message=''):
    global pd_user
    global pd_invite_code
    register_public_time = load_config(config='register_public_time')
    if int(time.time()) < register_public_time:
        pd_invite_code = pd_read_sql_query('select * from invite_code;')
        pd_user = pd_read_sql_query('select * from user;')
        if hadname(tgid=tgid) == 'B':
            return 'A'  # already have an account
        message = message.split(' ')
        name = message[-1]
        if name == '' or name == ' ':
            return 'B'  # do not input a name
        data = '{"Name":"' + name + '","HasPassword":true}'
        params = (('api_key', embyapi),
                  )
        headers = {
            'accept': 'application/json',
            'Content-Type': 'application/json',
        }
        r = requests.post(url=embyurl + '/emby/Users/New', headers=headers,
                          params=params, data=data).text
        try:
            r = json.loads(r)  # create a new user
        except json.decoder.JSONDecodeError:
            if r.find('already exists.'):
                return 'D'  # already exists
        data1 = '{"IsAdministrator":false,"IsHidden":true,"IsHiddenRemotely":true,"IsDisabled":false,"EnableRemoteControlOfOtherUsers":false,"EnableSharedDeviceControl":false,"EnableRemoteAccess":true,"EnableLiveTvManagement":false,"EnableLiveTvAccess":true,"EnableMediaPlayback":true,"EnableAudioPlaybackTranscoding":false,"EnableVideoPlaybackTranscoding":false,"EnablePlaybackRemuxing":false,"EnableContentDeletion":false,"EnableContentDownloading":false,"EnableSubtitleDownloading":false,"EnableSubtitleManagement":false,"EnableSyncTranscoding":false,"EnableMediaConversion":false,"EnableAllDevices":true,"SimultaneousStreamLimit":3}'
        requests.post(embyurl + '/emby/Users/' + r['Id'] + '/Policy',
                      headers=headers,
                      params=params, data=data1)  # update policy
        NewPw = ''.join(random.sample(string.ascii_letters + string.digits, 8))
        data = '{"CurrentPw":"" , "NewPw":"' + NewPw + '","ResetPassword" : false}'
        requests.post(f"{embyurl}/emby/users/{r['Id']}/Password?api_key={embyapi}",
                      headers=headers, data=data)
        pd_invite_code = pd_read_sql_query('select * from invite_code;')
        pd_user = pd_read_sql_query('select * from user;')
        tgid_find = (pd_user['tgid'] == tgid)
        tgid_a = (pd_user[tgid_find]['tgid'])
        tgid_a = tgid_a.to_list()
        tgid_find = (pd_user['tgid'] == tgid)
        try:
            tgid_a = int(pd_user[tgid_find]['tgid'])  # find the tgid if the user is in the databse
        except TypeError:
            df_write = pd.DataFrame({'tgid': tgid, 'admin': 'F','emby_name': str(r['Name']), 'emby_id': str(r['Id']), 'canrig': 'F'},index=[0])
            pd_to_sql(df_write, 'user', index=False, if_exists='append')  # add the user info
            return r['Name'], NewPw
        sqlemby_name = f"UPDATE `{db_name}`.`user` SET `emby_name`='{r['Name']}' WHERE  `tgid`='{tgid}';"
        sqlcanrig = f"UPDATE `{db_name}`.`user` SET `canrig`='F' WHERE  `tgid`={tgid};"
        sqlemby_id = f"UPDATE `{db_name}`.`user` SET `emby_id`='{r['Id']}' WHERE  `tgid`='{tgid}';"
        db_execute(sqlcanrig)
        db_execute(sqlemby_name)
        db_execute(sqlemby_id)
        pd_invite_code = pd_read_sql_query('select * from invite_code;')
        pd_user = pd_read_sql_query('select * from user;')
        return r['Name'], NewPw
    else:
        register_method = 'None'
        write_conofig(config='register_method',parms='None')
        write_conofig(config='register_public_time',parms=0)
        return 'C'


async def create_user(tgid=0, message=''):
    global pd_user
    global pd_invite_code
    register_public_user = load_config(config='register_public_user')
    if register_public_user > 0:
        pd_invite_code = pd_read_sql_query('select * from invite_code;')
        pd_user = pd_read_sql_query('select * from user;')
        if hadname(tgid=tgid) == 'B':
            return 'A'  # already have an account
        message = message.split(' ')
        name = message[-1]
        if name == '' or name == ' ':
            return 'B'  # do not input a name
        data = '{"Name":"' + name + '","HasPassword":true}'
        params = (('api_key', embyapi),
                  )
        headers = {
            'accept': 'application/json',
            'Content-Type': 'application/json',
        }
        r = requests.post(url=embyurl + '/emby/Users/New', headers=headers,
                          params=params, data=data).text
        try:
            r = json.loads(r)  # create a new user
        except json.decoder.JSONDecodeError:
            if r.find('already exists.'):
                return 'D'  # already exists
        data1 = '{"IsAdministrator":false,"IsHidden":true,"IsHiddenRemotely":true,"IsDisabled":false,"EnableRemoteControlOfOtherUsers":false,"EnableSharedDeviceControl":false,"EnableRemoteAccess":true,"EnableLiveTvManagement":false,"EnableLiveTvAccess":true,"EnableMediaPlayback":true,"EnableAudioPlaybackTranscoding":false,"EnableVideoPlaybackTranscoding":false,"EnablePlaybackRemuxing":false,"EnableContentDeletion":false,"EnableContentDownloading":false,"EnableSubtitleDownloading":false,"EnableSubtitleManagement":false,"EnableSyncTranscoding":false,"EnableMediaConversion":false,"EnableAllDevices":true,"SimultaneousStreamLimit":3}'
        requests.post(embyurl + '/emby/Users/' + r['Id'] + '/Policy',
                      headers=headers,
                      params=params, data=data1)  # update policy
        NewPw = ''.join(random.sample(string.ascii_letters + string.digits, 8))
        data = '{"CurrentPw":"" , "NewPw":"' + NewPw + '","ResetPassword" : false}'
        requests.post(f"{embyurl}/emby/users/{r['Id']}/Password?api_key={embyapi}",
                      headers=headers, data=data)
        pd_invite_code = pd_read_sql_query('select * from invite_code;')
        pd_user = pd_read_sql_query('select * from user;')
        tgid_find = (pd_user['tgid'] == tgid)
        tgid_a = (pd_user[tgid_find]['tgid'])
        tgid_a = tgid_a.to_list()
        tgid_find = (pd_user['tgid'] == tgid)
        try:
            tgid_a = int(pd_user[tgid_find]['tgid'])  # find the tgid if the user is in the databse
        except TypeError:
            df_write = pd.DataFrame({'tgid': tgid, 'admin': 'F', 'emby_name': str(r['Name']), 'emby_id': str(r['Id']), 'canrig': 'F'}, index=[0])
            pd_to_sql(df_write, 'user', index=False, if_exists='append')  # add the user info
            write_conofig(config='register_public_user',parms=register_public_user - 1)
            return r['Name'], NewPw
        sqlemby_name = f"UPDATE `{db_name}`.`user` SET `emby_name`='{r['Name']}' WHERE  `tgid`='{tgid}';"
        sqlcanrig = f"UPDATE `{db_name}`.`user` SET `canrig`='F' WHERE  `tgid`={tgid};"
        sqlemby_id = f"UPDATE `{db_name}`.`user` SET `emby_id`='{r['Id']}' WHERE  `tgid`='{tgid}';"
        db_execute(sqlcanrig)
        db_execute(sqlemby_name)
        db_execute(sqlemby_id)
        pd_invite_code = pd_read_sql_query('select * from invite_code;')
        pd_user = pd_read_sql_query('select * from user;')
        write_conofig(config='register_public_user',parms=register_public_user - 1)
        return r['Name'], NewPw
    else:
        write_conofig(config='register_method', parms='None')
        write_conofig(config='register_public_user',parms=0)
        return 'C'


def load_config(config=''):
    global pd_config
    pd_config = pd_read_sql_query('select * from config;')
    re = pd_config.at[0,config]
    return re


def write_conofig(config='',parms=''):
    code_used = f"UPDATE `{db_name}`.`config` SET `{config}`='{parms}' WHERE  `id`='1';"
    db_execute(code_used)
    return 'OK'


def ItemsCount():
    r = requests.get(f'{embyurl}/Items/Counts?api_key={embyapi}').text
    r= json.loads(r)
    MovieCount = r['MovieCount']
    SeriesCount = r['SeriesCount']
    EpisodeCount = r['EpisodeCount']
    return MovieCount,SeriesCount,EpisodeCount


async def reset(tgid=0):
    global pd_user
    global pd_invite_code
    pd_invite_code = pd.read_sql_query('select * from invite_code;', engine)
    pd_user = pd.read_sql_query('select * from user;', engine)

    tgid_find = (pd_user['tgid'] == tgid)
    tgid_a = (pd_user[tgid_find]['tgid'])
    tgid_a = tgid_a.to_list()
    try:
        tgid = tgid_a[-1]
    except IndexError:
        return 'NotInTheDatabase'
    emby_name = (pd_user[tgid_find]['emby_name'])
    emby_name = emby_name.to_list()
    emby_name = emby_name[-1]
    emby_id = (pd_user[tgid_find]['emby_id'])
    emby_id = emby_id.to_list()
    emby_id = emby_id[-1]
    canrig = (pd_user[tgid_find]['canrig'])
    canrig = canrig.to_list()
    canrig = canrig[-1]
    if emby_name != 'None':
        headers = {
            'accept': 'application/json',
            'Content-Type': 'application/json',
        }
        data = '{"CurrentPw":"" , "NewPw":"","ResetPassword" : true}'
        requests.post(f"{embyurl}/emby/users/{emby_id}/Password?api_key={embyapi}",
                      headers=headers, data=data)
        NewPw = ''.join(random.sample(string.ascii_letters + string.digits, 8))
        data = '{"CurrentPw":"" , "NewPw":"' + NewPw + '","ResetPassword" : false}'
        requests.post(f"{embyurl}/emby/users/{emby_id}/Password?api_key={embyapi}",
                      headers=headers, data=data)
        return 'HaveAnEmby',NewPw
    else:
        return 'NotHaveAnEmby',canrig


async def del_admin(tgid=0, replyid=0):
    if IsAdmin(tgid=tgid):
        global pd_admin
        pd_admin = pd.read_sql_query('select * from admin;', engine)

        tgid_find = (pd_admin['tgid'] == replyid)
        tgid_a = (pd_admin[tgid_find]['tgid'])
        tgid_a = tgid_a.to_list()
        try:
            tgid = tgid_a[-1]
            with engine.begin() as conn:
                conn.execute(f"delete from admin where tgid={replyid}")
            return 'Success'
        except IndexError:
            return 'NotAdmin'
    else:
        return 'A'


async def add_admin(tgid=0, replyid=0):
    if IsAdmin(tgid=tgid):
        global pd_admin
        pd_admin = pd.read_sql_query('select * from admin;', engine)

        tgid_find = (pd_admin['tgid'] == replyid)
        tgid_a = (pd_admin[tgid_find]['tgid'])
        tgid_a = tgid_a.to_list()
        try:
            tgid = tgid_a[-1]
            return 'IsAdmin'
        except IndexError:
            df_write = pd.DataFrame(
                {'tgid': replyid},
                index=[0])
            pd_to_sql(df_write, 'admin', index=False, if_exists='append')  # add the user info
            return 'Success'
    else:
        return 'A'


def admin_list(tgid=0):
    if IsAdmin(tgid=tgid):
        global pd_admin
        pd_admin = pd.read_sql_query('select * from admin;', engine)
        admin_list = pd_admin['tgid'].to_list()
        text = f"以下为所有管理员，共{len(admin_list)}个\n"
        for admin in admin_list:
            text += f'用户<a href="tg://user?id={admin}">{admin}</a>\n'
        return text
    else:
        return 'A'

@app.on_message(filters.chat(groupid) & filters.left_chat_member)
async def member_left_group_handler(client, message):
    if member_left_group_ban and message and message.left_chat_member and message.left_chat_member.id:
        replyid = message.left_chat_member.id
        re = await BanEmby(True, tgid=0, message=message, replyid=replyid)
        if re[0] == 'A':
            await message.reply(f'用户<a href="tg://user?id={replyid}">{replyid}</a>的Emby账号{re[1]}已被ban\n原因：用户已经退出群组')
            await app.send_message(chat_id=ban_channel_id, text=f'#Ban\n用户：<a href="tg://user?id={replyid}">{replyid}</a>\nEmby账号：{re[1]}\n原因：退群封禁')
        elif re[0] == 'C':
            await message.reply(f'用户<a href="tg://user?id={replyid}">{replyid}</a>没有Emby账号，但是已经取消了他的注册资格\n原因：用户已经退出群组')

@app.on_message(filters.text)
async def my_handler(client, message):
    tgid = message.from_user.id
    text = str(message.text)
    if str(text) == '/new_code' or text == f'/new_code{bot_name}':
            re = await CreateCode(tgid=tgid)
            if re == 'A':
                await message.reply('不是管理员请勿使用管理员命令')
            else:
                if IsReply(message=message) == False:
                    if message.chat.id == groupid:
                        await app.send_message(chat_id=groupid, text=f'生成成功，邀请码<code>{re}</code>')
                    else:
                        await app.send_message(chat_id=tgid, text=f'生成成功，邀请码<code>{re}</code>')
                else:
                    replyid = IsReply(message=message)
                    await message.reply('已为这个用户生成邀请码')
                    await app.send_message(chat_id=replyid, text=f'生成成功，邀请码<code>{re}</code>')
                    await app.send_message(chat_id=tgid, text=f'已为用户<a href="tg://user?id={replyid}">{replyid}</a>生成邀请码，邀请码<code>{re}</code>')
    elif str(text).find('/invite') == 0:
        if prichat(message=message):
            re = await invite(tgid=tgid, message=str(message.text))
            if re == 'A':
                await message.reply('没有找到这个邀请码')
            if re == 'B':
                await message.reply('邀请码已被使用')
            if re[0] == 'C':
                await app.send_message(chat_id=int(re[1]),
                                       text=f'邀请码<code>{str(message.text).split(" ")[-1]}</code>已被<a href="tg://user?id={tgid}">{tgid}</a>使用')
                await message.reply('已获得注册资格，邀请码已失效')
            if re == 'D':
                await message.reply('您已有账号或已经获得注册资格，请不要重复使用邀请码')
        else:
            await message.reply('请勿在群组使用该命令')
    elif str(text).find('/create') == 0:
        if str(text) == '/create':
            await message.reply('请输入用户名')

        elif str(text) == '/create_code':
            pass
        else:
            if prichat(message=message):
                register_method = load_config(config='register_method')
                if register_method == 'None':
                    re = await create(tgid=tgid, message=str(message.text))
                elif register_method == 'User':
                    re = await create_user(tgid=tgid,message=text)
                elif register_method == 'Time':
                    re = await create_time(tgid=tgid,message=text)
                if re == 'A':
                    await message.reply('您已经注册过emby账号，请勿重复注册')
                elif re == 'C':
                    await message.reply('您还未获得注册资格')
                elif re == 'B':
                    await message.reply('请输入用户名，用户名不要包含空格')
                elif re == 'D':
                    await message.reply('该用户名已被使用')
                else:
                    await message.reply(f'创建成功，账号<code>{re[0]}</code>，初始密码为<code>{re[1]}</code>，密码不进行保存，请尽快登陆修改密码')
            else:
                await message.reply('请勿在群组使用该命令')
    elif str(text).find('/register_all_time') == 0:
        re = await register_all_time(tgid=tgid, message=text)
        if re == 'A':
            await message.reply('您不是管理员，请勿随意使用管理命令')
        else:
            expired = time.localtime(re)
            expired = time.strftime("%Y/%m/%d %H:%M:%S", expired)
            await message.reply(f"注册已开放，将在{expired}关闭注册")
    elif str(text) == '/kankan' or text == f'/kankan{bot_name}':
        replyid = IsReply(message=message)
        if replyid != False:
            re = userinfo(tgid=replyid)
            if IsAdmin(tgid=tgid):
                if re == 'NotInTheDatabase':
                    await message.reply('用户未入库，无信息')
                elif re[0] == 'HaveAnEmby':
                    await message.reply('用户信息已私发，请查看')
                    await app.send_message(chat_id=tgid, text=f'用户<a href="tg://user?id={replyid}">{replyid}</a>的信息\nEmby Name: {re[1]}\n Emby ID: {re[2]}\n上次活动时间{re[3]}\n账号创建时间{re[4]}\n被ban时间{re[5]}')
                elif re[0] == 'NotHaveAnEmby':
                    await message.reply(f'此用户没有emby账号，可注册：{re[1]}')
            else:
                await message.reply('非管理员请勿随意查看他人信息')
        else:
            re = userinfo(tgid=tgid)
            if re == 'NotInTheDatabase':
                await message.reply('用户未入库，无信息')
            elif re[0] == 'HaveAnEmby':
                await message.reply('用户信息已私发，请查看')
                await app.send_message(chat_id=tgid,
                                       text=f'用户<a href="tg://user?id={tgid}">{tgid}</a>的信息\nEmby Name: {re[1]}\n Emby ID: {re[2]}\n上次活动时间{re[3]}\n账号创建时间{re[4]}\n被ban时间{re[5]}')
            elif re[0] == 'NotHaveAnEmby':
                await message.reply(f'此用户没有emby账号，可注册：{re[1]}')
    elif str(text) == '/help' or str(text) == '/start' or text == f'/start{bot_name}' or text == f'/help{bot_name}':
        await message.reply('用户命令：\n/invite + 邀请码 使用邀请码获取创建账号资格\n/create + 用户名 创建用户（用户名不可包含空格）\n/kankan 查看用户信息（仅可查看自己的信息）\n/count 查看服务器内片子数量\n/line 查看线路\n/求片 格式:/求片 TMDB链接 影片名\n/reset 重置密码\n/help 输出'
                            '本帮助\n\n管理命令：\n/new_code 创建新的邀请码\n/remove 邀请码管理\n/ad_list 管理员列表\n/register_all_time + 时间（分）开放注册，时长为指定时间\n/register_all_user + 人数 开放指定数量的注册名额\n/kankan 回复一位用户，查看他的信息\n/b_emby 禁用一位用户的Emby账号\n/unb_emby 解禁一位用户的Emby账户\n/add_admin 回复一位用户，添加管理员\n/del_admin 回复一位用户，删除管理员')
    elif str(text).find('/register_all_user') == 0:
        re = await register_all_user(tgid=tgid, message=text)
        if re == 'A':
            await message.reply('您不是管理员，请勿随意使用管理命令')
        else:
            await message.reply(f"注册已开放，本次共有{re}个名额")
    elif str(text).find('/b_emby') == 0:
        if IsReply(message=message) != False:
            replyid = IsReply(message=message)
            re = await BanEmby(False,tgid=tgid, message=message, replyid=replyid)
            if re[0] == 'A':
                await message.reply(f'用户<a href="tg://user?id={replyid}">{replyid}</a>的Emby账号{re[1]}已被ban')
                await app.send_message(chat_id=ban_channel_id, text=f'#Ban\n用户：<a href="tg://user?id={replyid}">{replyid}</a>\nEmby账号：{re[1]}\n原因：管理员封禁')
            elif re[0] == 'B':
                await message.reply('请勿随意使用管理员命令')
            elif re[0] == 'C':
                await message.reply(f'用户<a href="tg://user?id={replyid}">{replyid}</a>没有Emby账号，但是已经取消了他的注册资格')
            elif re[0] == 'D':
                await message.reply(f'用户<a href="tg://user?id={replyid}">{replyid}</a>没有Emby账号，也没有注册资格')
        else:
            await message.reply('请回复一条消息使用该功能')
    elif str(text).find('/unb_emby') == 0:
        if IsReply(message=message) != False:
            replyid = IsReply(message=message)
            re = await UnbanEmby(tgid=tgid, message=message, replyid=replyid)
            if re[0] == 'A':
                await message.reply(f'用户<a href="tg://user?id={replyid}">{replyid}</a>的Emby账号{re[1]}已解除封禁')
                await app.send_message(chat_id=ban_channel_id, text=f'#Unban\n用户：<a href="tg://user?id={replyid}">{replyid}</a>\nEmby账号：{re[1]}\n原因：管理员解封')
            elif re[0] == 'B':
                await message.reply('请勿随意使用管理员命令')
            elif re[0] == 'C':
                await message.reply(f'用户<a href="tg://user?id={replyid}">{replyid}</a>没有Emby账号，也没有注册资格')
        else:
            await message.reply('请回复一条消息使用该命令')
    elif text == '/line' or text == f'/line{bot_name}':
        if prichat(message=message):
            if hadname(tgid=tgid) == 'B':
                await message.reply(line)
            else:
                await message.reply('无Emby账号无法查看线路')
        else:
            await message.reply('请勿在群组中使用此命令')
    elif text.find('/求片') == 0:
        text = text.split(' ')
        url = text[1]
        name = text[2]
        if url.find('themoviedb.org') == -1 or url.find('tv') == -1 and url.find('movie') == -1:
            await message.reply('链接不符合规范')
        else:
            await message.reply('已发送请求')
            await app.send_message(chat_id=groupid,text=f'#求片\n影片名 #{name}\nTMDB链接：<code>{url}</code>\nTGID <a href="tg://user?id={tgid}">{tgid}</a>')
    elif text == '/count' or text == f'/count{bot_name}':
        re = ItemsCount()
        await message.reply(f'🎬电影数量：{re[0]}\n📽️剧集数量：{re[1]}\n🎞️总集数：{re[2]}')
    elif str(text).find('/remove') == 0:
        if prichat(message=message):
            re = await remove(message=str(message.text))
            if re == 'A':
                await message.reply('没有找到这个邀请码')
            if re == 'B':
                await message.reply('邀请码已被使用')
            if re == 'C':
                await message.reply('邀请码已作废')
        else:
            await message.reply('请勿在群组使用该命令')
    elif str(text).find('/reset') == 0:
        if prichat(message=message):
            re = await reset(tgid=tgid)
            if re == 'NotInTheDatabase':
                await message.reply('用户未入库，无信息')
            elif re[0] == 'HaveAnEmby':
                await app.send_message(chat_id=tgid,
                                       text=f'用户<a href="tg://user?id={tgid}">{tgid}</a>的Emby账号密码已修改为<code>{re[1]}</code>')
            elif re[0] == 'NotHaveAnEmby':
                await message.reply(f'此用户没有emby账号，可注册：{re[1]}')
        else:
            await message.reply('请勿在群组使用该命令')
    elif str(text).find('/add_admin') == 0:
        if IsReply(message=message) != False:
            replyid = IsReply(message=message)
            re = await add_admin(tgid=tgid, replyid=replyid)
            if re == 'A':
                await message.reply('不是管理员请勿使用管理员命令')
            elif re == 'IsAdmin':
                await message.reply(f'用户<a href="tg://user?id={replyid}">{replyid}</a>已经是管理员了')
            elif re == 'Success':
                await message.reply(f'成功添加用户<a href="tg://user?id={replyid}">{replyid}</a>为管理员')
        else:
            await message.reply('请回复一条消息使用该命令')
    elif str(text).find('/del_admin') == 0:
        if IsReply(message=message) != False:
            replyid = IsReply(message=message)
            if replyid == 123456:
                await message.reply(f'用户<a href="tg://user?id={tgid}">{tgid}</a>你想造反？')
            else:
                re = await del_admin(tgid=tgid, replyid=replyid)
                if re == 'A':
                    await message.reply('不是管理员请勿使用管理员命令')
                elif re == 'Success':
                    await message.reply(f'成功移除用户<a href="tg://user?id={replyid}">{replyid}</a>管理员权限')
                elif re == 'NotAdmin':
                    await message.reply(f'用户<a href="tg://user?id={replyid}">{replyid}</a>不是管理员,无法移除')
        else:
            await message.reply('请回复一条消息使用该命令')
    elif str(text).find('/ad_list') == 0:
        re = admin_list(tgid=tgid)
        if re == 'A':
            await message.reply('不是管理员请勿使用管理员命令')
        else:
            await message.reply(re)
app.run()