/* ***** BEGIN LICENSE BLOCK *****
 * Version: MPL 1.1
 *
 * The contents of this file are subject to the Mozilla Public License Version
 * 1.1 (the "License"); you may not use this file except in compliance with
 * the License. You may obtain a copy of the License at
 * http://www.mozilla.org/MPL/
 *
 * Software distributed under the License is distributed on an "AS IS" basis,
 * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
 * for the specific language governing rights and limitations under the
 * License.
 *
 * The Original Code is Boogie.
 *
 * The Initial Developer of the Original Code is
 * Ondrej Donek.
 * Portions created by the Initial Developer are Copyright (C) 2008
 * the Initial Developer. All Rights Reserved.
 *
 * Contributor(s):
 *
 * ***** END LICENSE BLOCK ***** */


// Constants with SQL queries

const SET_DB_ENCODING = "PRAGMA encoding = \"UTF-8\"; ";

const CREATE_BUGS_DB =
"CREATE TABLE [bugs] (" +
"[idb] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
"[idt] INTEGER NULL," +
"[idp] INTEGER NULL," +
"[idc] INTEGER NULL," +
"[idd] INTEGER NULL," +
"[ids] INTEGER NULL," +
"[idr] INTEGER NULL," +
"[idsv] INTEGER NULL," +
"[idpr] INTEGER NULL," +
"[idv1] INTEGER NULL," +
"[idv2] INTEGER NULL," +
"[since] TIMESTAMP DEFAULT CURRENT_TIMESTAMP NULL" +
")";

const CREATE_BUGS_TYPES_DB =
"CREATE TABLE [types] (" +
"[idt] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
"[name] VARCHAR(122) NOT NULL," +
"[description] VARCHAR(255) NOT NULL" +
");";

const CREATE_BUGS_TYPES_DB_DATA = 
    ["INSERT INTO [types] ([name], [description]) VALUES ('bug', '');",
     "INSERT INTO [types] ([name], [description]) VALUES ('enhancement', '');"];

const CREATE_BUGS_STATUSES_DB =
"CREATE TABLE [statuses] (" + 
"[ids] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," + 
"[name] VARCHAR(122) NOT NULL," + 
"[description] VARCHAR(255) NULL," + 
"[color] VARCHAR(7) NULL," +
"UNIQUE(name)" +
");";

const CREATE_BUGS_STATUSES_DB_DATA = 
    ["INSERT INTO [statuses] ([name], [description], [color]) VALUES ('open', 'Opened bug.', '#FFCEE7');", 
     "INSERT INTO [statuses] ([name], [description], [color]) VALUES ('in progress', 'Bug which we are working on', '#BBDDFF');",
     "INSERT INTO [statuses] ([name], [description], [color]) VALUES ('waiting', 'Waiting bug.', '#C6ECC6');",
     "INSERT INTO [statuses] ([name], [description], [color]) VALUES ('closed', 'Closed bug.', '#CCCCCC');"];

const CREATE_BUGS_RESOLUTIONS_DB =
"CREATE TABLE [resolutions] (" +
"[idr] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," +
"[name] VARCHAR(122) NOT NULL," +
"[description] VARCHAR(255) NULL," +
"UNIQUE(name)" +
");";

const CREATE_BUGS_RESOLUTIONS_DB_DATA =
    ["INSERT INTO [resolutions] ([name], [description]) VALUES (\"not resolved\", \"\");",
     "INSERT INTO [resolutions] ([name], [description]) VALUES (\"fixed\", \"\");",
     "INSERT INTO [resolutions] ([name], [description]) VALUES (\"partially fixed\", \"\");",
     "INSERT INTO [resolutions] ([name], [description]) VALUES (\"won't fix\", \"\");",
     "INSERT INTO [resolutions] ([name], [description]) VALUES (\"not reproducible\", \"\");",
     "INSERT INTO [resolutions] ([name], [description]) VALUES (\"duplicate\", \"\");",
     "INSERT INTO [resolutions] ([name], [description]) VALUES (\"by design\", \"\");",
     "INSERT INTO [resolutions] ([name], [description]) VALUES (\"invalid\", \"\");"];

const CREATE_BUGS_SEVERITIES_DB =
"CREATE TABLE [severities] (" +
"[idsv] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," +
"[name] VARCHAR(122) NOT NULL," +
"[description] VARCHAR(255) NULL," +
"UNIQUE(name)" +
");";

