// First, clear any existing data (optional)
MATCH (n) DETACH DELETE n;

// Create constraints and indexes (REQUIRED - run first!)
CREATE CONSTRAINT student_id IF NOT EXISTS FOR (s:Student) REQUIRE s.id IS UNIQUE;
CREATE CONSTRAINT course_id IF NOT EXISTS FOR (c:Course) REQUIRE c.id IS UNIQUE;
CREATE CONSTRAINT faculty_id IF NOT EXISTS FOR (f:Faculty) REQUIRE f.id IS UNIQUE;
CREATE CONSTRAINT degree_id IF NOT EXISTS FOR (d:Degree) REQUIRE d.id IS UNIQUE;
CREATE CONSTRAINT requirement_id IF NOT EXISTS FOR (r:RequirementGroup) REQUIRE r.id IS UNIQUE;
CREATE CONSTRAINT term_id IF NOT EXISTS FOR (t:Term) REQUIRE t.id IS UNIQUE;
CREATE CONSTRAINT textbook_id IF NOT EXISTS FOR (tb:Textbook) REQUIRE tb.id IS UNIQUE;

// Create indexes for better query performance
CREATE INDEX student_learning_style IF NOT EXISTS FOR (s:Student) ON (s.learningStyle);
CREATE INDEX course_department IF NOT EXISTS FOR (c:Course) ON (c.department);
CREATE INDEX course_level IF NOT EXISTS FOR (c:Course) ON (c.level);
CREATE INDEX term_type IF NOT EXISTS FOR (t:Term) ON (t.type);

// Check node counts
MATCH (n) RETURN labels(n)[0] as Label, count(n) as Count;

// Should see approximately:
// Student: 500
// Course: 100
// Faculty: 30
// Degree: 4
// Term: 12
// Textbook: 80-100

// Find what successful similar students do differently
// First get a sample student who has course completions
MATCH (target:Student)-[:COMPLETED]->(:Course)
WITH target.id AS targetStudentId LIMIT 1  // Get first student with courses

// Get target student's GPA equivalent
MATCH (target:Student {id: targetStudentId})-[tc:COMPLETED]->(c:Course)
WITH target, targetStudentId, 
     AVG(CASE tc.grade
         WHEN 'A' THEN 4.0 WHEN 'A-' THEN 3.7
         WHEN 'B+' THEN 3.3 WHEN 'B' THEN 3.0 WHEN 'B-' THEN 2.7
         WHEN 'C+' THEN 2.3 WHEN 'C' THEN 2.0 WHEN 'C-' THEN 1.7
         WHEN 'D+' THEN 1.3 WHEN 'D' THEN 1.0
         ELSE 0 END) AS targetGPA

// Find similar students who are performing better
MATCH (target)-[sim:SIMILAR_LEARNING_STYLE|SIMILAR_PERFORMANCE]->(peer:Student)
WHERE sim.similarity > 0.7
MATCH (peer)-[pc:COMPLETED]->(course:Course)
WITH target, targetGPA, peer, sim,
     AVG(CASE pc.grade
         WHEN 'A' THEN 4.0 WHEN 'A-' THEN 3.7
         WHEN 'B+' THEN 3.3 WHEN 'B' THEN 3.0 WHEN 'B-' THEN 2.7
         WHEN 'C+' THEN 2.3 WHEN 'C' THEN 2.0 WHEN 'C-' THEN 1.7
         WHEN 'D+' THEN 1.3 WHEN 'D' THEN 1.0
         ELSE 0 END) AS peerGPA
WHERE peerGPA > targetGPA + 0.3  // At least 0.3 GPA points higher

// Analyze behavioral differences in multiple dimensions
MATCH (peer)-[peerCompleted:COMPLETED]->(course:Course)

// 1. Course Load Analysis
WITH target, peer, 
     COUNT(DISTINCT peerCompleted) AS peerCourseCount,
     AVG(peerCompleted.difficulty) AS peerPerceivedDifficulty,
     COLLECT(DISTINCT course.level) AS peerCourseLevels,
     peerGPA

// 2. Textbook Engagement Analysis
OPTIONAL MATCH (peer)-[peerView:VIEWED_PAGE]->(textbook:Textbook)
WITH target, peer, peerGPA, peerCourseCount, peerPerceivedDifficulty, peerCourseLevels,
     COUNT(peerView) AS peerTextbookViews,
     COUNT(DISTINCT textbook) AS peerTextbooksUsed,
     AVG(peerView.duration) AS peerAvgReadingDuration

