-- Content Pipeline Tables for Parketry
-- Run with: mysql -u root -p wordpress < 001_create_tables.sql

-- Topics table: 120 topics with priority scoring
CREATE TABLE IF NOT EXISTS parketry_content_topics (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,
    content_cluster ENUM('kosten', 'recht', 'technik', 'branchen', 'vergleich', 'howto', 'trends', 'probleme') NOT NULL,
    priority ENUM('A', 'B', 'C') NOT NULL,
    priority_score TINYINT UNSIGNED NOT NULL CHECK (priority_score BETWEEN 1 AND 100),
    target_keywords VARCHAR(500) NOT NULL,
    secondary_keywords VARCHAR(500),
    search_intent ENUM('informational', 'transactional', 'navigational', 'commercial') NOT NULL DEFAULT 'informational',
    status ENUM('pending', 'in_progress', 'published', 'skipped', 'failed') NOT NULL DEFAULT 'pending',
    skip_reason VARCHAR(500),
    article_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_status (status),
    INDEX idx_priority (priority_score DESC),
    INDEX idx_cluster (content_cluster),
    INDEX idx_status_priority (status, priority_score DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Articles table: Generated content with fact-check tracking
CREATE TABLE IF NOT EXISTS parketry_articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    topic_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,
    content_markdown MEDIUMTEXT NOT NULL,
    content_html MEDIUMTEXT NOT NULL,
    meta_description VARCHAR(160) NOT NULL,
    word_count INT UNSIGNED NOT NULL,
    fact_check_passed BOOLEAN NOT NULL DEFAULT FALSE,
    fact_check_log TEXT,
    fact_check_attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
    generation_cost_usd DECIMAL(10, 6) NOT NULL DEFAULT 0,
    thumbnail_path VARCHAR(500),
    wp_post_id BIGINT UNSIGNED,
    wp_media_id BIGINT UNSIGNED,
    author_id BIGINT UNSIGNED,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    published_at TIMESTAMP NULL,

    FOREIGN KEY (topic_id) REFERENCES parketry_content_topics(id) ON DELETE CASCADE,
    INDEX idx_wp_post (wp_post_id),
    INDEX idx_topic (topic_id),
    INDEX idx_published (published_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add foreign key from topics to articles after articles table exists
ALTER TABLE parketry_content_topics
    ADD CONSTRAINT fk_topic_article
    FOREIGN KEY (article_id) REFERENCES parketry_articles(id) ON DELETE SET NULL;


-- Published content table: For semantic deduplication
-- Includes existing articles and pipeline-generated content
CREATE TABLE IF NOT EXISTS parketry_published_content (
    id INT AUTO_INCREMENT PRIMARY KEY,
    source ENUM('existing', 'pipeline') NOT NULL,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL,
    summary TEXT NOT NULL,
    main_keywords VARCHAR(500) NOT NULL,
    wp_post_id BIGINT UNSIGNED,
    published_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_source (source),
    INDEX idx_slug (slug),
    FULLTEXT INDEX ft_content (title, summary)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Migration tracking table
CREATE TABLE IF NOT EXISTS parketry_migrations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    migration_name VARCHAR(255) NOT NULL UNIQUE,
    executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Record this migration
INSERT INTO parketry_migrations (migration_name) VALUES ('001_create_tables')
ON DUPLICATE KEY UPDATE executed_at = CURRENT_TIMESTAMP;
