######################################################################################
# this file recreates all the views needed for the CLAI prototype as of 2004/07/31
# this file must be rerun every time data is changed in the data tables!


######################################################################################
# scaffold-skill

DROP TABLE IF EXISTS vwScaffoldKnowledgeComponent;
CREATE TABLE vwScaffoldKnowledgeComponent TYPE=InnoDB

SELECT DISTINCT
  tblScaffold.ID as scaffoldID, tblKnowledgeComponent.ID as knowledgeComponentID,
  tblScaffold.widgetName,
  tblKnowledgeComponent.name as knowledgeComponentName,
  tblKnowledgeComponent.transferModelID

FROM
  tblScaffold, tblScaffoldKnowledgeComponentMap, tblKnowledgeComponent

WHERE
      tblScaffoldKnowledgeComponentMap.scaffoldID=tblScaffold.ID
  AND tblScaffoldKnowledgeComponentMap.knowledgeComponentID=tblKnowledgeComponent.ID

ORDER BY
  tblScaffold.ID, tblKnowledgeComponent.transferModelID, tblKnowledgeComponent.ID;

ALTER TABLE vwScaffoldKnowledgeComponent
      ADD INDEX mapIndex (scaffoldID, knowledgeComponentID);



######################################################################################
# scaffold-standard

DROP TABLE IF EXISTS vwScaffoldStandard;
CREATE TABLE vwScaffoldStandard TYPE=InnoDB

SELECT DISTINCT
  tblScaffold.ID as scaffoldID, tblStandard.ID as standardID,
  tblScaffold.widgetName,
  tblStandard.name as standardName, tblStandard.reportingCategoryID

FROM
  tblScaffold, tblAssistment, tblAssistmentStandardMap, tblStandard

WHERE
      tblScaffold.assistmentID=tblAssistment.ID
  AND tblAssistmentStandardMap.assistmentID=tblAssistment.ID
  AND tblAssistmentStandardMap.standardID=tblStandard.ID

ORDER BY
  tblScaffold.ID, tblStandard.reportingCategoryID, tblStandard.ID;

ALTER TABLE vwScaffoldStandard
      ADD INDEX mapIndex (scaffoldID, standardID);



######################################################################################
# standard-skill

DROP TABLE IF EXISTS vwStandardKnowledgeComponent;
CREATE TABLE vwStandardKnowledgeComponent TYPE=InnoDB

SELECT DISTINCT
  vwScaffoldStandard.standardID, vwScaffoldKnowledgeComponent.knowledgeComponentID,
  vwScaffoldStandard.standardName, vwScaffoldStandard.reportingCategoryID,
  vwScaffoldKnowledgeComponent.knowledgeComponentName,
  vwScaffoldKnowledgeComponent.transferModelID

FROM
  vwScaffoldStandard, vwScaffoldKnowledgeComponent

WHERE
      vwScaffoldStandard.scaffoldID=vwScaffoldKnowledgeComponent.scaffoldID

ORDER BY
  vwScaffoldStandard.reportingCategoryID, vwScaffoldStandard.standardID,
  vwScaffoldKnowledgeComponent.transferModelID,
  vwScaffoldKnowledgeComponent.knowledgeComponentID;

ALTER TABLE vwStandardKnowledgeComponent
      ADD INDEX mapIndex (standardID, knowledgeComponentID);



######################################################################################
# student-scaffold performance on first attempts

DROP TABLE IF EXISTS vwStudentScaffold;
CREATE TABLE vwStudentScaffold TYPE=InnoDB

SELECT
  tblStudent.ID as studentID, tblScaffold.ID as scaffoldID,
  tblStudent.username, tblScaffold.widgetName,
  sum(IF(tblAction.actionTypeID=1 AND tblAction.resultTypeID=1, 1, 0)) as numRight,
  count(*) as numTries

FROM
  tblStudent, tblSession, tblQuestionStep, tblScaffold, tblAction

WHERE
      tblStudent.ID=tblSession.studentID
  AND tblSession.ID=tblQuestionStep.sessionID
  AND tblQuestionStep.scaffoldID=tblScaffold.ID
  AND tblQuestionStep.ID=tblAction.questionStepID
  AND
      tblAction.theOrder=1

GROUP BY
  tblStudent.ID, tblScaffold.ID, tblStudent.username, tblScaffold.widgetName

ORDER BY
  tblStudent.ID, tblScaffold.ID;

ALTER TABLE vwStudentScaffold
      ADD INDEX mapIndex (studentID, scaffoldID);



