DROP TABLE IF EXISTS extusers;

CREATE TABLE extusers (
  uid         varchar(100) NOT NULL,
  firstname   varchar(100) NOT NULL,
  lastname    varchar(100) NOT NULL,
  email       varchar(100) NOT NULL,
  suspended   integer NOT NULL DEFAULT 0,
  dob         integer NOT NULL DEFAULT 0,
  employeetype varchar(100) NOT NULL DEFAULT 'Unknown',
  description text,
  broomstick  varchar(100)
);

INSERT INTO extusers
  ( uid, firstname, lastname, email, suspended)
VALUES
  ( 'potterj', 'James', 'Potter', 'james.potter@example.com', 0),
  ( 'potterh', 'Harry', 'Potter', 'harry.potter@example.com', 1),
  ( 'weasleyr', 'Ronald', 'Weasley', 'ronald.weasley@example.com', 1),
  ( 'grangerh', 'Hermione', 'Granger', 'hermione.granger@example.com', 1),
  ( 'dumbledorea', 'Albus', 'Dumbledore', 'albus.dumbledore@example.com', 1),
  ( 'snapes', 'Severus', 'Snape', 'severus.snape@example.com', 1),
  ( 'creeveyc', 'Colin', 'Creevey', 'colin.creevey@example.com', 1)
  ;

UPDATE extusers SET
  dob = 11123456,
  employeetype = 'Temp',
  description = 'Person',
  broomstick = 'Firebolt 1000';

UPDATE extusers SET suspended = 2 WHERE suspended = 1;
UPDATE extusers SET suspended = 1 WHERE suspended = 0;
UPDATE extusers SET suspended = 0 WHERE suspended = 1;
UPDATE extusers SET
  dob = 9876543,
  employeetype = 'Intern',
  description = 'Muggle',
  broomstick = 'Dusty';


DROP TABLE IF EXISTS extcourses;

CREATE TABLE extcourses (
  idnumber    varchar(100) NOT NULL,
  fullname    varchar(100) NOT NULL,
  shortname   varchar(100) NOT NULL
);

INSERT INTO extcourses
  ( idnumber, fullname, shortname )
VALUES
  ( 'potterj', 'James Potter', 'potterj' ),
  ( 'potterh', 'Harry Potter', 'potterh'),
  ( 'weasleyr', 'Ronald Weasley', 'weasleyr')
  ;