// 3. Study Pattern Analysis
OPTIONAL MATCH (peer)-[peerInt:INTERACTED_WITH]->(textbook2:Textbook)
WHERE peerInt.interactionType IN ['highlight', 'note']
WITH target, peer, peerGPA, peerCourseCount, peerPerceivedDifficulty, 
     peerCourseLevels, peerTextbookViews, peerTextbooksUsed, peerAvgReadingDuration,
     COUNT(peerInt) AS peerActiveStudyInteractions

// Get target student's behaviors for comparison
MATCH (target)-[targetCompleted:COMPLETED]->(tCourse:Course)
OPTIONAL MATCH (target)-[targetView:VIEWED_PAGE]->(tTextbook:Textbook)
OPTIONAL MATCH (target)-[targetInt:INTERACTED_WITH]->(tTextbook2:Textbook)
WHERE targetInt.interactionType IN ['highlight', 'note']

WITH peer, peerGPA,
     // Peer metrics
     peerCourseCount, peerPerceivedDifficulty, peerTextbookViews, 
     peerTextbooksUsed, peerAvgReadingDuration, peerActiveStudyInteractions,
     // Target metrics
     COUNT(DISTINCT targetCompleted) AS targetCourseCount,
     AVG(targetCompleted.difficulty) AS targetPerceivedDifficulty,
     COUNT(targetView) AS targetTextbookViews,
     COUNT(DISTINCT tTextbook) AS targetTextbooksUsed,
     AVG(targetView.duration) AS targetAvgReadingDuration,
     COUNT(targetInt) AS targetActiveStudyInteractions

// Calculate behavioral differences and generate recommendations
RETURN 
    // Performance Gap
    ROUND(AVG(peerGPA - targetGPA), 2) AS gpaGap,
    COUNT(DISTINCT peer) AS similarSuccessfulPeers,
    
    // Course Load Insights
    ROUND(AVG(peerCourseCount - targetCourseCount)) AS coursesPerTermDifference,
    CASE 
        WHEN AVG(peerCourseCount) > AVG(targetCourseCount) 
        THEN 'Take ' + toString(ROUND(AVG(peerCourseCount - targetCourseCount))) + ' more courses per term'
        WHEN AVG(peerCourseCount) < AVG(targetCourseCount)
        THEN 'Reduce course load by ' + toString(ROUND(AVG(targetCourseCount - peerCourseCount))) + ' courses'
        ELSE 'Course load is optimal'
    END AS courseLoadRecommendation,
    
    // Textbook Engagement Insights
    ROUND(AVG(peerTextbookViews - targetTextbookViews)) AS textbookEngagementGap,
    CASE 
        WHEN AVG(peerTextbookViews) > AVG(targetTextbookViews) * 1.5
        THEN 'Increase textbook reading by ' + toString(ROUND((AVG(peerTextbookViews) / AVG(targetTextbookViews) - 1) * 100)) + '%'
        ELSE 'Textbook engagement is sufficient'
    END AS textbookRecommendation,
    
    // Study Behavior Insights
    ROUND(AVG(peerAvgReadingDuration - targetAvgReadingDuration), 1) AS readingDurationGap,
    CASE 
        WHEN AVG(peerAvgReadingDuration) > AVG(targetAvgReadingDuration)
        THEN 'Spend ' + toString(ROUND(AVG(peerAvgReadingDuration - targetAvgReadingDuration))) + ' more minutes per reading session'
        ELSE 'Reading session duration is good'
    END AS readingDurationRecommendation,
    
    // Active Learning Insights
    ROUND(AVG(peerActiveStudyInteractions - targetActiveStudyInteractions)) AS activeStudyGap,
    CASE 
        WHEN AVG(peerActiveStudyInteractions) > AVG(targetActiveStudyInteractions) * 1.5
        THEN 'Increase active study (highlighting/notes) by ' + toString(ROUND((AVG(peerActiveStudyInteractions) / NULLIF(AVG(targetActiveStudyInteractions), 0) - 1) * 100)) + '%'
        ELSE 'Active study engagement is adequate'
    END AS activeStudyRecommendation,
    
    // Difficulty Perception
    ROUND(AVG(targetPerceivedDifficulty - peerPerceivedDifficulty), 2) AS difficultyPerceptionGap,
    CASE
        WHEN AVG(targetPerceivedDifficulty) > AVG(peerPerceivedDifficulty) + 0.5
        THEN 'Similar students find courses easier - consider seeking study groups or tutoring'
        ELSE 'Difficulty perception aligns with successful peers'
    END AS difficultyInsight