######################################################################################
# student-assistment performance on first attempts

DROP TABLE IF EXISTS vwStudentAssistment;
CREATE TABLE vwStudentAssistment TYPE=InnoDB

SELECT
  vwStudentScaffold.studentID, tblScaffold.assistmentID,
  vwStudentScaffold.username, tblAssistment.name as assistmentName,
  sum(numRight) as numRight, sum(numTries) as numTries,
  sum(IF(tblScaffold.theOrder=0, numRight, 0)) as firstScaffoldRight
FROM
  vwStudentScaffold, tblScaffold, tblAssistment
WHERE
      vwStudentScaffold.scaffoldID=tblScaffold.ID
  AND tblScaffold.assistmentID=tblAssistment.ID
GROUP BY
  vwStudentScaffold.studentID, tblScaffold.assistmentID
ORDER BY
  vwStudentScaffold.studentID, tblScaffold.assistmentID;

ALTER TABLE vwStudentAssistment
      ADD INDEX mapIndex (studentID, assistmentID);



######################################################################################
# student-skill performance on first attempts

DROP TABLE IF EXISTS vwStudentKnowledgeComponent;
CREATE TABLE vwStudentKnowledgeComponent TYPE=InnoDB

SELECT
  vwStudentScaffold.studentID, vwScaffoldKnowledgeComponent.knowledgeComponentID,
  vwStudentScaffold.username,
  vwScaffoldKnowledgeComponent.knowledgeComponentName,
  vwScaffoldKnowledgeComponent.transferModelID,
  sum(numRight) as numRight, sum(numTries) as numTries

FROM
  vwStudentScaffold, vwScaffoldKnowledgeComponent

WHERE
      vwStudentScaffold.scaffoldID=vwScaffoldKnowledgeComponent.scaffoldID

GROUP BY
  vwStudentScaffold.studentID, vwScaffoldKnowledgeComponent.knowledgeComponentID,
  vwStudentScaffold.username,
  vwScaffoldKnowledgeComponent.knowledgeComponentName,
  vwScaffoldKnowledgeComponent.transferModelID

ORDER BY
  vwStudentScaffold.studentID, vwScaffoldKnowledgeComponent.transferModelID,
  vwScaffoldKnowledgeComponent.knowledgeComponentID;

ALTER TABLE vwStudentKnowledgeComponent
      ADD INDEX mapIndex (studentID, knowledgeComponentID);



######################################################################################
# student-standard performance on first attempts

DROP TABLE IF EXISTS vwStudentStandard;
CREATE TABLE vwStudentStandard TYPE=InnoDB

SELECT
  vwStudentScaffold.studentID, vwScaffoldStandard.standardID,
  vwStudentScaffold.username,
  vwScaffoldStandard.standardName,
  vwScaffoldStandard.reportingCategoryID,
  sum(numRight) as numRight, sum(numTries) as numTries

FROM
  vwStudentScaffold, vwScaffoldStandard

WHERE
      vwStudentScaffold.scaffoldID=vwScaffoldStandard.scaffoldID

GROUP BY
  vwStudentScaffold.studentID, vwScaffoldStandard.standardID,
  vwStudentScaffold.username,
  vwScaffoldStandard.standardName,
  vwScaffoldStandard.reportingCategoryID

ORDER BY
  vwStudentScaffold.studentID, vwScaffoldStandard.reportingCategoryID,
  vwScaffoldStandard.standardID;

ALTER TABLE vwStudentStandard
      ADD INDEX mapIndex (studentID, standardID);



######################################################################################
# allstudents-standard performance on first attempts

DROP TABLE IF EXISTS vwAllStudentsStandard;
CREATE TABLE vwAllStudentsStandard TYPE=InnoDB

SELECT
  tblStandard.ID as standardID,
  tblStandard.name as standardName, tblStandard.reportingCategoryID,
  avg(numRight/numTries) as performance

FROM
  tblStandard LEFT JOIN vwStudentStandard
              ON vwStudentStandard.standardID=tblStandard.ID

GROUP BY
  tblStandard.ID,
  tblStandard.name, tblStandard.reportingCategoryID

ORDER BY
  tblStandard.reportingCategoryID, tblStandard.ID;

ALTER TABLE vwAllStudentsStandard
      ADD INDEX mapIndex (standardID);



######################################################################################
# allstudents-skills performance on first attempts

DROP TABLE IF EXISTS vwAllStudentsKnowledgeComponent;
CREATE TABLE vwAllStudentsKnowledgeComponent TYPE=InnoDB

