-- Flexible PostgreSQL import schema for Learning Outcome OS AI-ready dataset v2

DROP TABLE IF EXISTS schools;
CREATE TABLE schools (
  school_id TEXT,
  school_name TEXT,
  board TEXT,
  state TEXT,
  city TEXT,
  region_type TEXT,
  academic_year TEXT,
  is_synthetic INTEGER
);
-- COPY schools FROM '/path/schools.csv' WITH CSV HEADER;

DROP TABLE IF EXISTS classes;
CREATE TABLE classes (
  class_id TEXT,
  school_id TEXT,
  grade INTEGER,
  section TEXT,
  class_name TEXT,
  academic_year TEXT,
  class_teacher_id TEXT
);
-- COPY classes FROM '/path/classes.csv' WITH CSV HEADER;

DROP TABLE IF EXISTS subjects;
CREATE TABLE subjects (
  subject_id TEXT,
  grade INTEGER,
  subject TEXT,
  subject_code TEXT
);
-- COPY subjects FROM '/path/subjects.csv' WITH CSV HEADER;

DROP TABLE IF EXISTS chapters;
CREATE TABLE chapters (
  chapter_id TEXT,
  grade INTEGER,
  subject TEXT,
  chapter TEXT,
  chapter_order INTEGER,
  lo_count INTEGER,
  is_active INTEGER
);
-- COPY chapters FROM '/path/chapters.csv' WITH CSV HEADER;

DROP TABLE IF EXISTS teachers;
CREATE TABLE teachers (
  teacher_id TEXT,
  school_id TEXT,
  class_id TEXT,
  teacher_name TEXT,
  grade INTEGER,
  section TEXT,
  subject TEXT,
  email TEXT,
  experience_years INTEGER,
  ai_feedback_participation_rate DOUBLE PRECISION
);
-- COPY teachers FROM '/path/teachers.csv' WITH CSV HEADER;

DROP TABLE IF EXISTS student_profiles;
CREATE TABLE student_profiles (
  student_id TEXT,
  school_id TEXT,
  class_id TEXT,
  grade INTEGER,
  section TEXT,
  roll_number INTEGER,
  student_name TEXT,
  learning_style TEXT,
  learner_archetype TEXT,
  baseline_level TEXT,
  attendance_percentage INTEGER,
  assignment_completion_rate INTEGER,
  average_login_per_week INTEGER,
  inactive_days_last_14 INTEGER,
  parent_contact_available INTEGER,
  is_synthetic INTEGER,
  train_split TEXT
);
-- COPY student_profiles FROM '/path/student_profiles.csv' WITH CSV HEADER;

DROP TABLE IF EXISTS learning_outcomes;
CREATE TABLE learning_outcomes (
  lo_id TEXT,
  grade INTEGER,
  subject TEXT,
  chapter TEXT,
  competency TEXT,
  title TEXT,
  description TEXT,
  difficulty TEXT,
  bloom_level TEXT,
  source_framework TEXT,
  source_pdf TEXT,
  source_pages TEXT,
  alignment_confidence DOUBLE PRECISION,
  difficulty_score INTEGER,
  bloom_score INTEGER,
  embedding_text TEXT,
  is_active INTEGER,
  train_split TEXT
);
-- COPY learning_outcomes FROM '/path/learning_outcomes.csv' WITH CSV HEADER;

DROP TABLE IF EXISTS lo_dependencies;
CREATE TABLE lo_dependencies (
  lo_id TEXT,
  prerequisite_lo_id TEXT,
  relationship_type TEXT,
  strength TEXT
);
-- COPY lo_dependencies FROM '/path/lo_dependencies.csv' WITH CSV HEADER;

DROP TABLE IF EXISTS questions;
CREATE TABLE questions (
  question_id TEXT,
  lo_id TEXT,
  grade INTEGER,
  subject TEXT,
  chapter TEXT,
  question_text TEXT,
  question_type TEXT,
  difficulty TEXT,
  difficulty_score INTEGER,
  bloom_level TEXT,
  bloom_score INTEGER,
  correct_answer TEXT,
  rubric TEXT,
  max_marks INTEGER,
  source TEXT,
  source_lo_pdf TEXT,
  alignment_confidence DOUBLE PRECISION,
  embedding_text TEXT,
  train_split TEXT
);
-- COPY questions FROM '/path/questions.csv' WITH CSV HEADER;

