# coding: utf-8
from sqlalchemy import Column, Date, DateTime, Float, Index, Integer, String, Text
from sqlalchemy.schema import FetchedValue
from flask_sqlalchemy import SQLAlchemy


# db = SQLAlchemy()

#與當前項目相關的模型文件，即所有的實體類在此編寫
from coffee_manage import db

#用戶表
class User(db.Model):
    __tablename__ = 'user'

    userID = db.Column(db.Integer, primary_key=True)
    firstname = db.Column(db.String(30), nullable=False)
    lastname = db.Column(db.String(30), nullable=False)
    mail = db.Column(db.String(50), nullable=False)
    phone = db.Column(db.String(20), nullable=False)
    username = db.Column(db.String(30), nullable=False, unique=True)
    password = db.Column(db.String(40), nullable=False)
    status = db.Column(db.Integer, nullable=False, info='0:admin;1:new;9:disable')

#發酵槽溫度感測器表
class TankTemSensor(db.Model):
    __tablename__ = 'tank_tem_sensor'

    sn = db.Column(db.Integer, primary_key=True)
    datetime = db.Column(db.DateTime, nullable=False)
    tank_num = db.Column(db.Integer, nullable=False)
    tem = db.Column(db.Text, nullable=False)

#發酵槽二氧化碳感測器表
class TankCO2Sensor(db.Model):
    __tablename__ = 'tank_co2_sensor'

    sn = db.Column(db.Integer, primary_key=True)
    datetime = db.Column(db.DateTime, nullable=False)
    tank_num = db.Column(db.Integer, nullable=False)
    co2 = db.Column(db.Text, nullable=False)

#發酵槽酸鹼值感測器表
class TankPHSensor(db.Model):
    __tablename__ = 'tank_ph_sensor'

    sn = db.Column(db.Integer, primary_key=True)
    datetime = db.Column(db.DateTime, nullable=False)
    tank_num = db.Column(db.Integer, nullable=False)
    ph = db.Column(db.Text, nullable=False)

#發酵槽酸鹼值感測器表
class TankECSensor(db.Model):
    __tablename__ = 'tank_ec_sensor'

    sn = db.Column(db.Integer, primary_key=True)
    datetime = db.Column(db.DateTime, nullable=False)
    tank_num = db.Column(db.Integer, nullable=False)
    ec = db.Column(db.Text, nullable=False)

#發酵槽超音波感測器表
class TankSonicSensor(db.Model):
    __tablename__ = 'tank_sonic_sensor'

    sn = db.Column(db.Integer, primary_key=True)
    datetime = db.Column(db.DateTime, nullable=False)
    tank_num = db.Column(db.Integer, nullable=False)
    sonic = db.Column(db.Text, nullable=False)


class BeanValve(db.Model):
    __tablename__ = 'bean_valve'

    sn = db.Column(db.Integer, primary_key=True)
    datetime = db.Column(db.DateTime, nullable=False)
    duration = db.Column(db.Text, nullable=False)
    start = db.Column(db.Text, nullable=False)
    end = db.Column(db.Text, nullable=False)

class WashMachine(db.Model):
    __tablename__ = 'wash_machine'

    sn = db.Column(db.Integer, primary_key=True)
    datetime = db.Column(db.DateTime, nullable=False)
    duration = db.Column(db.Text, nullable=False)
    start = db.Column(db.Text, nullable=False)
    end = db.Column(db.Text, nullable=False)

class ConveyorBelt1(db.Model):
    __tablename__ = 'conveyor_belt1'

    sn = db.Column(db.Integer, primary_key=True)
    datetime = db.Column(db.DateTime, nullable=False)
    duration = db.Column(db.Text, nullable=False)
    start = db.Column(db.Text, nullable=False)
    end = db.Column(db.Text, nullable=False)

class Cargo1Disinfect(db.Model):
    __tablename__ = 'cargo1_disinfect'

    sn = db.Column(db.Integer, primary_key=True)
    datetime = db.Column(db.DateTime, nullable=False)
    duration = db.Column(db.Text, nullable=False)
    start = db.Column(db.Text, nullable=False)
    end = db.Column(db.Text, nullable=False)

class ColorMachine(db.Model):
    __tablename__ = 'color_machine'

    sn = db.Column(db.Integer, primary_key=True)
    datetime = db.Column(db.DateTime, nullable=False)
    duration = db.Column(db.Text, nullable=False)
    start = db.Column(db.Text, nullable=False)
    end = db.Column(db.Text, nullable=False)

class ConveyorBelt2(db.Model):
    __tablename__ = 'conveyor_belt2'

    sn = db.Column(db.Integer, primary_key=True)
    datetime = db.Column(db.DateTime, nullable=False)
    duration = db.Column(db.Text, nullable=False)
    start = db.Column(db.Text, nullable=False)
    end = db.Column(db.Text, nullable=False)

class PeelingMachine(db.Model):
    __tablename__ = 'peeling_machine'

    sn = db.Column(db.Integer, primary_key=True)
    datetime = db.Column(db.DateTime, nullable=False)
    duration = db.Column(db.Text, nullable=False)
    start = db.Column(db.Text, nullable=False)
    end = db.Column(db.Text, nullable=False)