const CREATE_BUGS_SEVERITIES_DB_DATA =
    ["INSERT INTO [severities] ([name], [description]) VALUES (\"N/A\", \"\");",
     "INSERT INTO [severities] ([name], [description]) VALUES (\"critical\", \"\");",
     "INSERT INTO [severities] ([name], [description]) VALUES (\"high\", \"\");",
     "INSERT INTO [severities] ([name], [description]) VALUES (\"low\", \"\");",
     "INSERT INTO [severities] ([name], [description]) VALUES (\"trivial\", \"\");"];

const CREATE_BUGS_PRIORITIES_DB =
"CREATE TABLE [priorities] (" + 
"[idpr] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," + 
"[name] VARCHAR(122) NULL," + 
"[description] VARCHAR(255) NULL," +
"UNIQUE(name)" +
");";

const CREATE_BUGS_PRIORITIES_DB_DATA =
    ["INSERT INTO [priorities] ([name], [description]) VALUES (\"N/A\", \"\");", 
     "INSERT INTO [priorities] ([name], [description]) VALUES (\"showstopper\", \"\");", 
     "INSERT INTO [priorities] ([name], [description]) VALUES (\"high\", \"\");",
     "INSERT INTO [priorities] ([name], [description]) VALUES (\"low\", \"\");",
     "INSERT INTO [priorities] ([name], [description]) VALUES (\"bonus\", \"\");"];

const CREATE_BUGS_POSTS_DB =
"CREATE TABLE [posts] (" + 
"[idpo] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," + 
"[idb] INTEGER NULL," + 
"[parent_id] INTEGER NULL," + 
"[summary] TEXT NULL," + 
"[text] TEXT NULL," + 
"[since] TIMESTAMP DEFAULT CURRENT_TIMESTAMP NULL" + 
");";

const CREATE_BUGS_POSTS_ATTACHEMENTS_DB =
"CREATE TABLE [posts_attachements] (" + 
"[idpatt] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," + 
"[idpo] INTEGER NULL," + 
"[name] VARCHAR(255) NULL," + 
"[mime] VARCHAR(255) NULL," + 
"[data] BLOB NULL" + 
");";

const CREATE_BUGS_DEVELOPERS_DB =
"CREATE TABLE [developers] (" + 
"[idd] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," + 
"[firstname] VARCHAR(255) NULL," + 
"[surname] VARCHAR(255) NULL," + 
"[email] VARCHAR(255) NULL," +
"[im] VARCHAR(255) NULL," +
"[summary] TEXT NULL" +
");";

const CREATE_BUGS_PROJECTS_DB =
"CREATE TABLE [projects] (" + 
"[idp] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," + 
"[idd] INTEGER NULL," + 
"[name] VARCHAR(255) NULL," + 
"[description] TEXT NULL," +
"[homepage_uri] VARCHAR(255) NULL," +
"[repository_uri] VARCHAR(255) NULL," +
"UNIQUE(name)" +
");";

const CREATE_BUGS_PROJECTS_COMPONENTS_DB =
"CREATE TABLE [components] (" + 
"[idc] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," + 
"[idp] INTEGER NULL," + 
"[name] VARCHAR(255) NULL," + 
"[description] TEXT NULL," +
"UNIQUE(name)" +
");";

const CREATE_BUGS_PROJECTS_VERSIONS_DB =
"CREATE TABLE [versions] (" + 
"[idv] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," + 
"[idp] INTEGER NULL," + 
"[name] VARCHAR(255) NULL," + 
"[description] TEXT NULL," +
"UNIQUE(name)" +
");";

const CREATE_BUGS_NOTES_DB =
"CREATE TABLE [notes] (" + 
"[idn] INTEGER PRIMARY KEY AUTOINCREMENT NULL," + 
"[idd] INTEGER NULL," + 
"[idp] INTEGER NULL," + 
"[summary] TEXT NULL," + 
"[text] TEXT NULL," + 
"[since] TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP NULL" + 
");";

