CREATE TABLE IF NOT EXISTS `CommitValidationMessage` (
  `id` SERIAL,
  `commit_id` bigint(20) NOT NULL,
  `providerId` varchar(100) DEFAULT NULL,
  `authorEmail` varchar(100) DEFAULT NULL,
  `committerEmail` varchar(255) DEFAULT NULL,
  `eclipseId` varchar(255) DEFAULT NULL,
  `statusCode` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `CommitValidationStatus` (
  `id` SERIAL,
  `commitHash` varchar(100) NOT NULL,
  `project` varchar(100) NOT NULL,
  `lastModified` datetime DEFAULT NULL,
  `creationDate` datetime DEFAULT NULL,
  `provider` varchar(100) NOT NULL,
  `repoUrl` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `CommitValidationStatusGrouping` (
  `fingerprint` varchar(255) NOT NULL,
  `commit_id` bigint(20)  NOT NULL,
  PRIMARY KEY (`commit_id`,`fingerprint`)
);

CREATE TABLE IF NOT EXISTS `PrivateProjectEvent` (
  `userId` int(10) NOT NULL,
  `projectId` int(10) NOT NULL,
  `projectPath` varchar(255) NOT NULL,
  `ef_username` varchar(100) DEFAULT NULL,
  `parentProject` int(10) DEFAULT NULL,
  `creationDate` datetime NOT NULL,
  `deletionDate` datetime DEFAULT NULL,
  PRIMARY KEY (`userId`, `projectId`, `projectPath`)
);

ALTER TABLE CommitValidationMessage ADD COLUMN IF NOT EXISTS `committerEmail` varchar(255) DEFAULT NULL;

CREATE TABLE GithubWebhookTracking (
  id SERIAL NOT NULL,
  installationId varchar(63) NOT NULL,
  lastKnownState varchar(63) NOT NULL,
  headSha varchar(63) NOT NULL,
  repositoryFullName varchar(127) NOT NULL,
  pullRequestNumber int NOT NULL,
  lastUpdated datetime DEFAULT NULL,
  PRIMARY KEY (id)
);