-- Roles permissions table
CREATE TABLE IF NOT EXISTS rolesPermissions (
  roleID INT PRIMARY KEY,
  permissionLevel VARCHAR(255) NOT NULL,
  permissionLevelDescription VARCHAR(255) NOT NULL
);

-- Users table (Users are the login profiles for people)
CREATE TABLE IF NOT EXISTS  users (
  userID INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(255) NOT NULL,
  roleID INT NOT NULL,
  FOREIGN KEY (roleID) REFERENCES rolesPermissions(roleID)
);

-- Table to log login/logout information
CREATE TABLE IF NOT EXISTS userLoginLogout (
  eventId INT AUTO_INCREMENT PRIMARY KEY,
  eventTimestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  userID INT NOT NULL, 
  eventType VARCHAR(255) NOT NULL,   -- successfulLogin, unsuccessfulLogin, logout
  FOREIGN KEY (userID) REFERENCES users(userID)
);



CREATE TABLE IF NOT EXISTS degreeTypeLookup (
    degreeTypeId INT PRIMARY KEY AUTO_INCREMENT,
    degreeType ENUM('AS', 'BS', 'MS', 'PhD')
);

CREATE TABLE IF NOT EXISTS involvementType (
    involvementTypeId INT PRIMARY KEY AUTO_INCREMENT,
    description VARCHAR(255) NOT NULL
);

CREATE TABLE IF NOT EXISTS stateLookup (
    stateId INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE IF NOT EXISTS countryLookup (
    countryId INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE IF NOT EXISTS address (
    addressId INT PRIMARY KEY AUTO_INCREMENT,
    address1 VARCHAR(255) NOT NULL,
    address2 VARCHAR(255),
    city VARCHAR(255) NOT NULL,
    stateId INT,
    countryId INT,
    zipCode VARCHAR(20) NOT NULL,
    FOREIGN KEY (stateId) REFERENCES stateLookup(stateId),
    FOREIGN KEY (countryId) REFERENCES countryLookup(countryId)
);

CREATE TABLE IF NOT EXISTS people (
    peopleId INT PRIMARY KEY AUTO_INCREMENT,
    firstName VARCHAR(255) NOT NULL,
    lastName VARCHAR(255) NOT NULL,
    middleName VARCHAR(255),
    maidenName VARCHAR(255),
    suffix VARCHAR(32),
    nickName VARCHAR(255),
    techAlumniChapter INT, 
    classYear VARCHAR(16) NOT NULL,
    gradYear VARCHAR(16),
    gradSemester ENUM('fall', 'winter', 'spring', 'summer'),
    gender ENUM('male', 'female', 'other')
);

CREATE TABLE IF NOT EXISTS peopleDegree (
    peopleDegreeId INT PRIMARY KEY AUTO_INCREMENT,
    peopleId INT, 
    degreeTypeId INT, 
    degreeDepartment VARCHAR(50),
    degreeCollege VARCHAR(50),
    degreeYear VARCHAR(16),
    degreeDescription VARCHAR(255),
    FOREIGN KEY (peopleId) REFERENCES people(peopleId),
    FOREIGN KEY (degreeTypeId) REFERENCES degreeTypeLookup(degreeTypeId)
);

CREATE TABLE IF NOT EXISTS chapterLookup (
    chapterId INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE IF NOT EXISTS peopleXAddress (
    peopleId INT PRIMARY KEY,
    addressId INT UNIQUE,
    preferredAddress VARCHAR(255) NOT NULL,
    FOREIGN KEY (peopleId) REFERENCES people(peopleId),
    FOREIGN KEY (addressId) REFERENCES address(addressId)
);

CREATE TABLE IF NOT EXISTS peopleContact (
    peopleContactId INT PRIMARY KEY AUTO_INCREMENT,
    peopleId INT,
    contactNumber VARCHAR(255) NOT NULL,
    contactType VARCHAR(50) NOT NULL, 
    preferredContact BOOLEAN NOT NULL DEFAULT FALSE,
    FOREIGN KEY (peopleId) REFERENCES people(peopleId)
);

CREATE TABLE IF NOT EXISTS involvementLookup (
    involvementId INT PRIMARY KEY AUTO_INCREMENT,
    involvementType INT,
    FOREIGN KEY (involvementType) REFERENCES involvementType(involvementTypeId)
);

CREATE TABLE IF NOT EXISTS peopleXInvolvement (
    peopleId INT,
    involvementId INT,
    PRIMARY KEY (peopleId, involvementId),
    FOREIGN KEY (peopleId) REFERENCES people(peopleId),
    FOREIGN KEY (involvementId) REFERENCES involvementLookup(involvementId)
);

INSERT INTO stateLookup (stateId, name) 
VALUES (1, 'Virginia');

INSERT INTO countryLookup (countryId, name) 
VALUES (1, 'United States');

-- I think there is already a people table that exists in our DB and 
-- has data that does not include peopoleID