from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

engine = create_engine('postgresql://ubuntu:thinkful@localhost:5432/tbay')
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()

from datetime import datetime
from sqlalchemy import Table, Column, Integer, String, Float, DateTime, Date, ForeignKey

user_item_table = Table('user_item_association', Base.metadata,
    Column('user_id', Integer, ForeignKey('users.id')),
    Column('item_id', Integer, ForeignKey('items.id'))
)

class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True)
    username = Column(String, nullable=False)
    password = Column(String, nullable=False)
    sell_items = relationship("Item",backref="user")
    bid_items = relationship("Item", secondary="user_item_association", backref="users")

    bid_id = Column(Integer, ForeignKey('bids.id'))

class Item(Base):
    __tablename__ = "items"

    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    description = Column(String)
    start_time = Column(DateTime, default=datetime.utcnow)
    bids = relationship("Bid", backref="item")

    user_id = Column(Integer, ForeignKey('users.id'),nullable=False)

class Bid(Base):
    __tablename__ = "bids"

    id = Column(Integer, primary_key=True)
    price = Column(Float, nullable=False)

    item_id = Column(Integer, ForeignKey('items.id'),nullable=False)
    users = relationship("User", backref="bid")

Base.metadata.create_all(engine)

John = User(username="John",password="Johnpw")
Michael = User(username="Michael",password="Michaelpw")
Amanda = User(username="Amanda",password="Amandapw")

Baseball = Item(name="Baseball", user=John)
Guitar = Item(name="Guitar",user=John)

Ball_Bid1 = Bid(price=20.50, item=Baseball)
Ball_Bid2 = Bid(price=22.70, item=Baseball)

Michael.bid_items = [Baseball]

session.add_all([John, Baseball, Guitar, Ball_Bid1, Ball_Bid2])
session.commit()

for item in John.sell_items:
    print(item.name)

for bid in Baseball.bids:
    print(bid.price)

for item in Michael.bid_items:
    print(item.name)