namespace EAIAdminDAL.Query
{
    // Dialect-neutral by construction (see EAIInitiativeAdminQB's header note for the repo-wide
    // rule this follows): no GETDATE()/NOW(), search wildcard built in C#.
    public static class EAIContextSettingQB
    {
        public const string GET_SETTING_LIST = @"
            SELECT
                S.CONTEXTSETTINGID       AS ContextSettingId,
                S.CONTEXTKEYDEFINITIONID AS ContextKeyDefinitionId,
                K.CONTEXTKEY             AS ContextKey,
                S.SCOPELEVEL             AS ScopeLevel,
                S.SCOPEVALUE             AS ScopeValue,
                S.CONTEXTVALUE           AS ContextValue,
                S.PRIORITY               AS Priority,
                S.TENANTID               AS TenantId,
                S.VERSION                AS Version,
                S.STATUS                 AS Status,
                S.CREATEDBYID            AS CreatedById,
                CB.EMPLOYEENAME          AS CreatedByName,
                S.CREATEDON              AS CreatedOn,
                S.MODIFIEDBYID           AS ModifiedById,
                MB.EMPLOYEENAME          AS ModifiedByName,
                S.MODIFIEDON             AS ModifiedOn
            FROM EAI.MEAICONTEXTSETTING S
            JOIN EAI.MEAICONTEXTKEYDEFINITION K ON S.CONTEXTKEYDEFINITIONID = K.CONTEXTKEYDEFINITIONID
            LEFT JOIN MEMPLOYEE CB ON S.CREATEDBYID = CB.EMPLOYEEID
            LEFT JOIN MEMPLOYEE MB ON S.MODIFIEDBYID = MB.EMPLOYEEID
            WHERE (S.TENANTID = @TenantId OR S.TENANTID = -1)
              AND S.STATUS = 1
              AND (
                    @ContextKeyDefinitionId = 0
                    OR S.CONTEXTKEYDEFINITIONID = @ContextKeyDefinitionId
                  )
            ORDER BY K.CONTEXTKEY ASC, S.SCOPELEVEL ASC, S.PRIORITY ASC";

        public const string GET_SETTING = @"
            SELECT
                S.CONTEXTSETTINGID       AS ContextSettingId,
                S.CONTEXTKEYDEFINITIONID AS ContextKeyDefinitionId,
                K.CONTEXTKEY             AS ContextKey,
                S.SCOPELEVEL             AS ScopeLevel,
                S.SCOPEVALUE             AS ScopeValue,
                S.CONTEXTVALUE           AS ContextValue,
                S.PRIORITY               AS Priority,
                S.TENANTID               AS TenantId,
                S.VERSION                AS Version,
                S.STATUS                 AS Status,
                S.CREATEDBYID            AS CreatedById,
                CB.EMPLOYEENAME          AS CreatedByName,
                S.CREATEDON              AS CreatedOn,
                S.MODIFIEDBYID           AS ModifiedById,
                MB.EMPLOYEENAME          AS ModifiedByName,
                S.MODIFIEDON             AS ModifiedOn
            FROM EAI.MEAICONTEXTSETTING S
            JOIN EAI.MEAICONTEXTKEYDEFINITION K ON S.CONTEXTKEYDEFINITIONID = K.CONTEXTKEYDEFINITIONID
            LEFT JOIN MEMPLOYEE CB ON S.CREATEDBYID = CB.EMPLOYEEID
            LEFT JOIN MEMPLOYEE MB ON S.MODIFIEDBYID = MB.EMPLOYEEID
            WHERE S.CONTEXTSETTINGID = @ContextSettingId
              AND (S.TENANTID = @TenantId OR S.TENANTID = -1)
              AND S.STATUS = 1";

        // AutoNumber PK; TenantId is server-set (login.ClientId for scoped rows, or -1 for
        // Global/ClientAttribute rows — the BLL decides based on ScopeLevel, not the client).
        public const string INSERT_SETTING = @"
            INSERT INTO EAI.MEAICONTEXTSETTING (
                CONTEXTSETTINGID, CONTEXTKEYDEFINITIONID, SCOPELEVEL, SCOPEVALUE, CONTEXTVALUE,
                PRIORITY, TENANTID, VERSION, STATUS,
                CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
            )
            VALUES (
                @ContextSettingId, @ContextKeyDefinitionId, @ScopeLevel, @ScopeValue, @ContextValue,
                @Priority, @TenantId, @Version, 1,
                @CreatedById, @CreatedOn, @CreatedById, @CreatedOn
            )";