// Find peers who aced a course you're struggling with
// First find a student with a low grade in any course
MATCH (you:Student)-[yourGrade:COMPLETED]->(course:Course)
WHERE yourGrade.grade IN ['C', 'C-', 'D', 'F']
WITH you, course, yourGrade LIMIT 1  // Get first example
MATCH (you)-[sim:SIMILAR_LEARNING_STYLE]->(peer:Student)-[peerGrade:COMPLETED]->(course)
WHERE sim.similarity > 0.8 
  AND peerGrade.grade IN ['A', 'A-', 'B+']
RETURN peer.id, peer.name, peerGrade.grade, sim.similarity,
       peerGrade.studyHours - yourGrade.studyHours AS extraStudyHours
ORDER BY sim.similarity DESC

// What course combinations work well for students like you?
// First get a student with similar learning style connections
MATCH (you:Student)-[:SIMILAR_LEARNING_STYLE]->(peer:Student)
WITH you LIMIT 1  // Get first student with similarity connections
MATCH (you)-[sim:SIMILAR_LEARNING_STYLE]->(peer:Student)
WHERE sim.similarity > 0.75
MATCH (peer)-[c1:COMPLETED]->(course1:Course), 
      (peer)-[c2:COMPLETED]->(course2:Course)
WHERE c1.term = c2.term  // Taken in same term
  AND c1.grade IN ['A', 'A-', 'B+']
  AND c2.grade IN ['A', 'A-', 'B+']
WITH course1, course2, COUNT(DISTINCT peer) AS successfulPeers
WHERE successfulPeers >= 3
RETURN course1.name, course2.name, successfulPeers
ORDER BY successfulPeers DESC

// Find optimal next courses based on learning style and prerequisites
// First, get a sample student and current term
MATCH (student:Student)-[:PURSUING]->(degree:Degree)
WITH student LIMIT 1  // Get first student, or replace with specific ID
MATCH (term:Term)
WHERE term.startDate > date()  // Get future term
WITH student, term, degree ORDER BY term.startDate LIMIT 1
MATCH (course:Course)-[:OFFERED_IN]->(term)
MATCH (course)-[:FULFILLS]->(:RequirementGroup)-[:PART_OF]->(degree)

// Ensure prerequisites are met
WHERE NOT EXISTS {
  MATCH (prereq:Course)-[:PREREQUISITE_FOR]->(course)
  WHERE NOT (student)-[:COMPLETED]->(prereq)
}

// Student hasn't already completed the course
AND NOT (student)-[:COMPLETED]->(course)

// Find similar students and their experiences with these courses
OPTIONAL MATCH (student)-[sim:SIMILAR_LEARNING_STYLE]->(similar:Student)-[comp:COMPLETED]->(course)
WHERE sim.similarity > 0.7

WITH student, course, term, degree,
     CASE WHEN COUNT(comp) > 0 
          THEN AVG(comp.difficulty) 
          ELSE course.avgDifficulty END AS predictedDifficulty,
     COUNT(comp) AS similarStudentsCount

// Check how many future courses this would unlock
OPTIONAL MATCH (course)-[:PREREQUISITE_FOR]->(futureCourse)
WHERE (futureCourse)-[:FULFILLS]->(:RequirementGroup)-[:PART_OF]->(degree)

RETURN course.id AS courseId,
       course.name AS courseName,
       course.credits AS credits,
       predictedDifficulty,
       COUNT(futureCourse) AS unlockedCourses,
       similarStudentsCount,
       course.instructionModes AS availableModes
ORDER BY predictedDifficulty ASC, unlockedCourses DESC, credits DESC
LIMIT 5

