import pymysql
import time
import paho.mqtt.publish as publish
import paho.mqtt.client as mqtt
import datetime
import random
import json
import paramiko
from flask_mqtt import Mqtt

#新增一筆資料在清洗資料表
def insert_data_clean():
    insert = conn.cursor()
    insert_status = "INSERT INTO `clean_container_status`( `Clean_Input_1`, `Clean_Input_2`, `Clean_Tank_1`, " \
                    "`Clean_Tank_2`, `Clean_Tank_3`, `Clean_Tank_4`, `Reclaimed_Tank_1`, `ColorSelect_Input_1`, " \
                    "`ColorSelect_Input_2`, `ColorSelect_Tank_1`, `ColorSelect_Tank_2`, `ColorSelect_Output_g1`, " \
                    "`ColorSelect_Output_b1`, `ColorSelect_Output_g2`, `ColorSelect_Output_b2`, `Peel_Tank_1`, " \
                    "`Peel_Tank_2`, `Peel_Output_1`, `Peel_Output_2`, `Peel_Output_b1`) SELECT `Clean_Input_1`, " \
                    "`Clean_Input_2`, `Clean_Tank_1`, `Clean_Tank_2`, `Clean_Tank_3`, `Clean_Tank_4`, " \
                    "`Reclaimed_Tank_1`, `ColorSelect_Input_1`, `ColorSelect_Input_2`, `ColorSelect_Tank_1`, " \
                    "`ColorSelect_Tank_2`, `ColorSelect_Output_g1`, `ColorSelect_Output_b1`, `ColorSelect_Output_g2`," \
                    " `ColorSelect_Output_b2`, `Peel_Tank_1`, `Peel_Tank_2`, `Peel_Output_1`, `Peel_Output_2`, " \
                    "`Peel_Output_b1` FROM clean_container_status ORDER BY `datetime` DESC LIMIT 1"
    insert.execute(insert_status)
    conn.commit()

#更新清洗入料儲豆槽狀態 CI_Waiting改成CI_InputtingBean
def update_Clean_Input_status(c):
    insert_data_clean()
    update_Clean_status = conn.cursor()
    Clean_name = 'Clean_Input_'
    re_clean = "UPDATE `clean_container_status` SET " + Clean_name + str(c) + " = 'CI_InputtingBean' ORDER BY `datetime` DESC LIMIT 1 "
    update_Clean_status.execute(re_clean)
    conn.commit()

#更新清洗桶槽狀態 C_Waiting改成C_InputtingBean
def update_Clean_Tank_status1(c_tank):
    insert_data_clean()
    update_status_clean_Tank = conn.cursor()
    Clean_Tank_name = 'Clean_Tank_'
    re_clean_Tank = "UPDATE `clean_container_status` SET "+Clean_Tank_name+str(c_tank)+"= 'C_InputtingBean' " \
         "ORDER BY `datetime` DESC LIMIT 1 "
    update_status_clean_Tank.execute(re_clean_Tank)
    conn.commit()

#更新清洗桶槽狀態 C_InputtingBean改成C_Stand_by
def update_Clean_Tank_status(c_tank):
    insert_data_clean()
    update_status_clean_Tank = conn.cursor()
    clean_Tank_name = 'Clean_Tank_'
    re_clean_Tank = "UPDATE `clean_container_status` SET "+clean_Tank_name+str(c_tank)+"= 'C_Stand_by' " \
         "ORDER BY `datetime` DESC LIMIT 1 "
    update_status_clean_Tank.execute(re_clean_Tank)
    conn.commit()

#更新色選機狀態S_Waiting改成S_InputtingBean
def update_ColorSelect_Tank_status(s_Tank):
    insert_data_clean()
    update_status_colorSelect = conn.cursor()
    ColorSelect_name = 'ColorSelect_Tank_'
    re_colorSelect = "UPDATE `clean_container_status` SET "+ColorSelect_name+str(s_Tank)+"= 'S_InputtingBean' " \
         "ORDER BY `datetime` DESC LIMIT 1 "
    update_status_colorSelect.execute(re_colorSelect)
    conn.commit()

#更新色選機狀態 S_InputtingBean改成S_Stand_by
def update_ColorSelect_Tank_status_Stand_by(s_Tank):
    insert_data_clean()
    update_status_colorSelect = conn.cursor()
    ColorSelect_name = 'ColorSelect_Tank_'
    re_colorSelect = "UPDATE `clean_container_status` SET "+ColorSelect_name+str(s_Tank)+"= 'S_Stand_by' " \
         "ORDER BY `datetime` DESC LIMIT 1 "
    update_status_colorSelect.execute(re_colorSelect)
    conn.commit()

