-- Add audit trail columns to track who created/updated content
-- This migration adds created_by and updated_by columns to all content tables

-- Blog posts
ALTER TABLE blog_posts ADD COLUMN created_by INTEGER REFERENCES users(id);
ALTER TABLE blog_posts ADD COLUMN updated_by INTEGER REFERENCES users(id);

-- News
ALTER TABLE news ADD COLUMN created_by INTEGER REFERENCES users(id);
ALTER TABLE news ADD COLUMN updated_by INTEGER REFERENCES users(id);

-- Activities
ALTER TABLE activities ADD COLUMN created_by INTEGER REFERENCES users(id);
ALTER TABLE activities ADD COLUMN updated_by INTEGER REFERENCES users(id);

-- Documents
ALTER TABLE documents ADD COLUMN created_by INTEGER REFERENCES users(id);
ALTER TABLE documents ADD COLUMN updated_by INTEGER REFERENCES users(id);

-- Lost items
ALTER TABLE lost_items ADD COLUMN created_by INTEGER REFERENCES users(id);
ALTER TABLE lost_items ADD COLUMN updated_by INTEGER REFERENCES users(id);

-- Varia
ALTER TABLE varia ADD COLUMN created_by INTEGER REFERENCES users(id);
ALTER TABLE varia ADD COLUMN updated_by INTEGER REFERENCES users(id);

-- Create indexes for better query performance
CREATE INDEX IF NOT EXISTS idx_blog_posts_created_by ON blog_posts(created_by);
CREATE INDEX IF NOT EXISTS idx_blog_posts_updated_by ON blog_posts(updated_by);
CREATE INDEX IF NOT EXISTS idx_news_created_by ON news(created_by);
CREATE INDEX IF NOT EXISTS idx_news_updated_by ON news(updated_by);
CREATE INDEX IF NOT EXISTS idx_activities_created_by ON activities(created_by);
CREATE INDEX IF NOT EXISTS idx_activities_updated_by ON activities(updated_by);
CREATE INDEX IF NOT EXISTS idx_documents_created_by ON documents(created_by);
CREATE INDEX IF NOT EXISTS idx_documents_updated_by ON documents(updated_by);
CREATE INDEX IF NOT EXISTS idx_lost_items_created_by ON lost_items(created_by);
CREATE INDEX IF NOT EXISTS idx_lost_items_updated_by ON lost_items(updated_by);
CREATE INDEX IF NOT EXISTS idx_varia_created_by ON varia(created_by);
CREATE INDEX IF NOT EXISTS idx_varia_updated_by ON varia(updated_by);

