Cavote/schema.sql

drop table if exists user_choice;
drop table if exists user_choice_buffer_anonymous;
drop table if exists user_vote;
drop table if exists choices;
drop table if exists cardinals;
drop table if exists values_;
drop table if exists attachments;
drop table if exists votes;
drop table if exists user_group;
drop table if exists groups;
drop table if exists users;

PRAGMA foreign_keys = ON;

create table users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    email TEXT UNIQUE NOT NULL,
    password TEXT NOT NULL,
    openid TEXT NOT NULL,
    name TEXT UNIQUE NOT NULL, 
    organization TEXT,
    is_admin BOOLEAN DEFAULT 0 NOT NULL,
    key TEXT,
    CHECK (is_admin IN (0, 1))
);

create table groups (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT UNIQUE NOT NULL,
    system BOOLEAN DEFAULT 0 NOT NULL,
    CHECK (system IN (0, 1))
);

create table user_group (
    id_user INTEGER,
    id_group INTEGER,
    FOREIGN KEY(id_user) REFERENCES users (id) ON DELETE CASCADE,
    FOREIGN KEY(id_group) REFERENCES groups (id) ON DELETE CASCADE,
    PRIMARY KEY(id_user, id_group)
);

create table cardinals (
    id INTEGER UNIQUE NOT NULL PRIMARY KEY,
    name TEXT UNIQUE NOT NULL,
    first INTEGER NOT NULL
);

create table values_ (
    name TEXT NOT NULL,
    weight INTEGER NOT NULL,
    id_cardinal INTEGER NOT NULL,
    FOREIGN KEY(id_cardinal) REFERENCES cardinals (id) ON DELETE CASCADE,
    PRIMARY KEY(id_cardinal, weight)
);

create table votes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    description TEXT,
    category TEXT,
    date_begin INTEGER DEFAULT CURRENT_TIMESTAMP NOT NULL,
    date_end INTEGER NOT NULL,
    quorum FLOAT DEFAULT 1,
    reminder_last_days INTEGER DEFAULT 3 NOT NULL,
    is_transparent BOOLEAN DEFAULT 1 NOT NULL,
    is_public BOOLEAN DEFAULT 1 NOT NULL,
    is_anonymous BOOLEAN DEFAULT 1 NOT NULL,
    is_open BOOLEAN DEFAULT 0 NOT NULL,
    is_terminated BOOLEAN DEFAULT 0 NOT NULL,
    is_hidden BOOLEAN DEFAULT 0 NOT NULL,
    id_author INTEGER DEFAULT 1 NOT NULL,
    id_group INTEGER DEFAULT 1 NOT NULL,
    id_cardinal INTEGER NOT NULL,
    FOREIGN KEY(id_author) REFERENCES users (id) ON DELETE SET DEFAULT,
    FOREIGN KEY(id_group) REFERENCES groups (id),
    FOREIGN KEY(id_cardinal) REFERENCES cardinals (id),
    CHECK (is_transparent IN (0, 1)),
    CHECK (is_public IN (0, 1)),
    CHECK (is_open IN (0, 1)),
    CHECK (is_terminated IN (0, 1)),
    CHECK (is_hidden IN (0, 1))
);

create table attachments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    url TEXT NOT NULL,
    id_vote INTEGER NOT NULL,
    FOREIGN KEY(id_vote) REFERENCES votes (id) ON DELETE CASCADE
);

create table choices (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    id_vote INTEGER NOT NULL,
    FOREIGN KEY(id_vote) REFERENCES votes (id) ON DELETE CASCADE
);

create table user_choice (
    id_user INTEGER,
    id_choice INTEGER NOT NULL,
    id_cardinal INTEGER,
    weight INTEGER,
    FOREIGN KEY(id_user) REFERENCES users (id) ON DELETE CASCADE,
    FOREIGN KEY(id_choice) REFERENCES choices (id) ON DELETE CASCADE,
    FOREIGN KEY(id_cardinal, weight) REFERENCES values_ (id_cardinal, weight) ON DELETE CASCADE,
    PRIMARY KEY(id_user, id_choice)
);

create table user_choice_buffer_anonymous (
 -- NOTE: same table structure as user_choice, used to randomize insertion per vote
    id_user INTEGER,
    id_choice INTEGER NOT NULL,
    id_cardinal INTEGER,
    weight INTEGER,
    FOREIGN KEY(id_user) REFERENCES users (id) ON DELETE CASCADE,
    FOREIGN KEY(id_choice) REFERENCES choices (id) ON DELETE CASCADE,
    FOREIGN KEY(id_cardinal, weight) REFERENCES values_ (id_cardinal, weight) ON DELETE CASCADE,
    PRIMARY KEY(id_user, id_choice)
);

create table user_vote (
    date INTEGER DEFAULT CURRENT_TIMESTAMP NOT NULL,
    id_user INTEGER NOT NULL,
    id_vote INTEGER NOT NULL,
    FOREIGN KEY(id_user) REFERENCES users (id) ON DELETE CASCADE,
    FOREIGN KEY(id_vote) REFERENCES votes (id) ON DELETE CASCADE,
    PRIMARY KEY(id_user, id_vote)
);

-- Test data

INSERT INTO users (id, email, password, openid, name, organization, is_admin, key)
VALUES (1, "admin+cavote+1@localhost.localdomain", "", "", "admin1", "Organisation", 1, "victory");
INSERT INTO users (id, email, password, openid, name, organization, is_admin, key)
VALUES (2, "admin+cavote+2@localhost.localdomain", "", "", "admin2", "Organisation", 1, "victory");
INSERT INTO users (id, email, password, openid, name, organization, is_admin, key)
VALUES (3, "admin+cavote+3@localhost.localdomain", "", "", "admin3", "Organisation", 1, "victory");
INSERT INTO users (id, email, password, openid, name, organization, is_admin, key)
VALUES (4, "admin+cavote+4@localhost.localdomain", "", "", "admin4", "Organisation", 1, "victory");
INSERT INTO users (id, email, password, openid, name, organization, is_admin, key)
VALUES (4, "admin+cavote+5@localhost.localdomain", "", "", "admin5", "Organisation", 1, "victory");
-- to login, go to /login/1/victory
INSERT INTO groups (id, name, system) VALUES (1, "Tout le monde", 1);
INSERT INTO groups (name) VALUES ("CA");
INSERT INTO groups (name) VALUES ("Membres");
INSERT INTO user_group (id_user, id_group) VALUES(1, 1);
INSERT INTO user_group (id_user, id_group) VALUES(2, 1);
INSERT INTO user_group (id_user, id_group) VALUES(3, 1);
INSERT INTO user_group (id_user, id_group) VALUES(4, 1);
INSERT INTO user_group (id_user, id_group) VALUES(5, 1);