DROP TABLE IF EXISTS user;
DROP TABLE IF EXISTS room;
DROP TABLE IF EXISTS mac;
DROP TABLE IF EXISTS volt;
DROP TABLE IF EXISTS image;
DROP TABLE IF EXISTS displayschedule;

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

CREATE TABLE room (
  id INTEGER PRIMARY KEY AUTOINCREMENT, 
  roomname TEXT NOT NULL UNIQUE,
  teacher TEXT DEFAULT Unbekannt
);

CREATE TABLE mac (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  mac TEXT UNIQUE NOT NULL,
  roomid INTEGER UNIQUE,
  FOREIGN KEY(roomid) REFERENCES room(id)
);

CREATE TABLE volt (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  volt REAL,
  macid INT NOT NULL,
  statusTime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (macid) REFERENCES mac(id) 
);

CREATE TABLE image (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  png TEXT not NULL,
  bin TEXT not NULL
);

CREATE TABLE displayschedule (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  imageid INT NOT NULL,
  macid INT NOT NULL,
  earliest TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  latest TIMESTAMP
);


