docs/database-schema.sql

-- Crossword Puzzle Webapp Database Schema

-- Topics table
CREATE TABLE topics (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE,
  description TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Words table  
CREATE TABLE words (
  id SERIAL PRIMARY KEY,
  word VARCHAR(20) NOT NULL,
  length INTEGER NOT NULL,
  difficulty_level INTEGER DEFAULT 1 CHECK (difficulty_level BETWEEN 1 AND 3),
  topic_id INTEGER REFERENCES topics(id) ON DELETE CASCADE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_topic_length (topic_id, length),
  INDEX idx_difficulty (difficulty_level)
);

-- Clues table
CREATE TABLE clues (
  id SERIAL PRIMARY KEY,
  word_id INTEGER REFERENCES words(id) ON DELETE CASCADE,
  clue_text TEXT NOT NULL,
  difficulty INTEGER DEFAULT 1 CHECK (difficulty BETWEEN 1 AND 3),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Optional: Generated puzzles cache table
CREATE TABLE generated_puzzles (
  id SERIAL PRIMARY KEY,
  grid_data JSONB NOT NULL,
  clues_data JSONB NOT NULL,
  topics TEXT[] NOT NULL,
  difficulty INTEGER DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  expires_at TIMESTAMP DEFAULT (CURRENT_TIMESTAMP + INTERVAL '24 hours')
);

-- Sample data seeds
INSERT INTO topics (name, description) VALUES 
  ('Animals', 'Creatures from the animal kingdom'),
  ('Science', 'Scientific terms and concepts'),
  ('Geography', 'Places, landforms, and geographical features'),
  ('Technology', 'Computing and technology terms'),
  ('History', 'Historical events, people, and periods'),
  ('Sports', 'Sports, games, and athletic activities');

-- Sample words for Animals topic
INSERT INTO words (word, length, difficulty_level, topic_id) VALUES 
  ('DOG', 3, 1, 1),
  ('CAT', 3, 1, 1),
  ('ELEPHANT', 8, 2, 1),
  ('TIGER', 5, 1, 1),
  ('WHALE', 5, 2, 1),
  ('BUTTERFLY', 9, 3, 1),
  ('PENGUIN', 7, 2, 1),
  ('GIRAFFE', 7, 2, 1);

-- Sample clues for the words
INSERT INTO clues (word_id, clue_text, difficulty) VALUES 
  (1, 'Man''s best friend', 1),
  (2, 'Feline pet that purrs', 1),
  (3, 'Largest land mammal', 2),
  (4, 'Striped big cat', 1),
  (5, 'Largest marine mammal', 2),
  (6, 'Colorful insect with wings', 3),
  (7, 'Black and white Antarctic bird', 2),
  (8, 'Tallest animal in the world', 2);