#更新色選機出料儲豆槽狀態 SO_Waiting改成SO_InputtingBean
def update_ColorSelect_Output_status(so_Output):
    insert_data_clean()
    update_status_colorSelect = conn.cursor()
    ColorSelect_name = 'ColorSelect_Output_g'
    re_colorSelect = "UPDATE `clean_container_status` SET "+ColorSelect_name+str(so_Output)+"= 'SO_InputtingBean' " \
         "ORDER BY `datetime` DESC LIMIT 1 "
    update_status_colorSelect.execute(re_colorSelect)
    conn.commit()

#更新色選機出料儲豆槽狀態 SO_InputtingBean改成SO_Stand_by
def update_ColorSelect_Output_status_Stand_by(so_Output):
    insert_data_clean()
    update_status_colorSelect = conn.cursor()
    ColorSelect_name = 'ColorSelect_Output_g'
    re_colorSelect = "UPDATE `clean_container_status` SET "+ColorSelect_name+str(so_Output)+"= 'SO_Stand_by' " \
         "ORDER BY `datetime` DESC LIMIT 1 "
    update_status_colorSelect.execute(re_colorSelect)
    conn.commit()

#更新脫皮機狀態 P_Waiting改成P_InputtingBean
def update_Peel_Tank_status(p_Output):
    insert_data_clean()
    update_status_Peel = conn.cursor()
    Peel_name = 'Peel_Tank_'
    re_Peel = "UPDATE `clean_container_status` SET "+Peel_name+str(p_Output)+"= 'P_InputtingBean' " \
         "ORDER BY `datetime` DESC LIMIT 1 "
    update_status_Peel.execute(re_Peel)
    conn.commit()

#更新脫皮機狀態 P_InputtingBean改成P_Stand_by
def update_Peel_Tank_status_Stand_by(p_Output):
    insert_data_clean()
    update_status_Peel = conn.cursor()
    Peel_name = 'Peel_Tank_'
    re_Peel = "UPDATE `clean_container_status` SET "+Peel_name+str(p_Output)+"= 'P_Stand_by' " \
         "ORDER BY `datetime` DESC LIMIT 1 "
    update_status_Peel.execute(re_Peel)
    conn.commit()

#更新脫皮機出料儲豆槽狀態 PO_Waiting改成PO_InputtingBean
def update_Peel_Output_status(po_Output):
    insert_data_clean()
    update_status_Peel = conn.cursor()
    Peel_name = 'Peel_Output_'
    re_Peel = "UPDATE `clean_container_status` SET "+Peel_name+str(po_Output)+"= 'PO_InputtingBean' " \
         "ORDER BY `datetime` DESC LIMIT 1 "
    update_status_Peel.execute(re_Peel)
    conn.commit()

#更新脫皮機出料儲豆槽狀態 PO_InputtingBean改成PO_Stand_by
def update_Peel_Output_status_Stand_by(po_Output):
    insert_data_clean()
    update_status_Peel = conn.cursor()
    Peel_name = 'Peel_Output_'
    re_Peel = "UPDATE `clean_container_status` SET "+Peel_name+str(po_Output)+"= 'PO_Stand_by' " \
         "ORDER BY `datetime` DESC LIMIT 1 "
    update_status_Peel.execute(re_Peel)
    conn.commit()

#傳送MQTT訊號
def send_mqtt():
    # 連線設定
    # 初始化地端程式
    client = mqtt.Client()
    # 設置日期時間的格式
    ISOTIMEFORMAT = '%m/%d %H:%M:%S'
    t = datetime.datetime.now().strftime(ISOTIMEFORMAT)

    # 設定登入帳號密碼
    client.username_pw_set("aisky-server","aisky")

    # 設定連線資訊(IP, Port, 連線時間)
    client.connect("60.250.156.234", 1883, 60)

    payload = {"command": "F_InputtingBean"}
    print(json.dumps(payload),t)
    #要發布的主題和內容
    #自行測試用
    client.publish("AISKY/Coffee/MK-G/b8:27:eb:7e:24:78", json.dumps(payload))
    #正式測試用
    #client.publish("AISKY/Coffee/MK-G/b8:27:eb:b4:59:3e", json.dumps(payload))
    #time.sleep(5)

#while True:
conn = pymysql.connect(
    host='127.0.0.1',
    user='root',
    password='g53743001',
    db='coffee',
    charset='utf8'
)

