-- ============================================================
-- Content Automation Engine (CAE) - Database Schema v1.0
-- ============================================================

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

CREATE DATABASE IF NOT EXISTS `content_engine`
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_unicode_ci;

USE `content_engine`;

-- ============================================================
-- projects
-- ============================================================
CREATE TABLE IF NOT EXISTS `projects` (
  `id`                    INT(11)      NOT NULL AUTO_INCREMENT,
  `business_name`         VARCHAR(255) NOT NULL,
  `website_url`           VARCHAR(500) DEFAULT NULL,
  `target_audience`       TEXT         DEFAULT NULL,
  `content_intent`        VARCHAR(100) DEFAULT NULL,
  `brand_positioning`     TEXT         DEFAULT NULL,
  `tone_voice`            VARCHAR(100) DEFAULT 'professional',
  `brand_dos`             TEXT         DEFAULT NULL,
  `brand_donts`           TEXT         DEFAULT NULL,
  `pov_type`              VARCHAR(50)  DEFAULT 'third_person',
  `seo_philosophy`        TEXT         DEFAULT NULL,
  `compliance_rules`      TEXT         DEFAULT NULL,
  `brand_reference_style` TEXT         DEFAULT NULL,
  `cta_style`             TEXT         DEFAULT NULL,
  `no_no_words`           TEXT         DEFAULT NULL,
  `guideline_doc_url`     VARCHAR(500) DEFAULT NULL,
  `brand_config`          JSON         DEFAULT NULL,
  `seo_config`            JSON         DEFAULT NULL,
  `content_rules`         JSON         DEFAULT NULL,
  `is_active`             TINYINT(1)   DEFAULT 1,
  `created_at`            DATETIME     DEFAULT CURRENT_TIMESTAMP,
  `updated_at`            DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- page_types
-- ============================================================
CREATE TABLE IF NOT EXISTS `page_types` (
  `id`              INT(11)                       NOT NULL AUTO_INCREMENT,
  `name`            VARCHAR(100)                  NOT NULL,
  `slug`            VARCHAR(100)                  NOT NULL,
  `category`        ENUM('on_page','off_page')    DEFAULT 'on_page',
  `description`     TEXT                          DEFAULT NULL,
  `structure_rules` JSON                          DEFAULT NULL,
  `seo_rules`       JSON                          DEFAULT NULL,
  `content_blocks`  JSON                          DEFAULT NULL,
  `min_word_count`  INT(11)                       DEFAULT 800,
  `max_word_count`  INT(11)                       DEFAULT 2500,
  `is_active`       TINYINT(1)                    DEFAULT 1,
  `created_at`      DATETIME                      DEFAULT CURRENT_TIMESTAMP,
  `updated_at`      DATETIME                      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- prompt_templates
-- ============================================================
CREATE TABLE IF NOT EXISTS `prompt_templates` (
  `id`            INT(11)      NOT NULL AUTO_INCREMENT,
  `page_type_id`  INT(11)      NOT NULL,
  `name`          VARCHAR(255) NOT NULL,
  `system_prompt` LONGTEXT     NOT NULL,
  `user_prompt`   LONGTEXT     NOT NULL,
  `variables`     JSON         DEFAULT NULL,
  `version`       VARCHAR(20)  DEFAULT '1.0',
  `is_active`     TINYINT(1)   DEFAULT 1,
  `created_at`    DATETIME     DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `page_type_id` (`page_type_id`),
  CONSTRAINT `fk_pt_page_type` FOREIGN KEY (`page_type_id`)
    REFERENCES `page_types` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- topics
-- ============================================================
CREATE TABLE IF NOT EXISTS `topics` (
  `id`                   INT(11)      NOT NULL AUTO_INCREMENT,
  `project_id`           INT(11)      NOT NULL,
  `title`                VARCHAR(500) NOT NULL,
  `primary_keyword`      VARCHAR(255) NOT NULL,
  `supporting_keywords`  TEXT         DEFAULT NULL,
  `search_intent`        ENUM('informational','navigational','transactional','commercial') DEFAULT 'informational',
  `content_angle`        TEXT         DEFAULT NULL,
  `page_type_id`         INT(11)      NOT NULL,
  `target_word_count`    INT(11)      DEFAULT 1200,
  `status`               ENUM('pending','generating','generated','approved','failed') DEFAULT 'pending',
  `created_at`           DATETIME     DEFAULT CURRENT_TIMESTAMP,
  `updated_at`           DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `project_id`   (`project_id`),
  KEY `page_type_id` (`page_type_id`),
  CONSTRAINT `fk_topics_project`   FOREIGN KEY (`project_id`)   REFERENCES `projects`   (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_topics_page_type` FOREIGN KEY (`page_type_id`) REFERENCES `page_types` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- generated_content
-- ============================================================
CREATE TABLE IF NOT EXISTS `generated_content` (
  `id`                  INT(11)                                           NOT NULL AUTO_INCREMENT,
  `topic_id`            INT(11)                                           NOT NULL,
  `prompt_used`         LONGTEXT                                          DEFAULT NULL,
  `output`              LONGTEXT                                          DEFAULT NULL,
  `input_tokens`        INT(11)                                           DEFAULT 0,
  `output_tokens`       INT(11)                                           DEFAULT 0,
  `model_used`          VARCHAR(100)                                      DEFAULT NULL,
  `generation_time_ms`  INT(11)                                           DEFAULT 0,
  `status`              ENUM('draft','qa_failed','approved','published')  DEFAULT 'draft',
  `created_at`          DATETIME                                          DEFAULT CURRENT_TIMESTAMP,
  `updated_at`          DATETIME                                          DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `topic_id` (`topic_id`),
  CONSTRAINT `fk_gc_topic` FOREIGN KEY (`topic_id`) REFERENCES `topics` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- qa_logs
-- ============================================================
CREATE TABLE IF NOT EXISTS `qa_logs` (
  `id`               INT(11)       NOT NULL AUTO_INCREMENT,
  `content_id`       INT(11)       NOT NULL,
  `issues_found`     JSON          DEFAULT NULL,
  `passed`           TINYINT(1)    DEFAULT 0,
  `word_count`       INT(11)       DEFAULT 0,
  `keyword_density`  DECIMAL(5,2)  DEFAULT 0.00,
  `h1_count`         INT(11)       DEFAULT 0,
  `retry_count`      INT(11)       DEFAULT 0,
  `created_at`       DATETIME      DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `content_id` (`content_id`),
  CONSTRAINT `fk_qa_content` FOREIGN KEY (`content_id`) REFERENCES `generated_content` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
