class Order(db.Model):
    __tablename__ = 'orders'
    order_id = db.Column(db.Integer,
                         primary_key=True,
                         autoincrement=True,
                         nullable=False,
                         unique=True,
                         index=True)
    shop_id = db.Column(db.Integer,
                        db.ForeignKey('shops.shop_id'),
                        nullable=False,
                        index=True)
    user_id = db.Column(db.Integer,
                        db.ForeignKey('users.user_id'),
                        nullable=False,
                        index=True)
    user_contact = db.Column(db.String(32), nullable=False)
    user_location = db.Column(db.Enum(
        'Student Center', 'Shaw College', 'Muse College', 'Deligentia College',
        'Harmonia College', 'Le Tian Building', 'Zhi Ren Building',
        'Zhi Xin Building', 'Cheng Dao Building', 'Dao Yuan Building',
        'Li Wen Building', 'Qi Xian Building', 'Sports Hall', 'Research A',
        'Research B', 'Teaching A', 'Teaching B', 'Teaching C', 'Teaching D',
        'Administration Building', 'Staff Residence 1', 'Staff Residence 2',
        'Staff Residence 3', 'Staff Residence 4'),
                              nullable=False)
    delivery_fee = db.Column(db.Integer, nullable=False, default=0)
    create_time = db.Column(db.DateTime,
                            nullable=False,
                            default=datetime.utcnow)
    order_status = db.Column(db.Enum('pending', 'approved', 'denied',
                                     'delivering', 'finished', 'cancelled'),
                             nullable=False)
class Bookmark(db.Model):
    __tablename__ = 'bookmarks'
    user_id = db.Column(db.Integer,
                        db.ForeignKey('users.user_id'),
                        primary_key=True,
                        nullable=False,
                        index=True)
    shop_id = db.Column(db.Integer,
                        db.ForeignKey('shops.shop_id'),
                        primary_key=True,
                        nullable=False,
                        index=True)
class Order(db.Model):
    __tablename__ = 'orders'
    order_id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=False, unique=True, index=True)
    shop_id = db.Column(db.Integer, db.ForeignKey('shops.shop_id'), nullable=False, index=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'), nullable=False, index=True)
    user_location = db.Column(db.String(32))
    phone_number = db.Column(db.String(32))
    delivery_fee = db.Column(db.Integer)
    create_time = db.Column(db.DateTime, default=datetime.utcnow)
    order_status = db.Column(db.Enum('pending', 'approved', 'denied', 'delivering', 'finished', 'cancelled'))

    user = db.relationship('User', back_populates='orders') # user and orders
    shop = db.relationship('Shop', back_populates='orders') # shop and orders
    purchased_products = db.relationship('Purchased_Product', back_populates='order', cascade='all, delete-orphan') # order and purchased products
class Shop(db.Model):
    __tablename__ = 'shops'
    shop_id = db.Column(db.Integer,
                        primary_key=True,
                        autoincrement=True,
                        nullable=False,
                        unique=True,
                        index=True)
    user_id = db.Column(db.Integer,
                        db.ForeignKey('users.user_id'),
                        nullable=False,
                        index=True)
    shop_name = db.Column(db.String(32), nullable=False, index=True)
    shop_avatar = db.Column(db.LargeBinary, default=None)
    shop_info = db.Column(db.String(256), nullable=False, default='')
    shop_delivery_fee = db.Column(db.Integer, nullable=False, default=0)
    shop_rate_total = db.Column(db.Integer, nullable=False)
    shop_rate_number = db.Column(db.Integer, nullable=False)
    shop_balance = db.Column(db.Float, nullable=False)
    shop_contact = db.Column(db.String(32), nullable=False)
    shop_location = db.Column(db.Enum(
        'Student Center', 'Shaw College', 'Muse College', 'Deligentia College',
        'Harmonia College', 'Le Tian Building', 'Zhi Ren Building',
        'Zhi Xin Building', 'Research A', 'Research B', 'Teaching A',
        'Teaching B', 'Teaching C', 'Teaching D'),
                              nullable=False)
    shop_location_detail = db.Column(db.String(256),
                                     nullable=False,
                                     default='')
    shop_license_number = db.Column(db.String(32), nullable=False)
    shop_status = db.Column(db.Enum('open', 'closed', 'blocked', 'cancelled'),
                            nullable=False)
class Application(db.Model):
    __tablename__ = 'applications'
    application_id = db.Column(db.Integer,
                               primary_key=True,
                               autoincrement=True,
                               nullable=False,
                               unique=True,
                               index=True)
    user_id = db.Column(db.Integer,
                        db.ForeignKey('users.user_id'),
                        nullable=False,
                        index=True)
    application_type = db.Column(db.Enum('open', 'cancel'), nullable=False)
    shop_name = db.Column(db.String(32), nullable=False)
    shop_info = db.Column(db.String(256), nullable=False, default='')
    shop_contact = db.Column(db.String(32), nullable=False)
    shop_location = db.Column(db.Enum(
        'Student Center', 'Shaw College', 'Muse College', 'Deligentia College',
        'Harmonia College', 'Le Tian Building', 'Zhi Ren Building',
        'Zhi Xin Building', 'Research A', 'Research B', 'Teaching A',
        'Teaching B', 'Teaching C', 'Teaching D'),
                              nullable=False)
    shop_location_detail = db.Column(db.String(256),
                                     nullable=False,
                                     default='')
    shop_license_number = db.Column(db.String(32), nullable=False)
    application_status = db.Column(db.Enum('pending', 'approved', 'denied'),
                                   nullable=False)
class Purchased_Product(db.Model):
    __tablename__ = 'purchased_products'
    product_id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=False, unique=True, index=True)
    order_id = db.Column(db.Integer, db.ForeignKey('orders.order_id'), nullable=False, index=True)
    product_name = db.Column(db.String(32))
    product_price = db.Column(db.Float)
    quantity = db.Column(db.Integer)

    order = db.relationship('Order', back_populates='purchased_products') # order and purchased products
