
CREATE TABLE `was_openai_article_outline` (
  `openai_article_outline_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `openai_article_id` int(11) unsigned NOT NULL,
  `openai_request_id` int(11) DEFAULT NULL,
  `gpt_model` varchar(64) NOT NULL,
  `wordcount_est` int(11) DEFAULT NULL,
  `wordcount_real` int(11) DEFAULT NULL,
  `update_timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `insert_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`openai_article_outline_id`),
  KEY `idx_openai_article_id` (`openai_article_id`)
) ENGINE=InnoDB;

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

CREATE TABLE `was_openai_article_blog` (
  `openai_article_blog_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `openai_article_id` int(11) unsigned NOT NULL,
  `blog_topic` varchar(256) NOT NULL,
  `blog_summary` text NOT NULL,
  `update_timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `insert_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`openai_article_blog_id`),
  KEY `idx_openai_article_id` (`openai_article_id`)
) ENGINE=InnoDB;

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

CREATE TABLE `was_openai_article_aboutme` (
  `openai_article_aboutme_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `openai_article_id` int(11) unsigned NOT NULL,
  `aboutme_summary` text NOT NULL,
  `aboutme_name` varchar(256) NOT NULL,
  `aboutme_niche` varchar(256) NOT NULL,
  `aboutme_brand` varchar(256) NOT NULL,
  `aboutme_url` varchar(256) NOT NULL,
  `update_timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `insert_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`openai_article_aboutme_id`),
  KEY `idx_openai_article_id` (`openai_article_id`)
) ENGINE=InnoDB;

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

CREATE TABLE `was_openai_article_review` (
  `openai_article_review_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `openai_article_id` int(11) unsigned NOT NULL,
  `review_product` varchar(256) NOT NULL,
  `review_tone` varchar(256) NOT NULL,
  `update_timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `insert_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`openai_article_review_id`),
  KEY `idx_openai_article_id` (`openai_article_id`)
) ENGINE=InnoDB;
CREATE TRIGGER `was_openai_article_review_insert_timestamp` BEFORE INSERT ON `was_openai_article_review` FOR EACH ROW SET NEW.insert_timestamp = NOW();

CREATE TABLE `was_openai_article_sales` (
  `openai_article_sales_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `openai_article_id` int(11) unsigned NOT NULL,
  `sales_product` varchar(256) NOT NULL,
  `update_timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `insert_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`openai_article_sales_id`),
  KEY `idx_openai_article_id` (`openai_article_id`)
) ENGINE=InnoDB;
CREATE TRIGGER `was_openai_article_sales_insert_timestamp` BEFORE INSERT ON `was_openai_article_sales` FOR EACH ROW SET NEW.insert_timestamp = NOW();

CREATE TABLE `was_openai_article_outline_section` (
  `openai_article_outline_section_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `openai_article_outline_id` int(11) unsigned NOT NULL,
  `section_type` ENUM('AI', 'USER') NOT NULL DEFAULT 'AI',
  `section_heading` varchar(256) NOT NULL,
  `section_number` int(2) unsigned NOT NULL,
  `update_timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `insert_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`openai_article_outline_section_id`),
  KEY `idx_openai_article_outline_id` (`openai_article_outline_id`)
) ENGINE=InnoDB;

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

CREATE TABLE `was_openai_article_outline_section_topic` (
  `openai_article_outline_section_topic_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `openai_article_outline_section_id` int(11) unsigned NOT NULL,
  `section_topic` varchar(256) NOT NULL,
  `update_timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `insert_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`openai_article_outline_section_topic_id`),
  KEY `idx_openai_article_outline_section_id` (`openai_article_outline_section_id`)
) ENGINE=InnoDB;

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

CREATE TABLE `was_openai_article` (
  `openai_article_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `openai_article_content_id` int(11) unsigned DEFAULT NULL,
  `openai_article_outline_id` int(11) unsigned DEFAULT NULL,
  `openai_article_status_id` int(11) unsigned NOT NULL,
  `openai_article_type_id` int(11) unsigned NOT NULL,
  `sitecontent_article_id` int(11) unsigned DEFAULT NULL,
  `sitecontent_bucket_id` int(11) unsigned DEFAULT NULL,
  `user_id` int(11) unsigned NOT NULL,
  `sections_count` int(11) unsigned NOT NULL,
  `article_title` varchar(256) NOT NULL,
  `is_hidden` tinyint(1) unsigned NOT NULL DEFAULT 0,
  `update_timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `insert_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`openai_article_id`),
  KEY `idx_openai_article_content_id` (`openai_article_content_id`),
  KEY `idx_openai_article_status_id` (`openai_article_status_id`),
  KEY `idx_openai_article_outline_id` (`openai_article_outline_id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB;

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

CREATE TABLE `was_openai_article_content` (
  `openai_article_content_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `openai_author_id` int(11) unsigned DEFAULT NULL,
  `wordcount_est` int(11) DEFAULT NULL,
  `wordcount_real` int(11) DEFAULT NULL,
  `update_timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `insert_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`openai_article_content_id`),
  KEY `idx_openai_author_id` (`openai_author_id`)
) ENGINE=InnoDB;

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

