CREATE TABLE users (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255) NOT NULL,
	first_name VARCHAR(255),
	last_name VARCHAR(255),
	password VARCHAR(32) NOT NULL,
	email_addr VARCHAR(128),
	service_number INT DEFAULT NULL,
	INDEX idx_name (name),
	INDEX idx_first_name (first_name),
	INDEX idx_last_name (last_name),
	INDEX idx_service_number (service_number)
) ENGINE=InnoDB;

CREATE TABLE permission_roles (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255),
	description TEXT,
	UNIQUE INDEX idx_name (name)
) ENGINE=InnoDB;

CREATE TABLE user_roles (
	user_id INT NOT NULL,
	role_id INT NOT NULL,
	PRIMARY KEY (user_id, role_id),
	FOREIGN KEY fk_users_user_id (user_id) REFERENCES users (id) ON DELETE CASCADE,
	FOREIGN KEY fk_roles_role_id (role_id) REFERENCES permission_roles (id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE permission_resources (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255),
	description TEXT,
	UNIQUE INDEX idx_name (name)
) ENGINE=InnoDB;

CREATE TABLE permission_rules (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255),
	role_id INT NOT NULL,
	resource_id INT NOT NULL,
	allowed BIT DEFAULT 1,
	INDEX idx_name (name),
	FOREIGN KEY fk_role_id (role_id) REFERENCES permission_roles (id) ON DELETE CASCADE,
	FOREIGN KEY fk_permission_resource_id (resource_id) REFERENCES permission_resources (id) ON DELETE CASCADE
) ENGINE=InnoDB;



CREATE TABLE reccs (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255) NOT NULL UNIQUE,
	acronym VARCHAR(16) NOT NULL UNIQUE,
	ord INT UNIQUE
) ENGINE=InnoDB;

CREATE TABLE user_reccs (
	user_id INT NOT NULL PRIMARY KEY,
	recc_id INT NOT NULL,
	FOREIGN KEY fk_users_user_id (user_id) REFERENCES users (id) ON DELETE CASCADE,
	FOREIGN KEY fk_reccs_recc_id (recc_id) REFERENCES reccs (id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE states (
	id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
	acronym VARCHAR(8) NOT NULL UNIQUE,
	name VARCHAR(255) NOT NULL UNIQUE
) ENGINE=InnoDB;

CREATE TABLE divisions (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255) NOT NULL UNIQUE
) ENGINE=InnoDB;

CREATE TABLE document_groups (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255) NOT NULL UNIQUE
) ENGINE=InnoDB;

CREATE TABLE documents (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255) NOT NULL,
	description TEXT,
	filepath VARCHAR(255),
	document_group INT NULL,
	FOREIGN KEY fk_document_groups_group (document_group) REFERENCES document_groups (id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE forms (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name varchar(255) NOT NULL UNIQUE,
	description varchar(255)
) ENGINE=InnoDB;

CREATE TABLE submissions (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	form_id INT NOT NULL,
	load_date DATETIME,
	filename VARCHAR(255),
	FOREIGN KEY fk_form_id (form_id) REFERENCES forms (id),
	INDEX idx_filename (filename)
) ENGINE=InnoDB;

CREATE TABLE paygrades (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	acronym varchar(16),
	name VARCHAR(64),
	rate NUMERIC(8,2),
	parent_paygrade INT DEFAULT NULL
) ENGINE=InnoDB;

CREATE INDEX idx_name ON paygrades (name);
CREATE INDEX idx_acronym ON paygrades (acronym);

CREATE TABLE login_codes (
	code VARCHAR(64) NOT NULL,
	user_id INT NOT NULL,
	valid_until TIMESTAMP NOT NULL,
	PRIMARY KEY (user_id, code),
	FOREIGN KEY fk_users_user_id (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB;


CREATE TABLE holidays (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	year INT DEFAULT NULL,
	month TINYINT DEFAULT NULL,
	day TINYINT DEFAULT NULL,
	name VARCHAR(255),
	INDEX idx_date (year, month, day),
	INDEX idx_year (year),
	INDEX idx_month (month),
	INDEX idx_day (day)
) ENGINE=InnoDB;