-- PostgreSQL database schema for free open song contest idea
-- as described here: http://sivers.org/song-contest-idea

BEGIN;

CREATE SCHEMA contest;
SET search_path = contest;

CREATE TABLE genres (
	code varchar(16) primary key
);

CREATE TABLE songs (
	id serial primary key,
	person_id integer not null,
	lang char(2) not null default 'en',
	artist text,
	title text,
	soundcloud_url text,
	lyrics text,
	genre varchar(16) REFERENCES genres,
	created_at timestamp with time zone not null default CURRENT_TIMESTAMP
);
CREATE INDEX songs_person ON songs(person_id);
CREATE INDEX songs_genre ON songs(genre);

CREATE TABLE votes (
	id serial primary key,
	person_id integer not null,
	song_id integer not null REFERENCES songs,
	vote integer,
	comment text,
	created_at timestamp with time zone not null default CURRENT_TIMESTAMP,
	UNIQUE (person_id, song_id)
);
CREATE INDEX votes_person ON votes(person_id);
CREATE INDEX votes_song ON votes(song_id);

CREATE TABLE companies (
	id serial primary key,
	person_id integer not null,
	name text,
	url text
);

CREATE TABLE prizes (
	id serial primary key,
	company_id integer not null REFERENCES companies,
	name text,
	description text,  -- English
	created_at timestamp with time zone not null default CURRENT_TIMESTAMP,
	song_id integer REFERENCES songs  -- NULL until claimed
);
CREATE INDEX prizes_company ON prizes(company_id);
CREATE INDEX prizes_song ON prizes(song_id);

CREATE TABLE wishes (
	song_id integer not null REFERENCES songs,
	prize_id integer not null REFERENCES prizes,
	PRIMARY KEY (song_id, prize_id)
);

CREATE TABLE winners (   -- cache/summary
	id serial primary key,
	month char(7) not null,
	genre varchar(16) not null REFERENCES genres,
	song_id integer not null REFERENCES songs,
	votes integer not null,
	average numeric
);
CREATE INDEX winner_month ON winners(month);
CREATE INDEX winner_genre ON winners(genre);

COMMIT;