import enum

import sqlalchemy
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import (
    BigInteger,
    Boolean,
    DATE,
    Integer,
    MetaData,
    TIMESTAMP,
    Table,
    Text,
    create_engine,
)
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.sql import expression
from sqlalchemy.types import DateTime

from delphi_api.const import SQLALCHEMY_DATABASE_URI

db = SQLAlchemy()


# noinspection PyPep8Naming
class utcnow(expression.FunctionElement):
    """See: https://docs.sqlalchemy.org/en/13/core/compiler.html#utc-timestamp-function"""
    type = DateTime()


@compiles(utcnow)
def default_utcnow(element, compiler, **kw):
    return "TIMEZONE('utc', CURRENT_TIMESTAMP)"


@compiles(utcnow, 'postgresql')
def pg_utcnow(element, compiler, **kw):
    return "TIMEZONE('utc', CURRENT_TIMESTAMP)"


engine = create_engine(SQLALCHEMY_DATABASE_URI)

#: Have postgres generate the uuid4 for primary keys
pg_uuid = sqlalchemy.text('gen_random_uuid()')

#: MetaData for the BaseModel
BaseMetaData = MetaData(bind=engine)
#: Declarative style base model for Postgres models
BaseModel = declarative_base(bind=engine, metadata=BaseMetaData)

#: Global session to be instantiated within local scopes
Session = sessionmaker(bind=engine, autoflush=False)


class TrackableMixin:
    created_at = db.Column(TIMESTAMP, nullable=False, server_default=utcnow())
    updated_at = db.Column(TIMESTAMP, nullable=True, onupdate=utcnow())
    deleted_at = db.Column(TIMESTAMP, nullable=True)

    @hybrid_property
    def updated(self):
        return self.updated_at is not None

    @hybrid_property
    def deleted(self):
        return self.deleted_at is not None


class ImageFieldMixin:
    @hybrid_property
    def image(self):
        return {}


class PlaylistType(enum.Enum):
    playlist = 'playlist'
    chart = 'chart'


artist_product_table = Table(
    'artist_product', BaseModel.metadata,
    db.Column('artist_id', Text, db.ForeignKey('artist.artist_id'), nullable=False),
    db.Column('product_id', Text, db.ForeignKey('product.product_id'), nullable=False),
)

label_product_table = Table(
    'label_product', BaseModel.metadata,
    db.Column('label_id', Text, db.ForeignKey('label.label_id'), nullable=False),
    db.Column('product_id', Text, db.ForeignKey('product.product_id'), nullable=False),
)

artist_track_table = Table(
    'artist_track', BaseModel.metadata,
    db.Column('artist_id', Text, db.ForeignKey('artist.artist_id'), nullable=False),
    db.Column('track_id', Text, db.ForeignKey('track.track_id'), nullable=False),
)


class Region(BaseModel, TrackableMixin):
    __tablename__ = 'region'
    region_id = db.Column(Text, primary_key=True, comment='Same as country_code')
    region_name = db.Column(Text, nullable=True)
    region_code_no = db.Column(Text, nullable=True)
    sub_region_name = db.Column(Text, nullable=True)
    sub_region_code_no = db.Column(Text, nullable=True)
    country_code = db.Column(Text, nullable=True, unique=True, index=True,
                             comment='GRAS:isrc_country_code')
    country_code_a3 = db.Column(Text, nullable=True, unique=True, index=True,
                                comment='GRAS:iso_3166_A3_country_code')
    country_name = db.Column(Text, nullable=True, comment='GRAS:country_name')
    country_code_no = db.Column(Text, nullable=True, unique=True, index=True)


class Dsp(BaseModel, TrackableMixin):
    __tablename__ = 'dsp'
    dsp_id = db.Column(Text, primary_key=True)
    name = db.Column(Text, nullable=False)
    slug = db.Column(Text, nullable=False, unique=True, index=True)
    partner_key = db.Column(Integer, nullable=True, index=True)
    partner_code = db.Column(Text, nullable=True)
    active = db.Column(Boolean, nullable=False, default=True)

    playlists = relationship('Playlist', back_populates='dsp')
    playlist_ranks = relationship('PlaylistRank', back_populates='dsp')
    charts = relationship('Chart', back_populates='dsp')
    chart_ranks = relationship('ChartRank', back_populates='dsp')


class Label(BaseModel, TrackableMixin):
    __tablename__ = 'label'
    label_id = db.Column(Text, primary_key=True)
    name = db.Column(Text, nullable=True, comment='GRAS:label_name')
    repertoire_owner = db.Column(Text, nullable=True)
    profit_center = db.Column(Text, nullable=True)
    parent_repertoire_owner = db.Column(Text, nullable=True)
    products = relationship('Product', back_populates='label')


