CREATE TABLE alembic_version (
    version_num VARCHAR(32) NOT NULL, 
    CONSTRAINT alembic_version_pkc PRIMARY KEY (version_num)
);

-- Running upgrade  -> 0709da242beb

SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE accounts (
    id VARCHAR(16) NOT NULL, 
    name VARCHAR(32) NOT NULL, 
    uid VARCHAR(16) NOT NULL, 
    reminders VARCHAR(6) DEFAULT 'always' NOT NULL, 
    password BLOB NOT NULL, 
    password_must_change BOOLEAN DEFAULT 0 NOT NULL, 
    totp_secret BLOB, 
    is_admin BOOLEAN DEFAULT 0 NOT NULL, 
    settings_id VARCHAR(16) NOT NULL, 
    last_login TIMESTAMP, 
    invalid_attempts INTEGER DEFAULT '0' NOT NULL, 
    last_invalid_attempt TIMESTAMP, 
    created TIMESTAMP DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
    modified TIMESTAMP, 
    status VARCHAR(8) DEFAULT 'active' NOT NULL, 
    PRIMARY KEY (id), 
    UNIQUE (name), 
    UNIQUE (uid), 
    FOREIGN KEY(uid) REFERENCES people (id), 
    FOREIGN KEY(settings_id) REFERENCES settings (id), 
    UNIQUE (id), 
    CHECK (reminders IN ('always', 'more', 'fewer', 'never')), 
    CHECK (password_must_change IN (0, 1)), 
    CHECK (is_admin IN (0, 1)), 
    CHECK (status IN ('active', 'disabled'))
);

CREATE TABLE albums (
    id VARCHAR(16) NOT NULL, 
    name VARCHAR(64) NOT NULL, 
    cover_id VARCHAR(16), 
    uid VARCHAR(16) NOT NULL, 
    event_id VARCHAR(16), 
    owner_id VARCHAR(16) NOT NULL, 
    created TIMESTAMP DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
    modified TIMESTAMP, 
    status VARCHAR(8) DEFAULT 'active' NOT NULL, 
    PRIMARY KEY (id), 
    CONSTRAINT uniq_album_user UNIQUE (name, uid), 
    FOREIGN KEY(cover_id) REFERENCES pictures (id), 
    FOREIGN KEY(event_id) REFERENCES events (id), 
    FOREIGN KEY(uid) REFERENCES people (id) ON DELETE CASCADE, 
    FOREIGN KEY(owner_id) REFERENCES accounts (id) ON DELETE CASCADE, 
    UNIQUE (id), 
    CHECK (status IN ('active', 'disabled'))
);

CREATE TABLE apicredentials (
    id VARCHAR(16) NOT NULL, 
    vendor VARCHAR(32) NOT NULL, 
    type VARCHAR(16) DEFAULT 'email' NOT NULL, 
    url VARCHAR(64), 
    `key` VARCHAR(128) NOT NULL, 
    secret BLOB, 
    otherdata BLOB, 
    settings_id VARCHAR(16) NOT NULL, 
    owner_id VARCHAR(16) NOT NULL, 
    created TIMESTAMP DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
    modified TIMESTAMP, 
    status VARCHAR(8) DEFAULT 'active' NOT NULL, 
    PRIMARY KEY (id), 
    FOREIGN KEY(owner_id) REFERENCES accounts (id), 
    FOREIGN KEY(settings_id) REFERENCES settings (id), 
    UNIQUE (id), 
    FOREIGN KEY(owner_id) REFERENCES accounts (id), 
    CHECK (status IN ('active', 'disabled'))
);

CREATE TABLE categories (
    id VARCHAR(16) NOT NULL, 
    name VARCHAR(64) NOT NULL, 
    owner_id VARCHAR(16) NOT NULL, 
    created TIMESTAMP DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
    modified TIMESTAMP, 
    status VARCHAR(8) DEFAULT 'active' NOT NULL, 
    PRIMARY KEY (id), 
    CONSTRAINT uniq_category_owner UNIQUE (name, owner_id), 
    FOREIGN KEY(owner_id) REFERENCES accounts (id) ON DELETE CASCADE, 
    UNIQUE (id), 
    CHECK (status IN ('active', 'disabled'))
);

