models.py

#
# Aura Engine API (https://gitlab.servus.at/aura/engine-api)
#
# Copyright (C) 2020 - The Aura Engine Team.
#
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU Affero General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU Affero General Public License for more details.
#
# You should have received a copy of the GNU Affero General Public License
# along with this program.  If not, see <http://www.gnu.org/licenses/>.


import datetime
import json

from flask_marshmallow import Marshmallow
from flask_sqlalchemy import SQLAlchemy
from marshmallow import Schema, fields, post_dump
from sqlalchemy import Boolean, Column, DateTime, Integer, String, create_engine
from sqlalchemy.event import listen

db = SQLAlchemy()
ma = Marshmallow()


class PlayLog(db.Model):
    """
    Table holding play-log entries.
    """

    __tablename__ = "playlog"

    # Primary Key
    track_start = Column(DateTime, primary_key=True)

    # Columns
    track_artist = Column(String(256))
    track_album = Column(String(256))
    track_title = Column(String(256))
    track_duration = Column(Integer)
    track_type = Column(Integer)
    track_num = Column(Integer)
    playlist_id = Column(Integer)
    timeslot_id = Column(Integer)
    show_id = Column(Integer)
    show_name = Column(String(256))
    log_source = Column(
        Integer
    )  # The play-out source which this log is coming from (e.g. engine1, engine2)
    is_synced = Column(Boolean)  # Only relevant for main nodes, in a multi-node setup

    def __init__(self, data):
        """
        Initializes a trackservice entry
        """
        self.track_start = data.track_start
        self.track_artist = data.track_artist
        self.track_album = data.track_album
        self.track_title = data.track_title
        self.track_duration = data.track_duration
        self.track_type = data.track_type
        self.track_num = data.track_num
        self.playlist_id = data.playlist_id
        self.timeslot_id = data.timeslot_id
        self.show_id = data.show_id
        self.show_name = data.show_name
        self.log_source = data.log_source
        self.is_synced = False
        if not self.track_duration:
            self.track_duration = 0

    def save(self):
        db.session.add(self)
        db.session.commit()

    @staticmethod
    def get(start_time):
        """
        Selects the playlog identified by start time.
        """
        db.session.commit()
        track = (
            db.session.query(PlayLog)
            .filter(PlayLog.track_start <= str(start_time))
            .order_by(PlayLog.track_start.desc())
            .first()
        )
        return track

    @staticmethod
    def select_recent():
        """
        Selects the most recent played track. Equals to the current track if it's still playing.
        """
        db.session.commit()
        now = datetime.datetime.now()

        track = (
            db.session.query(PlayLog)
            .order_by(PlayLog.track_start.desc())
            .filter(PlayLog.track_start <= str(now))
            .first()
        )

        return track

    @staticmethod
    def select_current():
        """
        Selects the currently playing track.
        """
        db.session.commit()
        now = datetime.datetime.now()

        track = PlayLog.select_recent()

        if track:
            # Station fallback may not provide a duration. Take this one, it's better than nothing.
            if track.track_duration == 0 or not track.track_duration:
                return track

            # Preferably only get playlogs which are known for still being on air
            if track.track_start + datetime.timedelta(0, track.track_duration) > now:
                return track

        return None

    @staticmethod
    def select_for_timeslot(timeslot_id):
        """
        Selects all playlogs which appear to be in the current timeslot.
        If playlogs without a valid timeslot are queried (-1), then only
        the last 50 total playlogs are respected.
        """
        db.session.commit()
        now = datetime.datetime.now()
        before12h = datetime.datetime.now() - datetime.timedelta(hours=12)
        playlogs = None

        # Invalid Timeslot ID
        if timeslot_id == -1:
            playlogs = []
            result = (
                db.session.query(PlayLog)
                .order_by(PlayLog.track_start.desc())
                .filter(PlayLog.track_start >= str(before12h))
                .limit(50)
            )
            for playlog in result.all():
                if playlog.timeslot_id != -1:
                    break
                playlogs.append(playlog)

        # Valid Timeslot ID
        else:
            result = (
                db.session.query(PlayLog)
                .order_by(PlayLog.track_start.desc())
                .filter(PlayLog.timeslot_id == timeslot_id)
            )
            playlogs = result.all()

        return playlogs

    @staticmethod
    def paginate(page, page_size, from_time=None, to_time=None, skip_synced=False):
        """
        Returns a list of entries for a given page and an start time (optional).
        """
        if not from_time:
            from_time = datetime.datetime.utcnow().date()
        if not to_time:
            to_time = datetime.datetime.now()

        def q(page=0, page_size=None):
            query = db.session.query(PlayLog).order_by(PlayLog.track_start.desc())
            if isinstance(from_time, datetime.datetime):
                query = query.filter(
                    PlayLog.track_start >= from_time.isoformat(" ", "seconds")
                )
            if isinstance(to_time, datetime.datetime):
                query = query.filter(
                    PlayLog.track_start <= to_time.isoformat(" ", "seconds")
                )
            if skip_synced == True:
                query = query.filter(PlayLog.is_synced == False)
            listen(query, "before_compile", apply_limit(page, page_size), retval=True)
            print("Paginate Query: " + str(query))
            return query

        def apply_limit(page, page_size):
            def wrapped(query):
                if page_size:
                    query = query.limit(page_size)
                    if page:
                        query = query.offset(page * page_size)
                return query

            return wrapped

        return q(page, page_size)

    @staticmethod
    def select_last_hours(n):
        """
        Selects the tracks playing in the past (`n`) hours.
        """
        db.session.commit()
        last_hours = datetime.datetime.today() - datetime.timedelta(hours=n)
        tracks = (
            db.session.query(PlayLog)
            .filter(PlayLog.track_start >= str(last_hours))
            .order_by(PlayLog.track_start.desc())
            .all()
        )
        return tracks

    @staticmethod
    def select_by_day(day):
        """
        Select the track-service items for a day.
        """
        db.session.commit()
        day_plus_one = day + datetime.timedelta(days=1)
        tracks = (
            db.session.query(PlayLog)
            .filter(
                PlayLog.track_start >= str(day), PlayLog.track_start < str(day_plus_one)
            )
            .order_by(PlayLog.track_start.desc())
            .all()
        )
        return tracks

    @staticmethod
    def select_by_range(from_day, to_day):
        """
        Selects the track-service items for a day range.
        """
        db.session.commit()
        tracks = (
            db.session.query(PlayLog)
            .filter(
                PlayLog.track_start >= str(from_day), PlayLog.track_start < str(to_day)
            )
            .order_by(PlayLog.track_start.desc())
            .all()
        )
        return tracks

    def __str__(self):
        return "Track [track_start: %s, track_title: %s]" % (
            str(self.track_start),
            str(self.track_title),
        )


