CREATE TABLE IF NOT EXISTS topics (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    category_id BIGINT UNSIGNED NULL,
    content_locale VARCHAR(16) NOT NULL DEFAULT 'ar',
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(191) NOT NULL,
    body MEDIUMTEXT NOT NULL,
    kind VARCHAR(16) NOT NULL DEFAULT 'discussion',
    status VARCHAR(32) NOT NULL DEFAULT 'published',
    is_pinned TINYINT(1) NOT NULL DEFAULT 0,
    is_locked TINYINT(1) NOT NULL DEFAULT 0,
    views_count INT UNSIGNED NOT NULL DEFAULT 0,
    comments_count INT UNSIGNED NOT NULL DEFAULT 0,
    last_activity_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_topics_slug (slug),
    KEY idx_topics_listing (status, is_pinned, last_activity_at),
    KEY idx_topics_category (category_id),
    KEY idx_topics_locale (content_locale),
    KEY idx_topics_user (user_id),
    CONSTRAINT fk_topics_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_topics_category FOREIGN KEY (category_id) REFERENCES categories (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
