-- Migration 001: QA detail columns, SEO metrics, content editor history, meta extras
-- Run via: http://localhost/Content/migrate.php

ALTER TABLE `qa_logs`
  ADD COLUMN IF NOT EXISTS `checks_detail`       JSON          DEFAULT NULL  AFTER `issues_found`,
  ADD COLUMN IF NOT EXISTS `readability_score`   DECIMAL(5,2)  DEFAULT 0.00  AFTER `keyword_density`,
  ADD COLUMN IF NOT EXISTS `avg_sentence_length` DECIMAL(5,2)  DEFAULT 0.00  AFTER `readability_score`,
  ADD COLUMN IF NOT EXISTS `paragraph_count`     INT(11)       DEFAULT 0     AFTER `avg_sentence_length`,
  ADD COLUMN IF NOT EXISTS `unique_word_ratio`   DECIMAL(5,2)  DEFAULT 0.00  AFTER `paragraph_count`;

ALTER TABLE `generated_content`
  ADD COLUMN IF NOT EXISTS `meta_extras` JSON DEFAULT NULL AFTER `status`;

CREATE TABLE IF NOT EXISTS `content_edits` (
  `id`             INT(11)      NOT NULL AUTO_INCREMENT,
  `content_id`     INT(11)      NOT NULL,
  `content_before` LONGTEXT     DEFAULT NULL,
  `content_after`  LONGTEXT     NOT NULL,
  `word_count`     INT(11)      DEFAULT 0,
  `edit_note`      VARCHAR(500) DEFAULT NULL,
  `created_at`     DATETIME     DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `content_id` (`content_id`),
  CONSTRAINT `fk_ce_content` FOREIGN KEY (`content_id`)
    REFERENCES `generated_content` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Fix: update all prompt templates to remove "Conclusion" heading
UPDATE `prompt_templates` SET
  `system_prompt` = REPLACE(`system_prompt`,
    'Conclusion with CTA',
    'Closing section — NEVER use "Conclusion" as a heading. Write a natural final section with an engaging, topic-relevant heading (e.g. "Your Next Step", "Ready to Get Started?", "What This Means for You"). The ending must feel conversational and flow naturally into the CTA.'),
  `user_prompt` = REPLACE(`user_prompt`,
    '5. Conclusion (summarize key points, strong CTA)',
    '5. Closing section (natural heading — NEVER "Conclusion", summarise value, strong CTA)'),
  `user_prompt` = REPLACE(`user_prompt`,
    '6. Closing CTA',
    '6. Closing section (natural engaging heading, not "Conclusion")'),
  `updated_at` = NOW();

UPDATE `prompt_templates` SET
  `system_prompt` = REPLACE(`system_prompt`,
    'Conclusion',
    'Closing section (do NOT label it "Conclusion")'),
  `updated_at` = NOW()
WHERE `system_prompt` LIKE '%Conclusion%'
  AND `system_prompt` NOT LIKE '%NEVER use "Conclusion"%';