const CREATE_VIEW_BUGS_FULL =
"CREATE VIEW [bugs_full] AS " +
"SELECT " +
" bugs.idb AS [id], " +
" (SELECT types.name FROM types WHERE types.idt = bugs.idt) AS [type], " +
" (SELECT projects.name FROM projects WHERE projects.idp = bugs.idp) AS [project], " +
" (SELECT components.name FROM components WHERE components.idc = bugs.idc) AS [component], " +
" (SELECT developers.surname FROM developers WHERE developers.idd = bugs.idd) AS [developer], " +
" (SELECT statuses.name FROM statuses WHERE statuses.ids = bugs.ids) AS [status], " +
" (SELECT resolutions.name FROM resolutions WHERE resolutions.idr = bugs.idr) AS [resolution], " +
" (SELECT severities.name FROM severities WHERE severities.idsv = bugs.idsv) AS [severity], " +
" (SELECT priorities.name FROM priorities WHERE priorities.idpr = bugs.idpr) AS [priority], " +
" (SELECT versions.name FROM versions WHERE versions.idv = bugs.idv1) AS [version1], " +
" (SELECT versions.name FROM versions WHERE versions.idv = bugs.idv2) AS [version2], " +
" bugs.since AS [since]" +
"FROM bugs WHERE 1";

// ===========================================================================

// Bugs
const INSERT_BUG  = "INSERT INTO bugs (idt, idp, idc, idd, ids, idr, idsv, idpr, idv1, idv1, since) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10) ";
const SELECT_BUG  = "SELECT * FROM bugs WHERE idb=?1 LIMIT 1 ";
const BUGS_COUNT  = "SELECT count(idb) FROM bugs WHERE 1 ";
const SELECT_BUGS = "SELECT * FROM bugs WHERE ?1 ORDER BY ?2 ASC ";
const UPDATE_BUG  = "UPDATE bugs SET idd=?1, name=?2, description=?3 WHERE idp=?4 ";
const REMOVE_BUG  = "DELETE FROM bugs WHERE idp=?1 LIMIT 1 ";

// Types
const INSERT_TYPE  = "INSERT INTO types (name, description) VALUES (?1, ?2) ";
const SELECT_TYPE  = "SELECT * FROM types WHERE idt=?1 LIMIT 1 ";
const TYPES_COUNT  = "SELECT count(idt) FROM types WHERE 1 ";
const SELECT_TYPES = "SELECT * FROM types ORDER BY name ASC ";
const UPDATE_TYPE  = "UPDATE types SET name=?1, description=?2 WHERE idt=?3 ";
const REMOVE_TYPE  = "DELETE FROM types WHERE idt=?1 LIMIT 1 ";

// Bug Posts

// Projects
const INSERT_PROJECT  = "INSERT INTO projects (idd, name, description, homepage_uri, repository_uri) VALUES (?1, ?2, ?3, ?4, ?5) ";
const SELECT_PROJECT  = "SELECT * FROM projects WHERE idp=?1 LIMIT 1 ";
const PROJECTS_COUNT  = "SELECT count(idp) FROM projects WHERE 1 ";
const SELECT_PROJECTS = "SELECT * FROM projects ORDER BY name ASC ";
const UPDATE_PROJECT  = "UPDATE projects SET idd=?1, name=?2, description=?3, homepage_uri=?4, repository_uri=?5 WHERE idp=?6 ";
const REMOVE_PROJECT  = "DELETE FROM projects WHERE idp=?1 LIMIT 1 ";

// Components
const INSERT_COMPONENT  = "INSERT INTO components (idp, name, description) VALUES (?1, ?2, ?3) ";
const SELECT_COMPONENT  = "SELECT * FROM components WHERE idc=?1 LIMIT 1 ";
const COMPONENTS_COUNT  = "SELECT count(idc) FROM components WHERE 1 ";
const SELECT_COMPONENTS = "SELECT * FROM components ORDER BY name ASC ";
const UPDATE_COMPONENT  = "UPDATE components SET idp=?1, name=?2, description=?3 WHERE idc=?4 ";
const REMOVE_COMPONENT  = "DELETE FROM components WHERE idc=?1 LIMIT 1 ";

// Developers
const INSERT_DEVELOPER  = "INSERT INTO developers (firstname, surname, email, im, summary) VALUES (?1, ?2, ?3, ?4, ?5) ";
const SELECT_DEVELOPER  = "SELECT * FROM developers WHERE idd=?1 LIMIT 1 ";
const DEVELOPERS_COUNT  = "SELECT count(idd) FROM developers WHERE 1 ";
const SELECT_DEVELOPERS = "SELECT * FROM developers ORDER BY surname, firstname ASC ";
const UPDATE_DEVELOPER  = "UPDATE developers SET firstname=?1, surname=?2, email=?3, im=?4, summary=?5 WHERE idd=?6 ";
const REMOVE_DEVELOPER  = "DELETE FROM developers WHERE idd=?1 LIMIT 1 ";

