<?php
/********************************************************************************
 * (c)1337 aheadware.com - All rights reserved
 ********************************************************************************/


/**
 * Class TB_Shared_Ent_TeamData_Notification
 *
 * @property integer $id
 * @property string|null $ref_id
 * @property integer $receiver_profile_id
 * @property integer|null $creator_profile_id
 * @property string|null $creator_name
 * @property string|null $headline
 * @property string|null $message
 * @property array|null $additional_data_js
 * @property int $is_read
 * @property DateTime $last_updated_dt
 * @property DateTime $created_dt
 *
 */
class TB_Shared_Ent_TeamData_Notification extends Francis_Db_Row
{
    /**
     * @param $profileId
     * @return array
     */
    public static function getForProfileId( $profileId )
    {
        $sql  = 'SELECT * FROM ' . self::getTable() . ' WHERE receiver_profile_id = :receiver_profile_id ';
        $sql .= 'ORDER BY created_dt DESC';

        return self::findMany( $sql, array( ':receiver_profile_id' => $profileId) );
    }

    /**
     * @param $profileId
     * @return bool
     */
    public static function markAllAsReadForProfileId( $profileId )
    {
        $sql = 'UPDATE ' . self::getTable() . ' SET is_read=1 WHERE receiver_profile_id = :profile_id';
        return self::execute( $sql, array( ':profile_id' => $profileId ) );

    }

    /**
     * @param $profileId
     * @return bool
     */
    public static function markAllAsReadForProfileIdAndRefId( $profileId, $refId )
    {
        $sql  = 'UPDATE ' . self::getTable() . ' SET is_read=1 WHERE ';
        $sql .= 'receiver_profile_id = :profile_id AND ref_id = :ref_id ';
        return self::execute( $sql, array( ':profile_id' => $profileId, ':ref_id' => $refId  ) );

    }

    /**
     * @param $profileId
     * @param $refId
     * @return mixed
     */
    public static function hasProfileIdUnreadOfRefId( $profileId, $refId )
    {
        $dbh = self::getDbh();
        $sql  = 'SELECT 1 FROM ' . self::getTable() . ' WHERE ';
        $sql .= 'ref_id = :ref_id AND ';
        $sql .= 'receiver_profile_id = :receiver_profile_id AND ';
        $sql .= 'is_read = 0';
        $stmt = $dbh->prepare( $sql );
        $stmt->execute( array(
            ':receiver_profile_id' => $profileId,
            ':ref_id' => $refId
        ));
        $res = $stmt->fetch();
        return $res;
    }

    /**
     * @param $teamId
     * @param $tsLastNewsRead
     */
    public static function getNewsCountForProfileId( TB_Shared_Ent_TeamData_Profile $profile, TB_Shared_Ent_TeamData_ProfileTimestamp $ts ) {
        $dbh = self::getDbh();
        $sql  = 'SELECT COUNT(*) AS cnt FROM ' . self::getTable() . ' WHERE ( team_id = :team_id OR ISNULL(team_id) ) ';
        $sql .= 'AND created > :tsLastNewsRead AND author_profile_id != :profile_id';
        $stmt = $dbh->prepare( $sql );
        $stmt->execute( array(
            ':team_id' => $profile->team_id,
            ':tsLastNewsRead' => $ts->last_news_update,
            ':profile_id' => $profile->id
        ));
        $res = $stmt->fetch( PDO::FETCH_ASSOC );
        if ( is_array ( $res ) && isset ( $res[ 'cnt' ] ) ) {
            return (int)$res[ 'cnt' ];
        } else {
            return 0;
        }
    }

    /**
     * Delete all profile related news entries
     *
     * @param int $profileId
     * @return bool
     */
    public static function deleteByAuthorProfileId( $profileId ) {
        $sql = 'DELETE FROM ' . self::getTable() . ' WHERE creator_profile_id = :profile_id';
        return self::execute( $sql, array( ':profile_id' => $profileId ) );
    }

    public static function deleteByReceiverProfileId( $profileId ) {
        $sql = 'DELETE FROM ' . self::getTable() . ' WHERE receiver_profile_id = :profile_id';
        return self::execute( $sql, array( ':profile_id' => $profileId ) );
    }

    /**
     * @param DateTime $deleteOffsetDt
     * @return bool
     */
    public static function cleanUp( \DateTime $deleteOffsetDt)
    {
        $sql = 'DELETE FROM ' . self::getTable() . ' WHERE created_dt < :delete_offset_time' ;
        return self::execute( $sql, array( ':delete_offset_time' => $deleteOffsetDt->format( 'Y-m-d H:i:s' ) ) );
    }

    /**
     * Delete all entries for given team id
     * @param int $teamId
     * @return bool
     */
    public static function deleteByTeamId( $teamId ) {
        $sql = 'DELETE FROM ' . self::getTable() . ' WHERE team_id  = :team_id';
        return self::execute( $sql, array( ':team_id' => $teamId) );
    }

    /**
     * Return table name
     *
     * @return null|string
     */
    public static function getTable()
    {
        return 'notification';
    }

    /**
     * Return db handler
     *
     * @return Francis_Db_MySql|null
     */
    public static function getDbh()
    {
        return TB_Shared_Db_TeamData::get();
    }
}