CREATE TABLE IF NOT EXISTS "students"
(
    "id" INTEGER NOT NULL,
    "name" VARCHAR(30) NOT NULL,
    "group_id" INTEGER NOT NULL,

    PRIMARY KEY ("id" AUTOINCREMENT),
    FOREIGN KEY ("group_id") REFERENCES "groups" ("id")
);

CREATE TABLE IF NOT EXISTS "groups"
(
    "id" INTEGER NOT NULL,
    "cource" INTEGER NOT NULL,
    "name" VARCHAR(10),

    PRIMARY KEY ("id" AUTOINCREMENT)
);

CREATE TABLE IF NOT EXISTS "marks"
(
    "id" INTEGER NOT NULL,
    "student_id" INTEGER  NOT NULL,
    "day" TEXT NOT NULL,
    "subject_id" INTEGER NOT NULL,
    "mark" INTEGER NOT NULL,

    PRIMARY KEY ("id" AUTOINCREMENT),
    FOREIGN KEY ("student_id") REFERENCES "students" ("id"),
    FOREIGN KEY ("subject_id") REFERENCES "subjects" ("id")
);

CREATE TABLE IF NOT EXISTS "subjects"
(
    "id" INTEGER NOT NULL,
    "name" VARCHAR(30) NOT NULL,
    "description" TEXT,

    PRIMARY KEY ("id" AUTOINCREMENT)
);

CREATE TABLE IF NOT EXISTS "lessons"
(
    "id" INTEGER NOT NULL,
    "subject_id" INTEGER NOT NULL,
    "day" TEXT NOT NULL,
    "is_high_week" BOOLEAN NOT NULL,
    "group_id" INTEGER NOT NULL,
    "teacher_id" INTEGER NOT NULL,
    "theme_id" INTEGER NOT NULL

    PRIMARY KEY ("id" AUTOINCREMENT),
    FOREIGN KEY ("subject_id") REFERENCES "subjects" ("id"),
    FOREIGN KEY ("group_id") REFERENCES "groups" ("id"),
    FOREIGN KEY ("teacher_id") REFERENCES "teachers" ("id"),
    FOREIGN KEY ("theme_id") REFERENCES "themes" ("id")
);

CREATE TABLE IF NOT EXISTS "themes"
(
    "id" INTEGER NOT NULL,
    "subject_id" INTEGER NOT NULL,
    "name" VARCHAR(30),
    "description" TEXT,

    PRIMARY KEY ("id" AUTOINCREMENT),
    FOREIGN KEY ("subject_id") REFERENCES "subjects" ("id")

);

CREATE TABLE IF NOT EXISTS "users"
(
    "id" INTEGER NOT NULL,
    "login" VARCHAR(30) NOT NULL,
    "password" TEXT NOT NULL,
    "access_level" INTEGER NOT NULL,
    "role" BLOB NOT NULL,

    PRIMARY KEY ("id" AUTOINCREMENT)
);

CREATE TABLE IF NOT EXISTS "teachers"
(
    "id" INTEGER NOT NULL,
    "name" VARCHAR(40) NOT NULL,
    "user_id" INTEGER NOT NULL,

    PRIMARY KEY ("id" AUTOINCREMENT),
    FOREIGN KEY ("user_id") REFERENCES users ("id")
);

CREATE TABLE IF NOT EXISTS "tokens"
(
    "id" INTEGER NOT NULL,
    "value" TEXT NOT NULL,
    "expiration" TEXT,
    "access_level" INTEGER NOT NULL,
    "role" BLOB NOT NULL,
    "user_id" INTEGER NOT NULL,

    PRIMARY KEY ("id" AUTOINCREMENT),
    FOREIGN KEY ("user_id") REFERENCES "users"("id")
);

CREATE TABLE IF NOT EXISTS "subject_plans"
(
    "id" INTEGER NOT NULL,
    "subject_id" INTEGER NOT NULL,
    "group_id" INTEGER NOT NULL,
    "teacher_id" INTEGER NOT NULL,
    "day_start" TEXT NOT NULL,
    "day_end" TEXT NOT NULL,

    PRIMARY KEY ("id" AUTOINCREMENT),
    FOREIGN KEY ("subject_id") REFERENCES "subjects" ("id"),
    FOREIGN KEY ("group_id") REFERENCES "groups" ("id"),
    FOREIGN KEY ("teacher_id") REFERENCES "teachers" ("id")
);