File size: 3,395 Bytes
d9a16d6 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 |
-- Initial database schema for crossword puzzle application
-- This migration creates the basic tables for topics, words, and clues
CREATE TABLE IF NOT EXISTS topics (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL UNIQUE,
description TEXT,
difficulty VARCHAR(20) DEFAULT 'medium' CHECK (difficulty IN ('easy', 'medium', 'hard')),
word_count INTEGER DEFAULT 0,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS words (
id SERIAL PRIMARY KEY,
word VARCHAR(50) NOT NULL,
length INTEGER NOT NULL,
topic_id INTEGER REFERENCES topics(id) ON DELETE CASCADE,
difficulty VARCHAR(20) DEFAULT 'medium' CHECK (difficulty IN ('easy', 'medium', 'hard')),
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT unique_word_topic UNIQUE (word, topic_id)
);
CREATE TABLE IF NOT EXISTS clues (
id SERIAL PRIMARY KEY,
word_id INTEGER REFERENCES words(id) ON DELETE CASCADE,
clue_text TEXT NOT NULL,
difficulty VARCHAR(20) DEFAULT 'medium' CHECK (difficulty IN ('easy', 'medium', 'hard')),
is_primary BOOLEAN DEFAULT true,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS generated_puzzles (
id SERIAL PRIMARY KEY,
grid_data JSONB NOT NULL,
clues_data JSONB NOT NULL,
metadata JSONB,
topics TEXT[] NOT NULL,
difficulty VARCHAR(20) DEFAULT 'medium',
word_count INTEGER,
grid_size INTEGER,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
expires_at TIMESTAMP WITH TIME ZONE DEFAULT (CURRENT_TIMESTAMP + INTERVAL '24 hours')
);
-- Indexes for better query performance
CREATE INDEX IF NOT EXISTS idx_words_topic_id ON words(topic_id);
CREATE INDEX IF NOT EXISTS idx_words_length ON words(length);
CREATE INDEX IF NOT EXISTS idx_words_difficulty ON words(difficulty);
CREATE INDEX IF NOT EXISTS idx_clues_word_id ON clues(word_id);
CREATE INDEX IF NOT EXISTS idx_clues_primary ON clues(is_primary);
CREATE INDEX IF NOT EXISTS idx_puzzles_topics ON generated_puzzles USING GIN(topics);
CREATE INDEX IF NOT EXISTS idx_puzzles_difficulty ON generated_puzzles(difficulty);
CREATE INDEX IF NOT EXISTS idx_puzzles_expires ON generated_puzzles(expires_at);
-- Function to update word count in topics table
CREATE OR REPLACE FUNCTION update_topic_word_count()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
UPDATE topics
SET word_count = word_count + 1, updated_at = CURRENT_TIMESTAMP
WHERE id = NEW.topic_id;
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
UPDATE topics
SET word_count = word_count - 1, updated_at = CURRENT_TIMESTAMP
WHERE id = OLD.topic_id;
RETURN OLD;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
-- Trigger to automatically update word count
CREATE TRIGGER trigger_update_word_count
AFTER INSERT OR DELETE ON words
FOR EACH ROW
EXECUTE FUNCTION update_topic_word_count();
-- Function to clean up expired puzzles
CREATE OR REPLACE FUNCTION cleanup_expired_puzzles()
RETURNS INTEGER AS $$
DECLARE
deleted_count INTEGER;
BEGIN
DELETE FROM generated_puzzles WHERE expires_at < CURRENT_TIMESTAMP;
GET DIAGNOSTICS deleted_count = ROW_COUNT;
RETURN deleted_count;
END;
$$ LANGUAGE plpgsql; |