SELECT
  tblKnowledgeComponent.ID as knowledgeComponentID,
  tblKnowledgeComponent.name as knowledgeComponentName,
  tblKnowledgeComponent.transferModelID,
  avg(numRight/numTries) as performance

FROM
  tblKnowledgeComponent LEFT JOIN vwStudentKnowledgeComponent
     ON vwStudentKnowledgeComponent.knowledgeComponentID=tblKnowledgeComponent.ID

GROUP BY
  tblKnowledgeComponent.ID,
  tblKnowledgeComponent.name, tblKnowledgeComponent.transferModelID

ORDER BY
  tblKnowledgeComponent.transferModelID, tblKnowledgeComponent.ID;

ALTER TABLE vwAllStudentsKnowledgeComponent
      ADD INDEX mapIndex (knowledgeComponentID);



######################################################################################
# allstudents-scaffold performance on first attempts

DROP TABLE IF EXISTS vwAllStudentsScaffold;
CREATE TABLE vwAllStudentsScaffold TYPE=InnoDB

SELECT
  tblScaffold.ID as scaffoldID, tblScaffold.assistmentID,
  tblScaffold.image as scaffoldImage,
  tblScaffold.questionText, tblScaffold.answer as correctAnswer,
  sum(numRight) as numright, sum(numTries) as numTries

FROM
  tblScaffold LEFT JOIN vwStudentScaffold
              ON vwStudentScaffold.scaffoldID=tblScaffold.ID

GROUP BY
  tblScaffold.ID, tblScaffold.assistmentID,
  tblScaffold.image, tblScaffold.questionText, tblScaffold.answer

ORDER BY
  tblScaffold.assistmentID, tblScaffold.ID;

ALTER TABLE vwAllStudentsScaffold
      ADD INDEX mapIndex (assistmentID, scaffoldID);



######################################################################################
# allstudents-scaffold performance on first attempts with skills

DROP TABLE IF EXISTS vwAllStudentsScaffoldSkills;
CREATE TABLE vwAllStudentsScaffoldSkills TYPE=InnoDB

SELECT
  vwAllStudentsScaffold.scaffoldID, vwAllStudentsScaffold.assistmentID,
  vwAllStudentsScaffold.scaffoldImage,
  vwAllStudentsScaffold.questionText, vwAllStudentsScaffold.correctAnswer,
  tblKnowledgeComponent.ID as knowledgeComponentID,
  tblKnowledgeComponent.name as knowledgeComponentName,
  tblKnowledgeComponent.description as knowledgeComponentDescription,
  vwAllStudentsScaffold.numright, vwAllStudentsScaffold.numTries

FROM
  vwAllStudentsScaffold,
  vwScaffoldKnowledgeComponent, tblKnowledgeComponent

WHERE
      vwAllStudentsScaffold.scaffoldID=vwScaffoldKnowledgeComponent.scaffoldID
  AND vwScaffoldKnowledgeComponent.knowledgeComponentID=tblKnowledgeComponent.ID

GROUP BY
  vwAllStudentsScaffold.scaffoldID, vwAllStudentsScaffold.assistmentID,
  vwAllStudentsScaffold.scaffoldImage,
  vwAllStudentsScaffold.questionText, vwAllStudentsScaffold.correctAnswer,
  vwAllStudentsScaffold.numright, vwAllStudentsScaffold.numTries

ORDER BY
  vwAllStudentsScaffold.assistmentID, vwAllStudentsScaffold.scaffoldID;

ALTER TABLE vwAllStudentsScaffoldSkills
      ADD INDEX mapIndex (assistmentID, scaffoldID);



######################################################################################
# allstudents-assistment-scaffold performance on first attempts with skills

DROP TABLE IF EXISTS vwAllStudentsAssistmentScaffoldSkills;
CREATE TABLE vwAllStudentsAssistmentScaffoldSkills TYPE=InnoDB

SELECT
  tblAssistment.ID as assistmentID, tblAssistment.name as assistmentName,
  tblSource.image as sourceImage, tblAssistment.image as assistmentImage,
  "MCAS" as sourceTest, tblSource.year as sourceYear, tblSource.name as sourceItem,
  tblScaffold.theOrder as scaffoldOrder,
  vwAllStudentsScaffoldSkills.scaffoldID,
  vwAllStudentsScaffoldSkills.scaffoldImage,
  vwAllStudentsScaffoldSkills.questionText,
  vwAllStudentsScaffoldSkills.correctAnswer,
  vwAllStudentsScaffoldSkills.knowledgeComponentID,
  vwAllStudentsScaffoldSkills.knowledgeComponentName,
  vwAllStudentsScaffoldSkills.knowledgeComponentDescription,
  vwAllStudentsScaffoldSkills.numright, vwAllStudentsScaffoldSkills.numTries

