DROP TABLE identities;
DROP SEQUENCE identities_id_sequence;
DROP TABLE password_accounts;
DROP SEQUENCE password_accounts_id_sequence;
DROP TABLE roles;
DROP SEQUENCE roles_id_sequence;
DROP TABLE identity_roles;
DROP TABLE artifacts;
DROP TABLE sessions;
DROP TABLE name_identifiers;
DROP SEQUENCE name_identifiers_id_sequence;

CREATE TABLE identities (
	id	INTEGER PRIMARY KEY,
	name	VARCHAR(200),
	email	VARCHAR(200),
	lasso_dump TEXT,
	lasso_proxy_dump TEXT,
	resource_id	VARCHAR(200),
	entry_id	VARCHAR(200),
	locked_down	BOOLEAN
);
CREATE SEQUENCE identities_id_sequence;

CREATE TABLE password_accounts (
	id	INTEGER PRIMARY KEY,
	username	VARCHAR(200),
	password	VARCHAR(200),
	dumb_question	VARCHAR(100),
	smart_answer	VARCHAR(200),
	identity_id	INTEGER NOT NULL
);
CREATE SEQUENCE password_accounts_id_sequence;

CREATE TABLE roles (
	id	INTEGER PRIMARY KEY,
	name	VARCHAR(200)
);
CREATE SEQUENCE roles_id_sequence;
INSERT INTO roles VALUES (nextval('roles_id_sequence'), 'admin');

CREATE TABLE identity_roles (
	identity_id	INTEGER NOT NULL,
	role_id		INTEGER NOT NULL
);

CREATE TABLE artifacts (
	artifact	VARCHAR(100) PRIMARY KEY,
	session_id	VARCHAR(100),
	provider_id	TEXT
);

CREATE TABLE name_identifiers (
	id	INTEGER PRIMARY KEY,
	name_identifier	VARCHAR(256),
	identity_id INTEGER
);
CREATE SEQUENCE name_identifiers_id_sequence;
CREATE INDEX name_identifers_content ON name_identifiers (name_identifier);