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

-- Disable foreign key checks and commits
SET FOREIGN_KEY_CHECKS=0;
SET AUTOCOMMIT = 0;

-- CREATE UNIVERSITIES TABLE
CREATE OR REPLACE Table Universities (
    universityID INT NOT NULL AUTO_INCREMENT UNIQUE,
    universityName VARCHAR(255) NOT NULL,
    city VARCHAR(255) NOT NULL,
    state ENUM('AL', 'AK', 'AZ', 'AR', 'CA', 'CO', 'CT', 'DE', 'DC', 'FL', 'GA',
               'HI', 'ID', 'IL', 'IN', 'IA', 'KS', 'KY', 'LA', 'ME', 'MD', 'MA',
               'MI', 'MN', 'MS', 'MO', 'MT', 'NE', 'NV', 'NH', 'NJ', 'NM', 'NY',
               'NC', 'ND', 'OH', 'OK', 'OR', 'PA', 'RI', 'SC', 'SD', 'TN', 'TX',
               'UT', 'VT', 'VA', 'WA', 'WV', 'WI', 'WY') NOT NULL,
    partnerDate VARCHAR(50) NOT NULL,
    PRIMARY KEY (universityID)
);

-- CREATE PROGRAMS TABLE
-- universityID FK links to Universities
CREATE OR REPLACE Table Programs (
    programID INT NOT NULL AUTO_INCREMENT UNIQUE,
    programName VARCHAR(255) NOT NULL,
    startDate VARCHAR(50) NOT NULL,
    endDate VARCHAR(50) NOT NULL,
    cost DECIMAL(19,2) NOT NULL,
    academicTerm ENUM('AUT', 'SUM', 'WIN', 'SPR') NOT NULL,
    year YEAR NOT NULL,
    country VARCHAR(2) NOT NULL,
    universityID INT NOT NULL,
    PRIMARY KEY (programID),
    CONSTRAINT fk_Programs_Universities FOREIGN KEY (universityID)
        REFERENCES Universities (universityID)
        ON DELETE CASCADE
);

-- CREATE FACULTY LEADERS TABLE
-- universityID FK links to Universities (can be set to NULL)
CREATE OR REPLACE Table FacultyLeaders (
    facultyID INT NOT NULL AUTO_INCREMENT UNIQUE,
    facultyName VARCHAR(255) NOT NULL,
    facultyEmail VARCHAR(255) NOT NULL,
    facultyPhone VARCHAR(45) NULL,
    department VARCHAR(255) NOT NULL,
    universityID INT NULL,
    PRIMARY KEY (facultyID),
    CONSTRAINT fk_FacultyLeaders_Universities FOREIGN KEY (universityID)
        REFERENCES Universities (universityID)
        ON DELETE SET NULL
);

-- CREATE STUDENTS TABLE
-- universityID FK links to Universities (can be set to NULL)
CREATE OR REPLACE Table Students (
    studentID INT NOT NULL AUTO_INCREMENT UNIQUE,
    studentName VARCHAR(255) NOT NULL,
    studentEmail VARCHAR(255) NOT NULL,
    studentPhone VARCHAR(45) NULL,
    dateOfBirth VARCHAR(50) NOT NULL,
    major VARCHAR(255) NULL,
    graduationYear YEAR NOT NULL,
    universityID INT NULL,
    PRIMARY KEY (studentID),
    CONSTRAINT fk_Students_Universities FOREIGN KEY (universityID)
        REFERENCES Universities (universityID)
        ON DELETE SET NULL
);

-- CREATE INTERSECTION TABLE FOR PROGRAMS AND FACULTY LEADERS
CREATE OR REPLACE Table ProgramsFacultyDetails (
    programsFacultyDetailsID INT NOT NULL AUTO_INCREMENT UNIQUE,
    programID INT NOT NULL,
    facultyID INT NOT NULL,
    CONSTRAINT unique_faculty_program UNIQUE (facultyID, programID),
    PRIMARY KEY (programsFacultyDetailsID),
    CONSTRAINT fk_Programs_ProgramsFacultyDetails FOREIGN KEY (programID)
        REFERENCES Programs(programID)
        ON DELETE CASCADE,
    CONSTRAINT fk_Faculty_ProgramsFacultyDetails FOREIGN KEY (facultyID)
        REFERENCES FacultyLeaders(facultyID)
        ON DELETE CASCADE
);

