drop table if exists user_choice;
drop table if exists choices;
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 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,
    is_transparent BOOLEAN DEFAULT 1 NOT NULL, 
    is_public BOOLEAN DEFAULT 1 NOT NULL,
    is_multiplechoice BOOLEAN DEFAULT 1 NOT NULL,
    is_weighted BOOLEAN DEFAULT 0 NOT NULL,
    is_open BOOLEAN DEFAULT 0 NOT NULL,
    is_terminated BOOLEAN DEFAULT 0 NOT NULL,
    id_author INTEGER DEFAULT 1 NOT NULL,
    id_group INTEGER DEFAULT 1 NOT NULL,
    FOREIGN KEY(id_author) REFERENCES users (id) ON DELETE SET DEFAULT,
    FOREIGN KEY(id_group) REFERENCES groups (id),
    CHECK (is_transparent IN (0, 1)),
    CHECK (is_public IN (0, 1)),
    CHECK (is_weighted IN (0, 1)),
    CHECK (is_open IN (0, 1)),
    CHECK (is_terminated 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,
    weight INTEGER,
    FOREIGN KEY(id_user) REFERENCES users (id) ON DELETE CASCADE,
    FOREIGN KEY(id_choice) REFERENCES choices (id) ON DELETE CASCADE,
    PRIMARY KEY(id_user, id_choice)
);

-- Test data

INSERT INTO users (id, email, password, openid, name, organization, is_admin, key)
VALUES (1, "admin@admin.fr", "", "", "Toto (admin) Tata", "World corp", 1, "victory");
-- to login, go to /login/1/victory
INSERT INTO groups (id, name, system) VALUES (1, "Tous", 1);
INSERT INTO groups (name) VALUES ("CA");
INSERT INTO groups (name) VALUES ("Membres");
INSERT INTO user_group (id_user, id_group) VALUES(1, 1);