-- Create outbound event queue for offline event creation and reliable relay transmission
-- This allows users to create events when offline and sync them when connectivity is restored

CREATE TABLE outbound_event_queue (
    queue_id INTEGER PRIMARY KEY AUTOINCREMENT,
    event_id TEXT NOT NULL,
    encrypted_event TEXT NOT NULL,
    ccn_pubkey TEXT NOT NULL,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    attempts INTEGER NOT NULL DEFAULT 0,
    last_attempt INTEGER NULL,
    status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'sending', 'sent', 'failed')),
    error_message TEXT NULL,

    -- Ensure one queue entry per event
    UNIQUE(event_id),

    -- Foreign key constraints
    FOREIGN KEY (ccn_pubkey) REFERENCES ccns(pubkey) ON DELETE CASCADE,
    FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
);

-- Indexes for efficient querying
CREATE INDEX idx_outbound_queue_status ON outbound_event_queue(status);
CREATE INDEX idx_outbound_queue_ccn_pubkey ON outbound_event_queue(ccn_pubkey);
CREATE INDEX idx_outbound_queue_created_at ON outbound_event_queue(created_at);
CREATE INDEX idx_outbound_queue_last_attempt ON outbound_event_queue(last_attempt);

-- Cleanup trigger for old completed/failed events
CREATE TRIGGER cleanup_old_queue_entries
AFTER UPDATE ON outbound_event_queue
WHEN NEW.status IN ('sent', 'failed') AND NEW.attempts >= 5
BEGIN
    -- Keep failed events for 30 days for debugging, sent events for 1 day
    DELETE FROM outbound_event_queue
    WHERE queue_id = NEW.queue_id
    AND (
        (status = 'sent' AND created_at < (unixepoch() - 86400)) OR
        (status = 'failed' AND created_at < (unixepoch() - 2592000))
    );
END;