namespace FrameworkDAL.Query.Promotion
{
    public static class PromotionCounterpartyQB
    {
        // Required index: PK on ENVIRONMENTCODE already covers all lookups below (small table,
        // an admin registry, not a high-volume transactional one).

        public const string GET_BY_ENVIRONMENT_CODE = @"
            SELECT ENVIRONMENTCODE AS EnvironmentCode,
                   DISPLAYNAME     AS DisplayName,
                   PUBLICKEYPEM    AS PublicKeyPem,
                   ISACTIVE        AS IsActive,
                   CREATEDBYID     AS CreatedById,
                   CREATEDON       AS CreatedOn,
                   MODIFIEDBYID    AS ModifiedById,
                   MODIFIEDON      AS ModifiedOn
            FROM   MPROMOTIONCOUNTERPARTY
            WHERE  ENVIRONMENTCODE = @EnvironmentCode
            AND    ISACTIVE        = 1";

        // Admin edit-load — unlike GET_BY_ENVIRONMENT_CODE above (used by the crypto service to
        // resolve an ACTIVE counterparty only), this must also surface inactive rows so an admin
        // can open and re-activate one.
        public const string GET_BY_ENVIRONMENT_CODE_FOR_ADMIN = @"
            SELECT ENVIRONMENTCODE AS EnvironmentCode,
                   DISPLAYNAME     AS DisplayName,
                   PUBLICKEYPEM    AS PublicKeyPem,
                   ISACTIVE        AS IsActive,
                   CREATEDBYID     AS CreatedById,
                   CREATEDON       AS CreatedOn,
                   MODIFIEDBYID    AS ModifiedById,
                   MODIFIEDON      AS ModifiedOn
            FROM   MPROMOTIONCOUNTERPARTY
            WHERE  ENVIRONMENTCODE = @EnvironmentCode";

        public const string GET_LIST = @"
            SELECT ENVIRONMENTCODE AS EnvironmentCode,
                   DISPLAYNAME     AS DisplayName,
                   PUBLICKEYPEM    AS PublicKeyPem,
                   ISACTIVE        AS IsActive,
                   CREATEDBYID     AS CreatedById,
                   CREATEDON       AS CreatedOn,
                   MODIFIEDBYID    AS ModifiedById,
                   MODIFIEDON      AS ModifiedOn
            FROM   MPROMOTIONCOUNTERPARTY
            ORDER  BY ENVIRONMENTCODE";

        public const string GET_SELECTLIST = @"
            SELECT ENVIRONMENTCODE AS EnvironmentCode,
                   DISPLAYNAME     AS DisplayName
            FROM   MPROMOTIONCOUNTERPARTY
            WHERE  ISACTIVE = 1
            ORDER  BY DISPLAYNAME";

        public const string CHECK_EXISTS = @"
            SELECT COUNT(1) FROM MPROMOTIONCOUNTERPARTY WHERE ENVIRONMENTCODE = @EnvironmentCode";

        public const string INSERT = @"
            INSERT INTO MPROMOTIONCOUNTERPARTY
                (ENVIRONMENTCODE, DISPLAYNAME, PUBLICKEYPEM, ISACTIVE,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
            VALUES
                (@EnvironmentCode, @DisplayName, @PublicKeyPem, @IsActive,
                 @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn)";

        public const string UPDATE = @"
            UPDATE MPROMOTIONCOUNTERPARTY
            SET    DISPLAYNAME  = @DisplayName,
                   PUBLICKEYPEM = @PublicKeyPem,
                   ISACTIVE     = @IsActive,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = @ModifiedOn
            WHERE  ENVIRONMENTCODE = @EnvironmentCode";

        public const string DELETE = @"
            DELETE FROM MPROMOTIONCOUNTERPARTY WHERE ENVIRONMENTCODE = @EnvironmentCode";
    }
}