CREATE TABLE contacts (
    id VARCHAR(16) NOT NULL, 
    label VARCHAR(6) DEFAULT 'home' NOT NULL, 
    type VARCHAR(9) DEFAULT 'email' NOT NULL, 
    carrier VARCHAR(16), 
    info VARCHAR(255), 
    person_id VARCHAR(16) NOT NULL, 
    privacy VARCHAR(11) DEFAULT 'guests_only' NOT NULL, 
    created TIMESTAMP DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
    modified TIMESTAMP, 
    status VARCHAR(11) DEFAULT 'unconfirmed' NOT NULL, 
    PRIMARY KEY (id), 
    CONSTRAINT uniq_info_type UNIQUE (info, type), 
    FOREIGN KEY(person_id) REFERENCES people (id), 
    UNIQUE (id), 
    CHECK (label IN ('home', 'mobile', 'other', 'work')), 
    CHECK (type IN ('email', 'linkedin', 'location', 'messenger', 'slack', 'sms', 'voice', 'whatsapp')), 
    CHECK (privacy IN ('public', 'invitees', 'guests_only', 'secret')), 
    CHECK (status IN ('active', 'unconfirmed', 'disabled'))
);

CREATE TABLE events (
    id VARCHAR(16) NOT NULL, 
    name VARCHAR(255) NOT NULL, 
    description TEXT NOT NULL, 
    starts TIMESTAMP DEFAULT 0 NOT NULL, 
    duration INTEGER NOT NULL, 
    capacity INTEGER DEFAULT 0, 
    guestpartymax INTEGER DEFAULT 4, 
    location_id VARCHAR(16) NOT NULL, 
    theme_id VARCHAR(16) NOT NULL, 
    rsvp_start TIMESTAMP DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
    rsvp_by TIMESTAMP NOT NULL, 
    rsvps_active BOOLEAN DEFAULT 1 NOT NULL, 
    waitlist_active BOOLEAN DEFAULT 0 NOT NULL, 
    comments_active BOOLEAN DEFAULT 1 NOT NULL, 
    category_id VARCHAR(16) NOT NULL, 
    privacy VARCHAR(11) DEFAULT 'public' NOT NULL, 
    guestlist_privacy VARCHAR(11) DEFAULT 'invitees' NOT NULL, 
    owner_id VARCHAR(16) NOT NULL, 
    created TIMESTAMP DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
    modified TIMESTAMP, 
    status VARCHAR(8) NOT NULL, 
    PRIMARY KEY (id), 
    FOREIGN KEY(category_id) REFERENCES categories (id), 
    FOREIGN KEY(location_id) REFERENCES locations (id), 
    FOREIGN KEY(theme_id) REFERENCES themes (id), 
    FOREIGN KEY(owner_id) REFERENCES accounts (id), 
    UNIQUE (id), 
    CHECK (rsvps_active IN (0, 1)), 
    CHECK (waitlist_active IN (0, 1)), 
    CHECK (comments_active IN (0, 1)), 
    CHECK (privacy IN ('public', 'invitees', 'guests_only', 'secret')), 
    CHECK (guestlist_privacy IN ('public', 'invitees', 'guests_only', 'secret')), 
    CHECK (status IN ('active', 'canceled', 'disabled'))
);

CREATE TABLE grants (
    id VARCHAR(16) NOT NULL, 
    name VARCHAR(24) NOT NULL, 
    value VARCHAR(64) NOT NULL, 
    uid VARCHAR(16) NOT NULL, 
    owner_id VARCHAR(16) NOT NULL, 
    expires TIMESTAMP, 
    created TIMESTAMP DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
    modified TIMESTAMP, 
    status VARCHAR(8) DEFAULT 'active' NOT NULL, 
    PRIMARY KEY (id), 
    CONSTRAINT uniq_grant_user UNIQUE (name, uid), 
    FOREIGN KEY(uid) REFERENCES people (id) ON DELETE CASCADE, 
    FOREIGN KEY(owner_id) REFERENCES accounts (id) ON DELETE CASCADE, 
    UNIQUE (id), 
    CHECK (status IN ('active', 'disabled'))
);

CREATE TABLE guests (
    id VARCHAR(16) NOT NULL, 
    person_id VARCHAR(16) NOT NULL, 
    event_id VARCHAR(16) NOT NULL, 
    remarks VARCHAR(255), 
    response VARCHAR(14), 
    partysize INTEGER DEFAULT 1, 
    reminders VARCHAR(6) DEFAULT 'always' NOT NULL, 
    is_host BOOLEAN DEFAULT 0 NOT NULL, 
    is_waitlisted BOOLEAN DEFAULT 0 NOT NULL, 
    magic VARCHAR(8) NOT NULL, 
    magic_expires TIMESTAMP, 
    attended BOOLEAN, 
    responded TIMESTAMP, 
    viewed TIMESTAMP, 
    created TIMESTAMP DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
    modified TIMESTAMP, 
    status VARCHAR(9) NOT NULL, 
    PRIMARY KEY (id), 
    CONSTRAINT uniq_person_event UNIQUE (person_id, event_id), 
    FOREIGN KEY(person_id) REFERENCES people (id), 
    FOREIGN KEY(event_id) REFERENCES events (id), 
    UNIQUE (id), 
    CHECK (response IN ('yes', 'no', 'considering-it', 'remind-me')), 
    CHECK (reminders IN ('always', 'more', 'fewer', 'never')), 
    CHECK (is_host IN (0, 1)), 
    CHECK (is_waitlisted IN (0, 1)), 
    CHECK (attended IN (0, 1)), 
    CHECK (status IN ('active', 'disabled', 'suspended'))
);