FROM
  tblSource, tblAssistment, tblScaffold, vwAllStudentsScaffoldSkills

WHERE
      tblSource.ID=tblAssistment.sourceID
  AND tblAssistment.ID=tblScaffold.assistmentID
  AND tblScaffold.ID=vwAllStudentsScaffoldSkills.scaffoldID

ORDER BY
  tblAssistment.ID, tblScaffold.theOrder;

ALTER TABLE vwAllStudentsAssistmentScaffoldSkills
      ADD INDEX mapIndex (assistmentID, scaffoldID);



######################################################################################
# scaffold-wrong

DROP TABLE IF EXISTS vwScaffoldWrong;
CREATE TABLE vwScaffoldWrong TYPE=InnoDB

SELECT
  tblScaffold.ID as scaffoldID,
  tblAction.actionText as wrongAnswer, count(*) as numWrong

FROM
  tblScaffold, tblQuestionStep, tblAction

WHERE
      tblScaffold.ID=tblQuestionStep.scaffoldID
  AND tblQuestionStep.ID=tblAction.questionStepID
  AND
      tblAction.actionTypeID=1 AND tblAction.resultTypeID!=1
  AND tblAction.actionText!="" AND tblAction.actionText IS NOT NULL

GROUP BY
  tblScaffold.ID, tblAction.actionText

ORDER BY
  tblScaffold.ID, numWrong DESC, tblAction.actionText;

ALTER TABLE vwScaffoldWrong
      ADD INDEX mapIndex (scaffoldID);



######################################################################################
# allstudents-assistment-scaffold performance on first attempts with skills & wrongs

DROP TABLE IF EXISTS vwAllStudentsSkillsAndWrongs;
CREATE TABLE vwAllStudentsSkillsAndWrongs TYPE=InnoDB

SELECT
  vwAllStudentsAssistmentScaffoldSkills.assistmentID,
  vwAllStudentsAssistmentScaffoldSkills.assistmentName,
  vwAllStudentsAssistmentScaffoldSkills.assistmentImage,
  vwAllStudentsAssistmentScaffoldSkills.sourceImage,
  vwAllStudentsAssistmentScaffoldSkills.sourceTest,
  vwAllStudentsAssistmentScaffoldSkills.sourceYear,
  vwAllStudentsAssistmentScaffoldSkills.sourceItem,
  vwAllStudentsAssistmentScaffoldSkills.scaffoldOrder,
  vwAllStudentsAssistmentScaffoldSkills.scaffoldID,
  vwAllStudentsAssistmentScaffoldSkills.scaffoldImage,
  vwAllStudentsAssistmentScaffoldSkills.questionText,
  vwAllStudentsAssistmentScaffoldSkills.correctAnswer,
  vwAllStudentsAssistmentScaffoldSkills.knowledgeComponentID,
  vwAllStudentsAssistmentScaffoldSkills.knowledgeComponentName,
  vwAllStudentsAssistmentScaffoldSkills.knowledgeComponentDescription,
  vwAllStudentsAssistmentScaffoldSkills.numRight,
  vwAllStudentsAssistmentScaffoldSkills.numTries,
  vwScaffoldWrong.wrongAnswer, vwScaffoldWrong.numWrong

FROM
  vwAllStudentsAssistmentScaffoldSkills LEFT JOIN vwScaffoldWrong
  ON vwAllStudentsAssistmentScaffoldSkills.scaffoldID=vwScaffoldWrong.scaffoldID

ORDER BY
  vwAllStudentsAssistmentScaffoldSkills.assistmentID,
  vwAllStudentsAssistmentScaffoldSkills.scaffoldOrder,
  vwScaffoldWrong.numWrong DESC;

ALTER TABLE vwAllStudentsSkillsAndWrongs
      ADD INDEX mapIndex (assistmentID, scaffoldID);



######################################################################################
# scaffold-hint per student

DROP TABLE IF EXISTS vwScaffoldHintPerStudent;
CREATE TABLE vwScaffoldHintPerStudent TYPE=InnoDB

SELECT
  tblScaffold.ID as scaffoldID,
  tblAction.resultText as hintText,
  count(*) as numHintViews, min(tblAction.actionTime) as firstRequestTime

