namespace AccountsDAL.Query.WarehousePostingRule
{
    // CRUD SQL for MWAREHOUSEMEASUREPOSTINGRULE admin endpoints. Separate from
    // AccountsDAL.Query.Warehouse.WarehouseFactPostingQB, which holds the posting engine's own
    // read-only GET_POSTING_RULES query (joined, batched, hot-path) — this file is the
    // admin-maintenance side (Get single / Save / Delete), following the same
    // Get/Save/Update/Delete shape as CostCenterTypeQB.
    public static class WarehousePostingRuleQB
    {
        public const string GET_WAREHOUSEPOSTINGRULE = @"SELECT
                                                    R.POSTINGRULEID       AS PostingRuleId,
                                                    R.MEASUREID           AS MeasureId,
                                                    M.MEASURECODE         AS MeasureCode,
                                                    R.SOURCETYPE          AS SourceType,
                                                    R.NATURECOLUMNTYPE    AS NatureColumnType,
                                                    R.NATUREVALUES        AS NatureValues,
                                                    R.ACCOUNTTYPEFILTER   AS AccountTypeFilter,
                                                    R.AGGREGATIONMODE     AS AggregationMode,
                                                    R.SIGNCONVENTION      AS SignConvention,
                                                    R.FORMULAEXPRESSION   AS FormulaExpression,
                                                    R.ISACTIVE            AS IsActive,
                                                    R.STATUS              AS PostingRuleStatus,
                                                    R.VERSION             AS PostingRuleVersion,
                                                    R.CREATEDBYID         AS PostingRuleCreatedById,
                                                    R.CREATEDON           AS PostingRuleCreatedOn,
                                                    R.MODIFIEDBYID        AS PostingRuleModifiedById,
                                                    R.MODIFIEDON          AS PostingRuleModifiedOn
                                                FROM MWAREHOUSEMEASUREPOSTINGRULE R
                                                INNER JOIN MWAREHOUSEMEASURE M ON R.MEASUREID = M.MEASUREID
                                                WHERE R.POSTINGRULEID = @postingruleid;";

        // Full list, filtered by FactId (via the MEASUREID -> MWAREHOUSEMEASURE -> FACTID chain) so
        // an admin UI can show "all posting rules for FFINANCE" rather than the whole table.
        public const string GET_WAREHOUSEPOSTINGRULE_LIST = @"SELECT
                                                    R.POSTINGRULEID       AS PostingRuleId,
                                                    R.MEASUREID           AS MeasureId,
                                                    M.MEASURECODE         AS MeasureCode,
                                                    R.SOURCETYPE          AS SourceType,
                                                    R.NATURECOLUMNTYPE    AS NatureColumnType,
                                                    R.NATUREVALUES        AS NatureValues,
                                                    R.ACCOUNTTYPEFILTER   AS AccountTypeFilter,
                                                    R.AGGREGATIONMODE     AS AggregationMode,
                                                    R.SIGNCONVENTION      AS SignConvention,
                                                    R.FORMULAEXPRESSION   AS FormulaExpression,
                                                    R.ISACTIVE            AS IsActive,
                                                    R.STATUS              AS PostingRuleStatus,
                                                    R.VERSION             AS PostingRuleVersion,
                                                    R.CREATEDBYID         AS PostingRuleCreatedById,
                                                    R.CREATEDON           AS PostingRuleCreatedOn,
                                                    R.MODIFIEDBYID        AS PostingRuleModifiedById,
                                                    R.MODIFIEDON          AS PostingRuleModifiedOn
                                                FROM MWAREHOUSEMEASUREPOSTINGRULE R
                                                INNER JOIN MWAREHOUSEMEASURE M ON R.MEASUREID = M.MEASUREID
                                                WHERE (@FactId IS NULL OR M.FACTID = @FactId)
                                                ORDER BY R.POSTINGRULEID;";

        // One rule per MEASUREID — the UK_MWHMEASUREPOSTRULE_MEASUREID constraint enforces this at
        // the DB level; the BLL checks this first to surface a clear validation error instead of a
        // raw constraint-violation exception.
        public const string GET_EXISTING_RULE_FOR_MEASURE = @"SELECT POSTINGRULEID
                                                FROM MWAREHOUSEMEASUREPOSTINGRULE
                                                WHERE MEASUREID = @MeasureId;";

        // Every MEASURECODE registered for the fact that owns @MeasureId — used by the BLL to
        // validate every FORMULAEXPRESSION token resolves to a real, same-fact measure before Save,
        // per the PDF's rule-load-time validation requirement (never a runtime surprise).
        public const string GET_MEASURECODES_FOR_SAME_FACT = @"SELECT M2.MEASURECODE
                                                FROM MWAREHOUSEMEASURE M1
                                                INNER JOIN MWAREHOUSEMEASURE M2 ON M2.FACTID = M1.FACTID
                                                WHERE M1.MEASUREID = @MeasureId;";

        public const string SAVE_WAREHOUSEPOSTINGRULE = @"INSERT INTO MWAREHOUSEMEASUREPOSTINGRULE
                                                        (
                                                            POSTINGRULEID,
                                                            MEASUREID,
                                                            SOURCETYPE,
                                                            NATURECOLUMNTYPE,
                                                            NATUREVALUES,
                                                            ACCOUNTTYPEFILTER,
                                                            AGGREGATIONMODE,
                                                            SIGNCONVENTION,
                                                            FORMULAEXPRESSION,
                                                            ISACTIVE,
                                                            STATUS,
                                                            VERSION,
                                                            CREATEDBYID,
                                                            CREATEDON,
                                                            MODIFIEDBYID,
                                                            MODIFIEDON
                                                        )
                                                        VALUES
                                                        (
                                                            @PostingRuleId,
                                                            @MeasureId,
                                                            @SourceType,
                                                            @NatureColumnType,
                                                            @NatureValues,
                                                            @AccountTypeFilter,
                                                            @AggregationMode,
                                                            @SignConvention,
                                                            @FormulaExpression,
                                                            @IsActive,
                                                            @PostingRuleStatus,
                                                            @PostingRuleVersion,
                                                            @PostingRuleCreatedById,
                                                            @PostingRuleCreatedOn,
                                                            @PostingRuleModifiedById,
                                                            @PostingRuleModifiedOn
                                                        );";

        public const string UPDATE_WAREHOUSEPOSTINGRULE = @"UPDATE MWAREHOUSEMEASUREPOSTINGRULE
                                                    SET
                                                        SOURCETYPE          = @SourceType,
                                                        NATURECOLUMNTYPE    = @NatureColumnType,
                                                        NATUREVALUES        = @NatureValues,
                                                        ACCOUNTTYPEFILTER   = @AccountTypeFilter,
                                                        AGGREGATIONMODE     = @AggregationMode,
                                                        SIGNCONVENTION      = @SignConvention,
                                                        FORMULAEXPRESSION   = @FormulaExpression,
                                                        ISACTIVE            = @IsActive,
                                                        STATUS              = @PostingRuleStatus,
                                                        VERSION             = @PostingRuleVersion,
                                                        MODIFIEDBYID        = @PostingRuleModifiedById,
                                                        MODIFIEDON          = @PostingRuleModifiedOn
                                                    WHERE
                                                        POSTINGRULEID = @PostingRuleId;";

        public const string DELETE_WAREHOUSEPOSTINGRULE = @"DELETE FROM MWAREHOUSEMEASUREPOSTINGRULE
                                                        WHERE POSTINGRULEID = @postingruleid;";
    }
}