import flask
from flask import *
import hashlib
import random
import pymysql
from gevent import pywsgi

app = flask.Flask(__name__)

print("gms_server.py is running")

# 定义一个函数，用于添加一个新的machineID到表中
def add_machine(machineID, key):
  # 检查表中是否已经存在该machineID
  conn, cursor = get_conn()
  sql = "SELECT * FROM gms_id_data WHERE machineID = %s"
  cursor.execute(sql, machineID)
  result = cursor.fetchone()
  if result:
    # 如果存在，返回错误信息
    return "--->该machineID:{}已经存在，请勿重复添加！".format(machineID)
  else:
    # 如果不存在，继续下一步
    # 计算到期时间，为当前时间加上7天，格式化为日期类型
    sql = "SELECT DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 7 DAY)"
    cursor.execute(sql)
    expire_time = cursor.fetchone()[0]
    # 插入一条新的记录到表中，包括machineID，register_time和expire_time，order_no为空
    sql = "INSERT INTO gms_id_data (machineID, license_key, expire_time) VALUES (%s, %s, %s)"
    cursor.execute(sql, (machineID, key, expire_time))
    # 关闭数据库
    close_conn(conn, cursor)
    # 返回成功信息
    return "--->成功添加machineID为{}的记录，到期时间为{}。".format(machineID, expire_time)

# 定义一个函数，用于插入一个machineID的订单号和到期时间
def insert_order(machineID, order_no):
  # 检查表中是否存在该machineID
  conn, cursor = get_conn()
  sql = "SELECT * FROM gms_id_data WHERE machineID = %s"
  cursor.execute(sql, machineID)
  result = cursor.fetchone()
  if not result:
    # 如果不存在，返回错误信息
    return "--->该machineID:{}不存在，请先添加！".format(machineID)
  else:
    # 如果存在，继续下一步
    sql = "SELECT * FROM gms_id_data WHERE order_no = %s"
    cursor.execute(sql, order_no)
    result = cursor.fetchone()
    if result:
        # 如果不存在，返回错误信息
        return "--->该order_no:{}已经存在，请勿重复添加！".format(order_no)
    # 查询该machineID的最新的register_time和expire_time，分别赋值给变量rt和et
    sql = "SELECT register_time, expire_time FROM gms_id_data WHERE machineID = %s ORDER BY register_time DESC LIMIT 1"
    cursor.execute(sql, machineID)
    rt, et = cursor.fetchone()
    # 计算当前时间，赋值给变量ct
    sql = "SELECT CURRENT_TIMESTAMP"
    cursor.execute(sql)
    ct = cursor.fetchone()[0]
    # 判断ct和et的大小
    if ct > et:
      # 如果ct大于et，说明已经过期，那么新的到期时间为ct加上1年
      sql = "SELECT DATE_ADD(%s, INTERVAL 1 YEAR)"
      cursor.execute(sql, ct)
    else:
      # 如果ct小于et，说明还没过期，那么新的到期时间为et加上1年
      sql = "SELECT DATE_ADD(%s, INTERVAL 1 YEAR)"
      cursor.execute(sql, et)
    new_expire_time = cursor.fetchone()[0]
    # 插入一条新的记录到表中，包括machineID，order_no，register_time和expire_time
    sql = "INSERT INTO gms_id_data (machineID, license_key, order_no, register_time, expire_time) VALUES (%s, %s, %s, %s, %s)"
    cursor.execute(sql, (machineID, "", order_no, ct, new_expire_time))
    # 关闭数据库
    close_conn(conn, cursor)
    # 返回成功信息
    return "--->成功插入machineID为{}的记录，订单号为{}，到期时间为{}。".format(machineID, order_no, new_expire_time)

@app.route("/generator", methods = ["POST"])  # 激活码生成
def gen():
    machineID = request.args.get('machineID')
    #conn = sqlite3.connect(database = DATABASE_FILE)  # 连接数据库
    conn, cursor = get_conn()
    sql = "SELECT `machineID` FROM gms_id_data WHERE `machineID`='%s'" % machineID  # 查找机器是否已经生成激活码
    cursor.execute(sql)
    num = 0
    for data in cursor.fetchall():
        #print(data)
        num += 1
    if (num != 0):
        close_conn(conn, cursor)
        return jsonify({"result": "failed"})  # 已经生成返回[失败]
    else:
        # 未生成进行生成
        rint = random.randint(100000000000000, 999999999999999)  # 选取随机数
        md5 = hashlib.md5(str(rint).encode()).hexdigest()[:16].upper()  # 进行MD5加密，16是截取的长度，可根据需要自行更改
        msg = add_machine(machineID, md5)
        print(msg)
        return jsonify({"result": "success"})  # 返回[成功]

