create table Publisher (
	publisherId int(11) NOT NULL AUTO_INCREMENT,
	validatedVersion int(11),
	PRIMARY KEY ('publisherId')
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into Publisher (publisherId, validatedVersion)
	select ID_EDITEUR, ID_EDITEUR from editeur;

create table PublisherVersion (
	publisherVersionId int(11) NOT NULL AUTO_INCREMENT,
	publisher_publisherId int(11) NOT NULL,
	publisherName varchar(50) NOT NULL,
	publisherStreetAddress varchar(300),
	publisherPostalCode varchar(15),
	publisherPostOfficeBoxNumber varchar(50),
	publisherAddressRegion varchar(50),
	publisherAddressLocality varchar(50),
	publisherAddressCountry_countryId int(11),
	publisherTelephone varchar(25),
	publisherEmail varchar(50),
	publisherURL varchar(100),
	publisherActive TINYINT(1),
	publisherHistory longtext,
	publisherVersionAuthor_userId int(11),
	publisherVersionDatetime DATETIME,
	PRIMARY KEY ('publisherVersionId')
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into PublisherVersion (publisherVersionId, publisher_publisherId,
	publisherName, publisherStreetAddress, publisherPostalCode,
	publisherPostOfficeBoxNumber, publisherAddressRegion,
	publisherAddressLocality, publisherAddressCountry_countryId,
	publisherTelephone, publisherEmail, publisherURL, publisherActive,
	publisherHistory, publisherVersionAuthor_userId, publisherVersionDatetime)
select ID_EDITEUR, ID_EDITEUR, NOM_EDITEUR, ADR_EDITEUR, CP_EDITEUR, NULL,
	NULL, VILLE, countryId, TEL_EDITEUR, EMAIL_EDITEUR, URL_EDITEUR, NULL,
	HISTORIQUE, ID_UTILISATEUR, DATE_ACTION
from editeur e
left join Country on countryName = e.PAYS
join journal j on j.ID_JOURNALISABLE = e.ID_EDITEUR
where j.DATE_ACTION = (select max(DATE_ACTION) from journal j2
	where j2.ID_JOURNALISABLE = e.ID_EDITEUR)
union
select ID_EDITEUR, ID_EDITEUR, NOM_EDITEUR, ADR_EDITEUR, CP_EDITEUR, NULL,
	NULL, VILLE, countryId, TEL_EDITEUR, EMAIL_EDITEUR, URL_EDITEUR, NULL,
	HISTORIQUE, null, null
from editeur e
left join Country on countryName = e.PAYS
where ID_EDITEUR not in (select distinct ID_JOURNALISABLE from journal)
;

create table PublisherAction (
	publisherActionId int(11) NOT NULL AUTO_INCREMENT,
	ActionType TINYINT UNSIGNED NOT NULL,
	publisherActionAuthor_userId  int(11) NOT NULL,
	publisherVersion_publisherVersionId int(11) NOT NULL,
	publisherActionDatetime DATETIME,
	publisher_publisherId int(11) NOT NULL,
	PRIMARY KEY ('publisherActionId')
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into PublisherAction (ActionType, publisherActionAuthor_userId,
	publisherVersion_publisherVersionId, publisherActionDatetime,
	publisher_publisherId)
select 0, ID_UTILISATEUR, ID_EDITEUR, DATE_ACTION, ID_EDITEUR
from editeur e
join journal j on j.ID_JOURNALISABLE = e.ID_EDITEUR
where j.DATE_ACTION = (select max(DATE_ACTION) from journal j2
	where j2.ID_JOURNALISABLE = e.ID_EDITEUR)
;

show create table Publisher;

show create table PublisherVersion;

show create table PublisherAction;