-- Migration: Clean up document_metadata table
-- Date: 2025-10-04
-- Description: Remove legacy meta_key/meta_value columns, keep only metadata_key/metadata_value

-- Step 1: Copy any data from old columns to new columns (if not already there)
UPDATE document_metadata 
SET metadata_key = meta_key,
    metadata_value = meta_value
WHERE metadata_key IS NULL 
  AND meta_key IS NOT NULL;

-- Step 2: Drop the legacy columns
ALTER TABLE document_metadata 
  DROP COLUMN IF EXISTS meta_key,
  DROP COLUMN IF EXISTS meta_value;

-- Step 3: Make metadata_key NOT NULL (it should already be populated)
ALTER TABLE document_metadata 
  ALTER COLUMN metadata_key SET NOT NULL;

-- Verification query
SELECT 
  COUNT(*) as total_rows,
  COUNT(metadata_key) as rows_with_key,
  COUNT(metadata_value) as rows_with_value
FROM document_metadata;