@app.route("/verify", methods = ["POST"])  # 激活码验证
def verify():
    machineID = request.args.get('machineID')
    key = request.args.get('license_key')
    conn, cursor = get_conn()
    sql = "SELECT `machineID`,`license_key` FROM gms_id_data WHERE `machineID` = '%s' and `license_key` = '%s'" % (machineID, key) # 查询激活码是否正确
    cursor.execute(sql)
    num = 0
    for data in cursor.fetchall():
        num += 1
    close_conn(conn, cursor)
    if (num != 0):
        return jsonify({"result": "success"})
    else:
        return jsonify({"result": "failed"})

# 根据机器ID查询订单
@app.route("/getorder", methods=["POST"])
def get_order_no():
    machineID = request.args.get('machineID')
    conn, cursor = get_conn()
    sql = "SELECT `order_no` FROM gms_id_data WHERE `machineID`='%s' ORDER BY expire_time DESC LIMIT 1" % machineID
    cursor.execute(sql)
    if cursor.rowcount == 0:
        close_conn(conn, cursor)
        return jsonify({"result": "failed", "message": "No matching record found"})
    data = cursor.fetchall()[0][0]
    close_conn(conn, cursor)
    if (data is not None):
        return jsonify({"result": "success"})
    else:
        return jsonify({"result": "failed"})

# select * from gms_id_data where `order_no` = '202309070000GMS09';
# 查询指定订单号
@app.route("/order", methods = ["POST"])  # 激活码验证
def verify_order():
    order_no = request.args.get('order_no')
    if order_no.find("_") >= 0:
        order_no_parts = order_no.split("_")
        order_no = order_no_parts[1]
    if len(order_no) < 10:  # 假设订单号至少需要 5 位长度，可根据实际情况调整
        return jsonify({"result": "failed"})
    conn, cursor = get_conn()
    # 模糊匹配 order_no
    sql = "SELECT * FROM gms_id_data WHERE `order_no` LIKE '%%%s%%'" % order_no
    cursor.execute(sql)
    num = 0
    for data in cursor.fetchall():
        num += 1
    close_conn(conn, cursor)
    if (num != 0):
        return jsonify({"result": "success"})
    else:
        return jsonify({"result": "failed"})

# UPDATE gms_id_data SET `order_no` = '202311030000GMS05'  WHERE `machineID` = '31015A664399933E';
# 新增数据库订单号
@app.route("/update", methods = ["POST"])
def new_order():
    machineID = request.args.get('machineID')
    order_no  = request.args.get('order_no')
    if order_no.find("_") >= 0:
        order_no = order_no.split("_")[1]
    msg = insert_order(machineID, order_no)
    print(msg)
    return jsonify({"result": "success"})

# 根据机器ID查询到期状态
@app.route("/time", methods = ["POST"])  # 激活码验证
def verify_time():
    conn, cursor = get_conn()
    machineID = request.args.get('machineID')
    # 查询该machineID的最新的expire_time，赋值给变量et
    sql = "SELECT expire_time FROM gms_id_data WHERE machineID = %s ORDER BY expire_time DESC LIMIT 1"
    cursor.execute(sql, machineID)
    et = cursor.fetchone()
    if et is None:
        return jsonify({"result": "failed", "future_time": ""})
    et = et[0]
    # 计算当前时间，赋值给变量ct
    sql = "SELECT CURRENT_TIMESTAMP"
    cursor.execute(sql)
    ct = cursor.fetchone()[0]
    close_conn(conn, cursor)
    if ct > et:
        return jsonify({"result": "failed", "future_time": et})
    else:
        return jsonify({"result": "success", "future_time": et})

def get_conn():
    """
    :return: 连接，游标
    """
    # 创建连接
    conn = pymysql.connect(host="localhost",
                    port=3306,
                    user="root",
                    passwd="7758258753",
                    database="db3",
                    autocommit=True,
                    charset="utf8mb4")
    # 创建游标
    cursor = conn.cursor()  # 执行完毕返回的结果集默认以元组显示
    return conn, cursor

def close_conn(conn, cursor):
    if cursor:
        cursor.close()
    if conn:
        conn.close()

server = pywsgi.WSGIServer(('0.0.0.0', 5001), app)
server.serve_forever()