namespace AnalyticsDAL.Query.AnalysisQuery
{
    public static class AnalysisQueryQB
    {
        // Lightweight lookup for DatasetResolver: just the two columns it needs (query type,
        // to reject DIRECT-type queries per the SqlWorkbench-safe-executor gap; AnalysisId,
        // to resolve MANALYSISWORKSPACE.OVERRIDEDATASOURCEID) — avoids loading/deserializing
        // the full AnalysisQuery JSON payload GET_ANALYSISQUERY_DETAIL returns.
        public const string GET_ANALYSISQUERY_TYPE_AND_ANALYSISID = @"
            SELECT aq.QUERYTYPE   AS QueryType,
                   aq.ANALYSISID  AS AnalysisId
            FROM   MANALYSISQUERY aq
            WHERE  aq.ANALYSISQUERYID = @AnalysisQueryId
            AND    aq.STATUS         <> 2";

        public const string GET_ANALYSISQUERY_DETAIL = @"
            SELECT aq.ANALYSISQUERYID    AS AnalysisQueryId,
                   aq.ANALYSISID         AS AnalysisId,
                   a.ANALYSISCODE        AS AnalysisCode,
                   a.ANALYSISNAME        AS AnalysisName,
                   aq.QUERYNAME          AS AnalysisQueryQueryName,
                   aq.QUERYTYPE          AS AnalysisQueryQueryType,
                   aq.QUERY              AS AnalysisQueryQuery,
                   aq.ORDERBY            AS AnalysisQueryOrderBy,
                   aq.SORTORDER          AS AnalysisQuerySortOrder,
                   aq.STATUS             AS AnalysisQueryStatus,
                   aq.VERSION            AS AnalysisQueryVersion,
                   aq.SOURCETYPE         AS AnalysisQuerySourceType,
                   aq.CREATEDBYID        AS AnalysisQueryCreatedById,
                   aq.CREATEDON          AS AnalysisQueryCreatedOn,
                   aq.MODIFIEDBYID       AS AnalysisQueryModifiedById,
                   aq.MODIFIEDON         AS AnalysisQueryModifiedOn
            FROM   MANALYSISQUERY aq
            JOIN   MANALYSIS a ON a.ANALYSISID = aq.ANALYSISID
            WHERE  aq.ANALYSISQUERYID = @analysisqueryid";

        // Required indexes: IX_MANALYSISQUERY_ANALYSISID(ANALYSISID, STATUS)
        public const string GET_SELECTLIST_ANALYSISQUERY = @"
            SELECT ANALYSISQUERYID AS Id,
                   QUERYNAME       AS QueryName
            FROM   MANALYSISQUERY
            WHERE  ANALYSISID = @analysisid
            AND    STATUS = 1
            ORDER  BY QUERYNAME";

        // Required indexes: IX_MANALYSISQUERYFIELDS_QUERYID(ANALYSISQUERYID, DISPLAYSLNO)
        public const string GET_ANALYSISQUERY_FIELDS_DETAIL = @"
            SELECT qf.ANALYSISQUERYFIELDID           AS AnalysisQueryFieldsId,
                   qf.ANALYSISQUERYID                AS AnalysisQueryId,
                   aq.QUERYNAME                      AS AnalysisQueryName,
                   aq.QUERYTYPE                      AS AnalysisQueryCode,
                   qf.ANALYSISFIELDID                AS AnalysisQueryFieldsAnalysisFieldId,
                   qf.SLNO                           AS AnalysisQueryFieldsSlNo,
                   qf.DISPLAYSLNO                    AS AnalysisQueryFieldsDisplaySlNo,
                   qf.DISPLAYTYPE                    AS AnalysisQueryFieldsDisplayType,
                   qf.FILTERCONDITION                AS AnalysisQueryFieldsFilterCondition,
                   qf.ISFILTER                       AS AnalysisQueryFieldsIsFilter,
                   qf.CRITERIAATTRIBUTEID            AS CriteriaAttributeId,
                   qf.DISPLAYNAME                    AS AnalysisQueryFieldsDisplayName,
                   qf.AGGREGATIONTYPE                AS AnalysisQueryFieldsAggregationType,
                   qf.PERIODTYPE                     AS AnalysisQueryFieldsPeriodtype,
                   c.DBFIELDNAME                     AS AnalysisQueryFieldsDbObjectFieldName,
                   obj.DBOBJECTNAME                  AS AnalysisQueryFieldsDbObjectName,
                   ISNULL(ca.CRITERIAATTRIBUTENAME,'') AS CriteriaAttributeName
            FROM   MANALYSISQUERYFIELDS qf
            JOIN   MANALYSISQUERY aq ON aq.ANALYSISQUERYID = qf.ANALYSISQUERYID
            JOIN   MANALYSISFIELDS af ON af.ANALYSISFIELDID = qf.ANALYSISFIELDID
            JOIN   DBOBJECTFIELDS c   ON c.DBOBJECTFIELDID  = af.DBOBJECTFIELDID
            JOIN   MANALYSISOBJECT ao ON ao.ANALYSISOBJECTID = af.ANALYSISOBJECTID
            JOIN   DBOBJECT obj       ON obj.DBOBJECTID      = ao.DBOBJECTID
            LEFT   JOIN MCRITERIAATTRIBUTE ca ON ca.CRITERIAATTRIBUTEID = qf.CRITERIAATTRIBUTEID
            WHERE  qf.ANALYSISQUERYID = @analysisqueryid
            ORDER  BY qf.DISPLAYSLNO ASC";

