DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS holidays;
DROP TABLE IF EXISTS lockout_states;
DROP TABLE IF EXISTS auth_attempts;
DROP TABLE IF EXISTS user_lockout_states;

CREATE TABLE users (
	service_number INT PRIMARY KEY,
	first_name VARCHAR(255) COLLATE NOCASE,
	last_name VARCHAR(255) COLLATE NOCASE,
	email_addr VARCHAR(128) COLLATE NOCASE,
	pin CHAR(40),
	location VARCHAR(255) COLLATE NOCASE,
	level VARCHAR(255) COLLATE NOCASE,
	division VARCHAR(255) COLLATE NOCASE,
	branch VARCHAR(255) COLLATE NOCASE,
	section VARCHAR(255) COLLATE NOCASE,
	position VARCHAR(255) COLLATE NOCASE,
	cost_centre INT,
	phone VARCHAR(16),
	fax VARCHAR(16),
	mobile VARCHAR(16),
	mgmt_level TINYINT,
	manager_service_number INT,
	manager_mgmt_level TINYINT,
	uses_pin TINYINT(1),
	default_rdo_date DATE DEFAULT NULL
);

CREATE INDEX idx_first_name ON users (first_name);
CREATE INDEX idx_last_name ON users (last_name);
CREATE INDEX idx_mgmt_level ON users (mgmt_level);
CREATE INDEX idx_manager_service_number ON users (manager_service_number);
CREATE INDEX idx_manager_mgmt_level ON users (manager_mgmt_level);

CREATE TABLE lockout_states (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	duration INT NOT NULL,
	tries INT NOT NULL,
	next_state TINYINT DEFAULT NULL,
	is_start_state TINYINT(1) DEFAULT 0,
	is_locked_out TINYINT(1) DEFAULT 1
);
CREATE TABLE auth_attempts (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	service_number INT NOT NULL,
	attempt_time TIMESTAMP,
	was_success TINYINT(1) DEFAULT 0
);
CREATE TABLE user_lockout_states (
	service_number INT NOT NULL PRIMARY KEY,
	state_id TINYINT NOT NULL UNIQUE,
	start_time TIMESTAMP
);

CREATE INDEX idx_service_number ON auth_attempts (service_number);

INSERT INTO lockout_states (id, duration, tries, next_state, is_start_state, is_locked_out) VALUES (1, 0, 3, 2, 1, 0);
INSERT INTO lockout_states (id, duration, tries, next_state, is_start_state, is_locked_out) VALUES (2, 300, 2, 3, 0, 1);
INSERT INTO lockout_states (id, duration, tries, next_state, is_start_state, is_locked_out) VALUES (3, 600, 1, 4, 0, 1);
INSERT INTO lockout_states (id, duration, tries, next_state, is_start_state, is_locked_out) VALUES (4, 900, 1, 4, 0, 1);
