-- Create logs table for sync jobs and jetstream activity
CREATE TABLE logs (
    id BIGSERIAL PRIMARY KEY,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    log_type VARCHAR(50) NOT NULL, -- 'sync_job', 'jetstream', etc.
    job_id UUID NULL, -- For sync job logs, null for jetstream logs
    user_did TEXT NULL, -- User associated with the log (for filtering)
    slice_uri TEXT NULL, -- Slice associated with the log (for filtering)
    level VARCHAR(20) NOT NULL DEFAULT 'info', -- 'debug', 'info', 'warn', 'error'
    message TEXT NOT NULL,
    metadata JSONB NULL -- Additional structured data (counts, errors, etc.)
);

-- Create indexes for efficient queries
CREATE INDEX idx_logs_type_job_id ON logs (log_type, job_id);
CREATE INDEX idx_logs_type_created_at ON logs (log_type, created_at);
CREATE INDEX idx_logs_user_did ON logs (user_did);
CREATE INDEX idx_logs_slice_uri ON logs (slice_uri);

-- Add some helpful comments
COMMENT ON TABLE logs IS 'Unified logging table for sync jobs, jetstream, and other system activities';
COMMENT ON COLUMN logs.log_type IS 'Type of log entry: sync_job, jetstream, system, etc.';
COMMENT ON COLUMN logs.job_id IS 'Associated job ID for sync job logs, null for other log types';
COMMENT ON COLUMN logs.level IS 'Log level: debug, info, warn, error';
COMMENT ON COLUMN logs.metadata IS 'Additional structured data as JSON (progress, errors, counts, etc.)';