-- OBE Syllabus Generator System database schema
-- Compatible with SQLite-style SQL. Adapt AUTO_INCREMENT/UUID handling as needed for MySQL or PostgreSQL.

PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS roles (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL UNIQUE CHECK (name IN ('Admin', 'Program Head', 'Faculty'))
);

CREATE TABLE IF NOT EXISTS institutions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS mission_vision_philosophy (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  institution_id INTEGER NOT NULL,
  mission TEXT NOT NULL,
  vision TEXT NOT NULL,
  educational_philosophy TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (institution_id) REFERENCES institutions(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS mission_keywords (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  institution_id INTEGER NOT NULL,
  keyword TEXT NOT NULL,
  description TEXT,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (institution_id, keyword),
  FOREIGN KEY (institution_id) REFERENCES institutions(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS programs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL UNIQUE,
  description TEXT NOT NULL,
  created_by INTEGER,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  role_id INTEGER NOT NULL,
  program_id INTEGER,
  full_name TEXT NOT NULL,
  email TEXT UNIQUE,
  password_hash TEXT,
  status TEXT NOT NULL DEFAULT 'Active' CHECK (status IN ('Active', 'Inactive')),
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (role_id) REFERENCES roles(id),
  FOREIGN KEY (program_id) REFERENCES programs(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS program_educational_objectives (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  program_id INTEGER NOT NULL,
  code TEXT NOT NULL,
  description TEXT NOT NULL,
  sort_order INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (program_id, code),
  FOREIGN KEY (program_id) REFERENCES programs(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS peo_mission_keyword_mapping (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  peo_id INTEGER NOT NULL,
  mission_keyword_id INTEGER NOT NULL,
  created_by INTEGER,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (peo_id, mission_keyword_id),
  FOREIGN KEY (peo_id) REFERENCES program_educational_objectives(id) ON DELETE CASCADE,
  FOREIGN KEY (mission_keyword_id) REFERENCES mission_keywords(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS program_outcomes (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  program_id INTEGER NOT NULL,
  code TEXT NOT NULL,
  description TEXT NOT NULL,
  sort_order INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (program_id, code),
  FOREIGN KEY (program_id) REFERENCES programs(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS courses (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  program_id INTEGER NOT NULL,
  course_code TEXT NOT NULL,
  course_title TEXT NOT NULL,
  course_description TEXT NOT NULL,
  units INTEGER NOT NULL CHECK (units > 0),
  prerequisite TEXT,
  year_level TEXT NOT NULL,
  semester_term TEXT NOT NULL,
  assigned_faculty_id INTEGER,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (program_id, course_code),
  FOREIGN KEY (program_id) REFERENCES programs(id) ON DELETE CASCADE,
  FOREIGN KEY (assigned_faculty_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS course_learning_outcomes (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  course_id INTEGER NOT NULL,
  code TEXT NOT NULL,
  description TEXT NOT NULL,
  bloom_verb TEXT,
  sort_order INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (course_id, code),
  FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS clo_po_mapping (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  clo_id INTEGER NOT NULL,
  po_id INTEGER NOT NULL,
  mapping_level TEXT NOT NULL CHECK (mapping_level IN ('I', 'E', 'D')),
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (clo_id, po_id),
  FOREIGN KEY (clo_id) REFERENCES course_learning_outcomes(id) ON DELETE CASCADE,
  FOREIGN KEY (po_id) REFERENCES program_outcomes(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS syllabi (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  course_id INTEGER NOT NULL UNIQUE,
  faculty_id INTEGER,
  status TEXT NOT NULL DEFAULT 'Draft' CHECK (status IN ('Draft', 'Submitted', 'Approved', 'Returned')),
  submitted_at TEXT,
  approved_by INTEGER,
  approved_at TEXT,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
  FOREIGN KEY (faculty_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS topics (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  syllabus_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  description TEXT NOT NULL,
  intended_learning_outcomes TEXT NOT NULL,
  learning_resources TEXT NOT NULL,
  hours_or_weeks TEXT NOT NULL,
  sort_order INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (syllabus_id) REFERENCES syllabi(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS teaching_learning_activities (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  topic_id INTEGER NOT NULL,
  activity TEXT NOT NULL,
  sort_order INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (topic_id) REFERENCES topics(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS assessments (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  syllabus_id INTEGER NOT NULL,
  type TEXT NOT NULL,
  title TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (syllabus_id) REFERENCES syllabi(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS assessment_clo_mapping (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  assessment_id INTEGER NOT NULL,
  clo_id INTEGER NOT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (assessment_id, clo_id),
  FOREIGN KEY (assessment_id) REFERENCES assessments(id) ON DELETE CASCADE,
  FOREIGN KEY (clo_id) REFERENCES course_learning_outcomes(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS references_list (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  syllabus_id INTEGER NOT NULL,
  reference_type TEXT NOT NULL,
  citation TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (syllabus_id) REFERENCES syllabi(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS students (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  course_id INTEGER NOT NULL,
  student_no TEXT NOT NULL,
  full_name TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'Active' CHECK (status IN ('Active', 'Inactive')),
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (course_id, student_no),
  FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS assessment_scores (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  assessment_id INTEGER NOT NULL,
  student_id INTEGER NOT NULL,
  score REAL NOT NULL CHECK (score >= 0 AND score <= 100),
  encoded_by INTEGER,
  encoded_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (assessment_id, student_id),
  FOREIGN KEY (assessment_id) REFERENCES assessments(id) ON DELETE CASCADE,
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  FOREIGN KEY (encoded_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS clo_attainment (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  course_id INTEGER NOT NULL,
  clo_id INTEGER NOT NULL,
  passing_score REAL NOT NULL DEFAULT 75 CHECK (passing_score > 0 AND passing_score <= 100),
  students_total INTEGER NOT NULL DEFAULT 0,
  students_passed INTEGER NOT NULL DEFAULT 0,
  attainment_percentage REAL NOT NULL DEFAULT 0,
  computed_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (course_id, clo_id),
  FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
  FOREIGN KEY (clo_id) REFERENCES course_learning_outcomes(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS curriculum_mapping (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  program_id INTEGER NOT NULL,
  course_id INTEGER,
  source_type TEXT NOT NULL CHECK (source_type IN ('Mission', 'PEO', 'PO', 'Course', 'CLO', 'Assessment')),
  source_id INTEGER NOT NULL,
  target_type TEXT NOT NULL CHECK (target_type IN ('Mission', 'PEO', 'PO', 'Course', 'CLO', 'Assessment')),
  target_id INTEGER NOT NULL,
  mapping_level TEXT CHECK (mapping_level IN ('I', 'E', 'D')),
  created_by INTEGER,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (program_id, course_id, source_type, source_id, target_type, target_id),
  FOREIGN KEY (program_id) REFERENCES programs(id) ON DELETE CASCADE,
  FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS rubrics (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  assessment_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  created_by INTEGER,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (assessment_id) REFERENCES assessments(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS rubric_criteria (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  rubric_id INTEGER NOT NULL,
  criterion TEXT NOT NULL,
  excellent_description TEXT,
  satisfactory_description TEXT,
  needs_improvement_description TEXT,
  mapped_clo_id INTEGER,
  sort_order INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (rubric_id) REFERENCES rubrics(id) ON DELETE CASCADE,
  FOREIGN KEY (mapped_clo_id) REFERENCES course_learning_outcomes(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS quality_improvement_actions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  program_id INTEGER NOT NULL,
  course_id INTEGER,
  clo_id INTEGER,
  po_id INTEGER,
  trigger_result TEXT NOT NULL,
  recommended_action TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'Open' CHECK (status IN ('Open', 'In Progress', 'Closed')),
  created_by INTEGER,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  closed_at TEXT,
  FOREIGN KEY (program_id) REFERENCES programs(id) ON DELETE CASCADE,
  FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
  FOREIGN KEY (clo_id) REFERENCES course_learning_outcomes(id) ON DELETE SET NULL,
  FOREIGN KEY (po_id) REFERENCES program_outcomes(id) ON DELETE SET NULL,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS syllabus_versions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  syllabus_id INTEGER NOT NULL,
  version_no INTEGER NOT NULL,
  action TEXT NOT NULL,
  revision_comment TEXT,
  snapshot_json TEXT,
  created_by INTEGER,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (syllabus_id, version_no),
  FOREIGN KEY (syllabus_id) REFERENCES syllabi(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE INDEX IF NOT EXISTS idx_users_role_id ON users(role_id);
CREATE INDEX IF NOT EXISTS idx_mission_keywords_institution_id ON mission_keywords(institution_id);
CREATE INDEX IF NOT EXISTS idx_peo_mission_keyword_peo_id ON peo_mission_keyword_mapping(peo_id);
CREATE INDEX IF NOT EXISTS idx_peo_mission_keyword_keyword_id ON peo_mission_keyword_mapping(mission_keyword_id);
CREATE INDEX IF NOT EXISTS idx_users_program_id ON users(program_id);
CREATE INDEX IF NOT EXISTS idx_courses_program_id ON courses(program_id);
CREATE INDEX IF NOT EXISTS idx_courses_assigned_faculty_id ON courses(assigned_faculty_id);
CREATE INDEX IF NOT EXISTS idx_clos_course_id ON course_learning_outcomes(course_id);
CREATE INDEX IF NOT EXISTS idx_clo_po_mapping_clo_id ON clo_po_mapping(clo_id);
CREATE INDEX IF NOT EXISTS idx_clo_po_mapping_po_id ON clo_po_mapping(po_id);
CREATE INDEX IF NOT EXISTS idx_assessments_syllabus_id ON assessments(syllabus_id);
CREATE INDEX IF NOT EXISTS idx_topics_syllabus_id ON topics(syllabus_id);
CREATE INDEX IF NOT EXISTS idx_students_course_id ON students(course_id);
CREATE INDEX IF NOT EXISTS idx_assessment_scores_assessment_id ON assessment_scores(assessment_id);
CREATE INDEX IF NOT EXISTS idx_assessment_scores_student_id ON assessment_scores(student_id);
CREATE INDEX IF NOT EXISTS idx_clo_attainment_course_id ON clo_attainment(course_id);
CREATE INDEX IF NOT EXISTS idx_curriculum_mapping_program_id ON curriculum_mapping(program_id);
CREATE INDEX IF NOT EXISTS idx_curriculum_mapping_course_id ON curriculum_mapping(course_id);
CREATE INDEX IF NOT EXISTS idx_rubrics_assessment_id ON rubrics(assessment_id);
CREATE INDEX IF NOT EXISTS idx_rubric_criteria_rubric_id ON rubric_criteria(rubric_id);
CREATE INDEX IF NOT EXISTS idx_quality_improvement_program_id ON quality_improvement_actions(program_id);
CREATE INDEX IF NOT EXISTS idx_syllabus_versions_syllabus_id ON syllabus_versions(syllabus_id);

INSERT OR IGNORE INTO roles (id, name) VALUES
  (1, 'Admin'),
  (2, 'Program Head'),
  (3, 'Faculty');