// Statuses
const INSERT_STATUS   = "INSERT INTO statuses (name, description, color) VALUES (?1, ?2, ?3) ";
const SELECT_STATUS   = "SELECT * FROM statuses WHERE ids=?1 LIMIT 1 ";
const STATUSES_COUNT  = "SELECT count(ids) FROM statuses WHERE 1 ";
const SELECT_STATUSES = "SELECT * FROM statuses ORDER BY name ASC ";
const UPDATE_STATUS   = "UPDATE statuses SET name=?1, description=?2, color=?3 WHERE ids=?4 ";
const REMOVE_STATUS   = "DELETE FROM statuses WHERE ids=?1 LIMIT 1 ";

// Resolutions
const INSERT_RESOLUTION  = "INSERT INTO resolutions (name, description) VALUES (?1, ?2) ";
const SELECT_RESOLUTION  = "SELECT * FROM resolutions WHERE idr=?1 LIMIT 1 ";
const RESOLUTIONS_COUNT  = "SELECT count(idr) FROM resolutions WHERE 1 ";
const SELECT_RESOLUTIONS = "SELECT * FROM resolutions ORDER BY name ASC ";
const UPDATE_RESOLUTION  = "UPDATE resolutions SET name=?1, description=?2 WHERE idr=?3 ";
const REMOVE_RESOLUTION  = "DELETE FROM resolutions WHERE idr=?1 LIMIT 1 ";

// Priorities
const INSERT_PRIORITY   = "INSERT INTO priorities (name, description) VALUES (?1, ?2) ";
const SELECT_PRIORITY   = "SELECT * FROM priorities WHERE idpr=?1 LIMIT 1 ";
const PRIORITIES_COUNT  = "SELECT count(idpr) FROM priorities WHERE 1 ";
const SELECT_PRIORITIES = "SELECT * FROM priorities ORDER BY name ASC ";
const UPDATE_PRIORITY   = "UPDATE priorities SET name=?1, description=?2 WHERE idpr=?3 ";
const REMOVE_PRIORITY   = "DELETE FROM priorities WHERE idpr=?1 LIMIT 1 ";

// Severities
const INSERT_SEVERITY   = "INSERT INTO severities (name, description) VALUES (?1, ?2) ";
const SELECT_SEVERITY   = "SELECT * FROM severities WHERE idsv=?1 LIMIT 1 ";
const SEVERITIES_COUNT  = "SELECT count(idsv) FROM severities WHERE 1 ";
const SELECT_SEVERITIES = "SELECT * FROM severities ORDER BY name ASC ";
const UPDATE_SEVERITY   = "UPDATE severities SET name=?1, description=?2 WHERE idsv=?3 ";
const REMOVE_SEVERITY   = "DELETE FROM severities WHERE idsv=?1 LIMIT 1 ";

// Versions
const INSERT_VERSION  = "INSERT INTO versions (idp, name, description) VALUES (?1, ?2, ?3) ";
const SELECT_VERSION  = "SELECT * FROM versions WHERE idv=?1 LIMIT 1 ";
const VERSIONS_COUNT  = "SELECT count(idv) FROM versions WHERE 1 ";
const SELECT_VERSIONS = "SELECT * FROM versions ORDER BY name ASC ";
const UPDATE_VERSION  = "UPDATE versions SET idp=?1, name=?2, description=?3 WHERE idv=?4 ";
const REMOVE_VERSION  = "DELETE FROM versions WHERE idv=?1 LIMIT 1 ";

// Notes
const INSERT_NOTE  = "INSERT INTO notes (idd, idp, summary, text, since) VALUES (?1, ?2, ?3, ?4, ?5) ";
const SELECT_NOTE  = "SELECT * FROM notes WHERE idn=?1 LIMIT 1 ";
const NOTES_COUNT  = "SELECT count(idn) FROM notes WHERE 1 ";
const SELECT_NOTES = "SELECT * FROM notes ORDER BY summary ASC ";
const UPDATE_NOTE  = "UPDATE notes SET idd=?1, idp=?2, summary=?3, text=?4, since=?4 WHERE idn=?5 ";
const REMOVE_NOTE  = "DELETE FROM notes WHERE idn=?1 LIMIT 1 ";


// ===========================================================================
// Other queries

// XXX Use truncate!
const TRUNCATE_BOOGIE_TABLE = "REMOVE FROM ?1 ";