DROP TABLE IF EXISTS question_options;
CREATE TABLE question_options (
  question_id TEXT,
  option_label TEXT,
  option_text TEXT,
  is_correct INTEGER
);
-- COPY question_options FROM '/path/question_options.csv' WITH CSV HEADER;

DROP TABLE IF EXISTS content_catalog;
CREATE TABLE content_catalog (
  content_id TEXT,
  lo_id TEXT,
  grade INTEGER,
  subject TEXT,
  chapter TEXT,
  title TEXT,
  content_type TEXT,
  target_use TEXT,
  difficulty TEXT,
  duration_minutes INTEGER,
  language TEXT,
  description TEXT,
  estimated_mastery_gain DOUBLE PRECISION,
  embedding_text TEXT,
  is_active INTEGER,
  train_split TEXT
);
-- COPY content_catalog FROM '/path/content_catalog.csv' WITH CSV HEADER;

DROP TABLE IF EXISTS assessments;
CREATE TABLE assessments (
  assessment_id TEXT,
  school_id TEXT,
  class_id TEXT,
  grade INTEGER,
  section TEXT,
  subject TEXT,
  assessment_name TEXT,
  assessment_type TEXT,
  scheduled_date TEXT,
  max_marks INTEGER,
  question_count INTEGER,
  academic_year TEXT,
  train_split TEXT
);
-- COPY assessments FROM '/path/assessments.csv' WITH CSV HEADER;

DROP TABLE IF EXISTS assessment_questions;
CREATE TABLE assessment_questions (
  assessment_id TEXT,
  question_id TEXT,
  question_order INTEGER
);
-- COPY assessment_questions FROM '/path/assessment_questions.csv' WITH CSV HEADER;

DROP TABLE IF EXISTS student_attempts;
CREATE TABLE student_attempts (
  attempt_id TEXT,
  assessment_id TEXT,
  school_id TEXT,
  class_id TEXT,
  student_id TEXT,
  question_id TEXT,
  lo_id TEXT,
  grade INTEGER,
  section TEXT,
  subject TEXT,
  question_type TEXT,
  difficulty_score INTEGER,
  bloom_score INTEGER,
  is_correct INTEGER,
  marks_obtained DOUBLE PRECISION,
  max_marks INTEGER,
  marks_ratio DOUBLE PRECISION,
  time_taken_seconds INTEGER,
  hint_used INTEGER,
  attempt_number INTEGER,
  submitted_at TEXT,
  train_split TEXT
);
-- COPY student_attempts FROM '/path/student_attempts.csv' WITH CSV HEADER;

DROP TABLE IF EXISTS initial_mastery_profiles;
CREATE TABLE initial_mastery_profiles (
  student_id TEXT,
  lo_id TEXT,
  grade INTEGER,
  section TEXT,
  subject TEXT,
  chapter TEXT,
  train_split TEXT,
  attempt_count INTEGER,
  accuracy DOUBLE PRECISION,
  average_marks_ratio DOUBLE PRECISION,
  average_time_seconds DOUBLE PRECISION,
  hint_usage_rate DOUBLE PRECISION,
  mastery_score DOUBLE PRECISION,
  status TEXT,
  confidence DOUBLE PRECISION,
  last_updated TEXT
);
-- COPY initial_mastery_profiles FROM '/path/initial_mastery_profiles.csv' WITH CSV HEADER;

DROP TABLE IF EXISTS mastery_profiles;
CREATE TABLE mastery_profiles (
  student_id TEXT,
  lo_id TEXT,
  grade INTEGER,
  section TEXT,
  subject TEXT,
  chapter TEXT,
  train_split TEXT,
  attempt_count INTEGER,
  accuracy DOUBLE PRECISION,
  average_marks_ratio DOUBLE PRECISION,
  average_time_seconds DOUBLE PRECISION,
  hint_usage_rate DOUBLE PRECISION,
  mastery_score DOUBLE PRECISION,
  status TEXT,
  confidence DOUBLE PRECISION,
  last_updated TEXT
);
-- COPY mastery_profiles FROM '/path/mastery_profiles.csv' WITH CSV HEADER;

