CREATE TABLE `was_openai_author` (
  `openai_author_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL,
  `author_name` varchar(64) NOT NULL,
  `author_description` text NOT NULL,
  `author_thumbnail` text NOT NULL,
  `author_narrative` varchar(64) 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_author_id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB;


CREATE TABLE `was_openai_author_attribute` (
  `openai_author_attribute_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `attribute_display` varchar(64) 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_author_attribute_id`)
) ENGINE=InnoDB;


CREATE TABLE `was_openai_author_attribute_label` (
  `openai_author_attribute_label_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `openai_author_attribute_id` int(11) unsigned NOT NULL,
  `label_display` TEXT NOT NULL,
  `label_value` tinyint(1) NOT NULL DEFAULT 1,
  `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_author_attribute_label_id`),
  KEY `idx_openai_author_attribute_id` (`openai_author_attribute_id`)
) ENGINE=InnoDB;


CREATE TABLE `was_openai_author_attribute_assign` (
  `openai_author_attribute_value_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `openai_author_attribute_id` int(11) unsigned NOT NULL,
  `openai_author_id` int(11) unsigned NOT NULL,
  `assign_value` tinyint(1) NOT NULL DEFAULT 1,
  `is_editable` tinyint(1) 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_author_attribute_value_id`),
  KEY `idx_openai_author_attribute_id` (`openai_author_attribute_id`),
  KEY `idx_openai_author_id` (`openai_author_id`)
) ENGINE=InnoDB;


CREATE TABLE `was_openai_author_quirk` (
  `openai_author_quirk_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `quirk_display` varchar(20) 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_author_quirk_id`)
) ENGINE=InnoDB;


CREATE TABLE `was_openai_author_quirk_assign` (
  `openai_author_quirk_assign_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `openai_author_quirk_id` int(11) unsigned NOT NULL,
  `openai_author_id` int(11) unsigned NOT NULL,
  `assign_status` tinyint(1) NOT NULL DEFAULT 1,
  `is_editable` tinyint(1) 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_author_quirk_assign_id`),
  KEY `idx_openai_author_quirk_id` (`openai_author_quirk_id`),
  KEY `idx_openai_author_id` (`openai_author_id`)
) ENGINE=InnoDB;


CREATE TABLE `was_openai_author_sample` (
  `openai_author_sample_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `openai_author_id` int(11) unsigned NOT NULL,
  `sample_label` varchar(64) NOT NULL,
  `sample_content` 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_author_sample_id`),
  KEY `idx_openai_author_id` (`openai_author_id`)
) ENGINE=InnoDB;

