"""
Auction project
Name: Nguyen Duy Thanh
Date: 27.12.2017
"""

from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import Column, String, Integer, Float, DateTime, ForeignKey

app = Flask(__name__)
app.config.from_pyfile('config.cfg')

db = SQLAlchemy(app)


class Item(db.Model):
    __tablename__ = 'item'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    description = Column(String, nullable=False)
    start_time = Column(DateTime)
    bid = db.relationship('Bid', backref='item', lazy=True)

    def __init__(self, id, name, description, start_time):
        self.id = id
        self.name = name
        self.description = description
        self.start_time = start_time


class User(db.Model):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    username = Column(String, nullable=False)
    password = Column(String, nullable=False)
    bid = db.relationship('Bid', backref='user', lazy=True)

    def __init__(self, id, username, password):
        self.id = id
        self.username = username
        self.password = password


class Bid(db.Model):
    __tablename__ = 'bid'
    id = Column(Integer, primary_key=True)
    price = Column(Float, nullable=False)
    user_id = Column(Integer, ForeignKey('user.id'), nullable=False)
    item_id = Column(Integer, ForeignKey('item.id'), nullable=False)

    def __init__(self, id, price):
        self.id = id
        self.price = price


@app.route('/insert')
def insert():
    # Add three users to the database
    user1 = User(1, 'ndthanh', 'newYear')
    user2 = User(2, 'bill', 'MSN*999')
    user3 = User(3, 'steve', 'App@2017')
    db.session.add(user1)
    db.session.add(user2)
    db.session.add(user3)

    # Add an item to the db
    item1 = Item(1, 'baseball', 'The baseball description.', '2017-12-21 00:00:00')
    db.session.add(item1)

    # Make one user auction a baseball
    bid1 = Bid(1, 10.5)
    bid1.user_id = user1.id
    bid1.item_id = item1.id
    db.session.add(bid1)

    # Have each other user place two bids on the baseball
    bid2 = Bid(2, 12.3)
    bid2.user_id = user2.id
    bid2.item_id = item1.id

    bid3 = Bid(3, 9.5)
    bid3.user_id = user2.id
    bid3.item_id = item1.id
    db.session.add(bid2)
    db.session.add(bid3)

    db.session.commit()
    return "Added data into DB!"


# Perform a query to find out which user placed the highest bid
@app.route('/query')
def query():
    max_price = db.session.query(db.func.max(Bid.price)).scalar()
    query_result = db.session.query(Bid).filter(Bid.price == max_price).all()

    user_id_all = []
    for item in query_result:
        user_id_all.append(item.user_id)

    # Find out which user placed the highest bid
    user_all = []
    for uid in user_id_all:
        user = User.query.filter_by(id=uid).first()
        user_all.append(user.username)

    return str(user_all)


# Drop all tables in db if exists
@app.route('/droptbls')
def drop_tables():
    db.reflect()
    db.drop_all('__all__')
    return "The tables have been drop."


# Create the tables
@app.route('/createtbls')
def create_tables():
    db.create_all()
    return "The tables have been created."

# Home page
@app.route('/')
def index():
    return render_template('index.html')


if __name__ == '__main__':
    app.run(debug=True)