        // ── BI engine's dimension/measure picker (BICatalogBLL's AnalysisQuery branch) ──────
        // Distinct from GET_ANALYSISQUERY_FIELDS_DETAIL above (the legacy designer's own, much
        // wider shape) — just Field/Label/RawDataType/Aggregation, classified by DISPLAYTYPE
        // the same way the designer itself assigns zones when saving
        // (analysisquerydesigner.component.ts's ZONE_DISPLAY_TYPES: HORIZONTAL=0,VERTICAL=1,
        // INFO=2,SUMMARY=3,COMPARISON=5 — FILTER shares DisplayType=0 with HORIZONTAL,
        // distinguished only by ISFILTER=1, hence excluding ISFILTER=1 explicitly below rather
        // than relying on DISPLAYTYPE alone). COMPARISON(5) is excluded from both — not a
        // dimension or a measure for picker purposes, per the plan's v1 scope.
        // Required indexes: IX_MANALYSISQUERYFIELDS_QUERYID(ANALYSISQUERYID, DISPLAYSLNO)
        public const string GET_BI_DIMENSIONS_FOR_QUERY = @"
            SELECT  c.DBFIELDNAME       AS Field,
                    qf.DISPLAYNAME      AS Label,
                    c.DBFIELDATATYPE    AS RawDataType
            FROM    MANALYSISQUERYFIELDS qf
            JOIN    MANALYSISFIELDS af  ON af.ANALYSISFIELDID = qf.ANALYSISFIELDID
            JOIN    DBOBJECTFIELDS c    ON c.DBOBJECTFIELDID  = af.DBOBJECTFIELDID
            WHERE   qf.ANALYSISQUERYID = @AnalysisQueryId
            AND     qf.ISFILTER        = 0
            AND     qf.DISPLAYTYPE     IN (0, 1, 2)
            ORDER   BY qf.DISPLAYSLNO";

        public const string GET_BI_MEASURES_FOR_QUERY = @"
            SELECT  c.DBFIELDNAME       AS Field,
                    qf.DISPLAYNAME      AS Label,
                    c.DBFIELDATATYPE    AS RawDataType,
                    qf.AGGREGATIONTYPE  AS Aggregation
            FROM    MANALYSISQUERYFIELDS qf
            JOIN    MANALYSISFIELDS af  ON af.ANALYSISFIELDID = qf.ANALYSISFIELDID
            JOIN    DBOBJECTFIELDS c    ON c.DBOBJECTFIELDID  = af.DBOBJECTFIELDID
            WHERE   qf.ANALYSISQUERYID = @AnalysisQueryId
            AND     qf.ISFILTER        = 0
            AND     qf.DISPLAYTYPE     = 3
            ORDER   BY qf.DISPLAYSLNO";

        // Palette of all available analysis fields for the designer — not yet in this query
        // Required indexes: IX_MANALYSISFIELDS_ANALYSISID_STATUS(ANALYSISID, STATUS, SORTORDER)
        public const string GET_ANALYSISFIELDS_FOR_DESIGNER = @"
            SELECT af.ANALYSISFIELDID   AS Id,
                   af.DISPLAYNAME       AS DisplayName,
                   c.DBFIELDNAME        AS DbFieldName,
                   obj.DBOBJECTNAME     AS DbObjectName,
                   af.FILTEREXPRESSION  AS Filterexpression,
                   aqf.FILTERCONDITION  AS FilterCondition
            FROM   MANALYSISFIELDS af
            JOIN   DBOBJECTFIELDS c   ON c.DBOBJECTFIELDID  = af.DBOBJECTFIELDID
            JOIN   MANALYSISOBJECT ao ON ao.ANALYSISOBJECTID = af.ANALYSISOBJECTID
            JOIN   DBOBJECT obj       ON obj.DBOBJECTID      = ao.DBOBJECTID
            LEFT JOIN MANALYSISQUERYFIELDS aqf
                   ON  aqf.ANALYSISFIELDID = af.ANALYSISFIELDID
                   AND aqf.ANALYSISQUERYID = @AnalysisQueryId
            WHERE  af.ANALYSISID = @analysisid
            AND    af.STATUS = 1
            ORDER  BY af.SORTORDER, af.DISPLAYNAME";

