namespace AccountsDAL.Query.Warehouse
{
    // SQL for warehouse admin CRUD — covers MWAREHOUSEFACT, MWAREHOUSEMEASURE,
    // MWAREHOUSEMEASUREPOSTINGRULE, and the DIMOU ETL populate.
    // Indexes: IX_MWAREHOUSEMEASURE_FACTID already present (Analytics_Phase1A migration).
    // All writes include MODIFIEDON=GETDATE() and VERSION increment where applicable.
    public static class WarehouseAdminQB
    {
        // ─── MWAREHOUSEFACT ──────────────────────────────────────────────────

        public const string GET_WAREHOUSE_FACTS = @"
            SELECT  F.FACTID,
                    F.FACTCODE,
                    F.FACTNAME,
                    F.FACTTABLENAME,
                    F.GRAINDESCRIPTION,
                    F.DATEDIMCOLUMN,
                    F.OUDIMCOLUMN,
                    F.BUSINESSDESCRIPTION,
                    F.SYNONYMS,
                    F.SORTORDER,
                    F.SOURCETYPE,
                    F.TENANTID,
                    F.CREATEDBYID,
                    F.CREATEDON,
                    F.MODIFIEDBYID,
                    F.MODIFIEDON,
                    F.STATUS
            FROM    MWAREHOUSEFACT F
            WHERE   F.STATUS <> 2
            ORDER   BY F.SORTORDER, F.FACTCODE";

        public const string INSERT_WAREHOUSE_FACT = @"
            INSERT INTO MWAREHOUSEFACT
                (FACTID, FACTCODE, FACTNAME, FACTTABLENAME, GRAINDESCRIPTION,
                 DATEDIMCOLUMN, OUDIMCOLUMN, BUSINESSDESCRIPTION, SYNONYMS,
                 SORTORDER, SOURCETYPE, TENANTID,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, STATUS, VERSION)
            VALUES
                (@FactId, @FactCode, @FactName, @FactTableName, @GrainDescription,
                 @DateDimColumn, @OuDimColumn, @BusinessDescription, @Synonyms,
                 @SortOrder, @SourceType, @TenantId,
                 @CreatedById, GETDATE(), @ModifiedById, GETDATE(), 1, 0)";

        public const string UPDATE_WAREHOUSE_FACT = @"
            UPDATE  MWAREHOUSEFACT
            SET     FACTNAME            = @FactName,
                    FACTTABLENAME       = @FactTableName,
                    GRAINDESCRIPTION    = @GrainDescription,
                    DATEDIMCOLUMN       = @DateDimColumn,
                    OUDIMCOLUMN         = @OuDimColumn,
                    BUSINESSDESCRIPTION = @BusinessDescription,
                    SYNONYMS            = @Synonyms,
                    SORTORDER           = @SortOrder,
                    MODIFIEDBYID        = @ModifiedById,
                    MODIFIEDON          = GETDATE(),
                    VERSION             = VERSION + 1
            WHERE   FACTID = @FactId";

        public const string DELETE_WAREHOUSE_FACT = @"
            UPDATE  MWAREHOUSEFACT
            SET     STATUS       = 2,
                    MODIFIEDBYID = @ModifiedById,
                    MODIFIEDON   = GETDATE()
            WHERE   FACTID = @FactId";

        // ─── MWAREHOUSEMEASURE ───────────────────────────────────────────────

        public const string GET_WAREHOUSE_MEASURES_BY_FACT = @"
            SELECT  M.MEASUREID,
                    M.FACTID,
                    M.MEASURECODE,
                    M.MEASURENAME,
                    M.COLUMNNAME,
                    M.DATATYPE,
                    M.ADDITIVITYTYPE,
                    M.SEMIADDITIVEDIMENSION,
                    M.DEFAULTAGGREGATION,
                    M.FORMATSTRING,
                    M.BUSINESSDESCRIPTION,
                    M.SYNONYMS,
                    M.ISVISIBLE,
                    M.SORTORDER,
                    M.CREATEDBYID,
                    M.CREATEDON,
                    M.MODIFIEDBYID,
                    M.MODIFIEDON,
                    M.STATUS
            FROM    MWAREHOUSEMEASURE M
            WHERE   M.FACTID  = @FactId
            AND     M.STATUS  <> 2
            ORDER   BY M.SORTORDER, M.MEASURECODE";