class Product(db.Model):
    __tablename__ = 'products'
    product_id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=False, unique=True, index=True)
    shop_id = db.Column(db.Integer, db.ForeignKey('shops.shop_id'), nullable=False, index=True)
    product_name = db.Column(db.String(32), index=True)
    product_avatar = db.Column(db.LargeBinary)
    product_info = db.Column(db.String(256))
    product_price = db.Column(db.Float)
    total_sale = db.Column(db.Integer)

    shop = db.relationship('Shop', back_populates='products') # shop and products
class Application(db.Model):
    __tablename__ = 'applications'
    application_id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=False, unique=True, index=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'), nullable=False, index=True)
    application_type = db.Column(db.Enum('open', 'cancel'))
    shop_name = db.Column(db.String(32))
    shop_info = db.Column(db.String(256))
    shop_contact = db.Column(db.String(32))
    shop_location = db.Column(db.String(32))
    shop_location_detail = db.Column(db.String(256))
    shop_license_number = db.Column(db.String(32))
    application_status = db.Column(db.Enum('pending', 'approved', 'denied'))

    user = db.relationship('User', back_populates='applications') # user and applications
class Purchased_Product(db.Model):
    __tablename__ = 'purchased_products'
    product_id = db.Column(db.Integer,
                           primary_key=True,
                           autoincrement=True,
                           nullable=False,
                           unique=True,
                           index=True)
    order_id = db.Column(db.Integer,
                         db.ForeignKey('orders.order_id'),
                         nullable=False,
                         index=True)
    product_name = db.Column(db.String(32), nullable=False)
    product_price = db.Column(db.Float, nullable=False)
    quantity = db.Column(db.Integer, nullable=False, default=1)
class Product(db.Model):
    __tablename__ = 'products'
    product_id = db.Column(db.Integer,
                           primary_key=True,
                           autoincrement=True,
                           nullable=False,
                           unique=True,
                           index=True)
    shop_id = db.Column(db.Integer,
                        db.ForeignKey('shops.shop_id'),
                        nullable=False,
                        index=True)
    product_name = db.Column(db.String(32), nullable=False, index=True)
    product_avatar = db.Column(db.LargeBinary, default=None)
    product_info = db.Column(db.String(256), nullable=False, default='')
    product_price = db.Column(db.Float, nullable=False)
    total_sale = db.Column(db.Integer, nullable=False)
class Shop(db.Model):
    __tablename__ = 'shops'
    shop_id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=False, unique=True, index=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'), nullable=False, index=True)
    shop_name = db.Column(db.String(32), index=True)
    shop_avatar = db.Column(db.LargeBinary)
    shop_info = db.Column(db.String(256))
    shop_delivery_fee = db.Column(db.Integer)
    shop_rate_total = db.Column(db.Integer)
    shop_rate_number = db.Column(db.Integer)
    shop_balance = db.Column(db.Float)
    shop_contact = db.Column(db.String(32))
    shop_location = db.Column(db.String(32))
    shop_location_detail = db.Column(db.String(256))
    shop_license_number = db.Column(db.String(32))
    shop_status = db.Column(db.Enum('open', 'closed', 'blocked', 'cancelled'))

    user = db.relationship('User', back_populates='shops') # user and shops
    products = db.relationship('Product', back_populates='shop', cascade='all, delete-orphan') # shop and products
    orders = db.relationship('Order', back_populates='shop', cascade='all, delete-orphan') # shop and orders
    bookmarked_by_users = db.relationship('User', secondary='bookmarks', back_populates='bookmarked_shops') # shops and users whom they bookmarked by