/*******************************************************
Authors: Kristin Eberman & Alexander Giardino (Team 131)
Project: Education Expeditions Database
CS340 | Winter 2023 | Oregon State University
DATA MANIPULATION QUERIES
********************************************************/

-- UNIVERSITIES --
-- SELECT: query to display the universities
SELECT universityName, city, state, partnerDate
    FROM Universities
    ORDER BY universityName;

-- INSERT: query to add a new university
-- uses ~ character to denote variables from the backend
INSERT INTO Universities (universityName, city, state, partnerDate)
    VALUES (~universityName, ~uniCity, ~uniState_from_dropdown, ~uniPartnerDate);

-- UPDATE: query to update university information
-- uses ~ character to denote variables from the backend
UPDATE Universities 
    SET universityName = ~universityName, city = ~universityCity, state = ~universityState,
    partnerDate = ~universityPartnerDate
    WHERE universityID = ~universityID_from_update_form;

-- DELETE: query to delete a university (ex: no longer a partner with our company)
-- uses ~ character to denote variables from the backend
DELETE FROM Universities WHERE universityID = ~universityID_from_delete_form;


-- PROGRAMS --
-- SELECT: query to display the programs
SELECT programName, startDate, endDate, cost, academicTerm, year, country, universityName
    FROM Programs
    INNER JOIN Universities ON Programs.universityID = Universities.universityID
    ORDER BY programName;

-- INSERT: query to add a new program
-- uses ~ character to denote variables from the backend
INSERT INTO Programs (programName, startDate, endDate, cost, academicTerm, 
    year, country, universityID)
    VALUES (~programName, ~startDate, ~endDate, ~cost, ~academicTerm, ~year, 
    ~country_from_dropdown, ~universityID_from_dropdown);

-- UPDATE: query to update program information
-- uses ~ character to denote variables from the backend
UPDATE Programs 
    SET programName = ~programName, startDate = ~startDate, endDate = ~endDate, cost = ~cost,
    academicTerm = ~academicTerm, year = ~year, country = ~country_from_dropdown,
    universityID = ~universityID_from_dropdown
    WHERE programID = ~programID_from_update_form;

-- DELETE: query to delete a program
-- uses ~ character to denote variables from the backend
DELETE FROM Programs WHERE programID = ~programID_from_delete_form;


-- FACULTY LEADERS --
-- SELECT: query to display faculty leaders
SELECT facultyName, facultyEmail, facultyPhone, department, universityName
    FROM FacultyLeaders
    INNER JOIN Universities ON FacultyLeaders.universityID = Universities.universityID
    ORDER BY department;

-- INSERT: query to add a new faculty leader
-- uses ~ character to denote variables from the backend
INSERT INTO FacultyLeaders (facultyName, facultyEmail, facultyPhone, department, universityID)
    VALUES (~facultyName, ~facultyEmail, ~facultyPhone, ~department, ~universityID_from_dropdown);

-- UPDATE: query to update faculty information
-- uses ~ character to denote variables from the backend
UPDATE FacultyLeaders 
    SET facultyName = ~facultyName, facultyEmail = ~facultyEmail, facultyPhone = ~facultyPhone,
    department = ~department, universityID = ~universityID_from_dropdown
    WHERE facultyID = ~facultyID_from_update_form;

-- DELETE: query to delete a faculty leader
-- uses ~ character to denote variables from the backend
DELETE FROM FacultyLeaders WHERE facultyID = ~facultyID_from_delete_form;


-- STUDENTS --
-- SELECT: query to display students
SELECT studentName, studentEmail, studentPhone, dateOfBirth, major, graduationYear, universityName
    FROM Students
    INNER JOIN Universities ON Students.universityID = Universities.universityID
    ORDER BY studentName;

-- INSERT: query to add a new student
-- uses ~ character to denote variables from the backend
INSERT INTO Students (studentName, studentEmail, studentPhone, dateOfBirth, 
    major, graduationYear, universityID)
    VALUES (~studentName, ~studentEmail, ~studentPhone, ~dateOfBirth, ~major, 
    ~graduationYear, ~universityID_from_dropdown);

-- UPDATE: query to update student information
-- uses ~ character to denote variables from the backend
UPDATE Students 
    SET studentName = ~studentName, studentEmail = ~studentEmail, studentPhone = ~studentPhone,
    dateOfBirth = ~dateOfBirth, major = ~major, graduationYear = ~graduationYear, 
    universityID = ~universityID_from_dropdown
    WHERE studentID = ~studentID_from_update_form;

-- DELETE: query to delete a student
-- uses ~ character to denote variables from the backend
DELETE FROM Students WHERE studentID = ~studentID_from_delete_form;


-- PROGRAMS DETAILS --
-- SELECT: query to show Program:Faculty relationships when user selects a specific program 
SELECT programName, facultyName 
    FROM Programs 
    INNER JOIN ProgramsFacultyDetails ON ProgramsFacultyDetails.programID = Programs.programID 
    INNER JOIN FacultyLeaders ON ProgramsFacultyDetails.facultyID = FacultyLeaders.facultyID 
    WHERE Programs.programID = ~programID_from_update_form;

-- SELECT: query to show Program:University relationships when user selects a specific program 
SELECT programName, universityName 
    FROM Programs 
    INNER JOIN Universities ON Programs.universityID = Universities.universityID 
    WHERE Programs.programID = ~programID_from_update_form;

-- SELECT: query to show Program:Student relationships when user selects a specific program 
SELECT programName, Programs.programID, studentName, Students.studentID 
    FROM Programs 
    INNER JOIN ProgramsStudentsDetails ON ProgramsStudentsDetails.programID = Programs.programID 
    INNER JOIN Students ON ProgramsStudentsDetails.studentID = Students.studentID 
    WHERE Programs.programID = ~programID_from_update_form;

-- DELETE: query to remove student from program
DELETE 
    FROM ProgramsStudentsDetails WHERE studentID = ~studentID_from_update_form AND programID = ~programID_from_update_form

-- PROGRAMS W/ FACULTY LEADERS --
-- INSERT: query to add a new Programs:FacultyLeaders M:M relationship
-- uses ~ character to denote variables from the backend
INSERT INTO ProgramsFacultyDetails (programID, facultyID)
    VALUES (~programID_from_dropdown, ~facultyID_from_dropdown);

-- PROGRAMS W/ STUDENTS --
-- INSERT: query to add a new Programs:Students M:M relationship
-- uses ~ character to denote variables from the backend
INSERT INTO ProgramsStudentsDetails (programID, studentID)
    VALUES (~programID_from_dropdown, ~studentID_from_dropdown);