CREATE TABLE IF NOT EXISTS questions (
    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,
    body LONGTEXT NOT NULL,
    slug VARCHAR(191) NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'pending',
    duplicate_of_id BIGINT UNSIGNED NULL,
    is_anonymous TINYINT(1) NOT NULL DEFAULT 0,
    views_count INT UNSIGNED NOT NULL DEFAULT 0,
    answers_count INT UNSIGNED NOT NULL DEFAULT 0,
    votes_count INT NOT NULL DEFAULT 0,
    comments_count INT UNSIGNED NOT NULL DEFAULT 0,
    best_answer_id BIGINT UNSIGNED NULL,
    meta_description VARCHAR(191) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_questions_slug (slug),
    KEY idx_questions_user_id (user_id),
    KEY idx_questions_category_id (category_id),
    KEY idx_questions_duplicate_of_id (duplicate_of_id),
    KEY idx_questions_best_answer_id (best_answer_id),
    CONSTRAINT fk_questions_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_questions_category FOREIGN KEY (category_id) REFERENCES categories (id) ON DELETE SET NULL,
    CONSTRAINT fk_questions_duplicate FOREIGN KEY (duplicate_of_id) REFERENCES questions (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS answers (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    question_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NULL,
    body LONGTEXT NOT NULL,
    votes_count INT NOT NULL DEFAULT 0,
    is_best TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_answers_question_id (question_id),
    KEY idx_answers_user_id (user_id),
    CONSTRAINT fk_answers_question FOREIGN KEY (question_id) REFERENCES questions (id) ON DELETE CASCADE,
    CONSTRAINT fk_answers_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
