# 1. 用于操作 SQLite 數(shù)據(jù)庫
dotnet add package Microsoft.Data.Sqlite

# 2. 用于高效的對象序列化/反序列化
dotnet add package MessagePack

using Microsoft.Data.Sqlite;
using System;
using System.IO;
using System.Threading.Tasks;
using System.Collections.Concurrent;
using pei_repspark_admin_webapi.Entities.Constants;
using MessagePack.Resolvers;
using MessagePack; // 請確保您的常量命名空間正確

namespace YOU_PROJECT.Utils
{
    public static class CacheHelper
    {
        private static readonly string _dbPath;
        private static readonly string _connectionString;
        private static readonly MessagePackSerializerOptions _serializerOptions = ContractlessStandardResolver.Options;
        private static readonly int default_limit = CacheConstants.FILTER_LIMIT;

        // 用于線程安全的鎖管理器，確保每個(gè) key 都有一個(gè)獨(dú)立的鎖對象
        private static readonly ConcurrentDictionary<string, object> _locks = new ConcurrentDictionary<string, object>();

        /// <summary>
        /// 靜態(tài)構(gòu)造函數(shù)，在類第一次被訪問時(shí)自動運(yùn)行一次，用于初始化數(shù)據(jù)庫。
        /// </summary>
        static CacheHelper()
        {
            // 將數(shù)據(jù)庫文件放在應(yīng)用程序的根目錄下，確保路徑一致性
            _dbPath = Path.Combine(AppContext.BaseDirectory, "cache.db");
            _connectionString = $"Data Source={_dbPath}";
            InitializeDatabase();
        }

        /// <summary>
        /// 初始化數(shù)據(jù)庫：如果數(shù)據(jù)庫文件或表不存在，則創(chuàng)建它們。
        /// </summary>
        private static void InitializeDatabase()
        {
            try
            {
                using var connection = new SqliteConnection(_connectionString);
                connection.Open();

                // 創(chuàng)建一個(gè)可重用的 command 對象
                var command = connection.CreateCommand();

                // 1. 執(zhí)行 PRAGMA 指令以優(yōu)化并發(fā)性能
                command.CommandText = "PRAGMA journal_mode=WAL;";
                command.ExecuteNonQuery();

                // 2. 重用同一個(gè) command 對象，執(zhí)行 CREATE TABLE 指令
                command.CommandText = @"
                CREATE TABLE IF NOT EXISTS CacheStore (
                    Key TEXT NOT NULL PRIMARY KEY,
                    Value BLOB NOT NULL,
                    InsertionTimeUtc INTEGER NOT NULL,
                    ExpirationTimeUtc INTEGER NOT NULL,
                    SlidingExpirationMinutes INTEGER NOT NULL
                );";
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                // 如果數(shù)據(jù)庫初始化失敗，這是個(gè)嚴(yán)重問題，需要記錄下來
                // 在生產(chǎn)環(huán)境中，應(yīng)使用專業(yè)的日志庫（如 Serilog, NLog）
                ($"[CACHE CRITICAL] Database initialization failed. Error: {ex.Message}").LogErr();
                // 拋出異常，因?yàn)槿绻麛?shù)據(jù)庫無法初始化，整個(gè)緩存服務(wù)都無法工作
                throw;
            }
        }

        #region Public Cache Modification Methods (Add, Clean)

        public static void AddCacheWithAbsolute(string key, object value, int minute)
        {
            Set(key, value, TimeSpan.FromMinutes(minute), isSliding: false);
        }

        public static void AddCacheWithRelative(string key, object value, int minute)
        {
            Set(key, value, TimeSpan.FromMinutes(minute), isSliding: true);
        }

        public static void AddCache(string key, object value)
        {
            // 對于永久緩存，我們設(shè)置一個(gè)極大的過期時(shí)間
            var longLivedTimeSpan = TimeSpan.FromDays(30); // 30 days
            Set(key, value, longLivedTimeSpan, isSliding: false);
        }

        public static void CleanCache(string key)
        {
            try
            {
                using var connection = new SqliteConnection(_connectionString);
                connection.Open();

                var command = connection.CreateCommand();
                command.CommandText = "DELETE FROM CacheStore WHERE Key = $key;";
                command.Parameters.AddWithValue("$key", key);
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ($"[CACHE ERROR] Failed to clean cache for key '{key}'. Error: {ex.Message}").LogErr();
            }
        }

        #endregion

        #region Public Cache Retrieval Methods (Get, GetOrQuery)

        public static object? GetCache(string key)
        {
            var (found, value) = TryGetCache<object>(key);
            return found ? value : null;
        }

        public static T GetCacheOrQuery<T>(string key, Func<T> myFunc)
        {
            return GetCacheOrQuery<T>(key, default_limit, myFunc);
        }

        public static T GetCacheOrQuery<T>(string key, int minute, Func<T> myFunc)
        {
            return GetOrSet(key, minute, () => myFunc());
        }

        public static r GetCacheOrQuery<p, r>(string key, Func<p, r> myFunc, p param1)
        {
            return GetCacheOrQuery<p, r>(key, default_limit, myFunc, param1);
        }

        public static r GetCacheOrQuery<p, r>(string key, int minter, Func<p, r> myFunc, p param1)
        {
            return GetOrSet(key, minter, () => myFunc(param1));
        }

        // --- 為了簡潔，這里省略了剩余的 GetCacheOrQuery 重載 ---
        // --- 您可以按照下面的 `GetOrSet` 模式輕松地實(shí)現(xiàn)它們 ---
        // 示例：
        public static r GetCacheOrQuery<p1, p2, r>(string key, int minter, Func<p1, p2, r> myFunc, p1 param1, p2 param2)
        {
            return GetOrSet(key, minter, () => myFunc(param1, param2));
        }

