CREATE TABLE IF NOT EXISTS users (
    id VARCHAR PRIMARY KEY,
    username VARCHAR NOT NULL,
    email VARCHAR NOT NULL,
    passwd VARCHAR NOT NULL,
    level INT NOT NULL
);

CREATE TABLE IF NOT EXISTS tokens (token VARCHAR PRIMARY KEY, user_id VARCHAR);

create table if not exists applications (
    id text primary key,
    url text not null,
    simple_url text not null,
    company text,
    recruiter text,
    title text,
    mesasge text default '',
    user_id text,
    extra_data text,
    job_level text default '',
    -- remote - hyrbrid - in-person
    inperson_type text default '',
    location text default '',
    status_id text default null,
    agency boolean default false,
    create_time timestamp default now ()
);

create table if not exists applications_urls (
    application_id text,
    url text not null,
    primary key (application_id, url)
);

create table if not exists flair (
    id text primary key,
    user_id text not null,
    color text default '#ff0000',
    name text default 'New Flair',
    expr text default 'flair',
    description text default '',
    showFullDescription integer default 1,
    sort integer default 0
);

create table if not exists flair_link (
    id text primary key,
    application_id text not null,
    flair_id text not null
);

create table if not exists events (
    id text primary key,
    application_id text not null,
    --
    -- Event Types
    --
    --
    -- Creation(0),
    -- StatusUpdate(1),
    -- Page(2)
    event_type integer not null,
    -- This only matters when event_type == 1
    new_status_id text,
    time timestamp default now ()
);

--
-- User Controlled Status
--
create table if not exists user_status_node (
    id text primary key,
    user_id text not null,
    name text not null,
    icon text not null,
    x integer default 0,
    y integer default 0,
    width integer default 0,
    height integer default 0,
    permission integer default 0,
    visible boolean default true,
    endable boolean default false
);

create table if not exists user_status_link (
    id text primary key,
    -- You technically can get this by loking a the source and target nodes but that seams more complicated
    user_id text not null,
    -- This can be null because null means creation
    source_node text,
    -- This can be null because null means creation
    target_node text,
    source_x integer default 0,
    source_y integer default 0,
    target_x integer default 0,
    target_y integer default 0,
    -- If this link is bidiretoral
    bi boolean
);
