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


/**
 * Class TB_Shared_Ent_TeamData_Notification
 *
 * @property integer $id
 * @property integer|null $creator_id
 * @property integer $recipient_id
 * @property integer|null $conversation_id
 * @property string $subject
 * @property string $message
 * @property int $is_read
 * @property DateTime $last_updated_dt
 * @property DateTime $created_dt
 *
 */
class TB_Shared_Ent_TeamData_Message extends Francis_Db_Row
{
    /**
     * @param $profileId
     * @return array
     */
    public static function getForProfileId( $profileId )
    {
        $sql  = 'SELECT p.first_name AS first_name, p.last_name AS last_name, p.pic_url AS pic_url, ';
        $sql .= 'm.* FROM ' . self::getTable() . ' AS m ';
        $sql .= 'LEFT JOIN ' . TB_Shared_Ent_TeamData_Profile::getTable() . ' AS p ';
        $sql .= 'ON p.id = m.creator_id ';
        $sql .= 'WHERE recipient_id = :recipient_id ';
        $sql .= 'ORDER BY created_dt DESC';

        $dbh = self::getDbh();
        $stmt = $dbh->prepare( $sql );
        $stmt->execute( array(
            ':recipient_id' => $profileId
        ));
        $res = $stmt->fetchAll( PDO::FETCH_ASSOC );
        return $res;
    }

    /**
     * @param $profileId
     * @param $messageId
     * @return mixed
     */
    public static function getForProfileIdById( $profileId, $messageId )
    {
        $sql  = 'SELECT p.first_name AS first_name, p.last_name AS last_name, p.pic_url AS pic_url, ';
        $sql .= 'm.* FROM ' . self::getTable() . ' AS m ';
        $sql .= 'LEFT JOIN ' . TB_Shared_Ent_TeamData_Profile::getTable() . ' AS p ';
        $sql .= 'ON p.id = m.creator_id ';
        $sql .= 'WHERE recipient_id = :recipient_id ';
        $sql .= 'AND m.id = :message_id ';
        $sql .= 'ORDER BY created_dt DESC ';
        $sql .= 'LIMIT 1 ';

        $dbh = self::getDbh();
        $stmt = $dbh->prepare( $sql );
        $stmt->execute( array(
            ':recipient_id' => $profileId,
            ':message_id' => $messageId
        ));
        $res = $stmt->fetch( PDO::FETCH_ASSOC );
        return $res;
    }

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

    }

    /**
     * @param $teamId
     * @param $tsLastNewsRead
     */
    public static function getNewsCountForProfileId( TB_Shared_Ent_TeamData_Profile $profile, TB_Shared_Ent_TeamData_ProfileTimestamp $ts ) {
        return 0;
    }

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

    /**
     * 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 'message';
    }

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