-- create smd__report table and add foreign keys

CREATE TABLE smd__report (
	reportID INT(11) unsigned NOT NULL AUTO_INCREMENT,
	name VARCHAR(64) NOT NULL,
	image VARCHAR(255),
	organizationID INT(11) unsigned NOT NULL,
	updatedBy INT(11) unsigned,
	updatedAt TIMESTAMP,
	createdBy INT(11) unsigned NOT NULL,
	createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	status ENUM('active', 'archived') NOT NULL,
	organizationType ENUM('single', 'multi') NOT NULL,
	reportType ENUM('social', 'inhouse') NOT NULL,
	`start` DATE NOT NULL,
	`end` DATE NOT NULL,
	compareStart DATE,
	compareEnd DATE,
	dataModifiers SET('aggregated'),
	coverImage VARCHAR(255),
	PRIMARY KEY (reportID)
);

ALTER TABLE smd__report MODIFY COLUMN updatedAt timestamp on update CURRENT_TIMESTAMP NULL;
ALTER TABLE smd__report MODIFY COLUMN status enum('active','archived') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'active' NOT NULL;

ALTER TABLE smd__report ADD CONSTRAINT smd__report_FK_createdBy FOREIGN KEY (createdBy) REFERENCES cms__user(userID);
ALTER TABLE smd__report ADD CONSTRAINT smd__report_FK_organization FOREIGN KEY (organizationID) REFERENCES smd__organization(organizationID);
ALTER TABLE smd__report ADD CONSTRAINT smd__report_FK_updatedBy FOREIGN KEY (updatedBy) REFERENCES cms__user(userID);

-- create smd__report_shared_with_users table and add foreign keys

CREATE TABLE smd__report_shared_with_users (
	reportID INT(11) unsigned,
	userID INT(11) unsigned
);

ALTER TABLE smd__report_shared_with_users ADD CONSTRAINT smd__report_shared_with_users_FK_user FOREIGN KEY (userID) REFERENCES cms__user(userID);
ALTER TABLE smd__report_shared_with_users ADD CONSTRAINT smd__report_shared_with_users_FK_report FOREIGN KEY (reportID) REFERENCES smd__report(reportID);

CREATE TABLE smd__report_has_social_sites (
	reportID INT(11) unsigned,
	siteID INT(11) unsigned
);

ALTER TABLE smd__report_has_social_sites ADD CONSTRAINT smd__report_has_social_site_FK_site FOREIGN KEY (siteID) REFERENCES smd__social_site(siteID);
ALTER TABLE smd__report_has_social_sites ADD CONSTRAINT smd__report_has_social_site_FK_report FOREIGN KEY (reportID) REFERENCES smd__report(reportID);
