-- C Nostr Relay Database Schema
-- SQLite schema for storing Nostr events with JSON tags support

-- Schema version tracking
PRAGMA user_version = 2;

-- Enable foreign key support
PRAGMA foreign_keys = ON;

-- Optimize for performance
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA cache_size = 10000;

-- Core events table with hybrid single-table design
CREATE TABLE events (
    id TEXT PRIMARY KEY,                -- Nostr event ID (hex string)
    pubkey TEXT NOT NULL,               -- Public key of event author (hex string)
    created_at INTEGER NOT NULL,       -- Event creation timestamp (Unix timestamp)
    kind INTEGER NOT NULL,              -- Event kind (0-65535)
    event_type TEXT NOT NULL CHECK (event_type IN ('regular', 'replaceable', 'ephemeral', 'addressable')),
    content TEXT NOT NULL,              -- Event content (text content only)
    sig TEXT NOT NULL,                  -- Event signature (hex string)
    tags JSON NOT NULL DEFAULT '[]',   -- Event tags as JSON array
    first_seen INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))  -- When relay received event
);

-- Core performance indexes
CREATE INDEX idx_events_pubkey ON events(pubkey);
CREATE INDEX idx_events_kind ON events(kind);
CREATE INDEX idx_events_created_at ON events(created_at DESC);
CREATE INDEX idx_events_event_type ON events(event_type);

-- Composite indexes for common query patterns
CREATE INDEX idx_events_kind_created_at ON events(kind, created_at DESC);
CREATE INDEX idx_events_pubkey_created_at ON events(pubkey, created_at DESC);
CREATE INDEX idx_events_pubkey_kind ON events(pubkey, kind);

-- Schema information table
CREATE TABLE schema_info (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL,
    updated_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
);

-- Insert schema metadata
INSERT INTO schema_info (key, value) VALUES 
    ('version', '2'),
    ('description', 'Hybrid single-table Nostr relay schema with JSON tags'),
    ('created_at', strftime('%s', 'now'));

-- Helper views for common queries
CREATE VIEW recent_events AS
SELECT id, pubkey, created_at, kind, event_type, content
FROM events
WHERE event_type != 'ephemeral'
ORDER BY created_at DESC
LIMIT 1000;

CREATE VIEW event_stats AS
SELECT 
    event_type,
    COUNT(*) as count,
    AVG(length(content)) as avg_content_length,
    MIN(created_at) as earliest,
    MAX(created_at) as latest
FROM events
GROUP BY event_type;

-- Optimization: Trigger for automatic cleanup of ephemeral events older than 1 hour
CREATE TRIGGER cleanup_ephemeral_events
    AFTER INSERT ON events
    WHEN NEW.event_type = 'ephemeral'
BEGIN
    DELETE FROM events 
    WHERE event_type = 'ephemeral' 
    AND first_seen < (strftime('%s', 'now') - 3600);
END;

-- Replaceable event handling trigger
CREATE TRIGGER handle_replaceable_events
    AFTER INSERT ON events
    WHEN NEW.event_type = 'replaceable'
BEGIN
    DELETE FROM events 
    WHERE pubkey = NEW.pubkey 
    AND kind = NEW.kind 
    AND event_type = 'replaceable'
    AND id != NEW.id;
END;