<?php

#-------------------------------------------------------------------------------
# DESCRIPTION: This file contains all SQL queries to be used by the PHP scripts
#              The queries are stored in the global $cfg['sql'] associative
#              array.
#-------------------------------------------------------------------------------

$cfg['sql']['create']['users'] = <<<SQL
CREATE TABLE users
(
	user_id		INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
	handle		VARCHAR(15) UNIQUE,
	password	VARCHAR(50),
	first_name	VARCHAR(50),
	last_name	VARCHAR(50),
	birth_date	DATE,
	address		VARCHAR(100),
	city		VARCHAR(50),
	state		VARCHAR(50),
	country		VARCHAR(50),
	zip			VARCHAR(10),
	phone		VARCHAR(20),
	email		VARCHAR(50),
	quote		VARCHAR(200),
	theme		VARCHAR(20) DEFAULT 'default',
	question	VARCHAR(100),
	secret		VARCHAR(100),
	registered	TIMESTAMP,
	rating		FLOAT DEFAULT 300,
	volatility	FLOAT DEFAULT 50
);
SQL;

#-------------------------------------------------------------------------------

$cfg['sql']['create']['results'] = <<<SQL
CREATE TABLE results
(
    user_id		INT NOT NULL REFERENCES users(user_id),
    contest_id  INT NOT NULL REFERENCES contests(contest_id),
    rating      FLOAT NOT NULL,
    volatility  FLOAT NOT NULL,
    old_rat     FLOAT NOT NULL,
    old_vol     FLOAT NOT NULL,
    PRIMARY KEY(user_id, contest_id)
);
SQL;

#-------------------------------------------------------------------------------

$cfg['sql']['create']['groups'] = <<<SQL
CREATE TABLE groups
(
	group_id	INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
	name			VARCHAR(50) UNIQUE
);
SQL;

#-------------------------------------------------------------------------------

$cfg['sql']['create']['categories'] = <<<SQL
CREATE TABLE categories
(
	cat_id		INT PRIMARY KEY NOT NULL,
	name			VARCHAR(100)
);
SQL;

#-------------------------------------------------------------------------------

$cfg['sql']['create']['problems'] = <<<SQL
CREATE TABLE problems
(
	prob_id		VARCHAR(10) NOT NULL,
	contest_id	INT NOT NULL REFERENCES contests(contest_id),
	cat_id		INT REFERENCES categories(cat_id),
	summary		VARCHAR(100),
	content		TEXT,
	weight		INT UNSIGNED,
	time_limit	FLOAT,
    mem_limit	FLOAT,
    version     INT DEFAULT 0,
	PRIMARY KEY(prob_id, contest_id)
);
SQL;

#-------------------------------------------------------------------------------

$cfg['sql']['create']['contests'] = <<<SQL
CREATE TABLE contests
(
	contest_id	INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
	name		VARCHAR(100),
	description	TEXT,
	team_size	INT,
	show_time	DATETIME,
	begin_time	DATETIME,
    end_time	DATETIME,
    tested      BIT NOT NULL DEFAULT 0,
	manager		INT NOT NULL REFERENCES users(user_id),
	closed      BIT NOT NULL DEFAULT 0,
    rules       VARCHAR(100),
    ctype       INT NOT NULL DEFAULT 0
);
SQL;

#-------------------------------------------------------------------------------

$cfg['sql']['create']['teams'] = <<<SQL
CREATE TABLE teams
(
	team_id		INT NOT NULL AUTO_INCREMENT,
	contest_id	INT NOT NULL REFERENCES contests(contest_id),
	name		VARCHAR(15),
	college		VARCHAR(100),
    score       FLOAT,
    last_seen   DATETIME,
	PRIMARY KEY(team_id, contest_id),
	UNIQUE(contest_id, name)
)
SQL;

#-------------------------------------------------------------------------------

$cfg['sql']['create']['members'] = <<<SQL
CREATE TABLE members
(
	contest_id	INT NOT NULL REFERENCES contests(contest_id),
	team_id		INT NOT NULL REFERENCES teams(team_id), 
    user_id		INT NOT NULL REFERENCES users(user_id),
    old_rating  FLOAT,
    new_rating  FLOAT,
	PRIMARY KEY(contest_id, user_id)
)
SQL;

#-------------------------------------------------------------------------------

$cfg['sql']['create']['solutions'] = <<<SQL
CREATE TABLE solutions
(
	team_id		INT NOT NULL REFERENCES teams(team_id),
	prob_id		VARCHAR(10) NOT NULL REFERENCES problems(prob_id),
	contest_id	INT NOT NULL REFERENCES contests(contest_id),
	language	VARCHAR(20),
	open_time	DATETIME,
	submit_time DATETIME,
	source		MEDIUMBLOB,
    submits		INT DEFAULT 0,
    score       FLOAT,
    passed      VARCHAR(100),
	PRIMARY KEY(contest_id, team_id, prob_id)
);
SQL;

