-- Migration: Add dictionaries table and dictionary_id to words
-- This script creates the dictionaries table and adds dictionary_id field to words table

-- ============================================
-- Table: dictionaries
-- ============================================
CREATE TABLE IF NOT EXISTS dictionaries (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

-- Insert default dictionary "Все слова" with id = 0
-- Note: PostgreSQL SERIAL starts from 1, so we need to use a workaround
-- First, set the sequence to allow inserting 0, then insert, then reset sequence
DO $$
BEGIN
    -- Set sequence to -1 so next value will be 0
    PERFORM setval('dictionaries_id_seq', -1, false);
    
    -- Insert the default dictionary with id = 0
    INSERT INTO dictionaries (id, name) 
    VALUES (0, 'Все слова')
    ON CONFLICT (id) DO NOTHING;
    
    -- Set the sequence to start from 1 (so next auto-increment will be 1)
    PERFORM setval('dictionaries_id_seq', 1, false);
END $$;

-- ============================================
-- Alter words table: Add dictionary_id column
-- ============================================
ALTER TABLE words 
ADD COLUMN IF NOT EXISTS dictionary_id INTEGER DEFAULT 0 REFERENCES dictionaries(id);

-- Update all existing words to have dictionary_id = 0
UPDATE words 
SET dictionary_id = 0 
WHERE dictionary_id IS NULL;

-- Make dictionary_id NOT NULL after setting default values
ALTER TABLE words 
ALTER COLUMN dictionary_id SET NOT NULL,
ALTER COLUMN dictionary_id SET DEFAULT 0;

-- Create index on dictionary_id for better join performance
CREATE INDEX IF NOT EXISTS idx_words_dictionary_id ON words(dictionary_id);

-- ============================================
-- Comments for documentation
-- ============================================
COMMENT ON TABLE dictionaries IS 'Dictionaries table storing dictionary information';
COMMENT ON COLUMN words.dictionary_id IS 'Reference to dictionary. Default is 0 (Все слова)';