schema.sql

-- Schema SQL para banco de dados de cursos da UAlg
-- Tabelas normalizadas para armazenar informações de cursos, módulos e documentos

PRAGMA foreign_keys = ON;

-- Tabela de níveis de curso (Licenciatura, Mestrado, Doutoramento, etc.)
CREATE TABLE IF NOT EXISTS levels (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT UNIQUE NOT NULL
);

-- Tabela de escolas/faculdades
CREATE TABLE IF NOT EXISTS schools (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT UNIQUE NOT NULL
);

-- Tabela de áreas de conhecimento
CREATE TABLE IF NOT EXISTS areas (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT UNIQUE NOT NULL
);

-- Tabela principal de cursos
CREATE TABLE IF NOT EXISTS courses (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  code TEXT,
  title TEXT NOT NULL,
  description TEXT,
  level_id INTEGER,
  school_id INTEGER,
  language TEXT,
  regime TEXT,
  modality TEXT,
  url TEXT UNIQUE,
  last_scraped TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY(level_id) REFERENCES levels(id),
  FOREIGN KEY(school_id) REFERENCES schools(id)
);

-- Tabela de relação many-to-many entre cursos e áreas
CREATE TABLE IF NOT EXISTS course_area (
  course_id INTEGER,
  area_id INTEGER,
  PRIMARY KEY(course_id, area_id),
  FOREIGN KEY(course_id) REFERENCES courses(id) ON DELETE CASCADE,
  FOREIGN KEY(area_id) REFERENCES areas(id) ON DELETE CASCADE
);

-- Tabela de módulos/unidades curriculares
CREATE TABLE IF NOT EXISTS modules (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  code TEXT,
  title TEXT NOT NULL,
  description TEXT,
  ects REAL,
  year INTEGER,
  semester INTEGER,
  UNIQUE(code, title)
);

-- Tabela de relação many-to-many entre módulos e cursos
CREATE TABLE IF NOT EXISTS module_course (
  module_id INTEGER,
  course_id INTEGER,
  mandatory BOOLEAN DEFAULT 1,
  PRIMARY KEY(module_id, course_id),
  FOREIGN KEY(module_id) REFERENCES modules(id) ON DELETE CASCADE,
  FOREIGN KEY(course_id) REFERENCES courses(id) ON DELETE CASCADE
);

-- Tabela de documentos associados aos cursos (PDFs, etc.)
CREATE TABLE IF NOT EXISTS course_documents (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  course_id INTEGER,
  title TEXT,
  url TEXT,
  local_path TEXT,
  filetype TEXT,
  FOREIGN KEY(course_id) REFERENCES courses(id) ON DELETE CASCADE
);

-- Índices para melhorar performance de consultas
CREATE INDEX IF NOT EXISTS idx_courses_level ON courses(level_id);
CREATE INDEX IF NOT EXISTS idx_courses_school ON courses(school_id);
CREATE INDEX IF NOT EXISTS idx_courses_url ON courses(url);
CREATE INDEX IF NOT EXISTS idx_course_area_course ON course_area(course_id);
CREATE INDEX IF NOT EXISTS idx_course_area_area ON course_area(area_id);
CREATE INDEX IF NOT EXISTS idx_module_course_course ON module_course(course_id);
CREATE INDEX IF NOT EXISTS idx_module_course_module ON module_course(module_id);
CREATE INDEX IF NOT EXISTS idx_course_documents_course ON course_documents(course_id);