#-------------------------------------------------------------------------------

# for autosaving solutions
$cfg['sql']['create']['drafts'] = <<<SQL
CREATE TABLE drafts
(
    user_id		INT NOT NULL REFERENCES users(user_id),
    prob_id		VARCHAR(10) NOT NULL REFERENCES problems(prob_id),
    contest_id	INT NOT NULL REFERENCES contests(contest_id),
    language	VARCHAR(20),
    source		MEDIUMBLOB,
    created     DATETIME NOT NULL,
    PRIMARY KEY(user_id)
);
SQL;

#-------------------------------------------------------------------------------

$cfg['sql']['create']['perms'] = <<<SQL
CREATE TABLE perms
(
	user_id		INT NOT NULL REFERENCES users(user_id),
	group_id	INT NOT NULL REFERENCES groups(group_id),
	PRIMARY KEY(user_id, group_id)
);
SQL;

#-------------------------------------------------------------------------------

$cfg['sql']['create']['views'] = <<<SQL
CREATE TABLE views
(
	group_id	INT NOT NULL REFERENCES groups(group_id),
	view		VARCHAR(50) NOT NULL,
	PRIMARY KEY(group_id, view)
);
SQL;

#-------------------------------------------------------------------------------

$cfg['sql']['create']['forgot'] = <<<SQL
CREATE TABLE forgot
(
	user_id		INT NOT NULL REFERENCES users(user_id),
	password	VARCHAR(50),
	PRIMARY KEY(user_id)
);
SQL;

#-------------------------------------------------------------------------------

# table for holding submitted programs temporarily while they're being tested
$cfg['sql']['create']['submits'] = <<<SQL
CREATE TABLE submits
(
	user_id		INT NOT NULL REFERENCES users(user_id),
	submit_id	INT NOT NULL AUTO_INCREMENT,
	prob_id		VARCHAR(10) NOT NULL REFERENCES problems(prob_id),
	contest_id	INT NOT NULL REFERENCES contests(contest_id),
	source		MEDIUMBLOB,
	language		VARCHAR(20),
	time			DATETIME,
	mode			VARCHAR(20),
    custom      TEXT DEFAULT NULL,
	PRIMARY KEY(user_id, submit_id)
);
SQL;

#-------------------------------------------------------------------------------

# table for holding submitted programs temporarily while they're being tested
$cfg['sql']['create']['bulletin'] = <<<SQL
CREATE TABLE bulletin
(
    post_id     INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    poster_id	INT NOT NULL REFERENCES users(user_id),
    subject     VARCHAR(100),
    message     TEXT,
    level       INT NOT NULL,
    posted      DATETIME,
    addbreaks	BIT NOT NULL DEFAULT 1
);
SQL;


#-------------------------------------------------------------------------------
# Queries: users

$cfg['sql']['users_list'] = 'SELECT user_id, handle, first_name, last_name, email FROM users';
$cfg['sql']['user_by_handle'] = 'SELECT * FROM users WHERE handle = ?';
$cfg['sql']['user_by_id'] = 'SELECT * FROM users WHERE user_id = ?';
$cfg['sql']['user_by_email'] = 'SELECT * FROM users WHERE email = ?';
$cfg['sql']['clean_forgot'] = 'DELETE FROM forgot WHERE user_id = ?';
$cfg['sql']['del_user_by_id'] = 'DELETE FROM users WHERE user_id = ?';

#-------------------------------------------------------------------------------
# Queries: groups

$cfg['sql']['groups_list'] = 'SELECT * FROM groups';
$cfg['sql']['group_by_name'] = 'SELECT * FROM groups WHERE name = ?';
$cfg['sql']['group_by_id'] = 'SELECT * FROM groups WHERE group_id = ?';
$cfg['sql']['del_group_by_id'] = 'DELETE FROM groups WHERE group_id = ?';

#-------------------------------------------------------------------------------
# Queries: views and groups

$cfg['sql']['views_by_group_id'] = 'SELECT * FROM views WHERE group_id = ?';
$cfg['sql']['group_and_view'] = 'SELECT * FROM views WHERE group_id = ? AND view = ?';
$cfg['sql']['del_group_and_view'] = 'DELETE FROM views WHERE group_id = ? AND view = ?';

#-------------------------------------------------------------------------------
# Queries: users and groups

$cfg['sql']['groups_by_user_id'] = 'SELECT * FROM groups, perms' .
		' WHERE groups.group_id = perms.group_id AND user_id = ?';