class Product(BaseModel, TrackableMixin):
    __tablename__ = 'product'
    product_id = db.Column(Text, primary_key=True)
    label_id = db.Column(Text, db.ForeignKey(Label.label_id), nullable=True)
    prod_no = db.Column(Text, index=True, comment='GRAS:prod_no')
    digital_barcode = db.Column(Text, nullable=True, comment='GRAS:digital_barcode')
    orig_prod_no = db.Column(Text, nullable=True, comment='GRAS:orig_prod_no')
    release_date = db.Column(DATE, nullable=True, comment='GRAS:first_release_date')
    digital_title_suppl = db.Column(Text, nullable=True, comment='GRAS:digital_title_suppl')
    sub_title = db.Column(Text, nullable=True, comment='GRAS:sub_title')
    copyright_line = db.Column(Text, nullable=True, comment='GRAS:c_line')
    isan = db.Column(Text, nullable=True, comment='GRAS:isan')

    name = db.Column(Text, nullable=True)
    spotify_popularity = db.Column(BigInteger, nullable=True)
    spotify_album_id = db.Column(Text, nullable=True)

    label = relationship('Label', back_populates='products')
    artists = relationship('Artist', back_populates='products', secondary=artist_product_table)
    tracks = relationship('Track', back_populates='product')


class Artist(BaseModel, TrackableMixin, ImageFieldMixin):
    __tablename__ = 'artist'
    artist_id = db.Column(Text, primary_key=True)
    description = db.Column(Text, nullable=True)
    particip_no = db.Column(Text, index=True, comment='GRAS:particip_no')
    full_name = db.Column(Text, nullable=True, comment='GRAS:particip_full_name')
    first_name = db.Column(Text, nullable=True, comment='GRAS:first_name')
    last_name = db.Column(Text, nullable=True, comment='GRAS:last_name')
    spotify_artist_id = db.Column(Text, nullable=True)
    apple_artist_id = db.Column(Integer, nullable=True)
    spotify_popularity = db.Column(BigInteger, nullable=True)
    date_of_birth = db.Column(DATE, nullable=True)
    date_of_death = db.Column(DATE, nullable=True)
    isni = db.Column(Text, nullable=True)

    products = relationship('Product', back_populates='artists', secondary=artist_product_table)
    tracks = relationship('Track', back_populates='artists', secondary=artist_track_table)
    participants = relationship('Participant', uselist=True, viewonly=True,
                                primaryjoin="and_(Artist.artist_id==Participant.artist_id,"
                                            "Participant.membership_type=='Primary')")

    @property
    def artist_and_participant_ids(self):
        # noinspection PyTypeChecker
        return [self.artist_id] + [p.participant_id for p in self.participants]

    @hybrid_property
    def tracks_ext(self):
        """Extended tracks including featuring artists"""
        # noinspection PyPropertyAccess
        return db.session.query(Track).select_from(Track) \
            .join(artist_track_table) \
            .filter(artist_track_table.c.artist_id.in_(self.artist_and_participant_ids))


class Playlist(BaseModel, TrackableMixin, ImageFieldMixin):
    __tablename__ = 'playlist'
    playlist_id = db.Column(Text, primary_key=True)
    dsp_id = db.Column(Text, db.ForeignKey(Dsp.dsp_id), index=True, nullable=False)
    country_code = db.Column(Text, db.ForeignKey(Region.region_id), index=True, nullable=True)
    name = db.Column(Text, nullable=True)
    description = db.Column(Text, nullable=True)
    num_tracks = db.Column(Integer, nullable=True)
    playlist_type = db.Column(Text, nullable=True)
    uri = db.Column(Text, nullable=True)
    dsp_playlist_id = db.Column(Text, nullable=False)

    dsp = relationship('Dsp', back_populates='playlists')
    playlist_ranks = relationship('PlaylistRank', back_populates='playlist')
    playlist_rank = relationship('PlaylistRank', uselist=False, viewonly=True,
                                 primaryjoin='and_(Playlist.playlist_id==PlaylistRank.playlist_id, '
                                             'PlaylistRank.is_latest==True)')

    @hybrid_property
    def rank(self):
        return self.playlist_rank.rank if self.playlist_rank else None


class PlaylistRank(BaseModel, TrackableMixin):
    __tablename__ = 'playlist_rank'
    playlist_rank_id = db.Column(BigInteger, primary_key=True)
    playlist_id = db.Column(Text, db.ForeignKey(Playlist.playlist_id), index=True, nullable=False)
    report_date = db.Column(DATE, nullable=False)
    rank = db.Column(BigInteger, nullable=True)
    dsp_id = db.Column(Text, db.ForeignKey(Dsp.dsp_id), index=False, nullable=False)
    country_code = db.Column(Text, db.ForeignKey(Region.region_id), index=True, nullable=True)
    is_latest = db.Column(Boolean, nullable=False, default=False)

    dsp = relationship('Dsp', back_populates='playlist_ranks')
    playlist = relationship('Playlist', back_populates='playlist_ranks')


