SQL query: a stored procedure that receive user ProfileId and get table messages contact list

I need a help with a complicated select query.I have message table

CREATE TABLE Message(
    MessageId int IDENTITY(1,1) NOT NULL, /*primary key*/
    FromProfileId int NOT NULL, /*foreign key to Profile table*/
    ToProfileId int NOT NULL, /*foreign key to Profile table*/
    Datetime datetime NOT NULL,
    MessageText ntext NOT NULL,
    IsNew bit NOT NULL )

I need a stored procedure that receive user ProfileId and get table messages contact list, something like this:

• ContactProfileId (ProfileId that user has sent message or has received message),
• AllMessagesCount (all sent/received messages count to/from ContactProfileId),
• NewMessagesCount (new messages count received from ContactProfileId),
• LastMessageDateTime (last message datetime from/to ContactProfileId),
• some ContactProfileId profile information joined from Profile table.

Any ideas how can I achieve that result?
Updated:Here Is the example of some data

MessageId FromProfileId ToProfileId messageDatetime          IsNew
--------------------------------------------------------------------
1         2              3          2010-11-20 18:16:40.230  1
2         2              3          2010-12-20 18:16:40.230  1
3         3              2          2010-10-20 18:16:40.230  0
4         3              4          2010-12-25 18:16:40.230  1

result of SP when parameter ProfileId=3 must be this

ContactProfileId  AllMessagesCount  NewMessagesCount  LastMessageDateTime      ContactName
---------------------------------------------------------------------------------------------------------------------
2                  3                2                2010-12-20 18:16:40.230   Contact Name joined from Profile Table 
4                  1                0                2010-12-25 18:16:40.230   Contact Name joined from Profile Table

Answer

SELECT m.*, p.Name AS ContactName
FROM (
  SELECT
    ContactProfileId,
    AllMessagesCount = COUNT(*),
    NewMessagesCount = COUNT(CASE IsNew WHEN 1 THEN 1 END),
    LastMessageDateTime = MAX(messageDateTime)
  FROM (
    SELECT
      ContactProfileId =
        CASE ToProfileId
          WHEN @ProfileId THEN FromProfileId
          ELSE ToProfileId
        END)
      messageDateTime,
      IsNew
    FROM Message
    WHERE @ProfileId IN (FromProfileId, ToProfileId)
  ) m
  GROUP BY ContactProfileId
) m
  INNER JOIN Profile p ON m.ContactProfileId = p.Id

Or, using CTEs:

WITH filteredmessages AS (
  SELECT
    ContactProfileId =
      CASE ToProfileId
        WHEN @ProfileId THEN FromProfileId
        ELSE ToProfileId
      END)
    messageDateTime,
    IsNew
  FROM Message
  WHERE @ProfileId IN (FromProfileId, ToProfileId)
),

groupedmessages AS (
  SELECT
    ContactProfileId,
    AllMessagesCount = COUNT(*),
    NewMessagesCount = COUNT(CASE IsNew WHEN 1 THEN 1 END),
    LastMessageDateTime = MAX(messageDateTime)
  FROM filteredmessages
  GROUP BY ContactProfileId
)

SELECT m.*, p.Name AS ContactName
FROM groupedmessages m
  INNER JOIN Profile p ON m.ContactProfileId = p.Id