from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///new_book_database.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)


class Book(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(250), unique=True, nullable=False)
    author = db.Column(db.String(250), nullable=False)
    rating = db.Column(db.Float, nullable=False)

    def __repr__(self):
        return f'"<{self.id}> {self.title} - {self.author}"'

    def get_dict(self):
        return {
            "id": self.id,
            "title": self.title,
            "author": self.author,
            "rating": self.rating,
        }


db.create_all()

# creating new record
db.session.add(Book(id=1, title="ABC", author="DEF", rating=5.96))
db.session.add(Book(title="OMG", author="DEF", rating=5.96))  # id will be autogenerated
db.session.add(Book(title="OMG1", author="DEF", rating=7.96))  # id will be autogenerated
db.session.add(Book(title="OMG2", author="DEF", rating=4.96))  # id will be autogenerated
db.session.commit()

# read all
books = db.session.query(Book).all()
print(books)
# or
books = Book.query.all()
print(books)

# read a particular record
my_books = Book.query.filter_by(title='ABC').all()
my_book = Book.query.filter_by(title='ABC').first()
print(my_book)
print(my_books)

# updating
my_book = Book.query.filter_by(title='ABC').first()
my_book.title = 'ABC-2'
db.session.commit()

books = Book.query.all()
print(books)

# update a record by Primary key
book_id = 2
my_book = Book.query.get(book_id)
my_book.title = 'OMGLOLO'
db.session.commit()

books = Book.query.all()
print(books)

# deleting by id
book_id = 1
my_book = Book.query.get(book_id)
db.session.delete(my_book)
db.session.commit()

books = Book.query.all()
print(books)

# ordering
books_ranking = Book.query.order_by(Book.rating.desc())
# books_ranking = Book.query.order_by(Book.rating.asc())  # for ascending

c = db.session.query(Book).count()  # count row

for book in books_ranking:
    print(book.get_dict())