$cfg['sql']['user_and_group_name'] = 'SELECT * FROM perms, groups WHERE user_id = ? AND groups.group_id = perms.group_id AND groups.name = ?';
$cfg['sql']['user_and_group'] = 'SELECT * FROM perms WHERE user_id = ? AND group_id = ?';
$cfg['sql']['del_user_and_group'] = 'DELETE FROM perms WHERE user_id = ? AND group_id = ?';
$cfg['sql']['users_by_group_name'] = 'SELECT * FROM groups, perms, users WHERE groups.name = ?'.
	' AND groups.group_id = perms.group_id AND perms.user_id = users.user_id';
 
$cfg['sql']['auth_check'] = 'SELECT view FROM views, groups, perms' .
		' WHERE views.group_id = groups.group_id AND perms.group_id = groups.group_id' .
		' AND perms.user_id = ?';
$cfg['sql']['del_user_perms_by_id'] = 'DELETE FROM perms WHERE user_id = ?';

#-------------------------------------------------------------------------------
# Queries: contests

$cfg['sql']['contests_list'] = 'SELECT * FROM contests';
$cfg['sql']['relevant_contests_list'] = 'SELECT *, begin_time < NOW() AS running FROM contests WHERE show_time < NOW() AND end_time > NOW()';
$cfg['sql']['running_contests_list'] = 'SELECT * FROM contests WHERE begin_time < NOW() AND end_time > NOW()';
$cfg['sql']['count_running_contests'] = 'SELECT COUNT(*) AS count FROM contests WHERE begin_time < NOW() AND end_time > NOW() ORDER BY end_time DESC';
$cfg['sql']['past_contests_list'] = 'SELECT * FROM contests WHERE end_time < NOW() ORDER BY end_time DESC';
$cfg['sql']['contest_by_id'] = 'SELECT *, show_time > NOW() AS show_future, begin_time > NOW() AS begin_future, end_time > NOW() AS end_future FROM contests WHERE contest_id = ?';
$cfg['sql']['contest_by_id_safe'] = 'SELECT *, begin_time > NOW() AS begin_future, end_time > NOW() AS end_future ' .
		'FROM contests WHERE contest_id = ? AND show_time < NOW()';
$cfg['sql']['del_contest_by_id'] = 'DELETE FROM contests WHERE contest_id = ?';
$cfg['sql']['contests_by_manager'] = 'SELECT * FROM contests WHERE manager = ?';
$cfg['sql']['contest_time'] = 'SELECT UNIX_TIMESTAMP(end_time)-UNIX_TIMESTAMP(begin_time) AS time, '.
	'UNIX_TIMESTAMP(end_time)-UNIX_TIMESTAMP() AS remain FROM contests WHERE contest_id = ?';
$cfg['sql']['contest_type'] = 'SELECT ctype FROM contests WHERE contest_id = ?';

#-------------------------------------------------------------------------------
# Queries: problems

$cfg['sql']['problems_by_contest_id'] = 'SELECT prob_id, cat_id, summary, weight, time_limit, mem_limit '.
	'FROM problems WHERE contest_id = ? ORDER BY weight ASC';
$cfg['sql']['problem_by_id'] = 'SELECT prob_id, contest_id, cat_id, summary, weight, time_limit, mem_limit, version FROM problems WHERE prob_id = ? AND contest_id = ?';    
$cfg['sql']['problem_content_by_id'] = 'SELECT content FROM problems WHERE prob_id = ? AND contest_id = ?';
$cfg['sql']['del_problem_by_id'] = 'DELETE FROM problems WHERE prob_id = ? AND contest_id = ?';

#-------------------------------------------------------------------------------
# Queries: teams

$cfg['sql']['team_by_name'] = 'SELECT * FROM teams WHERE name = ? AND contest_id = ?';
$cfg['sql']['count_teams_by_contest_id'] = 'SELECT COUNT(team_id) AS count FROM teams WHERE contest_id = ?';
$cfg['sql']['teams_by_contest_id'] = 'SELECT * FROM teams WHERE contest_id = ? ORDER BY score DESC';
$cfg['sql']['count_last_teams_by_contest_id'] = 'SELECT COUNT(team_id) AS count FROM teams '.
'WHERE contest_id = ? AND UNIX_TIMESTAMP(last_seen)+30*60 > UNIX_TIMESTAMP(NOW())';
$cfg['sql']['team_seen'] = 'UPDATE teams SET last_seen = NOW() WHERE contest_id = ? AND team_id = ?';
$cfg['sql']['standings_by_contest_id'] = 'SELECT teams.team_id, teams.name, teams.college, SUM(solutions.score) AS score FROM teams, solutions
WHERE teams.team_id=solutions.team_id AND solutions.contest_id=? AND teams.contest_id=solutions.contest_id
GROUP BY solutions.team_id ORDER BY score DESC';

