namespace PartnerDAL.Query.PartnerTerm
{
    public static class PartnerTermQB
    {
        // Index: IX_TPARTNERTERM_PRODUCT_LANG on (PARTNERPRODUCTID, LANGUAGEID) INCLUDE (TERMKEY, TERMVALUE, STATUS)
        public const string GET_TERMINOLOGY_BUNDLE = @"
            SELECT PT.TERMKEY   AS TermKey,
                   PT.TERMVALUE AS TermValue
            FROM   TPARTNERTERM PT
            INNER JOIN MCLIENTDETAILS CD ON CD.PARTNERPRODUCTID = PT.PARTNERPRODUCTID
            WHERE  CD.CLIENTID   = @ClientId
              AND  PT.LANGUAGEID = @LanguageId
              AND  PT.STATUS     = 1;";

        public const string GET_PARTNER_TERM = @"
            SELECT PARTNERTERMID    AS PartnerTermId,
                   PARTNERPRODUCTID AS PartnerProductId,
                   TERMKEY          AS TermKey,
                   TERMVALUE        AS TermValue,
                   LANGUAGEID       AS LanguageId,
                   STATUS           AS Status,
                   CREATEDBYID      AS CreatedById,
                   CREATEDON        AS CreatedOn,
                   MODIFIEDBYID     AS ModifiedById,
                   MODIFIEDON       AS ModifiedOn
            FROM   TPARTNERTERM
            WHERE  PARTNERPRODUCTID = @PartnerProductId
              AND  LANGUAGEID       = @LanguageId
              AND  STATUS           = 1
            ORDER BY TERMKEY;";

        public const string SAVE_PARTNER_TERM = @"
            INSERT INTO TPARTNERTERM
                (PARTNERPRODUCTID, TERMKEY, TERMVALUE, LANGUAGEID, STATUS,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
            VALUES
                (@PartnerProductId, @TermKey, @TermValue, @LanguageId, @Status,
                 @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn);
            SELECT SCOPE_IDENTITY();";

        public const string UPDATE_PARTNER_TERM = @"
            UPDATE TPARTNERTERM
            SET    TERMVALUE    = @TermValue,
                   LANGUAGEID   = @LanguageId,
                   STATUS       = @Status,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = @ModifiedOn
            WHERE  PARTNERTERMID = @PartnerTermId;";

        public const string DELETE_PARTNER_TERM = @"
            DELETE FROM TPARTNERTERM
            WHERE  PARTNERTERMID = @PartnerTermId;";
    }
}