namespace FrameworkDAL.Query.SessionStore
{
    /// <summary>
    /// SQL constants for MSESSIONSTORE in the GB5 system database.
    ///
    /// Required indexes (already on MSESSIONSTORE):
    ///   PK_MSESSIONSTORE_SESSIONID          — SESSIONID (clustered)
    ///   UK_MSESSIONSTORE_SERVERCONFIGID     — LOGINEVENTLOGID, SERVERCONFIGID (unique)
    ///   MSESSIONSTORE_SERVERCONFIGIDLOGINEVENTLOGID — SERVERCONFIGID, LOGINEVENTLOGID
    /// </summary>
    public static class SessionStoreQB
    {
        // ── INSERT / UPSERT ──────────────────────────────────────────────────

        /// <summary>
        /// SQL Server: MERGE-based upsert.
        /// Inserts a new session row or updates LASTLOGINUSEDTIME if the session
        /// already exists for the same (SERVERCONFIGID, LOGINEVENTLOGID) pair.
        /// Params: @ServerconfigId, @LoginEventLogId, @LoginTime,
        ///         @LastLoginUsedTime, @MachineIp, @UserId, @UserCode, @UserName
        /// </summary>
        public const string UPSERT_SESSION_SQL = @"
            MERGE INTO MSESSIONSTORE WITH (HOLDLOCK) AS target
            USING (SELECT @ServerconfigId   AS SERVERCONFIGID,
                          @LoginEventLogId  AS LOGINEVENTLOGID) AS source
            ON (    target.SERVERCONFIGID  = source.SERVERCONFIGID
                AND target.LOGINEVENTLOGID = source.LOGINEVENTLOGID)
            WHEN NOT MATCHED THEN
                INSERT (SERVERCONFIGID, LOGINEVENTLOGID, LOGINTIME, LASTLOGINUSEDTIME,
                        MACHINEIP, USERID, USERCODE, USERNAME, ISACTIVE)
                VALUES (@ServerconfigId, @LoginEventLogId, @LoginTime, @LastLoginUsedTime,
                        @MachineIp, @UserId, @UserCode, @UserName, 0)
            WHEN MATCHED THEN
                UPDATE SET LASTLOGINUSEDTIME = @LastLoginUsedTime;";

        /// <summary>
        /// PostgreSQL: INSERT … ON CONFLICT upsert.
        /// Params: same as UPSERT_SESSION_SQL.
        /// </summary>
        public const string UPSERT_SESSION_POSTGRESQL = @"
            INSERT INTO MSESSIONSTORE
                (SERVERCONFIGID, LOGINEVENTLOGID, LOGINTIME, LASTLOGINUSEDTIME,
                 MACHINEIP, USERID, USERCODE, USERNAME, ISACTIVE)
            VALUES
                (@ServerconfigId, @LoginEventLogId, @LoginTime, @LastLoginUsedTime,
                 @MachineIp, @UserId, @UserCode, @UserName, 0)
            ON CONFLICT (LOGINEVENTLOGID, SERVERCONFIGID)
            DO UPDATE SET LASTLOGINUSEDTIME = EXCLUDED.LASTLOGINUSEDTIME;";

        // ── UPDATE ───────────────────────────────────────────────────────────

        /// <summary>
        /// Update LASTLOGINUSEDTIME for an existing session (activity heartbeat).
        /// Params: @LastLoginUsedTime, @ServerconfigId, @LoginEventLogId
        /// </summary>
        public const string UPDATE_SESSION = @"
            UPDATE MSESSIONSTORE
               SET LASTLOGINUSEDTIME = @LastLoginUsedTime
             WHERE SERVERCONFIGID  = @ServerconfigId
               AND LOGINEVENTLOGID = @LoginEventLogId;";

        // ── DELETE ───────────────────────────────────────────────────────────

        /// <summary>
        /// Delete a specific session on logout.
        /// Params: @ServerconfigId, @LoginEventLogId
        /// </summary>
        public const string DELETE_SESSION = @"
            DELETE FROM MSESSIONSTORE
             WHERE SERVERCONFIGID  = @ServerconfigId
               AND LOGINEVENTLOGID = @LoginEventLogId;";

        /// <summary>
        /// Delete stale sessions (SQL Server).
        /// Removes sessions whose LASTLOGINUSEDTIME is older than @TodayDate,
        /// and also cleans up GB5ADMIN sessions with a blank MachineIp.
        /// Params: @TodayDate (DateTime)
        /// </summary>
        public const string DELETE_OLD_SESSIONS_SQL = @"
            DELETE FROM MSESSIONSTORE
             WHERE LASTLOGINUSEDTIME < @TodayDate
                OR (    LASTLOGINUSEDTIME >= @TodayDate
                    AND ISNULL(MACHINEIP, '') = ''
                    AND USERCODE = 'GB5ADMIN');";

        /// <summary>
        /// Delete stale sessions (PostgreSQL).
        /// Params: @TodayDate (DateTime)
        /// </summary>
        public const string DELETE_OLD_SESSIONS_POSTGRESQL = @"
            DELETE FROM MSESSIONSTORE
             WHERE LASTLOGINUSEDTIME < @TodayDate
                OR (    LASTLOGINUSEDTIME >= @TodayDate
                    AND COALESCE(MACHINEIP, '') = ''
                    AND USERCODE = 'GB5ADMIN');";

        // ── SELECT ───────────────────────────────────────────────────────────

        /// <summary>
        /// Count rows for a given session — used before UPDATE to guard against phantom writes.
        /// Params: @ServerconfigId, @LoginEventLogId
        /// </summary>
        public const string SELECT_SESSION_COUNT = @"
            SELECT COUNT(*)
              FROM MSESSIONSTORE
             WHERE SERVERCONFIGID  = @ServerconfigId
               AND LOGINEVENTLOGID = @LoginEventLogId;";


        /// <summary>
        /// Fetch idle sessions that have exceeded the expiry window (SQL Server).
        /// Used by the auto-logout background job.
        /// Params: @ExpiryTime (minutes, int), @ServerconfigId
        /// </summary>
        public const string GET_UNUSED_SESSIONS_SQL = @"
            SELECT LOGINEVENTLOGID          AS LoginEventLogId,
                   ISNULL(MACHINEIP,'NONE') AS MachineIp,
                   USERID                   AS UserId,
                   USERNAME                 AS UserName
              FROM MSESSIONSTORE
             WHERE DATEADD(MINUTE, @ExpiryTime, LASTLOGINUSEDTIME) <= GETUTCDATE()
               AND LOGINTIME  >= CAST(GETUTCDATE() AS DATE)
               AND ISACTIVE    = 0
               AND SERVERCONFIGID = @ServerconfigId;";

        /// <summary>
        /// Fetch idle sessions that have exceeded the expiry window (PostgreSQL).
        /// Params: @ExpiryTime (minutes, int), @ServerconfigId
        /// </summary>
        public const string GET_UNUSED_SESSIONS_POSTGRESQL = @"
            SELECT LOGINEVENTLOGID                    AS LoginEventLogId,
                   COALESCE(MACHINEIP, 'NONE')        AS MachineIp,
                   USERID                             AS UserId,
                   USERNAME                           AS UserName
              FROM MSESSIONSTORE
             WHERE LASTLOGINUSEDTIME + (@ExpiryTime * INTERVAL '1 minute') <= NOW() AT TIME ZONE 'UTC'
               AND LOGINTIME  >= CURRENT_DATE
               AND ISACTIVE    = 0
               AND SERVERCONFIGID = @ServerconfigId;";
    }
}