DROP TABLE IF EXISTS engagement_logs;
CREATE TABLE engagement_logs (
  engagement_id TEXT,
  student_id TEXT,
  school_id TEXT,
  class_id TEXT,
  activity_date TEXT,
  login_count INTEGER,
  active_minutes INTEGER,
  content_completed_count INTEGER,
  quiz_attempt_count INTEGER,
  recommendation_click_count INTEGER,
  video_watch_ratio DOUBLE PRECISION,
  discussion_posts INTEGER,
  device_type TEXT,
  train_split TEXT
);
-- COPY engagement_logs FROM '/path/engagement_logs.csv' WITH CSV HEADER;

DROP TABLE IF EXISTS risk_profiles;
CREATE TABLE risk_profiles (
  risk_prediction_id TEXT,
  student_id TEXT,
  school_id TEXT,
  class_id TEXT,
  grade INTEGER,
  section TEXT,
  subject TEXT,
  risk_score DOUBLE PRECISION,
  risk_level TEXT,
  risk_label INTEGER,
  primary_reasons TEXT,
  recommended_intervention TEXT,
  model_version TEXT,
  generated_at TEXT,
  confidence DOUBLE PRECISION,
  train_split TEXT
);
-- COPY risk_profiles FROM '/path/risk_profiles.csv' WITH CSV HEADER;

DROP TABLE IF EXISTS recommendations;
CREATE TABLE recommendations (
  recommendation_id TEXT,
  student_id TEXT,
  school_id TEXT,
  class_id TEXT,
  lo_id TEXT,
  content_id TEXT,
  grade INTEGER,
  section TEXT,
  subject TEXT,
  recommendation_type TEXT,
  priority TEXT,
  reason TEXT,
  ai_confidence DOUBLE PRECISION,
  generated_at TEXT,
  shown_to_student INTEGER,
  clicked INTEGER,
  is_completed INTEGER,
  observed_mastery_gain DOUBLE PRECISION,
  model_version TEXT,
  train_split TEXT
);
-- COPY recommendations FROM '/path/recommendations.csv' WITH CSV HEADER;

DROP TABLE IF EXISTS teacher_interventions;
CREATE TABLE teacher_interventions (
  intervention_id TEXT,
  school_id TEXT,
  class_id TEXT,
  teacher_id TEXT,
  grade INTEGER,
  section TEXT,
  subject TEXT,
  lo_id TEXT,
  intervention_type TEXT,
  affected_students INTEGER,
  avg_mastery_before DOUBLE PRECISION,
  suggested_action TEXT,
  scheduled_week TEXT,
  status TEXT,
  expected_mastery_gain DOUBLE PRECISION,
  generated_by_ai INTEGER,
  train_split TEXT
);
-- COPY teacher_interventions FROM '/path/teacher_interventions.csv' WITH CSV HEADER;

DROP TABLE IF EXISTS subjective_answers;
CREATE TABLE subjective_answers (
  answer_id TEXT,
  attempt_id TEXT,
  student_id TEXT,
  question_id TEXT,
  lo_id TEXT,
  grade INTEGER,
  subject TEXT,
  question_type TEXT,
  student_answer TEXT,
  model_answer TEXT,
  rubric TEXT,
  max_marks INTEGER,
  teacher_marks DOUBLE PRECISION,
  ai_predicted_marks DOUBLE PRECISION,
  absolute_error DOUBLE PRECISION,
  rubric_match_score DOUBLE PRECISION,
  concept_coverage_score DOUBLE PRECISION,
  feedback_text TEXT,
  teacher_review_required INTEGER,
  train_split TEXT
);
-- COPY subjective_answers FROM '/path/subjective_answers.csv' WITH CSV HEADER;

DROP TABLE IF EXISTS teacher_feedback;
CREATE TABLE teacher_feedback (
  feedback_id TEXT,
  teacher_id TEXT,
  student_id TEXT,
  related_entity_type TEXT,
  related_entity_id TEXT,
  feedback_type TEXT,
  teacher_rating INTEGER,
  correction_required INTEGER,
  correction_text TEXT,
  created_at TEXT,
  train_split TEXT
);
-- COPY teacher_feedback FROM '/path/teacher_feedback.csv' WITH CSV HEADER;