class ConveyorBelt3(db.Model):
    __tablename__ = 'conveyor_belt3'

    sn = db.Column(db.Integer, primary_key=True)
    datetime = db.Column(db.DateTime, nullable=False)
    duration = db.Column(db.Text, nullable=False)
    start = db.Column(db.Text, nullable=False)
    end = db.Column(db.Text, nullable=False)



class Bacteria(db.Model):
    __tablename__ = 'bacteria'

    sn = db.Column(db.Integer, primary_key=True)
    datetime = db.Column(db.DateTime, nullable=False)
    tank_num = db.Column(db.Integer, nullable=False)
    duration = db.Column(db.Text, nullable=False)
    start = db.Column(db.Text, nullable=False)
    end = db.Column(db.Text, nullable=False)

class Cargo2Disinfect(db.Model):
    __tablename__ = 'cargo2_disinfect'

    sn = db.Column(db.Integer, primary_key=True)
    datetime = db.Column(db.DateTime, nullable=False)
    tank_num = db.Column(db.Integer, nullable=False)
    duration = db.Column(db.Text, nullable=False)
    start = db.Column(db.Text, nullable=False)
    end = db.Column(db.Text, nullable=False)

class Heating(db.Model):
    __tablename__ = 'heating'

    sn = db.Column(db.Integer, primary_key=True)
    datetime = db.Column(db.DateTime, nullable=False)
    tank_num = db.Column(db.Integer, nullable=False)
    duration = db.Column(db.Text, nullable=False)
    start = db.Column(db.Text, nullable=False)
    end = db.Column(db.Text, nullable=False)

class Stir(db.Model):
    __tablename__ = 'stir'

    sn = db.Column(db.Integer, primary_key=True)
    datetime = db.Column(db.DateTime, nullable=False)
    tank_num = db.Column(db.Integer, nullable=False)
    duration = db.Column(db.Text, nullable=False)
    start = db.Column(db.Text, nullable=False)
    end = db.Column(db.Text, nullable=False)

class WaterInjection(db.Model):
    __tablename__ = 'water_injection'

    sn = db.Column(db.Integer, primary_key=True)
    datetime = db.Column(db.DateTime, nullable=False)
    tank_num = db.Column(db.Integer, nullable=False)
    duration = db.Column(db.Text, nullable=False)
    start = db.Column(db.Text, nullable=False)
    end = db.Column(db.Text, nullable=False)



class TopValve(db.Model):
    __tablename__ = 'top_valve'

    sn = db.Column(db.Integer, primary_key=True)
    datetime = db.Column(db.DateTime, nullable=False)
    tank_num = db.Column(db.Integer, nullable=False)
    duration = db.Column(db.Text, nullable=False)
    start = db.Column(db.Text, nullable=False)
    end = db.Column(db.Text, nullable=False)

class BottomValve(db.Model):
    __tablename__ = 'bottom_valve'

    sn = db.Column(db.Integer, primary_key=True)
    datetime = db.Column(db.DateTime, nullable=False)
    tank_num = db.Column(db.Integer, nullable=False)
    duration = db.Column(db.Text, nullable=False)
    start = db.Column(db.Text, nullable=False)
    end = db.Column(db.Text, nullable=False)

class Temperature(db.Model):
    __tablename__ = 'temperature'

    sn = db.Column(db.Integer, primary_key=True)
    datetime = db.Column(db.DateTime, nullable=False)
    tank_num = db.Column(db.Integer, nullable=False)
    duration = db.Column(db.Text, nullable=False)
    start = db.Column(db.Text, nullable=False)
    end = db.Column(db.Text, nullable=False)

class Hoist(db.Model):
    __tablename__ = 'hoist'

    sn = db.Column(db.Integer, primary_key=True)
    datetime = db.Column(db.DateTime, nullable=False)
    duration = db.Column(db.Text, nullable=False)
    start = db.Column(db.Text, nullable=False)
    end = db.Column(db.Text, nullable=False)

class Dryer(db.Model):
    __tablename__ = 'dryer'

    sn = db.Column(db.Integer, primary_key=True)
    datetime = db.Column(db.DateTime, nullable=False)
    duration = db.Column(db.Text, nullable=False)
    start = db.Column(db.Text, nullable=False)
    end = db.Column(db.Text, nullable=False)

class Relay(db.Model):
    __tablename__ = 'relay'

    sn = db.Column(db.Integer, primary_key=True)
    datetime = db.Column(db.DateTime, nullable=False)
    tank_num = db.Column(db.Integer, nullable=False)
    status= db.Column(db.Text, nullable=False)


class PeelingMachineRPM(db.Model):
    __tablename__ = 'peeling_machine_rpm'

    sn = db.Column(db.Integer, primary_key=True)
    datetime = db.Column(db.DateTime, nullable=False)
    tank_num = db.Column(db.Integer, nullable=False)
    rpm = db.Column(db.Text, nullable=False)

class StirRPM(db.Model):
    __tablename__ = 'stir_rpm'

    sn = db.Column(db.Integer, primary_key=True)
    datetime = db.Column(db.DateTime, nullable=False)
    tank_num = db.Column(db.Integer, nullable=False)
    rpm = db.Column(db.Text, nullable=False)



db.create_all()