#抓取清洗入料儲豆槽狀態
cur1 = conn.cursor()
sql1 = "SELECT `clean_Input_1`,`clean_Input_2` FROM `clean_container_status` ORDER BY `sn` DESC LIMIT 1 "
cur1.execute(sql1)
get_Clean_Input = cur1.fetchone()

# 清洗入料儲豆槽1
CI1 = get_Clean_Input[0]
# 清洗入料儲豆槽2
CI2 = get_Clean_Input[1]

get_Clean_Input_status_N = [CI1, CI2]
c = 1
for i in range(len(get_Clean_Input_status_N)):
    c = i + 1
    p = 1
    if(get_Clean_Input_status_N[i] == 'CI_Waiting'):
        N = 'CI' + str(c)
        print(N + ' ' + get_Clean_Input_status_N[i] + ' 空桶等待')
        update_Clean_Input_status(c)
        time.sleep(1)
    elif(get_Clean_Input_status_N[i] == 'CI_InputtingBean'):
        print('CI' + str(c) + ' ' + get_Clean_Input_status_N[i] + ' 入豆中')
        send_mqtt()
        print('發送MQTT訊號通知', '採收豆子儲存槽'  + ' 可出豆')
        break
        # 修改清洗入料槽狀態
        #update_dry_Input_status(f)
        # 修改採收豆子儲存槽狀態
        #update_Ferment_Output_status(p)

        #要判斷咖啡豆採收豆子儲存槽狀態
        # for b in range(len(get_Ferment_Output_status_N)):
        #     p = b + 1
        #     if (get_Ferment_Output_status_N[b] == 'FO_InputtingBean'):
        #         print('FO' + str(p) + ' ' + get_Ferment_Output_status_N[b] + ' 入豆中')
        #     elif (get_Ferment_Output_status_N[b] == 'FO_Waiting'):
        #         print('FO' + str(p) + ' ' + get_Ferment_Output_status_N[b] + ' 空桶等待')
        #     elif (get_Ferment_Output_status_N[b] == 'FO_Stand_by'):
        #         print('FO' + str(p) + '待命')
        #     elif (get_Ferment_Output_status_N[b] == 'FO_OutputtingBean'):
        #         print('FO' + str(p) + ' ' + get_Ferment_Output_status_N[b] + ' 可出豆')
        #         send_mqtt()
        #         print('發送MQTT訊號通知', 'FO' + str(p) + ' 可出豆')
        #         # 修改乾燥入料槽狀態
        #         update_dry_Input_status(f)
        #         # 修改發酵出料狀態
        #         update_Ferment_Output_status(p)
        #         break

    elif(get_Clean_Input_status_N[i] == 'CI_OutputtingBean'):
        print('CI' + str(c) + ' ' + get_Clean_Input_status_N[i] + ' 可出豆')
        #抓取清洗統槽狀態
        cur1 = conn.cursor()
        sql1 = "SELECT `Clean_Tank_1`, `Clean_Tank_2`, `Clean_Tank_3`, `Clean_Tank_4` " \
               "FROM `clean_container_status` ORDER BY `sn` DESC LIMIT 1"
        cur1.execute(sql1)
        get_clean_Tank = cur1.fetchone()

        # 清洗槽1-4號
        C1 = get_clean_Tank[0]
        C2 = get_clean_Tank[1]
        C3 = get_clean_Tank[2]
        C4 = get_clean_Tank[3]

        clean_Tank_status = [C1, C2, C3, C4]
        c_tank = 1
        for i_tank in range(len(clean_Tank_status)):
            c_tank = i_tank + 1
            if (clean_Tank_status[i_tank] == 'C_Waiting'):
                update_Dry_Tank_status1(d_tank)
                print('D' + str(d_tank) + '修改發酵桶槽狀態C_Waiting->C_InputtingBean')
            elif (clean_Tank_status[i_tank] == 'C_InputtingBean'):
                # 修改發酵桶槽狀態C_InputtingBean->C_Standby
                update_Clean_Tank_status(c_tank)
                print('C' + str(c_tank) + ' ' + clean_Tank_status[i_tank] + ' 入豆中')
                send_mqtt()
                break
            elif (clean_Tank_status[i_tank] == 'C_OutputtingBean'):
                print('C' + str(c_tank) + ' ' + clean_Tank_status[i_tank] + ' 可出豆')
                #抓取色選機狀態
                cur1 = conn.cursor()
                sql1 = "SELECT `ColorSelect_Tank_1`,`ColorSelect_Tank_2` FROM `clean_container_status` ORDER BY `sn` DESC LIMIT 1 "
                cur1.execute(sql1)
                get_ColorSelect_Tank = cur1.fetchone()

                # 色選機狀態1
                S1 = get_ColorSelect_Tank[0]
                # 色選機狀態2
                S2 = get_ColorSelect_Tank[1]
                get_ColorSelect_Tank_status = [S1, S2]
                s_Tank = 1
                for i_Output in range(len(get_ColorSelect_Tank_status)):
                    s_Input = i_Output + 1
                    if (get_ColorSelect_Tank_status[i_Output] == 'S_Waiting'):
                        update_ColorSelect_Tank_status(s_Tank)
                        print('S' + str(s_Tank) + ' 修改色選機狀態S_Waiting->S_InputtingBean')
                    elif (get_ColorSelect_Tank_status[i_Output] == 'S_InputtingBean'):
                        update_ColorSelect_Tank_status_Stand_by(s_Tank)
                        print('修改' + 'S' + str(s_Tank) + '色選機狀態，並發送MQTT訊號。')
                        send_mqtt()
                    elif (get_ColorSelect_Tank_status[i_Output] == 'S_OutputtingBean'):
                        print('修改' + 'S' + str(s_Tank) + '可出豆')
                        cur1 = conn.cursor()
                        sql1 = "SELECT `ColorSelect_Output_g1`,`ColorSelect_Output_g2` FROM `clean_container_status` ORDER BY `sn` DESC LIMIT 1 "
                        cur1.execute(sql1)
                        get_ColorSelect_Output = cur1.fetchone()

                        # 色選出料儲豆槽1
                        SO1 = get_ColorSelect_Output[0]
                        # 色選出料儲豆槽2
                        SO2 = get_ColorSelect_Output[1]
                        get_ColorSelect_Output_status = [SO1, SO2]
                        so_Output = 1
                        for i_Output in range(len(get_ColorSelect_Output_status)):
                            so_Tank = i_Output + 1
                            if (get_ColorSelect_Output_status[i_Output] == 'SO_Waiting'):
                                update_ColorSelect_Output_status(so_Output)
                                print('SO' + str(so_Output) + ' 修改色選機狀態SO_Waiting->SO_InputtingBean')
                            elif (get_ColorSelect_Output_status[i_Output] == 'SO_InputtingBean'):
                                update_ColorSelect_Output_status_Stand_by(so_Output)
                                print('修改' + 'SO' + str(so_Output) + '色選機狀態，並發送MQTT訊號。')
                                send_mqtt()
                            elif (get_ColorSelect_Output_status[i_Output] == 'SO_OutputtingBean'):
                                print('修改' + 'SO' + str(so_Output) + '可出豆')
                                #抓取脫皮機狀態
                                cur1 = conn.cursor()
                                sql1 = "SELECT `Peel_Tank_1`,`Peel_Tank_2` FROM `clean_container_status` ORDER BY `sn` DESC LIMIT 1 "
                                cur1.execute(sql1)
                                get_Peel_Tank = cur1.fetchone()

                                # 脫皮狀態1
                                P1 = get_Peel_Tank[0]
                                # 脫皮狀態2
                                P2 = get_Peel_Tank[1]
                                get_Peel_Tank_status = [P1, P2]
                                p_Output = 1
                                for i_Output in range(len(get_Peel_Tank_status)):
                                    p_Output = i_Output + 1
                                    if (get_Peel_Tank_status[i_Output] == 'P_Waiting'):
                                        update_Peel_Tank_status(p_Output)
                                        print('P' + str(p_Output) + ' 修改脫皮機狀態P_Waiting->P_InputtingBean')
                                    elif (get_Peel_Tank_status[i_Output] == 'P_InputtingBean'):
                                        update_Peel_Tank_status_Stand_by(p_Output)
                                        print('修改' + 'P' + str(p_Output) + '脫皮機狀態，並發送MQTT訊號。')
                                        send_mqtt()
                                    elif (get_Peel_Tank_status[i_Output] == 'P_OutputtingBean'):
                                        print('修改' + 'P' + str(p_Output) + '可出豆')
                                        #抓取脫皮出要狀態
                                        cur1 = conn.cursor()
                                        sql1 = "SELECT `Peel_Output_1`,`Peel_Output_2` FROM `clean_container_status` ORDER BY `sn` DESC LIMIT 1 "
                                        cur1.execute(sql1)
                                        get_Peel_Output = cur1.fetchone()

                                        # 脫皮出料狀態1
                                        PO1 = get_Peel_Output[0]
                                        # 脫皮出料狀態2
                                        PO2 = get_Peel_Output[1]
                                        get_Peel_Output_status = [PO1, PO2]
                                        po_Output = 1
                                        for i_Output in range(len(get_Peel_Output_status)):
                                            po_Output = i_Output + 1
                                            if (get_Peel_Output_status[i_Output] == 'PO_Waiting'):
                                                update_Peel_Output_status(po_Output)
                                                print('PO' + str(po_Output) + ' 修改脫皮機出料狀態PO_Waiting->PO_InputtingBean')
                                            elif (get_Peel_Output_status[i_Output] == 'PO_InputtingBean'):
                                                update_Peel_Output_statusstatus_Stand_by(po_Output)
                                                print('修改' + 'PO' + str(po_Output) + '脫皮機出料狀態，並發送MQTT訊號。')
                                                send_mqtt()
                                            elif (get_Peel_Output_status[i_Output] == 'PO_Standby'):
                                                print('P' + str(po_Output) + ' 待命中')
                                            elif (get_Peel_Output_status[i_Output] == 'PO_Warning'):
                                                print('P' + str(po_Output) + ' 警告')
                                    elif (get_Peel_Tank_status[i_Output] == 'P_Standby'):
                                        print('P' + str(p_Output) + ' 待命中')
                                    elif (get_Peel_Tank_status[i_Output] == 'P_Warning'):
                                        print('P' + str(p_Output) + ' 警告')
                            elif (get_ColorSelect_Output_status[i_Output] == 'SO_Standby'):
                                print('SO' + str(so_Output) + ' 待命中')
                            elif (get_ColorSelect_Output_status[i_Output] == 'SO_Warning'):
                                print('SO' + str(so_Output) + ' 警告')
                    elif (get_ColorSelect_Tank_status[i_Output] == 'S_Standby'):
                        print('S' + str(s_Tank) + ' 待命中')
                    elif (get_ColorSelect_Tank_status[i_Output] == 'S_Warning'):
                        print('S' + str(s_Tank) + ' 警告')
            elif (clean_Tank_status[i_tank] == 'C_Standby'):
                print('C' + str(d_tank) + ' ' + clean_Tank_status[i_tank] + ' 待命')
            elif (clean_Tank_status[i_tank] == 'C_Warning'):
                print('C' + str(d_tank) + ' ' + clean_Tank_status[i_tank] + ' 警告')
            elif (clean_Tank_status[i_tank] == 'C_InputtingBean_Pause'):
                print('C' + str(c_tank) + ' ' + clean_Tank_status[i_tank] + ' 入豆暫停')
            elif (clean_Tank_status[i_tank] == 'C_InputtingBean_Finish'):
                print('C' + str(c_tank) + ' ' + clean_Tank_status[i_tank] + ' 入豆完成')
            elif (clean_Tank_status[i_tank] == 'C_RecycleWaterIn1'):
                print('C' + str(d_tank) + ' ' + clean_Tank_status[i_tank] + ' 乾燥中')
            elif (clean_Tank_status[i_tank] == 'C_Wash1'):
                print('C' + str(d_tank) + ' ' + clean_Tank_status[i_tank] + ' 清洗中')
            elif (clean_Tank_status[i_tank] == 'C_SewageOut1'):
                print('C' + str(d_tank) + ' ' + clean_Tank_status[i_tank] + ' 清洗中')
            elif (clean_Tank_status[i_tank] == 'C_RecycleWaterIn2'):
                print('C' + str(d_tank) + ' ' + clean_Tank_status[i_tank] + ' 乾燥中')
            elif (clean_Tank_status[i_tank] == 'C_Wash2'):
                print('C' + str(d_tank) + ' ' + clean_Tank_status[i_tank] + ' 清洗中')
            elif (clean_Tank_status[i_tank] == 'C_RecycleWaterOut2'):
                print('C' + str(d_tank) + ' ' + clean_Tank_status[i_tank] + ' 清洗中')
            elif (clean_Tank_status[i_tank] == 'C_RecycleWaterIn3'):
                print('C' + str(d_tank) + ' ' + clean_Tank_status[i_tank] + ' 乾燥中')
            elif (clean_Tank_status[i_tank] == 'C_Wash3'):
                print('C' + str(d_tank) + ' ' + clean_Tank_status[i_tank] + ' 清洗中')
            elif (clean_Tank_status[i_tank] == 'C_RecycleWaterOut3'):
                print('C' + str(d_tank) + ' ' + clean_Tank_status[i_tank] + ' 乾燥中')
    elif(get_Clean_Input_status_N[i] == 'CI_Standby'):
        print('CI' + str(f) + ' ' + get_Clean_Input_status_N[i] + '待命')
    elif(get_Clean_Input_status_N[i] == 'CI_Warning'):
        print('CI' + str(f) + ' ' + get_Clean_Input_status_N[i] + '警告')