from datetime import datetime

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker

engine = create_engine('sqlite:///sqlite3.db')
engine.connect()


Base = declarative_base()


class Artist(Base):
    __tablename__ = 'Artist'
    artistid = Column(Integer(), primary_key=True)
    name = Column(String(100))
    Albums = relationship("Album")


class Album(Base):
    __tablename__ = 'Album'
    albumid = Column(Integer(), primary_key=True)
    title = Column(String(100))
    artistid = Column(Integer, ForeignKey("Artist.artistid"))
    Tracks = relationship("Track")


class Genre(Base):
    __tablename__ = 'Genre'
    genreid = Column(Integer(), primary_key=True)
    name = Column(String(100))
    Tracks = relationship("Track")


class MediaType(Base):
    __tablename__ = 'MediaType'
    mediatypeid = Column(Integer(), primary_key=True)
    name = Column(String(100))
    Tracks = relationship("Track")


class Track(Base):
    __tablename__ = 'Track'
    trackid = Column(Integer(), primary_key=True)
    name = Column(String(100))
    albumid = Column(Integer, ForeignKey("Album.albumid"))
    mediatypeid = Column(Integer, ForeignKey("MediaType.mediatypeid"))
    genreid = Column(Integer, ForeignKey("Genre.genreid"))
    composer = Column(String(100))
    milliseconds = Column(Integer())
    bytes = Column(Integer())
    unitprice = Column(Integer())
    PlaylistTracks = relationship("PlaylistTrack")


class Playlist(Base):
    __tablename__ = 'Playlist'
    playlistid = Column(Integer(), primary_key=True)
    name = Column(String(100))
    PlaylistTracks = relationship("PlaylistTrack")


class PlaylistTrack(Base):
    __tablename__ = 'PlaylistTrack'
    playlisttrackid = Column(Integer(), primary_key=True)
    trackid = Column(Integer, ForeignKey("Track.trackid"))
    playlistid = Column(Integer, ForeignKey("Playlist.playlistid"))


Session = sessionmaker(bind=engine)
session = Session()

Base.metadata.create_all(engine)

ar1 = Artist(name='Imagine Dragons')
ar2 = Artist(name='Mick Jagger and Keith Richards')
ar3 = Artist(name='Artik&Asti')
ar4 = Artist(name='Zivert')
ar5 = Artist(name='Кино')
ar6 = Artist(name='Баста')
ar7 = Artist(name='Би-2')
ar8 = Artist(name='The Rolling Stones')
ar9 = Artist(name='Red Hot Chili Peppers')
ar10 = Artist(name='Король и Шут')

al1 = Album(title='Как в старой сказке', artistid=1)
al2 = Album(title='Новогодняя песня', artistid=2)
al3 = Album(title='Миллениум X', artistid=3)
al4 = Album(title='Zima', artistid=4)
al5 = Album(title='12_22', artistid=5)
al6 = Album(title='Скандал', artistid=6)
al7 = Album(title='Горизонт событий', artistid=7)
al8 = Album(title='Aftermath', artistid=8)
al9 = Album(title='Океан', artistid=9)
al10 = Album(title='Night Visions', artistid=10)
al11 = Album(title='Birds', artistid=10)

g1 = Genre(name='punk')
g2 = Genre(name='Foreign rap')
g3 = Genre(name='Foreign pop')
g4 = Genre(name='alternative')
g5 = Genre(name='Foreign rock')
g6 = Genre(name='rap')
g7 = Genre(name='rock')
g8 = Genre(name='Russian stage')
g9 = Genre(name='Stage')
g10 = Genre(name='Russian rock')

mt1 = MediaType(name='mp3')
mt2 = MediaType(name='aac')
mt3 = MediaType(name='wav')
mt4 = MediaType(name='flac')
mt5 = MediaType(name='dsd')

