DROP TABLE IF EXISTS items CASCADE;
DROP TABLE IF EXISTS messages CASCADE;
DROP TABLE IF EXISTS public_keys CASCADE;
DROP TYPE msgtype;

CREATE TYPE msgtype AS ENUM ( 'posting', 'approval', 'image', 'img64Obv', 'img64Rev' );

CREATE TABLE public_keys (
key_id SERIAL PRIMARY KEY,
screen_name VARCHAR(50) UNIQUE NOT NULL,
address VARCHAR(50) NOT NULL,
public_key VARCHAR(255) NOT NULL,
e2ee_public_key VARCHAR(512),
ipv4 VARCHAR(50) NOT NULL,
approved_by INTEGER []
);

CREATE TABLE messages (
msg_id SERIAL PRIMARY KEY,
replying_to INTEGER,
key_id INTEGER,
FOREIGN KEY(key_id) REFERENCES public_keys(key_id),
msgtype msgtype,
msgtext TEXT NOT NULL,
nonce VARCHAR(100) NOT NULL,
address VARCHAR(100) NOT NULL,
signature VARCHAR(200) NOT NULL,
submitter VARCHAR(100) NOT NULL
);

CREATE TABLE items (
item_id SERIAL primary key,
obverse_image INTEGER NOT NULL UNIQUE,
FOREIGN KEY(obverse_image) REFERENCES messages(msg_id),
reverse_image INTEGER UNIQUE,
FOREIGN KEY(reverse_image) REFERENCES messages(msg_id),
label1 TEXT NOT NULL,
label2 TEXT,
label3 TEXT,
label4 TEXT,
nonce VARCHAR(100),
address VARCHAR(100),
signature VARCHAR(200),
submitter VARCHAR(100)
);

