-- Categories within a pageant CREATE TABLE categories ( id SERIAL PRIMARY KEY, pageant_id INT REFERENCES pageants(id), name TEXT -- 'Landscape', 'Wildlife', 'Macro' );
-- Entries CREATE TABLE entries ( id SERIAL PRIMARY KEY, user_id UUID REFERENCES users(id), pageant_id INT REFERENCES pageants(id), category_id INT REFERENCES categories(id), media_url TEXT, title TEXT, description TEXT, submitted_at TIMESTAMP, is_approved BOOLEAN ); enature pageant
-- Votes (public) CREATE TABLE votes ( id SERIAL PRIMARY KEY, entry_id INT REFERENCES entries(id), user_id UUID REFERENCES users(id), score INT CHECK (score BETWEEN 1 AND 10), created_at TIMESTAMP, UNIQUE(entry_id, user_id) ); -- Categories within a pageant CREATE TABLE categories