namespace AnalyticsDAL.Query.BIFieldMapping
{
    public static class BIFieldMappingQB
    {
        // Index: IX_MBIFIELDMAPPING_CATALOGID
        public const string GET_DIMENSIONS_BY_CATALOG_ID = @"
            SELECT  f.BIFIELDMAPPINGID     AS BIFieldMappingId,
                    f.BICATALOGID          AS BICatalogId,
                    f.SOURCEFIELDNAME      AS SourceFieldName,
                    f.MAPPEDAS             AS MappedAs,
                    f.LABEL                AS Label,
                    f.DATATYPE             AS DataType,
                    f.DEFAULTAGGREGATION   AS DefaultAggregation,
                    f.ADDITIVITYTYPE       AS AdditivityType,
                    f.SEMIADDITIVEDIMENSION AS SemiAdditiveDimension,
                    f.ISVISIBLE            AS IsVisible
            FROM    MBIFIELDMAPPING f
            WHERE   f.BICATALOGID = @BICatalogId
            AND     f.MAPPEDAS    = 0
            AND     f.STATUS     <> 2
            ORDER BY f.SORTORDER, f.LABEL";

        // Index: IX_MBIFIELDMAPPING_CATALOGID
        public const string GET_MEASURES_BY_CATALOG_ID = @"
            SELECT  f.BIFIELDMAPPINGID     AS BIFieldMappingId,
                    f.BICATALOGID          AS BICatalogId,
                    f.SOURCEFIELDNAME      AS SourceFieldName,
                    f.MAPPEDAS             AS MappedAs,
                    f.LABEL                AS Label,
                    f.DATATYPE             AS DataType,
                    f.DEFAULTAGGREGATION   AS DefaultAggregation,
                    f.ADDITIVITYTYPE       AS AdditivityType,
                    f.SEMIADDITIVEDIMENSION AS SemiAdditiveDimension,
                    f.ISVISIBLE            AS IsVisible
            FROM    MBIFIELDMAPPING f
            WHERE   f.BICATALOGID = @BICatalogId
            AND     f.MAPPEDAS    = 1
            AND     f.STATUS     <> 2
            ORDER BY f.SORTORDER, f.LABEL";

        // Phase 2's SuggestMappings review/save step — every prior MBIFIELDMAPPING row (the
        // ApiService pilot's own three) was seeded manually via raw SQL; this is the first
        // real app-level Insert.
        public const string INSERT_FIELD_MAPPING = @"
            INSERT INTO MBIFIELDMAPPING
                (BIFIELDMAPPINGID, BICATALOGID, SOURCEFIELDNAME, MAPPEDAS, LABEL, DATATYPE,
                 DEFAULTAGGREGATION, ADDITIVITYTYPE, SEMIADDITIVEDIMENSION,
                 ISVISIBLE, SORTORDER, COMPUTEEXPRESSION, VERSION, STATUS,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
            VALUES
                (@BIFieldMappingId, @BICatalogId, @SourceFieldName, @MappedAs, @Label, @DataType,
                 @DefaultAggregation, @AdditivityType, @SemiAdditiveDimension,
                 @IsVisible, @SortOrder, @ComputeExpression, 0, 1,
                 @CreatedById, GETDATE(), @ModifiedById, GETDATE())";

        // Computed/derived fields for a catalog -- MBIFIELDMAPPING rows with MAPPEDAS = 2
        // (Computed), a value that (by construction) is excluded from GET_DIMENSIONS_BY_CATALOG_ID
        // (MAPPEDAS=0) and GET_MEASURES_BY_CATALOG_ID (MAPPEDAS=1) above, so a computed field never
        // leaks into either picker or into ValidateAdditivity's measure/dimension registries.
        // Index: IX_MBIFIELDMAPPING_CATALOGID
        public const string GET_COMPUTED_FIELDS_BY_CATALOG_ID = @"
            SELECT  f.BIFIELDMAPPINGID     AS BIFieldMappingId,
                    f.BICATALOGID          AS BICatalogId,
                    f.SOURCEFIELDNAME      AS SourceFieldName,
                    f.MAPPEDAS             AS MappedAs,
                    f.LABEL                AS Label,
                    f.DATATYPE             AS DataType,
                    f.ISVISIBLE            AS IsVisible,
                    f.COMPUTEEXPRESSION    AS ComputeExpression
            FROM    MBIFIELDMAPPING f
            WHERE   f.BICATALOGID = @BICatalogId
            AND     f.MAPPEDAS    = 2
            AND     f.STATUS     <> 2
            ORDER BY f.SORTORDER, f.LABEL";
    }
}