<commit_before>-- This line removes old tables, it's in a comment for safety reasons. Please make sure it's safe before uncommenting!
--drop table assignment, course, submission, participant, users cascade;
CREATE TABLE IF NOT EXISTS Users (
    UserId       SERIAL         PRIMARY KEY,
    UserName     TEXT           UNIQUE,
    FirstName    VARCHAR(64)    NOT NULL,
    LastName     VARCHAR(64)    NOT NULL,
    PersNr       CHAR(10)       NOT NULL,
    Pswd         TEXT           NOT NULL
    );
CREATE TABLE IF NOT EXISTS Course (
    CourseId    VARCHAR(10)    PRIMARY KEY,
    Year        INT            NOT NULL,
    Term        CHAR(4)        NOT NULL,
    CourseCode  CHAR(6)        NOT NULL,
    CourseName  TEXT
    );
CREATE TYPE role AS ENUM ('Teacher', 'Student', 'Assistant');
CREATE TABLE IF NOT EXISTS Participant (
    UserId       INT           references Users(UserId),
    CourseId     VARCHAR(10)   references Course(CourseId),
    Function     role          NOT NULL
    );
CREATE TABLE IF NOT EXISTS Assignment (
    AssignmentId      SERIAL         PRIMARY KEY,
    CourseId          VARCHAR(10)    references Course(CourseId) NOT NULL,
    Title             TEXT           NOT NULL,
    StartDate         timestamp      NOT NULL,
    EndDate           timestamp      NOT NULL,
    MinTime           INT            NOT NULL,
    MaxTime           INT            NOT NULL,
    Published         timestamp,
    GradeScale        TEXT           NOT NULL
    );
CREATE TABLE IF NOT EXISTS Submission (
    AssignmentId    INT          references Assignment(AssignmentId) NOT NULL,
    StudentId           INT          references Users(UserId) NOT NULL,
    StudentPublishConsent   Boolean ,
    SubmissionDate      timestamp    NOT NULL,
    Grade               VARCHAR (3),
    TeacherId           INT          references Users(UserId),
    PublishStudentSubmission    Boolean ,
    PRIMARY KEY (AssignmentId, StudentId)
    );
CREATE TYPE lang AS ENUM ('Swedish', 'English', 'Klingon');
CREATE TABLE IF NOT EXISTS Config (
    UserId              INT         references Users(UserId),
    Language            lang        NOT NULL,
    Email               VARCHAR(100),
    TextSize            INT         NOT NULL,
    );
-- serial   4 bytes autoincrementing integer    1 to 2147483647
-- bigserial    8 bytes large autoincrementing integer  1 to 9223372036854775807<commit_msg>Added submission status and video lengths. Updated drop expression. Designated primary keys to some tables and changed config->mail to a boolean.<commit_after>-- This line removes old tables, it's in a comment for safety reasons. Please make sure it's safe before uncommenting!
--DROP TABLE Assignment, Course, Submission, Participant, Users, Config cascade;
CREATE TABLE IF NOT EXISTS Users (
    UserId                      SERIAL          PRIMARY KEY,
    UserName                    TEXT            UNIQUE,
    FirstName                   VARCHAR(64)     NOT NULL,
    LastName                    VARCHAR(64)     NOT NULL,
    PersNr                      CHAR(10)        NOT NULL,
    Pswd                        TEXT            NOT NULL
    );
CREATE TABLE IF NOT EXISTS Course (
    CourseId                    VARCHAR(10)     PRIMARY KEY,
    Year                        INT             NOT NULL,
    Term                        CHAR(4)         NOT NULL,
    CourseCode                  CHAR(6)         NOT NULL,
    CourseName                  TEXT
    );
CREATE TYPE role AS ENUM ('Teacher', 'Student', 'Assistant');
CREATE TABLE IF NOT EXISTS Participant (
    UserId                      INT             references Users(UserId),
    CourseId                    VARCHAR(10)     references Course(CourseId),
    Function                    role            NOT NULL,
    PRIMARY KEY (UserId, CourseId)
    );
CREATE TABLE IF NOT EXISTS Assignment (
    AssignmentId                SERIAL          PRIMARY KEY,
    CourseId                    VARCHAR(10)     references Course(CourseId) NOT NULL,
    Title                       TEXT            NOT NULL,
    StartDate                   timestamp       NOT NULL,
    EndDate                     timestamp       NOT NULL,
    MinTime                     INT             NOT NULL,
    MaxTime                     INT             NOT NULL,
    Published                   timestamp,
    GradeScale                  TEXT            NOT NULL,
    VidLength                   INT             NOT NULL
    );

CREATE TYPE subStatus AS ENUM ('Answer', 'NoAnswer', 'Blank');
CREATE TABLE IF NOT EXISTS Submission (
    AssignmentId                INT             references Assignment(AssignmentId) NOT NULL,
    StudentId                   INT             references Users(UserId) NOT NULL,
    StudentPublishConsent       Boolean ,
    SubmissionDate              timestamp       NOT NULL,
    Grade                       VARCHAR (3),
    TeacherId                   INT             references Users(UserId),
    PublishStudentSubmission    Boolean,
    Status                      subStatus       NOT NULL,
    AnswerVidLength             INT             NOT NULL,
    FeedbackVidLength           INT             NOT NULL,
    PRIMARY KEY (AssignmentId, StudentId)
    );

CREATE TYPE lang AS ENUM ('Swedish', 'English', 'Klingon');
CREATE TABLE IF NOT EXISTS Config (
    UserId                      INT             references Users(UserId),
    Language                    lang            NOT NULL,
    Email                       Boolean,
    TextSize                    INT             NOT NULL,
    PRIMARY KEY (UserId)
    );
-- serial       4 bytes autoincrementing integer        1 to 2147483647
-- bigserial    8 bytes large autoincrementing integer  1 to 9223372036854775807
{'fraction_non_alphanumeric': 0.048963234012894236, 'fraction_numerical': 0.017250392054364874, 'mean_word_length': 1.4219409282700421, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 4, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 15, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6244903', 'n_tokens_mistral': 1458, 'n_tokens_neox': 1357, 'n_words': 513}