        public static r GetCacheOrQuery<p1, p2, r>(string key, Func<p1, p2, r> myFunc, p1 param1, p2 param2)
        {
            return GetCacheOrQuery(key, default_limit, myFunc, param1, param2);
        }

        // ... 請為其他所有重載方法應(yīng)用相同的模式 ...

        #endregion

        #region Core Logic (Private Methods)

        /// <summary>
        /// 核心的 "Get-Or-Set" 方法，實(shí)現(xiàn)了雙重檢查鎖定以確保線程安全。
        /// </summary>
        private static T GetOrSet<T>(string key, int minute, Func<T> queryFunc)
        {
            // 第一次檢查（在鎖之外），這是為了在緩存命中的情況下獲得最高性能
            var (found, value) = TryGetCache<T>(key);
            if (found)
            {
                Console.WriteLine($"Get [{key}] cache, survival time is [{minute}] minutes");
                return value!;
            }

            // 獲取或?yàn)楫?dāng)前 key 創(chuàng)建一個(gè)唯一的鎖對象
            var lockObject = _locks.GetOrAdd(key, k => new object());

            // 進(jìn)入鎖代碼塊，確保同一時(shí)間只有一個(gè)線程能為這個(gè) key 生成緩存
            lock (lockObject)
            {
                // 第二次檢查（在鎖之內(nèi)），防止在等待鎖的過程中，其他線程已經(jīng)生成了緩存
                (found, value) = TryGetCache<T>(key);
                if (found)
                {
                    return value!;
                }

                // 執(zhí)行昂貴的數(shù)據(jù)查詢操作
                var result = queryFunc();

                // 將查詢結(jié)果存入緩存
                if (result != null)
                {
                    Set(key, result, TimeSpan.FromMinutes(minute), isSliding: false);
                }
                Console.WriteLine($"Added [{key}] cache, survival time is [{minute}] minutes");
                return result;
            }
        }

        /// <summary>
        /// 統(tǒng)一的緩存寫入方法。
        /// </summary>
        private static void Set(string key, object value, TimeSpan expiration, bool isSliding)
        {
            if (value == null) return;

            try
            {
                var serializedValue = MessagePackSerializer.Serialize(value, _serializerOptions);
                var now = DateTime.UtcNow;

                using var connection = new SqliteConnection(_connectionString);
                connection.Open();

                var command = connection.CreateCommand();
                command.CommandText = @"
                    INSERT OR REPLACE INTO CacheStore (Key, Value, InsertionTimeUtc, ExpirationTimeUtc, SlidingExpirationMinutes)
                    VALUES ($key, $value, $insertion, $expiration, $sliding);";

                command.Parameters.AddWithValue("$key", key);
                command.Parameters.AddWithValue("$value", serializedValue);
                command.Parameters.AddWithValue("$insertion", now.Ticks);
                command.Parameters.AddWithValue("$expiration", now.Add(expiration).Ticks);
                command.Parameters.AddWithValue("$sliding", isSliding ? expiration.TotalMinutes : 0);

                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ($"[CACHE ERROR] Failed to set cache for key '{key}'. Error: {ex.Message}").LogErr();
                // 吞掉異常，保證主程序繼續(xù)運(yùn)行
            }
        }

        /// <summary>
        /// 嘗試從緩存中獲取數(shù)據(jù)，并處理滑動過期的更新邏輯。
        /// </summary>
        private static (bool found, T? value) TryGetCache<T>(string key)
        {
            try
            {
                using var connection = new SqliteConnection(_connectionString);
                connection.Open();

                var command = connection.CreateCommand();
                command.CommandText = @"
                    SELECT Value, SlidingExpirationMinutes FROM CacheStore
                    WHERE Key = $key AND ExpirationTimeUtc > $now;";
                command.Parameters.AddWithValue("$key", key);
                command.Parameters.AddWithValue("$now", DateTime.UtcNow.Ticks);

                using var reader = command.ExecuteReader();
                if (reader.Read())
                {
                    var blob = reader.GetFieldValue<byte[]>(0);
                    var slidingMinutes = reader.GetInt64(1);

                    // 如果是滑動過期項(xiàng)，則更新其過期時(shí)間
                    if (slidingMinutes > 0)
                    {
                        try
                        {
                            var updateCmd = connection.CreateCommand();
                            updateCmd.CommandText = "UPDATE CacheStore SET ExpirationTimeUtc = $newExpiration WHERE Key = $key;";
                            updateCmd.Parameters.AddWithValue("$key", key);
                            updateCmd.Parameters.AddWithValue("$newExpiration", DateTime.UtcNow.AddMinutes(slidingMinutes).Ticks);
                            updateCmd.ExecuteNonQuery();
                        }
                        catch (Exception updateEx)
                        {
                            // 滑動過期更新失敗不是致命錯(cuò)誤，只記錄警告
                            ($"[CACHE WARNING] Failed to update sliding expiration for key '{key}'. Error: {updateEx.Message}").LogErr();
                        }
                    }

                    var deserializedValue = MessagePackSerializer.Deserialize<T>(blob, _serializerOptions);
                    return (true, deserializedValue);
                }
            }
            catch (Exception ex)
            {
                ($"[CACHE ERROR] Failed to get cache for key '{key}'. Error: {ex.Message}").LogErr();
            }

            // 如果發(fā)生任何錯(cuò)誤或未找到，都返回“未命中”
            return (false, default);
        }

        #endregion
    }
}