namespace GB5Shared.Signatory.Query
{
    public static class SignatoryQB
    {
        // Covering index: IX_MSIGNATORY_MODULEID_TENANTID (MODULEID, TENANTID) WHERE ISACTIVE = 1
        public const string GET_SIGNATORY = @"
            SELECT
                S.SIGNATORYID                  AS SignatoryId,
                S.MODULEID                     AS ModuleId,
                M.MODULECODE                   AS ModuleCode,
                M.MODULENAME                   AS ModuleName,
                S.SIGNATORYNAME                AS SignatoryName,
                S.SIGNATORYDESIGNATION         AS SignatoryDesignation,
                S.EMPLOYEEID                   AS EmployeeId,
                S.SIGNATUREIMAGESTORAGEPATH    AS SignatureImageStoragePath,
                S.PFXTHUMBPRINT                AS PfxThumbprint,
                S.ISACTIVE                     AS IsActive,
                S.VERSION                      AS Version,
                S.STATUS                       AS Status,
                S.SORTORDER                    AS SortOrder,
                S.SOURCETYPE                   AS SourceType,
                S.CREATEDBYID                  AS CreatedById,
                S.CREATEDON                    AS CreatedOn,
                S.MODIFIEDBYID                 AS ModifiedById,
                S.MODIFIEDON                   AS ModifiedOn,
                S.TENANTID                     AS TenantId
            FROM   DBO.MSIGNATORY S
            JOIN   DBO.MMODULE M ON M.MODULEID = S.MODULEID
            WHERE  S.SIGNATORYID = @signatoryid
            AND    S.TENANTID    = @tenantid;";

        public const string GET_ACTIVE_SIGNATORY_BY_MODULE = @"
            SELECT
                S.SIGNATORYID                  AS SignatoryId,
                S.MODULEID                     AS ModuleId,
                M.MODULECODE                   AS ModuleCode,
                M.MODULENAME                   AS ModuleName,
                S.SIGNATORYNAME                AS SignatoryName,
                S.SIGNATORYDESIGNATION         AS SignatoryDesignation,
                S.EMPLOYEEID                   AS EmployeeId,
                S.SIGNATUREIMAGESTORAGEPATH    AS SignatureImageStoragePath,
                S.PFXTHUMBPRINT                AS PfxThumbprint,
                S.ISACTIVE                     AS IsActive,
                S.VERSION                      AS Version,
                S.STATUS                       AS Status,
                S.SORTORDER                    AS SortOrder,
                S.SOURCETYPE                   AS SourceType,
                S.CREATEDBYID                  AS CreatedById,
                S.CREATEDON                    AS CreatedOn,
                S.MODIFIEDBYID                 AS ModifiedById,
                S.MODIFIEDON                   AS ModifiedOn,
                S.TENANTID                     AS TenantId
            FROM   DBO.MSIGNATORY S
            JOIN   DBO.MMODULE M ON M.MODULEID = S.MODULEID
            WHERE  S.MODULEID  = @moduleid
            AND    S.ISACTIVE  = 1
            AND    S.STATUS    = 1
            AND    S.TENANTID  = @tenantid
            ORDER  BY S.SORTORDER ASC;";

        public const string GET_SELECTLIST_SIGNATORY = @"
            SELECT
                S.SIGNATORYID   AS Id,
                M.MODULECODE   AS Code,
                S.SIGNATORYNAME AS Name
            FROM   DBO.MSIGNATORY S
            JOIN   DBO.MMODULE M ON M.MODULEID = S.MODULEID
            WHERE  S.ISACTIVE = 1
            AND    S.STATUS   = 1
            AND    S.TENANTID = @tenantid
            ORDER  BY S.SORTORDER ASC;";

        public const string SAVE_SIGNATORY = @"
            INSERT INTO DBO.MSIGNATORY
            (
                SIGNATORYID, MODULEID, SIGNATORYNAME, SIGNATORYDESIGNATION, EMPLOYEEID,
                SIGNATUREIMAGESTORAGEPATH, PFXTHUMBPRINT, ISACTIVE,
                VERSION, STATUS, SORTORDER, SOURCETYPE,
                CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TENANTID
            )
            VALUES
            (
                @SignatoryId, @ModuleId, @SignatoryName, @SignatoryDesignation, @EmployeeId,
                @SignatureImageStoragePath, @PfxThumbprint, @IsActive,
                @Version, @Status, @SortOrder, @SourceType,
                @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @TenantId
            );";

        public const string UPDATE_SIGNATORY = @"
            UPDATE DBO.MSIGNATORY
            SET    MODULEID                  = @ModuleId,
                   SIGNATORYNAME              = @SignatoryName,
                   SIGNATORYDESIGNATION       = @SignatoryDesignation,
                   EMPLOYEEID                 = @EmployeeId,
                   SIGNATUREIMAGESTORAGEPATH  = @SignatureImageStoragePath,
                   PFXTHUMBPRINT              = @PfxThumbprint,
                   ISACTIVE                   = @IsActive,
                   VERSION                    = @Version,
                   STATUS                     = @Status,
                   SORTORDER                  = @SortOrder,
                   MODIFIEDBYID               = @ModifiedById,
                   MODIFIEDON                 = @ModifiedOn
            WHERE  SIGNATORYID = @SignatoryId
            AND    TENANTID    = @TenantId;";

        public const string DELETE_SIGNATORY = @"
            DELETE FROM DBO.MSIGNATORY
            WHERE  SIGNATORYID = @signatoryid
            AND    TENANTID    = @tenantid;";
    }
}