package dmo.fs.db.dodex.mariadb;

import dmo.fs.db.dodex.CreateDatabase;

import java.util.Locale;

public abstract class DbMariadb implements CreateDatabase {
	protected final static String CHECKLOGINSQL = "select 1 from information_schema.tables where table_name='LOGIN'";
	protected final static String CHECKUSERSQL = "select 1 from information_schema.tables where table_name='users'";
	protected final static String CHECKMESSAGESSQL = "select 1 from information_schema.tables where table_name='messages'";
	protected final static String CHECKUNDELIVEREDSQL = "select 1 from information_schema.tables where table_name='undelivered'";
	protected final static String CHECKGROUPSSQL = "select 1 from information_schema.tables where table_name='groups'";
	protected final static String CHECKMEMBERSQL = "select 1 from information_schema.tables where table_name='member'";
	protected final static String CHECKHANDICAPSQL = "SELECT table_name FROM information_schema.tables WHERE table_name in ('golfer', 'course', 'scores', 'ratings')";
	protected final static String SELECTONE = "SELECT 1;";
	protected enum CreateTable {
		CREATEUSERS(
				"CREATE TABLE users (" +
						"id INT NOT NULL AUTO_INCREMENT," +
						"name VARCHAR(255) CHARACTER SET utf8mb4 collate  utf8mb4_bin NOT NULL," +
						"password VARCHAR(255) NOT NULL," +
						"ip VARCHAR(255) NOT NULL," +
						"last_login DATETIME NOT NULL," +
						"PRIMARY KEY (id)," +
						"UNIQUE INDEX name_password_UNIQUE (name ASC, password ASC));"),
		CREATEMESSAGES(
				"CREATE TABLE messages (" +
						"id INT NOT NULL AUTO_INCREMENT," +
						"message MEDIUMTEXT NOT NULL," +
						"from_handle VARCHAR(255) CHARACTER SET utf8mb4 collate  utf8mb4_bin NOT NULL," +
						"post_date DATETIME NOT NULL," +
						"PRIMARY KEY (id));"),
		CREATEUNDELIVERED(
				"CREATE TABLE undelivered (" +
						"user_id INT NOT NULL," +
						"message_id INT NOT NULL," +
						"INDEX fk_undelivered_users_idx (user_id ASC)," +
						"INDEX fk_undelivered_messages_idx (message_id ASC)," +
						"CONSTRAINT fk_undelivered_users " +
						"FOREIGN KEY (user_id) " +
						"REFERENCES users (id) " +
						"ON DELETE NO ACTION " +
						"ON UPDATE NO ACTION," +
						"CONSTRAINT fk_undelivered_messages " +
						"FOREIGN KEY (message_id) " +
						"REFERENCES messages (id) " +
						"ON DELETE NO ACTION " +
						"ON UPDATE NO ACTION);"),
		CREATEGOLFER(
				"CREATE TABLE IF NOT EXISTS golfer (" +
						"PIN CHARACTER(8) primary key NOT NULL," +
						"FIRST_NAME VARCHAR(32) NOT NULL," +
						"LAST_NAME VARCHAR(32) NOT NULL," +
						"HANDICAP DOUBLE(4,1) DEFAULT 0.0," +
						"COUNTRY CHARACTER(2) DEFAULT 'US' NOT NULL," +
						"STATE CHARACTER(2) DEFAULT 'NV' NOT NULL," +
						"OVERLAP_YEARS BOOLEAN," +
						"PUBLIC BOOLEAN," +
						"LAST_LOGIN TIMESTAMP NOT NULL)"),
		CREATECOURSE(
				"CREATE TABLE IF NOT EXISTS course (" +
						"COURSE_SEQ INTEGER primary key auto_increment NOT NULL," +
						"COURSE_NAME VARCHAR(128) NOT NULL," +
						"COURSE_COUNTRY VARCHAR(128) NOT NULL," +
						"COURSE_STATE CHARACTER(2) NOT NULL )"),
		CREATERATINGS(
				"CREATE TABLE IF NOT EXISTS ratings (" +
						"COURSE_SEQ INTEGER NOT NULL," +
						"TEE INTEGER NOT NULL," +
						"TEE_COLOR VARCHAR(16)," +
						"TEE_RATING DOUBLE(4,1) NOT NULL," +
						"TEE_SLOPE INTEGER NOT NULL," +
						"TEE_PAR INTEGER DEFAULT '72' NOT NULL, PRIMARY KEY (COURSE_SEQ, TEE)," +
						"INDEX idx_rating_course (course_seq ASC)," +
						"CONSTRAINT fk_course_ratings " +
						"FOREIGN KEY (COURSE_SEQ) " +
						"REFERENCES course (COURSE_SEQ) " +
						"ON DELETE NO ACTION " +
						"ON UPDATE NO ACTION)"),
		CREATESCORES(
				"CREATE TABLE IF NOT EXISTS scores (" +
						"PIN CHARACTER(8) NOT NULL," +
						"GROSS_SCORE INTEGER NOT NULL," +
						"NET_SCORE DOUBLE(4,1)," +
						"ADJUSTED_SCORE INTEGER NOT NULL," +
						"TEE_TIME TEXT NOT NULL," +
						"HANDICAP DOUBLE(4,1)," +
						"COURSE_SEQ INTEGER," +
						"COURSE_TEES INTEGER," +
						"USED CHARACTER(1)," +
						"CONSTRAINT fk_course_scores " +
						"FOREIGN KEY (COURSE_SEQ) " +
						"REFERENCES course (COURSE_SEQ) " +
						"ON DELETE NO ACTION " +
						"ON UPDATE NO ACTION," +
						"CONSTRAINT fk_golfer_scores " +
						"FOREIGN KEY (PIN) " +
						"REFERENCES golfer (PIN) " +
						"ON DELETE NO ACTION " +
						"ON UPDATE NO ACTION)"),
		CREATEGROUPS(
				"CREATE TABLE IF NOT EXISTS groups (" +
						"ID INTEGER primary key auto_increment NOT NULL," +
						"NAME VARCHAR(24) NOT NULL," +
						"OWNER INTEGER NOT NULL DEFAULT 0," +
						"CREATED DATETIME NOT NULL DEFAULT current_timestamp()," +
						"UPDATED DATETIME DEFAULT NULL ON UPDATE current_timestamp()," +
						"UNIQUE KEY unique_on_name (NAME))"
		),
		CREATEMEMBER(
				"CREATE TABLE IF NOT EXISTS member (" +
						"GROUP_ID INTEGER NOT NULL DEFAULT 0," +
						"USER_ID INTEGER NOT NULL DEFAULT 0," +
						"PRIMARY KEY (GROUP_ID,USER_ID)," +
						"KEY U_fk_MEMBER_USER_idx (USER_ID)," +
						"CONSTRAINT U_fk_MEMBER_GROUP FOREIGN KEY (GROUP_ID) REFERENCES groups (ID) ON DELETE NO ACTION ON UPDATE NO ACTION," +
						"CONSTRAINT U_fk_MEMBER_USER FOREIGN KEY (USER_ID) REFERENCES users (ID) ON DELETE NO ACTION ON UPDATE NO ACTION)"
		);

		String sql;

		CreateTable(String sql) {
			this.sql = sql;
		}
	}

	public String getCreateTable(String table) {
		return CreateTable.valueOf("CREATE" + table.toUpperCase(Locale.US)).sql;
	}
}