DROP TABLE IF EXISTS user;
DROP TABLE IF EXISTS question;
DROP TABLE IF EXISTS course;
DROP TABLE IF EXISTS lecture;
DROP TABLE IF EXISTS upvote;

CREATE TABLE user (
  user_id INTEGER PRIMARY KEY AUTOINCREMENT,
  username TEXT UNIQUE NOT NULL,
  password TEXT NOT NULL,
  lecturer INTEGER NOT NULL
);

CREATE TABLE question (
  question_id INTEGER PRIMARY KEY AUTOINCREMENT,
  lecture_id INTEGER NOT NULL,
  created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  question_text TEXT NOT NULL,
  upvotes INTEGER NOT NULL,
  has_answer BOOLEAN DEFAULT 0,
  answer TEXT,
  user_id INTEGER,
  FOREIGN KEY (lecture_id) REFERENCES lecture (lecture_id),
  FOREIGN KEY (user_id) REFERENCES user (user_id)
);

CREATE TABLE course (
  course_id INTEGER PRIMARY KEY AUTOINCREMENT,
  course_name TEXT UNIQUE NOT NULL
  /*user_id INTEGER NOT NULL,
  FOREIGN KEY (user_id) REFERENCES user (user_id)*/
);

CREATE TABLE lecture (
  lecture_id INTEGER PRIMARY KEY AUTOINCREMENT,
  lecture_date DATE,
  course_id INTEGER,
  topic TEXT NOT NULL,
  FOREIGN KEY (course_id) REFERENCES course(course_id)
);

CREATE TABLE upvote (
  upvote_id INTEGER PRIMARY KEY AUTOINCREMENT,
  question_id INTEGER,
  user_login TEXT
);

INSERT INTO course (course_name) VALUES ('Tworzenie systemów informatycznych');
INSERT INTO course (course_name) VALUES ('Optymalizacja kombinatoryczna');
INSERT INTO course (course_name) VALUES ('Podstawy JavaScriptu dla programistów C#');

INSERT INTO question (lecture_id, question_text, upvotes) VALUES (2, 'Nudne pytanie',0);
INSERT INTO question (lecture_id, question_text, upvotes) VALUES (2, 'Czy pytania będą proste?',78);
INSERT INTO question (lecture_id, question_text, upvotes) VALUES (2, 'Czy to będzie na egzaminie?',111);
INSERT INTO question (lecture_id, question_text, upvotes) VALUES (2, 'Nic nie rozumiem.',6);
INSERT INTO question (lecture_id, question_text, upvotes) VALUES (2, 'Chcę zdaaaaać',9);

INSERT INTO lecture (lecture_date,course_id,topic) VALUES('2019-01-30',1,'Jak przetrwać chwilę prawdy?');
INSERT INTO lecture (lecture_date,course_id,topic) VALUES('2019-01-16',1,'Peopleware');
INSERT INTO lecture (lecture_date,course_id,topic) VALUES('2019-01-09',1,'Jak zostać komputerowym geniuszem?');
INSERT INTO lecture (lecture_date,course_id,topic) VALUES('2018-12-01',1,'Jak zaplanować projekt informatyczny?');

INSERT INTO lecture (lecture_date,course_id,topic) VALUES('2019-01-18',2,'Zaawansowane algorytmy znajdujące skojarzenia w grafach');
INSERT INTO lecture (lecture_date,course_id,topic) VALUES('2019-01-07',3,'Wprowadzenie do Ajaxa');
INSERT INTO lecture (lecture_date,course_id,topic) VALUES('2019-01-14',3,'Wysyłanie i ściąganie danych przy pomocy Ajaxa');



select * from user;
select * from question;