
CREATE TABLE ppl (
	ppl_id INTEGER PRIMARY KEY AUTO_INCREMENT,
	ppl_login VARCHAR(32) NOT NULL, -- leerlingnummer of afkorting htmlencoded
	ppl_forename VARCHAR(64) NOT NULL, -- html encoded
	ppl_prefix VARCHAR(16) NOT NULL, -- html encoded
	ppl_surname VARCHAR(128) NOT NULL, -- html encoded
	ppl_active INTEGER DEFAULT 0, -- 0 means active and NULL means inactive 
	ppl_name_normalized VARCHAR(208) NOT NULL, -- html encoded
	ppl_forename_normalized VARCHAR(64) NOT NULL, -- html encoded
	ppl_prefix_normalized VARCHAR(16) NOT NULL, -- html encoded
	ppl_surname_normalized VARCHAR(128) NOT NULL, -- html encoded
	UNIQUE ( ppl_login, ppl_active ) -- only one ppl_login may be active at any given time
);

CREATE TABLE grp (
	grp_id INTEGER PRIMARY KEY AUTO_INCREMENT,
	grp_name VARCHAR(32) NOT NULL, -- html encoded
	grp_home INTEGER, -- boolean, 1: 'stamklas', 0: 'cluster',
	UNIQUE ( grp_name )
);

CREATE TABLE subj (
	subj_id INTEGER PRIMARY KEY AUTO_INCREMENT,
	subj_abbrev VARCHAR(8) NOT NULL, -- html encoded
	UNIQUE ( subj_abbrev )
);

CREATE TABLE years (
	year_id INTEGER PRIMARY KEY AUTO_INCREMENT,
	year_long VARCHAR(9) NOT NULL,
	UNIQUE (year_long)
);

CREATE TABLE grp2subj (
	grp2subj_id INTEGER PRIMARY KEY AUTO_INCREMENT,
	grp_id INTEGER NOT NULL,
	subj_id INTEGER,
	UNIQUE ( grp_id, subj_id )
);
CREATE INDEX grp2subj0 ON grp2subj ( grp_id );
CREATE INDEX grp2subj1 ON grp2subj ( subj_id );

CREATE TABLE ppl2grp (
	ppl2grp_id INTEGER PRIMARY KEY AUTO_INCREMENT,
	ppl_id INTEGER NOT NULL,
	grp_id INTEGER NOT NULL,
	ppl_enter INTEGER NOT NULL, -- seconds since epoch
	ppl_exit INTEGER NOT NULL -- seconds since epoch
);
CREATE INDEX ppl2grp0 ON ppl2grp ( ppl_id );
CREATE INDEX ppl2grp1 ON ppl2grp ( grp_id );

CREATE TABLE ppl2grp2subj (
	ppl2grp2subj_id INTEGER PRIMARY KEY AUTO_INCREMENT,
	ppl_id INTEGER NOT NULL,
	grp2subj_id INTEGER NOT NULL,
	ppl_enter INTEGER NOT NULL, -- seconds since epoch
	ppl_exit INTEGER NOT NULL -- seconds since epoch
);
CREATE INDEX ppl2grp2subj0 ON ppl2grp2subj ( ppl_id );
CREATE INDEX ppl2grp2subj1 ON ppl2grp2subj ( grp2subj_id );

CREATE TABLE year2grp2subj (
	year2grp2subj_id INTEGER PRIMARY KEY AUTO_INCREMENT,
	year_id INTEGER NOT NULL,
	grp2subj_id INTEGER NOT NULL,
	UNIQUE ( year_id, grp2subj_id )
);
CREATE INDEX year2grp2subj0 ON year2grp2subj ( year_id );
CREATE INDEX year2grp2subj1 ON year2grp2subj ( grp2subj_id );

CREATE TABLE ppl2altlogin (
	ppl2altloginl_id INTEGER PRIMARY KEY AUTO_INCREMENT,
	ppl_id INTEGER,
	altlogin VARCHAR(64),
	UNIQUE (ppl_id)
);
CREATE INDEX ppl2altlogin0 ON ppl2altlogin ( ppl_id );
CREATE INDEX ppl2altlogin1 ON ppl2altlogin ( altlogin );

