CREATE TABLE complaint (
  ComplaintID  int AUTO_INCREMENT NOT NULL COMMENT 'Unique ID representing a complaint',
  RegNo        varchar(30) NOT NULL COMMENT 'Register number of the student who filed this complaint. Maps to RegNo of Student table.',
  AssignedTo   varchar(30) NOT NULL COMMENT 'StaffID to which this complaint has been assigned. Maps to StaffID of Staff table',
  PRIMARY KEY (ComplaintID)
) ENGINE = InnoDB
  COMMENT = 'Contains data regarding a complaint';

CREATE TABLE staff (
  StaffID  varchar(30) NOT NULL COMMENT 'A unique ID assigned to each staff. Same as UserID of User table',
  `Year`   year COMMENT 'Year the staff is currently handling',
  `Section`  varchar(1) DEFAULT '-' COMMENT 'Section the staff is currently handling',
  PRIMARY KEY (StaffID)
) ENGINE = InnoDB
  COMMENT = 'Contains information about a staff member';

CREATE TABLE student (
  RegNo    varchar(30) NOT NULL COMMENT 'A unique register number assigned to each student',
  `Year`   year NOT NULL COMMENT 'Year in which the student joined the college',
  `Section`  varchar(1) NOT NULL COMMENT 'Section of the student',
  PRIMARY KEY (RegNo)
) ENGINE = InnoDB
  COMMENT = 'Contains information about a student';


CREATE TABLE `user` (
  UserID        varchar(30) NOT NULL COMMENT 'RegNo/StaffID',
  UserName      varchar(50) COMMENT 'Full name of the User',
  `Password`    varchar(33) NOT NULL COMMENT 'Password for login',
  PRIMARY KEY (UserID)
) ENGINE = InnoDB
  COMMENT = 'Contains basic information for a Complaint Management System user';

/* Foreign Keys */
ALTER TABLE complaint ADD CONSTRAINT complaint_ibfk_1 FOREIGN KEY (AssignedTo) REFERENCES staff(StaffID);

ALTER TABLE complaint ADD CONSTRAINT complaint_ibfk_2 FOREIGN KEY (Category) REFERENCES compcats(CategoryID);

ALTER TABLE complaint ADD CONSTRAINT complaint_ibfk_3 FOREIGN KEY (RegNo) REFERENCES student(RegNo);

ALTER TABLE student ADD CONSTRAINT RegNo FOREIGN KEY (RegNo) REFERENCES `user`(UserID);

ALTER TABLE `user` ADD CONSTRAINT user_ibfk_1 FOREIGN KEY (PriviledgeID) REFERENCES priviledge(PriviledgeID);