DROP TABLE IF EXISTS student_digital_twins;
CREATE TABLE student_digital_twins (
  digital_twin_id TEXT,
  student_id TEXT,
  school_id TEXT,
  class_id TEXT,
  grade INTEGER,
  section TEXT,
  overall_mastery_score DOUBLE PRECISION,
  strongest_subject TEXT,
  weakest_subject TEXT,
  top_weak_lo_ids TEXT,
  learning_speed_score DOUBLE PRECISION,
  consistency_score DOUBLE PRECISION,
  preferred_content_type TEXT,
  current_risk_level TEXT,
  current_risk_score DOUBLE PRECISION,
  recommended_next_action TEXT,
  last_updated TEXT,
  train_split TEXT
);
-- COPY student_digital_twins FROM '/path/student_digital_twins.csv' WITH CSV HEADER;

DROP TABLE IF EXISTS ai_prediction_logs;
CREATE TABLE ai_prediction_logs (
  prediction_log_id TEXT,
  model_name TEXT,
  model_version TEXT,
  entity_type TEXT,
  entity_id TEXT,
  prediction_output TEXT,
  confidence DOUBLE PRECISION,
  latency_ms INTEGER,
  created_at TEXT,
  train_split TEXT
);
-- COPY ai_prediction_logs FROM '/path/ai_prediction_logs.csv' WITH CSV HEADER;

DROP TABLE IF EXISTS ml_features_student_subject;
CREATE TABLE ml_features_student_subject (
  feature_row_id TEXT,
  student_id TEXT,
  school_id TEXT,
  class_id TEXT,
  grade INTEGER,
  section TEXT,
  subject TEXT,
  avg_mastery_score DOUBLE PRECISION,
  weak_lo_count INTEGER,
  developing_lo_count INTEGER,
  mastered_lo_count INTEGER,
  avg_confidence DOUBLE PRECISION,
  avg_accuracy DOUBLE PRECISION,
  avg_marks_ratio DOUBLE PRECISION,
  avg_time_seconds DOUBLE PRECISION,
  hint_usage_rate DOUBLE PRECISION,
  total_attempts INTEGER,
  attendance_percentage INTEGER,
  assignment_completion_rate INTEGER,
  average_login_per_week INTEGER,
  inactive_days_last_14 INTEGER,
  avg_active_minutes DOUBLE PRECISION,
  total_logins INTEGER,
  avg_video_watch_ratio DOUBLE PRECISION,
  total_content_completed INTEGER,
  total_quiz_attempts INTEGER,
  risk_score DOUBLE PRECISION,
  risk_level TEXT,
  risk_label INTEGER,
  train_split TEXT
);
-- COPY ml_features_student_subject FROM '/path/ml_features_student_subject.csv' WITH CSV HEADER;

DROP TABLE IF EXISTS ml_features_student_lo;
CREATE TABLE ml_features_student_lo (
  feature_row_id TEXT,
  student_id TEXT,
  lo_id TEXT,
  grade INTEGER,
  section TEXT,
  subject TEXT,
  chapter TEXT,
  train_split TEXT,
  attempt_count INTEGER,
  accuracy DOUBLE PRECISION,
  average_marks_ratio DOUBLE PRECISION,
  average_time_seconds DOUBLE PRECISION,
  hint_usage_rate DOUBLE PRECISION,
  mastery_score DOUBLE PRECISION,
  status TEXT,
  confidence DOUBLE PRECISION,
  last_updated TEXT,
  school_id TEXT,
  class_id TEXT,
  attendance_percentage INTEGER,
  assignment_completion_rate INTEGER,
  average_login_per_week INTEGER,
  inactive_days_last_14 INTEGER,
  mastery_label INTEGER
);
-- COPY ml_features_student_lo FROM '/path/ml_features_student_lo.csv' WITH CSV HEADER;

DROP TABLE IF EXISTS training_lo_tagging;
CREATE TABLE training_lo_tagging (
  question_id TEXT,
  question_text TEXT,
  embedding_text TEXT,
  lo_id TEXT,
  grade INTEGER,
  subject TEXT,
  chapter TEXT,
  difficulty TEXT,
  difficulty_score INTEGER,
  bloom_level TEXT,
  bloom_score INTEGER,
  train_split TEXT
);
-- COPY training_lo_tagging FROM '/path/training_lo_tagging.csv' WITH CSV HEADER;

