-- Fix event_chunks table schema to add proper security constraints for chunked message handling

-- Drop the old table if it exists
DROP TABLE IF EXISTS event_chunks;

-- Create the event_chunks table with correct schema and security constraints
CREATE TABLE event_chunks (
    chunk_id INTEGER PRIMARY KEY AUTOINCREMENT,
    message_id TEXT NOT NULL,
    chunk_index INTEGER NOT NULL,
    total_chunks INTEGER NOT NULL CHECK (total_chunks > 0 AND total_chunks <= 1000),
    content TEXT NOT NULL,
    created_at INTEGER NOT NULL,
    ccn_pubkey TEXT NOT NULL,

    -- SECURITY: Prevent duplicate chunks and enforce data integrity
    UNIQUE(message_id, chunk_index, ccn_pubkey),

    -- SECURITY: Ensure chunk_index is within valid bounds
    CHECK (chunk_index >= 0 AND chunk_index < total_chunks),

    -- SECURITY: Limit message_id length to prevent DoS
    CHECK (length(message_id) <= 100),

    -- SECURITY: Limit content size to prevent memory exhaustion
    CHECK (length(content) <= 65536),

    -- SECURITY: Foreign key reference to ensure CCN exists
    FOREIGN KEY (ccn_pubkey) REFERENCES ccns(pubkey) ON DELETE CASCADE
);

-- Indexes for performance
CREATE INDEX idx_event_chunks_message_id ON event_chunks(message_id);
CREATE INDEX idx_event_chunks_created_at ON event_chunks(created_at);
CREATE INDEX idx_event_chunks_ccn_pubkey ON event_chunks(ccn_pubkey);

-- SECURITY: Automatic cleanup trigger for old chunks to prevent storage exhaustion
CREATE TRIGGER cleanup_old_chunks
AFTER INSERT ON event_chunks
WHEN (SELECT COUNT(*) FROM event_chunks WHERE created_at < (unixepoch() - 86400)) > 0
BEGIN
    DELETE FROM event_chunks
    WHERE created_at < (unixepoch() - 86400);
END;