// Compare reading patterns between high and low performing students
MATCH (student:Student)-[comp:COMPLETED]->(course:Course)
MATCH (student)-[view:VIEWED_PAGE]->(textbook:Textbook)<-[:REQUIRES]-(course)
WITH student, course, textbook, comp,
     COUNT(view) AS totalPages,
     AVG(view.duration) AS avgTimePerPage,
     COLLECT(view.timestamp) AS viewTimes
WITH student, course, textbook,
     totalPages,
     avgTimePerPage,
     // Calculate reading pattern metrics
     CASE WHEN size(viewTimes) > 1
          THEN reduce(s = 0, i in range(0, size(viewTimes)-2) |
               s + duration.between(
                    datetime(replace(viewTimes[i], ' ', 'T')),
                    datetime(replace(viewTimes[i+1], ' ', 'T'))
               ).days)
          ELSE 0 END AS totalGaps,
     size(viewTimes) AS totalSessions,
     comp.grade AS grade
WHERE comp.grade IN ['A', 'A-', 'B+', 'C', 'C-', 'D']
RETURN student.id,
       course.name,
       grade,
       totalPages,
       avgTimePerPage,
       totalGaps / totalSessions AS avgDaysBetweenSessions,
       CASE 
         WHEN grade IN ['A', 'A-', 'B+'] THEN 'High Performing'
         ELSE 'Low Performing'
       END AS performanceCategory
ORDER BY grade ASC

// Find students who might be at risk based on textbook interaction patterns
MATCH (student:Student)-[comp:COMPLETED]->(course:Course)
MATCH (course)-[:REQUIRES]->(textbook:Textbook)
OPTIONAL MATCH (student)-[view:VIEWED_PAGE]->(textbook)
WITH student, course, textbook,
     COUNT(view) AS totalViews,
     COLLECT(view.timestamp) AS viewTimes
WITH student, course,
     AVG(totalViews) AS avgViewsPerTextbook,
     CASE WHEN size(viewTimes) > 0
          THEN reduce(s = 0, t in viewTimes |
               s + CASE WHEN size(viewTimes) > 0 
                    THEN 1 ELSE 0 END) / CASE WHEN size(viewTimes) > 0 THEN size(viewTimes) ELSE 1 END
          ELSE 0 END AS examWeekRatio
WHERE (examWeekRatio > 0.7 OR avgViewsPerTextbook < 10) AND avgViewsPerTextbook > 0
RETURN student.id,
       student.name,
       COUNT(DISTINCT course) AS coursesAtRisk,
       ROUND(AVG(avgViewsPerTextbook)) AS avgTextbookViews,
       ROUND(AVG(examWeekRatio) * 100) AS percentageLastMinuteReading
ORDER BY coursesAtRisk DESC, avgTextbookViews ASC

// Analyze textbook effectiveness by comparing student interactions and performance
MATCH (course:Course)-[:REQUIRES]->(textbook:Textbook)
MATCH (student:Student)-[comp:COMPLETED]->(course)
OPTIONAL MATCH (student)-[view:VIEWED_PAGE]->(textbook)
OPTIONAL MATCH (student)-[interact:INTERACTED_WITH]->(textbook)
WITH course, textbook,
     COUNT(DISTINCT student) AS totalStudents,
     AVG(CASE WHEN comp.grade IN ['A', 'A-', 'B+'] THEN 1 ELSE 0 END) AS successRate,
     AVG(CASE WHEN view IS NOT NULL THEN 1 ELSE 0 END) AS readingRate,
     AVG(CASE WHEN interact.interactionType = 'highlight' THEN 1 ELSE 0 END) AS highlightRate,
     AVG(CASE WHEN interact.interactionType = 'note' THEN 1 ELSE 0 END) AS noteRate
RETURN course.name,
       textbook.name,
       totalStudents,
       ROUND(successRate * 100) AS successPercentage,
       ROUND(readingRate * 100) AS readingPercentage,
       ROUND(highlightRate * 100) AS highlightPercentage,
       ROUND(noteRate * 100) AS notePercentage
ORDER BY successRate DESC

// Find students with similar reading patterns for study group recommendations
// First get a student with textbook views
MATCH (student:Student)-[view1:VIEWED_PAGE]->(textbook:Textbook)
WITH student LIMIT 1  // Get first student with textbook views
MATCH (student)-[view1:VIEWED_PAGE]->(textbook:Textbook)
MATCH (other:Student)-[view2:VIEWED_PAGE]->(textbook)
WHERE other.id <> student.id
WITH student, other, textbook,
     COLLECT(view1.timestamp) AS student1Times,
     COLLECT(view2.timestamp) AS student2Times,
     COUNT(DISTINCT view1) AS student1Views,
     COUNT(DISTINCT view2) AS student2Views
