schema.sql

-- Tables --
CREATE TABLE Users (
	id TEXT PRIMARY KEY,
	dayPoints INTEGER,
	match TEXT,
	remindMinutes INTEGER,
	reminded INTEGER DEFAULT 0,
	matchPointsHistory TEXT
);
CREATE TABLE SupercellPlayers (
	tag TEXT NOT NULL,
	type INTEGER NOT NULL,
	userId TEXT NOT NULL,
	name TEXT NOT NULL,
	notifications INTEGER DEFAULT 0,
	data TEXT,
	active BOOLEAN NOT NULL DEFAULT FALSE,
	PRIMARY KEY (tag, type),
	UNIQUE (tag, userId)
);
CREATE TABLE Predictions (
	matchId TEXT NOT NULL,
	userId TEXT NOT NULL REFERENCES Users(id) ON DELETE CASCADE,
	prediction TEXT NOT NULL,
	PRIMARY KEY (userId, matchId)
);
CREATE TABLE Reminders (
	id TEXT NOT NULL,
	timestamp INTEGER NOT NULL,
	userId TEXT NOT NULL,
	remind TEXT NOT NULL,
	PRIMARY KEY (id, userId)
);
CREATE TABLE Tournaments (
	name TEXT NOT NULL,
	flags INTEGER NOT NULL,
	game INTEGER NOT NULL,
	logChannel TEXT NOT NULL,
	guildId TEXT NOT NULL,
	registrationMode INTEGER NOT NULL DEFAULT 0,
	rounds TEXT NOT NULL,
	statusFlags INTEGER NOT NULL DEFAULT 0,
	team INTEGER NOT NULL DEFAULT 1,
	bracketsTime INTEGER,
	categoryId TEXT,
	channelName TEXT,
	channelsTime INTEGER,
	endedCategoryId TEXT,
	endedChannelName TEXT,
	matchMessageLink TEXT,
	minPlayers INTEGER,
	maxPlayers INTEGER,
	registrationChannel TEXT,
	registrationChannelName TEXT,
	registrationEnd INTEGER,
	registrationTemplateLink TEXT,
	registrationRole TEXT,
	registrationStart INTEGER,
	registrationMessage TEXT,
	roundType INTEGER,
	currentRound INTEGER,
	participantCount INTEGER NOT NULL DEFAULT 0,
	workflowId TEXT,
	id INTEGER PRIMARY KEY AUTOINCREMENT
);
CREATE TABLE TournamentTeams (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE Participants (
	tournamentId INTEGER NOT NULL REFERENCES Tournaments(id) ON DELETE CASCADE,
	userId TEXT NOT NULL,
	tag TEXT,
	name TEXT,
	team INTEGER REFERENCES TournamentTeams(id) ON DELETE CASCADE,
	UNIQUE (tournamentId, tag),
	FOREIGN KEY (tag, userId) REFERENCES SupercellPlayers(tag, userId) ON DELETE RESTRICT,
	PRIMARY KEY (tournamentId, userId)
);
CREATE TABLE Matches (
	id INTEGER,
	tournamentId INTEGER NOT NULL REFERENCES Tournaments(id) ON DELETE CASCADE,
	status INTEGER NOT NULL DEFAULT 0,
	user1 TEXT NOT NULL,
	user2 TEXT,
	channelId TEXT,
	result1 INTEGER,
	result2 INTEGER,
	PRIMARY KEY (id, tournamentId),
	FOREIGN KEY (tournamentId, user1) REFERENCES Participants(tournamentId, userId) ON DELETE RESTRICT,
	FOREIGN KEY (tournamentId, user2) REFERENCES Participants(tournamentId, userId) ON DELETE RESTRICT
);
-- Indexes --
CREATE INDEX SupercellPlayersNotificationsIndex ON SupercellPlayers(notifications)
WHERE notifications > 0;
CREATE INDEX SupercellPlayersIndex ON SupercellPlayers(userId, type, active, tag, name);
CREATE INDEX User1Matches ON Matches(tournamentId, user1);
CREATE INDEX User2Matches ON Matches(tournamentId, user2);
CREATE UNIQUE INDEX ChannelMatches ON Matches(channelId);
CREATE INDEX ParticipantsPlayerIndex ON Participants(tag, userId);
-- Triggers --
CREATE TRIGGER IncreaseParticipantCount
AFTER
INSERT ON Participants BEGIN
UPDATE Tournaments
SET participantCount = participantCount + 1
WHERE id = NEW.tournamentId;
END;
CREATE TRIGGER DecreaseParticipantCount
AFTER DELETE ON Participants BEGIN
UPDATE Tournaments
SET participantCount = participantCount - 1
WHERE id = OLD.tournamentId;
END;