CREATE TABLE lists (
    id VARCHAR(16) NOT NULL, 
    name VARCHAR(64), 
    description VARCHAR(1023), 
    privacy VARCHAR(11) DEFAULT 'secret' NOT NULL, 
    owner_id VARCHAR(16) NOT NULL, 
    category_id VARCHAR(16) NOT NULL, 
    created TIMESTAMP DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
    modified TIMESTAMP, 
    status VARCHAR(8) DEFAULT 'active' NOT NULL, 
    PRIMARY KEY (id), 
    CONSTRAINT uniq_list_owner UNIQUE (name, owner_id), 
    FOREIGN KEY(category_id) REFERENCES categories (id), 
    FOREIGN KEY(owner_id) REFERENCES accounts (id), 
    UNIQUE (id), 
    CHECK (privacy IN ('public', 'invitees', 'guests_only', 'secret')), 
    CHECK (status IN ('active', 'disabled'))
);

CREATE TABLE listmembers (
    list_id VARCHAR(16) NOT NULL, 
    person_id VARCHAR(16) NOT NULL, 
    created TIMESTAMP DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
    CONSTRAINT uniq_listmember UNIQUE (list_id, person_id), 
    FOREIGN KEY(list_id) REFERENCES lists (id) ON DELETE CASCADE, 
    FOREIGN KEY(person_id) REFERENCES people (id) ON DELETE CASCADE
);

CREATE TABLE locations (
    id VARCHAR(16) NOT NULL, 
    name VARCHAR(255), 
    address VARCHAR(255), 
    city VARCHAR(64) NOT NULL, 
    state VARCHAR(16), 
    postalcode VARCHAR(12), 
    country VARCHAR(2) DEFAULT 'US' NOT NULL, 
    geo_lng FLOAT, 
    geo_lat FLOAT, 
    neighborhood VARCHAR(32), 
    privacy VARCHAR(11) DEFAULT 'public' NOT NULL, 
    owner_id VARCHAR(16) NOT NULL, 
    category_id VARCHAR(16) NOT NULL, 
    created TIMESTAMP DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
    modified TIMESTAMP, 
    status VARCHAR(8) NOT NULL, 
    PRIMARY KEY (id), 
    FOREIGN KEY(owner_id) REFERENCES accounts (id), 
    FOREIGN KEY(category_id) REFERENCES categories (id), 
    UNIQUE (id), 
    CHECK (privacy IN ('public', 'invitees', 'guests_only', 'secret')), 
    CHECK (status IN ('active', 'disabled'))
);

CREATE TABLE messages (
    id VARCHAR(16) NOT NULL, 
    sender_id VARCHAR(16) NOT NULL, 
    recipient_id VARCHAR(16), 
    subject VARCHAR(128), 
    content VARCHAR(1023) NOT NULL, 
    event_id VARCHAR(16) NOT NULL, 
    privacy VARCHAR(11) DEFAULT 'secret' NOT NULL, 
    published BOOLEAN, 
    owner_id VARCHAR(16) NOT NULL, 
    viewed TIMESTAMP, 
    created TIMESTAMP DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
    modified TIMESTAMP, 
    status VARCHAR(8) DEFAULT 'active' NOT NULL, 
    PRIMARY KEY (id), 
    FOREIGN KEY(sender_id) REFERENCES people (id), 
    FOREIGN KEY(recipient_id) REFERENCES people (id), 
    FOREIGN KEY(event_id) REFERENCES events (id), 
    FOREIGN KEY(owner_id) REFERENCES accounts (id), 
    UNIQUE (id), 
    CHECK (privacy IN ('public', 'invitees', 'guests_only', 'secret')), 
    CHECK (published IN (0, 1)), 
    CHECK (status IN ('active', 'disabled'))
);

CREATE TABLE people (
    id VARCHAR(16) NOT NULL, 
    name VARCHAR(64) NOT NULL, 
    identity VARCHAR(64) NOT NULL, 
    lang VARCHAR(6) DEFAULT 'en_US' NOT NULL, 
    tz_id INTEGER DEFAULT 598 NOT NULL, 
    location_id VARCHAR(16), 
    picture_id VARCHAR(16), 
    category_id VARCHAR(16) NOT NULL, 
    privacy VARCHAR(11) DEFAULT 'public' NOT NULL, 
    created TIMESTAMP DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
    modified TIMESTAMP, 
    status VARCHAR(9) NOT NULL, 
    PRIMARY KEY (id), 
    UNIQUE (identity), 
    FOREIGN KEY(location_id) REFERENCES locations (id), 
    FOREIGN KEY(picture_id) REFERENCES pictures (id), 
    FOREIGN KEY(category_id) REFERENCES categories (id), 
    FOREIGN KEY(tz_id) REFERENCES time_zone_name (id), 
    UNIQUE (id), 
    CHECK (privacy IN ('public', 'invitees', 'guests_only', 'secret')), 
    CHECK (status IN ('active', 'disabled', 'suspended'))
);

