namespace AccountsDAL.Query.WarehouseMeasure
{
    // CRUD SQL for MWAREHOUSEMEASURE admin endpoints — the same Get/Save/Update/Delete shape as
    // WarehousePostingRuleQB. Separate from AccountsDAL.Query.Warehouse.WarehouseFactPostingQB's
    // read-only GET_POSTING_RULES hot path.
    public static class WarehouseMeasureQB
    {
        public const string GET_WAREHOUSEMEASURE = @"SELECT
                                                    MEASUREID              AS MeasureId,
                                                    FACTID                 AS FactId,
                                                    MEASURECODE            AS MeasureCode,
                                                    MEASURENAME            AS MeasureName,
                                                    COLUMNNAME             AS ColumnName,
                                                    DATATYPE               AS DataType,
                                                    ADDITIVITYTYPE         AS AdditivityType,
                                                    SEMIADDITIVEDIMENSION  AS SemiAdditiveDimension,
                                                    DEFAULTAGGREGATION     AS DefaultAggregation,
                                                    FORMATSTRING           AS FormatString,
                                                    BUSINESSDESCRIPTION    AS BusinessDescription,
                                                    SYNONYMS               AS Synonyms,
                                                    ISVISIBLE              AS IsVisible,
                                                    STATUS                 AS MeasureStatus,
                                                    VERSION                AS MeasureVersion,
                                                    SORTORDER              AS MeasureSortOrder,
                                                    CREATEDBYID            AS MeasureCreatedById,
                                                    CREATEDON              AS MeasureCreatedOn,
                                                    MODIFIEDBYID           AS MeasureModifiedById,
                                                    MODIFIEDON             AS MeasureModifiedOn
                                                FROM MWAREHOUSEMEASURE
                                                WHERE MEASUREID = @measureid;";

        // Full list, filtered by FactId so an admin UI can show "all measures for FFINANCE" rather
        // than the whole table.
        public const string GET_WAREHOUSEMEASURE_LIST = @"SELECT
                                                    MEASUREID              AS MeasureId,
                                                    FACTID                 AS FactId,
                                                    MEASURECODE            AS MeasureCode,
                                                    MEASURENAME            AS MeasureName,
                                                    COLUMNNAME             AS ColumnName,
                                                    DATATYPE               AS DataType,
                                                    ADDITIVITYTYPE         AS AdditivityType,
                                                    SEMIADDITIVEDIMENSION  AS SemiAdditiveDimension,
                                                    DEFAULTAGGREGATION     AS DefaultAggregation,
                                                    FORMATSTRING           AS FormatString,
                                                    BUSINESSDESCRIPTION    AS BusinessDescription,
                                                    SYNONYMS               AS Synonyms,
                                                    ISVISIBLE              AS IsVisible,
                                                    STATUS                 AS MeasureStatus,
                                                    VERSION                AS MeasureVersion,
                                                    SORTORDER              AS MeasureSortOrder,
                                                    CREATEDBYID            AS MeasureCreatedById,
                                                    CREATEDON              AS MeasureCreatedOn,
                                                    MODIFIEDBYID           AS MeasureModifiedById,
                                                    MODIFIEDON             AS MeasureModifiedOn
                                                FROM MWAREHOUSEMEASURE
                                                WHERE (@FactId IS NULL OR FACTID = @FactId)
                                                ORDER BY SORTORDER, MEASUREID;";

        // UK_MWAREHOUSEMEASURE_FACTCOLUMN enforces one row per (FACTID, COLUMNNAME) at the DB
        // level; the BLL checks this first for a clear validation error instead of a raw
        // constraint-violation exception.
        public const string GET_EXISTING_MEASURE_FOR_COLUMN = @"SELECT MEASUREID
                                                FROM MWAREHOUSEMEASURE
                                                WHERE FACTID = @FactId AND COLUMNNAME = @ColumnName;";

        // Confirms COLUMNNAME is an actual physical column on the fact table's DDL before Save —
        // per the guide's "Key BLL rule: COLUMNNAME must already exist as a physical column in the
        // fact table's DDL" (section 09.3). {FactTableName} is resolved from
        // MWAREHOUSEFACT.FACTTABLENAME and validated against ^[A-Z0-9_]+$ by the BLL before
        // substitution here — never raw request input, same discipline as WarehouseFactPostingDAL.
        public const string GET_FACT_TABLE_NAME_FOR_MEASURE_CHECK = @"SELECT FACTTABLENAME
                                                FROM MWAREHOUSEFACT
                                                WHERE FACTID = @FactId AND STATUS <> 2;";

        public const string CHECK_COLUMN_EXISTS_ON_TABLE = @"SELECT COUNT(*)
                                                FROM INFORMATION_SCHEMA.COLUMNS
                                                WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @ColumnName;";

        public const string SAVE_WAREHOUSEMEASURE = @"INSERT INTO MWAREHOUSEMEASURE
                                                        (
                                                            MEASUREID,
                                                            FACTID,
                                                            MEASURECODE,
                                                            MEASURENAME,
                                                            COLUMNNAME,
                                                            DATATYPE,
                                                            ADDITIVITYTYPE,
                                                            SEMIADDITIVEDIMENSION,
                                                            DEFAULTAGGREGATION,
                                                            FORMATSTRING,
                                                            BUSINESSDESCRIPTION,
                                                            SYNONYMS,
                                                            ISVISIBLE,
                                                            STATUS,
                                                            VERSION,
                                                            SORTORDER,
                                                            CREATEDBYID,
                                                            CREATEDON,
                                                            MODIFIEDBYID,
                                                            MODIFIEDON
                                                        )
                                                        VALUES
                                                        (
                                                            @MeasureId,
                                                            @FactId,
                                                            @MeasureCode,
                                                            @MeasureName,
                                                            @ColumnName,
                                                            @DataType,
                                                            @AdditivityType,
                                                            @SemiAdditiveDimension,
                                                            @DefaultAggregation,
                                                            @FormatString,
                                                            @BusinessDescription,
                                                            @Synonyms,
                                                            @IsVisible,
                                                            @MeasureStatus,
                                                            @MeasureVersion,
                                                            @MeasureSortOrder,
                                                            @MeasureCreatedById,
                                                            @MeasureCreatedOn,
                                                            @MeasureModifiedById,
                                                            @MeasureModifiedOn
                                                        );";

        public const string UPDATE_WAREHOUSEMEASURE = @"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,
                                                        STATUS                 = @MeasureStatus,
                                                        VERSION                = @MeasureVersion,
                                                        SORTORDER              = @MeasureSortOrder,
                                                        MODIFIEDBYID           = @MeasureModifiedById,
                                                        MODIFIEDON             = @MeasureModifiedOn
                                                    WHERE
                                                        MEASUREID = @MeasureId;";

        public const string DELETE_WAREHOUSEMEASURE = @"DELETE FROM MWAREHOUSEMEASURE
                                                        WHERE MEASUREID = @measureid;";
    }
}