t1 = Track(name='Bones', albumid=1, mediatypeid=5, genreid=10, composer='Дэн Рейнольдс', milliseconds=11123, bytes=12115, unitprice=111)
t2 = Track(name='Paint it black', albumid=2, mediatypeid=2, genreid=5, composer='Mick Jagger', milliseconds=111111, bytes=87654, unitprice=10)
t3 = Track(name='Гармония', albumid=3, mediatypeid=3, genreid=3, composer='Севиль', milliseconds=222222, bytes=11215, unitprice=25)
t4 = Track(name='Wake up!', albumid=4, mediatypeid=4, genreid=4, composer='Zivert', milliseconds=333333, bytes=11251, unitprice=50)
t5 = Track(name='Группа крови', albumid=5, mediatypeid=5, genreid=5, composer='Виктор Цой', milliseconds=123222, bytes=11512, unitprice=75)
t6 = Track(name='Вечно молодой', albumid=6, mediatypeid=1, genreid=6, composer='Баста', milliseconds=984844, bytes=11521, unitprice=125)
t7 = Track(name='Компромисс', albumid=7, mediatypeid=2, genreid=7, composer='Шура', milliseconds=32323, bytes=12511, unitprice=150)
t8 = Track(name='Русская зима', albumid=7, mediatypeid=3, genreid=8, composer='Нина Бродская', milliseconds=444433, bytes=12151, unitprice=175)
t9 = Track(name='Салют, Вера', albumid=7, mediatypeid=4, genreid=9, composer='Валерий Меладзе', milliseconds=44332, bytes=15211, unitprice=111)
t10 = Track(name='Dreams', albumid=3, mediatypeid=5, genreid=10, composer='Дэн Рейнольдс', milliseconds=22233, bytes=10000, unitprice=175)
t11 = Track(name='Кукла колдуна', albumid=1, mediatypeid=1, genreid=1, composer='Михаил Горшенёв', milliseconds=12345, bytes=98765, unitprice=111)


pl1 = Playlist(name='Songs for Soul')
pl2 = Playlist(name='Relax Songs')
pl3 = Playlist(name='Songs for Montage')
pl4 = Playlist(name='Rock Songs')
pl5 = Playlist(name='Pop Songs')

plt1 = PlaylistTrack(trackid=1, playlistid=1)
plt2 = PlaylistTrack(trackid=5, playlistid=1)
plt3 = PlaylistTrack(trackid=8, playlistid=1)
plt4 = PlaylistTrack(trackid=3, playlistid=2)
plt5 = PlaylistTrack(trackid=7, playlistid=2)
plt6 = PlaylistTrack(trackid=9, playlistid=2)
plt7 = PlaylistTrack(trackid=10, playlistid=3)
plt8 = PlaylistTrack(trackid=1, playlistid=4)
plt9 = PlaylistTrack(trackid=10, playlistid=4)
plt10 = PlaylistTrack(trackid=1, playlistid=5)
plt11 = PlaylistTrack(trackid=2, playlistid=5)
plt12 = PlaylistTrack(trackid=3, playlistid=5)
plt13 = PlaylistTrack(trackid=5, playlistid=5)
plt14 = PlaylistTrack(trackid=6, playlistid=5)
plt15 = PlaylistTrack(trackid=8, playlistid=5)
plt16 = PlaylistTrack(trackid=9, playlistid=5)

session.add_all([ar1, ar2, ar3, ar4, ar5, ar6, ar7, ar8, ar9, ar10,
                 al1, al2, al3, al4, al5, al6, al7, al8, al9, al10,
                 g1, g2, g3, g4, g5, g6, g7, g8, g9, g10,
                 mt1, mt2, mt3, mt4, mt5,
                 t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11,
                 pl1, pl2, pl3, pl4, pl5,
                 plt1, plt2, plt3, plt4, plt5, plt6, plt7, plt8, plt9, plt10, plt11, plt12, plt13, plt14, plt15, plt16])

session.commit()

print('Задание №1')
print(*session.query(Track.name, Artist.name, Track.milliseconds).
      join(Album, Track.albumid == Album.albumid).
      join(Artist, Artist.artistid == Album.artistid).
      order_by(Track.name).all(), sep='\n')
print('\n')


print('Задание №2')
print(session.query(Track).count())
print('\n')


print('Задание №3')
print(*session.query(Album.__table__).limit(10).all(), sep='\n')
print('\n')


print('Задание №4')
print(*session.query(Genre.__table__).filter(Genre.name.like("%Foreign%")).all(), sep='\n')
print('\n')


print('Задание №5')
print(*session.query(Album.title, Artist.name, func.sum(Track.bytes), func.sum(Track.unitprice)).
      join(Track, Track.albumid == Album.albumid).
      join(Artist, Album.artistid == Artist.artistid).
      group_by(Album.title).having(func.count('*') > 0).all(), sep='\n')
print('\n')


print('Задание №6')
print(session.query(Artist.__table__).
      join(Album, Album.artistid == Artist.artistid).
      join(Track, Track.albumid == Album.albumid).
      join(PlaylistTrack, PlaylistTrack.trackid == Track.trackid).distinct().count())
print('\n')


print('Задание №7')
print(session.query(Artist.__table__).
      join(Album, Album.artistid == Artist.artistid).
      join(Track, Track.albumid == Album.albumid).
      join(PlaylistTrack, PlaylistTrack.trackid == Track.trackid).
      where(PlaylistTrack.playlistid == 5).distinct().count())