#-------------------------------------------------------------------------------
# Queries: members

$cfg['sql']['users_by_contest_id'] = 'SELECT * FROM members, teams WHERE contest_id = ?';
$cfg['sql']['user_and_contest'] = 'SELECT * FROM members, teams '.
'WHERE user_id = ? AND members.contest_id = ? AND teams.contest_id=members.contest_id AND members.team_id = teams.team_id';
$cfg['sql']['users_by_team_id'] = 'SELECT handle FROM members, users WHERE members.user_id=users.user_id
AND members.contest_id=? AND members.team_id=?';
	
#-------------------------------------------------------------------------------
# Queries: solutions

$cfg['sql']['solution_by_id'] = 'SELECT *, UNIX_TIMESTAMP(submit_time)-UNIX_TIMESTAMP(open_time) AS elapsed, UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(open_time) AS elapsed2 '.
    'FROM solutions WHERE contest_id = ? AND team_id = ? AND prob_id = ?';
$cfg['sql']['score_by_id'] = 'SELECT score, language '.
    'FROM solutions WHERE contest_id = ? AND team_id = ? AND prob_id = ?';
$cfg['sql']['problems_by_team_id'] = 'SELECT prob_id FROM solutions WHERE team_id = ?';
$cfg['sql']['solutions_by_contest_id'] = 'SELECT teams.name, teams.last_seen, solutions.prob_id, weight, solutions.language, solutions.open_time, solutions.submit_time, solutions.submits, solutions.score, solutions.passed FROM solutions, teams, problems '.
	'WHERE solutions.contest_id = ? AND teams.contest_id=solutions.contest_id AND solutions.team_id = teams.team_id AND problems.prob_id = solutions.prob_id '.
	'ORDER BY weight DESC, submit_time DESC';
$cfg['sql']['open_solution'] = 'INSERT INTO solutions (contest_id, team_id, prob_id, open_time, submits) VALUES (?, ?, ?, NOW(), 0)';
$cfg['sql']['submit_solution'] = 'UPDATE solutions SET submits=submits+1, submit_time=NOW(), language=?, source=?, score=? '.
	'WHERE contest_id = ? AND team_id = ? AND prob_id = ?';
$cfg['sql']['first_solution_open_time'] = 'SELECT MAX(UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(open_time)) AS elapsed FROM solutions WHERE contest_id = ? AND team_id = ?';
	
#-------------------------------------------------------------------------------
# Queries: submits

$cfg['sql']['submit_by_id'] = 'SELECT * FROM submits WHERE submit_id = ? AND user_id = ?';
$cfg['sql']['submit_by_user'] = 'SELECT * FROM submits WHERE user_id = ?';
$cfg['sql']['pending_submits'] = 'SELECT COUNT(*) AS count FROM submits';
$cfg['sql']['pending_user_submits'] = 'SELECT submit_id, time FROM submits WHERE user_id = ?';
$cfg['sql']['insert_submit'] = 'INSERT INTO submits (contest_id, prob_id, user_id, source, language, mode, time) '.
	'values (?, ?, ?, ?, ?, ?, NOW())';
$cfg['sql']['delete_submit'] = 'DELETE FROM submits WHERE submit_id = ? AND user_id = ?';
$cfg['sql']['expire_submits'] = 'DELETE FROM submits WHERE UNIX_TIMESTAMP(time)+? < UNIX_TIMESTAMP(NOW())';

#-------------------------------------------------------------------------------
# Queries: drafts

$cfg['sql']['draft_by_user'] = 'SELECT * FROM drafts WHERE user_id = ?';
$cfg['sql']['delete_draft_by_user'] = 'DELETE FROM drafts WHERE user_id = ?';
$cfg['sql']['update_draft'] = 'UPDATE drafts SET created = NOW() WHERE user_id = ?';
$cfg['sql']['clean_drafts'] = 'DELETE FROM drafts WHERE UNIX_TIMESTAMP(created)+'.
    $cfg['draft']['expiry_time'].' < UNIX_TIMESTAMP(NOW())';

#-------------------------------------------------------------------------------
# Queries: bulletins

$cfg['sql']['bulletin_by_level'] = 'SELECT bulletin.*, users.handle FROM bulletin, users 
WHERE level = ? AND users.user_id = bulletin.poster_id ORDER BY posted DESC';
$cfg['sql']['bulletin_post'] = 'INSERT INTO bulletin (poster_id, subject, message, posted, level, addbreaks)
VALUES (?, ?, ?, NOW(), ?, ?)';
$cfg['sql']['bulletin_by_id'] = 'SELECT * FROM bulletin WHERE post_id = ?';
$cfg['sql']['delete_bulletin_by_id'] = 'DELETE FROM bulletin WHERE post_id = ?';

?>