-- Seed data for crossword puzzle application
-- This file populates the database with initial topics and words

-- Insert topics
INSERT INTO topics (name, description, difficulty) VALUES
('Animals', 'Various animals from around the world', 'easy'),
('Science', 'Scientific terms and concepts', 'medium'),
('Geography', 'Countries, cities, and geographical features', 'medium'),
('Technology', 'Computer and technology terms', 'hard'),
('History', 'Historical events and figures', 'hard'),
('Sports', 'Sports and athletic activities', 'easy')
ON CONFLICT (name) DO NOTHING;

-- Insert words and clues for Animals topic
WITH animal_topic AS (SELECT id FROM topics WHERE name = 'Animals')
INSERT INTO words (word, length, topic_id, difficulty) 
SELECT word_data.word, LENGTH(word_data.word), animal_topic.id, 'easy'
FROM animal_topic,
(VALUES 
    ('DOG'), ('CAT'), ('ELEPHANT'), ('TIGER'), ('WHALE'), 
    ('BUTTERFLY'), ('BIRD'), ('FISH'), ('LION'), ('BEAR'),
    ('RABBIT'), ('HORSE'), ('SHEEP'), ('GOAT'), ('DUCK'),
    ('CHICKEN'), ('SNAKE'), ('TURTLE'), ('FROG'), ('SHARK'),
    ('DOLPHIN'), ('PENGUIN'), ('MONKEY'), ('ZEBRA'), ('GIRAFFE')
) AS word_data(word)
ON CONFLICT (word, topic_id) DO NOTHING;

-- Insert clues for animals
WITH animal_words AS (
    SELECT w.id, w.word 
    FROM words w 
    JOIN topics t ON w.topic_id = t.id 
    WHERE t.name = 'Animals'
)
INSERT INTO clues (word_id, clue_text, difficulty)
SELECT aw.id, clue_data.clue, 'easy'
FROM animal_words aw
JOIN (VALUES 
    ('DOG', 'Man''s best friend'),
    ('CAT', 'Feline pet that purrs'),
    ('ELEPHANT', 'Large mammal with a trunk'),
    ('TIGER', 'Striped big cat'),
    ('WHALE', 'Largest marine mammal'),
    ('BUTTERFLY', 'Colorful flying insect'),
    ('BIRD', 'Flying creature with feathers'),
    ('FISH', 'Aquatic animal with gills'),
    ('LION', 'King of the jungle'),
    ('BEAR', 'Large mammal that hibernates'),
    ('RABBIT', 'Hopping mammal with long ears'),
    ('HORSE', 'Riding animal with hooves'),
    ('SHEEP', 'Woolly farm animal'),
    ('GOAT', 'Horned farm animal'),
    ('DUCK', 'Water bird that quacks'),
    ('CHICKEN', 'Farm bird that lays eggs'),
    ('SNAKE', 'Slithering reptile'),
    ('TURTLE', 'Shelled reptile'),
    ('FROG', 'Amphibian that croaks'),
    ('SHARK', 'Predatory ocean fish'),
    ('DOLPHIN', 'Intelligent marine mammal'),
    ('PENGUIN', 'Flightless Antarctic bird'),
    ('MONKEY', 'Primate that swings in trees'),
    ('ZEBRA', 'Striped African animal'),
    ('GIRAFFE', 'Tallest land animal')
) AS clue_data(word, clue) ON aw.word = clue_data.word;

-- Insert words and clues for Science topic
WITH science_topic AS (SELECT id FROM topics WHERE name = 'Science')
INSERT INTO words (word, length, topic_id, difficulty) 
SELECT word_data.word, LENGTH(word_data.word), science_topic.id, 'medium'
FROM science_topic,
(VALUES 
    ('ATOM'), ('GRAVITY'), ('MOLECULE'), ('PHOTON'), ('CHEMISTRY'),
    ('PHYSICS'), ('BIOLOGY'), ('ELEMENT'), ('OXYGEN'), ('CARBON'),
    ('HYDROGEN'), ('ENERGY'), ('FORCE'), ('VELOCITY'), ('MASS'),
    ('VOLUME'), ('DENSITY'), ('PRESSURE'), ('ELECTRON'), ('PROTON'),
    ('NEUTRON'), ('NUCLEUS'), ('CELL'), ('DNA'), ('PROTEIN')
) AS word_data(word)
ON CONFLICT (word, topic_id) DO NOTHING;

-- Insert clues for science
WITH science_words AS (
    SELECT w.id, w.word 
    FROM words w 
    JOIN topics t ON w.topic_id = t.id 
    WHERE t.name = 'Science'
)
INSERT INTO clues (word_id, clue_text, difficulty)
SELECT sw.id, clue_data.clue, 'medium'
FROM science_words sw
JOIN (VALUES 
    ('ATOM', 'Smallest unit of matter'),
    ('GRAVITY', 'Force that pulls objects down'),
    ('MOLECULE', 'Group of atoms bonded together'),
    ('PHOTON', 'Particle of light'),
    ('CHEMISTRY', 'Study of matter and reactions'),
    ('PHYSICS', 'Study of matter and energy'),
    ('BIOLOGY', 'Study of living organisms'),
    ('ELEMENT', 'Pure chemical substance'),
    ('OXYGEN', 'Gas essential for breathing'),
    ('CARBON', 'Element found in all life'),
    ('HYDROGEN', 'Lightest chemical element'),
    ('ENERGY', 'Capacity to do work'),
    ('FORCE', 'Push or pull on an object'),
    ('VELOCITY', 'Speed with direction'),
    ('MASS', 'Amount of matter in object'),
    ('VOLUME', 'Amount of space occupied'),
    ('DENSITY', 'Mass per unit volume'),
    ('PRESSURE', 'Force per unit area'),
    ('ELECTRON', 'Negatively charged particle'),
    ('PROTON', 'Positively charged particle'),
    ('NEUTRON', 'Neutral atomic particle'),
    ('NUCLEUS', 'Center of an atom'),
    ('CELL', 'Basic unit of life'),
    ('DNA', 'Genetic blueprint molecule'),
    ('PROTEIN', 'Complex biological molecule')
) AS clue_data(word, clue) ON sw.word = clue_data.word;