CREATE TABLE pictures (
    id VARCHAR(16) NOT NULL, 
    caption VARCHAR(255), 
    person_id VARCHAR(16) NOT NULL, 
    content BLOB, 
    thumbnail50x50 BLOB, 
    thumbnail160x160 BLOB, 
    privacy VARCHAR(11) DEFAULT 'guests_only' NOT NULL, 
    category_id VARCHAR(16) NOT NULL, 
    album_id VARCHAR(16), 
    owner_id VARCHAR(16) NOT NULL, 
    created TIMESTAMP DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
    modified TIMESTAMP, 
    status VARCHAR(8) NOT NULL, 
    PRIMARY KEY (id), 
    FOREIGN KEY(album_id) REFERENCES albums (id), 
    FOREIGN KEY(person_id) REFERENCES people (id), 
    FOREIGN KEY(category_id) REFERENCES categories (id), 
    FOREIGN KEY(owner_id) REFERENCES accounts (id), 
    UNIQUE (id), 
    CHECK (privacy IN ('public', 'invitees', 'guests_only', 'secret')), 
    CHECK (status IN ('active', 'disabled'))
);

CREATE TABLE settings (
    id VARCHAR(16) NOT NULL, 
    name VARCHAR(32) NOT NULL, 
    privacy VARCHAR(11) DEFAULT 'public' NOT NULL, 
    smtp_port INTEGER DEFAULT 25 NOT NULL, 
    smtp_smarthost VARCHAR(255), 
    country VARCHAR(2) NOT NULL, 
    lang VARCHAR(6) DEFAULT 'en_US' NOT NULL, 
    tz_id INTEGER DEFAULT 598 NOT NULL, 
    url VARCHAR(255), 
    theme_id VARCHAR(16) NOT NULL, 
    window_title VARCHAR(127) DEFAULT 'Conclave Event Management', 
    default_cat_id VARCHAR(16) NOT NULL, 
    administrator_id VARCHAR(16) NOT NULL, 
    default_hostlist_id VARCHAR(16), 
    created TIMESTAMP DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
    modified TIMESTAMP, 
    PRIMARY KEY (id), 
    FOREIGN KEY(default_cat_id) REFERENCES categories (id), 
    FOREIGN KEY(theme_id) REFERENCES themes (id), 
    FOREIGN KEY(administrator_id) REFERENCES people (id), 
    FOREIGN KEY(default_hostlist_id) REFERENCES lists (id), 
    FOREIGN KEY(tz_id) REFERENCES time_zone_name (id), 
    UNIQUE (id), 
    CHECK (privacy IN ('public', 'invitees', 'guests_only', 'secret'))
);

CREATE TABLE time_zone_name (
    id INTEGER NOT NULL, 
    name VARCHAR(32) NOT NULL, 
    status VARCHAR(8) DEFAULT 'active' NOT NULL, 
    PRIMARY KEY (id), 
    UNIQUE (id), 
    UNIQUE (name), 
    CHECK (status IN ('active', 'disabled'))
);

CREATE TABLE tags (
    id VARCHAR(16) NOT NULL, 
    name VARCHAR(32) NOT NULL, 
    value VARCHAR(256), 
    owner_id VARCHAR(16) NOT NULL, 
    created TIMESTAMP DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
    modified TIMESTAMP, 
    PRIMARY KEY (id), 
    FOREIGN KEY(owner_id) REFERENCES accounts (id), 
    UNIQUE (id)
);

CREATE TABLE themes (
    id VARCHAR(16) NOT NULL, 
    name VARCHAR(32) NOT NULL, 
    category_id VARCHAR(16) NOT NULL, 
    owner_id VARCHAR(16) NOT NULL, 
    created TIMESTAMP DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
    modified TIMESTAMP, 
    status VARCHAR(8) DEFAULT 'active' NOT NULL, 
    PRIMARY KEY (id), 
    FOREIGN KEY(category_id) REFERENCES categories (id) ON DELETE CASCADE, 
    FOREIGN KEY(owner_id) REFERENCES accounts (id) ON DELETE CASCADE, 
    UNIQUE (id), 
    CHECK (status IN ('active', 'disabled'))
);

INSERT INTO alembic_version (version_num) VALUES ('0709da242beb');