class Chart(BaseModel, TrackableMixin, ImageFieldMixin):
    __tablename__ = 'chart'
    chart_id = db.Column(Text, primary_key=True)
    dsp_id = db.Column(Text, db.ForeignKey(Dsp.dsp_id), index=True, nullable=False)
    country_code = db.Column(Text, db.ForeignKey(Region.region_id), index=True, nullable=True)
    name = db.Column(Text, nullable=True)
    description = db.Column(Text, nullable=True)
    num_tracks = db.Column(Integer, nullable=True)
    chart_type = db.Column(Text, nullable=True)
    uri = db.Column(Text, nullable=True)
    dsp_chart_id = db.Column(Text, nullable=True)
    chart_group = db.Column(Text, nullable=False)

    dsp = relationship('Dsp', back_populates='charts')
    chart_ranks = relationship('ChartRank', back_populates='chart')
    chart_rank = relationship('ChartRank', uselist=False,
                              primaryjoin='and_(Chart.chart_id==ChartRank.chart_id,'
                                          'ChartRank.is_latest==True)')

    @hybrid_property
    def rank(self):
        return self.chart_rank.rank if self.chart_rank else None


class ChartRank(BaseModel, TrackableMixin):
    __tablename__ = 'chart_rank'
    chart_rank_id = db.Column(BigInteger, primary_key=True)
    chart_id = db.Column(Text, db.ForeignKey(Chart.chart_id), index=True, nullable=False)
    report_date = db.Column(DATE, nullable=False)
    rank = db.Column(BigInteger, nullable=True)
    dsp_id = db.Column(Text, db.ForeignKey(Dsp.dsp_id), index=False, nullable=False)
    country_code = db.Column(Text, db.ForeignKey(Region.region_id), index=True, nullable=True)
    is_latest = db.Column(Boolean, nullable=False, default=False)

    dsp = relationship('Dsp', back_populates='chart_ranks')
    chart = relationship('Chart', back_populates='chart_ranks')


class TrackFamily(BaseModel, TrackableMixin):
    """This table is not currently used by the API"""
    __tablename__ = 'track_family'
    track_family_id = db.Column(Text, primary_key=True)
    song_id = db.Column(Text, nullable=False)
    track_isrc = db.Column(Text, nullable=False)
    track_name = db.Column(Text, nullable=True)


class Track(BaseModel, TrackableMixin):
    __tablename__ = 'track'
    track_id = db.Column(Text, primary_key=True)
    product_id = db.Column(Text, db.ForeignKey(Product.product_id), nullable=False)
    track_family_id = db.Column(Text, db.ForeignKey(TrackFamily.track_family_id), nullable=True)
    track_no = db.Column(Text, index=True, comment='GRAS:track_no')
    grid_no = db.Column(Text, index=True, comment='GRAS:grid_no')
    track_ext = db.Column(Text, index=False, comment='GRAS:track_ext')
    release_date = db.Column(DATE, nullable=True, comment='GRAS:orig_release_date')
    isrc = db.Column(Text, nullable=True, index=True, comment='GRAS:isrc')
    name = db.Column(Text, nullable=True)
    track_name_suppl = db.Column(Text, nullable=True, comment='GRAS:track_name_suppl')
    spotify_popularity = db.Column(Integer, nullable=True)
    spotify_track_id = db.Column(Text, nullable=True)
    first_recording_date = db.Column(DATE, nullable=True)
    lyrics_version = db.Column(Text, nullable=True)
    sequence_number = db.Column(BigInteger, nullable=True)
    side_no = db.Column(BigInteger, nullable=True)
    is_main_product = db.Column(Boolean, default=False)

    product = relationship('Product', back_populates='tracks')
    artists = relationship('Artist', back_populates='tracks', secondary=artist_track_table)


class Participant(BaseModel, TrackableMixin):
    """This table is not currently used by the API"""
    __tablename__ = 'participant'
    participant_id = db.Column(Text, db.ForeignKey(Artist.artist_id), nullable=False,
                               primary_key=True)
    artist_id = db.Column(Text, db.ForeignKey(Artist.artist_id), nullable=False,
                          primary_key=True)
    participant_name = db.Column(Text, nullable=True)
    membership_type = db.Column(Text, nullable=True)
    seq_no = db.Column(Integer, nullable=True)