-- Insert words and clues for Geography topic
WITH geography_topic AS (SELECT id FROM topics WHERE name = 'Geography')
INSERT INTO words (word, length, topic_id, difficulty) 
SELECT word_data.word, LENGTH(word_data.word), geography_topic.id, 'medium'
FROM geography_topic,
(VALUES 
    ('MOUNTAIN'), ('OCEAN'), ('DESERT'), ('CONTINENT'), ('RIVER'),
    ('ISLAND'), ('FOREST'), ('VALLEY'), ('LAKE'), ('BEACH'),
    ('PLATEAU'), ('CANYON'), ('GLACIER'), ('VOLCANO'), ('EQUATOR'),
    ('LATITUDE'), ('CLIMATE'), ('CAPITAL'), ('BORDER'), ('COAST')
) AS word_data(word)
ON CONFLICT (word, topic_id) DO NOTHING;

-- Insert clues for geography
WITH geography_words AS (
    SELECT w.id, w.word 
    FROM words w 
    JOIN topics t ON w.topic_id = t.id 
    WHERE t.name = 'Geography'
)
INSERT INTO clues (word_id, clue_text, difficulty)
SELECT gw.id, clue_data.clue, 'medium'
FROM geography_words gw
JOIN (VALUES 
    ('MOUNTAIN', 'High elevation landform'),
    ('OCEAN', 'Large body of salt water'),
    ('DESERT', 'Dry, arid region'),
    ('CONTINENT', 'Large landmass'),
    ('RIVER', 'Flowing body of water'),
    ('ISLAND', 'Land surrounded by water'),
    ('FOREST', 'Dense area of trees'),
    ('VALLEY', 'Low area between hills'),
    ('LAKE', 'Body of freshwater'),
    ('BEACH', 'Sandy shore by water'),
    ('PLATEAU', 'Elevated flat area'),
    ('CANYON', 'Deep gorge with steep sides'),
    ('GLACIER', 'Moving mass of ice'),
    ('VOLCANO', 'Mountain that erupts'),
    ('EQUATOR', 'Earth''s middle line'),
    ('LATITUDE', 'Distance from equator'),
    ('CLIMATE', 'Long-term weather pattern'),
    ('CAPITAL', 'Main city of country'),
    ('BORDER', 'Boundary between countries'),
    ('COAST', 'Land meeting the sea')
) AS clue_data(word, clue) ON gw.word = clue_data.word;

-- Insert words and clues for Technology topic
WITH technology_topic AS (SELECT id FROM topics WHERE name = 'Technology')
INSERT INTO words (word, length, topic_id, difficulty) 
SELECT word_data.word, LENGTH(word_data.word), technology_topic.id, 'hard'
FROM technology_topic,
(VALUES 
    ('COMPUTER'), ('INTERNET'), ('ALGORITHM'), ('DATABASE'), ('SOFTWARE'),
    ('HARDWARE'), ('NETWORK'), ('CODE'), ('ROBOT'), ('DIGITAL'),
    ('PROCESSOR'), ('MEMORY'), ('KEYBOARD'), ('MONITOR'), ('MOUSE'),
    ('SMARTPHONE'), ('TABLET'), ('LAPTOP'), ('SERVER'), ('CLOUD'),
    ('WEBSITE'), ('EMAIL'), ('BROWSER'), ('SEARCH'), ('DOWNLOAD')
) AS word_data(word)
ON CONFLICT (word, topic_id) DO NOTHING;

-- Insert clues for technology
WITH technology_words AS (
    SELECT w.id, w.word 
    FROM words w 
    JOIN topics t ON w.topic_id = t.id 
    WHERE t.name = 'Technology'
)
INSERT INTO clues (word_id, clue_text, difficulty)
SELECT tw.id, clue_data.clue, 'hard'
FROM technology_words tw
JOIN (VALUES 
    ('COMPUTER', 'Electronic processing device'),
    ('INTERNET', 'Global computer network'),
    ('ALGORITHM', 'Set of rules for solving problems'),
    ('DATABASE', 'Organized collection of data'),
    ('SOFTWARE', 'Computer programs'),
    ('HARDWARE', 'Physical computer components'),
    ('NETWORK', 'Connected system of computers'),
    ('CODE', 'Programming instructions'),
    ('ROBOT', 'Automated machine'),
    ('DIGITAL', 'Using binary data'),
    ('PROCESSOR', 'Computer''s brain'),
    ('MEMORY', 'Data storage component'),
    ('KEYBOARD', 'Input device with keys'),
    ('MONITOR', 'Computer display screen'),
    ('MOUSE', 'Pointing input device'),
    ('SMARTPHONE', 'Portable computing device'),
    ('TABLET', 'Touchscreen computing device'),
    ('LAPTOP', 'Portable computer'),
    ('SERVER', 'Computer that serves data'),
    ('CLOUD', 'Internet-based computing'),
    ('WEBSITE', 'Collection of web pages'),
    ('EMAIL', 'Electronic mail'),
    ('BROWSER', 'Web navigation software'),
    ('SEARCH', 'Look for information'),
    ('DOWNLOAD', 'Transfer data to device')
) AS clue_data(word, clue) ON tw.word = clue_data.word;