WITH student, other, textbook,
     AVG(ABS(student1Views - student2Views)) AS viewDifference,
     AVG(CASE 
         WHEN size(student1Times) > 0 AND size(student2Times) > 0
         THEN duration.between(
              datetime(replace(student1Times[0], ' ', 'T')),
              datetime(replace(student2Times[0], ' ', 'T'))
         ).hours
         ELSE 24 END) AS timingDifference
WHERE viewDifference < 10 AND timingDifference < 12
WITH other.id AS otherId,
     other.learningStyle AS learningStyle,
     viewDifference,
     timingDifference,
     COLLECT(DISTINCT textbook.name) AS commonTextbooks
RETURN otherId,
       learningStyle,
       ROUND(viewDifference) AS avgViewDifference,
       ROUND(timingDifference) AS avgTimingDifferenceHours,
       commonTextbooks
ORDER BY viewDifference ASC, timingDifference ASC
LIMIT 5

// Find textbook consumption patterns of similar but higher-performing students
// Get a sample student with both course completions and textbook interactions
MATCH (target:Student)-[:COMPLETED]->(:Course)
MATCH (target)-[:VIEWED_PAGE]->(:Textbook)
WITH target LIMIT 1  // Use parameter if provided: WHERE target.id = COALESCE($studentId, target.id)
MATCH (target)-[comp:COMPLETED]->(course:Course)
WITH target, course, comp.grade AS grade

// Find similar students based on multiple criteria
MATCH (target)-[styleSim:SIMILAR_LEARNING_STYLE]->(similar:Student)
WHERE styleSim.similarity > 0.7  // High learning style similarity

// Ensure they're pursuing the same degree
MATCH (target)-[:PURSUING]->(degree:Degree)
MATCH (similar)-[:PURSUING]->(degree)

// Find students who performed better in the same courses
MATCH (similar)-[simComp:COMPLETED]->(course)
WHERE simComp.grade > grade  // Better grade in same course

// Get their textbook interactions for this course
MATCH (similar)-[view:VIEWED_PAGE]->(textbook:Textbook)
WHERE view.courseId = course.id

// Calculate reading patterns
WITH target, similar, course, textbook, view,
     duration.between(
       datetime(replace(view.timestamp, ' ', 'T')),
       datetime(replace(view.timestamp, ' ', 'T')) + duration({minutes: view.duration})
     ) AS readingSession

// Group by student and course to analyze patterns
WITH target, similar, course,
     COUNT(DISTINCT view) AS totalPageViews,
     COUNT(DISTINCT date(datetime(replace(view.timestamp, ' ', 'T')))) AS uniqueReadingDays,
     AVG(view.duration) AS avgReadingDuration,
     MIN(view.timestamp) AS firstRead,
     MAX(view.timestamp) AS lastRead,
     duration.between(
       datetime(replace(MIN(view.timestamp), ' ', 'T')),
       datetime(replace(MAX(view.timestamp), ' ', 'T'))
     ) AS readingSpan

// Calculate reading consistency and intensity
WITH target, similar, course,
     totalPageViews,
     uniqueReadingDays,
     avgReadingDuration,
     readingSpan,
     CASE 
       WHEN readingSpan.days > 0 
       THEN toFloat(uniqueReadingDays) / readingSpan.days 
       ELSE 0 
     END AS readingConsistency,
     CASE 
       WHEN readingSpan.days > 0 
       THEN toFloat(totalPageViews) / readingSpan.days 
       ELSE 0 
     END AS readingIntensity

// Get target student's patterns for comparison
MATCH (target)-[targetView:VIEWED_PAGE]->(targetTextbook:Textbook)
WHERE targetView.courseId = course.id
WITH target, similar, course,
     totalPageViews,
     uniqueReadingDays,
     avgReadingDuration,
     readingConsistency,
     readingIntensity,
     COUNT(DISTINCT targetView) AS targetPageViews,
     COUNT(DISTINCT date(datetime(replace(targetView.timestamp, ' ', 'T')))) AS targetReadingDays,
     AVG(targetView.duration) AS targetAvgDuration