class PlayLogSchema(ma.SQLAlchemyAutoSchema):
    """
    Schema for playlog entries.
    """

    class Meta:
        model = PlayLog
        sqla_session = db.session

    SKIP_VALUES = set([None])

    @post_dump
    def remove_skip_values(self, data, many=False):
        return {
            key: value for key, value in data.items() if value not in self.SKIP_VALUES
        }


class TrackSchema(ma.SQLAlchemySchema):
    """
    Schema for trackservice entries.
    """

    class Meta:
        model = PlayLog
        sqla_session = db.session
        fields = (
            "track_start",
            "track_artist",
            "track_album",
            "track_title",
            "track_duration",
            "track_type",
            "track_num",
            "playlist_id",
            "timeslot_id",
            "show_id",
            "show_name",
        )


class ActivityLog(db.Model):
    """
    Table holding a log of play-out source activity and their sync states.

    Only used in "SYNC" deployment mode.
    """

    __tablename__ = "activity_log"

    # Primary Key
    log_time = Column(DateTime, primary_key=True)

    # Columns
    source_number = Column(Integer)
    is_synced = Column(Boolean)

    def __init__(self, source_number):
        """
        Initializes an activity entry
        """
        self.log_time = datetime.datetime.now()
        self.source_number = source_number
        self.is_synced = False

    @staticmethod
    def is_empty():
        """
        Checks if the tables is empty.
        """
        db.session.commit()
        return not db.session.query(ActivityLog).one_or_none()

    @staticmethod
    def get_active_source():
        """
        Retrieves the currently active source.
        """
        db.session.commit()
        source = (
            db.session.query(ActivityLog).order_by(ActivityLog.log_time.desc()).first()
        )
        return source

    def save(self):
        db.session.add(self)
        db.session.commit()


class HealthHistory(db.Model):
    """
    Table holding an history of health information for sources.
    """

    __tablename__ = "health_history"

    # Primary Key
    log_time = Column(DateTime, primary_key=True)

    # Columns
    log_source = Column(Integer)  # The source the history entry relates to
    is_healthy = Column(
        Boolean
    )  # Indicates if source is "healthy enough" to be used for play-out
    is_synced = Column(Boolean)  # Only relevant for main nodes, in a multi-node setup
    health_info = Column(String(4096))  # Stringified JSON object or other, if needed

    def __init__(self, source_number, log_time, is_healthy, health_info):
        """
        Initializes an health entry.
        """
        self.log_time = log_time
        self.log_source = source_number
        self.is_healthy = is_healthy
        self.is_synced = False
        self.health_info = health_info

    @staticmethod
    def get_latest_entry(source_number):
        """
        Retrieves the most recent health history entry for the given source number.
        """
        return (
            db.session.query(HealthHistory)
            .filter(HealthHistory.log_source == source_number)
            .order_by(HealthHistory.log_time.desc())
            .first()
        )

    def save(self):
        db.session.add(self)
        db.session.commit()