DROP TABLE IF EXISTS training_bloom_classification;
CREATE TABLE training_bloom_classification (
  question_id TEXT,
  question_text TEXT,
  embedding_text TEXT,
  bloom_level TEXT,
  bloom_score INTEGER,
  grade INTEGER,
  subject TEXT,
  question_type TEXT,
  train_split TEXT
);
-- COPY training_bloom_classification FROM '/path/training_bloom_classification.csv' WITH CSV HEADER;

DROP TABLE IF EXISTS training_risk_prediction;
CREATE TABLE training_risk_prediction (
  feature_row_id TEXT,
  student_id TEXT,
  school_id TEXT,
  class_id TEXT,
  grade INTEGER,
  section TEXT,
  subject TEXT,
  avg_mastery_score DOUBLE PRECISION,
  weak_lo_count INTEGER,
  developing_lo_count INTEGER,
  mastered_lo_count INTEGER,
  avg_confidence DOUBLE PRECISION,
  avg_accuracy DOUBLE PRECISION,
  avg_marks_ratio DOUBLE PRECISION,
  avg_time_seconds DOUBLE PRECISION,
  hint_usage_rate DOUBLE PRECISION,
  total_attempts INTEGER,
  attendance_percentage INTEGER,
  assignment_completion_rate INTEGER,
  average_login_per_week INTEGER,
  inactive_days_last_14 INTEGER,
  avg_active_minutes DOUBLE PRECISION,
  total_logins INTEGER,
  avg_video_watch_ratio DOUBLE PRECISION,
  total_content_completed INTEGER,
  total_quiz_attempts INTEGER,
  risk_score DOUBLE PRECISION,
  risk_level TEXT,
  risk_label INTEGER,
  train_split TEXT
);
-- COPY training_risk_prediction FROM '/path/training_risk_prediction.csv' WITH CSV HEADER;

DROP TABLE IF EXISTS training_mastery_prediction;
CREATE TABLE training_mastery_prediction (
  feature_row_id TEXT,
  student_id TEXT,
  lo_id TEXT,
  grade INTEGER,
  section TEXT,
  subject TEXT,
  chapter TEXT,
  train_split TEXT,
  attempt_count INTEGER,
  accuracy DOUBLE PRECISION,
  average_marks_ratio DOUBLE PRECISION,
  average_time_seconds DOUBLE PRECISION,
  hint_usage_rate DOUBLE PRECISION,
  mastery_score DOUBLE PRECISION,
  status TEXT,
  confidence DOUBLE PRECISION,
  last_updated TEXT,
  school_id TEXT,
  class_id TEXT,
  attendance_percentage INTEGER,
  assignment_completion_rate INTEGER,
  average_login_per_week INTEGER,
  inactive_days_last_14 INTEGER,
  mastery_label INTEGER
);
-- COPY training_mastery_prediction FROM '/path/training_mastery_prediction.csv' WITH CSV HEADER;

DROP TABLE IF EXISTS training_answer_scoring;
CREATE TABLE training_answer_scoring (
  answer_id TEXT,
  question_id TEXT,
  student_id TEXT,
  lo_id TEXT,
  grade INTEGER,
  subject TEXT,
  question_type TEXT,
  student_answer TEXT,
  model_answer TEXT,
  rubric TEXT,
  max_marks INTEGER,
  teacher_marks DOUBLE PRECISION,
  ai_predicted_marks DOUBLE PRECISION,
  rubric_match_score DOUBLE PRECISION,
  concept_coverage_score DOUBLE PRECISION,
  teacher_review_required INTEGER,
  train_split TEXT
);
-- COPY training_answer_scoring FROM '/path/training_answer_scoring.csv' WITH CSV HEADER;

DROP TABLE IF EXISTS training_recommendation_outcomes;
CREATE TABLE training_recommendation_outcomes (
  recommendation_id TEXT,
  student_id TEXT,
  lo_id TEXT,
  content_id TEXT,
  grade INTEGER,
  subject TEXT,
  recommendation_type TEXT,
  priority TEXT,
  ai_confidence DOUBLE PRECISION,
  clicked INTEGER,
  is_completed INTEGER,
  observed_mastery_gain DOUBLE PRECISION,
  train_split TEXT
);
-- COPY training_recommendation_outcomes FROM '/path/training_recommendation_outcomes.csv' WITH CSV HEADER;
