PostgreSQL Storage Provider

dotnet add package Zetian
dotnet add package Zetian.Storage.PostgreSQL

using Zetian.Server;
using Zetian.Storage.PostgreSQL.Extensions;

// Basic setup with auto table creation
var server = new SmtpServerBuilder()
    .Port(25)
    .WithPostgreSqlStorage(
        "Host=localhost;Database=smtp;Username=postgres;Password=secret;")
    .Build();

await server.StartAsync();

var server = new SmtpServerBuilder()
    .Port(25)
    .WithPostgreSqlStorage(
        "Host=localhost;Database=smtp;Username=postgres;Password=secret;",
        config =>
        {
            config.TableName = "smtp_messages";
            config.SchemaName = "mail";
            config.AutoCreateTable = true;
            
            // JSONB for flexible header storage
            config.UseJsonbForHeaders = true;
            
            // Enable partitioning
            config.EnablePartitioning = true;
            config.PartitionInterval = PartitionInterval.Monthly;
            
            // Performance
            config.CreateIndexes = true;
            config.CompressMessageBody = true;
            config.MaxMessageSizeMB = 100;
        })
    .Build();

-- Monthly partitioning example
CREATE TABLE smtp_messages (
    id BIGSERIAL,
    message_id VARCHAR(255) NOT NULL,
    received_date TIMESTAMPTZ NOT NULL,
    headers JSONB,
    message_body BYTEA,
    PRIMARY KEY (id, received_date)
) PARTITION BY RANGE (received_date);

-- Create partitions
CREATE TABLE smtp_messages_2024_01 
PARTITION OF smtp_messages
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE smtp_messages_2024_02 
PARTITION OF smtp_messages
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- Query JSONB headers
SELECT message_id, headers->>'From' as sender, 
       headers->>'Subject' as subject
FROM smtp_messages
WHERE headers @> '{"From": "[email protected]"}';

-- Search with GIN index
SELECT * FROM smtp_messages
WHERE headers @> '{"X-Spam-Score": "0"}';

-- Extract specific header
SELECT headers->'Received' as received_chain
FROM smtp_messages
WHERE message_id = 'ABC123';