## phpTodo - Web based todo lists                                             ##
## Copyright (C) 2005-07  Jason Frisvold <friz@godshell.com>                  ##
##                                                                            ##
## This program is free software; you can redistribute it and/or modify       ##
## it under the terms of the GNU General Public License as published by       ##
## the Free Software Foundation; either version 2 of the License, or          ##
## (at your option) any later version.                                        ##
##                                                                            ##
## This program is distributed in the hope that it will be useful,            ##
## but WITHOUT ANY WARRANTY; without even the implied warranty of             ##
## MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the              ##
## GNU General Public License for more details.                               ##
##                                                                            ##
## You should have received a copy of the GNU General Public License          ##
## along with this program; if not, write to the Free Software                ##
## Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA ##

## $Id: todo.sql,v 1.29 2007/03/06 17:58:04 xen0phage Exp $ ##

# DROP DATABASE phpTodo;
# CREATE DATABASE phpTodo;
# USE phpTodo;

CREATE TABLE users       (id              INTEGER     UNSIGNED    NOT NULL AUTO_INCREMENT,
                          username        CHAR(15)                NOT NULL,
                          password        CHAR(40)                NOT NULL,
                          admin           TINYINT     UNSIGNED    NOT NULL DEFAULT 0,
                          full_name       CHAR(40),
                          email           CHAR(255)               NOT NULL,
                          PRIMARY KEY     (id),
                          UNIQUE          (username),
                          INDEX           (password));

CREATE TABLE priority    (id              TINYINT     UNSIGNED    NOT NULL AUTO_INCREMENT,
                          name            CHAR(20)                NOT NULL,
                          priority        TINYINT                 NOT NULL,
                          PRIMARY KEY     (id));

CREATE TABLE status      (id              TINYINT     UNSIGNED    NOT NULL AUTO_INCREMENT,
                          status          CHAR(20)                NOT NULL,
                          PRIMARY KEY     (id));

CREATE TABLE order_by    (id              TINYINT     UNSIGNED    NOT NULL AUTO_INCREMENT,
                          field_name      CHAR(30)                NOT NULL,
                          pretty_name     CHAR(20)                NOT NULL,
                          PRIMARY KEY     (id));

CREATE TABLE category    (id              INTEGER     UNSIGNED    NOT NULL AUTO_INCREMENT,
                          category        CHAR(15)                NOT NULL,
                          user_id         INTEGER     UNSIGNED    NOT NULL,
                          PRIMARY KEY     (id),
                          INDEX           (user_id));

CREATE TABLE user_prefs  (id              INTEGER     UNSIGNED    NOT NULL AUTO_INCREMENT,
                          user_id         INTEGER     UNSIGNED    NOT NULL,
                          priority_sort   TINYINT     UNSIGNED    NOT NULL DEFAULT 0,
                          status_sort     TINYINT     UNSIGNED    NOT NULL DEFAULT 0,
                          duedate_sort    TINYINT     UNSIGNED    NOT NULL DEFAULT 0,
                          subject_sort    TINYINT     UNSIGNED    NOT NULL DEFAULT 0,
                          category_filter INTEGER     UNSIGNED    NOT NULL DEFAULT 0,
                          show_complete   TINYINT     UNSIGNED    NOT NULL DEFAULT 0,
                          sort_stabilizer CHAR(15)                NOT NULL DEFAULT 'taskid',
                          sort_order      CHAR(3)                 NOT NULL DEFAULT 'asc',
                          PRIMARY KEY     (id),
                          FOREIGN KEY     (user_id) REFERENCES users(id),
                          INDEX           (user_id));

CREATE TABLE sessions    (id              INTEGER     UNSIGNED    NOT NULL AUTO_INCREMENT,
                          phpsessid       CHAR(32)                NOT NULL,
                          last            INTEGER     UNSIGNED    NOT NULL,
                          user_id         INTEGER     UNSIGNED    NOT NULL,
                          PRIMARY KEY     (id),
                          FOREIGN KEY     (user_id) REFERENCES users(id),
                          INDEX           (last),
                          INDEX           (phpsessid));

