import sqlalchemy
import datetime
from sqlalchemy import Column, ForeignKey, create_engine
from sqlalchemy.types import (INTEGER, TEXT, VARCHAR, DATETIME)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

Base = declarative_base()


class Author(Base):
    __tablename__ = 'authors'
    id = Column(INTEGER, primary_key=True)
    name = Column(VARCHAR(30), unique=True, nullable=False)
    last_activity = Column(DATETIME, nullable=False)
    posts = relationship("Post")
    def __repr__(self):
      return f'{self.id}  {self.name}    {self.last_activity}'


class Post(Base):
    __tablename__ = 'posts'
    id = Column(INTEGER, primary_key=True)
    title = Column(VARCHAR(50), unique=True, nullable=False)
    content = Column(TEXT, nullable=False)
    author_id = Column(INTEGER, ForeignKey('authors.id'), nullable=False)
    published_on = Column(DATETIME, nullable=False)

    author = relationship("Author")
    def __repr__(self):
      return f'{self.id}  {self.title}    {self.content}   {self.author_id}   {self.published_on}'


engine = create_engine('sqlite:///blog.db', echo=True)
session = sqlalchemy.orm.Session(bind=engine)

Base.metadata.create_all(bind=engine)

# author1 = Author(name='Joker', last_activity=datetime.datetime.now())
# author2 = Author(name='MegaAdmin', last_activity=datetime.datetime.now())
# author3 = Author(name='JuniorAdmin', last_activity=datetime.datetime.now())

# session.add_all([author1, author2,author3])


# post1 = Post(title = 'LOL', content = '...1', author_id= 1 , published_on=datetime.datetime.now())
# post2 = Post(title = 'CHIKIBAMBONY', content = '...2', author_id = 2, published_on=datetime.datetime.now())
# post3 = Post(title = 'AWESOME', content = '...3', author_id = 1, published_on=datetime.datetime.now())
# post4 = Post(title = 'CHEBUREK', content = '...4', author_id = 2, published_on=datetime.datetime.now())
# post5 = Post(title = 'TVAR', content = '...5', author_id = 3, published_on=datetime.datetime.now())
# post6 = Post(title = 'GERALD', content = '...6', author_id = 1, published_on=datetime.datetime.now())

# # session.add_all([post1,post2,post3,post4,post5,post6])

session.commit()

# data = session.query(Post).all()
# # data[0].title = "The Best"

# p1 = session.query(Post).get(1)
# print (p1)



d = session.query(Author).filter_by(id=2).first()
print (d.posts)

# new = Post(title='AL',content = '?',published_on = datetime.datetime.now())
# d.posts.append(new)

# session.commit()