        public const string INSERT_WAREHOUSE_MEASURE = @"
            INSERT INTO MWAREHOUSEMEASURE
                (MEASUREID, FACTID, MEASURECODE, MEASURENAME, COLUMNNAME,
                 DATATYPE, ADDITIVITYTYPE, SEMIADDITIVEDIMENSION, DEFAULTAGGREGATION,
                 FORMATSTRING, BUSINESSDESCRIPTION, SYNONYMS, ISVISIBLE, SORTORDER,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, STATUS, VERSION)
            VALUES
                (@MeasureId, @FactId, @MeasureCode, @MeasureName, @ColumnName,
                 @DataType, @AdditivityType, @SemiAdditiveDimension, @DefaultAggregation,
                 @FormatString, @BusinessDescription, @Synonyms, @IsVisible, @SortOrder,
                 @CreatedById, GETDATE(), @ModifiedById, GETDATE(), 1, 0)";

        public const string UPDATE_WAREHOUSE_MEASURE = @"
            UPDATE  MWAREHOUSEMEASURE
            SET     MEASURECODE             = @MeasureCode,
                    MEASURENAME             = @MeasureName,
                    COLUMNNAME              = @ColumnName,
                    DATATYPE                = @DataType,
                    ADDITIVITYTYPE          = @AdditivityType,
                    SEMIADDITIVEDIMENSION   = @SemiAdditiveDimension,
                    DEFAULTAGGREGATION      = @DefaultAggregation,
                    FORMATSTRING            = @FormatString,
                    BUSINESSDESCRIPTION     = @BusinessDescription,
                    SYNONYMS                = @Synonyms,
                    ISVISIBLE               = @IsVisible,
                    SORTORDER               = @SortOrder,
                    MODIFIEDBYID            = @ModifiedById,
                    MODIFIEDON              = GETDATE(),
                    VERSION                 = VERSION + 1
            WHERE   MEASUREID = @MeasureId";

        public const string DELETE_WAREHOUSE_MEASURE = @"
            UPDATE  MWAREHOUSEMEASURE
            SET     STATUS       = 2,
                    MODIFIEDBYID = @ModifiedById,
                    MODIFIEDON   = GETDATE()
            WHERE   MEASUREID = @MeasureId";

        // ─── MWAREHOUSEMEASUREPOSTINGRULE ────────────────────────────────────

        public const string GET_WAREHOUSE_POSTING_RULES_BY_FACT = @"
            SELECT  R.POSTINGRULEID,
                    R.MEASUREID,
                    M.MEASURECODE,
                    M.MEASURENAME,
                    R.SOURCETYPE,
                    R.NATURECOLUMNTYPE,
                    R.NATUREVALUES,
                    R.ACCOUNTTYPEFILTER,
                    R.AGGREGATIONMODE,
                    R.SIGNCONVENTION,
                    R.FORMULAEXPRESSION,
                    R.ISACTIVE,
                    R.VERSION,
                    R.STATUS,
                    R.CREATEDBYID,
                    R.CREATEDON,
                    R.MODIFIEDBYID,
                    R.MODIFIEDON
            FROM    MWAREHOUSEMEASUREPOSTINGRULE R
            INNER   JOIN MWAREHOUSEMEASURE M ON R.MEASUREID = M.MEASUREID
            INNER   JOIN MWAREHOUSEFACT    F ON M.FACTID    = F.FACTID
            WHERE   F.FACTID  = @FactId
            AND     R.STATUS  <> 2
            ORDER   BY M.SORTORDER, M.MEASURECODE";