-- CREATE INTERSECTION TABLE FOR PROGRAMS AND STUDENTS
CREATE OR REPLACE Table ProgramsStudentsDetails (
    programsStudentsDetailsID INT NOT NULL AUTO_INCREMENT UNIQUE,
    programID INT NOT NULL,
    studentID INT NOT NULL,
    CONSTRAINT unique_students_program UNIQUE (studentID, programID),
    PRIMARY KEY (programsStudentsDetailsID),
    CONSTRAINT fk_Programs_ProgramsStudentsDetails FOREIGN KEY (programID)
        REFERENCES Programs (programID)
        ON DELETE CASCADE,
    CONSTRAINT fk_Students_ProgramsStudentsDetails FOREIGN KEY (studentID)
        REFERENCES Students (studentID)
        ON DELETE CASCADE
);

-- INSERT SAMPLE DATA INTO UNIVERSITIES
INSERT INTO Universities (universityName, city, state, partnerDate)
    VALUES ('Oregon State University', 'Corvallis', 'OR', '2000-09-27'),
    ('Adventure College', 'Sunnyside', 'ID', '2005-02-15'),
    ('Explorer Institution', 'Seattle', 'WA', '2008-10-01'),
    ('University of Travel', 'Oceanview', 'CA', '2014-08-30');

-- INSERT SAMPLE DATA INTO PROGRAMS
INSERT INTO Programs (programName, startDate, endDate, cost, academicTerm, year, country, universityID)
    VALUES ('Modern Architecture', '2023-06-15', '2023-07-03', 3999.00, 'SUM', 2023, 'ES', 2),
    ('Computer Science', '2023-09-30', '2023-10-21', 3500.99, 'AUT', 2023, 'US', 3),
    ('Elementary Education', '2024-04-10', '2024-04-30', 2750.00, 'SPR', 2024, 'UK', 4),
    ('Public Health', '2024-01-15', '2024-02-03', 2999.99, 'WIN', 2024, 'ES', 3);

-- INSERT SAMPLE DATA INTO FACULTY LEADERS
INSERT INTO FacultyLeaders (facultyName, facultyEmail, facultyPhone, department, universityID)
    VALUES ('Victoria Builder', 'vbuilder@adventure.edu', NULL, 'Architecture', 2),
    ('John Database', 'john@ei.edu', '555-555-5555', 'Computer Science', 3),
    ('Terry Teacher', 'terry@unitravel.edu', '555-123-4567', 'Education', 4),
    ('Chris Healer', 'chealer@ei.edu', NULL, 'Health Sciences', 3),
    ('Jane Tutor', 'jane@unitravel.edu', '555-987-6543', 'Education', 4);

-- INSERT SAMPLE DATA INTO STUDENTS
INSERT INTO Students (studentName, studentEmail, studentPhone, dateOfBirth, major, graduationYear, universityID)
    VALUES ('Agnes Student', 'agnes@unitravel.edu', NULL, '2001-12-10', 'Teaching', 2024, 4),
    ('Casey Studies', 'cstudies@ei.edu', NULL, '2004-05-15', 'Health Tech', 2026, 3),
    ('Robert Reads', 'rr@adventure.edu', '555-888-0090', '2003-08-18', 'Urban Studies', 2025, 2),
    ('Penelope Pencil', 'ppencil@adventure.edu', NULL, '2002-03-12', 'Art Studies', 2025, 2),
    ('Mary Thinker', 'mthinker@unitravel.edu', '555-239-9873', '2003-04-30', 'Social Work', 2025, 4);

-- INSERT SAMPLE DATA INTO PROGRAMS AND FACULTY LEADERS INTERSECTION TABLE
INSERT INTO ProgramsFacultyDetails (programID, facultyID)
    VALUES (1, 1), (2, 2), (3, 3), (3, 5), (4, 4);

-- INSERT SAMPLE DATA INTO PROGRAMS AND STUDENTS INTERSECTION TABLE
INSERT INTO ProgramsStudentsDetails (programID, studentID)
    VALUES (1, 3), (1, 4), (2, 2), (3, 1), (3, 5), (4, 2);
    

-- Re-enable foreign key checks and commits
SET FOREIGN_KEY_CHECKS=1;
COMMIT;