Install-Package Grpc.Net.Client
Install-Package Google.Protobuf
Install-Package Grpc.Tools

/// <summary>
/// 初始化數(shù)據(jù)庫(kù)表 UploadSessions，用于記錄上傳會(huì)話(huà)信息。
/// 如果表不存在，則創(chuàng)建該表。
/// </summary>
private void InitializeDatabase()
{
    using var connection = new SqliteConnection(_connectionString);
    connection.Open();

    var command = connection.CreateCommand();
    command.CommandText = @"
        CREATE TABLE IF NOT EXISTS UploadSessions (
            SessionId TEXT PRIMARY KEY,       -- 會(huì)話(huà)唯一標(biāo)識(shí)符（GUID）
            FileName TEXT NOT NULL,           -- 文件名
            FileSize INTEGER NOT NULL,        -- 文件總大?。ㄗ止?jié)）
            FileHash TEXT NOT NULL,           -- 文件哈希值（用于斷點(diǎn)續(xù)傳校驗(yàn)）
            UploadedBytes INTEGER NOT NULL,   -- 已上傳字節(jié)數(shù)（初始為0）
            TempFilePath TEXT NOT NULL,       -- 臨時(shí)文件路徑
            CreatedAt TEXT NOT NULL,          -- 創(chuàng)建時(shí)間（UTC格式字符串）
            CompletedAt TEXT                  -- 完成時(shí)間（可為空）
        )";
    command.ExecuteNonQuery();
}

/// <summary>
/// 創(chuàng)建一個(gè)新的上傳會(huì)話(huà)，并插入數(shù)據(jù)庫(kù)中。
/// </summary>
/// <param name="fileName">上傳文件的原始名稱(chēng)</param>
/// <param name="fileSize">文件總大小</param>
/// <param name="fileHash">文件的哈希值，用于校驗(yàn)完整性</param>
/// <returns>生成的會(huì)話(huà)ID</returns>
public string CreateSession(string fileName, long fileSize, string fileHash)
{
    var sessionId = Guid.NewGuid().ToString(); // 生成唯一會(huì)話(huà)ID
    var tempFilePath = Path.Combine(_tempStoragePath, $"temp_{sessionId}_{Path.GetFileName(fileName)}");

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

    var command = connection.CreateCommand();
    command.CommandText = @"
        INSERT INTO UploadSessions 
        (SessionId, FileName, FileSize, FileHash, UploadedBytes, TempFilePath, CreatedAt)
        VALUES 
        (@SessionId, @FileName, @FileSize, @FileHash, 0, @TempFilePath, @CreatedAt)";

    command.Parameters.AddWithValue("@SessionId", sessionId);
    command.Parameters.AddWithValue("@FileName", fileName);
    command.Parameters.AddWithValue("@FileSize", fileSize);
    command.Parameters.AddWithValue("@FileHash", fileHash);
    command.Parameters.AddWithValue("@TempFilePath", tempFilePath);
    command.Parameters.AddWithValue("@CreatedAt", DateTime.UtcNow.ToString("o")); // ISO8601 格式時(shí)間

    command.ExecuteNonQuery();

    return sessionId;
}

/// <summary>
/// 根據(jù)會(huì)話(huà)ID獲取上傳會(huì)話(huà)的信息。
/// </summary>
/// <param name="sessionId">會(huì)話(huà)ID</param>
/// <returns>UploadSession 對(duì)象，若未找到則返回 null</returns>
public UploadSession GetSession(string sessionId)
{
    using var connection = new SqliteConnection(_connectionString);
    connection.Open();

    var command = connection.CreateCommand();
    command.CommandText = "SELECT * FROM UploadSessions WHERE SessionId = @SessionId";
    command.Parameters.AddWithValue("@SessionId", sessionId);

    using var reader = command.ExecuteReader();
    if (reader.Read())
    {
        return new UploadSession
        {
            SessionId = reader.GetString(0),
            FileName = reader.GetString(1),
            FileSize = reader.GetInt64(2),
            FileHash = reader.GetString(3),
            UploadedBytes = reader.GetInt64(4),
            TempFilePath = reader.GetString(5),
            CreatedAt = DateTime.Parse(reader.GetString(6)),
            CompletedAt = reader.IsDBNull(7) ? null : DateTime.Parse(reader.GetString(7))
        };
    }

    return null;
}

/// <summary>
/// 根據(jù)文件名和哈希查找最近的一次上傳會(huì)話(huà)。
/// 主要用于斷點(diǎn)續(xù)傳時(shí)查找已有會(huì)話(huà)。
/// </summary>
/// <param name="fileName">文件名</param>
/// <param name="fileHash">文件哈希值</param>
/// <returns>最近一次的 UploadSession 對(duì)象，若未找到則返回 null</returns>
public UploadSession FindSession(string fileName, string fileHash)
{
    using var connection = new SqliteConnection(_connectionString);
    connection.Open();

    var command = connection.CreateCommand();
    command.CommandText = @"
        SELECT * FROM UploadSessions 
        WHERE FileName = @FileName AND FileHash = @FileHash
        ORDER BY CreatedAt DESC
        LIMIT 1";

    command.Parameters.AddWithValue("@FileName", fileName);
    command.Parameters.AddWithValue("@FileHash", fileHash);

    using var reader = command.ExecuteReader();
    if (reader.Read())
    {
        return new UploadSession
        {
            SessionId = reader.GetString(0),
            FileName = reader.GetString(1),
            FileSize = reader.GetInt64(2),
            FileHash = reader.GetString(3),
            UploadedBytes = reader.GetInt64(4),
            TempFilePath = reader.GetString(5),
            CreatedAt = DateTime.Parse(reader.GetString(6)),
            CompletedAt = reader.IsDBNull(7) ? null : DateTime.Parse(reader.GetString(7))
        };
    }

    return null;
}