        public const string SAVE_ANALYSISQUERY = @" INSERT INTO MANALYSISQUERY
                                                        (
                                                            ANALYSISQUERYID,
                                                            ANALYSISID,
                                                            QUERYNAME,
                                                            QUERYTYPE,
                                                            QUERY,
                                                            VERSION,
                                                            STATUS,
                                                            SOURCETYPE,
                                                            SORTORDER,
                                                            CREATEDBYID,
                                                            CREATEDON,
                                                            MODIFIEDBYID,
                                                            MODIFIEDON,
                                                            ORDERBY,
                                                            TENANTID,
                                                            WORKSPACEID
                                                        )
                                                        VALUES
                                                        (
                                                            @AnalysisQueryId,
                                                            @AnalysisId,
                                                            @AnalysisQueryQueryName,
                                                            @AnalysisQueryQueryType,
                                                            @AnalysisQueryQuery,
                                                            @AnalysisQueryVersion,
                                                            @AnalysisQueryStatus,
                                                            @AnalysisQuerySourceType,
                                                            @AnalysisQuerySortOrder,
                                                            @AnalysisQueryCreatedById,
                                                            GETDATE(),
                                                            @AnalysisQueryModifiedById,
                                                            GETDATE(),
                                                            @AnalysisQueryOrderBy,
                                                            @AnalysisQueryTenantId,
                                                            @AnalysisQueryWorkspaceId
                                                        );";

        public const string UPDATE_ANALYSISQUERY = @"UPDATE MANALYSISQUERY
                                                    SET
                                                        QUERYNAME    = @AnalysisQueryQueryName,
                                                        QUERYTYPE    = @AnalysisQueryQueryType,
                                                        QUERY        = @AnalysisQueryQuery,
                                                        ORDERBY      = @AnalysisQueryOrderBy,
                                                        VERSION      = @AnalysisQueryVersion,
                                                        SORTORDER    = @AnalysisQuerySortOrder,
                                                        STATUS       = @AnalysisQueryStatus,
                                                        MODIFIEDBYID = @AnalysisQueryModifiedById,
                                                        MODIFIEDON   = GETUTCDATE()
                                                    WHERE ANALYSISQUERYID = @AnalysisQueryId;";

        public const string DELETE_ANALYSISQUERY = @"UPDATE MANALYSISQUERY
                                                SET    STATUS = 2,
                                                       MODIFIEDBYID = @ModifiedById,
                                                       MODIFIEDON   = GETUTCDATE()
                                                WHERE  ANALYSISQUERYID = @AnalysisQueryId";

        // Delete-insert pattern for fields: wipe existing then re-insert all
        public const string DELETE_ANALYSISQUERY_FIELDS = @"DELETE FROM MANALYSISQUERYFIELDS
                                                            WHERE  ANALYSISQUERYID = @analysisqueryid";

        public const string SAVE_ANALYSISQUERYFIELD = @"INSERT INTO MANALYSISQUERYFIELDS
                                                        (
                                                            ANALYSISQUERYFIELDID,
                                                            ANALYSISQUERYID,
                                                            SLNO,
                                                            DISPLAYSLNO,
                                                            DISPLAYTYPE,
                                                            FILTERCONDITION,
                                                            ANALYSISFIELDID,
                                                            ISFILTER,
                                                            CRITERIAATTRIBUTEID,
                                                            DISPLAYNAME,
                                                            AGGREGATIONTYPE,
                                                            PERIODTYPE
                                                        )
                                                        VALUES
                                                        (
                                                            @AnalysisQueryFieldsId,
                                                            @AnalysisQueryId,
                                                            @AnalysisQueryFieldsSlNo,
                                                            @AnalysisQueryFieldsDisplaySlNo,
                                                            @AnalysisQueryFieldsDisplayType,
                                                            @AnalysisQueryFieldsFilterCondition,
                                                            @AnalysisQueryFieldsAnalysisFieldId,
                                                            @AnalysisQueryFieldsIsFilter,
                                                            @CriteriaAttributeId,
                                                            @AnalysisQueryFieldsDisplayName,
                                                            @AnalysisQueryFieldsAggregationType,
                                                            @AnalysisQueryFieldsPeriodtype
                                                        );";
    }
}