-- Migration 001: Add audit columns to all tables
-- Run this migration to add updated_at and updated_by columns

-- =============================================================================
-- USERS TABLE
-- =============================================================================
-- Already has created_at, add updated_at
ALTER TABLE users
  ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

-- =============================================================================
-- PROJECTS TABLE
-- =============================================================================
-- Already has created_at and updated_at
-- Add updated_by for tracking who made changes
ALTER TABLE projects
  ADD COLUMN IF NOT EXISTS updated_by INT NULL,
  ADD CONSTRAINT fk_projects_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL;

-- =============================================================================
-- PROJECT_NODES TABLE
-- =============================================================================
ALTER TABLE project_nodes
  ADD COLUMN IF NOT EXISTS created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

-- =============================================================================
-- PROJECT_EDGES TABLE
-- =============================================================================
ALTER TABLE project_edges
  ADD COLUMN IF NOT EXISTS created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

-- =============================================================================
-- COMPONENT_LIBRARY TABLE
-- =============================================================================
-- Already has created_at and created_by
ALTER TABLE component_library
  ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  ADD COLUMN IF NOT EXISTS updated_by INT NULL,
  ADD CONSTRAINT fk_component_library_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL;

-- =============================================================================
-- COMPONENT_FREQ_DATA TABLE
-- =============================================================================
-- Already has imported_at
-- Add source tracking
ALTER TABLE component_freq_data
  ADD COLUMN IF NOT EXISTS imported_by INT NULL,
  ADD COLUMN IF NOT EXISTS source_file VARCHAR(255) NULL COMMENT 'Original CSV filename',
  ADD CONSTRAINT fk_component_freq_data_imported_by FOREIGN KEY (imported_by) REFERENCES users(id) ON DELETE SET NULL;

-- =============================================================================
-- COMPONENT_S2P_FILES TABLE
-- =============================================================================
-- Already has uploaded_at and uploaded_by
ALTER TABLE component_s2p_files
  ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
