-- Excellent Blog Database Schema
USE excellent_blog;

-- Categories table for blog organization
CREATE TABLE IF NOT EXISTS categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    color VARCHAR(7) DEFAULT '#007cba',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Articles table for both internal and external blogs
CREATE TABLE IF NOT EXISTS articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    content LONGTEXT NOT NULL,
    excerpt TEXT,
    featured_image VARCHAR(255),
    type ENUM('internal', 'external') NOT NULL,
    status ENUM('draft', 'published', 'scheduled') DEFAULT 'draft',
    author_id INT,
    category_id INT,
    published_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    views INT DEFAULT 0,
    likes INT DEFAULT 0,
    is_ai_generated BOOLEAN DEFAULT FALSE,
    source_url VARCHAR(500),
    meta_title VARCHAR(255),
    meta_description TEXT,
    FOREIGN KEY (author_id) REFERENCES users(id),
    FOREIGN KEY (category_id) REFERENCES categories(id),
    INDEX idx_type_status (type, status),
    INDEX idx_published_at (published_at),
    INDEX idx_slug (slug)
);

-- Tags table
CREATE TABLE IF NOT EXISTS tags (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    slug VARCHAR(50) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Article tags junction table
CREATE TABLE IF NOT EXISTS article_tags (
    article_id INT,
    tag_id INT,
    PRIMARY KEY (article_id, tag_id),
    FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);

-- Languages table for i18n support
CREATE TABLE IF NOT EXISTS languages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(5) UNIQUE NOT NULL,
    name VARCHAR(50) NOT NULL,
    native_name VARCHAR(50) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Article translations table
CREATE TABLE IF NOT EXISTS article_translations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    article_id INT NOT NULL,
    language_code VARCHAR(5) NOT NULL,
    title VARCHAR(255) NOT NULL,
    content LONGTEXT NOT NULL,
    excerpt TEXT,
    meta_title VARCHAR(255),
    meta_description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE,
    FOREIGN KEY (language_code) REFERENCES languages(code),
    UNIQUE KEY unique_article_language (article_id, language_code)
);

-- Comments table
CREATE TABLE IF NOT EXISTS comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    article_id INT NOT NULL,
    author_name VARCHAR(100) NOT NULL,
    author_email VARCHAR(100) NOT NULL,
    content TEXT NOT NULL,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    parent_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE,
    FOREIGN KEY (parent_id) REFERENCES comments(id) ON DELETE CASCADE
);

-- Published articles tracking
CREATE TABLE IF NOT EXISTS published_articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    article_id INT NOT NULL,
    platform_id INT NOT NULL,
    external_id VARCHAR(255),
    external_url VARCHAR(500),
    published_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('pending', 'published', 'failed') DEFAULT 'pending',
    error_message TEXT,
    FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE,
    FOREIGN KEY (platform_id) REFERENCES external_platforms(id),
    UNIQUE KEY unique_article_platform (article_id, platform_id)
);

-- Crawled sources configuration
CREATE TABLE IF NOT EXISTS crawl_sources (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    url VARCHAR(255) NOT NULL,
    selector VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    last_crawled TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- System settings
CREATE TABLE IF NOT EXISTS settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    key_name VARCHAR(100) UNIQUE NOT NULL,
    value TEXT,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert supported languages
INSERT INTO languages (code, name, native_name) VALUES
('zh-TW', 'Traditional Chinese', '繁體中文'),
('zh-CN', 'Simplified Chinese', '简体中文'),
('en', 'English', 'English'),
('ja', 'Japanese', '日本語'),
('vi', 'Vietnamese', 'Tiếng Việt'),
('ms', 'Malay', 'Bahasa Melayu');

-- Insert default categories
INSERT INTO categories (name, slug, description, color) VALUES
('Technology', 'technology', 'Technology related articles', '#007cba'),
('Business', 'business', 'Business and entrepreneurship', '#28a745'),
('AI & Machine Learning', 'ai-ml', 'Artificial Intelligence and Machine Learning', '#dc3545'),
('Web Development', 'web-dev', 'Web development and programming', '#ffc107'),
('Marketing', 'marketing', 'Digital marketing and SEO', '#6f42c1'),
('News', 'news', 'Latest industry news', '#fd7e14');

-- Insert default system settings
INSERT INTO settings (key_name, value, description) VALUES
('site_title', 'Excellent Blog', 'Main site title'),
('site_description', 'AI-Powered International Blog Platform', 'Site description for SEO'),
('auto_crawl_enabled', '1', 'Enable automatic article crawling'),
('auto_publish_enabled', '1', 'Enable automatic publishing to external platforms'),
('ai_generation_enabled', '1', 'Enable AI article generation'),
('default_language', 'zh-TW', 'Default site language'),
('articles_per_page', '10', 'Number of articles per page'),
('crawl_interval', '3600', 'Crawling interval in seconds (1 hour)');