class HealthHistorySchema(ma.SQLAlchemyAutoSchema):
    """
    Schema for health history entries.
    """

    class Meta:
        model = HealthHistory
        sqla_session = db.session


class ClockInfo(db.Model):
    """
    Table holding information for the current and next show to be displayed by the studio clock.

    Important: This table doesn't hold the history of information for efficiency. It only stores one
    record for each possible source. For example in a redundant deployment it holds two records, for
    engine1 and engine2.

    The stringified objects allow easy, future extension of the properties, without the need to change
    the database model.
    """

    __tablename__ = "clock_info"

    # Primary Key
    log_source = Column(
        Integer, primary_key=True
    )  # The source this entry was updated from ("1" for engine1, "2" for engine2)

    # Columns
    log_time = Column(DateTime)
    current_track = None  # Populated live from within `get_info(..)`
    planned_playlist = Column(
        String(4096)
    )  # Stringified "#/components/schemas/Playlist" OpenAPI JSON object
    current_timeslot = Column(
        String(2048)
    )  # Stringified "#/components/schemas/Timeslot" OpenAPI JSON object
    next_timeslot = Column(
        String(2048)
    )  # Stringified "#/components/schemas/Timeslot" OpenAPI JSON object

    def __init__(self):
        """
        Initializes an clock info entry.
        """

    def set_info(
        self, source_number, planned_playlist, current_timeslot, next_timeslot
    ):
        """
        Sets the values for a clock info entry.
        """
        self.log_time = datetime.datetime.now()
        self.log_source = source_number
        if planned_playlist:
            self.planned_playlist = json.dumps(planned_playlist.to_dict(), default=str)
        else:
            self.planned_playlist = None
        if current_timeslot:
            self.current_timeslot = json.dumps(current_timeslot.to_dict(), default=str)
        else:
            self.current_timeslot = None
        if next_timeslot:
            self.next_timeslot = json.dumps(next_timeslot.to_dict(), default=str)
        else:
            self.next_timeslot = None

    @staticmethod
    def get(source_number):
        """
        Retrieves the clock info for the given source number.
        """
        return (
            db.session.query(ClockInfo)
            .filter(ClockInfo.log_source == source_number)
            .first()
        )

    @staticmethod
    def get_info(source_number):
        """
        Retrieves the clock info for the given source number.
        """
        track_schema = TrackSchema()
        info = dict()
        data = (
            db.session.query(ClockInfo)
            .filter(ClockInfo.log_source == source_number)
            .first()
        )
        current_track = PlayLog.select_current()
        planned_playlist_id = -1
        playlogs = None

        # Construct the clock `info` object
        if data:
            info["log_source"] = data.log_source
            info["log_time"] = data.log_time

            # Get the track currently playing
            if current_track:
                info["current_track"] = track_schema.dump(current_track)

            # Append the missing planned playlist items to the ones played
            if data.planned_playlist:
                info["planned_playlist"] = json.loads(data.planned_playlist)
            else:
                info["planned_playlist"] = {}

            # Get the current timeslot
            if data.current_timeslot:
                info["current_timeslot"] = json.loads(data.current_timeslot)

                # Get the most recently played track (because right now nothing might be playing)
                most_recent_track = PlayLog.select_recent()

                # Is the most recent track part of the current timeslot?
                if (
                    most_recent_track.timeslot_id
                    == info["current_timeslot"]["timeslot_id"]
                ):

                    # Get the actual playlogs of the current timeslot, until now
                    playlog_schema = PlayLogSchema(many=True)
                    playlogs = PlayLog.select_for_timeslot(
                        most_recent_track.timeslot_id
                    )
                    playlogs.sort(key=lambda track: track.track_start, reverse=False)
                    info["current_playlogs"] = playlog_schema.dump(playlogs)
                    if info["current_playlogs"] == None:
                        info["current_playlogs"] = {}

                    # Invalid timeslots (e.g. in fallback scenarios) get a virtual start date of the first fallback track
                    if info["current_timeslot"]["timeslot_id"] == -1:
                        if playlogs and playlogs[0]:
                            info["current_timeslot"]["timeslot_start"] = playlogs[
                                0
                            ].track_start

            # Get the next timeslot
            if data.next_timeslot:
                info["next_timeslot"] = json.loads(data.next_timeslot)
            else:
                info["next_timeslot"] = {}

        return info

    def save(self):
        db.session.add(self)
        db.session.commit()

    def update(self):
        db.session.merge(self)
        db.session.commit()


class ClockInfoSchema(ma.SQLAlchemySchema):
    """
    Schema for trackservice entries.
    """

    class Meta:
        model = ClockInfo
        sqla_session = db.session
        fields = (
            "log_source",
            "log_time",
            "current_track",
            "planned_playlist",
            "current_playlogs",
            "current_timeslot",
            "next_timeslot",
        )