        public const string INSERT_WAREHOUSE_POSTING_RULE = @"
            INSERT INTO MWAREHOUSEMEASUREPOSTINGRULE
                (POSTINGRULEID, MEASUREID, SOURCETYPE,
                 NATURECOLUMNTYPE, NATUREVALUES, ACCOUNTTYPEFILTER,
                 AGGREGATIONMODE, SIGNCONVENTION, FORMULAEXPRESSION,
                 ISACTIVE, VERSION, STATUS,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
            VALUES
                (@PostingRuleId, @MeasureId, @SourceType,
                 @NatureColumnType, @NatureValues, @AccountTypeFilter,
                 @AggregationMode, @SignConvention, @FormulaExpression,
                 @IsActive, 0, 1,
                 @CreatedById, GETDATE(), @ModifiedById, GETDATE())";

        public const string UPDATE_WAREHOUSE_POSTING_RULE = @"
            UPDATE  MWAREHOUSEMEASUREPOSTINGRULE
            SET     SOURCETYPE        = @SourceType,
                    NATURECOLUMNTYPE  = @NatureColumnType,
                    NATUREVALUES      = @NatureValues,
                    ACCOUNTTYPEFILTER = @AccountTypeFilter,
                    AGGREGATIONMODE   = @AggregationMode,
                    SIGNCONVENTION    = @SignConvention,
                    FORMULAEXPRESSION = @FormulaExpression,
                    ISACTIVE          = @IsActive,
                    MODIFIEDBYID      = @ModifiedById,
                    MODIFIEDON        = GETDATE(),
                    VERSION           = VERSION + 1
            WHERE   POSTINGRULEID = @PostingRuleId";

        public const string DELETE_WAREHOUSE_POSTING_RULE = @"
            UPDATE  MWAREHOUSEMEASUREPOSTINGRULE
            SET     STATUS       = 2,
                    MODIFIEDBYID = @ModifiedById,
                    MODIFIEDON   = GETDATE()
            WHERE   POSTINGRULEID = @PostingRuleId";

        // ─── DIMOU ETL populate ───────────────────────────────────────────────
        // MERGE returns @@ROWCOUNT = INSERT + UPDATE rows affected.
        // REGIONID/REGIONCODE/REGIONNAME come from MSTATE (region ≡ state in GB5).
        // ISNULL guards cover OUs without a DEFAULTADDRESSID or a partial address.
        public const string POPULATE_DIM_OU = @"
            MERGE DIMOU AS TARGET
            USING (
                SELECT  OU.OUID,
                        OU.OUCODE,
                        OU.OUNAME,
                        OU.COMPANYID,
                        ISNULL(C.COMPANYCODE,  '')  AS COMPANYCODE,
                        ISNULL(C.COMPANYNAME,  '')  AS COMPANYNAME,
                        OU.BRANCHID,
                        ISNULL(B.BRANCHCODE,   '')  AS BRANCHCODE,
                        ISNULL(B.BRANCHNAME,   '')  AS BRANCHNAME,
                        OU.DIVISIONID,
                        ISNULL(D.DIVISIONCODE, '')  AS DIVISIONCODE,
                        ISNULL(D.DIVISIONNAME, '')  AS DIVISIONNAME,
                        ISNULL(CI.CITYID,       0)  AS CITYID,
                        ISNULL(CI.CITYCODE,    '')  AS CITYCODE,
                        ISNULL(CI.CITYNAME,    '')  AS CITYNAME,
                        ISNULL(S.STATEID,       0)  AS REGIONID,
                        ISNULL(S.STATECODE,    '')  AS REGIONCODE,
                        ISNULL(S.STATENAME,    '')  AS REGIONNAME,
                        ISNULL(CO.COUNTRYID,    0)  AS COUNTRYID,
                        ISNULL(CO.COUNTRYCODE, '')  AS COUNTRYCODE,
                        ISNULL(CO.COUNTRYNAME, '')  AS COUNTRYNAME
                FROM    MORGANIZATIONUNIT OU
                LEFT    JOIN MCOMPANY  C  ON OU.COMPANYID       = C.COMPANYID
                LEFT    JOIN MBRANCH   B  ON OU.BRANCHID        = B.BRANCHID
                LEFT    JOIN MDIVISION D  ON OU.DIVISIONID      = D.DIVISIONID
                LEFT    JOIN MADDRESS  A  ON OU.DEFAULTADDRESSID = A.ADDRESSID
                LEFT    JOIN MCITY     CI ON A.CITYID           = CI.CITYID
                LEFT    JOIN MSTATE    S  ON A.STATEID          = S.STATEID
                LEFT    JOIN MCOUNTRY  CO ON A.COUNTRYID        = CO.COUNTRYID
                WHERE   OU.STATUS <> 2
            ) AS SOURCE ON TARGET.OUID = SOURCE.OUID
            WHEN MATCHED THEN
                UPDATE SET
                    TARGET.OUCODE       = SOURCE.OUCODE,
                    TARGET.OUNAME       = SOURCE.OUNAME,
                    TARGET.COMPANYID    = SOURCE.COMPANYID,
                    TARGET.COMPANYCODE  = SOURCE.COMPANYCODE,
                    TARGET.COMPANYNAME  = SOURCE.COMPANYNAME,
                    TARGET.BRANCHID     = SOURCE.BRANCHID,
                    TARGET.BRANCHCODE   = SOURCE.BRANCHCODE,
                    TARGET.BRANCHNAME   = SOURCE.BRANCHNAME,
                    TARGET.DIVISIONID   = SOURCE.DIVISIONID,
                    TARGET.DIVISIONCODE = SOURCE.DIVISIONCODE,
                    TARGET.DIVISIONNAME = SOURCE.DIVISIONNAME,
                    TARGET.CITYID       = SOURCE.CITYID,
                    TARGET.CITYCODE     = SOURCE.CITYCODE,
                    TARGET.CITYNAME     = SOURCE.CITYNAME,
                    TARGET.REGIONID     = SOURCE.REGIONID,
                    TARGET.REGIONCODE   = SOURCE.REGIONCODE,
                    TARGET.REGIONNAME   = SOURCE.REGIONNAME,
                    TARGET.COUNTRYID    = SOURCE.COUNTRYID,
                    TARGET.COUNTRYCODE  = SOURCE.COUNTRYCODE,
                    TARGET.COUNTRYNAME  = SOURCE.COUNTRYNAME,
                    TARGET.MODIFIEDON   = GETDATE()
            WHEN NOT MATCHED BY TARGET THEN
                INSERT  (OUID, OUCODE, OUNAME,
                         COMPANYID,  COMPANYCODE,  COMPANYNAME,
                         BRANCHID,   BRANCHCODE,   BRANCHNAME,
                         DIVISIONID, DIVISIONCODE, DIVISIONNAME,
                         CITYID,     CITYCODE,     CITYNAME,
                         REGIONID,   REGIONCODE,   REGIONNAME,
                         COUNTRYID,  COUNTRYCODE,  COUNTRYNAME,
                         MODIFIEDON)
                VALUES  (SOURCE.OUID, SOURCE.OUCODE, SOURCE.OUNAME,
                         SOURCE.COMPANYID,  SOURCE.COMPANYCODE,  SOURCE.COMPANYNAME,
                         SOURCE.BRANCHID,   SOURCE.BRANCHCODE,   SOURCE.BRANCHNAME,
                         SOURCE.DIVISIONID, SOURCE.DIVISIONCODE, SOURCE.DIVISIONNAME,
                         SOURCE.CITYID,     SOURCE.CITYCODE,     SOURCE.CITYNAME,
                         SOURCE.REGIONID,   SOURCE.REGIONCODE,   SOURCE.REGIONNAME,
                         SOURCE.COUNTRYID,  SOURCE.COUNTRYCODE,  SOURCE.COUNTRYNAME,
                         GETDATE());";
    }
}