SELECT * FROM `packet_item_records`;
SELECT * FROM `packet_data`;
SELECT * FROM `packet_students`;
SELECT * FROM `miscon_packetdata_refs`;
SELECT * FROM `miscon_packet_refs`;

-- Update packet records with empty year columns (a requirement of the new scripts)
SELECT *, YEAR(created) FROM packets WHERE packet_year = 0;
UPDATE packets SET packet_year = YEAR(created) WHERE packet_year = 0;

-- Populate the packet_students table
SELECT packet_ref, packet_number, IF(LENGTH(gender) = 1, gender, 'O'), IF(LENGTH(language) = 2, language, '--'), IF(grade > 0, grade, 0), IF(age > 0, age, 0) FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id GROUP BY packet_ref, packet_number ORDER BY packet_ref, packet_number;
REPLACE INTO packet_students (packet_ref, student_id, gender, primary_lang, grade, age) SELECT packet_ref, packet_number, IF(LENGTH(gender) = 1, gender, 'O'), IF(LENGTH(language) = 2, language, '--'), IF(grade > 0, grade, 0), IF(age > 0, age, 0) FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id GROUP BY packet_ref, packet_number ORDER BY packet_ref, packet_number;

-- Populate the miscon_packetdata_refs table with information from the miscon_packet_refs table
SELECT miscon_ref, packet_ref, packet_number, item_ref FROM miscon_packet_refs JOIN packet_item_records ON packet_record_ref = packet_item_records.id JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;
REPLACE INTO miscon_packetdata_refs (miscon_ref, packet_ref, student_ref, item_ref) SELECT miscon_ref, packet_ref, packet_number, item_ref FROM miscon_packet_refs JOIN packet_item_records ON packet_record_ref = packet_item_records.id JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;

-- Populate the packet_data table with the answers for each student. Must be done one answer at a time.
SELECT * FROM `packet_item_records` WHERE no_response NOT IN (0,1);
SELECT packet_ref, packet_number, item_ref, '0A', no_response, '' FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;
REPLACE INTO packet_data (packet_ref, student_ref, item_ref, qnum, answer, answer_comment) SELECT packet_ref, packet_number, item_ref, '0A', no_response, '' FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;

SELECT * FROM `packet_item_records` WHERE unfinished NOT IN (0,1);
SELECT packet_ref, packet_number, item_ref, '0B', unfinished, '' FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;
REPLACE INTO packet_data (packet_ref, student_ref, item_ref, qnum, answer, answer_comment) SELECT packet_ref, packet_number, item_ref, '0B', unfinished, '' FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;

SELECT * FROM `packet_item_records` WHERE flag NOT IN (0,1);
SELECT packet_ref, packet_number, item_ref, '0C', flag, q13 FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;
REPLACE INTO packet_data (packet_ref, student_ref, item_ref, qnum, answer, answer_comment) SELECT packet_ref, packet_number, item_ref, '0C', flag, q13 FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;

SELECT * FROM `packet_item_records` WHERE exclude NOT IN (0,1);
SELECT packet_ref, packet_number, item_ref, '0D', exclude, '' FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;
REPLACE INTO packet_data (packet_ref, student_ref, item_ref, qnum, answer, answer_comment) SELECT packet_ref, packet_number, item_ref, '0D', exclude, '' FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;

SELECT * FROM `packet_item_records` WHERE evidence NOT IN (0,1,2,3,4);
SELECT packet_ref, packet_number, item_ref, '0E', evidence, '' FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;
REPLACE INTO packet_data (packet_ref, student_ref, item_ref, qnum, answer, answer_comment) SELECT packet_ref, packet_number, item_ref, '0E', evidence, '' FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;

SELECT * FROM `packet_item_records` WHERE exclude2 NOT IN (0,1,2,3,4);
SELECT packet_ref, packet_number, item_ref, '0F', exclude2, exclude_notes FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;
REPLACE INTO packet_data (packet_ref, student_ref, item_ref, qnum, answer, answer_comment) SELECT packet_ref, packet_number, item_ref, '0F', exclude2, exclude_notes FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;

SELECT packet_ref, packet_number, item_ref, '0H', '', q12 FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;
REPLACE INTO packet_data (packet_ref, student_ref, item_ref, qnum, answer, answer_comment) SELECT packet_ref, packet_number, item_ref, '0H', '', q12 FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;