CREATE TABLE listentries (id              INTEGER     UNSIGNED    NOT NULL AUTO_INCREMENT,
                          user_id         INTEGER     UNSIGNED    NOT NULL,
                          subject         CHAR(75)                NOT NULL,
                          start_date      DATETIME,
                          due_date        DATETIME,
                          create_date     DATETIME,
                          complete_date   DATETIME,
                          priority_id     TINYINT     UNSIGNED    NOT NULL,
                          status_id       TINYINT     UNSIGNED    NOT NULL,
                          category_id     INTEGER     UNSIGNED    NOT NULL DEFAULT 1,
                          last_modified   TIMESTAMP,
                          PRIMARY KEY     (id),
                          INDEX           (user_id),
                          INDEX           (category_id),
                          INDEX           (priority_id),
                          INDEX           (status_id),
                          FOREIGN KEY     (user_id) REFERENCES users(id),
                          FOREIGN KEY     (priority_id) REFERENCES priority(id),
                          FOREIGN KEY     (status_id) REFERENCES status(id),
                          FOREIGN KEY     (category_id) REFERENCES category(id));

CREATE TABLE description (id              INTEGER     UNSIGNED    NOT NULL AUTO_INCREMENT,
                          list_id         INTEGER     UNSIGNED    NOT NULL,
                          description     TEXT,
                          nextaction      TEXT,
                          PRIMARY KEY     (id),
                          FOREIGN KEY     (list_id) REFERENCES listentries(id),
                          INDEX           (list_id));

CREATE TABLE feeds       (id              INTEGER     UNSIGNED    NOT NULL AUTO_INCREMENT,
                          user_id         INTEGER     UNSIGNED    NOT NULL,
                          secret          CHAR(15)                NOT NULL,
                          name            CHAR(15)                NOT NULL,
                          version         CHAR(8)                 NOT NULL DEFAULT 'RSS0.91',
                          priority        TINYINT     UNSIGNED    NOT NULL,
                          status          TINYINT     UNSIGNED    NOT NULL,
                          category        INTEGER     UNSIGNED    NOT NULL,
                          order_by        TINYINT     UNSIGNED    NOT NULL,
                          max_limit       TINYINT     UNSIGNED    NOT NULL,
                          show_completed  TINYINT     UNSIGNED    NOT NULL,
                          PRIMARY KEY     (id),
                          FOREIGN KEY     (category)  REFERENCES category(id),
                          FOREIGN KEY     (order_by)  REFERENCES order_by(id),
                          INDEX           (user_id),
                          INDEX           (secret),
                          INDEX           (name));

INSERT INTO users VALUES (NULL, 'admin', SHA1('password'), 1, 'Administrative User', 'admin@example.com');

INSERT INTO priority VALUES (NULL, 'High', 1);
INSERT INTO priority VALUES (NULL, 'Medium', 5);
INSERT INTO priority VALUES (NULL, 'Low', 10);
INSERT INTO priority VALUES (NULL, 'None', 99);

INSERT INTO status VALUES (1, 'Not Started');
INSERT INTO status VALUES (2, 'Deferred');
INSERT INTO status VALUES (3, 'Waiting on Someone');
INSERT INTO status VALUES (50, 'In Progress');
INSERT INTO status VALUES (98, 'Cancelled');
INSERT INTO status VALUES (99, 'Completed');

INSERT INTO order_by VALUES (NULL, '', 'None');
INSERT INTO order_by VALUES (NULL, 'priority.priority', 'Priority');
INSERT INTO order_by VALUES (NULL, 'status.id', 'Status');
INSERT INTO order_by VALUES (NULL, 'listentries.subject', 'Subject');
INSERT INTO order_by VALUES (NULL, 'listentries.due_date', 'Due Date');
INSERT INTO order_by VALUES (NULL, 'listentries.start_date', 'Start Date');

INSERT INTO category VALUES (NULL, 'None', 0);
INSERT INTO category VALUES (NULL, 'Personal', 0);
INSERT INTO category VALUES (NULL, 'Work', 0);
INSERT INTO category VALUES (NULL, 'Other', 0);

INSERT INTO user_prefs VALUES (NULL, 1, 0, 0, 0, 0, 0, 0, 'none', 'asc');

# Change the following line to reflect the correct user, host, database name, and password
# GRANT insert, update, delete, select ON phpTodo.* TO phpTodo@localhost IDENTIFIED BY 'password';