using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FrameworkDAL.Query.Comment
{
    public static class CommentQB
    {
        public const string GET_COMMENT = @" 
       SELECT 
  -- Comment
  TC.COMMENTID AS CommentId,
  TC.PARENTCOMMENTID AS ParentCommentId,

  -- User
  TC.USERID AS UserId,
  U.USERCODE AS UserCode,
  U.USERNAME AS UserName,

  TC.TIMESTAMP AS CommentTimeStamp,
  TC.COMMENTSTATUS AS CommentCommentStatus,
  TC.ACCESSTYPE AS CommentAccessType,

  -- Private User Group
  TC.PRIVATEUSERGROUPID AS PrivateUserGroupId,
  PUG.USERGROUPCODE AS PrivateUserGroupCode,
  PUG.USERGROUPNAME AS PrivateUserGroupName,

  -- Object Info
  TC.OBJECTTYPEID AS CommentObjectTypeId,
  TC.OBJECTID AS CommentObjectId,
  TC.COMMENTTEXT AS CommentCommentText,
  TC.SECURITYLEVEL AS CommentSecurityLevel,

  -- Base
  TC.STATUS AS CommentStatus,
  TC.VERSION AS CommentVersion,

  -- Standard
  TC.CREATEDBYID AS CommentCreatedById,
  TC.CREATEDON AS CommentCreatedOn,
  TC.MODIFIEDBYID AS CommentModifiedById,
  TC.MODIFIEDON AS CommentModifiedOn,

  -- BizTransaction
  TC.BIZTRANSACTIONID AS BizTransactionId,

  -- OU
  TC.OUID AS OuId

FROM TCOMMENT TC
LEFT JOIN MUSER U ON TC.USERID = U.USERID
LEFT JOIN MUSERGROUP PUG ON TC.PRIVATEUSERGROUPID = PUG.USERGROUPID
WHERE TC.COMMENTID = @commentid;";
        public const string SAVE_COMMENT = @"
INSERT INTO TCOMMENT 
(
    COMMENTID,
    PARENTCOMMENTID,
    USERID,
    TIMESTAMP,
    COMMENTSTATUS,
    ACCESSTYPE,
    PRIVATEUSERGROUPID,
    OBJECTTYPEID,
    OBJECTID,
    COMMENTTEXT,
    SECURITYLEVEL,
    STATUS,
    VERSION,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    BIZTRANSACTIONID,
    OUID
)
VALUES
(
    @CommentId,
    @ParentCommentId,
    @UserId,
    @CommentTimeStamp,
    @CommentCommentStatus,     
    @CommentAccessType,
    @PrivateUserGroupId,
    @CommentObjectTypeId,
    @CommentObjectId,
    @CommentCommentText,
    @CommentSecurityLevel,
    @CommentStatus,
    @CommentVersion,
    @CommentCreatedById,
    @CommentCreatedOn,
    @CommentModifiedById,       
    @CommentModifiedOn,         
    @BizTransactionId,
    @OuId
);";
        public const string UPDATE_COMMENT = @"
UPDATE TCOMMENT
SET
    PARENTCOMMENTID      = @ParentCommentId,
    USERID              = @UserId,
    TIMESTAMP           = @CommentTimeStamp,
    COMMENTSTATUS       = @CommentCommentStatus,    
    ACCESSTYPE          = @CommentAccessType,
    PRIVATEUSERGROUPID  = @PrivateUserGroupId,
    OBJECTTYPEID        = @CommentObjectTypeId,
    OBJECTID            = @CommentObjectId,
    COMMENTTEXT         = @CommentCommentText,
    SECURITYLEVEL       = @CommentSecurityLevel,
    STATUS              = @CommentStatus,
    VERSION             = @CommentVersion ,     
    MODIFIEDBYID        = @CommentModifiedById,
    MODIFIEDON          = @CommentModifiedOn,
    BIZTRANSACTIONID    = @BizTransactionId,
    OUID                = @OuId
WHERE COMMENTID = @CommentId;";
        public const string DELETE_COMMENT = @"
        DELETE FROM TCOMMENT
        WHERE COMMENTID = @CommentId;";
    }
}