CREATE TABLE `was_openai_article_content_section` (
  `openai_article_content_section_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `openai_article_content_id` int(11) unsigned NOT NULL,
  `openai_request_id` int(11) DEFAULT NULL,
  `gpt_model` varchar(64) NOT NULL,
  `wordcount_est` int(11) DEFAULT NULL,
  `wordcount_real` int(11) DEFAULT NULL,
  `section_heading` varchar(256) NOT NULL,
  `section_number` int(2) unsigned NOT NULL,
  `update_timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `insert_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`openai_article_content_section_id`),
  KEY `idx_openai_article_content_id` (`openai_article_content_id`)
) ENGINE=InnoDB;

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

CREATE TABLE `was_openai_article_content_section_paragraph` (
  `openai_article_content_section_paragraph_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `openai_article_content_section_id` int(11) unsigned NOT NULL,
  `section_paragraph` text NOT NULL,
  `update_timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `insert_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`openai_article_content_section_paragraph_id`),
  KEY `idx_openai_article_content_section_id` (`openai_article_content_section_id`)
) ENGINE=InnoDB;

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

CREATE TABLE `was_openai_article_status` (
  `openai_article_status_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `status_slug` varchar(32) NOT NULL,
  `status_desc` TEXT NOT NULL,
  `update_timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `insert_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`openai_article_status_id`),
  KEY `idx_status_slug` (`status_slug`)
) ENGINE=InnoDB;

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

INSERT INTO was_openai_article_status(status_slug, status_desc)
VALUES
('outline-pending', 'The outline has not been picked up by a worker'),
('outline-processing', 'The outline has been picked up by gearman worker'),
('outline-complete', 'The outline has completed'),
('outline-failed', 'The outline has falied'),
('article-pending', 'The article has not been submitted yet'),
('article-processing', 'The article has been sent to gearman'),
('article-complete', 'The article has completed');


CREATE TABLE `was_openai_article_type` (
  `openai_article_type_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `type_slug` varchar(32) NOT NULL,
  `type_display` varchar(32) NOT NULL,
  `type_desc` TEXT NOT NULL,
  `update_timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `insert_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`openai_article_type_id`),
  KEY `idx_type_slug` (`type_slug`)
) ENGINE=InnoDB;

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

INSERT INTO was_openai_article_type(type_slug, type_display, type_desc)
VALUES
('blog', 'Blog', 'Article designated as a blog post'),
('aboutme', 'About Me', 'Article describing a website and its branding');

INSERT INTO `was_openai_wordcount_event` (`event_slug`, `event_description`) 
VALUES
('outline', 'Article Outline Request'),
('outline-refund', 'Article Outline Refund'),
('article-refund', 'Article Content Request');

-- Match the utf8mb4_unicode_ci collation with the was_sitecontent_article table so that we can run a UNION SELECT
ALTER TABLE `was_openai_article` CHANGE `article_title` `article_title` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '';

-- Added last updated timestamp to use instead of update timestamp for showing last edited time
ALTER TABLE `was_openai_article`
ADD `last_updated_timestamp` INT(11) UNSIGNED NOT NULL AFTER `is_hidden`;

UPDATE `was_openai_article`
SET `last_updated_timestamp` = UNIX_TIMESTAMP(`update_timestamp`)
WHERE `last_updated_timestamp` = 0;