// Calculate differences in reading patterns
WITH target, similar, course,
     totalPageViews - targetPageViews AS pageViewDiff,
     uniqueReadingDays - targetReadingDays AS readingDaysDiff,
     avgReadingDuration - targetAvgDuration AS durationDiff,
     readingConsistency,
     readingIntensity

// Aggregate insights across all similar students
WITH target, course,
     AVG(pageViewDiff) AS avgPageViewDiff,
     AVG(readingDaysDiff) AS avgReadingDaysDiff,
     AVG(durationDiff) AS avgDurationDiff,
     AVG(readingConsistency) AS avgReadingConsistency,
     AVG(readingIntensity) AS avgReadingIntensity,
     COUNT(DISTINCT similar) AS similarStudentCount

// Format recommendations
RETURN course.id AS courseId,
       course.name AS courseName,
       similarStudentCount AS numberOfSimilarStudents,
       CASE 
         WHEN avgPageViewDiff > 0 THEN 'Read more pages'
         ELSE 'Read fewer pages but more thoroughly'
       END AS pageViewRecommendation,
       CASE 
         WHEN avgReadingDaysDiff > 0 THEN 'Spread reading across more days'
         ELSE 'Consolidate reading into fewer, longer sessions'
       END AS readingScheduleRecommendation,
       CASE 
         WHEN avgDurationDiff > 0 THEN 'Spend more time per reading session'
         ELSE 'Take shorter, more focused reading breaks'
       END AS durationRecommendation,
       CASE 
         WHEN avgReadingConsistency > 0.5 THEN 'Maintain regular reading schedule'
         ELSE 'Establish more consistent reading habits'
       END AS consistencyRecommendation,
       CASE 
         WHEN avgReadingIntensity > 0.5 THEN 'Increase overall reading intensity'
         ELSE 'Focus on quality over quantity'
       END AS intensityRecommendation,
       avgPageViewDiff,
       avgReadingDaysDiff,
       avgDurationDiff,
       avgReadingConsistency,
       avgReadingIntensity
ORDER BY similarStudentCount DESC, avgPageViewDiff DESC
LIMIT 10

// Get a random student who has completed courses and is pursuing a degree
MATCH (s:Student)-[:PURSUING]->(d:Degree)
WHERE EXISTS((s)-[:COMPLETED]->(:Course))
RETURN s.id, s.name, d.name, COUNT{(s)-[:COMPLETED]->(:Course)} as coursesCompleted
LIMIT 10

// List all terms in the database
MATCH (t:Term)
RETURN t.id, t.name, t.startDate, t.endDate
ORDER BY t.startDate DESC

// Show prerequisite chains for Computer Science courses
MATCH path = (prereq:Course)-[:PREREQUISITE_FOR*1..3]->(course:Course)
WHERE course.department = 'Computer Science'
RETURN path
LIMIT 25

// Find high-performing students to learn from
MATCH (s:Student)-[c:COMPLETED]->(course:Course)
WHERE c.grade IN ['A', 'A-']
WITH s, COUNT(DISTINCT course) as excellentCourses
WHERE excellentCourses > 5
RETURN s.id, s.name, s.learningStyle, excellentCourses
ORDER BY excellentCourses DESC
LIMIT 10

PROFILE MATCH (s:Student)-[:COMPLETED]->(c:Course)
RETURN COUNT(*)

// Convert string timestamp to datetime
datetime(replace(timestamp_string, ' ', 'T'))

// Example:
WITH '2023-09-15 14:30:00' AS ts
RETURN datetime(replace(ts, ' ', 'T')) AS converted_datetime

// Check if data loaded correctly
CALL db.labels() YIELD label
RETURN label;

// Check relationship types
CALL db.relationshipTypes() YIELD relationshipType
RETURN relationshipType;

// See sample of each node type
MATCH (s:Student) RETURN s LIMIT 1
UNION
MATCH (c:Course) RETURN c LIMIT 1
UNION
MATCH (f:Faculty) RETURN f LIMIT 1
UNION
MATCH (d:Degree) RETURN d LIMIT 1;

// Check database statistics
CALL db.stats.retrieve("GRAPH COUNTS")
YIELD data
RETURN data;