        public const string UPDATE_SETTING = @"
            UPDATE EAI.MEAICONTEXTSETTING
            SET
                SCOPEVALUE     = @ScopeValue,
                CONTEXTVALUE   = @ContextValue,
                PRIORITY       = @Priority,
                VERSION        = @Version,
                MODIFIEDBYID   = @ModifiedById,
                MODIFIEDON     = @ModifiedOn
            WHERE CONTEXTSETTINGID = @ContextSettingId
              AND (TENANTID = @TenantId OR TENANTID = -1)
              AND STATUS = 1";

        public const string DELETE_SETTING = @"
            UPDATE EAI.MEAICONTEXTSETTING
            SET
                STATUS       = 0,
                MODIFIEDBYID = @ModifiedById,
                MODIFIEDON   = @ModifiedOn
            WHERE CONTEXTSETTINGID = @ContextSettingId
              AND (TENANTID = @TenantId OR TENANTID = -1)
              AND STATUS = 1";

        // ── Resolver read path — IEAIContextResolver ────────────────────────────────────────────
        // Fetches every row that COULD match a given request in one pass; the resolver (in C#)
        // does tier-weight/priority ranking and composition, not this query. ClassificationValues
        // is a List<string> of "{DimensionCode}:{ValueCode}" strings for this tenant — pass a
        // single-element placeholder like "__NONE__" when the tenant has no classification rows,
        // since Dapper's `IN @List` needs a non-empty list.
        public const string GET_CANDIDATE_SETTINGS_FOR_RESOLUTION = @"
            SELECT
                S.CONTEXTSETTINGID       AS ContextSettingId,
                S.CONTEXTKEYDEFINITIONID AS ContextKeyDefinitionId,
                K.CONTEXTKEY             AS ContextKey,
                K.COMPOSITIONMODE        AS CompositionMode,
                S.SCOPELEVEL             AS ScopeLevel,
                S.SCOPEVALUE             AS ScopeValue,
                S.CONTEXTVALUE           AS ContextValue,
                S.PRIORITY               AS Priority,
                S.MODIFIEDON             AS ModifiedOn
            FROM EAI.MEAICONTEXTSETTING S
            JOIN EAI.MEAICONTEXTKEYDEFINITION K ON S.CONTEXTKEYDEFINITIONID = K.CONTEXTKEYDEFINITIONID
            WHERE S.STATUS = 1
              AND K.STATUS = 1
              AND (
                    (S.SCOPELEVEL = 0 AND S.TENANTID = -1)
                    OR (S.SCOPELEVEL = 1 AND S.TENANTID = -1 AND S.SCOPEVALUE IN @ClassificationValues)
                    OR (S.SCOPELEVEL = 2 AND S.TENANTID = @TenantId AND S.SCOPEVALUE = @TenantIdStr)
                    OR (S.SCOPELEVEL = 3 AND S.TENANTID = @TenantId AND S.SCOPEVALUE = @RoleIdStr)
                    OR (S.SCOPELEVEL = 4 AND S.TENANTID = @TenantId AND S.SCOPEVALUE = @WorkOUIdStr)
                    OR (S.SCOPELEVEL = 5 AND S.TENANTID = @TenantId AND S.SCOPEVALUE = @UserIdStr)
                  )";

        // Returns DIMENSIONCODE/VALUECODE as separate columns — concatenated into "Dim:Value" in
        // C# (EAIContextSettingDAL), not here, to stay dialect-neutral (T-SQL '+' vs. Postgres '||').
        public const string GET_CLASSIFICATION_VALUES_FOR_TENANT = @"
            SELECT
                D.DIMENSIONCODE AS DimensionCode,
                V.VALUECODE     AS ValueCode
            FROM EAI.MEAICLIENTCLASSIFICATIONVALUE V
            JOIN EAI.MEAICLIENTCLASSIFICATIONDIMENSION D ON V.DIMENSIONID = D.DIMENSIONID
            WHERE V.TENANTID = @TenantId
              AND V.STATUS   = 1
              AND D.STATUS   = 1";

        // Resolver's only dependency on FrameworkDAL's MUSER table (columns confirmed via
        // UserQB.cs: ROLEID, WORKOUID, TENANTID) — done as a small local query rather than
        // depending on FrameworkDAL.IUserDAL, which lives in a different, cross-process-
        // inaccessible host (see IEAIContextSettingDAL's doc comment).
        public const string GET_USER_ROLE_AND_OU = @"
            SELECT
                U.ROLEID   AS RoleId,
                U.WORKOUID AS WorkOUId
            FROM MUSER U
            WHERE U.USERID   = @UserId
              AND U.TENANTID = @TenantId";
    }
}