-- Migration: Add config_dictionaries table (many-to-many relationship)
-- This script creates the config_dictionaries table linking configs and dictionaries

-- ============================================
-- Table: config_dictionaries
-- ============================================
CREATE TABLE IF NOT EXISTS config_dictionaries (
    config_id INTEGER NOT NULL REFERENCES configs(id) ON DELETE CASCADE,
    dictionary_id INTEGER NOT NULL REFERENCES dictionaries(id) ON DELETE CASCADE,
    PRIMARY KEY (config_id, dictionary_id)
);

-- Create indexes for better query performance
CREATE INDEX IF NOT EXISTS idx_config_dictionaries_config_id ON config_dictionaries(config_id);
CREATE INDEX IF NOT EXISTS idx_config_dictionaries_dictionary_id ON config_dictionaries(dictionary_id);

-- ============================================
-- Comments for documentation
-- ============================================
COMMENT ON TABLE config_dictionaries IS 'Many-to-many relationship table linking configs and dictionaries. If no dictionaries are selected for a config, all dictionaries will be used.';