Andrei
95070e5369
Features added: - Database schema for pages and media files with content types (Rich Text, HTML, Markdown) - Admin API routes for full page CRUD operations - Image upload functionality with file management - Rich text editor using TinyMCE with image insertion - Admin interface for creating/editing pages with SEO options - Dynamic navigation and footer integration - Public page display routes with proper SEO metadata - Support for featured images and content excerpts Admin features: - Create/edit/delete pages with rich content editor - Upload and manage images through media library - Configure pages to appear in navigation or footer - Set page status (Draft, Published, Archived) - SEO title and description management - Real-time preview of content changes 🤖 Generated with [Claude Code](https://claude.ai/code) Co-Authored-By: Claude <noreply@anthropic.com>
67 lines
2.1 KiB
PL/PgSQL
67 lines
2.1 KiB
PL/PgSQL
-- Enable required extensions
|
|
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
|
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
|
|
CREATE EXTENSION IF NOT EXISTS "pg_trgm";
|
|
CREATE EXTENSION IF NOT EXISTS "vector";
|
|
|
|
-- Create cache table for Bible verses
|
|
CREATE UNLOGGED TABLE IF NOT EXISTS verse_cache (
|
|
key VARCHAR(255) PRIMARY KEY,
|
|
value TEXT,
|
|
expires_at TIMESTAMP,
|
|
created_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
-- Function to create full-text search index (run after Prisma migration)
|
|
CREATE OR REPLACE FUNCTION setup_fulltext_search()
|
|
RETURNS void AS $$
|
|
BEGIN
|
|
-- Create GIN index for full-text search on Bible verses
|
|
CREATE INDEX IF NOT EXISTS verse_text_gin_idx ON "BibleVerse" USING gin(to_tsvector('english', text));
|
|
CREATE INDEX IF NOT EXISTS verse_text_trigram_idx ON "BibleVerse" USING gin(text gin_trgm_ops);
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Function for verse search with full-text search
|
|
CREATE OR REPLACE FUNCTION search_verses(search_query TEXT, limit_count INT DEFAULT 10)
|
|
RETURNS TABLE(
|
|
verse_id TEXT,
|
|
book_name TEXT,
|
|
chapter_num INT,
|
|
verse_num INT,
|
|
verse_text TEXT,
|
|
rank REAL
|
|
) AS $$
|
|
BEGIN
|
|
RETURN QUERY
|
|
SELECT
|
|
v.id::TEXT,
|
|
b.name,
|
|
c."chapterNum",
|
|
v."verseNum",
|
|
v.text,
|
|
CASE
|
|
WHEN to_tsvector('english', v.text) @@ plainto_tsquery('english', search_query) THEN
|
|
ts_rank(to_tsvector('english', v.text), plainto_tsquery('english', search_query))
|
|
WHEN v.text ILIKE '%' || search_query || '%' THEN 0.5
|
|
ELSE similarity(v.text, search_query)
|
|
END as rank
|
|
FROM "BibleVerse" v
|
|
JOIN "BibleChapter" c ON v."chapterId" = c.id
|
|
JOIN "BibleBook" b ON c."bookId" = b.id
|
|
WHERE
|
|
to_tsvector('english', v.text) @@ plainto_tsquery('english', search_query)
|
|
OR v.text ILIKE '%' || search_query || '%'
|
|
OR similarity(v.text, search_query) > 0.1
|
|
ORDER BY rank DESC, b."orderNum", c."chapterNum", v."verseNum"
|
|
LIMIT limit_count;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Session cleanup function
|
|
CREATE OR REPLACE FUNCTION cleanup_expired_sessions()
|
|
RETURNS void AS $$
|
|
BEGIN
|
|
DELETE FROM "Session" WHERE "expiresAt" < NOW();
|
|
END;
|
|
$$ LANGUAGE plpgsql; |