/// <summary>
/// 更新指定會(huì)話(huà)的已上傳字節(jié)數(shù)。
/// </summary>
/// <param name="sessionId">會(huì)話(huà)ID</param>
/// <param name="uploadedBytes">當(dāng)前已上傳字節(jié)數(shù)</param>
public void UpdateSessionProgress(string sessionId, long uploadedBytes)
{
    using var connection = new SqliteConnection(_connectionString);
    connection.Open();

    var command = connection.CreateCommand();
    command.CommandText = @"
        UPDATE UploadSessions 
        SET UploadedBytes = @UploadedBytes 
        WHERE SessionId = @SessionId";

    command.Parameters.AddWithValue("@SessionId", sessionId);
    command.Parameters.AddWithValue("@UploadedBytes", uploadedBytes);

    command.ExecuteNonQuery();
}

/// <summary>
/// 獲取指定會(huì)話(huà)的已上傳字節(jié)數(shù)。
/// </summary>
/// <param name="sessionId">會(huì)話(huà)ID</param>
/// <returns>已上傳字節(jié)數(shù)</returns>
public long GetUploadedBytes(string sessionId)
{
    using var connection = new SqliteConnection(_connectionString);
    connection.Open();

    var command = connection.CreateCommand();
    command.CommandText = "SELECT UploadedBytes FROM UploadSessions WHERE SessionId = @SessionId";
    command.Parameters.AddWithValue("@SessionId", sessionId);

    var result = command.ExecuteScalar();
    return result != null ? Convert.ToInt64(result) : 0;
}

/// <summary>
/// 將指定會(huì)話(huà)標(biāo)記為已完成。
/// </summary>
/// <param name="sessionId">會(huì)話(huà)ID</param>
public void CompleteSession(string sessionId)
{
    using var connection = new SqliteConnection(_connectionString);
    connection.Open();

    var command = connection.CreateCommand();
    command.CommandText = @"
        UPDATE UploadSessions 
        SET CompletedAt = @CompletedAt 
        WHERE SessionId = @SessionId";

    command.Parameters.AddWithValue("@SessionId", sessionId);
    command.Parameters.AddWithValue("@CompletedAt", DateTime.UtcNow.ToString("o"));

    command.ExecuteNonQuery();
}

/// <summary>
/// 終止指定會(huì)話(huà)并刪除臨時(shí)文件及數(shù)據(jù)庫(kù)記錄。
/// </summary>
/// <param name="sessionId">會(huì)話(huà)ID</param>
public void AbortSession(string sessionId)
{
    var session = GetSession(sessionId);
    if (session != null)
    {
        try
        {
            if (File.Exists(session.TempFilePath))
            {
                File.Delete(session.TempFilePath); // 刪除臨時(shí)文件
            }
        }
        catch
        {
            // 可選：記錄日志或處理異常
        }

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

        var command = connection.CreateCommand();
        command.CommandText = "DELETE FROM UploadSessions WHERE SessionId = @SessionId";
        command.Parameters.AddWithValue("@SessionId", sessionId);
        command.ExecuteNonQuery();
    }
}

/// <summary>
/// 清理過(guò)期的上傳會(huì)話(huà)（未完成且超過(guò)指定時(shí)間）。
/// 同時(shí)刪除對(duì)應(yīng)的臨時(shí)文件和數(shù)據(jù)庫(kù)記錄。
/// </summary>
/// <param name="expirationTime">會(huì)話(huà)的過(guò)期時(shí)間跨度</param>
public void CleanupExpiredSessions(TimeSpan expirationTime)
{
    var cutoff = DateTime.UtcNow - expirationTime;

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

    // 首先查詢(xún)所有過(guò)期會(huì)話(huà)
    var selectCommand = connection.CreateCommand();
    selectCommand.CommandText = @"
        SELECT SessionId, TempFilePath FROM UploadSessions 
        WHERE CreatedAt < @Cutoff AND (CompletedAt IS NULL OR CompletedAt < @Cutoff)";
    selectCommand.Parameters.AddWithValue("@Cutoff", cutoff.ToString("o"));

    var sessionsToDelete = new List<(string SessionId, string TempFilePath)>();

    using (var reader = selectCommand.ExecuteReader())
    {
        while (reader.Read())
        {
            sessionsToDelete.Add((reader.GetString(0), reader.GetString(1)));
        }
    }

    // 然后依次刪除臨時(shí)文件和數(shù)據(jù)庫(kù)記錄
    foreach (var (sessionId, tempFilePath) in sessionsToDelete)
    {
        try
        {
            if (File.Exists(tempFilePath))
            {
                File.Delete(tempFilePath);
            }
        }
        catch
        {
            // 可選：記錄日志或處理異常
        }

        var deleteCommand = connection.CreateCommand();
        deleteCommand.CommandText = "DELETE FROM UploadSessions WHERE SessionId = @SessionId";
        deleteCommand.Parameters.AddWithValue("@SessionId", sessionId);
        deleteCommand.ExecuteNonQuery();
    }
}