DROP TABLE IF EXISTS TICKET_Notifications;
DROP TABLE IF EXISTS TICKET_StateActor;
DROP TABLE IF EXISTS TICKET_hasState;
DROP TABLE IF EXISTS TICKET_Data;
DROP TABLE IF EXISTS TICKET_Request;
DROP TABLE IF EXISTS TICKET_Field;
DROP TABLE IF EXISTS TICKET_State;
DROP TABLE IF EXISTS TICKET_Workflow;
DROP TABLE IF EXISTS TICKET_User;
DROP TABLE IF EXISTS TICKET_Group;

CREATE TABLE TICKET_Group (
  pk_Group  INT AUTO_INCREMENT PRIMARY KEY,
  name      VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE TICKET_User (
  pk_User    INT AUTO_INCREMENT PRIMARY KEY,
  username   VARCHAR(100) NOT NULL UNIQUE,
  firstName  VARCHAR(100) NOT NULL,
  email      VARCHAR(255) NOT NULL,
  lastName   VARCHAR(100) NOT NULL,
  password   VARCHAR(255) NOT NULL,
  fk_Group   INT,
  FOREIGN KEY (fk_Group) REFERENCES TICKET_Group(pk_Group)
);

CREATE TABLE TICKET_Workflow (
  pk_Workflow    INT AUTO_INCREMENT PRIMARY KEY,
  title          VARCHAR(150) NOT NULL,
  iam_inserted   INT NOT NULL,
  date_inserted  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (iam_inserted) REFERENCES TICKET_User(pk_User)
);

CREATE TABLE TICKET_State (
  pk_State    INT AUTO_INCREMENT PRIMARY KEY,
  fk_Workflow INT NOT NULL,
  `no`        INT NOT NULL,
  title       VARCHAR(150) NOT NULL,
  FOREIGN KEY (fk_Workflow) REFERENCES TICKET_Workflow(pk_Workflow)
);

CREATE TABLE TICKET_Field (
  id_field    INT AUTO_INCREMENT PRIMARY KEY,
  fk_State    INT NOT NULL,
  label       VARCHAR(150) NOT NULL,
  type        ENUM('input', 'checkbox', 'date', 'text', 'number') NOT NULL,
  is_optional BOOLEAN NOT NULL DEFAULT FALSE,
  FOREIGN KEY (fk_State) REFERENCES TICKET_State(pk_State)
);

CREATE TABLE TICKET_Request (
  pk_Request   INT AUTO_INCREMENT PRIMARY KEY,
  fk_Workflow  INT NOT NULL,
  fk_State     INT NOT NULL,
  fk_User      INT NOT NULL,
  is_closed    TINYINT(1) NOT NULL DEFAULT 0,
  FOREIGN KEY (fk_Workflow) REFERENCES TICKET_Workflow(pk_Workflow),
  FOREIGN KEY (fk_State)    REFERENCES TICKET_State(pk_State),
  FOREIGN KEY (fk_User)     REFERENCES TICKET_User(pk_User)
);

CREATE TABLE TICKET_Data (
  pk_Data    INT AUTO_INCREMENT PRIMARY KEY,
  fk_Request INT NOT NULL,
  fk_Field   INT NOT NULL,
  value      TEXT,
  FOREIGN KEY (fk_Request) REFERENCES TICKET_Request(pk_Request),
  FOREIGN KEY (fk_Field)   REFERENCES TICKET_Field(id_field)
);

CREATE TABLE TICKET_hasState (
  fi_hasState   INT AUTO_INCREMENT PRIMARY KEY,
  fk_Request    INT NOT NULL,
  fk_State      INT NOT NULL,
  iam_inserted  INT NOT NULL,
  date_inserted DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (fk_Request)   REFERENCES TICKET_Request(pk_Request),
  FOREIGN KEY (fk_State)     REFERENCES TICKET_State(pk_State),
  FOREIGN KEY (iam_inserted) REFERENCES TICKET_User(pk_User)
);

CREATE TABLE TICKET_StateActor (
  pk_StateActor INT AUTO_INCREMENT PRIMARY KEY,
  fk_State   INT NOT NULL,
  fk_Group   INT NULL,
  fk_User    INT NULL,
  actorType  ENUM('USER', 'GROUP') NOT NULL,
  FOREIGN KEY (fk_State) REFERENCES TICKET_State(pk_State),
  FOREIGN KEY (fk_User)  REFERENCES TICKET_User(pk_User),
  FOREIGN KEY (fk_Group) REFERENCES TICKET_Group(pk_Group),
  CHECK (
    (fk_User IS NOT NULL  AND fk_Group IS NULL AND actorType = 'USER')  OR
    (fk_Group IS NOT NULL AND fk_User  IS NULL AND actorType = 'GROUP')
  )
);

CREATE TABLE TICKET_Notifications (
  pk_Notification INT AUTO_INCREMENT PRIMARY KEY,
  fk_State        INT NOT NULL,
  fk_User         INT NOT NULL,
  fk_Request      INT NOT NULL,
  sendAt          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  readAt          DATETIME NULL,
  FOREIGN KEY (fk_State)   REFERENCES TICKET_State(pk_State),
  FOREIGN KEY (fk_User)    REFERENCES TICKET_User(pk_User),
  FOREIGN KEY (fk_Request) REFERENCES TICKET_Request(pk_Request)
);
