Peer-to-peer mentorship

[razzius]

-- schema migration
alter table verification_email rename is_mentor to is_faculty;
alter table profile rename to faculty_profile;

CREATE TABLE student_program_option (
    id SERIAL NOT NULL, 
    date_created TIMESTAMP WITHOUT TIME ZONE NOT NULL, 
    value VARCHAR(50), 
    PRIMARY KEY (id), 
    UNIQUE (value)
);

CREATE TABLE student_year_option (
    id SERIAL NOT NULL, 
    date_created TIMESTAMP WITHOUT TIME ZONE NOT NULL, 
    value VARCHAR(50), 
    PRIMARY KEY (id), 
    UNIQUE (value)
);

CREATE TABLE student_pce_site_option (
    id SERIAL NOT NULL, 
    date_created TIMESTAMP WITHOUT TIME ZONE NOT NULL, 
    value VARCHAR(50), 
    PRIMARY KEY (id), 
    UNIQUE (value)
);

CREATE TABLE student_profile (
    id VARCHAR NOT NULL, 
    name VARCHAR(255) NOT NULL, 
    contact_email VARCHAR(120) NOT NULL, 
    profile_image_url VARCHAR(255), 
    date_created TIMESTAMP WITHOUT TIME ZONE NOT NULL, 
    date_updated TIMESTAMP WITHOUT TIME ZONE NOT NULL, 
    additional_information VARCHAR(500), 
    cadence VARCHAR(255) NOT NULL, 
    other_cadence VARCHAR(255), 
    available_for_mentoring BOOLEAN, 
    willing_discuss_personal BOOLEAN, 
    willing_student_group BOOLEAN, 
    program_id INTEGER, 
    current_year_id INTEGER, 
    pce_site_id INTEGER, 
    willing_dual_degrees BOOLEAN, 
    willing_advice_clinical_rotations BOOLEAN, 
    willing_research BOOLEAN, 
    willing_residency BOOLEAN, 
    verification_email_id INTEGER NOT NULL, 
    PRIMARY KEY (id), 
    FOREIGN KEY(program_id) REFERENCES student_program_option (id), 
    FOREIGN KEY(current_year_id) REFERENCES student_year_option (id), 
    FOREIGN KEY(pce_site_id) REFERENCES student_pce_site_option (id), 
    FOREIGN KEY(verification_email_id) REFERENCES verification_email (id)
);

alter table hospital_affiliation rename to faculty_hospital_affiliation;
alter table clinical_specialty rename to faculty_clinical_specialty;
alter table parts_of_me rename to faculty_parts_of_me;
alter table professional_interest rename to faculty_professional_interest;
alter table profile_activity rename to faculty_profile_activity;
alter table profile_degree rename to faculty_profile_degree;

CREATE TABLE student_hospital_affiliation (
    id SERIAL NOT NULL,
    tag_id INTEGER NOT NULL,
    profile_id VARCHAR NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY(tag_id) REFERENCES hospital_affiliation_option (id),
    FOREIGN KEY(profile_id) REFERENCES student_profile (id)
);

CREATE TABLE student_clinical_specialty (
    id SERIAL NOT NULL,
    tag_id INTEGER NOT NULL,
    profile_id VARCHAR NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY(tag_id) REFERENCES clinical_specialty_option (id),
    FOREIGN KEY(profile_id) REFERENCES student_profile (id)
);

CREATE TABLE student_parts_of_me (
    id SERIAL NOT NULL,
    tag_id INTEGER NOT NULL,
    profile_id VARCHAR NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY(tag_id) REFERENCES parts_of_me_option (id),
    FOREIGN KEY(profile_id) REFERENCES student_profile (id)
);

CREATE TABLE student_professional_interest (
    id SERIAL NOT NULL,
    tag_id INTEGER NOT NULL,
    profile_id VARCHAR NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY(tag_id) REFERENCES professional_interest_option (id),
    FOREIGN KEY(profile_id) REFERENCES student_profile (id)
);

CREATE TABLE student_profile_activity (
    id SERIAL NOT NULL,
    tag_id INTEGER NOT NULL,
    profile_id VARCHAR NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY(tag_id) REFERENCES activity_option (id),
    FOREIGN KEY(profile_id) REFERENCES student_profile (id)
);
-- profile star migration

CREATE TABLE new_profile_star (
    from_verification_email_id INTEGER NOT NULL, 
    to_verification_email_id INTEGER NOT NULL, 
    PRIMARY KEY (from_verification_email_id, to_verification_email_id), 
    FOREIGN KEY(from_verification_email_id) REFERENCES verification_email (id), 
    FOREIGN KEY(to_verification_email_id) REFERENCES verification_email (id)
);

insert into new_profile_star(from_verification_email_id, to_verification_email_id) select from_verification_emai
 l_id, verification_email.id as to_verification_email_id from profile_star inner join profile ON profile.id = profile_star.to_prof
 ile_id inner join verification_email on verification_email.id = profile.verification_email_id;

alter table profile_star rename to old_profile_star;
alter table new_profile_star rename to profile_star;
drop table old_profile_star;

-- inserting new tags
insert into student_program_option("value", "date_created") values
('Harvard School of Dental Medicine', now()),
('Health Sciences & Technology', now()),
('New Pathway', now()),
('Pathways', now());

insert into student_year_option("value", "date_created") values
('MS1', now()),
('MS2', now()),
('MS3', now()),
('MS4+', now()),
('DMD1', now()),
('DMD2', now()),
('DMD3', now()),
('DMD4+', now()),
('Dual Degree: EdM', now()),
('Dual Degree: MBA', now()),
('Dual Degree: MPH', now()),
('Dual Degree: MPP', now()),
('Dual Degree: PhD', now()),
('5th Year: Research', now()),
('G1', now()),
('G2', now()),
('G3', now()),
('G4+', now());

insert into student_pce_site_option("value", "date_created") values
('Beth Israel Deaconess Medical Center', now()),
('Brigham and Women''s Hospital', now()),
('Cambridge Health Alliance', now()),
('Massachusetts General Hospital', now()),
('Harvard School of Dental Medicine', now());