namespace AnalyticsDAL.Query.BIView
{
    public static class BIViewQB
    {
        // Unscoped by TenantId in the WHERE clause (only PK) — same rationale as
        // BICatalogQB.GET_CATALOG_ENTRY_BY_ID: the caller must be able to see a shared
        // (TENANTID = -1) view before comparing it against the caller's tenant in code.
        // Index: PK_MBIVIEW_BIVIEWID
        public const string GET_BIVIEW_BY_ID = @"
            SELECT  v.BIVIEWID           AS BIViewId,
                    v.BICATALOGID        AS BICatalogId,
                    v.BIVIEWCODE         AS BIViewCode,
                    v.BIVIEWNAME         AS BIViewName,
                    v.WIDGETTYPE         AS WidgetType,
                    v.BIDEFINITIONJSON   AS BIDefinitionJson,
                    v.ADAPTERCONFIGJSON  AS AdapterConfigJson,
                    v.SOURCETYPE         AS SourceType,
                    v.TENANTID           AS TenantId,
                    v.VERSION            AS Version,
                    v.STATUS             AS Status,
                    v.SORTORDER          AS SortOrder,
                    v.CREATEDBYID        AS CreatedById,
                    v.CREATEDON          AS CreatedOn,
                    v.MODIFIEDBYID       AS ModifiedById,
                    v.MODIFIEDON         AS ModifiedOn
            FROM    MBIVIEW v
            WHERE   v.BIVIEWID = @BIViewId
            AND     v.STATUS  <> 2";

        // List of saved views for one dataset — the Portlet Config UI's "pick or create a
        // BIView" step (Phase 3). Index: IX_MBIVIEW_BICATALOGID
        public const string GET_SELECTLIST_BIVIEW_BY_CATALOG_ID = @"
            SELECT  v.BIVIEWID           AS BIViewId,
                    v.BICATALOGID        AS BICatalogId,
                    v.BIVIEWCODE         AS BIViewCode,
                    v.BIVIEWNAME         AS BIViewName,
                    v.WIDGETTYPE         AS WidgetType,
                    v.BIDEFINITIONJSON   AS BIDefinitionJson,
                    v.ADAPTERCONFIGJSON  AS AdapterConfigJson,
                    v.SOURCETYPE         AS SourceType,
                    v.TENANTID           AS TenantId,
                    v.VERSION            AS Version,
                    v.STATUS             AS Status,
                    v.SORTORDER          AS SortOrder,
                    v.CREATEDBYID        AS CreatedById,
                    v.CREATEDON          AS CreatedOn,
                    v.MODIFIEDBYID       AS ModifiedById,
                    v.MODIFIEDON         AS ModifiedOn
            FROM    MBIVIEW v
            WHERE   v.BICATALOGID = @BICatalogId
            AND     v.TENANTID   IN (@TenantId, -1)
            AND     v.STATUS    <> 2
            ORDER BY v.SORTORDER, v.BIVIEWNAME";

        public const string INSERT_BIVIEW = @"
            INSERT INTO MBIVIEW
                (BIVIEWID, BICATALOGID, BIVIEWCODE, BIVIEWNAME, WIDGETTYPE,
                 BIDEFINITIONJSON, ADAPTERCONFIGJSON,
                 SOURCETYPE, TENANTID, VERSION, STATUS, SORTORDER,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
            VALUES
                (@BIViewId, @BICatalogId, @BIViewCode, @BIViewName, @WidgetType,
                 @BIDefinitionJson, @AdapterConfigJson,
                 @SourceType, @TenantId, 0, 1, @SortOrder,
                 @CreatedById, GETDATE(), @ModifiedById, GETDATE())";

        public const string UPDATE_BIVIEW = @"
            UPDATE MBIVIEW
            SET    BIVIEWCODE        = @BIViewCode,
                   BIVIEWNAME        = @BIViewName,
                   WIDGETTYPE        = @WidgetType,
                   BIDEFINITIONJSON  = @BIDefinitionJson,
                   ADAPTERCONFIGJSON = @AdapterConfigJson,
                   SORTORDER         = @SortOrder,
                   VERSION           = VERSION + 1,
                   MODIFIEDBYID      = @ModifiedById,
                   MODIFIEDON        = GETDATE()
            WHERE  BIVIEWID          = @BIViewId
            AND    TENANTID          = @TenantId";

        public const string DELETE_BIVIEW = @"
            UPDATE MBIVIEW
            SET    STATUS       = 2,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = GETDATE()
            WHERE  BIVIEWID     = @BIViewId
            AND    TENANTID     = @TenantId";
    }
}