crossword-app/database/migrations/001_initial_schema.sql

-- 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;