CREATE TABLE `was_openai_author_access` (
  `openai_author_access_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned 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 (`openai_author_access_id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB;

CREATE TRIGGER `was_openai_author_insert_timestamp` BEFORE INSERT ON `was_openai_author` FOR EACH ROW SET NEW.insert_timestamp = NOW();
CREATE TRIGGER `was_openai_author_attribute_insert_timestamp` BEFORE INSERT ON `was_openai_author_attribute` FOR EACH ROW SET NEW.insert_timestamp = NOW();
CREATE TRIGGER `was_openai_author_attribute_label_insert_timestamp` BEFORE INSERT ON `was_openai_author_attribute_label` FOR EACH ROW SET NEW.insert_timestamp = NOW();
CREATE TRIGGER `was_openai_author_attribute_assign_insert_timestamp` BEFORE INSERT ON `was_openai_author_attribute_assign` FOR EACH ROW SET NEW.insert_timestamp = NOW();
CREATE TRIGGER `was_openai_author_quirk_insert_timestamp` BEFORE INSERT ON `was_openai_author_quirk` FOR EACH ROW SET NEW.insert_timestamp = NOW();
CREATE TRIGGER `was_openai_author_quirk_assign_insert_timestamp` BEFORE INSERT ON `was_openai_author_quirk_assign` FOR EACH ROW SET NEW.insert_timestamp = NOW();
CREATE TRIGGER `was_openai_author_sample_insert_timestamp` BEFORE INSERT ON `was_openai_author_sample` FOR EACH ROW SET NEW.insert_timestamp = NOW();
CREATE TRIGGER `was_openai_author_access_insert_timestamp` BEFORE INSERT ON `was_openai_author_access` FOR EACH ROW SET NEW.insert_timestamp = NOW();

-- Only Kyle and Carson will have AI article access to start with
INSERT INTO was_openai_author_access (user_id)
SELECT user_id
FROM wa_user
WHERE username in('carson', 'kyle')
	AND live = 1;

INSERT INTO was_openai_author_attribute(attribute_display)
VALUES
('Language Proficiency'),
('Avoid Direct Audience Addresses'),
('Visual Imagery'),
('Interactive Elements'),
('Subjective Tone'),
('Slang and Colloquial Terms'),
('Humor'),
('Formality'),
('Conversational'),
('Complexity'),
('Descriptiveness'),
('Perspective'),
('Abstract Concepts'),
('Emotional Resonance'),
('Contemporary Language'),
('Pacing'),
('Write at a Grade 8 Reading Level'),
('Avoid Clichés'),
('Avoid Metaphors'),
('Avoid Overuse of Em-dashes'),
('Incorporate Ellipsis');

INSERT INTO was_openai_author_quirk(quirk_display)
VALUES
('Occasional Emphasis using em-dashes and ellipses.'),
('One-sentence dramatics.'),
('Occasional Rhetorical queries.'),
('Alliterative rhythms.'),
('Occasional UPPERCASE flair.'),
('Succinct sentences.'),
('Deep dives, eschewing clichés, and grounding your insights in concrete language.'),
('Utilize compact paragraphing; frequently incorporate one-line paragraphs for emphasis and pacing.'),
('Express personal feelings and opinions'),
('Utilize one-word paragraphs');

INSERT INTO was_openai_author_attribute_label (openai_author_attribute_id, label_value, label_display)
SELECT
  a.openai_author_attribute_id,
  label.value AS label_value,
  CASE a.attribute_display
  WHEN 'Language Proficiency' THEN
    CASE label.value
    WHEN 1 THEN 'Basic'
    WHEN 2 THEN 'Intermediate'
    WHEN 3 THEN 'Advanced'
    WHEN 4 THEN 'Expert'
    WHEN 5 THEN 'Scholarly'
    END
  WHEN 'Avoid Direct Audience Addresses' THEN
    CASE label.value
    WHEN 1 THEN 'Always Use'
    WHEN 2 THEN 'Frequently Use'
    WHEN 3 THEN 'Occasionally Use'
    WHEN 4 THEN 'Rarely Use'
    WHEN 5 THEN 'Never Use'
    END
  WHEN 'Visual Imagery' THEN
    CASE label.value
    WHEN 1 THEN 'None'
    WHEN 2 THEN 'Minimal'
    WHEN 3 THEN 'Moderate'
    WHEN 4 THEN 'Detailed'
    WHEN 5 THEN 'Vivid'
    END
  WHEN 'Interactive Elements' THEN
    CASE label.value
    WHEN 1 THEN 'None'
    WHEN 2 THEN 'Minimal'
    WHEN 3 THEN 'Occasional'
    WHEN 4 THEN 'Frequent'
    WHEN 5 THEN 'Constant'
    END
  WHEN 'Subjective Tone' THEN
    CASE label.value
    WHEN 1 THEN 'Objective'
    WHEN 2 THEN 'Somewhat Subjective'
    WHEN 3 THEN 'Balanced'
    WHEN 4 THEN 'Very Subjective'
    WHEN 5 THEN 'Opinionated'
    END
  WHEN 'Slang and Colloquial Terms' THEN
    CASE label.value
    WHEN 1 THEN 'None'
    WHEN 2 THEN 'Rare'
    WHEN 3 THEN 'Occasional'
    WHEN 4 THEN 'Frequent'
    WHEN 5 THEN 'Saturated'
    END
  WHEN 'Humor' THEN
    CASE label.value
    WHEN 1 THEN 'None'
    WHEN 2 THEN 'Subtle'
    WHEN 3 THEN 'Moderate'
    WHEN 4 THEN 'Noticeable'
    WHEN 5 THEN 'Dominant'
    END
  WHEN 'Formality' THEN
    CASE label.value
    WHEN 1 THEN 'Informal'
    WHEN 2 THEN 'Semi-informal'
    WHEN 3 THEN 'Neutral'
    WHEN 4 THEN 'Semi-formal'
    WHEN 5 THEN 'Formal'
    END
  WHEN 'Conversational' THEN
    CASE label.value
    WHEN 1 THEN 'Not Conversational'
    WHEN 2 THEN 'Slightly Conversational'
    WHEN 3 THEN 'Moderately Conversational'
    WHEN 4 THEN 'Very Conversational'
    WHEN 5 THEN 'As If Speaking'
    END
  WHEN 'Complexity' THEN
    CASE label.value
    WHEN 1 THEN 'Simple'
    WHEN 2 THEN 'Straightforward'
    WHEN 3 THEN 'Moderate'
    WHEN 4 THEN 'Complex'
    WHEN 5 THEN 'Intricate'
    END
  WHEN 'Descriptiveness' THEN
    CASE label.value
    WHEN 1 THEN 'Sparse'
    WHEN 2 THEN 'Minimal'
    WHEN 3 THEN 'Moderate'
    WHEN 4 THEN 'Detailed'
    WHEN 5 THEN 'Ornate'
    END
  WHEN 'Perspective' THEN
    CASE label.value
    WHEN 1 THEN 'Limited'
    WHEN 2 THEN 'Somewhat Broad'
    WHEN 3 THEN 'Balanced'
    WHEN 4 THEN 'Broad'
    WHEN 5 THEN 'All-Encompassing'
    END
  WHEN 'Abstract Concepts' THEN
    CASE label.value
    WHEN 1 THEN 'Concrete'
    WHEN 2 THEN 'Mostly Concrete'
    WHEN 3 THEN 'Balanced'
    WHEN 4 THEN 'Mostly Abstract'
    WHEN 5 THEN 'Highly Abstract'
    END
  WHEN 'Emotional Resonance' THEN
    CASE label.value
    WHEN 1 THEN 'Detached'
    WHEN 2 THEN 'Slightly Moving'
    WHEN 3 THEN 'Engaging'
    WHEN 4 THEN 'Stirring'
    WHEN 5 THEN 'Intense'
    END
  WHEN 'Contemporary Language' THEN
    CASE label.value
    WHEN 1 THEN 'Timeless'
    WHEN 2 THEN 'Occasionally Modern'
    WHEN 3 THEN 'Balanced'
    WHEN 4 THEN 'Mostly Modern'
    WHEN 5 THEN 'Ultra-Modern'
    END
  WHEN 'Pacing' THEN
    CASE label.value
    WHEN 1 THEN 'Slow'
    WHEN 2 THEN 'Leisurely'
    WHEN 3 THEN 'Balanced'
    WHEN 4 THEN 'Brisk'
    WHEN 5 THEN 'Fast-Paced'
    END
  WHEN 'Write at a Grade 8 Reading Level' THEN
    CASE label.value
    WHEN 1 THEN 'Advanced Vocabulary'
    WHEN 2 THEN 'High School Level'
    WHEN 3 THEN 'General Audience'
    WHEN 4 THEN 'Middle School Level'
    WHEN 5 THEN 'Grade 8 Specific'
    END
  WHEN 'Avoid Clichés' THEN
    CASE label.value
    WHEN 1 THEN 'Full of Clichés'
    WHEN 2 THEN 'Many Clichés'
    WHEN 3 THEN 'Some Clichés'
    WHEN 4 THEN 'Few Clichés'
    WHEN 5 THEN 'No Clichés'
    END
  WHEN 'Avoid Metaphors' THEN
    CASE label.value
    WHEN 1 THEN 'Rife with Metaphors'
    WHEN 2 THEN 'Many Metaphors'
    WHEN 3 THEN 'Some Metaphors'
    WHEN 4 THEN 'Few Metaphors'
    WHEN 5 THEN 'No Metaphors'
    END
  WHEN 'Avoid Overuse of Em-dashes' THEN
    CASE label.value
    WHEN 1 THEN 'Em-dash Overload'
    WHEN 2 THEN 'Frequent Em-dashes'
    WHEN 3 THEN 'Moderate Em-dashes'
    WHEN 4 THEN 'Rare Em-dashes'
    WHEN 5 THEN 'No Em-dashes'
    END
  WHEN 'Incorporate Ellipsis' THEN
    CASE label.value
    WHEN 1 THEN 'None'
    WHEN 2 THEN 'Rarely'
    WHEN 3 THEN 'Occasionally'
    WHEN 4 THEN 'Frequently'
    WHEN 5 THEN 'Excessive'
    END
  END AS label_display
FROM
  was_openai_author_attribute a
  JOIN (
    SELECT 1 AS value
    UNION ALL
    SELECT 2 AS value
    UNION ALL
    SELECT 3 AS value
    UNION ALL
    SELECT 4 AS value
    UNION ALL
    SELECT 5 AS value) AS label;
