namespace AccountsDAL.Query.WarehouseFact
{
    // CRUD SQL for MWAREHOUSEFACT admin endpoints — the same Get/Save/Update/Delete shape as
    // WarehousePostingRuleQB/WarehouseMeasureQB. Separate from
    // AccountsDAL.Query.Warehouse.WarehouseFactPostingQB's read-only GET_FACT_TABLE_NAME hot path.
    public static class WarehouseFactQB
    {
        public const string GET_WAREHOUSEFACT = @"SELECT
                                                    FACTID              AS FactId,
                                                    FACTCODE            AS FactCode,
                                                    FACTNAME            AS FactName,
                                                    FACTTABLENAME       AS FactTableName,
                                                    GRAINDESCRIPTION    AS GrainDescription,
                                                    DATEDIMCOLUMN       AS DateDimColumn,
                                                    OUDIMCOLUMN         AS OuDimColumn,
                                                    BUSINESSDESCRIPTION AS BusinessDescription,
                                                    SYNONYMS            AS Synonyms,
                                                    SOURCETYPE          AS SourceType,
                                                    TENANTID            AS TenantId,
                                                    STATUS              AS FactStatus,
                                                    VERSION             AS FactVersion,
                                                    SORTORDER           AS FactSortOrder,
                                                    CREATEDBYID         AS FactCreatedById,
                                                    CREATEDON           AS FactCreatedOn,
                                                    MODIFIEDBYID        AS FactModifiedById,
                                                    MODIFIEDON          AS FactModifiedOn
                                                FROM MWAREHOUSEFACT
                                                WHERE FACTID = @factid;";

        public const string GET_WAREHOUSEFACT_LIST = @"SELECT
                                                    FACTID              AS FactId,
                                                    FACTCODE            AS FactCode,
                                                    FACTNAME            AS FactName,
                                                    FACTTABLENAME       AS FactTableName,
                                                    GRAINDESCRIPTION    AS GrainDescription,
                                                    DATEDIMCOLUMN       AS DateDimColumn,
                                                    OUDIMCOLUMN         AS OuDimColumn,
                                                    BUSINESSDESCRIPTION AS BusinessDescription,
                                                    SYNONYMS            AS Synonyms,
                                                    SOURCETYPE          AS SourceType,
                                                    TENANTID            AS TenantId,
                                                    STATUS              AS FactStatus,
                                                    VERSION             AS FactVersion,
                                                    SORTORDER           AS FactSortOrder,
                                                    CREATEDBYID         AS FactCreatedById,
                                                    CREATEDON           AS FactCreatedOn,
                                                    MODIFIEDBYID        AS FactModifiedById,
                                                    MODIFIEDON          AS FactModifiedOn
                                                FROM MWAREHOUSEFACT
                                                ORDER BY SORTORDER, FACTID;";

        // UK_MWAREHOUSEFACT_FACTCODE enforces one row per (FACTCODE, TENANTID) at the DB level;
        // the BLL checks this first for a clear validation error instead of a raw constraint
        // violation.
        public const string GET_EXISTING_FACT_FOR_CODE = @"SELECT FACTID
                                                FROM MWAREHOUSEFACT
                                                WHERE FACTCODE = @FactCode AND TENANTID = @TenantId;";

        // Confirms FACTTABLENAME is an actual physical table before Save — the posting engine
        // (WarehouseFactPostingDAL) substitutes this value directly into DELETE/INSERT SQL text
        // (validated there against ^[A-Z0-9_]+$ only, never checked for real existence), so a typo
        // here would otherwise surface only the first time PostFact is called against it.
        public const string CHECK_TABLE_EXISTS = @"SELECT COUNT(*)
                                                FROM INFORMATION_SCHEMA.TABLES
                                                WHERE TABLE_NAME = @TableName;";

        public const string SAVE_WAREHOUSEFACT = @"INSERT INTO MWAREHOUSEFACT
                                                        (
                                                            FACTID,
                                                            FACTCODE,
                                                            FACTNAME,
                                                            FACTTABLENAME,
                                                            GRAINDESCRIPTION,
                                                            DATEDIMCOLUMN,
                                                            OUDIMCOLUMN,
                                                            BUSINESSDESCRIPTION,
                                                            SYNONYMS,
                                                            SOURCETYPE,
                                                            TENANTID,
                                                            STATUS,
                                                            VERSION,
                                                            SORTORDER,
                                                            CREATEDBYID,
                                                            CREATEDON,
                                                            MODIFIEDBYID,
                                                            MODIFIEDON
                                                        )
                                                        VALUES
                                                        (
                                                            @FactId,
                                                            @FactCode,
                                                            @FactName,
                                                            @FactTableName,
                                                            @GrainDescription,
                                                            @DateDimColumn,
                                                            @OuDimColumn,
                                                            @BusinessDescription,
                                                            @Synonyms,
                                                            @SourceType,
                                                            @TenantId,
                                                            @FactStatus,
                                                            @FactVersion,
                                                            @FactSortOrder,
                                                            @FactCreatedById,
                                                            @FactCreatedOn,
                                                            @FactModifiedById,
                                                            @FactModifiedOn
                                                        );";

        public const string UPDATE_WAREHOUSEFACT = @"UPDATE MWAREHOUSEFACT
                                                    SET
                                                        FACTCODE            = @FactCode,
                                                        FACTNAME            = @FactName,
                                                        FACTTABLENAME       = @FactTableName,
                                                        GRAINDESCRIPTION    = @GrainDescription,
                                                        DATEDIMCOLUMN       = @DateDimColumn,
                                                        OUDIMCOLUMN         = @OuDimColumn,
                                                        BUSINESSDESCRIPTION = @BusinessDescription,
                                                        SYNONYMS            = @Synonyms,
                                                        SOURCETYPE          = @SourceType,
                                                        STATUS              = @FactStatus,
                                                        VERSION             = @FactVersion,
                                                        SORTORDER           = @FactSortOrder,
                                                        MODIFIEDBYID        = @FactModifiedById,
                                                        MODIFIEDON          = @FactModifiedOn
                                                    WHERE
                                                        FACTID = @FactId;";

        public const string DELETE_WAREHOUSEFACT = @"DELETE FROM MWAREHOUSEFACT
                                                        WHERE FACTID = @factid;";
    }
}