-- Add support for different field types (file, api, csv, json, excel)
-- 005_add_field_types.sql

DO $$ 
BEGIN
  -- Add field_type column
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'structured_fields' AND column_name = 'field_type') THEN
    ALTER TABLE structured_fields ADD COLUMN field_type VARCHAR(50) DEFAULT 'database';
  END IF;
  
  -- Add file_path column
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'structured_fields' AND column_name = 'file_path') THEN
    ALTER TABLE structured_fields ADD COLUMN file_path VARCHAR(500);
  END IF;
  
  -- Add file_name column
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'structured_fields' AND column_name = 'file_name') THEN
    ALTER TABLE structured_fields ADD COLUMN file_name VARCHAR(255);
  END IF;
  
  -- Add api_endpoint column
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'structured_fields' AND column_name = 'api_endpoint') THEN
    ALTER TABLE structured_fields ADD COLUMN api_endpoint VARCHAR(500);
  END IF;
  
  -- Add field_name column (generic field name for non-database fields)
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'structured_fields' AND column_name = 'field_name') THEN
    ALTER TABLE structured_fields ADD COLUMN field_name VARCHAR(255);
  END IF;
  
  -- Add created_by column if missing
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'structured_fields' AND column_name = 'created_by') THEN
    ALTER TABLE structured_fields ADD COLUMN created_by UUID REFERENCES users(id) ON DELETE SET NULL;
  END IF;
END $$;

-- Add indexes for new columns
CREATE INDEX IF NOT EXISTS idx_structured_fields_field_type ON structured_fields(field_type);
CREATE INDEX IF NOT EXISTS idx_structured_fields_file_path ON structured_fields(file_path);
CREATE INDEX IF NOT EXISTS idx_structured_fields_api_endpoint ON structured_fields(api_endpoint);
CREATE INDEX IF NOT EXISTS idx_structured_fields_field_name ON structured_fields(field_name);