FROM
  tblScaffold, tblQuestionStep, tblAction

WHERE
      tblScaffold.ID=tblQuestionStep.scaffoldID
  AND tblQuestionStep.ID=tblAction.questionStepID
  AND
      tblAction.actionTypeID=2
  AND tblAction.resultText!="" AND tblAction.resultText IS NOT NULL

GROUP BY
  tblScaffold.ID, tblAction.resultText, tblQuestionStep.sessionID

ORDER BY
  tblScaffold.ID, firstRequestTime, numHintViews DESC, tblAction.resultText;

ALTER TABLE vwScaffoldHintPerStudent
      ADD INDEX mapIndex (scaffoldID);



######################################################################################
# scaffold-hint (per scaffold, counting number of students)

DROP TABLE IF EXISTS vwScaffoldHint;
CREATE TABLE vwScaffoldHint TYPE=InnoDB

SELECT
  vwScaffoldHintPerStudent.scaffoldID,
  vwScaffoldHintPerStudent.hintText, count(*) as numHintViews,
  min(vwScaffoldHintPerStudent.firstRequestTime) as firstRequestTime

FROM
  vwScaffoldHintPerStudent

GROUP BY
  vwScaffoldHintPerStudent.scaffoldID, vwScaffoldHintPerStudent.hintText

ORDER BY
  vwScaffoldHintPerStudent.scaffoldID,
  firstRequestTime, numHintViews DESC, vwScaffoldHintPerStudent.hintText;

ALTER TABLE vwScaffoldHint
      ADD INDEX mapIndex (scaffoldID);



######################################################################################
# scaffold-right (even if they asked for a hint at first) - with dupes per scaffold

DROP TABLE IF EXISTS vwScaffoldRightDuped;
CREATE TABLE vwScaffoldRightDuped TYPE=InnoDB

SELECT
  tblScaffold.ID as scaffoldID,
  tblAction.actionText as rightAnswer, count(*) as numRight

FROM
  tblScaffold, tblQuestionStep, tblAction

WHERE
      tblScaffold.ID=tblQuestionStep.scaffoldID
  AND tblQuestionStep.ID=tblAction.questionStepID
  AND
      tblAction.actionTypeID=1 AND tblAction.resultTypeID=1
  AND tblAction.actionText!="" AND tblAction.actionText IS NOT NULL

GROUP BY
  tblScaffold.ID, tblAction.actionText

ORDER BY
  tblScaffold.ID, numRight DESC, tblAction.actionText;

ALTER TABLE vwScaffoldRightDuped
      ADD INDEX mapIndex (scaffoldID);



######################################################################################
# scaffold-right (even if they asked for a hint at first) - the most common right

DROP TABLE IF EXISTS vwScaffoldRightMostCommon;
CREATE TABLE vwScaffoldRightMostCommon TYPE=InnoDB

SELECT
  vwScaffoldRightDuped.scaffoldID,
  sum(vwScaffoldRightDuped.numRight) as numRightTotal,
  max(vwScaffoldRightDuped.numRight) as mostCommonNumRight

FROM
  vwScaffoldRightDuped

GROUP BY
  vwScaffoldRightDuped.scaffoldID

ORDER BY
  vwScaffoldRightDuped.scaffoldID;

ALTER TABLE vwScaffoldRightMostCommon
      ADD INDEX mapIndex (scaffoldID);



######################################################################################
# scaffold-right (even if they asked for a hint at first) - unique per scaffold

DROP TABLE IF EXISTS vwScaffoldRight;
CREATE TABLE vwScaffoldRight TYPE=InnoDB

SELECT
  vwScaffoldRightDuped.scaffoldID,
  vwScaffoldRightDuped.rightAnswer,
  vwScaffoldRightMostCommon.numRightTotal as numRight

FROM
  vwScaffoldRightDuped, vwScaffoldRightMostCommon

WHERE
      vwScaffoldRightDuped.scaffoldID=vwScaffoldRightMostCommon.scaffoldID
  AND
      vwScaffoldRightDuped.numRight=vwScaffoldRightMostCommon.mostCommonNumRight

GROUP BY
  vwScaffoldRightDuped.scaffoldID

ORDER BY
  vwScaffoldRightDuped.scaffoldID, numRight DESC, vwScaffoldRightDuped.rightAnswer;

ALTER TABLE vwScaffoldRight
      ADD INDEX mapIndex (scaffoldID);







######################################################################################
# stuff for the mapping from standards to assistments to skills - only with perf. !!!

######################################################################################
#