
CREATE TABLE `was_hub_social_share_platform` (
  `social_share_platform_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `platform_slug` varchar(32) NOT NULL,
  `platform_name` varchar(32) NOT NULL,
  `insert_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `update_timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`social_share_platform_id`),
  UNIQUE KEY `unq_platform_slug` (`platform_slug`)
) ENGINE=InnoDB COMMENT='stores social sharing platforms';

CREATE TRIGGER `was_hub_social_share_platform_insert_timestamp` BEFORE INSERT ON `was_hub_social_share_platform`
FOR EACH ROW SET NEW.insert_timestamp = NOW();

INSERT INTO was_hub_social_share_platform (platform_slug, platform_name)
VALUES 
('pinterest', 'Pinterest'),
('facebook', 'Facebook'),
('x', 'X'),
('linkedin', 'Linkedin'),
('threads', 'Threads');


CREATE TABLE `was_hub_social_share_platform_post` (
  `social_share_platform_post_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `social_share_platform_id` int(11) unsigned NOT NULL,
  `wordpress_page_id` int(11) unsigned NOT NULL,
  `platform_post_title` varchar(64) NOT NULL,
  `platform_post_content` text NOT NULL,
  `platform_post_date` int(11) unsigned DEFAULT NULL,
  `insert_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `update_timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`social_share_platform_post_id`),
  KEY `idx_social_share_platform_id` (`social_share_platform_id`),
  KEY `idx_wordpress_page_id` (`wordpress_page_id`)
) ENGINE=InnoDB COMMENT='stores posts shared on social platforms';

CREATE TRIGGER `was_hub_social_share_platform_post_insert_timestamp` BEFORE INSERT ON `was_hub_social_share_platform_post`
FOR EACH ROW SET NEW.insert_timestamp = NOW();


CREATE TABLE `was_hub_social_share_platform_progress` (
  `social_share_platform_progress_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(11) unsigned NOT NULL,
  `social_share_platform_id` int(11) unsigned NOT NULL,
  `platform_percent` tinyint(3) NOT NULL DEFAULT 0,
  `insert_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `update_timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`social_share_platform_progress_id`),
  KEY `idx_business_id` (`business_id`),
  KEY `idx_social_share_platform_id` (`social_share_platform_id`)
) ENGINE=InnoDB COMMENT='stores progress of posts shared on social platform';

CREATE TRIGGER `was_hub_social_share_platform_progress_insert_timestamp` BEFORE INSERT ON `was_hub_social_share_platform_progress`
FOR EACH ROW SET NEW.insert_timestamp = NOW();


ALTER TABLE was_wordpress_page_api 
ADD COLUMN `pages_count` int(11) unsigned NOT NULL AFTER `domain`;