SELECT * FROM `packet_item_records` WHERE confusing NOT IN (0,1,2,3);
SELECT packet_ref, packet_number, item_ref, '1', confusing, q1 FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;
SELECT packet_ref, packet_number, item_ref, '1', IF(confusing=1, 'Y', IF(confusing=2, 'N', '')), q1 FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;
REPLACE INTO packet_data (packet_ref, student_ref, item_ref, qnum, answer, answer_comment) SELECT packet_ref, packet_number, item_ref, '1', IF(confusing=1, 'Y', IF(confusing=2, 'N', '')), q1 FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;

SELECT packet_ref, packet_number, item_ref, '2', '', q2 FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;
REPLACE INTO packet_data (packet_ref, student_ref, item_ref, qnum, answer, answer_comment) SELECT packet_ref, packet_number, item_ref, '2', '', q2 FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;

SELECT * FROM `packet_item_records` WHERE A NOT IN (0,1,2,3,4,5,6,7);
SELECT packet_ref, packet_number, item_ref, '3', A, q3 FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;
SELECT packet_ref, packet_number, item_ref, '3', IF(A=1, 'Y', IF(A=2, 'N', IF(A=3, 'NS', IF(A=5, 'Y|N', IF(A=6, 'Y|NS', IF(A=7, 'N|NS', IF(A=4, '', ''))))))), q3 FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;
REPLACE INTO packet_data (packet_ref, student_ref, item_ref, qnum, answer, answer_comment) SELECT packet_ref, packet_number, item_ref, '3', IF(A=1, 'Y', IF(A=2, 'N', IF(A=3, 'NS', IF(A=5, 'Y|N', IF(A=6, 'Y|NS', IF(A=7, 'N|NS', IF(A=4, '', ''))))))), q3 FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;

SELECT * FROM `packet_item_records` WHERE B NOT IN (0,1,2,3,4,5,6,7);
SELECT packet_ref, packet_number, item_ref, '4', B, q4 FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;
SELECT packet_ref, packet_number, item_ref, '4', IF(B=1, 'Y', IF(B=2, 'N', IF(B=3, 'NS', IF(B=5, 'Y|N', IF(B=6, 'Y|NS', IF(B=7, 'N|NS', IF(B=4, '', ''))))))), q4 FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;
REPLACE INTO packet_data (packet_ref, student_ref, item_ref, qnum, answer, answer_comment) SELECT packet_ref, packet_number, item_ref, '4', IF(B=1, 'Y', IF(B=2, 'N', IF(B=3, 'NS', IF(B=5, 'Y|N', IF(B=6, 'Y|NS', IF(B=7, 'N|NS', IF(B=4, '', ''))))))), q4 FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;

SELECT * FROM `packet_item_records` WHERE C NOT IN (0,1,2,3,4,5,6,7);
SELECT packet_ref, packet_number, item_ref, '5', C, q5 FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;
SELECT packet_ref, packet_number, item_ref, '5', IF(C=1, 'Y', IF(C=2, 'N', IF(C=3, 'NS', IF(C=5, 'Y|N', IF(C=6, 'Y|NS', IF(C=7, 'N|NS', IF(C=4, '', ''))))))), q5 FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;
REPLACE INTO packet_data (packet_ref, student_ref, item_ref, qnum, answer, answer_comment) SELECT packet_ref, packet_number, item_ref, '5', IF(C=1, 'Y', IF(C=2, 'N', IF(C=3, 'NS', IF(C=5, 'Y|N', IF(C=6, 'Y|NS', IF(C=7, 'N|NS', IF(C=4, '', ''))))))), q5 FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;

SELECT * FROM `packet_item_records` WHERE D NOT IN (0,1,2,3,4,5,6,7);
SELECT packet_ref, packet_number, item_ref, '6', D, q6 FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;
SELECT packet_ref, packet_number, item_ref, '6', IF(D=1, 'Y', IF(D=2, 'N', IF(D=3, 'NS', IF(D=5, 'Y|N', IF(D=6, 'Y|NS', IF(D=7, 'N|NS', IF(D=4, '', ''))))))), q6 FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;
REPLACE INTO packet_data (packet_ref, student_ref, item_ref, qnum, answer, answer_comment) SELECT packet_ref, packet_number, item_ref, '6', IF(D=1, 'Y', IF(D=2, 'N', IF(D=3, 'NS', IF(D=5, 'Y|N', IF(D=6, 'Y|NS', IF(D=7, 'N|NS', IF(D=4, '', ''))))))), q6 FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;

