Index: iknow-api/Controllers/AdminController.cs =================================================================== --- iknow-api/Controllers/AdminController.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) +++ iknow-api/Controllers/AdminController.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -0,0 +1,119 @@ +using System.Security.Claims; +using iknow_api.DTOs; +using iknow_api.Services; +using Microsoft.AspNetCore.Authorization; +using Microsoft.AspNetCore.Mvc; + +namespace iknow_api.Controllers +{ + /// + /// UC012 subjects, UC013 active semesters, UC014 professor assignments. + /// Every action requires the admin role, checked against the validated token. + /// + [ApiController] + [Route("api/[controller]")] + [Authorize] + public class AdminController : ControllerBase + { + private readonly IAdminService _adminService; + + public AdminController(IAdminService adminService) + { + _adminService = adminService; + } + + // ---------- UC012 ---------- + + [HttpGet("subjects")] + public async Task GetSubjects() => + RequireAdmin(out var error) ? Ok(await _adminService.GetSubjectsAsync()) : error; + + [HttpPost("subjects")] + public async Task CreateSubject([FromBody] SubjectWriteDto request) => + RequireAdmin(out var error) ? Result(await _adminService.CreateSubjectAsync(request)) : error; + + [HttpPut("subjects/{id:int}")] + public async Task UpdateSubject(int id, [FromBody] SubjectWriteDto request) => + RequireAdmin(out var error) ? Result(await _adminService.UpdateSubjectAsync(id, request)) : error; + + [HttpDelete("subjects/{id:int}")] + public async Task DeleteSubject(int id) => + RequireAdmin(out var error) ? Result(await _adminService.DeleteSubjectAsync(id)) : error; + + [HttpPost("subjects/{id:int}/majors")] + public async Task LinkMajor(int id, [FromBody] MajorLinkDto request) => + RequireAdmin(out var error) ? Result(await _adminService.LinkMajorAsync(id, request)) : error; + + [HttpDelete("subjects/{id:int}/majors/{majorId:int}")] + public async Task UnlinkMajor(int id, int majorId) => + RequireAdmin(out var error) ? Result(await _adminService.UnlinkMajorAsync(id, majorId)) : error; + + [HttpPost("subjects/{id:int}/prerequisites")] + public async Task AddPrerequisite(int id, [FromBody] PrerequisiteDto request) => + RequireAdmin(out var error) ? Result(await _adminService.AddPrerequisiteAsync(id, request)) : error; + + [HttpDelete("subjects/{id:int}/prerequisites/{dependencyId:int}")] + public async Task RemovePrerequisite(int id, int dependencyId) => + RequireAdmin(out var error) ? Result(await _adminService.RemovePrerequisiteAsync(id, dependencyId)) : error; + + // ---------- UC013 ---------- + + [HttpGet("semesters")] + public async Task GetSemesters() => + RequireAdmin(out var error) ? Ok(await _adminService.GetSemestersAsync()) : error; + + [HttpPost("semesters")] + public async Task CreateSemester([FromBody] SemesterWriteDto request) => + RequireAdmin(out var error) ? Result(await _adminService.CreateSemesterAsync(request)) : error; + + // ---------- UC014 ---------- + + [HttpGet("schedule/{semesterId:int}")] + public async Task GetSchedule(int semesterId) + { + if (!RequireAdmin(out var error)) return error; + + var schedule = await _adminService.GetScheduleAsync(semesterId); + return schedule is null + ? NotFound(new AdminResultDto { Ok = false, Message = "Semester not found." }) + : Ok(schedule); + } + + [HttpPost("schedule")] + public async Task Assign([FromBody] ScheduleWriteDto request) => + RequireAdmin(out var error) ? Result(await _adminService.AssignAsync(request)) : error; + + [HttpDelete("schedule")] + public async Task Unassign([FromBody] ScheduleWriteDto request) => + RequireAdmin(out var error) ? Result(await _adminService.UnassignAsync(request)) : error; + + // ---------- shared ---------- + + [HttpGet("majors")] + public async Task GetMajors() => + RequireAdmin(out var error) ? Ok(await _adminService.GetMajorsAsync()) : error; + + private IActionResult Result(AdminResultDto result) => + result.Ok ? Ok(result) : BadRequest(result); + + /// + /// The role comes from the validated token, so it cannot be set by the caller. + /// + private bool RequireAdmin(out IActionResult error) + { + var role = User.FindFirst(ClaimTypes.Role)?.Value ?? User.FindFirst("role")?.Value; + if (!string.Equals(role, nameof(Models.UserRole.Admin), StringComparison.OrdinalIgnoreCase)) + { + error = StatusCode(403, new AdminResultDto + { + Ok = false, + Message = "This endpoint is for administrators." + }); + return false; + } + + error = Ok(); + return true; + } + } +} Index: iknow-api/Controllers/DbHealthController.cs =================================================================== --- iknow-api/Controllers/DbHealthController.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) +++ iknow-api/Controllers/DbHealthController.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -0,0 +1,49 @@ +using iknow_api.Data; +using Microsoft.AspNetCore.Mvc; +using Microsoft.EntityFrameworkCore; + +namespace iknow_api.Controllers; + +[ApiController] +[Route("[controller]")] +public class DbHealthController(AppDbContext db) : ControllerBase +{ + /// + /// Verifies that the SSH tunnel is up, the DB credentials work, and that + /// the entity mapping matches the schema created by sql/ddl.sql. + /// + [HttpGet] + public async Task Get() + { + try + { + // EF projects SqlQuery onto a column named "Value", hence the alias. + var version = await db.Database + .SqlQuery($"SELECT version() AS \"Value\"") + .SingleAsync(); + + return Ok(new + { + connected = true, + server = version, + counts = new + { + users = await db.User.CountAsync(), + subjects = await db.Subjects.CountAsync(), + enrolledSemesters = await db.EnrolledSemesters.CountAsync(), + semesterSubjects = await db.SemesterSubjects.CountAsync(), + passedSubjects = await db.PassedSubjects.CountAsync(), + } + }); + } + catch (Exception ex) + { + return StatusCode(503, new + { + connected = false, + error = ex.Message, + hint = "Is tunnel_scripta.cmd running and listening on localhost:9999?" + }); + } + } +} Index: iknow-api/Controllers/EnrollmentController.cs =================================================================== --- iknow-api/Controllers/EnrollmentController.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) +++ iknow-api/Controllers/EnrollmentController.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -0,0 +1,78 @@ +using System.Security.Claims; +using iknow_api.DTOs; +using iknow_api.Services; +using Microsoft.AspNetCore.Authorization; +using Microsoft.AspNetCore.Mvc; + +namespace iknow_api.Controllers +{ + [ApiController] + [Route("api/[controller]")] + [Authorize] + public class EnrollmentController : ControllerBase + { + private readonly IEnrollmentService _enrollmentService; + + public EnrollmentController(IEnrollmentService enrollmentService) + { + _enrollmentService = enrollmentService; + } + + [HttpGet("options")] + public async Task GetOptions() + { + if (!TryGetStudentId(out var studentId, out var error)) + { + return error; + } + + return Ok(await _enrollmentService.GetOptionsAsync(studentId)); + } + + [HttpPost] + public async Task Enroll([FromBody] EnrollSemesterRequestDto request) + { + if (!TryGetStudentId(out var studentId, out var error)) + { + return error; + } + + var result = await _enrollmentService.EnrollAsync(studentId, request); + return result.Ok ? Ok(result) : BadRequest(result); + } + + /// + /// Reads the caller from the validated token, so a student can only ever + /// enrol themselves. + /// + private bool TryGetStudentId(out int studentId, out IActionResult error) + { + studentId = 0; + error = Forbid(); + + var role = User.FindFirst(ClaimTypes.Role)?.Value ?? User.FindFirst("role")?.Value; + if (!string.Equals(role, nameof(Models.UserRole.Student), StringComparison.OrdinalIgnoreCase)) + { + error = StatusCode(403, new EnrollSemesterResultDto + { + Ok = false, + Message = "Only students can enrol in a semester." + }); + return false; + } + + var idClaim = User.FindFirst("id")?.Value; + if (!int.TryParse(idClaim, out studentId)) + { + error = Unauthorized(new EnrollSemesterResultDto + { + Ok = false, + Message = "Token has no usable user id." + }); + return false; + } + + return true; + } + } +} Index: iknow-api/Controllers/ProfController.cs =================================================================== --- iknow-api/Controllers/ProfController.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) +++ iknow-api/Controllers/ProfController.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -0,0 +1,93 @@ +using System.Security.Claims; +using iknow_api.DTOs; +using iknow_api.Services; +using Microsoft.AspNetCore.Authorization; +using Microsoft.AspNetCore.Mvc; + +namespace iknow_api.Controllers +{ + [ApiController] + [Route("api/[controller]")] + [Authorize] + public class ProfController : ControllerBase + { + private readonly IProfService _profService; + + public ProfController(IProfService profService) + { + _profService = profService; + } + + /// + /// The subjects this professor teaches, each with the students enrolled + /// in it and their grade so far. + /// + [HttpGet("students")] + public async Task GetStudents() + { + if (!TryGetProfessorId(out var professorId, out var error)) + { + return error; + } + + return Ok(await _profService.GetStudentsBySubjectAsync(professorId)); + } + + [HttpPost("grade/add")] + public Task AddGrade([FromBody] GradeRequestDto request) => + SetGrade(request, GradeAction.Add); + + [HttpPost("grade/edit")] + public Task EditGrade([FromBody] GradeRequestDto request) => + SetGrade(request, GradeAction.Edit); + + [HttpPost("grade/remove")] + public Task RemoveGrade([FromBody] GradeRequestDto request) => + SetGrade(request, GradeAction.Remove); + + private async Task SetGrade(GradeRequestDto request, GradeAction action) + { + if (!TryGetProfessorId(out var professorId, out var error)) + { + return error; + } + + var result = await _profService.SetGradeAsync(professorId, request, action); + return result.Ok ? Ok(result) : BadRequest(result); + } + + /// + /// Reads the caller from the validated token and requires the professor + /// role, so one professor can never act on another one's subjects. + /// + private bool TryGetProfessorId(out int professorId, out IActionResult error) + { + professorId = 0; + error = Forbid(); + + var role = User.FindFirst(ClaimTypes.Role)?.Value ?? User.FindFirst("role")?.Value; + if (!string.Equals(role, nameof(Models.UserRole.Professor), StringComparison.OrdinalIgnoreCase)) + { + error = StatusCode(403, new GradeResultDto + { + Ok = false, + Message = "This endpoint is for professors." + }); + return false; + } + + var idClaim = User.FindFirst("id")?.Value; + if (!int.TryParse(idClaim, out professorId)) + { + error = Unauthorized(new GradeResultDto + { + Ok = false, + Message = "Token has no usable user id." + }); + return false; + } + + return true; + } + } +} Index: iknow-api/Controllers/UserController.cs =================================================================== --- iknow-api/Controllers/UserController.cs (revision 10009b9505124c201c365c19c07a9df40b091035) +++ iknow-api/Controllers/UserController.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -1,3 +1,4 @@ -using iknow_api.DTOs; +using iknow_api.DTOs; +using iknow_api.Models; using Microsoft.AspNetCore.Authorization; using Microsoft.AspNetCore.Mvc; @@ -83,4 +84,14 @@ var userData = await _userService.GetUserData(token); if (userData == null) return Ok(new { info = "can't get info" }); + + // The study programme comes from the student's most recent + // enrolment. Within an academic year winter precedes summer, + // so order by year first and put winter ahead of summer. + var currentEnrolment = userData.Enrolments? + .Where(es => es.Semester != null) + .OrderByDescending(es => es.Semester!.Year) + .ThenByDescending(es => es.Semester!.Type == sType.summer) + .FirstOrDefault(); + var personalInfo = new { @@ -88,13 +99,13 @@ embg = userData.EMBG ?? "", lastName = userData.Surname ?? "", - middleName = userData.MiddleName ?? "", + middleName = "", // Not an attribute of Users in the ER model firstName = userData.Name ?? "", maidenName = "", // Property doesn't exist in User model - dateOfBirth = userData.Bday.ToString("dd.MM.yyyy"), - gender = userData.Gender ?? "", - nationality = userData.Nationality ?? "", - citizenship = userData.Citizenship ?? "", + dateOfBirth = userData.Bday?.ToString("dd.MM.yyyy") ?? "", + gender = "", // Not an attribute of Users in the ER model + nationality = "", // Not an attribute of Users in the ER model + citizenship = "", // Not an attribute of Users in the ER model scholarship = "", - currentPlan = userData.EnrollmentInfo?.EnrollmentYear.ToString() ?? "", + currentPlan = userData.EnrollmentYear?.ToString() ?? "", registryNumber = "", notes = "", @@ -105,5 +116,5 @@ placeOfBirth = userData.ContactInfo?.City ?? "", municipalityOfBirth = userData.ContactInfo?.Municipality ?? "", - country = userData.Citizenship ?? "" + country = "" // Not an attribute of Users in the ER model }; var previousEducation = new @@ -112,6 +123,6 @@ profession = "", average = userData.HighSchool?.GPA.ToString() ?? "", - language = userData.Nationality ?? "", - country = userData.Nationality ?? "", + language = "", // Not an attribute of HighSchool in the ER model + country = "", previousUniversity = userData.HighSchool?.HighSchoolType.ToString() ?? "", previousFaculty = "", // Property doesn't exist in HighSchool model @@ -120,9 +131,9 @@ var enrollmentInfo = new { - enrollmentYear = userData.EnrollmentInfo?.EnrollmentYear.ToString() ?? "", - status = userData.EnrollmentInfo?.StudyStatus ?? "", + enrollmentYear = userData.EnrollmentYear?.ToString() ?? "", + status = "", // Not an attribute of Users in the ER model cycle = "Прв циклус", - program = userData.EnrollmentInfo?.Major?.Name ?? "", - quota = userData.EnrollmentInfo?.Quota.ToString() ?? "", + program = currentEnrolment?.Major?.Name ?? "", + quota = userData.Quota?.ToString() ?? "", secondaryEducationNumber = "", previousEducationCredits = "" @@ -132,5 +143,5 @@ placeOfResidence = userData.ContactInfo?.City ?? "", municipalityOfResidence = userData.ContactInfo?.Municipality ?? "", - country = userData.Citizenship ?? "", + country = "", // Not an attribute of Users in the ER model address = userData.ContactInfo?.Address ?? "", temporaryAddress = "", @@ -165,14 +176,16 @@ { id = userData[i].Id, - semester = (userData[i].Semester.Type == 0 ? "Зимски" : "Летен") + $"({userData[i].Semester.Year}/{userData[i].Semester.Year + 1})", + // sType declares summer first, so compare against the member + // rather than the ordinal - == 0 meant summer, not winter. + semester = (userData[i].Semester.Type == sType.winter ? "Зимски" : "Летен") + $"({userData[i].Semester.Year}/{userData[i].Semester.Year + 1})", direction = userData[i].Major?.Name ?? "", quota = userData[i].QuotaType.ToString(), - note = "", - studentCom = "", + note = userData[i].Note ?? "", + studentCom = userData[i].StudentComment ?? "", sum = "0,00", paid = "0,00", ukim = "", - createdOn = DateTime.Now.ToString("dd.MM.yyyy"), - dateChanged = DateTime.Now.ToString("dd.MM.yyyy"), + createdOn = userData[i].CratedAt.ToString("dd.MM.yyyy"), + dateChanged = userData[i].LastChange?.ToString("dd.MM.yyyy") ?? "", credits = "0,00", type = "Ред.", @@ -183,5 +196,5 @@ signatures = "0/5", status = "валиден", - completed = "Не" + completed = userData[i].Verified.HasValue ? "Да" : "Не" }; results.Add(newResult); @@ -208,5 +221,5 @@ { id = userData[i].Id, - name = (userData[i].Semester.Type == 0 ? "Зимски" : "Летен") + $"({userData[i].Semester.Year}/{userData[i].Semester.Year + 1})", + name = (userData[i].Semester.Type == sType.winter ? "Зимски" : "Летен") + $"({userData[i].Semester.Year}/{userData[i].Semester.Year + 1})", status = "валиден", serviceNumber = 1000000 + random.Next(10000, 100000) @@ -221,5 +234,5 @@ { // Create semester key like "winter_2025_2026" or "summer_2025_2026" - var semesterType = enrollment.Semester.Type == 0 ? "winter" : "summer"; + var semesterType = enrollment.Semester.Type == sType.winter ? "winter" : "summer"; var semesterKey = $"{semesterType}_{enrollment.Semester.Year}_{enrollment.Semester.Year + 1}"; @@ -258,9 +271,9 @@ } var currentSemester = userData.FirstOrDefault(); // Get the most recent/current semester - var semesterTypeGlobal = currentSemester.Semester.Type == 0 ? "winter" : "summer"; + var semesterTypeGlobal = currentSemester.Semester.Type == sType.winter ? "winter" : "summer"; var semesterData = new { id = $"{semesterTypeGlobal}_{currentSemester.Semester.Year}_{currentSemester.Semester.Year + 1}", - name = (currentSemester.Semester.Type == 0 ? "Зимски" : "Летен") + $" ({currentSemester.Semester.Year}/{currentSemester.Semester.Year + 1})", + name = (currentSemester.Semester.Type == sType.winter ? "Зимски" : "Летен") + $" ({currentSemester.Semester.Year}/{currentSemester.Semester.Year + 1})", status = "валиден", serviceNumber = (1000000 + random.Next(10000, 100000)).ToString(), @@ -318,5 +331,5 @@ date = passed.DatePassed.ToString("dd.MM.yyyy"), semester = passed.SemesterSubject.EnrolledSemester?.Semester != null - ? (passed.SemesterSubject.EnrolledSemester.Semester.Type == 0 ? "Зимски" : "Летен") + + ? (passed.SemesterSubject.EnrolledSemester.Semester.Type == sType.winter ? "Зимски" : "Летен") + $" ({passed.SemesterSubject.EnrolledSemester.Semester.Year}/{passed.SemesterSubject.EnrolledSemester.Semester.Year + 1})" : "", Index: now-api/Controllers/WeatherForecastController.cs =================================================================== --- iknow-api/Controllers/WeatherForecastController.cs (revision 10009b9505124c201c365c19c07a9df40b091035) +++ (revision ) @@ -1,33 +1,0 @@ -using Microsoft.AspNetCore.Mvc; - -namespace iknow_api.Controllers -{ - [ApiController] - [Route("[controller]")] - public class WeatherForecastController : ControllerBase - { - private static readonly string[] Summaries = new[] - { - "Freezing", "Bracing", "Chilly", "Cool", "Mild", "Warm", "Balmy", "Hot", "Sweltering", "Scorching" - }; - - private readonly ILogger _logger; - - public WeatherForecastController(ILogger logger) - { - _logger = logger; - } - - [HttpGet(Name = "GetWeatherForecast")] - public IEnumerable Get() - { - return Enumerable.Range(1, 5).Select(index => new WeatherForecast - { - Date = DateOnly.FromDateTime(DateTime.Now.AddDays(index)), - TemperatureC = Random.Shared.Next(-20, 55), - Summary = Summaries[Random.Shared.Next(Summaries.Length)] - }) - .ToArray(); - } - } -} Index: iknow-api/DTOs/AdminDTO.cs =================================================================== --- iknow-api/DTOs/AdminDTO.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) +++ iknow-api/DTOs/AdminDTO.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -0,0 +1,121 @@ +namespace iknow_api.DTOs +{ + // ---------- UC012 subjects ---------- + + public class AdminSubjectDto + { + public int Id { get; set; } + public string? Name { get; set; } + public string? Code { get; set; } + public int AwardedCredits { get; set; } + public int? DependencyCredit { get; set; } + public List Majors { get; set; } = new(); + public List Prerequisites { get; set; } = new(); + /// How many enrolments already contain this subject; it cannot be deleted while this is above zero. + public int EnrolledCount { get; set; } + } + + public class SubjectMajorDto + { + public int MajorId { get; set; } + public string? MajorName { get; set; } + public int MandatorySemester { get; set; } + } + + public class SubjectRefDto + { + public int Id { get; set; } + public string? Name { get; set; } + public string? Code { get; set; } + } + + public class SubjectWriteDto + { + public string? Name { get; set; } + public string? Code { get; set; } + public int AwardedCredits { get; set; } + public int? DependencyCredit { get; set; } + } + + public class MajorLinkDto + { + public int MajorId { get; set; } + public int MandatorySemester { get; set; } + } + + public class PrerequisiteDto + { + public int DependencyId { get; set; } + } + + // ---------- UC013 semesters ---------- + + public class AdminSemesterDto + { + public int Id { get; set; } + public int Year { get; set; } + public string? Type { get; set; } + public string? Name { get; set; } + public int EnrolmentCount { get; set; } + /// Subjects with no professor teaching them in this semester. + public List UncoveredSubjects { get; set; } = new(); + } + + public class SemesterWriteDto + { + public int Year { get; set; } + /// "winter" or "summer". + public string? Type { get; set; } + } + + // ---------- UC014 schedule ---------- + + public class ScheduleDto + { + public int SemesterId { get; set; } + public string? SemesterName { get; set; } + public List Assignments { get; set; } = new(); + public List Load { get; set; } = new(); + public List UncoveredSubjects { get; set; } = new(); + public List AllSubjects { get; set; } = new(); + public List Professors { get; set; } = new(); + } + + public class ScheduleRowDto + { + public int SubjectId { get; set; } + public string? SubjectName { get; set; } + public string? SubjectCode { get; set; } + public int ProfessorId { get; set; } + public string? ProfessorName { get; set; } + } + + public class ProfessorRefDto + { + public int Id { get; set; } + public string? Name { get; set; } + } + + public class ProfessorLoadDto + { + public int ProfessorId { get; set; } + public string? ProfessorName { get; set; } + public int Subjects { get; set; } + } + + public class ScheduleWriteDto + { + public int ProfessorId { get; set; } + public int SemesterId { get; set; } + public int SubjectId { get; set; } + } + + // ---------- shared ---------- + + public class AdminResultDto + { + public bool Ok { get; set; } + public string? Message { get; set; } + public int? Id { get; set; } + } +} Index: iknow-api/DTOs/EnrollmentDTO.cs =================================================================== --- iknow-api/DTOs/EnrollmentDTO.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) +++ iknow-api/DTOs/EnrollmentDTO.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -0,0 +1,57 @@ +namespace iknow_api.DTOs +{ + /// Everything the enrolment form needs to render. + public class EnrollmentOptionsDto + { + /// How many subjects an enrolment must contain. + public int RequiredSubjects { get; set; } + + /// Semesters this student has not enrolled in yet. + public List Semesters { get; set; } = new(); + + public List Majors { get; set; } = new(); + + /// The major from the most recent enrolment, if there is one. + public int? DefaultMajorId { get; set; } + } + + public class SemesterOptionDto + { + public int Id { get; set; } + public string? Name { get; set; } + public int Year { get; set; } + public string? Type { get; set; } + } + + public class MajorOptionDto + { + public int Id { get; set; } + public string? Name { get; set; } + public List Subjects { get; set; } = new(); + } + + public class SubjectOptionDto + { + public int Id { get; set; } + public string? Name { get; set; } + public string? Code { get; set; } + public int Credits { get; set; } + public int MandatorySemester { get; set; } + /// True when the student already passed this subject. + public bool AlreadyPassed { get; set; } + } + + public class EnrollSemesterRequestDto + { + public int SemesterId { get; set; } + public int MajorId { get; set; } + public List SubjectIds { get; set; } = new(); + } + + public class EnrollSemesterResultDto + { + public bool Ok { get; set; } + public string? Message { get; set; } + public int? EnrolledSemesterId { get; set; } + } +} Index: iknow-api/DTOs/ProfDTO.cs =================================================================== --- iknow-api/DTOs/ProfDTO.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) +++ iknow-api/DTOs/ProfDTO.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -0,0 +1,36 @@ +namespace iknow_api.DTOs +{ + /// One subject a professor teaches, with the students enrolled in it. + public class SubjectStudentsDto + { + public int Id { get; set; } + public string? Name { get; set; } + public string? Code { get; set; } + public List Users { get; set; } = new(); + } + + public class EnrolledStudentDto + { + /// users.id - what the grading endpoints expect as StudentId. + public int Id { get; set; } + public string? Name { get; set; } + /// users.index - the number a professor actually recognises. + public string? Index { get; set; } + /// 0 when the subject has not been graded yet. + public int Grade { get; set; } + public string? Semester { get; set; } + } + + public class GradeRequestDto + { + public int StudentId { get; set; } + public int SubjectId { get; set; } + public int Grade { get; set; } + } + + public class GradeResultDto + { + public bool Ok { get; set; } + public string? Message { get; set; } + } +} Index: iknow-api/Data/AppDbContext.cs =================================================================== --- iknow-api/Data/AppDbContext.cs (revision 10009b9505124c201c365c19c07a9df40b091035) +++ iknow-api/Data/AppDbContext.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -4,6 +4,17 @@ namespace iknow_api.Data { + /// + /// The schema is owned by sql/ddl.sql, not by EF migrations. Every entity + /// below is mapped explicitly onto the table and column names in that + /// script; nothing here may rely on EF naming conventions. + /// public class AppDbContext : DbContext { + /// + /// The schema created by sql/schema_creation.sql. Every table and enum + /// type is qualified with it, so nothing depends on search_path. + /// + public const string ProjectSchema = "project"; + public AppDbContext(DbContextOptions options) : base(options) { } @@ -12,5 +23,4 @@ public DbSet ContactInfo { get; set; } public DbSet HighSchool { get; set; } - public DbSet EnrollmentInfo { get; set; } public DbSet RefreshToken { get; set; } public DbSet Subjects { get; set; } @@ -27,169 +37,342 @@ public DbSet ProfessorSubjects { get; set; } - protected override void OnModelCreating(ModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); - modelBuilder.Entity().HasData( - new Major { Id = 1, Name = "PIT" }, - new Major { Id = 2, Name = "SIIS" }, - new Major { Id = 3, Name = "SEIS" }, - new Major { Id = 4, Name = "KN" } - ); - - - // ------------------------- - // DependencySubject (self-referencing many-to-many) - // ------------------------- - modelBuilder.Entity() - .HasKey(ds => new { ds.SubjectId, ds.DependencyId }); - - modelBuilder.Entity() - .HasOne(ds => ds.Subject) - .WithMany(s => s.Dependencies) - .HasForeignKey(ds => ds.SubjectId) - .OnDelete(DeleteBehavior.Restrict); - - modelBuilder.Entity() - .HasOne(ds => ds.Dependency) - .WithMany(s => s.Dependents) - .HasForeignKey(ds => ds.DependencyId) - .OnDelete(DeleteBehavior.Restrict); - - // ------------------------- - // MajorSubjects (many-to-many) - // ------------------------- - modelBuilder.Entity() - .HasKey(ab => new { ab.MajorId, ab.SubjectId }); - - modelBuilder.Entity() - .HasOne(ab => ab.Majors) - .WithMany(a => a.Subjects) - .HasForeignKey(ab => ab.MajorId) - .OnDelete(DeleteBehavior.Cascade); - - modelBuilder.Entity() - .HasOne(ab => ab.Subjects) - .WithMany(b => b.Majors) - .HasForeignKey(ab => ab.SubjectId) - .OnDelete(DeleteBehavior.Cascade); - - // ------------------------- - // UserDocuments (many-to-many) - // ------------------------- - modelBuilder.Entity() - .HasKey(ab => new { ab.UserId, ab.DocumentId }); - - modelBuilder.Entity() - .HasOne(ab => ab.User) - .WithMany(a => a.Documents) - .HasForeignKey(ab => ab.UserId) - .OnDelete(DeleteBehavior.Cascade); - - modelBuilder.Entity() - .HasOne(ab => ab.Documents) - .WithMany(b => b.User) - .HasForeignKey(ab => ab.DocumentId) - .OnDelete(DeleteBehavior.Cascade); - - // ------------------------- - // Payment (user enrollment) - // ------------------------- - modelBuilder.Entity() - .HasKey(p => p.Id); - - modelBuilder.Entity() - .HasOne(p => p.User) - .WithMany(u => u.EnrolledSemesters) - .HasForeignKey(p => p.UserId) - .OnDelete(DeleteBehavior.Cascade); - - modelBuilder.Entity() - .HasOne(p => p.EnrolledSemesters) - .WithMany(es => es.Users) - .HasForeignKey(p => p.EnrollmentInfoId) - .OnDelete(DeleteBehavior.Cascade); - - // ------------------------- - // SemesterSubject (student enrollments and professor assignments) - // ------------------------- - modelBuilder.Entity() - .HasKey(ss => ss.Id); // or composite key if you prefer {UserId, EnrolledSemesterId, SubjectId} - - // Student enrollment - modelBuilder.Entity() - .HasOne(ss => ss.User) - .WithMany(u => u.EnrolledSemesterUserSubjects) - .HasForeignKey(ss => ss.UserId) - .OnDelete(DeleteBehavior.Restrict); - - // Professor teaching - modelBuilder.Entity() - .HasOne(ss => ss.Professor) - .WithMany(u => u.TeachingSubjects) - .HasForeignKey(ss => ss.ProfessorId) - .OnDelete(DeleteBehavior.Restrict); - - // Subject relation - modelBuilder.Entity() - .HasOne(ss => ss.Subject) - .WithMany(s => s.SemesterSubjects) - .HasForeignKey(ss => ss.SubjectId) - .OnDelete(DeleteBehavior.Restrict); - - // EnrolledSemester relation - modelBuilder.Entity() - .HasOne(ss => ss.EnrolledSemester) - .WithMany(es => es.SemesterSubjects) - .HasForeignKey(ss => ss.EnrolledSemesterId) - .OnDelete(DeleteBehavior.Restrict); - - // ------------------------- - // PassedSubject (one-to-one with SemesterSubject) - // ------------------------- - modelBuilder.Entity() - .HasKey(ps => ps.Id); - - modelBuilder.Entity() - .HasOne(ps => ps.SemesterSubject) - .WithOne(ss => ss.PassedSubject) - .HasForeignKey(ps => ps.SemesterSubjectId) - .OnDelete(DeleteBehavior.Cascade); - - // ------------------------- - // ActiveSemesters to EnrolledSemesters (one-to-many) - // ------------------------- - modelBuilder.Entity() - .HasOne(es => es.Semester) - .WithMany(a => a.EnrolledSemesters) - .HasForeignKey(es => es.Id) - .OnDelete(DeleteBehavior.Restrict); - - // ------------------------- - // ProfessorSubjects (many-to-many: Professor -> Semester -> Subject) - // ------------------------- - modelBuilder.Entity() - .HasKey(ps => new { ps.ProfessorId, ps.SemesterId, ps.SubjectId }); - - modelBuilder.Entity() - .HasOne(ps => ps.Professor) - .WithMany() - .HasForeignKey(ps => ps.ProfessorId) - .OnDelete(DeleteBehavior.Cascade); - - modelBuilder.Entity() - .HasOne(ps => ps.Semester) - .WithMany() - .HasForeignKey(ps => ps.SemesterId) - .OnDelete(DeleteBehavior.Cascade); - - modelBuilder.Entity() - .HasOne(ps => ps.Subject) - .WithMany() - .HasForeignKey(ps => ps.SubjectId) - .OnDelete(DeleteBehavior.Cascade); + // The enum types themselves are declared in ddl.sql; this tells EF + // they exist so it does not try to treat the columns as text. + modelBuilder.HasPostgresEnum(ProjectSchema, "user_role"); + modelBuilder.HasPostgresEnum(ProjectSchema, "hs_type"); + modelBuilder.HasPostgresEnum(ProjectSchema, "quota_type"); + modelBuilder.HasPostgresEnum(ProjectSchema, "semester_type"); + modelBuilder.HasPostgresEnum(ProjectSchema, "grade_type"); + + // ------------------------- + // users + // ------------------------- + modelBuilder.Entity(e => + { + e.ToTable("users", ProjectSchema); + e.HasKey(u => u.Id); + e.Property(u => u.Id).HasColumnName("id"); + e.Property(u => u.Name).HasColumnName("name"); + e.Property(u => u.EMBG).HasColumnName("embg"); + e.Property(u => u.Surname).HasColumnName("surname"); + e.Property(u => u.Index).HasColumnName("index"); + e.Property(u => u.Bday).HasColumnName("bday").HasColumnType("timestamp without time zone"); + e.Property(u => u.Email).HasColumnName("email"); + e.Property(u => u.PasswordHash).HasColumnName("password"); + e.Property(u => u.Role).HasColumnName("role"); + e.Property(u => u.Quota).HasColumnName("quota"); + e.Property(u => u.EnrollmentYear).HasColumnName("enrollment_year"); + e.Property(u => u.CreatedAt).HasColumnName("created_at").HasColumnType("timestamp without time zone"); + e.HasIndex(u => u.Email).IsUnique(); + }); + + // ------------------------- + // high_school + // ------------------------- + modelBuilder.Entity(e => + { + e.ToTable("high_school", ProjectSchema); + e.HasKey(h => h.Id); + e.Property(h => h.Id).HasColumnName("id"); + e.Property(h => h.UserId).HasColumnName("user_id"); + e.Property(h => h.GPA).HasColumnName("gpa"); + e.Property(h => h.HighSchoolType).HasColumnName("type"); + e.HasOne(h => h.User) + .WithOne(u => u.HighSchool) + .HasForeignKey(h => h.UserId) + .OnDelete(DeleteBehavior.Restrict); + }); + + // ------------------------- + // contact + // ------------------------- + modelBuilder.Entity(e => + { + e.ToTable("contact", ProjectSchema); + e.HasKey(c => c.Id); + e.Property(c => c.Id).HasColumnName("id"); + e.Property(c => c.UserId).HasColumnName("user_id"); + e.Property(c => c.City).HasColumnName("city"); + e.Property(c => c.Municipality).HasColumnName("municipality"); + e.Property(c => c.Address).HasColumnName("address"); + e.Property(c => c.PhoneNumber).HasColumnName("number"); + e.Property(c => c.MicrosoftEmail).HasColumnName("microsoft_email"); + e.HasOne(c => c.User) + .WithOne(u => u.ContactInfo) + .HasForeignKey(c => c.UserId) + .OnDelete(DeleteBehavior.Restrict); + }); + + // ------------------------- + // major + // ------------------------- + modelBuilder.Entity(e => + { + e.ToTable("major", ProjectSchema); + e.HasKey(m => m.Id); + e.Property(m => m.Id).HasColumnName("id"); + e.Property(m => m.Name).HasColumnName("name"); + }); + + // ------------------------- + // active_semesters + // ------------------------- + modelBuilder.Entity(e => + { + e.ToTable("active_semesters", ProjectSchema); + e.HasKey(a => a.Id); + e.Property(a => a.Id).HasColumnName("id"); + e.Property(a => a.Year).HasColumnName("year"); + e.Property(a => a.Type).HasColumnName("type"); + }); + + // ------------------------- + // subjects + // ------------------------- + modelBuilder.Entity(e => + { + e.ToTable("subjects", ProjectSchema); + e.HasKey(s => s.Id); + e.Property(s => s.Id).HasColumnName("id"); + e.Property(s => s.Name).HasColumnName("name"); + e.Property(s => s.Code).HasColumnName("code"); + e.Property(s => s.AwardedCredits).HasColumnName("awarded_credits"); + e.Property(s => s.DependencyCredit).HasColumnName("dependency_credit"); + }); + + // ------------------------- + // enrolled_semesters + // ------------------------- + modelBuilder.Entity(e => + { + e.ToTable("enrolled_semesters", ProjectSchema); + e.HasKey(es => es.Id); + e.Property(es => es.Id).HasColumnName("id"); + e.Property(es => es.UserId).HasColumnName("user_id"); + e.Property(es => es.QuotaType).HasColumnName("quota"); + e.Property(es => es.MajorId).HasColumnName("major_id"); + e.Property(es => es.Note).HasColumnName("note"); + e.Property(es => es.StudentComment).HasColumnName("student_comment"); + e.Property(es => es.CratedAt).HasColumnName("created_at").HasColumnType("timestamp without time zone"); + e.Property(es => es.LastChange).HasColumnName("last_change").HasColumnType("timestamp without time zone"); + e.Property(es => es.Verified).HasColumnName("completed").HasColumnType("timestamp without time zone"); + e.Property(es => es.SemesterId).HasColumnName("semester_id"); + + e.HasOne(es => es.User) + .WithMany(u => u.Enrolments) + .HasForeignKey(es => es.UserId) + .OnDelete(DeleteBehavior.Restrict); + + // Note: this deliberately keys off SemesterId. An earlier version + // pointed the foreign key at es.Id, which silently tied every + // enrolment to the semester that happened to share its id. + e.HasOne(es => es.Semester) + .WithMany(a => a.EnrolledSemesters) + .HasForeignKey(es => es.SemesterId) + .OnDelete(DeleteBehavior.Restrict); + + e.HasOne(es => es.Major) + .WithMany() + .HasForeignKey(es => es.MajorId) + .OnDelete(DeleteBehavior.Restrict); + }); + + // ------------------------- + // semesters_subjects + // ------------------------- + modelBuilder.Entity(e => + { + e.ToTable("semesters_subjects", ProjectSchema); + e.HasKey(ss => ss.Id); + e.Property(ss => ss.Id).HasColumnName("id"); + e.Property(ss => ss.EnrolledSemesterId).HasColumnName("enrolled_semesters_id"); + e.Property(ss => ss.SubjectId).HasColumnName("subjects_id"); + e.Property(ss => ss.ProfessorId).HasColumnName("professor_id"); + e.Property(ss => ss.Signature).HasColumnName("signature"); + + e.HasOne(ss => ss.Professor) + .WithMany(u => u.TeachingSubjects) + .HasForeignKey(ss => ss.ProfessorId) + .OnDelete(DeleteBehavior.Restrict); + + e.HasOne(ss => ss.Subject) + .WithMany(s => s.SemesterSubjects) + .HasForeignKey(ss => ss.SubjectId) + .OnDelete(DeleteBehavior.Restrict); + + e.HasOne(ss => ss.EnrolledSemester) + .WithMany(es => es.SemesterSubjects) + .HasForeignKey(ss => ss.EnrolledSemesterId) + .OnDelete(DeleteBehavior.Restrict); + }); + + // ------------------------- + // passed_subjects + // ------------------------- + modelBuilder.Entity(e => + { + e.ToTable("passed_subjects", ProjectSchema); + e.HasKey(ps => ps.Id); + e.Property(ps => ps.Id).HasColumnName("id"); + e.Property(ps => ps.SemesterSubjectId).HasColumnName("enrolled_id"); + e.Property(ps => ps.Grade).HasColumnName("grade"); + e.Property(ps => ps.DatePassed).HasColumnName("date_passed").HasColumnType("timestamp without time zone"); + + e.HasOne(ps => ps.SemesterSubject) + .WithOne(ss => ss.PassedSubject) + .HasForeignKey(ps => ps.SemesterSubjectId) + .OnDelete(DeleteBehavior.Cascade); + }); + + // ------------------------- + // documents + // ------------------------- + modelBuilder.Entity(e => + { + e.ToTable("documents", ProjectSchema); + e.HasKey(d => d.Id); + e.Property(d => d.Id).HasColumnName("id"); + e.Property(d => d.Name).HasColumnName("type"); + e.Property(d => d.Body).HasColumnName("body"); + e.Property(d => d.Cost).HasColumnName("cost"); + }); + + // ------------------------- + // user_documents + // ------------------------- + modelBuilder.Entity(e => + { + e.ToTable("user_documents", ProjectSchema); + e.HasKey(ud => new { ud.UserId, ud.DocumentId }); + e.Property(ud => ud.UserId).HasColumnName("user_id"); + e.Property(ud => ud.DocumentId).HasColumnName("document_id"); + + e.HasOne(ud => ud.User) + .WithMany(u => u.Documents) + .HasForeignKey(ud => ud.UserId) + .OnDelete(DeleteBehavior.Cascade); + + e.HasOne(ud => ud.Documents) + .WithMany(d => d.User) + .HasForeignKey(ud => ud.DocumentId) + .OnDelete(DeleteBehavior.Cascade); + }); + + // ------------------------- + // token + // ------------------------- + modelBuilder.Entity(e => + { + e.ToTable("token", ProjectSchema); + e.HasKey(t => t.Id); + e.Property(t => t.Id).HasColumnName("id"); + e.Property(t => t.UserId).HasColumnName("user_id"); + e.Property(t => t.Token).HasColumnName("token"); + e.Property(t => t.ExpiresAt).HasColumnName("expires_at").HasColumnType("timestamp without time zone"); + e.Property(t => t.IsValid).HasColumnName("is_valid"); + + e.HasOne(t => t.User) + .WithMany() + .HasForeignKey(t => t.UserId) + .OnDelete(DeleteBehavior.Cascade); + }); + + // ------------------------- + // payment + // ------------------------- + modelBuilder.Entity(e => + { + e.ToTable("payment", ProjectSchema); + e.HasKey(p => p.Id); + e.Property(p => p.Id).HasColumnName("id"); + e.Property(p => p.UserId).HasColumnName("user_id"); + e.Property(p => p.EnrollmentInfoId).HasColumnName("enrollment_id"); + e.Property(p => p.Amount).HasColumnName("amount"); + + e.HasOne(p => p.User) + .WithMany(u => u.Payments) + .HasForeignKey(p => p.UserId) + .OnDelete(DeleteBehavior.Cascade); + + e.HasOne(p => p.EnrolledSemesters) + .WithMany(es => es.Users) + .HasForeignKey(p => p.EnrollmentInfoId) + .OnDelete(DeleteBehavior.Cascade); + }); + + // ------------------------- + // major_subjects + // ------------------------- + modelBuilder.Entity(e => + { + e.ToTable("major_subjects", ProjectSchema); + e.HasKey(ms => new { ms.MajorId, ms.SubjectId }); + e.Property(ms => ms.MajorId).HasColumnName("major_id"); + e.Property(ms => ms.SubjectId).HasColumnName("subject_id"); + e.Property(ms => ms.MandatorySemester).HasColumnName("mandatory_semester"); + + e.HasOne(ms => ms.Majors) + .WithMany(m => m.Subjects) + .HasForeignKey(ms => ms.MajorId) + .OnDelete(DeleteBehavior.Cascade); + + e.HasOne(ms => ms.Subjects) + .WithMany(s => s.Majors) + .HasForeignKey(ms => ms.SubjectId) + .OnDelete(DeleteBehavior.Cascade); + }); + + // ------------------------- + // dependency_subject + // ------------------------- + modelBuilder.Entity(e => + { + e.ToTable("dependency_subject", ProjectSchema); + e.HasKey(ds => new { ds.SubjectId, ds.DependencyId }); + e.Property(ds => ds.SubjectId).HasColumnName("subject_id"); + e.Property(ds => ds.DependencyId).HasColumnName("dependency_id"); + + e.HasOne(ds => ds.Subject) + .WithMany(s => s.Dependencies) + .HasForeignKey(ds => ds.SubjectId) + .OnDelete(DeleteBehavior.Restrict); + + e.HasOne(ds => ds.Dependency) + .WithMany(s => s.Dependents) + .HasForeignKey(ds => ds.DependencyId) + .OnDelete(DeleteBehavior.Restrict); + }); + + // ------------------------- + // professour_subjects (spelling matches the table in ddl.sql) + // ------------------------- + modelBuilder.Entity(e => + { + e.ToTable("professour_subjects", ProjectSchema); + e.HasKey(ps => new { ps.ProfessorId, ps.SemesterId, ps.SubjectId }); + e.Property(ps => ps.ProfessorId).HasColumnName("prof_id"); + e.Property(ps => ps.SemesterId).HasColumnName("active_semester_id"); + e.Property(ps => ps.SubjectId).HasColumnName("subject_id"); + + e.HasOne(ps => ps.Professor) + .WithMany() + .HasForeignKey(ps => ps.ProfessorId) + .OnDelete(DeleteBehavior.Cascade); + + e.HasOne(ps => ps.Semester) + .WithMany() + .HasForeignKey(ps => ps.SemesterId) + .OnDelete(DeleteBehavior.Cascade); + + e.HasOne(ps => ps.Subject) + .WithMany() + .HasForeignKey(ps => ps.SubjectId) + .OnDelete(DeleteBehavior.Cascade); + }); } - } } Index: iknow-api/Data/PgEnumLabels.cs =================================================================== --- iknow-api/Data/PgEnumLabels.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) +++ iknow-api/Data/PgEnumLabels.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -0,0 +1,49 @@ +using iknow_api.Models; +using Npgsql; + +namespace iknow_api.Data +{ + /// + /// Maps CLR enum member names onto the labels declared in sql/ddl.sql. + /// Most labels are just the lowercased member name; the ones that are not + /// (UserRole.Professor -> 'prof', Grade.Ten -> '10') are listed explicitly. + /// + /// The instances are static on purpose. EF Core caches its internal service + /// provider against the DbContext options, and a fresh translator instance + /// on every resolution makes each set of options look different - EF then + /// builds a new provider per resolution and throws + /// ManyServiceProvidersCreatedWarning once it has built twenty. + /// + public sealed class PgEnumLabels : INpgsqlNameTranslator + { + public static readonly PgEnumLabels UserRole = new( + new Dictionary + { + [nameof(Models.UserRole.Admin)] = "admin", + [nameof(Models.UserRole.Professor)] = "prof", + [nameof(Models.UserRole.Student)] = "student", + }); + + public static readonly PgEnumLabels Grade = new( + new Dictionary + { + [nameof(Models.Grade.Six)] = "6", + [nameof(Models.Grade.Seven)] = "7", + [nameof(Models.Grade.Eight)] = "8", + [nameof(Models.Grade.Nine)] = "9", + [nameof(Models.Grade.Ten)] = "10", + }); + + /// Lowercases the member name, which is all the other enums need. + public static readonly PgEnumLabels Lowercase = new(new Dictionary()); + + private readonly IReadOnlyDictionary _labels; + + private PgEnumLabels(IReadOnlyDictionary labels) => _labels = labels; + + public string TranslateTypeName(string clrName) => clrName; + + public string TranslateMemberName(string clrName) => + _labels.TryGetValue(clrName, out var label) ? label : clrName.ToLowerInvariant(); + } +} Index: now-api/Migrations/20251216191800_InitialCreate.Designer.cs =================================================================== --- iknow-api/Migrations/20251216191800_InitialCreate.Designer.cs (revision 10009b9505124c201c365c19c07a9df40b091035) +++ (revision ) @@ -1,790 +1,0 @@ -// -using System; -using Microsoft.EntityFrameworkCore; -using Microsoft.EntityFrameworkCore.Infrastructure; -using Microsoft.EntityFrameworkCore.Migrations; -using Microsoft.EntityFrameworkCore.Storage.ValueConversion; -using Npgsql.EntityFrameworkCore.PostgreSQL.Metadata; -using iknow_api.Data; - -#nullable disable - -namespace iknow_api.Migrations -{ - [DbContext(typeof(AppDbContext))] - [Migration("20251216191800_InitialCreate")] - partial class InitialCreate - { - /// - protected override void BuildTargetModel(ModelBuilder modelBuilder) - { -#pragma warning disable 612, 618 - modelBuilder - .HasAnnotation("ProductVersion", "9.0.10") - .HasAnnotation("Relational:MaxIdentifierLength", 63); - - NpgsqlModelBuilderExtensions.UseIdentityByDefaultColumns(modelBuilder); - - modelBuilder.Entity("iknow_api.Models.ActiveSemesters", b => - { - b.Property("Id") - .ValueGeneratedOnAdd() - .HasColumnType("integer"); - - NpgsqlPropertyBuilderExtensions.UseIdentityByDefaultColumn(b.Property("Id")); - - b.Property("Type") - .HasColumnType("integer"); - - b.Property("Year") - .HasColumnType("integer"); - - b.HasKey("Id"); - - b.ToTable("ActiveSemesters"); - }); - - modelBuilder.Entity("iknow_api.Models.ContactInfo", b => - { - b.Property("Id") - .ValueGeneratedOnAdd() - .HasColumnType("integer"); - - NpgsqlPropertyBuilderExtensions.UseIdentityByDefaultColumn(b.Property("Id")); - - b.Property("Address") - .HasColumnType("text"); - - b.Property("City") - .HasColumnType("text"); - - b.Property("MicrosoftEmail") - .HasColumnType("text"); - - b.Property("Municipality") - .HasColumnType("text"); - - b.Property("PhoneNumber") - .HasColumnType("text"); - - b.Property("UserId") - .HasColumnType("integer"); - - b.HasKey("Id"); - - b.HasIndex("UserId") - .IsUnique(); - - b.ToTable("ContactInfo"); - }); - - modelBuilder.Entity("iknow_api.Models.DependencySubject", b => - { - b.Property("SubjectId") - .HasColumnType("integer"); - - b.Property("DependencyId") - .HasColumnType("integer"); - - b.HasKey("SubjectId", "DependencyId"); - - b.HasIndex("DependencyId"); - - b.ToTable("DependencySubjects"); - }); - - modelBuilder.Entity("iknow_api.Models.Documents", b => - { - b.Property("Id") - .ValueGeneratedOnAdd() - .HasColumnType("integer"); - - NpgsqlPropertyBuilderExtensions.UseIdentityByDefaultColumn(b.Property("Id")); - - b.Property("Body") - .HasColumnType("text"); - - b.Property("Cost") - .HasColumnType("integer"); - - b.Property("Name") - .HasColumnType("text"); - - b.HasKey("Id"); - - b.ToTable("Documents"); - }); - - modelBuilder.Entity("iknow_api.Models.EnrolledSemesters", b => - { - b.Property("Id") - .HasColumnType("integer"); - - b.Property("CratedAt") - .HasColumnType("timestamp with time zone"); - - b.Property("LastChange") - .HasColumnType("timestamp with time zone"); - - b.Property("MajorId") - .HasColumnType("integer"); - - b.Property("Note") - .HasColumnType("text"); - - b.Property("QuotaType") - .HasColumnType("integer"); - - b.Property("SemesterId") - .HasColumnType("integer"); - - b.Property("StudentComment") - .HasColumnType("text"); - - b.Property("UserId") - .HasColumnType("integer"); - - b.Property("Verified") - .HasColumnType("timestamp with time zone"); - - b.HasKey("Id"); - - b.HasIndex("MajorId"); - - b.HasIndex("UserId"); - - b.ToTable("EnrolledSemesters"); - }); - - modelBuilder.Entity("iknow_api.Models.EnrollmentInfo", b => - { - b.Property("Id") - .ValueGeneratedOnAdd() - .HasColumnType("integer"); - - NpgsqlPropertyBuilderExtensions.UseIdentityByDefaultColumn(b.Property("Id")); - - b.Property("EnrollmentYear") - .HasColumnType("integer"); - - b.Property("MajorId") - .HasColumnType("integer"); - - b.Property("Quota") - .HasColumnType("integer"); - - b.Property("StudyStatus") - .HasColumnType("text"); - - b.Property("StudyType") - .HasColumnType("text"); - - b.Property("UserId") - .HasColumnType("integer"); - - b.HasKey("Id"); - - b.HasIndex("MajorId"); - - b.HasIndex("UserId") - .IsUnique(); - - b.ToTable("EnrollmentInfo"); - }); - - modelBuilder.Entity("iknow_api.Models.HighSchool", b => - { - b.Property("Id") - .ValueGeneratedOnAdd() - .HasColumnType("integer"); - - NpgsqlPropertyBuilderExtensions.UseIdentityByDefaultColumn(b.Property("Id")); - - b.Property("GPA") - .HasColumnType("real"); - - b.Property("HighSchoolType") - .HasColumnType("integer"); - - b.Property("StudyLanguage") - .HasColumnType("text"); - - b.Property("UserId") - .HasColumnType("integer"); - - b.HasKey("Id"); - - b.HasIndex("UserId") - .IsUnique(); - - b.ToTable("HighSchool"); - }); - - modelBuilder.Entity("iknow_api.Models.Major", b => - { - b.Property("Id") - .ValueGeneratedOnAdd() - .HasColumnType("integer"); - - NpgsqlPropertyBuilderExtensions.UseIdentityByDefaultColumn(b.Property("Id")); - - b.Property("Name") - .HasColumnType("text"); - - b.HasKey("Id"); - - b.ToTable("Majors"); - - b.HasData( - new - { - Id = 1, - Name = "PIT" - }, - new - { - Id = 2, - Name = "SIIS" - }, - new - { - Id = 3, - Name = "SEIS" - }, - new - { - Id = 4, - Name = "KN" - }); - }); - - modelBuilder.Entity("iknow_api.Models.MajorSubjects", b => - { - b.Property("MajorId") - .HasColumnType("integer"); - - b.Property("SubjectId") - .HasColumnType("integer"); - - b.Property("Accreditation") - .HasColumnType("integer"); - - b.Property("MandatorySemester") - .HasColumnType("integer"); - - b.HasKey("MajorId", "SubjectId"); - - b.HasIndex("SubjectId"); - - b.ToTable("MajorSubjects"); - }); - - modelBuilder.Entity("iknow_api.Models.PassedSubject", b => - { - b.Property("Id") - .ValueGeneratedOnAdd() - .HasColumnType("integer"); - - NpgsqlPropertyBuilderExtensions.UseIdentityByDefaultColumn(b.Property("Id")); - - b.Property("DatePassed") - .HasColumnType("timestamp with time zone"); - - b.Property("Grade") - .HasColumnType("integer"); - - b.Property("SemesterSubjectId") - .HasColumnType("integer"); - - b.HasKey("Id"); - - b.HasIndex("SemesterSubjectId") - .IsUnique(); - - b.ToTable("PassedSubjects"); - }); - - modelBuilder.Entity("iknow_api.Models.Payment", b => - { - b.Property("Id") - .ValueGeneratedOnAdd() - .HasColumnType("integer"); - - NpgsqlPropertyBuilderExtensions.UseIdentityByDefaultColumn(b.Property("Id")); - - b.Property("EnrollmentInfoId") - .HasColumnType("integer"); - - b.Property("UserId") - .HasColumnType("integer"); - - b.HasKey("Id"); - - b.HasIndex("EnrollmentInfoId"); - - b.HasIndex("UserId"); - - b.ToTable("Payments"); - }); - - modelBuilder.Entity("iknow_api.Models.ProfessorSubjects", b => - { - b.Property("ProfessorId") - .HasColumnType("integer"); - - b.Property("SemesterId") - .HasColumnType("integer"); - - b.Property("SubjectId") - .HasColumnType("integer"); - - b.HasKey("ProfessorId", "SemesterId", "SubjectId"); - - b.HasIndex("SemesterId"); - - b.HasIndex("SubjectId"); - - b.ToTable("ProfessorSubjects"); - }); - - modelBuilder.Entity("iknow_api.Models.RefreshToken", b => - { - b.Property("Id") - .ValueGeneratedOnAdd() - .HasColumnType("integer"); - - NpgsqlPropertyBuilderExtensions.UseIdentityByDefaultColumn(b.Property("Id")); - - b.Property("ExpiresAt") - .HasColumnType("timestamp with time zone"); - - b.Property("IsValid") - .HasColumnType("boolean"); - - b.Property("Token") - .HasColumnType("text"); - - b.Property("UserId") - .HasColumnType("integer"); - - b.HasKey("Id"); - - b.HasIndex("UserId"); - - b.ToTable("RefreshToken"); - }); - - modelBuilder.Entity("iknow_api.Models.SemesterSubject", b => - { - b.Property("Id") - .ValueGeneratedOnAdd() - .HasColumnType("integer"); - - NpgsqlPropertyBuilderExtensions.UseIdentityByDefaultColumn(b.Property("Id")); - - b.Property("EnrolledSemesterId") - .HasColumnType("integer"); - - b.Property("ProfessorId") - .HasColumnType("integer"); - - b.Property("Signature") - .HasColumnType("boolean"); - - b.Property("SubjectId") - .HasColumnType("integer"); - - b.Property("UserId") - .HasColumnType("integer"); - - b.HasKey("Id"); - - b.HasIndex("EnrolledSemesterId"); - - b.HasIndex("ProfessorId"); - - b.HasIndex("SubjectId"); - - b.HasIndex("UserId"); - - b.ToTable("SemesterSubjects"); - }); - - modelBuilder.Entity("iknow_api.Models.Subject", b => - { - b.Property("Id") - .ValueGeneratedOnAdd() - .HasColumnType("integer"); - - NpgsqlPropertyBuilderExtensions.UseIdentityByDefaultColumn(b.Property("Id")); - - b.Property("AwardedCredits") - .HasColumnType("integer"); - - b.Property("Code") - .HasColumnType("text"); - - b.Property("DependencyCredit") - .HasColumnType("integer"); - - b.Property("Name") - .HasColumnType("text"); - - b.HasKey("Id"); - - b.ToTable("Subjects"); - }); - - modelBuilder.Entity("iknow_api.Models.User", b => - { - b.Property("Id") - .ValueGeneratedOnAdd() - .HasColumnType("integer"); - - NpgsqlPropertyBuilderExtensions.UseIdentityByDefaultColumn(b.Property("Id")); - - b.Property("Bday") - .HasColumnType("timestamp with time zone"); - - b.Property("Citizenship") - .HasColumnType("text"); - - b.Property("CreatedAt") - .HasColumnType("timestamp with time zone"); - - b.Property("EMBG") - .HasColumnType("text"); - - b.Property("Email") - .HasColumnType("text"); - - b.Property("Gender") - .HasColumnType("text"); - - b.Property("Index") - .HasColumnType("text"); - - b.Property("MiddleName") - .HasColumnType("text"); - - b.Property("Name") - .HasColumnType("text"); - - b.Property("Nationality") - .HasColumnType("text"); - - b.Property("PasswordHash") - .HasColumnType("text"); - - b.Property("Role") - .HasColumnType("integer"); - - b.Property("Surname") - .HasColumnType("text"); - - b.HasKey("Id"); - - b.ToTable("User"); - }); - - modelBuilder.Entity("iknow_api.Models.UserDocuments", b => - { - b.Property("UserId") - .HasColumnType("integer"); - - b.Property("DocumentId") - .HasColumnType("integer"); - - b.HasKey("UserId", "DocumentId"); - - b.HasIndex("DocumentId"); - - b.ToTable("UserDocuments"); - }); - - modelBuilder.Entity("iknow_api.Models.ContactInfo", b => - { - b.HasOne("iknow_api.Models.User", "User") - .WithOne("ContactInfo") - .HasForeignKey("iknow_api.Models.ContactInfo", "UserId") - .OnDelete(DeleteBehavior.Cascade) - .IsRequired(); - - b.Navigation("User"); - }); - - modelBuilder.Entity("iknow_api.Models.DependencySubject", b => - { - b.HasOne("iknow_api.Models.Subject", "Dependency") - .WithMany("Dependents") - .HasForeignKey("DependencyId") - .OnDelete(DeleteBehavior.Restrict) - .IsRequired(); - - b.HasOne("iknow_api.Models.Subject", "Subject") - .WithMany("Dependencies") - .HasForeignKey("SubjectId") - .OnDelete(DeleteBehavior.Restrict) - .IsRequired(); - - b.Navigation("Dependency"); - - b.Navigation("Subject"); - }); - - modelBuilder.Entity("iknow_api.Models.EnrolledSemesters", b => - { - b.HasOne("iknow_api.Models.ActiveSemesters", "Semester") - .WithMany("EnrolledSemesters") - .HasForeignKey("Id") - .OnDelete(DeleteBehavior.Restrict) - .IsRequired(); - - b.HasOne("iknow_api.Models.Major", "Major") - .WithMany() - .HasForeignKey("MajorId") - .OnDelete(DeleteBehavior.Cascade) - .IsRequired(); - - b.HasOne("iknow_api.Models.User", "User") - .WithMany() - .HasForeignKey("UserId") - .OnDelete(DeleteBehavior.Cascade) - .IsRequired(); - - b.Navigation("Major"); - - b.Navigation("Semester"); - - b.Navigation("User"); - }); - - modelBuilder.Entity("iknow_api.Models.EnrollmentInfo", b => - { - b.HasOne("iknow_api.Models.Major", "Major") - .WithMany() - .HasForeignKey("MajorId") - .OnDelete(DeleteBehavior.Cascade) - .IsRequired(); - - b.HasOne("iknow_api.Models.User", "User") - .WithOne("EnrollmentInfo") - .HasForeignKey("iknow_api.Models.EnrollmentInfo", "UserId") - .OnDelete(DeleteBehavior.Cascade) - .IsRequired(); - - b.Navigation("Major"); - - b.Navigation("User"); - }); - - modelBuilder.Entity("iknow_api.Models.HighSchool", b => - { - b.HasOne("iknow_api.Models.User", "User") - .WithOne("HighSchool") - .HasForeignKey("iknow_api.Models.HighSchool", "UserId") - .OnDelete(DeleteBehavior.Cascade) - .IsRequired(); - - b.Navigation("User"); - }); - - modelBuilder.Entity("iknow_api.Models.MajorSubjects", b => - { - b.HasOne("iknow_api.Models.Major", "Majors") - .WithMany("Subjects") - .HasForeignKey("MajorId") - .OnDelete(DeleteBehavior.Cascade) - .IsRequired(); - - b.HasOne("iknow_api.Models.Subject", "Subjects") - .WithMany("Majors") - .HasForeignKey("SubjectId") - .OnDelete(DeleteBehavior.Cascade) - .IsRequired(); - - b.Navigation("Majors"); - - b.Navigation("Subjects"); - }); - - modelBuilder.Entity("iknow_api.Models.PassedSubject", b => - { - b.HasOne("iknow_api.Models.SemesterSubject", "SemesterSubject") - .WithOne("PassedSubject") - .HasForeignKey("iknow_api.Models.PassedSubject", "SemesterSubjectId") - .OnDelete(DeleteBehavior.Cascade) - .IsRequired(); - - b.Navigation("SemesterSubject"); - }); - - modelBuilder.Entity("iknow_api.Models.Payment", b => - { - b.HasOne("iknow_api.Models.EnrolledSemesters", "EnrolledSemesters") - .WithMany("Users") - .HasForeignKey("EnrollmentInfoId") - .OnDelete(DeleteBehavior.Cascade) - .IsRequired(); - - b.HasOne("iknow_api.Models.User", "User") - .WithMany("EnrolledSemesters") - .HasForeignKey("UserId") - .OnDelete(DeleteBehavior.Cascade) - .IsRequired(); - - b.Navigation("EnrolledSemesters"); - - b.Navigation("User"); - }); - - modelBuilder.Entity("iknow_api.Models.ProfessorSubjects", b => - { - b.HasOne("iknow_api.Models.User", "Professor") - .WithMany() - .HasForeignKey("ProfessorId") - .OnDelete(DeleteBehavior.Cascade) - .IsRequired(); - - b.HasOne("iknow_api.Models.ActiveSemesters", "Semester") - .WithMany() - .HasForeignKey("SemesterId") - .OnDelete(DeleteBehavior.Cascade) - .IsRequired(); - - b.HasOne("iknow_api.Models.Subject", "Subject") - .WithMany() - .HasForeignKey("SubjectId") - .OnDelete(DeleteBehavior.Cascade) - .IsRequired(); - - b.Navigation("Professor"); - - b.Navigation("Semester"); - - b.Navigation("Subject"); - }); - - modelBuilder.Entity("iknow_api.Models.RefreshToken", b => - { - b.HasOne("iknow_api.Models.User", "User") - .WithMany() - .HasForeignKey("UserId") - .OnDelete(DeleteBehavior.Cascade) - .IsRequired(); - - b.Navigation("User"); - }); - - modelBuilder.Entity("iknow_api.Models.SemesterSubject", b => - { - b.HasOne("iknow_api.Models.EnrolledSemesters", "EnrolledSemester") - .WithMany("SemesterSubjects") - .HasForeignKey("EnrolledSemesterId") - .OnDelete(DeleteBehavior.Restrict) - .IsRequired(); - - b.HasOne("iknow_api.Models.User", "Professor") - .WithMany("TeachingSubjects") - .HasForeignKey("ProfessorId") - .OnDelete(DeleteBehavior.Restrict) - .IsRequired(); - - b.HasOne("iknow_api.Models.Subject", "Subject") - .WithMany("SemesterSubjects") - .HasForeignKey("SubjectId") - .OnDelete(DeleteBehavior.Restrict) - .IsRequired(); - - b.HasOne("iknow_api.Models.User", "User") - .WithMany("EnrolledSemesterUserSubjects") - .HasForeignKey("UserId") - .OnDelete(DeleteBehavior.Restrict) - .IsRequired(); - - b.Navigation("EnrolledSemester"); - - b.Navigation("Professor"); - - b.Navigation("Subject"); - - b.Navigation("User"); - }); - - modelBuilder.Entity("iknow_api.Models.UserDocuments", b => - { - b.HasOne("iknow_api.Models.Documents", "Documents") - .WithMany("User") - .HasForeignKey("DocumentId") - .OnDelete(DeleteBehavior.Cascade) - .IsRequired(); - - b.HasOne("iknow_api.Models.User", "User") - .WithMany("Documents") - .HasForeignKey("UserId") - .OnDelete(DeleteBehavior.Cascade) - .IsRequired(); - - b.Navigation("Documents"); - - b.Navigation("User"); - }); - - modelBuilder.Entity("iknow_api.Models.ActiveSemesters", b => - { - b.Navigation("EnrolledSemesters"); - }); - - modelBuilder.Entity("iknow_api.Models.Documents", b => - { - b.Navigation("User"); - }); - - modelBuilder.Entity("iknow_api.Models.EnrolledSemesters", b => - { - b.Navigation("SemesterSubjects"); - - b.Navigation("Users"); - }); - - modelBuilder.Entity("iknow_api.Models.Major", b => - { - b.Navigation("Subjects"); - }); - - modelBuilder.Entity("iknow_api.Models.SemesterSubject", b => - { - b.Navigation("PassedSubject"); - }); - - modelBuilder.Entity("iknow_api.Models.Subject", b => - { - b.Navigation("Dependencies"); - - b.Navigation("Dependents"); - - b.Navigation("Majors"); - - b.Navigation("SemesterSubjects"); - }); - - modelBuilder.Entity("iknow_api.Models.User", b => - { - b.Navigation("ContactInfo"); - - b.Navigation("Documents"); - - b.Navigation("EnrolledSemesterUserSubjects"); - - b.Navigation("EnrolledSemesters"); - - b.Navigation("EnrollmentInfo"); - - b.Navigation("HighSchool"); - - b.Navigation("TeachingSubjects"); - }); -#pragma warning restore 612, 618 - } - } -} Index: now-api/Migrations/20251216191800_InitialCreate.cs =================================================================== --- iknow-api/Migrations/20251216191800_InitialCreate.cs (revision 10009b9505124c201c365c19c07a9df40b091035) +++ (revision ) @@ -1,595 +1,0 @@ -using System; -using Microsoft.EntityFrameworkCore.Migrations; -using Npgsql.EntityFrameworkCore.PostgreSQL.Metadata; - -#nullable disable - -#pragma warning disable CA1814 // Prefer jagged arrays over multidimensional - -namespace iknow_api.Migrations -{ - /// - public partial class InitialCreate : Migration - { - /// - protected override void Up(MigrationBuilder migrationBuilder) - { - migrationBuilder.CreateTable( - name: "ActiveSemesters", - columns: table => new - { - Id = table.Column(type: "integer", nullable: false) - .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn), - Year = table.Column(type: "integer", nullable: false), - Type = table.Column(type: "integer", nullable: false) - }, - constraints: table => - { - table.PrimaryKey("PK_ActiveSemesters", x => x.Id); - }); - - migrationBuilder.CreateTable( - name: "Documents", - columns: table => new - { - Id = table.Column(type: "integer", nullable: false) - .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn), - Name = table.Column(type: "text", nullable: true), - Body = table.Column(type: "text", nullable: true), - Cost = table.Column(type: "integer", nullable: false) - }, - constraints: table => - { - table.PrimaryKey("PK_Documents", x => x.Id); - }); - - migrationBuilder.CreateTable( - name: "Majors", - columns: table => new - { - Id = table.Column(type: "integer", nullable: false) - .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn), - Name = table.Column(type: "text", nullable: true) - }, - constraints: table => - { - table.PrimaryKey("PK_Majors", x => x.Id); - }); - - migrationBuilder.CreateTable( - name: "Subjects", - columns: table => new - { - Id = table.Column(type: "integer", nullable: false) - .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn), - Name = table.Column(type: "text", nullable: true), - Code = table.Column(type: "text", nullable: true), - AwardedCredits = table.Column(type: "integer", nullable: true), - DependencyCredit = table.Column(type: "integer", nullable: true) - }, - constraints: table => - { - table.PrimaryKey("PK_Subjects", x => x.Id); - }); - - migrationBuilder.CreateTable( - name: "User", - columns: table => new - { - Id = table.Column(type: "integer", nullable: false) - .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn), - Name = table.Column(type: "text", nullable: true), - EMBG = table.Column(type: "text", nullable: true), - Surname = table.Column(type: "text", nullable: true), - Index = table.Column(type: "text", nullable: true), - Email = table.Column(type: "text", nullable: true), - PasswordHash = table.Column(type: "text", nullable: true), - MiddleName = table.Column(type: "text", nullable: true), - Gender = table.Column(type: "text", nullable: true), - Nationality = table.Column(type: "text", nullable: true), - Citizenship = table.Column(type: "text", nullable: true), - Bday = table.Column(type: "timestamp with time zone", nullable: false), - CreatedAt = table.Column(type: "timestamp with time zone", nullable: false), - Role = table.Column(type: "integer", nullable: false) - }, - constraints: table => - { - table.PrimaryKey("PK_User", x => x.Id); - }); - - migrationBuilder.CreateTable( - name: "DependencySubjects", - columns: table => new - { - SubjectId = table.Column(type: "integer", nullable: false), - DependencyId = table.Column(type: "integer", nullable: false) - }, - constraints: table => - { - table.PrimaryKey("PK_DependencySubjects", x => new { x.SubjectId, x.DependencyId }); - table.ForeignKey( - name: "FK_DependencySubjects_Subjects_DependencyId", - column: x => x.DependencyId, - principalTable: "Subjects", - principalColumn: "Id", - onDelete: ReferentialAction.Restrict); - table.ForeignKey( - name: "FK_DependencySubjects_Subjects_SubjectId", - column: x => x.SubjectId, - principalTable: "Subjects", - principalColumn: "Id", - onDelete: ReferentialAction.Restrict); - }); - - migrationBuilder.CreateTable( - name: "MajorSubjects", - columns: table => new - { - MajorId = table.Column(type: "integer", nullable: false), - SubjectId = table.Column(type: "integer", nullable: false), - MandatorySemester = table.Column(type: "integer", nullable: false), - Accreditation = table.Column(type: "integer", nullable: false) - }, - constraints: table => - { - table.PrimaryKey("PK_MajorSubjects", x => new { x.MajorId, x.SubjectId }); - table.ForeignKey( - name: "FK_MajorSubjects_Majors_MajorId", - column: x => x.MajorId, - principalTable: "Majors", - principalColumn: "Id", - onDelete: ReferentialAction.Cascade); - table.ForeignKey( - name: "FK_MajorSubjects_Subjects_SubjectId", - column: x => x.SubjectId, - principalTable: "Subjects", - principalColumn: "Id", - onDelete: ReferentialAction.Cascade); - }); - - migrationBuilder.CreateTable( - name: "ContactInfo", - columns: table => new - { - Id = table.Column(type: "integer", nullable: false) - .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn), - City = table.Column(type: "text", nullable: true), - Address = table.Column(type: "text", nullable: true), - Municipality = table.Column(type: "text", nullable: true), - PhoneNumber = table.Column(type: "text", nullable: true), - MicrosoftEmail = table.Column(type: "text", nullable: true), - UserId = table.Column(type: "integer", nullable: false) - }, - constraints: table => - { - table.PrimaryKey("PK_ContactInfo", x => x.Id); - table.ForeignKey( - name: "FK_ContactInfo_User_UserId", - column: x => x.UserId, - principalTable: "User", - principalColumn: "Id", - onDelete: ReferentialAction.Cascade); - }); - - migrationBuilder.CreateTable( - name: "EnrolledSemesters", - columns: table => new - { - Id = table.Column(type: "integer", nullable: false), - UserId = table.Column(type: "integer", nullable: false), - QuotaType = table.Column(type: "integer", nullable: false), - MajorId = table.Column(type: "integer", nullable: false), - StudentComment = table.Column(type: "text", nullable: true), - Note = table.Column(type: "text", nullable: true), - CratedAt = table.Column(type: "timestamp with time zone", nullable: false), - LastChange = table.Column(type: "timestamp with time zone", nullable: false), - Verified = table.Column(type: "timestamp with time zone", nullable: false), - SemesterId = table.Column(type: "integer", nullable: false) - }, - constraints: table => - { - table.PrimaryKey("PK_EnrolledSemesters", x => x.Id); - table.ForeignKey( - name: "FK_EnrolledSemesters_ActiveSemesters_Id", - column: x => x.Id, - principalTable: "ActiveSemesters", - principalColumn: "Id", - onDelete: ReferentialAction.Restrict); - table.ForeignKey( - name: "FK_EnrolledSemesters_Majors_MajorId", - column: x => x.MajorId, - principalTable: "Majors", - principalColumn: "Id", - onDelete: ReferentialAction.Cascade); - table.ForeignKey( - name: "FK_EnrolledSemesters_User_UserId", - column: x => x.UserId, - principalTable: "User", - principalColumn: "Id", - onDelete: ReferentialAction.Cascade); - }); - - migrationBuilder.CreateTable( - name: "EnrollmentInfo", - columns: table => new - { - Id = table.Column(type: "integer", nullable: false) - .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn), - EnrollmentYear = table.Column(type: "integer", nullable: false), - Quota = table.Column(type: "integer", nullable: false), - MajorId = table.Column(type: "integer", nullable: false), - UserId = table.Column(type: "integer", nullable: false), - StudyType = table.Column(type: "text", nullable: true), - StudyStatus = table.Column(type: "text", nullable: true) - }, - constraints: table => - { - table.PrimaryKey("PK_EnrollmentInfo", x => x.Id); - table.ForeignKey( - name: "FK_EnrollmentInfo_Majors_MajorId", - column: x => x.MajorId, - principalTable: "Majors", - principalColumn: "Id", - onDelete: ReferentialAction.Cascade); - table.ForeignKey( - name: "FK_EnrollmentInfo_User_UserId", - column: x => x.UserId, - principalTable: "User", - principalColumn: "Id", - onDelete: ReferentialAction.Cascade); - }); - - migrationBuilder.CreateTable( - name: "HighSchool", - columns: table => new - { - Id = table.Column(type: "integer", nullable: false) - .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn), - GPA = table.Column(type: "real", nullable: false), - HighSchoolType = table.Column(type: "integer", nullable: false), - UserId = table.Column(type: "integer", nullable: false), - StudyLanguage = table.Column(type: "text", nullable: true) - }, - constraints: table => - { - table.PrimaryKey("PK_HighSchool", x => x.Id); - table.ForeignKey( - name: "FK_HighSchool_User_UserId", - column: x => x.UserId, - principalTable: "User", - principalColumn: "Id", - onDelete: ReferentialAction.Cascade); - }); - - migrationBuilder.CreateTable( - name: "ProfessorSubjects", - columns: table => new - { - ProfessorId = table.Column(type: "integer", nullable: false), - SemesterId = table.Column(type: "integer", nullable: false), - SubjectId = table.Column(type: "integer", nullable: false) - }, - constraints: table => - { - table.PrimaryKey("PK_ProfessorSubjects", x => new { x.ProfessorId, x.SemesterId, x.SubjectId }); - table.ForeignKey( - name: "FK_ProfessorSubjects_ActiveSemesters_SemesterId", - column: x => x.SemesterId, - principalTable: "ActiveSemesters", - principalColumn: "Id", - onDelete: ReferentialAction.Cascade); - table.ForeignKey( - name: "FK_ProfessorSubjects_Subjects_SubjectId", - column: x => x.SubjectId, - principalTable: "Subjects", - principalColumn: "Id", - onDelete: ReferentialAction.Cascade); - table.ForeignKey( - name: "FK_ProfessorSubjects_User_ProfessorId", - column: x => x.ProfessorId, - principalTable: "User", - principalColumn: "Id", - onDelete: ReferentialAction.Cascade); - }); - - migrationBuilder.CreateTable( - name: "RefreshToken", - columns: table => new - { - Id = table.Column(type: "integer", nullable: false) - .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn), - UserId = table.Column(type: "integer", nullable: false), - Token = table.Column(type: "text", nullable: true), - ExpiresAt = table.Column(type: "timestamp with time zone", nullable: false), - IsValid = table.Column(type: "boolean", nullable: false) - }, - constraints: table => - { - table.PrimaryKey("PK_RefreshToken", x => x.Id); - table.ForeignKey( - name: "FK_RefreshToken_User_UserId", - column: x => x.UserId, - principalTable: "User", - principalColumn: "Id", - onDelete: ReferentialAction.Cascade); - }); - - migrationBuilder.CreateTable( - name: "UserDocuments", - columns: table => new - { - UserId = table.Column(type: "integer", nullable: false), - DocumentId = table.Column(type: "integer", nullable: false) - }, - constraints: table => - { - table.PrimaryKey("PK_UserDocuments", x => new { x.UserId, x.DocumentId }); - table.ForeignKey( - name: "FK_UserDocuments_Documents_DocumentId", - column: x => x.DocumentId, - principalTable: "Documents", - principalColumn: "Id", - onDelete: ReferentialAction.Cascade); - table.ForeignKey( - name: "FK_UserDocuments_User_UserId", - column: x => x.UserId, - principalTable: "User", - principalColumn: "Id", - onDelete: ReferentialAction.Cascade); - }); - - migrationBuilder.CreateTable( - name: "Payments", - columns: table => new - { - Id = table.Column(type: "integer", nullable: false) - .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn), - UserId = table.Column(type: "integer", nullable: false), - EnrollmentInfoId = table.Column(type: "integer", nullable: false) - }, - constraints: table => - { - table.PrimaryKey("PK_Payments", x => x.Id); - table.ForeignKey( - name: "FK_Payments_EnrolledSemesters_EnrollmentInfoId", - column: x => x.EnrollmentInfoId, - principalTable: "EnrolledSemesters", - principalColumn: "Id", - onDelete: ReferentialAction.Cascade); - table.ForeignKey( - name: "FK_Payments_User_UserId", - column: x => x.UserId, - principalTable: "User", - principalColumn: "Id", - onDelete: ReferentialAction.Cascade); - }); - - migrationBuilder.CreateTable( - name: "SemesterSubjects", - columns: table => new - { - Id = table.Column(type: "integer", nullable: false) - .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn), - UserId = table.Column(type: "integer", nullable: false), - EnrolledSemesterId = table.Column(type: "integer", nullable: false), - SubjectId = table.Column(type: "integer", nullable: false), - ProfessorId = table.Column(type: "integer", nullable: false), - Signature = table.Column(type: "boolean", nullable: false) - }, - constraints: table => - { - table.PrimaryKey("PK_SemesterSubjects", x => x.Id); - table.ForeignKey( - name: "FK_SemesterSubjects_EnrolledSemesters_EnrolledSemesterId", - column: x => x.EnrolledSemesterId, - principalTable: "EnrolledSemesters", - principalColumn: "Id", - onDelete: ReferentialAction.Restrict); - table.ForeignKey( - name: "FK_SemesterSubjects_Subjects_SubjectId", - column: x => x.SubjectId, - principalTable: "Subjects", - principalColumn: "Id", - onDelete: ReferentialAction.Restrict); - table.ForeignKey( - name: "FK_SemesterSubjects_User_ProfessorId", - column: x => x.ProfessorId, - principalTable: "User", - principalColumn: "Id", - onDelete: ReferentialAction.Restrict); - table.ForeignKey( - name: "FK_SemesterSubjects_User_UserId", - column: x => x.UserId, - principalTable: "User", - principalColumn: "Id", - onDelete: ReferentialAction.Restrict); - }); - - migrationBuilder.CreateTable( - name: "PassedSubjects", - columns: table => new - { - Id = table.Column(type: "integer", nullable: false) - .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn), - SemesterSubjectId = table.Column(type: "integer", nullable: false), - Grade = table.Column(type: "integer", nullable: false), - DatePassed = table.Column(type: "timestamp with time zone", nullable: false) - }, - constraints: table => - { - table.PrimaryKey("PK_PassedSubjects", x => x.Id); - table.ForeignKey( - name: "FK_PassedSubjects_SemesterSubjects_SemesterSubjectId", - column: x => x.SemesterSubjectId, - principalTable: "SemesterSubjects", - principalColumn: "Id", - onDelete: ReferentialAction.Cascade); - }); - - migrationBuilder.InsertData( - table: "Majors", - columns: new[] { "Id", "Name" }, - values: new object[,] - { - { 1, "PIT" }, - { 2, "SIIS" }, - { 3, "SEIS" }, - { 4, "KN" } - }); - - migrationBuilder.CreateIndex( - name: "IX_ContactInfo_UserId", - table: "ContactInfo", - column: "UserId", - unique: true); - - migrationBuilder.CreateIndex( - name: "IX_DependencySubjects_DependencyId", - table: "DependencySubjects", - column: "DependencyId"); - - migrationBuilder.CreateIndex( - name: "IX_EnrolledSemesters_MajorId", - table: "EnrolledSemesters", - column: "MajorId"); - - migrationBuilder.CreateIndex( - name: "IX_EnrolledSemesters_UserId", - table: "EnrolledSemesters", - column: "UserId"); - - migrationBuilder.CreateIndex( - name: "IX_EnrollmentInfo_MajorId", - table: "EnrollmentInfo", - column: "MajorId"); - - migrationBuilder.CreateIndex( - name: "IX_EnrollmentInfo_UserId", - table: "EnrollmentInfo", - column: "UserId", - unique: true); - - migrationBuilder.CreateIndex( - name: "IX_HighSchool_UserId", - table: "HighSchool", - column: "UserId", - unique: true); - - migrationBuilder.CreateIndex( - name: "IX_MajorSubjects_SubjectId", - table: "MajorSubjects", - column: "SubjectId"); - - migrationBuilder.CreateIndex( - name: "IX_PassedSubjects_SemesterSubjectId", - table: "PassedSubjects", - column: "SemesterSubjectId", - unique: true); - - migrationBuilder.CreateIndex( - name: "IX_Payments_EnrollmentInfoId", - table: "Payments", - column: "EnrollmentInfoId"); - - migrationBuilder.CreateIndex( - name: "IX_Payments_UserId", - table: "Payments", - column: "UserId"); - - migrationBuilder.CreateIndex( - name: "IX_ProfessorSubjects_SemesterId", - table: "ProfessorSubjects", - column: "SemesterId"); - - migrationBuilder.CreateIndex( - name: "IX_ProfessorSubjects_SubjectId", - table: "ProfessorSubjects", - column: "SubjectId"); - - migrationBuilder.CreateIndex( - name: "IX_RefreshToken_UserId", - table: "RefreshToken", - column: "UserId"); - - migrationBuilder.CreateIndex( - name: "IX_SemesterSubjects_EnrolledSemesterId", - table: "SemesterSubjects", - column: "EnrolledSemesterId"); - - migrationBuilder.CreateIndex( - name: "IX_SemesterSubjects_ProfessorId", - table: "SemesterSubjects", - column: "ProfessorId"); - - migrationBuilder.CreateIndex( - name: "IX_SemesterSubjects_SubjectId", - table: "SemesterSubjects", - column: "SubjectId"); - - migrationBuilder.CreateIndex( - name: "IX_SemesterSubjects_UserId", - table: "SemesterSubjects", - column: "UserId"); - - migrationBuilder.CreateIndex( - name: "IX_UserDocuments_DocumentId", - table: "UserDocuments", - column: "DocumentId"); - } - - /// - protected override void Down(MigrationBuilder migrationBuilder) - { - migrationBuilder.DropTable( - name: "ContactInfo"); - - migrationBuilder.DropTable( - name: "DependencySubjects"); - - migrationBuilder.DropTable( - name: "EnrollmentInfo"); - - migrationBuilder.DropTable( - name: "HighSchool"); - - migrationBuilder.DropTable( - name: "MajorSubjects"); - - migrationBuilder.DropTable( - name: "PassedSubjects"); - - migrationBuilder.DropTable( - name: "Payments"); - - migrationBuilder.DropTable( - name: "ProfessorSubjects"); - - migrationBuilder.DropTable( - name: "RefreshToken"); - - migrationBuilder.DropTable( - name: "UserDocuments"); - - migrationBuilder.DropTable( - name: "SemesterSubjects"); - - migrationBuilder.DropTable( - name: "Documents"); - - migrationBuilder.DropTable( - name: "EnrolledSemesters"); - - migrationBuilder.DropTable( - name: "Subjects"); - - migrationBuilder.DropTable( - name: "ActiveSemesters"); - - migrationBuilder.DropTable( - name: "Majors"); - - migrationBuilder.DropTable( - name: "User"); - } - } -} Index: now-api/Migrations/AppDbContextModelSnapshot.cs =================================================================== --- iknow-api/Migrations/AppDbContextModelSnapshot.cs (revision 10009b9505124c201c365c19c07a9df40b091035) +++ (revision ) @@ -1,787 +1,0 @@ -// -using System; -using Microsoft.EntityFrameworkCore; -using Microsoft.EntityFrameworkCore.Infrastructure; -using Microsoft.EntityFrameworkCore.Storage.ValueConversion; -using Npgsql.EntityFrameworkCore.PostgreSQL.Metadata; -using iknow_api.Data; - -#nullable disable - -namespace iknow_api.Migrations -{ - [DbContext(typeof(AppDbContext))] - partial class AppDbContextModelSnapshot : ModelSnapshot - { - protected override void BuildModel(ModelBuilder modelBuilder) - { -#pragma warning disable 612, 618 - modelBuilder - .HasAnnotation("ProductVersion", "9.0.10") - .HasAnnotation("Relational:MaxIdentifierLength", 63); - - NpgsqlModelBuilderExtensions.UseIdentityByDefaultColumns(modelBuilder); - - modelBuilder.Entity("iknow_api.Models.ActiveSemesters", b => - { - b.Property("Id") - .ValueGeneratedOnAdd() - .HasColumnType("integer"); - - NpgsqlPropertyBuilderExtensions.UseIdentityByDefaultColumn(b.Property("Id")); - - b.Property("Type") - .HasColumnType("integer"); - - b.Property("Year") - .HasColumnType("integer"); - - b.HasKey("Id"); - - b.ToTable("ActiveSemesters"); - }); - - modelBuilder.Entity("iknow_api.Models.ContactInfo", b => - { - b.Property("Id") - .ValueGeneratedOnAdd() - .HasColumnType("integer"); - - NpgsqlPropertyBuilderExtensions.UseIdentityByDefaultColumn(b.Property("Id")); - - b.Property("Address") - .HasColumnType("text"); - - b.Property("City") - .HasColumnType("text"); - - b.Property("MicrosoftEmail") - .HasColumnType("text"); - - b.Property("Municipality") - .HasColumnType("text"); - - b.Property("PhoneNumber") - .HasColumnType("text"); - - b.Property("UserId") - .HasColumnType("integer"); - - b.HasKey("Id"); - - b.HasIndex("UserId") - .IsUnique(); - - b.ToTable("ContactInfo"); - }); - - modelBuilder.Entity("iknow_api.Models.DependencySubject", b => - { - b.Property("SubjectId") - .HasColumnType("integer"); - - b.Property("DependencyId") - .HasColumnType("integer"); - - b.HasKey("SubjectId", "DependencyId"); - - b.HasIndex("DependencyId"); - - b.ToTable("DependencySubjects"); - }); - - modelBuilder.Entity("iknow_api.Models.Documents", b => - { - b.Property("Id") - .ValueGeneratedOnAdd() - .HasColumnType("integer"); - - NpgsqlPropertyBuilderExtensions.UseIdentityByDefaultColumn(b.Property("Id")); - - b.Property("Body") - .HasColumnType("text"); - - b.Property("Cost") - .HasColumnType("integer"); - - b.Property("Name") - .HasColumnType("text"); - - b.HasKey("Id"); - - b.ToTable("Documents"); - }); - - modelBuilder.Entity("iknow_api.Models.EnrolledSemesters", b => - { - b.Property("Id") - .HasColumnType("integer"); - - b.Property("CratedAt") - .HasColumnType("timestamp with time zone"); - - b.Property("LastChange") - .HasColumnType("timestamp with time zone"); - - b.Property("MajorId") - .HasColumnType("integer"); - - b.Property("Note") - .HasColumnType("text"); - - b.Property("QuotaType") - .HasColumnType("integer"); - - b.Property("SemesterId") - .HasColumnType("integer"); - - b.Property("StudentComment") - .HasColumnType("text"); - - b.Property("UserId") - .HasColumnType("integer"); - - b.Property("Verified") - .HasColumnType("timestamp with time zone"); - - b.HasKey("Id"); - - b.HasIndex("MajorId"); - - b.HasIndex("UserId"); - - b.ToTable("EnrolledSemesters"); - }); - - modelBuilder.Entity("iknow_api.Models.EnrollmentInfo", b => - { - b.Property("Id") - .ValueGeneratedOnAdd() - .HasColumnType("integer"); - - NpgsqlPropertyBuilderExtensions.UseIdentityByDefaultColumn(b.Property("Id")); - - b.Property("EnrollmentYear") - .HasColumnType("integer"); - - b.Property("MajorId") - .HasColumnType("integer"); - - b.Property("Quota") - .HasColumnType("integer"); - - b.Property("StudyStatus") - .HasColumnType("text"); - - b.Property("StudyType") - .HasColumnType("text"); - - b.Property("UserId") - .HasColumnType("integer"); - - b.HasKey("Id"); - - b.HasIndex("MajorId"); - - b.HasIndex("UserId") - .IsUnique(); - - b.ToTable("EnrollmentInfo"); - }); - - modelBuilder.Entity("iknow_api.Models.HighSchool", b => - { - b.Property("Id") - .ValueGeneratedOnAdd() - .HasColumnType("integer"); - - NpgsqlPropertyBuilderExtensions.UseIdentityByDefaultColumn(b.Property("Id")); - - b.Property("GPA") - .HasColumnType("real"); - - b.Property("HighSchoolType") - .HasColumnType("integer"); - - b.Property("StudyLanguage") - .HasColumnType("text"); - - b.Property("UserId") - .HasColumnType("integer"); - - b.HasKey("Id"); - - b.HasIndex("UserId") - .IsUnique(); - - b.ToTable("HighSchool"); - }); - - modelBuilder.Entity("iknow_api.Models.Major", b => - { - b.Property("Id") - .ValueGeneratedOnAdd() - .HasColumnType("integer"); - - NpgsqlPropertyBuilderExtensions.UseIdentityByDefaultColumn(b.Property("Id")); - - b.Property("Name") - .HasColumnType("text"); - - b.HasKey("Id"); - - b.ToTable("Majors"); - - b.HasData( - new - { - Id = 1, - Name = "PIT" - }, - new - { - Id = 2, - Name = "SIIS" - }, - new - { - Id = 3, - Name = "SEIS" - }, - new - { - Id = 4, - Name = "KN" - }); - }); - - modelBuilder.Entity("iknow_api.Models.MajorSubjects", b => - { - b.Property("MajorId") - .HasColumnType("integer"); - - b.Property("SubjectId") - .HasColumnType("integer"); - - b.Property("Accreditation") - .HasColumnType("integer"); - - b.Property("MandatorySemester") - .HasColumnType("integer"); - - b.HasKey("MajorId", "SubjectId"); - - b.HasIndex("SubjectId"); - - b.ToTable("MajorSubjects"); - }); - - modelBuilder.Entity("iknow_api.Models.PassedSubject", b => - { - b.Property("Id") - .ValueGeneratedOnAdd() - .HasColumnType("integer"); - - NpgsqlPropertyBuilderExtensions.UseIdentityByDefaultColumn(b.Property("Id")); - - b.Property("DatePassed") - .HasColumnType("timestamp with time zone"); - - b.Property("Grade") - .HasColumnType("integer"); - - b.Property("SemesterSubjectId") - .HasColumnType("integer"); - - b.HasKey("Id"); - - b.HasIndex("SemesterSubjectId") - .IsUnique(); - - b.ToTable("PassedSubjects"); - }); - - modelBuilder.Entity("iknow_api.Models.Payment", b => - { - b.Property("Id") - .ValueGeneratedOnAdd() - .HasColumnType("integer"); - - NpgsqlPropertyBuilderExtensions.UseIdentityByDefaultColumn(b.Property("Id")); - - b.Property("EnrollmentInfoId") - .HasColumnType("integer"); - - b.Property("UserId") - .HasColumnType("integer"); - - b.HasKey("Id"); - - b.HasIndex("EnrollmentInfoId"); - - b.HasIndex("UserId"); - - b.ToTable("Payments"); - }); - - modelBuilder.Entity("iknow_api.Models.ProfessorSubjects", b => - { - b.Property("ProfessorId") - .HasColumnType("integer"); - - b.Property("SemesterId") - .HasColumnType("integer"); - - b.Property("SubjectId") - .HasColumnType("integer"); - - b.HasKey("ProfessorId", "SemesterId", "SubjectId"); - - b.HasIndex("SemesterId"); - - b.HasIndex("SubjectId"); - - b.ToTable("ProfessorSubjects"); - }); - - modelBuilder.Entity("iknow_api.Models.RefreshToken", b => - { - b.Property("Id") - .ValueGeneratedOnAdd() - .HasColumnType("integer"); - - NpgsqlPropertyBuilderExtensions.UseIdentityByDefaultColumn(b.Property("Id")); - - b.Property("ExpiresAt") - .HasColumnType("timestamp with time zone"); - - b.Property("IsValid") - .HasColumnType("boolean"); - - b.Property("Token") - .HasColumnType("text"); - - b.Property("UserId") - .HasColumnType("integer"); - - b.HasKey("Id"); - - b.HasIndex("UserId"); - - b.ToTable("RefreshToken"); - }); - - modelBuilder.Entity("iknow_api.Models.SemesterSubject", b => - { - b.Property("Id") - .ValueGeneratedOnAdd() - .HasColumnType("integer"); - - NpgsqlPropertyBuilderExtensions.UseIdentityByDefaultColumn(b.Property("Id")); - - b.Property("EnrolledSemesterId") - .HasColumnType("integer"); - - b.Property("ProfessorId") - .HasColumnType("integer"); - - b.Property("Signature") - .HasColumnType("boolean"); - - b.Property("SubjectId") - .HasColumnType("integer"); - - b.Property("UserId") - .HasColumnType("integer"); - - b.HasKey("Id"); - - b.HasIndex("EnrolledSemesterId"); - - b.HasIndex("ProfessorId"); - - b.HasIndex("SubjectId"); - - b.HasIndex("UserId"); - - b.ToTable("SemesterSubjects"); - }); - - modelBuilder.Entity("iknow_api.Models.Subject", b => - { - b.Property("Id") - .ValueGeneratedOnAdd() - .HasColumnType("integer"); - - NpgsqlPropertyBuilderExtensions.UseIdentityByDefaultColumn(b.Property("Id")); - - b.Property("AwardedCredits") - .HasColumnType("integer"); - - b.Property("Code") - .HasColumnType("text"); - - b.Property("DependencyCredit") - .HasColumnType("integer"); - - b.Property("Name") - .HasColumnType("text"); - - b.HasKey("Id"); - - b.ToTable("Subjects"); - }); - - modelBuilder.Entity("iknow_api.Models.User", b => - { - b.Property("Id") - .ValueGeneratedOnAdd() - .HasColumnType("integer"); - - NpgsqlPropertyBuilderExtensions.UseIdentityByDefaultColumn(b.Property("Id")); - - b.Property("Bday") - .HasColumnType("timestamp with time zone"); - - b.Property("Citizenship") - .HasColumnType("text"); - - b.Property("CreatedAt") - .HasColumnType("timestamp with time zone"); - - b.Property("EMBG") - .HasColumnType("text"); - - b.Property("Email") - .HasColumnType("text"); - - b.Property("Gender") - .HasColumnType("text"); - - b.Property("Index") - .HasColumnType("text"); - - b.Property("MiddleName") - .HasColumnType("text"); - - b.Property("Name") - .HasColumnType("text"); - - b.Property("Nationality") - .HasColumnType("text"); - - b.Property("PasswordHash") - .HasColumnType("text"); - - b.Property("Role") - .HasColumnType("integer"); - - b.Property("Surname") - .HasColumnType("text"); - - b.HasKey("Id"); - - b.ToTable("User"); - }); - - modelBuilder.Entity("iknow_api.Models.UserDocuments", b => - { - b.Property("UserId") - .HasColumnType("integer"); - - b.Property("DocumentId") - .HasColumnType("integer"); - - b.HasKey("UserId", "DocumentId"); - - b.HasIndex("DocumentId"); - - b.ToTable("UserDocuments"); - }); - - modelBuilder.Entity("iknow_api.Models.ContactInfo", b => - { - b.HasOne("iknow_api.Models.User", "User") - .WithOne("ContactInfo") - .HasForeignKey("iknow_api.Models.ContactInfo", "UserId") - .OnDelete(DeleteBehavior.Cascade) - .IsRequired(); - - b.Navigation("User"); - }); - - modelBuilder.Entity("iknow_api.Models.DependencySubject", b => - { - b.HasOne("iknow_api.Models.Subject", "Dependency") - .WithMany("Dependents") - .HasForeignKey("DependencyId") - .OnDelete(DeleteBehavior.Restrict) - .IsRequired(); - - b.HasOne("iknow_api.Models.Subject", "Subject") - .WithMany("Dependencies") - .HasForeignKey("SubjectId") - .OnDelete(DeleteBehavior.Restrict) - .IsRequired(); - - b.Navigation("Dependency"); - - b.Navigation("Subject"); - }); - - modelBuilder.Entity("iknow_api.Models.EnrolledSemesters", b => - { - b.HasOne("iknow_api.Models.ActiveSemesters", "Semester") - .WithMany("EnrolledSemesters") - .HasForeignKey("Id") - .OnDelete(DeleteBehavior.Restrict) - .IsRequired(); - - b.HasOne("iknow_api.Models.Major", "Major") - .WithMany() - .HasForeignKey("MajorId") - .OnDelete(DeleteBehavior.Cascade) - .IsRequired(); - - b.HasOne("iknow_api.Models.User", "User") - .WithMany() - .HasForeignKey("UserId") - .OnDelete(DeleteBehavior.Cascade) - .IsRequired(); - - b.Navigation("Major"); - - b.Navigation("Semester"); - - b.Navigation("User"); - }); - - modelBuilder.Entity("iknow_api.Models.EnrollmentInfo", b => - { - b.HasOne("iknow_api.Models.Major", "Major") - .WithMany() - .HasForeignKey("MajorId") - .OnDelete(DeleteBehavior.Cascade) - .IsRequired(); - - b.HasOne("iknow_api.Models.User", "User") - .WithOne("EnrollmentInfo") - .HasForeignKey("iknow_api.Models.EnrollmentInfo", "UserId") - .OnDelete(DeleteBehavior.Cascade) - .IsRequired(); - - b.Navigation("Major"); - - b.Navigation("User"); - }); - - modelBuilder.Entity("iknow_api.Models.HighSchool", b => - { - b.HasOne("iknow_api.Models.User", "User") - .WithOne("HighSchool") - .HasForeignKey("iknow_api.Models.HighSchool", "UserId") - .OnDelete(DeleteBehavior.Cascade) - .IsRequired(); - - b.Navigation("User"); - }); - - modelBuilder.Entity("iknow_api.Models.MajorSubjects", b => - { - b.HasOne("iknow_api.Models.Major", "Majors") - .WithMany("Subjects") - .HasForeignKey("MajorId") - .OnDelete(DeleteBehavior.Cascade) - .IsRequired(); - - b.HasOne("iknow_api.Models.Subject", "Subjects") - .WithMany("Majors") - .HasForeignKey("SubjectId") - .OnDelete(DeleteBehavior.Cascade) - .IsRequired(); - - b.Navigation("Majors"); - - b.Navigation("Subjects"); - }); - - modelBuilder.Entity("iknow_api.Models.PassedSubject", b => - { - b.HasOne("iknow_api.Models.SemesterSubject", "SemesterSubject") - .WithOne("PassedSubject") - .HasForeignKey("iknow_api.Models.PassedSubject", "SemesterSubjectId") - .OnDelete(DeleteBehavior.Cascade) - .IsRequired(); - - b.Navigation("SemesterSubject"); - }); - - modelBuilder.Entity("iknow_api.Models.Payment", b => - { - b.HasOne("iknow_api.Models.EnrolledSemesters", "EnrolledSemesters") - .WithMany("Users") - .HasForeignKey("EnrollmentInfoId") - .OnDelete(DeleteBehavior.Cascade) - .IsRequired(); - - b.HasOne("iknow_api.Models.User", "User") - .WithMany("EnrolledSemesters") - .HasForeignKey("UserId") - .OnDelete(DeleteBehavior.Cascade) - .IsRequired(); - - b.Navigation("EnrolledSemesters"); - - b.Navigation("User"); - }); - - modelBuilder.Entity("iknow_api.Models.ProfessorSubjects", b => - { - b.HasOne("iknow_api.Models.User", "Professor") - .WithMany() - .HasForeignKey("ProfessorId") - .OnDelete(DeleteBehavior.Cascade) - .IsRequired(); - - b.HasOne("iknow_api.Models.ActiveSemesters", "Semester") - .WithMany() - .HasForeignKey("SemesterId") - .OnDelete(DeleteBehavior.Cascade) - .IsRequired(); - - b.HasOne("iknow_api.Models.Subject", "Subject") - .WithMany() - .HasForeignKey("SubjectId") - .OnDelete(DeleteBehavior.Cascade) - .IsRequired(); - - b.Navigation("Professor"); - - b.Navigation("Semester"); - - b.Navigation("Subject"); - }); - - modelBuilder.Entity("iknow_api.Models.RefreshToken", b => - { - b.HasOne("iknow_api.Models.User", "User") - .WithMany() - .HasForeignKey("UserId") - .OnDelete(DeleteBehavior.Cascade) - .IsRequired(); - - b.Navigation("User"); - }); - - modelBuilder.Entity("iknow_api.Models.SemesterSubject", b => - { - b.HasOne("iknow_api.Models.EnrolledSemesters", "EnrolledSemester") - .WithMany("SemesterSubjects") - .HasForeignKey("EnrolledSemesterId") - .OnDelete(DeleteBehavior.Restrict) - .IsRequired(); - - b.HasOne("iknow_api.Models.User", "Professor") - .WithMany("TeachingSubjects") - .HasForeignKey("ProfessorId") - .OnDelete(DeleteBehavior.Restrict) - .IsRequired(); - - b.HasOne("iknow_api.Models.Subject", "Subject") - .WithMany("SemesterSubjects") - .HasForeignKey("SubjectId") - .OnDelete(DeleteBehavior.Restrict) - .IsRequired(); - - b.HasOne("iknow_api.Models.User", "User") - .WithMany("EnrolledSemesterUserSubjects") - .HasForeignKey("UserId") - .OnDelete(DeleteBehavior.Restrict) - .IsRequired(); - - b.Navigation("EnrolledSemester"); - - b.Navigation("Professor"); - - b.Navigation("Subject"); - - b.Navigation("User"); - }); - - modelBuilder.Entity("iknow_api.Models.UserDocuments", b => - { - b.HasOne("iknow_api.Models.Documents", "Documents") - .WithMany("User") - .HasForeignKey("DocumentId") - .OnDelete(DeleteBehavior.Cascade) - .IsRequired(); - - b.HasOne("iknow_api.Models.User", "User") - .WithMany("Documents") - .HasForeignKey("UserId") - .OnDelete(DeleteBehavior.Cascade) - .IsRequired(); - - b.Navigation("Documents"); - - b.Navigation("User"); - }); - - modelBuilder.Entity("iknow_api.Models.ActiveSemesters", b => - { - b.Navigation("EnrolledSemesters"); - }); - - modelBuilder.Entity("iknow_api.Models.Documents", b => - { - b.Navigation("User"); - }); - - modelBuilder.Entity("iknow_api.Models.EnrolledSemesters", b => - { - b.Navigation("SemesterSubjects"); - - b.Navigation("Users"); - }); - - modelBuilder.Entity("iknow_api.Models.Major", b => - { - b.Navigation("Subjects"); - }); - - modelBuilder.Entity("iknow_api.Models.SemesterSubject", b => - { - b.Navigation("PassedSubject"); - }); - - modelBuilder.Entity("iknow_api.Models.Subject", b => - { - b.Navigation("Dependencies"); - - b.Navigation("Dependents"); - - b.Navigation("Majors"); - - b.Navigation("SemesterSubjects"); - }); - - modelBuilder.Entity("iknow_api.Models.User", b => - { - b.Navigation("ContactInfo"); - - b.Navigation("Documents"); - - b.Navigation("EnrolledSemesterUserSubjects"); - - b.Navigation("EnrolledSemesters"); - - b.Navigation("EnrollmentInfo"); - - b.Navigation("HighSchool"); - - b.Navigation("TeachingSubjects"); - }); -#pragma warning restore 612, 618 - } - } -} Index: iknow-api/Models/EnrolledSemesters.cs =================================================================== --- iknow-api/Models/EnrolledSemesters.cs (revision 10009b9505124c201c365c19c07a9df40b091035) +++ iknow-api/Models/EnrolledSemesters.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -14,6 +14,6 @@ public string? Note { get; set; } public DateTime CratedAt { get; set; } - public DateTime LastChange { get; set; } - public DateTime Verified { get; set; } + public DateTime? LastChange { get; set; } + public DateTime? Verified { get; set; } public int SemesterId { get; set; } public ActiveSemesters? Semester { get; set; } Index: now-api/Models/EnrollmentInfo.cs =================================================================== --- iknow-api/Models/EnrollmentInfo.cs (revision 10009b9505124c201c365c19c07a9df40b091035) +++ (revision ) @@ -1,24 +1,0 @@ -using System.ComponentModel.DataAnnotations.Schema; -using Microsoft.AspNetCore.SignalR; -using Microsoft.Net.Http.Headers; - -namespace iknow_api.Models -{ - public enum Quota - { - drzavna, privatna, stipendija - } - public class EnrollmentInfo - { - public int Id { get; set; } - public int EnrollmentYear { get; set; } - public Quota Quota { get; set; } - public int MajorId { get; set; } - public Major? Major { get; set; } - public int UserId { get; set; } - public User? User { get; set; } - public string? StudyType { get; set; } - public string? StudyStatus { get; set; } - - } -} Index: iknow-api/Models/HighSchool.cs =================================================================== --- iknow-api/Models/HighSchool.cs (revision 10009b9505124c201c365c19c07a9df40b091035) +++ iknow-api/Models/HighSchool.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -1,4 +1,2 @@ -using System.ComponentModel.DataAnnotations.Schema; - namespace iknow_api.Models { @@ -7,4 +5,5 @@ Strucen, Gimnazija } + public class HighSchool { @@ -14,5 +13,4 @@ public int UserId { get; set; } public User? User { get; set; } - public string? StudyLanguage { get; set; } } } Index: iknow-api/Models/MajorSubjects.cs =================================================================== --- iknow-api/Models/MajorSubjects.cs (revision 10009b9505124c201c365c19c07a9df40b091035) +++ iknow-api/Models/MajorSubjects.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -6,5 +6,4 @@ public int SubjectId { get; set; } public int MandatorySemester { get; set; } - public int Accreditation { get; set; } public Major? Majors { get; set; } public Subject? Subjects { get; set; } Index: iknow-api/Models/Payment.cs =================================================================== --- iknow-api/Models/Payment.cs (revision 10009b9505124c201c365c19c07a9df40b091035) +++ iknow-api/Models/Payment.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -10,4 +10,5 @@ public User? User { get; set; } public int EnrollmentInfoId { get; set; } + public int Amount { get; set; } public EnrolledSemesters? EnrolledSemesters { get; set; } Index: iknow-api/Models/Quota.cs =================================================================== --- iknow-api/Models/Quota.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) +++ iknow-api/Models/Quota.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -0,0 +1,7 @@ +namespace iknow_api.Models +{ + public enum Quota + { + drzavna, privatna, stipendija + } +} Index: iknow-api/Models/SemesterSubjects.cs =================================================================== --- iknow-api/Models/SemesterSubjects.cs (revision 10009b9505124c201c365c19c07a9df40b091035) +++ iknow-api/Models/SemesterSubjects.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -1,17 +1,18 @@ namespace iknow_api.Models { + // The student is reached through EnrolledSemester.UserId; the ER model + // links SemestersSubjects to Users only through the professor. public class SemesterSubject { - public int Id { get; set; } - - public int UserId { get; set; } + public int Id { get; set; } + public int EnrolledSemesterId { get; set; } public int SubjectId { get; set; } public int ProfessorId { get; set; } public bool Signature { get; set; } - public User? User { get; set; } + public EnrolledSemesters? EnrolledSemester { get; set; } public Subject? Subject { get; set; } - public User? Professor { get; set; } + public User? Professor { get; set; } public PassedSubject? PassedSubject { get; set; } } Index: iknow-api/Models/User.cs =================================================================== --- iknow-api/Models/User.cs (revision 10009b9505124c201c365c19c07a9df40b091035) +++ iknow-api/Models/User.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -1,4 +1,2 @@ - - namespace iknow_api.Models { @@ -10,31 +8,32 @@ } + // Attributes follow the Users entity in the ER model exactly. public class User { - internal object Payments; - public int Id { get; set; } public string? Name { get; set; } - public string? EMBG {get; set; } + public string? EMBG { get; set; } public string? Surname { get; set; } public string? Index { get; set; } public string? Email { get; set; } public string? PasswordHash { get; set; } - public string? MiddleName { get; set; } - public string? Gender { get; set; } - public string? Nationality { get; set; } - public string? Citizenship { get; set; } - public DateTime Bday { get; set; } + public DateTime? Bday { get; set; } public DateTime CreatedAt { get; set; } public UserRole Role { get; set; } + public Quota? Quota { get; set; } + public int? EnrollmentYear { get; set; } + public HighSchool? HighSchool { get; set; } public ContactInfo? ContactInfo { get; set; } - public EnrollmentInfo? EnrollmentInfo { get; set; } public ICollection? Documents { get; set; } - public ICollection? EnrolledSemesters { get; set; } - public ICollection? EnrolledSemesterUserSubjects { get; set; } + // Named for the relationship it carries: Users 1 --pays-- N Payment. + public ICollection? Payments { get; set; } + + // Users 1 --submits-- N EnrolledSemesters + public ICollection? Enrolments { get; set; } + + // Users 1 --teaches-- N SemestersSubjects public ICollection? TeachingSubjects { get; set; } - } } Index: iknow-api/Program.cs =================================================================== --- iknow-api/Program.cs (revision 10009b9505124c201c365c19c07a9df40b091035) +++ iknow-api/Program.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -2,4 +2,5 @@ using System.Text.Json.Serialization; using iknow_api.Data; +using iknow_api.Models; using iknow_api.Repositories; using iknow_api.Services; @@ -59,7 +60,21 @@ builder.Services.AddOpenApi(); -// Register DbContext +// Register DbContext. +// The database lives behind the SSH tunnel: tunnel_scripta.cmd maps +// localhost:9999 to the faculty Postgres server. Start it before the API. +// The enum labels come from sql/ddl.sql, so each CLR enum is mapped onto its +// Postgres type by name; PgEnumLabels covers the members whose label is not +// simply the lowercased member name. builder.Services.AddDbContext(options => - options.UseNpgsql(builder.Configuration.GetConnectionString("DefaultConnection"))); + options.UseNpgsql( + builder.Configuration.GetConnectionString("DefaultConnection"), + npgsql => + { + npgsql.MapEnum("user_role", AppDbContext.ProjectSchema, PgEnumLabels.UserRole); + npgsql.MapEnum("hs_type", AppDbContext.ProjectSchema, PgEnumLabels.Lowercase); + npgsql.MapEnum("quota_type", AppDbContext.ProjectSchema, PgEnumLabels.Lowercase); + npgsql.MapEnum("semester_type", AppDbContext.ProjectSchema, PgEnumLabels.Lowercase); + npgsql.MapEnum("grade_type", AppDbContext.ProjectSchema, PgEnumLabels.Grade); + })); // Register your services @@ -71,4 +86,8 @@ builder.Services.AddScoped(); builder.Services.AddScoped(); +builder.Services.AddScoped(); +builder.Services.AddScoped(); +builder.Services.AddScoped(); +builder.Services.AddScoped(); builder.Services.AddAuthorization(); var app = builder.Build(); Index: iknow-api/Repository/Implementations/ProfRepository.cs =================================================================== --- iknow-api/Repository/Implementations/ProfRepository.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) +++ iknow-api/Repository/Implementations/ProfRepository.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -0,0 +1,77 @@ +using iknow_api.Data; +using iknow_api.Models; +using Microsoft.EntityFrameworkCore; + +namespace iknow_api.Repositories +{ + public class ProfRepository : IProfRepository + { + private readonly AppDbContext _context; + + public ProfRepository(AppDbContext context) + { + _context = context; + } + + /// + /// A professor teaches a subject for a given enrolment, so the students + /// are reached through semesters_subjects.professor_id and the student + /// through the enrolment - matching the ER model, which does not link + /// SemestersSubjects to a student directly. + /// + private IQueryable TaughtBy(int professorId) => + _context.SemesterSubjects + .Where(ss => ss.ProfessorId == professorId) + .Include(ss => ss.Subject) + .Include(ss => ss.PassedSubject) + .Include(ss => ss.EnrolledSemester!) + .ThenInclude(es => es.User) + .Include(ss => ss.EnrolledSemester!) + .ThenInclude(es => es.Semester); + + public async Task> GetTaughtSemesterSubjectsAsync(int professorId) => + await TaughtBy(professorId) + .AsNoTracking() + .AsSplitQuery() + .ToListAsync(); + + public async Task FindTaughtSemesterSubjectAsync( + int professorId, int studentId, int subjectId) + { + var candidates = await TaughtBy(professorId) + .Where(ss => ss.SubjectId == subjectId + && ss.EnrolledSemester!.UserId == studentId) + .AsSplitQuery() + .ToListAsync(); + + // Most recent enrolment first. Semesters are labelled Year/Year+1 + // throughout the app, so within one year summer follows winter. + return candidates + .OrderByDescending(ss => ss.EnrolledSemester!.Semester!.Year) + .ThenByDescending(ss => ss.EnrolledSemester!.Semester!.Type == sType.summer) + .FirstOrDefault(); + } + + public async Task GetPassedSubjectAsync(int semesterSubjectId) => + await _context.PassedSubjects + .FirstOrDefaultAsync(ps => ps.SemesterSubjectId == semesterSubjectId); + + public async Task AddPassedSubjectAsync(PassedSubject passed) + { + _context.PassedSubjects.Add(passed); + await _context.SaveChangesAsync(); + } + + public async Task UpdatePassedSubjectAsync(PassedSubject passed) + { + _context.PassedSubjects.Update(passed); + await _context.SaveChangesAsync(); + } + + public async Task RemovePassedSubjectAsync(PassedSubject passed) + { + _context.PassedSubjects.Remove(passed); + await _context.SaveChangesAsync(); + } + } +} Index: iknow-api/Repository/Implementations/RefreshTokenRepository.cs =================================================================== --- iknow-api/Repository/Implementations/RefreshTokenRepository.cs (revision 10009b9505124c201c365c19c07a9df40b091035) +++ iknow-api/Repository/Implementations/RefreshTokenRepository.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -41,5 +41,5 @@ rt.Token == refreshTokenDto.token && rt.IsValid && - rt.ExpiresAt > DateTime.UtcNow); + rt.ExpiresAt > DateTime.SpecifyKind(DateTime.UtcNow, DateTimeKind.Unspecified)); return token != null; Index: iknow-api/Repository/Implementations/UserRepository.cs =================================================================== --- iknow-api/Repository/Implementations/UserRepository.cs (revision 10009b9505124c201c365c19c07a9df40b091035) +++ iknow-api/Repository/Implementations/UserRepository.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -28,6 +28,12 @@ return await _context.User .Include(u => u.ContactInfo) - .Include(u => u.EnrollmentInfo) .Include(u => u.HighSchool) + // The study programme is reached through the enrolment, since + // Major hangs off EnrolledSemesters rather than off Users. + .Include(u => u.Enrolments!) + .ThenInclude(es => es.Major) + .Include(u => u.Enrolments!) + .ThenInclude(es => es.Semester) + .AsSplitQuery() .FirstOrDefaultAsync(u => u.Id == id); } @@ -48,9 +54,4 @@ await _context.SaveChangesAsync(); } - public async Task AddEnrollmentAsync(EnrollmentInfo enrollment) - { - _context.EnrollmentInfo.Add(enrollment); - await _context.SaveChangesAsync(); - } public async Task AddHighSchoolAsync(HighSchool highSchool) { @@ -89,5 +90,7 @@ .Include(ps => ps.SemesterSubject) .ThenInclude(ss => ss.Professor) - .Where(ps => ps.SemesterSubject.UserId == id) + // The ER model reaches the student through the enrolment, + // not through a user_id on semesters_subjects. + .Where(ps => ps.SemesterSubject.EnrolledSemester.UserId == id) .ToListAsync(); } Index: iknow-api/Repository/Interface/IProfRepository.cs =================================================================== --- iknow-api/Repository/Interface/IProfRepository.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) +++ iknow-api/Repository/Interface/IProfRepository.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -0,0 +1,26 @@ +using iknow_api.Models; + +namespace iknow_api.Repositories +{ + public interface IProfRepository + { + /// + /// Every semester-subject row taught by this professor, with the + /// enrolment, student, subject, semester and grade loaded. + /// + Task> GetTaughtSemesterSubjectsAsync(int professorId); + + /// + /// The semester-subject row this professor teaches for one student and + /// subject. When a student has taken the subject more than once, the + /// most recent enrolment wins. Null when the professor does not teach + /// that student in that subject. + /// + Task FindTaughtSemesterSubjectAsync(int professorId, int studentId, int subjectId); + + Task GetPassedSubjectAsync(int semesterSubjectId); + Task AddPassedSubjectAsync(PassedSubject passed); + Task UpdatePassedSubjectAsync(PassedSubject passed); + Task RemovePassedSubjectAsync(PassedSubject passed); + } +} Index: iknow-api/Repository/Interface/IUserRepository.cs =================================================================== --- iknow-api/Repository/Interface/IUserRepository.cs (revision 10009b9505124c201c365c19c07a9df40b091035) +++ iknow-api/Repository/Interface/IUserRepository.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -1,3 +1,3 @@ -using iknow_api.Models; +using iknow_api.Models; namespace iknow_api.Repositories { @@ -8,5 +8,4 @@ Task AddUserAsync(User user); Task AddContactAsync(ContactInfo contactInfo); - Task AddEnrollmentAsync(EnrollmentInfo enrollment); Task AddHighSchoolAsync(HighSchool highSchool); Task GetUsersCountAsync(); Index: iknow-api/Services/Implementations/AdminService.cs =================================================================== --- iknow-api/Services/Implementations/AdminService.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) +++ iknow-api/Services/Implementations/AdminService.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -0,0 +1,445 @@ +using iknow_api.Data; +using iknow_api.DTOs; +using iknow_api.Models; +using Microsoft.EntityFrameworkCore; + +namespace iknow_api.Services +{ + /// + /// Backs UC012 (subjects), UC013 (active semesters) and UC014 (professor + /// assignments). Every method reports constraint problems as a sentence + /// rather than letting a database exception reach the controller. + /// + public class AdminService : IAdminService + { + private readonly AppDbContext _context; + + public AdminService(AppDbContext context) + { + _context = context; + } + + private static string SemesterName(ActiveSemesters s) => + $"{(s.Type == sType.winter ? "Зимски" : "Летен")}({s.Year}/{s.Year + 1})"; + + // ============================================================ + // UC012 - subjects + // ============================================================ + + public async Task> GetSubjectsAsync() + { + var subjects = await _context.Subjects.AsNoTracking().ToListAsync(); + + var majorLinks = await _context.MajorSubjects.AsNoTracking() + .Include(ms => ms.Majors).ToListAsync(); + + var prerequisites = await _context.DependencySubjects.AsNoTracking().ToListAsync(); + + // A subject that students already took cannot be deleted. + var enrolledCounts = await _context.SemesterSubjects.AsNoTracking() + .GroupBy(ss => ss.SubjectId) + .Select(g => new { SubjectId = g.Key, Count = g.Count() }) + .ToDictionaryAsync(x => x.SubjectId, x => x.Count); + + var byId = subjects.ToDictionary(s => s.Id); + + return subjects + .OrderBy(s => s.Code) + .Select(s => new AdminSubjectDto + { + Id = s.Id, + Name = s.Name, + Code = s.Code, + AwardedCredits = s.AwardedCredits ?? 0, + DependencyCredit = s.DependencyCredit, + EnrolledCount = enrolledCounts.TryGetValue(s.Id, out var c) ? c : 0, + Majors = majorLinks + .Where(ms => ms.SubjectId == s.Id) + .Select(ms => new SubjectMajorDto + { + MajorId = ms.MajorId, + MajorName = ms.Majors?.Name, + MandatorySemester = ms.MandatorySemester + }) + .OrderBy(m => m.MajorName) + .ToList(), + Prerequisites = prerequisites + .Where(d => d.SubjectId == s.Id && byId.ContainsKey(d.DependencyId)) + .Select(d => new SubjectRefDto + { + Id = d.DependencyId, + Name = byId[d.DependencyId].Name, + Code = byId[d.DependencyId].Code + }) + .OrderBy(p => p.Code) + .ToList() + }) + .ToList(); + } + + public async Task CreateSubjectAsync(SubjectWriteDto request) + { + if (string.IsNullOrWhiteSpace(request.Name) || string.IsNullOrWhiteSpace(request.Code)) + { + return Fail("Name and code are required."); + } + if (request.AwardedCredits <= 0) + { + return Fail("Awarded credits must be greater than zero."); + } + // subjects.name and subjects.code are both UNIQUE. + if (await _context.Subjects.AnyAsync(s => s.Name == request.Name)) + { + return Fail($"A subject named '{request.Name}' already exists."); + } + if (await _context.Subjects.AnyAsync(s => s.Code == request.Code)) + { + return Fail($"Subject code '{request.Code}' is already taken."); + } + + var subject = new Subject + { + Name = request.Name, + Code = request.Code, + AwardedCredits = request.AwardedCredits, + DependencyCredit = request.DependencyCredit + }; + _context.Subjects.Add(subject); + await _context.SaveChangesAsync(); + + return Ok($"Subject '{subject.Name}' created.", subject.Id); + } + + public async Task UpdateSubjectAsync(int subjectId, SubjectWriteDto request) + { + var subject = await _context.Subjects.FirstOrDefaultAsync(s => s.Id == subjectId); + if (subject is null) return Fail("Subject not found."); + + if (string.IsNullOrWhiteSpace(request.Name) || string.IsNullOrWhiteSpace(request.Code)) + { + return Fail("Name and code are required."); + } + if (request.AwardedCredits <= 0) + { + return Fail("Awarded credits must be greater than zero."); + } + if (await _context.Subjects.AnyAsync(s => s.Name == request.Name && s.Id != subjectId)) + { + return Fail($"Another subject is already named '{request.Name}'."); + } + if (await _context.Subjects.AnyAsync(s => s.Code == request.Code && s.Id != subjectId)) + { + return Fail($"Subject code '{request.Code}' is already taken."); + } + + subject.Name = request.Name; + subject.Code = request.Code; + subject.AwardedCredits = request.AwardedCredits; + subject.DependencyCredit = request.DependencyCredit; + await _context.SaveChangesAsync(); + + return Ok($"Subject '{subject.Name}' updated.", subject.Id); + } + + public async Task DeleteSubjectAsync(int subjectId) + { + var subject = await _context.Subjects.FirstOrDefaultAsync(s => s.Id == subjectId); + if (subject is null) return Fail("Subject not found."); + + // semesters_subjects references subjects, so a taken subject cannot go. + var taken = await _context.SemesterSubjects.CountAsync(ss => ss.SubjectId == subjectId); + if (taken > 0) + { + return Fail($"Cannot delete: {taken} enrolment(s) already contain this subject."); + } + + await using var transaction = await _context.Database.BeginTransactionAsync(); + try + { + // The mapping rows must go first; their foreign keys would block the delete. + var prereqs = await _context.DependencySubjects + .Where(d => d.SubjectId == subjectId || d.DependencyId == subjectId) + .ToListAsync(); + _context.DependencySubjects.RemoveRange(prereqs); + + var majors = await _context.MajorSubjects + .Where(ms => ms.SubjectId == subjectId).ToListAsync(); + _context.MajorSubjects.RemoveRange(majors); + + var teaching = await _context.ProfessorSubjects + .Where(ps => ps.SubjectId == subjectId).ToListAsync(); + _context.ProfessorSubjects.RemoveRange(teaching); + + _context.Subjects.Remove(subject); + await _context.SaveChangesAsync(); + await transaction.CommitAsync(); + + return Ok($"Subject '{subject.Name}' deleted."); + } + catch + { + await transaction.RollbackAsync(); + throw; + } + } + + public async Task LinkMajorAsync(int subjectId, MajorLinkDto request) + { + if (!await _context.Subjects.AnyAsync(s => s.Id == subjectId)) + return Fail("Subject not found."); + if (!await _context.Majors.AnyAsync(m => m.Id == request.MajorId)) + return Fail("Study programme not found."); + if (request.MandatorySemester < 1) + return Fail("Semester must be 1 or greater."); + + var existing = await _context.MajorSubjects + .FirstOrDefaultAsync(ms => ms.SubjectId == subjectId && ms.MajorId == request.MajorId); + + if (existing is not null) + { + existing.MandatorySemester = request.MandatorySemester; + await _context.SaveChangesAsync(); + return Ok("Study programme mapping updated."); + } + + _context.MajorSubjects.Add(new MajorSubjects + { + SubjectId = subjectId, + MajorId = request.MajorId, + MandatorySemester = request.MandatorySemester + }); + await _context.SaveChangesAsync(); + return Ok("Subject added to the study programme."); + } + + public async Task UnlinkMajorAsync(int subjectId, int majorId) + { + var row = await _context.MajorSubjects + .FirstOrDefaultAsync(ms => ms.SubjectId == subjectId && ms.MajorId == majorId); + if (row is null) return Fail("That subject is not in that study programme."); + + _context.MajorSubjects.Remove(row); + await _context.SaveChangesAsync(); + return Ok("Subject removed from the study programme."); + } + + public async Task AddPrerequisiteAsync(int subjectId, PrerequisiteDto request) + { + if (subjectId == request.DependencyId) + { + // Mirrors CHECK (subject_id <> dependency_id). + return Fail("A subject cannot be its own prerequisite."); + } + if (!await _context.Subjects.AnyAsync(s => s.Id == subjectId)) + return Fail("Subject not found."); + if (!await _context.Subjects.AnyAsync(s => s.Id == request.DependencyId)) + return Fail("Prerequisite subject not found."); + if (await _context.DependencySubjects.AnyAsync( + d => d.SubjectId == subjectId && d.DependencyId == request.DependencyId)) + return Fail("That prerequisite is already set."); + + // Refuse a direct cycle: A requires B while B already requires A. + if (await _context.DependencySubjects.AnyAsync( + d => d.SubjectId == request.DependencyId && d.DependencyId == subjectId)) + return Fail("That would create a circular prerequisite."); + + _context.DependencySubjects.Add(new DependencySubject + { + SubjectId = subjectId, + DependencyId = request.DependencyId + }); + await _context.SaveChangesAsync(); + return Ok("Prerequisite added."); + } + + public async Task RemovePrerequisiteAsync(int subjectId, int dependencyId) + { + var row = await _context.DependencySubjects + .FirstOrDefaultAsync(d => d.SubjectId == subjectId && d.DependencyId == dependencyId); + if (row is null) return Fail("That prerequisite is not set."); + + _context.DependencySubjects.Remove(row); + await _context.SaveChangesAsync(); + return Ok("Prerequisite removed."); + } + + // ============================================================ + // UC013 - active semesters + // ============================================================ + + public async Task> GetSemestersAsync() + { + var semesters = await _context.ActiveSemesters.AsNoTracking().ToListAsync(); + var subjects = await _context.Subjects.AsNoTracking().ToListAsync(); + var teaching = await _context.ProfessorSubjects.AsNoTracking().ToListAsync(); + + var enrolments = await _context.EnrolledSemesters.AsNoTracking() + .GroupBy(es => es.SemesterId) + .Select(g => new { SemesterId = g.Key, Count = g.Count() }) + .ToDictionaryAsync(x => x.SemesterId, x => x.Count); + + return semesters + .OrderByDescending(s => s.Year) + .ThenByDescending(s => s.Type == sType.summer) + .Select(s => + { + var covered = teaching + .Where(t => t.SemesterId == s.Id) + .Select(t => t.SubjectId) + .ToHashSet(); + + return new AdminSemesterDto + { + Id = s.Id, + Year = s.Year, + Type = s.Type.ToString(), + Name = SemesterName(s), + EnrolmentCount = enrolments.TryGetValue(s.Id, out var c) ? c : 0, + UncoveredSubjects = subjects + .Where(sub => !covered.Contains(sub.Id)) + .OrderBy(sub => sub.Code) + .Select(sub => new SubjectRefDto { Id = sub.Id, Name = sub.Name, Code = sub.Code }) + .ToList() + }; + }) + .ToList(); + } + + public async Task CreateSemesterAsync(SemesterWriteDto request) + { + if (!Enum.TryParse(request.Type, ignoreCase: true, out var type)) + { + return Fail("Semester type must be 'winter' or 'summer'."); + } + if (request.Year < 2000 || request.Year > 2100) + { + return Fail("Year looks wrong; expected something between 2000 and 2100."); + } + // active_semesters has UNIQUE (year, type). + if (await _context.ActiveSemesters.AnyAsync(a => a.Year == request.Year && a.Type == type)) + { + return Fail("That semester is already open."); + } + + var semester = new ActiveSemesters { Year = request.Year, Type = type }; + _context.ActiveSemesters.Add(semester); + await _context.SaveChangesAsync(); + + return Ok($"Semester {SemesterName(semester)} opened.", semester.Id); + } + + // ============================================================ + // UC014 - professor assignments + // ============================================================ + + public async Task GetScheduleAsync(int semesterId) + { + var semester = await _context.ActiveSemesters.AsNoTracking() + .FirstOrDefaultAsync(a => a.Id == semesterId); + if (semester is null) return null; + + var subjects = await _context.Subjects.AsNoTracking().ToListAsync(); + var professors = await _context.User.AsNoTracking() + .Where(u => u.Role == Models.UserRole.Professor).ToListAsync(); + var assignments = await _context.ProfessorSubjects.AsNoTracking() + .Where(ps => ps.SemesterId == semesterId).ToListAsync(); + + var subjectById = subjects.ToDictionary(s => s.Id); + var profById = professors.ToDictionary(p => p.Id); + var covered = assignments.Select(a => a.SubjectId).ToHashSet(); + + return new ScheduleDto + { + SemesterId = semester.Id, + SemesterName = SemesterName(semester), + Assignments = assignments + .Where(a => subjectById.ContainsKey(a.SubjectId) && profById.ContainsKey(a.ProfessorId)) + .Select(a => new ScheduleRowDto + { + SubjectId = a.SubjectId, + SubjectName = subjectById[a.SubjectId].Name, + SubjectCode = subjectById[a.SubjectId].Code, + ProfessorId = a.ProfessorId, + ProfessorName = $"{profById[a.ProfessorId].Name} {profById[a.ProfessorId].Surname}".Trim() + }) + .OrderBy(r => r.SubjectCode) + .ToList(), + Load = professors + .Select(p => new ProfessorLoadDto + { + ProfessorId = p.Id, + ProfessorName = $"{p.Name} {p.Surname}".Trim(), + Subjects = assignments.Count(a => a.ProfessorId == p.Id) + }) + .OrderByDescending(l => l.Subjects).ThenBy(l => l.ProfessorName) + .ToList(), + UncoveredSubjects = subjects + .Where(s => !covered.Contains(s.Id)) + .OrderBy(s => s.Code) + .Select(s => new SubjectRefDto { Id = s.Id, Name = s.Name, Code = s.Code }) + .ToList(), + AllSubjects = subjects + .OrderBy(s => s.Code) + .Select(s => new SubjectRefDto { Id = s.Id, Name = s.Name, Code = s.Code }) + .ToList(), + Professors = professors + .OrderBy(p => p.Surname) + .Select(p => new ProfessorRefDto { Id = p.Id, Name = $"{p.Name} {p.Surname}".Trim() }) + .ToList() + }; + } + + public async Task AssignAsync(ScheduleWriteDto request) + { + var professor = await _context.User + .FirstOrDefaultAsync(u => u.Id == request.ProfessorId); + if (professor is null) return Fail("Professor not found."); + if (professor.Role != Models.UserRole.Professor) + return Fail("That user is not a professor."); + if (!await _context.ActiveSemesters.AnyAsync(a => a.Id == request.SemesterId)) + return Fail("Semester not found."); + if (!await _context.Subjects.AnyAsync(s => s.Id == request.SubjectId)) + return Fail("Subject not found."); + + var exists = await _context.ProfessorSubjects.AnyAsync( + ps => ps.ProfessorId == request.ProfessorId + && ps.SemesterId == request.SemesterId + && ps.SubjectId == request.SubjectId); + if (exists) return Ok("That assignment already exists."); + + _context.ProfessorSubjects.Add(new ProfessorSubjects + { + ProfessorId = request.ProfessorId, + SemesterId = request.SemesterId, + SubjectId = request.SubjectId + }); + await _context.SaveChangesAsync(); + return Ok("Professor assigned to the subject."); + } + + public async Task UnassignAsync(ScheduleWriteDto request) + { + var row = await _context.ProfessorSubjects.FirstOrDefaultAsync( + ps => ps.ProfessorId == request.ProfessorId + && ps.SemesterId == request.SemesterId + && ps.SubjectId == request.SubjectId); + if (row is null) return Fail("That assignment does not exist."); + + _context.ProfessorSubjects.Remove(row); + await _context.SaveChangesAsync(); + return Ok("Assignment removed."); + } + + public async Task> GetMajorsAsync() => + await _context.Majors.AsNoTracking() + .OrderBy(m => m.Name) + .Select(m => new SubjectRefDto { Id = m.Id, Name = m.Name, Code = null }) + .ToListAsync(); + + private static AdminResultDto Ok(string message, int? id = null) => + new() { Ok = true, Message = message, Id = id }; + + private static AdminResultDto Fail(string message) => + new() { Ok = false, Message = message }; + } +} Index: iknow-api/Services/Implementations/AuthService.cs =================================================================== --- iknow-api/Services/Implementations/AuthService.cs (revision 10009b9505124c201c365c19c07a9df40b091035) +++ iknow-api/Services/Implementations/AuthService.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -1,3 +1,3 @@ -using System.IdentityModel.Tokens.Jwt; +using System.IdentityModel.Tokens.Jwt; using System.Security.Claims; using System.Text; @@ -51,19 +51,19 @@ try { + // quota and enrollment_year are attributes of Users in the ER + // model, so they are set here rather than on a separate entity. var user = new User { Name = registerDto.Name, - MiddleName = registerDto.MiddleName, Surname = registerDto.Surname, Index = registerDto.Index, Email = registerDto.Email, PasswordHash = BCrypt.Net.BCrypt.HashPassword(registerDto.Password), - Bday = DateTime.SpecifyKind(registerDto.Bday, DateTimeKind.Utc), - CreatedAt = DateTime.UtcNow, + Bday = DateTime.SpecifyKind(registerDto.Bday, DateTimeKind.Unspecified), + CreatedAt = DateTime.SpecifyKind(DateTime.UtcNow, DateTimeKind.Unspecified), Role = (Models.UserRole)registerDto.Role, EMBG = registerDto.EMBG, - Nationality = registerDto.nationality, - Citizenship = registerDto.citizenship, - Gender = registerDto.gender + Quota = (Models.Quota)registerDto.quotaType, + EnrollmentYear = registerDto.enrollmentYear }; @@ -83,14 +83,4 @@ }; - var enrollmentInfo = new EnrollmentInfo - { - UserId = userId, - EnrollmentYear = registerDto.enrollmentYear, - Quota = (Models.Quota)registerDto.quotaType, - MajorId = registerDto.majorType, - StudyStatus = registerDto.studyStatus, - StudyType = registerDto.studyType - }; - var highSchool = new HighSchool { @@ -101,5 +91,4 @@ _context.ContactInfo.Add(contactInfo); - _context.EnrollmentInfo.Add(enrollmentInfo); _context.HighSchool.Add(highSchool); Index: iknow-api/Services/Implementations/EnrollmentService.cs =================================================================== --- iknow-api/Services/Implementations/EnrollmentService.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) +++ iknow-api/Services/Implementations/EnrollmentService.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -0,0 +1,232 @@ +using iknow_api.Data; +using iknow_api.DTOs; +using iknow_api.Models; +using Microsoft.EntityFrameworkCore; + +namespace iknow_api.Services +{ + public class EnrollmentService : IEnrollmentService + { + /// An enrolment is exactly this many subjects. + public const int RequiredSubjects = 5; + + private readonly AppDbContext _context; + + public EnrollmentService(AppDbContext context) + { + _context = context; + } + + public async Task GetOptionsAsync(int studentId) + { + var enrolments = await _context.EnrolledSemesters + .AsNoTracking() + .Include(es => es.Semester) + .Where(es => es.UserId == studentId) + .ToListAsync(); + + var takenSemesterIds = enrolments.Select(es => es.SemesterId).ToHashSet(); + + var available = await _context.ActiveSemesters + .AsNoTracking() + .Where(a => !takenSemesterIds.Contains(a.Id)) + .ToListAsync(); + + // Semesters are labelled Year/Year+1, so within a year winter comes first. + var semesters = available + .OrderBy(a => a.Year) + .ThenBy(a => a.Type == sType.summer) + .Select(a => new SemesterOptionDto + { + Id = a.Id, + Year = a.Year, + Type = a.Type.ToString(), + Name = $"{(a.Type == sType.winter ? "Зимски" : "Летен")}({a.Year}/{a.Year + 1})" + }) + .ToList(); + + var passedSubjectIds = await _context.PassedSubjects + .AsNoTracking() + .Where(ps => ps.SemesterSubject!.EnrolledSemester!.UserId == studentId) + .Select(ps => ps.SemesterSubject!.SubjectId) + .ToListAsync(); + var passed = passedSubjectIds.ToHashSet(); + + var majorRows = await _context.MajorSubjects + .AsNoTracking() + .Include(ms => ms.Majors) + .Include(ms => ms.Subjects) + .ToListAsync(); + + var majors = majorRows + .Where(ms => ms.Majors != null && ms.Subjects != null) + .GroupBy(ms => ms.Majors!.Id) + .Select(g => new MajorOptionDto + { + Id = g.Key, + Name = g.First().Majors!.Name, + Subjects = g + .Select(ms => new SubjectOptionDto + { + Id = ms.Subjects!.Id, + Name = ms.Subjects.Name, + Code = ms.Subjects.Code, + Credits = ms.Subjects.AwardedCredits ?? 0, + MandatorySemester = ms.MandatorySemester, + AlreadyPassed = passed.Contains(ms.Subjects.Id) + }) + .OrderBy(x => x.MandatorySemester) + .ThenBy(x => x.Name) + .ToList() + }) + .OrderBy(m => m.Name) + .ToList(); + + var latest = enrolments + .Where(es => es.Semester != null) + .OrderByDescending(es => es.Semester!.Year) + .ThenByDescending(es => es.Semester!.Type == sType.summer) + .FirstOrDefault(); + + return new EnrollmentOptionsDto + { + RequiredSubjects = RequiredSubjects, + Semesters = semesters, + Majors = majors, + DefaultMajorId = latest?.MajorId + }; + } + + public async Task EnrollAsync( + int studentId, EnrollSemesterRequestDto request) + { + var subjectIds = request.SubjectIds?.Distinct().ToList() ?? new List(); + + if (subjectIds.Count != RequiredSubjects) + { + return Fail($"Pick exactly {RequiredSubjects} different subjects; got {subjectIds.Count}."); + } + + if (!await _context.ActiveSemesters.AnyAsync(a => a.Id == request.SemesterId)) + { + return Fail("That semester does not exist."); + } + + // enrolled_semesters has UNIQUE (user_id, semester_id); check first so + // the student gets a sentence rather than a constraint violation. + if (await _context.EnrolledSemesters.AnyAsync( + es => es.UserId == studentId && es.SemesterId == request.SemesterId)) + { + return Fail("You are already enrolled in that semester."); + } + + if (!await _context.Majors.AnyAsync(m => m.Id == request.MajorId)) + { + return Fail("That study programme does not exist."); + } + + var offered = await _context.MajorSubjects + .Where(ms => ms.MajorId == request.MajorId) + .Select(ms => ms.SubjectId) + .ToListAsync(); + + var notOffered = subjectIds.Except(offered).ToList(); + if (notOffered.Count > 0) + { + return Fail($"Subject(s) {string.Join(", ", notOffered)} are not offered by that study programme."); + } + + // semesters_subjects.professor_id is NOT NULL, so every chosen subject + // needs somebody teaching it that semester. + var teachers = await _context.ProfessorSubjects + .Where(ps => ps.SemesterId == request.SemesterId && subjectIds.Contains(ps.SubjectId)) + .ToListAsync(); + + // A subject can be taught by more than one professor in the same + // semester. Spread students over them instead of always taking the + // lowest id, which would leave the other professor with nobody. + var currentLoad = await _context.SemesterSubjects + .Where(ss => subjectIds.Contains(ss.SubjectId) + && ss.EnrolledSemester!.SemesterId == request.SemesterId) + .GroupBy(ss => new { ss.SubjectId, ss.ProfessorId }) + .Select(g => new { g.Key.SubjectId, g.Key.ProfessorId, Count = g.Count() }) + .ToListAsync(); + + var loadLookup = currentLoad.ToDictionary( + x => (x.SubjectId, x.ProfessorId), x => x.Count); + + var professorBySubject = teachers + .GroupBy(ps => ps.SubjectId) + .ToDictionary( + g => g.Key, + // Fewest students first; professor id only breaks ties, so + // the choice stays deterministic and testable. + g => g.OrderBy(ps => loadLookup.TryGetValue((ps.SubjectId, ps.ProfessorId), out var n) ? n : 0) + .ThenBy(ps => ps.ProfessorId) + .First().ProfessorId); + + var untaught = subjectIds.Where(id => !professorBySubject.ContainsKey(id)).ToList(); + if (untaught.Count > 0) + { + return Fail($"Subject(s) {string.Join(", ", untaught)} are not taught in that semester."); + } + + var student = await _context.User.FirstOrDefaultAsync(u => u.Id == studentId); + if (student is null) + { + return Fail("Student not found."); + } + + var now = DateTime.SpecifyKind(DateTime.UtcNow, DateTimeKind.Unspecified); + + await using var transaction = await _context.Database.BeginTransactionAsync(); + try + { + var enrolment = new EnrolledSemesters + { + UserId = studentId, + SemesterId = request.SemesterId, + MajorId = request.MajorId, + // enrolled_semesters.quota is NOT NULL; fall back to the + // state quota when the student record has none. + QuotaType = student.Quota ?? Models.Quota.drzavna, + CratedAt = now, + LastChange = now, + Verified = null + }; + + _context.EnrolledSemesters.Add(enrolment); + await _context.SaveChangesAsync(); + + foreach (var subjectId in subjectIds) + { + _context.SemesterSubjects.Add(new SemesterSubject + { + EnrolledSemesterId = enrolment.Id, + SubjectId = subjectId, + ProfessorId = professorBySubject[subjectId], + Signature = false + }); + } + + await _context.SaveChangesAsync(); + await transaction.CommitAsync(); + + return new EnrollSemesterResultDto + { + Ok = true, + Message = $"Enrolled with {subjectIds.Count} subjects.", + EnrolledSemesterId = enrolment.Id + }; + } + catch + { + await transaction.RollbackAsync(); + throw; + } + } + + private static EnrollSemesterResultDto Fail(string message) => + new() { Ok = false, Message = message }; + } +} Index: iknow-api/Services/Implementations/ProfService.cs =================================================================== --- iknow-api/Services/Implementations/ProfService.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) +++ iknow-api/Services/Implementations/ProfService.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -0,0 +1,122 @@ +using iknow_api.DTOs; +using iknow_api.Models; +using iknow_api.Repositories; + +namespace iknow_api.Services +{ + public class ProfService : IProfService + { + private readonly IProfRepository _profRepository; + + public ProfService(IProfRepository profRepository) + { + _profRepository = profRepository; + } + + public async Task> GetStudentsBySubjectAsync(int professorId) + { + var taught = await _profRepository.GetTaughtSemesterSubjectsAsync(professorId); + + return taught + .Where(ss => ss.Subject != null && ss.EnrolledSemester?.User != null) + .GroupBy(ss => ss.Subject!.Id) + .Select(group => + { + var subject = group.First().Subject!; + return new SubjectStudentsDto + { + Id = subject.Id, + Name = subject.Name, + Code = subject.Code, + Users = group + .Select(ss => + { + var student = ss.EnrolledSemester!.User!; + var semester = ss.EnrolledSemester.Semester; + return new EnrolledStudentDto + { + Id = student.Id, + Name = $"{student.Name} {student.Surname}".Trim(), + Index = student.Index, + // 0 means "not graded yet"; the enum starts at 6. + Grade = ss.PassedSubject is null ? 0 : (int)ss.PassedSubject.Grade, + Semester = semester is null + ? null + : $"{(semester.Type == sType.winter ? "Зимски" : "Летен")}({semester.Year}/{semester.Year + 1})" + }; + }) + .OrderBy(u => u.Name) + .ToList() + }; + }) + .OrderBy(s => s.Name) + .ToList(); + } + + public async Task SetGradeAsync( + int professorId, GradeRequestDto request, GradeAction action) + { + // grade_type only declares 6..10, so anything else cannot be stored. + if (action != GradeAction.Remove && !Enum.IsDefined(typeof(Grade), request.Grade)) + { + return Fail($"Grade must be between 6 and 10; got {request.Grade}."); + } + + // This also enforces authorisation: the lookup is scoped to the + // subjects this professor actually teaches. + var semesterSubject = await _profRepository.FindTaughtSemesterSubjectAsync( + professorId, request.StudentId, request.SubjectId); + + if (semesterSubject is null) + { + return Fail("You do not teach this student in this subject."); + } + + var existing = await _profRepository.GetPassedSubjectAsync(semesterSubject.Id); + + switch (action) + { + case GradeAction.Add: + if (existing is not null) + { + return Fail("This subject is already graded. Use edit to change the grade."); + } + await _profRepository.AddPassedSubjectAsync(new PassedSubject + { + SemesterSubjectId = semesterSubject.Id, + Grade = (Grade)request.Grade, + // date_passed is `timestamp without time zone`. + DatePassed = DateTime.SpecifyKind(DateTime.UtcNow, DateTimeKind.Unspecified) + }); + return Ok($"Grade {request.Grade} added."); + + case GradeAction.Edit: + if (existing is null) + { + return Fail("This subject has no grade yet. Use add first."); + } + existing.Grade = (Grade)request.Grade; + existing.DatePassed = DateTime.SpecifyKind(DateTime.UtcNow, DateTimeKind.Unspecified); + await _profRepository.UpdatePassedSubjectAsync(existing); + return Ok($"Grade changed to {request.Grade}."); + + case GradeAction.Remove: + if (existing is null) + { + return Fail("This subject has no grade to remove."); + } + await _profRepository.RemovePassedSubjectAsync(existing); + return Ok("Grade removed."); + + default: + return Fail("Unknown action."); + } + } + + private static GradeResultDto Ok(string message) => + new() { Ok = true, Message = message }; + + private static GradeResultDto Fail(string message) => + new() { Ok = false, Message = message }; + } +} Index: iknow-api/Services/Implementations/RefreshTokenService.cs =================================================================== --- iknow-api/Services/Implementations/RefreshTokenService.cs (revision 10009b9505124c201c365c19c07a9df40b091035) +++ iknow-api/Services/Implementations/RefreshTokenService.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -31,5 +31,5 @@ Token = refreshToken, UserId = userid, - ExpiresAt = DateTime.UtcNow.AddMonths(1), + ExpiresAt = DateTime.SpecifyKind(DateTime.UtcNow.AddMonths(1), DateTimeKind.Unspecified), IsValid = true }; Index: iknow-api/Services/Interfaces/IAdminService.cs =================================================================== --- iknow-api/Services/Interfaces/IAdminService.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) +++ iknow-api/Services/Interfaces/IAdminService.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -0,0 +1,28 @@ +using iknow_api.DTOs; + +namespace iknow_api.Services +{ + public interface IAdminService + { + // UC012 - subjects + Task> GetSubjectsAsync(); + Task CreateSubjectAsync(SubjectWriteDto request); + Task UpdateSubjectAsync(int subjectId, SubjectWriteDto request); + Task DeleteSubjectAsync(int subjectId); + Task LinkMajorAsync(int subjectId, MajorLinkDto request); + Task UnlinkMajorAsync(int subjectId, int majorId); + Task AddPrerequisiteAsync(int subjectId, PrerequisiteDto request); + Task RemovePrerequisiteAsync(int subjectId, int dependencyId); + + // UC013 - active semesters + Task> GetSemestersAsync(); + Task CreateSemesterAsync(SemesterWriteDto request); + + // UC014 - professor assignments + Task GetScheduleAsync(int semesterId); + Task AssignAsync(ScheduleWriteDto request); + Task UnassignAsync(ScheduleWriteDto request); + + Task> GetMajorsAsync(); + } +} Index: iknow-api/Services/Interfaces/IEnrollmentService.cs =================================================================== --- iknow-api/Services/Interfaces/IEnrollmentService.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) +++ iknow-api/Services/Interfaces/IEnrollmentService.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -0,0 +1,16 @@ +using iknow_api.DTOs; + +namespace iknow_api.Services +{ + public interface IEnrollmentService + { + /// The semesters, majors and subjects this student may enrol in. + Task GetOptionsAsync(int studentId); + + /// + /// Creates one enrolled_semesters row and its semesters_subjects rows, + /// or returns why it could not. + /// + Task EnrollAsync(int studentId, EnrollSemesterRequestDto request); + } +} Index: iknow-api/Services/Interfaces/IProfService.cs =================================================================== --- iknow-api/Services/Interfaces/IProfService.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) +++ iknow-api/Services/Interfaces/IProfService.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -0,0 +1,18 @@ +using iknow_api.DTOs; + +namespace iknow_api.Services +{ + public enum GradeAction + { + Add, + Edit, + Remove + } + + public interface IProfService + { + Task> GetStudentsBySubjectAsync(int professorId); + + Task SetGradeAsync(int professorId, GradeRequestDto request, GradeAction action); + } +} Index: now-api/WeatherForecast.cs =================================================================== --- iknow-api/WeatherForecast.cs (revision 10009b9505124c201c365c19c07a9df40b091035) +++ (revision ) @@ -1,13 +1,0 @@ -namespace iknow_api -{ - public class WeatherForecast - { - public DateOnly Date { get; set; } - - public int TemperatureC { get; set; } - - public int TemperatureF => 32 + (int)(TemperatureC / 0.5556); - - public string? Summary { get; set; } - } -} Index: iknow-api/appsettings.Development.json.example =================================================================== --- iknow-api/appsettings.Development.json.example (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) +++ iknow-api/appsettings.Development.json.example (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -0,0 +1,14 @@ +{ + "Logging": { + "LogLevel": { + "Default": "Information", + "Microsoft.AspNetCore": "Warning" + } + }, + "ConnectionStrings": { + "DefaultConnection": "Host=localhost;Port=9999;Database=db_202526z_va_prj_know2026;Username=db_202526z_va_prj_iknow2026_owner;Password=YOUR_DB_PASSWORD_HERE;Search Path=project;Include Error Detail=true" + }, + "Jwt": { + "Key": "ANY_LOCAL_SIGNING_KEY_AT_LEAST_32_CHARACTERS_LONG" + } +} Index: iknow-api/appsettings.json =================================================================== --- iknow-api/appsettings.json (revision 10009b9505124c201c365c19c07a9df40b091035) +++ iknow-api/appsettings.json (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -10,9 +10,4 @@ "https://iknow-remaster.vercel.app" ], - "ConnectionStrings": { - "DefaultConnection": "Host=ep-divine-term-aghfotgc-pooler.c-2.eu-central-1.aws.neon.tech; Database=neondb; Username=neondb_owner; Password=npg_SxbBZmtw4I7P; SSL Mode=VerifyFull; Channel Binding=Require;" - }, - "Jwt": { - "Key": "YourSecretKeyHereMustBeAtLeast32CharactersLongForHS256Algorithm" - } + "AllowedHosts": "*" } Index: sql/data_load.sql =================================================================== --- sql/data_load.sql (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) +++ sql/data_load.sql (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -0,0 +1,164 @@ +SET search_path TO project; + +TRUNCATE TABLE + professour_subjects, + dependency_subject, + major_subjects, + payment, + token, + user_documents, + documents, + passed_subjects, + semesters_subjects, + enrolled_semesters, + subjects, + active_semesters, + major, + contact, + high_school, + users +RESTART IDENTITY CASCADE; + +INSERT INTO users (id, name, embg, surname, "index", bday, email, password, role, quota, enrollment_year) VALUES +(1, 'Stefan', '0101999450001', 'Saveski', '233149', '1999-01-01', 'stefan.saveski@students.finki.ukim.mk', '$2a$11$S9QIoRlV/rmkif7CKBjryuHwfLo1wTsXckX9oTduqrdyNWInCqR0S', 'student', 'drzavna', 2023), +(2, 'Boris', '0202999450002', 'Gjorgjievski','233188', '1999-02-02', 'boris.gjorgjievski@students.finki.ukim.mk','$2a$11$S9QIoRlV/rmkif7CKBjryuHwfLo1wTsXckX9oTduqrdyNWInCqR0S', 'student', 'privatna', 2023), +(3, 'Ana', '0303000450003', 'Petrova', '233200', '2000-03-03', 'ana.petrova@students.finki.ukim.mk', '$2a$11$S9QIoRlV/rmkif7CKBjryuHwfLo1wTsXckX9oTduqrdyNWInCqR0S', 'student', 'drzavna', 2023); + +INSERT INTO users (id, name, embg, surname, "index", bday, email, password, role, quota, enrollment_year) VALUES +(4, 'Vangel', '0404198045004', 'Ajanovski', NULL, '1980-04-04', 'vangel.ajanovski@finki.ukim.mk', '$2a$11$S9QIoRlV/rmkif7CKBjryuHwfLo1wTsXckX9oTduqrdyNWInCqR0S', 'prof', NULL, NULL), +(5, 'Marija', '0505198545005', 'Ilievska', NULL, '1985-05-05', 'marija.ilievska@finki.ukim.mk', '$2a$11$S9QIoRlV/rmkif7CKBjryuHwfLo1wTsXckX9oTduqrdyNWInCqR0S', 'prof', NULL, NULL); + +INSERT INTO users (id, name, embg, surname, "index", bday, email, password, role, quota, enrollment_year) VALUES +(6, 'Igor', '0606197545006', 'Adminovski', NULL, '1975-06-06', 'igor.adminovski@finki.ukim.mk', '$2a$11$S9QIoRlV/rmkif7CKBjryuHwfLo1wTsXckX9oTduqrdyNWInCqR0S', 'admin', NULL, NULL); + +INSERT INTO users (id, name, embg, surname, "index", bday, email, password, role, quota, enrollment_year) VALUES +(7, 'Riste', '0707197845007', 'Stojanov', NULL, '1978-07-07', 'riste.stojanov@finki.ukim.mk', '$2a$11$S9QIoRlV/rmkif7CKBjryuHwfLo1wTsXckX9oTduqrdyNWInCqR0S', 'prof', NULL, NULL), +(8, 'Katerina','0808198245008', 'Zdravkova', NULL, '1982-08-08', 'katerina.zdravkova@finki.ukim.mk', '$2a$11$S9QIoRlV/rmkif7CKBjryuHwfLo1wTsXckX9oTduqrdyNWInCqR0S', 'prof', NULL, NULL); + +INSERT INTO high_school (id, user_id, gpa, type) VALUES +(1, 1, 4.85, 'gimnazija'), +(2, 2, 4.60, 'strucen'), +(3, 3, 4.95, 'gimnazija'); + +INSERT INTO contact (id, user_id, city, municipality, address, number, microsoft_email) VALUES +(1, 1, 'Skopje', 'Centar', 'Bul. Partizanski Odredi 15', '070111111', 'stefan.saveski@live.finki.ukim.mk'), +(2, 2, 'Skopje', 'Karpos', 'Ul. Ivan Milutinovic 22', '071222222', 'boris.gjorgjievski@live.finki.ukim.mk'), +(3, 3, 'Tetovo', 'Tetovo', 'Ul. Ilindenska 5', '072333333', NULL); + +INSERT INTO major (id, name) VALUES +(1, 'Software Engineering and Information Systems'), +(2, 'Computer Science and Engineering'); + +INSERT INTO active_semesters (id, year, type) VALUES +(1, 2024, 'winter'), +(2, 2025, 'summer'), +(3, 2025, 'winter'), +(4, 2026, 'summer'), +(5, 2026, 'winter'); + +INSERT INTO subjects (id, name, code, awarded_credits, dependency_credit) VALUES +(1, 'Structured Programming', 'F18L1S001', 6, 0), +(2, 'Object Oriented Programming', 'F18L1S002', 6, 6), +(3, 'Databases', 'F18L2S010', 6, 6), +(4, 'Operating Systems', 'F18L2S011', 6, 0), +(5, 'Web Programming', 'F18L3S020', 6, 12), +(6, 'Algorithms and Data Structures', 'F18L2S012', 6, 6), +(7, 'Computer Networks', 'F18L2S013', 6, 6), +(8, 'Software Engineering', 'F18L3S021', 6, 12), +(9, 'Discrete Mathematics', 'F18L1S003', 6, 0), +(10, 'Calculus', 'F18L1S004', 6, 0), +(11, 'Linear Algebra', 'F18L1S005', 6, 0), +(12, 'Computer Architecture', 'F18L1S006', 6, 0), +(13, 'Artificial Intelligence', 'F18L3S022', 6, 12), +(14, 'Machine Learning', 'F18L4S030', 6, 18), +(15, 'Information Security', 'F18L3S023', 6, 12), +(16, 'Mobile Platforms', 'F18L3S024', 6, 12), +(17, 'Distributed Systems', 'F18L4S031', 6, 18), +(18, 'Human-Computer Interaction', 'F18L2S014', 6, 6), +(19, 'Probability and Statistics', 'F18L2S015', 6, 6), +(20, 'Compiler Design', 'F18L4S032', 6, 18); + +INSERT INTO enrolled_semesters (id, user_id, quota, major_id, note, student_comment, last_change, completed, semester_id) VALUES +(1, 1, 'drzavna', 1, NULL, 'prva godina', '2024-10-01 10:00:00', '2025-02-01 12:00:00', 1), +(2, 1, 'drzavna', 1, NULL, NULL, '2025-03-01 10:00:00', NULL, 2), +(3, 2, 'privatna', 1, NULL, NULL, '2024-10-05 09:30:00', '2025-02-03 11:00:00', 1), +(4, 3, 'drzavna', 2, NULL, NULL, '2024-10-02 08:15:00', NULL, 1); + +INSERT INTO semesters_subjects (id, enrolled_semesters_id, subjects_id, professor_id, signature) VALUES +(1, 1, 1, 4, TRUE), +(2, 1, 4, 5, TRUE), +(3, 2, 2, 4, FALSE), +(4, 3, 1, 4, TRUE), +(5, 4, 1, 4, TRUE), +(6, 4, 3, 5, FALSE); + +INSERT INTO passed_subjects (id, enrolled_id, grade, date_passed) VALUES +(1, 1, '10', '2025-01-25 14:00:00'), +(2, 2, '8', '2025-01-30 11:30:00'), +(3, 4, '9', '2025-01-28 09:45:00'), +(4, 5, '7', '2025-01-29 13:15:00'); + +INSERT INTO documents (id, type, body, cost) VALUES +(1, 'Uverenie za polozeni ispiti', 'Sodrzina na uverenie za polozeni ispiti...', 100), +(2, 'Potvrda za redoven student', 'Sodrzina na potvrda za redoven student...', 50), +(3, 'Diploma', 'Sodrzina na diploma...', 500); + +INSERT INTO user_documents (user_id, document_id) VALUES +(1, 1), +(1, 2), +(2, 2), +(3, 1); + +INSERT INTO token (id, user_id, token, expires_at, is_valid) VALUES +(1, 1, 'tok_abc123_stefan', '2026-08-01 00:00:00', TRUE), +(2, 2, 'tok_def456_boris', '2026-08-01 00:00:00', TRUE), +(3, 1, 'tok_old789_stefan', '2026-01-01 00:00:00', FALSE); + +INSERT INTO payment (id, user_id, enrollment_id, amount) VALUES +(1, 1, 1, 200), +(2, 1, 2, 200), +(3, 2, 3, 400), +(4, 3, 4, 200); + +INSERT INTO major_subjects (major_id, subject_id, mandatory_semester) VALUES +(1, 1, 1), (1, 9, 1), (1, 10, 1), (1, 11, 1), (1, 12, 1), +(1, 2, 2), (1, 6, 2), (1, 18, 2), (1, 19, 2), +(1, 3, 3), (1, 4, 3), (1, 7, 3), +(1, 5, 4), (1, 8, 4), (1, 13, 4), (1, 15, 4), (1, 16, 4), +(1, 14, 5), (1, 17, 5), +(2, 1, 1), (2, 9, 1), (2, 10, 1), (2, 11, 1), (2, 12, 1), +(2, 2, 2), (2, 6, 2), (2, 19, 2), +(2, 3, 3), (2, 4, 3), (2, 7, 3), (2, 18, 3), +(2, 5, 4), (2, 13, 4), (2, 15, 4), +(2, 14, 5), (2, 17, 5), (2, 20, 5); + +INSERT INTO dependency_subject (subject_id, dependency_id) VALUES +(2, 1), +(5, 2), +(3, 1); + +INSERT INTO professour_subjects (prof_id, active_semester_id, subject_id) +SELECT teacher.id, sem.id, subj.id +FROM subjects subj +CROSS JOIN active_semesters sem +CROSS JOIN LATERAL ( + SELECT u.id + FROM users u + WHERE u.role = 'prof' + ORDER BY u.id + OFFSET subj.id % (SELECT count(*) FROM users WHERE role = 'prof') + LIMIT 1 +) AS teacher; + +SELECT setval(pg_get_serial_sequence('users', 'id'), (SELECT max(id) FROM users)); +SELECT setval(pg_get_serial_sequence('high_school', 'id'), (SELECT max(id) FROM high_school)); +SELECT setval(pg_get_serial_sequence('contact', 'id'), (SELECT max(id) FROM contact)); +SELECT setval(pg_get_serial_sequence('major', 'id'), (SELECT max(id) FROM major)); +SELECT setval(pg_get_serial_sequence('active_semesters', 'id'), (SELECT max(id) FROM active_semesters)); +SELECT setval(pg_get_serial_sequence('subjects', 'id'), (SELECT max(id) FROM subjects)); +SELECT setval(pg_get_serial_sequence('enrolled_semesters', 'id'), (SELECT max(id) FROM enrolled_semesters)); +SELECT setval(pg_get_serial_sequence('semesters_subjects', 'id'), (SELECT max(id) FROM semesters_subjects)); +SELECT setval(pg_get_serial_sequence('passed_subjects', 'id'), (SELECT max(id) FROM passed_subjects)); +SELECT setval(pg_get_serial_sequence('documents', 'id'), (SELECT max(id) FROM documents)); +SELECT setval(pg_get_serial_sequence('token', 'id'), (SELECT max(id) FROM token)); +SELECT setval(pg_get_serial_sequence('payment', 'id'), (SELECT max(id) FROM payment)); Index: sql/iknow.dbml =================================================================== --- sql/iknow.dbml (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) +++ sql/iknow.dbml (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -0,0 +1,207 @@ +// IKnow / FINKI - dbdiagram.io (DBML) source +// Generated from schema_creation.sql, which is the source of truth. +// Paste into https://dbdiagram.io to render the relational diagram. + +Enum project.user_role { + student + prof + admin +} + +Enum project.hs_type { + strucen + gimnazija +} + +Enum project.quota_type { + privatna + drzavna + stipendija +} + +Enum project.semester_type { + summer + winter +} + +Enum project.grade_type { + "6" + "7" + "8" + "9" + "10" +} + +Table project.users { + id integer [pk, increment] + name varchar(100) [not null] + embg varchar(13) + surname varchar(100) [not null] + index varchar(20) + bday timestamp + email varchar(150) [not null, unique] + password varchar(255) [not null] + role project.user_role [not null] + quota project.quota_type + enrollment_year integer + created_at timestamp [not null, default: `now()`] +} + +Table project.high_school { + id integer [pk, increment] + user_id integer [not null, unique] + gpa real [not null] + type project.hs_type [not null] +} + +Table project.contact { + id integer [pk, increment] + user_id integer [not null, unique] + city varchar(100) [not null] + municipality varchar(100) [not null] + address varchar(150) [not null] + number varchar(20) [not null] + microsoft_email varchar(150) +} + +Table project.major { + id integer [pk, increment] + name varchar(150) [not null] +} + +Table project.active_semesters { + id integer [pk, increment] + year integer [not null] + type project.semester_type [not null] + + indexes { + (year, type) [unique] + } +} + +Table project.subjects { + id integer [pk, increment] + name varchar(150) [not null, unique] + code varchar(30) [not null, unique] + awarded_credits integer [not null] + dependency_credit integer +} + +Table project.enrolled_semesters { + id integer [pk, increment] + user_id integer [not null] + quota project.quota_type [not null] + major_id integer [not null] + note varchar(255) + student_comment varchar(255) + created_at timestamp [not null, default: `now()`] + last_change timestamp + completed timestamp + semester_id integer [not null] + + indexes { + (user_id, semester_id) [unique] + } +} + +Table project.semesters_subjects { + id integer [pk, increment] + enrolled_semesters_id integer [not null] + subjects_id integer [not null] + professor_id integer [not null] + signature boolean [not null, default: false] + + indexes { + (enrolled_semesters_id, subjects_id) [unique] + } +} + +Table project.passed_subjects { + id integer [pk, increment] + enrolled_id integer [not null, unique] + grade project.grade_type [not null] + date_passed timestamp [not null] +} + +Table project.documents { + id integer [pk, increment] + type varchar(100) [not null] + body text [not null] + cost integer [not null] +} + +Table project.user_documents { + user_id integer [not null] + document_id integer [not null] + + indexes { + (user_id, document_id) [pk] + } +} + +Table project.token { + id integer [pk, increment] + user_id integer [not null] + token varchar(255) [not null] + expires_at timestamp [not null] + is_valid boolean [not null, default: true] +} + +Table project.payment { + id integer [pk, increment] + user_id integer [not null] + enrollment_id integer [not null] + amount integer [not null] +} + +Table project.major_subjects { + major_id integer [not null] + subject_id integer [not null] + mandatory_semester integer [not null] + + indexes { + (major_id, subject_id) [pk] + } +} + +Table project.dependency_subject { + subject_id integer [not null] + dependency_id integer [not null] + + indexes { + (subject_id, dependency_id) [pk] + } +} + +Table project.professour_subjects { + prof_id integer [not null] + active_semester_id integer [not null] + subject_id integer [not null] + + indexes { + (prof_id, active_semester_id, subject_id) [pk] + } +} + +// ---------- Relationships ---------- +Ref: project.high_school.user_id - project.users.id // has_hs (1:1) +Ref: project.contact.user_id - project.users.id // has_contact (1:1) +Ref: project.token.user_id > project.users.id // has_token +Ref: project.payment.user_id > project.users.id // pays +Ref: project.payment.enrollment_id > project.enrolled_semesters.id // for_enrollment +Ref: project.enrolled_semesters.user_id > project.users.id // submits +Ref: project.enrolled_semesters.major_id > project.major.id // for_major +Ref: project.enrolled_semesters.semester_id > project.active_semesters.id // during +Ref: project.semesters_subjects.enrolled_semesters_id > project.enrolled_semesters.id // contains +Ref: project.semesters_subjects.subjects_id > project.subjects.id // taken_in +Ref: project.semesters_subjects.professor_id > project.users.id // teaches +Ref: project.passed_subjects.enrolled_id - project.semesters_subjects.id // results_in (1:1) +Ref: project.user_documents.user_id > project.users.id // owns +Ref: project.user_documents.document_id > project.documents.id // owns +Ref: project.major_subjects.major_id > project.major.id // includes +Ref: project.major_subjects.subject_id > project.subjects.id // includes +Ref: project.dependency_subject.subject_id > project.subjects.id // requires (dependent) +Ref: project.dependency_subject.dependency_id > project.subjects.id // requires (prerequisite) +Ref: project.professour_subjects.prof_id > project.users.id // teaches_subject +Ref: project.professour_subjects.active_semester_id > project.active_semesters.id // teaches_subject +Ref: project.professour_subjects.subject_id > project.subjects.id // teaches_subject Index: sql/schema.md =================================================================== --- sql/schema.md (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) +++ sql/schema.md (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -0,0 +1,185 @@ +# Schema diagram + +Generated from [`schema_creation.sql`](schema_creation.sql), which is the source of truth. If you change +a table there, change it here too. + +Renders inline on GitHub and in the VS Code markdown preview (Ctrl+Shift+V) - +no extension or tooling needed. Relationship labels use the names from the +conceptual ER model. + +```mermaid +erDiagram + users { + integer id PK + varchar name + varchar embg + varchar surname + varchar index + timestamp bday + varchar email UK + varchar password + user_role role + quota_type quota + integer enrollment_year + timestamp created_at + } + + high_school { + integer id PK + integer user_id FK,UK + real gpa + hs_type type + } + + contact { + integer id PK + integer user_id FK,UK + varchar city + varchar municipality + varchar address + varchar number + varchar microsoft_email + } + + major { + integer id PK + varchar name + } + + active_semesters { + integer id PK + integer year UK + semester_type type UK + } + + subjects { + integer id PK + varchar name UK + varchar code UK + integer awarded_credits + integer dependency_credit + } + + enrolled_semesters { + integer id PK + integer user_id FK,UK + quota_type quota + integer major_id FK + varchar note + varchar student_comment + timestamp created_at + timestamp last_change + timestamp completed + integer semester_id FK,UK + } + + semesters_subjects { + integer id PK + integer enrolled_semesters_id FK,UK + integer subjects_id FK,UK + integer professor_id FK + boolean signature + } + + passed_subjects { + integer id PK + integer enrolled_id FK,UK + grade_type grade + timestamp date_passed + } + + documents { + integer id PK + varchar type + text body + integer cost + } + + user_documents { + integer user_id PK,FK + integer document_id PK,FK + } + + token { + integer id PK + integer user_id FK + varchar token + timestamp expires_at + boolean is_valid + } + + payment { + integer id PK + integer user_id FK + integer enrollment_id FK + integer amount + } + + major_subjects { + integer major_id PK,FK + integer subject_id PK,FK + integer mandatory_semester + } + + dependency_subject { + integer subject_id PK,FK + integer dependency_id PK,FK + } + + professour_subjects { + integer prof_id PK,FK + integer active_semester_id PK,FK + integer subject_id PK,FK + } + + users ||--o| high_school : has_hs + users ||--o| contact : has_contact + users ||--o{ token : has_token + users ||--o{ payment : pays + users ||--o{ enrolled_semesters : submits + users ||--o{ user_documents : owns + documents ||--o{ user_documents : owned_by + users ||--o{ semesters_subjects : teaches + users ||--o{ professour_subjects : teaches_subject + + major ||--o{ enrolled_semesters : for_major + active_semesters ||--o{ enrolled_semesters : during + enrolled_semesters ||--o{ semesters_subjects : contains + enrolled_semesters ||--o{ payment : for_enrollment + + subjects ||--o{ semesters_subjects : taken_in + semesters_subjects ||--o| passed_subjects : results_in + + major ||--o{ major_subjects : includes + subjects ||--o{ major_subjects : included_in + + subjects ||--o{ dependency_subject : requires + subjects ||--o{ dependency_subject : prerequisite_for + + active_semesters ||--o{ professour_subjects : taught_in + subjects ||--o{ professour_subjects : taught_as +``` + +## Enum types + +| Type | Labels | +|------|--------| +| `user_role` | `student`, `prof`, `admin` | +| `hs_type` | `strucen`, `gimnazija` | +| `quota_type` | `privatna`, `drzavna`, `stipendija` | +| `semester_type` | `summer`, `winter` | +| `grade_type` | `6`, `7`, `8`, `9`, `10` | + +## Notes + +- `UK` on `enrolled_semesters.user_id` / `semester_id` is the composite + `UNIQUE (user_id, semester_id)` - one enrolment per student per semester. + Same for `semesters_subjects (enrolled_semesters_id, subjects_id)` and + `active_semesters (year, type)`. +- `users` appears twice around `semesters_subjects`: once as the student, via + `enrolled_semesters.user_id`, and once as `professor_id`. The ER model links + the student through the enrolment, not directly. +- `dependency_subject` is `subjects` related to itself: `subject_id` is the + dependent subject and `dependency_id` the prerequisite, with a `CHECK` that + they differ. +- `professour_subjects` keeps the spelling used by the table in `schema_creation.sql`. Index: sql/schema_creation.sql =================================================================== --- sql/schema_creation.sql (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) +++ sql/schema_creation.sql (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -0,0 +1,141 @@ +DROP SCHEMA IF EXISTS project CASCADE; +CREATE SCHEMA project; + +SET search_path TO project; + +CREATE TYPE user_role AS ENUM ('student', 'prof', 'admin'); +CREATE TYPE hs_type AS ENUM ('strucen', 'gimnazija'); +CREATE TYPE quota_type AS ENUM ('privatna', 'drzavna', 'stipendija'); +CREATE TYPE semester_type AS ENUM ('summer', 'winter'); +CREATE TYPE grade_type AS ENUM ('6', '7', '8', '9', '10'); + +CREATE TABLE users ( + id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, + name VARCHAR(100) NOT NULL, + embg VARCHAR(13), + surname VARCHAR(100) NOT NULL, + "index" VARCHAR(20), + bday TIMESTAMP, + email VARCHAR(150) NOT NULL UNIQUE, + password VARCHAR(255) NOT NULL, + role user_role NOT NULL, + quota quota_type, + enrollment_year INTEGER, + created_at TIMESTAMP NOT NULL DEFAULT now() +); + +CREATE TABLE high_school ( + id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + user_id INTEGER NOT NULL UNIQUE REFERENCES users (id), + gpa REAL NOT NULL, + type hs_type NOT NULL +); + +CREATE TABLE contact ( + id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + user_id INTEGER NOT NULL UNIQUE REFERENCES users (id), + city VARCHAR(100) NOT NULL, + municipality VARCHAR(100) NOT NULL, + address VARCHAR(150) NOT NULL, + number VARCHAR(20) NOT NULL, + microsoft_email VARCHAR(150) +); + +CREATE TABLE major ( + id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + name VARCHAR(150) NOT NULL +); + +CREATE TABLE active_semesters ( + id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + year INTEGER NOT NULL, + type semester_type NOT NULL, + UNIQUE (year, type) +); + +CREATE TABLE subjects ( + id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + name VARCHAR(150) NOT NULL UNIQUE, + code VARCHAR(30) NOT NULL UNIQUE, + awarded_credits INTEGER NOT NULL, + dependency_credit INTEGER +); + +CREATE TABLE enrolled_semesters ( + id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + user_id INTEGER NOT NULL REFERENCES users (id), + quota quota_type NOT NULL, + major_id INTEGER NOT NULL REFERENCES major (id), + note VARCHAR(255), + student_comment VARCHAR(255), + created_at TIMESTAMP NOT NULL DEFAULT now(), + last_change TIMESTAMP, + completed TIMESTAMP, + semester_id INTEGER NOT NULL REFERENCES active_semesters (id), + UNIQUE (user_id, semester_id) +); + +CREATE TABLE semesters_subjects ( + id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + enrolled_semesters_id INTEGER NOT NULL REFERENCES enrolled_semesters (id), + subjects_id INTEGER NOT NULL REFERENCES subjects (id), + professor_id INTEGER NOT NULL REFERENCES users (id), + signature BOOLEAN NOT NULL DEFAULT FALSE, + UNIQUE (enrolled_semesters_id, subjects_id) +); + +CREATE TABLE passed_subjects ( + id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + enrolled_id INTEGER NOT NULL UNIQUE REFERENCES semesters_subjects (id), + grade grade_type NOT NULL, + date_passed TIMESTAMP NOT NULL +); + +CREATE TABLE documents ( + id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + type VARCHAR(100) NOT NULL, + body TEXT NOT NULL, + cost INTEGER NOT NULL +); + +CREATE TABLE user_documents ( + user_id INTEGER NOT NULL REFERENCES users (id), + document_id INTEGER NOT NULL REFERENCES documents (id), + PRIMARY KEY (user_id, document_id) +); + +CREATE TABLE token ( + id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + user_id INTEGER NOT NULL REFERENCES users (id), + token VARCHAR(255) NOT NULL, + expires_at TIMESTAMP NOT NULL, + is_valid BOOLEAN NOT NULL DEFAULT TRUE +); + +CREATE TABLE payment ( + id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + user_id INTEGER NOT NULL REFERENCES users (id), + enrollment_id INTEGER NOT NULL REFERENCES enrolled_semesters (id), + amount INTEGER NOT NULL +); + +CREATE TABLE major_subjects ( + major_id INTEGER NOT NULL REFERENCES major (id), + subject_id INTEGER NOT NULL REFERENCES subjects (id), + mandatory_semester INTEGER NOT NULL, + PRIMARY KEY (major_id, subject_id) +); + +CREATE TABLE dependency_subject ( + subject_id INTEGER NOT NULL REFERENCES subjects (id), + dependency_id INTEGER NOT NULL REFERENCES subjects (id), + PRIMARY KEY (subject_id, dependency_id), + CHECK (subject_id <> dependency_id) +); + +CREATE TABLE professour_subjects ( + prof_id INTEGER NOT NULL REFERENCES users (id), + active_semester_id INTEGER NOT NULL REFERENCES active_semesters (id), + subject_id INTEGER NOT NULL REFERENCES subjects (id), + PRIMARY KEY (prof_id, active_semester_id, subject_id) +);