CREATE TABLE login (
             cookie     VARCHAR(100)  PRIMARY KEY,
             user_id    INTEGER NOT NULL,
             timeout    VARCHAR(15) NOT NULL
);

CREATE TABLE people (
             id       INTEGER PRIMARY KEY AUTOINCREMENT,
             fname    VARCHAR(20) NOT NULL,
             lname    VARCHAR(20) NOT NULL,
             email    VARCHAR(40) UNIQUE NOT NULL,
             password VARCHAR(10) NOT NULL,
             authcode VARCHAR(20),
             acked    VARCHAR(1)  NOT NULL,
             company  VARCHAR(30),
             country  VARCHAR(20),
             state    VARCHAR(20),
             city     VARCHAR(20),
             street   VARCHAR(30),
             zip      VARCHAR(10),
             phone    VARCHAR(20),
             cell     VARCHAR(20),
             fax      VARCHAR(20),
             pauseid  VARCHAR(20),
             url      VARCHAR(100),
             mongers  VARCHAR(100),
             bio      BLOB,
             photo    VARCHAR(1),
             day      VARCHAR(1),
             hide     VARCHAR(1),

             disabled VARCHAR(1),

             isadmin  VARCHAR(1),
             iseditor VARCHAR(1),

             other    VARCHAR(10000)
);

INSERT INTO people (id, fname, lname, email, password, acked) 
            VALUES (1, "Site", "Administrator", 'admin', 'admin', 1);

CREATE TABLE roles (
            id         INTEGER PRIMARY KEY,
            name       VARCHAR(100) UNIQUE NOT NULL
);

INSERT INTO roles (id, name) VALUES (1, 'site_admin');
INSERT INTO roles (id, name) VALUES (2, 'conf_admin');
INSERT INTO roles (id, name) VALUES (3, 'content_manager');
INSERT INTO roles (id, name) VALUES (4, 'accountant');

CREATE TABLE people_in_roles (
            id         INTEGER PRIMARY KEY,
            person     INTEGER NOT NULL,
            role       INTEGER NOT NULL,
            conference INTEGER
);

INSERT INTO people_in_roles (person, role) VALUES (1, 1);


CREATE TABLE talks (
            id         INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id    INTEGER NOT NULL,
            title      VARCHAR(100) NOT NULL,
            length     INTEGER NOT NULL,
            language   VARCHAR(20) NOT NULL,
            abstract   BLOB,
            accepted   INTEGER NOT NULL,
            stream     VARCHAR(1),
            other      VARCHAR(10000),
            room       VARCHAR(1),
            day        VARCHAR(1),
            start_time VARCHAR(10),
            conference INTEGER NOT NULL
);

CREATE TABLE categories (
            id         INTEGER PRIMARY KEY AUTOINCREMENT,
            name       VARCHAR(30) UNIQUE NOT NULL,
            conference INTEGER NOT NULL
);

CREATE TABLE talk_in_category (
            id         INTEGER PRIMARY KEY AUTOINCREMENT,
            talk       INTEGER NOT NULL,
            category   INTEGER NOT NULL
);

CREATE TABLE keywords (
            id         INTEGER PRIMARY KEY AUTOINCREMENT,
            talk       INTEGER,
            word       VARCHAR(30),
            conference INTEGER NOT NULL
);

CREATE TABLE days (
            id         INTEGER PRIMARY KEY AUTOINCREMENT,
            day        VARCHAR(10),
            start_time VARCHAR(10),
            end_time   VARCHAR(10),
            conference INTEGER NOT NULL
);

CREATE TABLE rooms (
            id         INTEGER PRIMARY KEY AUTOINCREMENT,
            name       VARCHAR(100),
            location   VARCHAR(100),
            size       INTEGER,
            conference INTEGER NOT NULL
);

CREATE TABLE config (
            id         INTEGER PRIMARY KEY,
            conference INTEGER,
            field      VARCHAR(100),
            value      VARCHAR(100)
);

CREATE TABLE conferences (
            id                 INTEGER PRIMARY KEY,
            handle             VARCHAR(100) UNIQUE NOT NULL,
            name               VARCHAR(255),
            proposals_enabled  VARCHAR(1) NOT NULL DEFAULT 0,
            proposal_lengths   VARCHAR(255) NOT NULL DEFAULT '',
            proposal_languages VARCHAR(255) NOT NULL DEFAULT '',
            show_schedule      VARCHAR(1) NOT NULL DEFAULT 0,
            email              VARCHAR(255) NOT NULL,
            email_signature    VARCHAR(255),
            db_timeout         INTEGER NOT NULL DEFAULT 100,
            templates_dir      VARCHAR(100),
            status             VARCHAR(1)
);

CREATE TABLE people_on_conference (
            id           INTEGER PRIMARY KEY,
            person       INTEGER NOT NULL,
            conference   INTEGER NOT NULL,
            payment      VARCHAR(20),
            participated VARCHAR(1)
);

CREATE TABLE urls (
            id           INTEGER PRIMARY KEY,
            url          VARCHAR(255) UNIQUE NOT NULL,
            conference   INTEGER NOT NULL
);