SELECT * FROM `packet_item_records` WHERE guessed NOT IN (0,1,2,3);
SELECT packet_ref, packet_number, item_ref, '7', guessed, '' FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;
SELECT packet_ref, packet_number, item_ref, '7', IF(guessed=1, 'Y', IF(guessed=2, 'N', '')), '' FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;
REPLACE INTO packet_data (packet_ref, student_ref, item_ref, qnum, answer, answer_comment) SELECT packet_ref, packet_number, item_ref, '7', IF(guessed=1, 'Y', IF(guessed=2, 'N', '')), '' FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;

SELECT * FROM `packet_item_records` WHERE other_answer NOT IN (0,1,2);
SELECT packet_ref, packet_number, item_ref, '8', other_answer, q7 FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;
SELECT packet_ref, packet_number, item_ref, '8', IF(other_answer=1, 'Y', IF(other_answer=2, 'N', '')), q7 FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;
REPLACE INTO packet_data (packet_ref, student_ref, item_ref, qnum, answer, answer_comment) SELECT packet_ref, packet_number, item_ref, '8', IF(other_answer=1, 'Y', IF(other_answer=2, 'N', '')), q7 FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;

SELECT * FROM `packet_item_records` WHERE picture NOT IN (0,1,2,3);
SELECT packet_ref, packet_number, item_ref, '9', picture, q8 FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;
SELECT packet_ref, packet_number, item_ref, '9', IF(picture=1, 'Y', IF(picture=2, 'N', '')), q8 FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;
REPLACE INTO packet_data (packet_ref, student_ref, item_ref, qnum, answer, answer_comment) SELECT packet_ref, packet_number, item_ref, '9', IF(picture=1, 'Y', IF(picture=2, 'N', '')), q8 FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;

SELECT * FROM `packet_item_records` WHERE studied NOT IN (0,1,2,3);
SELECT packet_ref, packet_number, item_ref, '10', studied, '' FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;
SELECT packet_ref, packet_number, item_ref, '10', IF(studied=1, 'Y', IF(studied=2, 'N', IF(studied=3, 'NS', ''))), '' FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;
REPLACE INTO packet_data (packet_ref, student_ref, item_ref, qnum, answer, answer_comment) SELECT packet_ref, packet_number, item_ref, '10', IF(studied=1, 'Y', IF(studied=2, 'N', IF(studied=3, 'NS', ''))), '' FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;

SELECT * FROM `packet_item_records` WHERE q9radio NOT IN (0,1,2,3);
SELECT packet_ref, packet_number, item_ref, '11', q9radio, q9 FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;
SELECT packet_ref, packet_number, item_ref, '11', IF(q9radio=1, 'Y', IF(q9radio=2, 'N', IF(q9radio=3, 'NS', ''))), q9 FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;
REPLACE INTO packet_data (packet_ref, student_ref, item_ref, qnum, answer, answer_comment) SELECT packet_ref, packet_number, item_ref, '11', IF(q9radio=1, 'Y', IF(q9radio=2, 'N', IF(q9radio=3, 'NS', ''))), q9 FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;

SELECT * FROM `packet_item_records` WHERE q10radio NOT IN (0,1,2,3);
SELECT packet_ref, packet_number, item_ref, '12', q10radio, q10 FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;
SELECT packet_ref, packet_number, item_ref, '12', IF(q10radio=1, 'Y', IF(q10radio=2, 'N', IF(q10radio=3, 'NS', ''))), q10 FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;
REPLACE INTO packet_data (packet_ref, student_ref, item_ref, qnum, answer, answer_comment) SELECT packet_ref, packet_number, item_ref, '12', IF(q10radio=1, 'Y', IF(q10radio=2, 'N', IF(q10radio=3, 'NS', ''))), q10 FROM packet_item_records JOIN packet_items ON packet_item_ref = packet_items.id ORDER BY packet_ref, packet_number;