CREATE TABLE IF NOT EXISTS question_follows (
    user_id BIGINT UNSIGNED NOT NULL,
    question_id BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, question_id),
    KEY idx_question_follows_question_id (question_id),
    CONSTRAINT fk_question_follows_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT fk_question_follows_question FOREIGN KEY (question_id) REFERENCES questions (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tag_follows (
    user_id BIGINT UNSIGNED NOT NULL,
    tag_id BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, tag_id),
    KEY idx_tag_follows_tag_id (tag_id),
    CONSTRAINT fk_tag_follows_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT fk_tag_follows_tag FOREIGN KEY (tag_id) REFERENCES tags (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS notification_preferences (
    user_id BIGINT UNSIGNED NOT NULL,
    event_type VARCHAR(64) NOT NULL,
    in_app_enabled TINYINT(1) NOT NULL DEFAULT 1,
    email_enabled TINYINT(1) NOT NULL DEFAULT 0,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, event_type),
    CONSTRAINT fk_notification_preferences_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET @comments_status_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'comments' AND COLUMN_NAME = 'status'
);
SET @comments_status_sql = IF(
    @comments_status_exists = 0,
    "ALTER TABLE comments ADD COLUMN status VARCHAR(32) NOT NULL DEFAULT 'published' AFTER body, ADD KEY idx_comments_status_created (status, created_at)",
    'SELECT 1'
);
PREPARE comments_status_stmt FROM @comments_status_sql;
EXECUTE comments_status_stmt;
DEALLOCATE PREPARE comments_status_stmt;

SET @api_prefix_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'api_tokens' AND COLUMN_NAME = 'token_prefix'
);
SET @api_prefix_sql = IF(
    @api_prefix_exists = 0,
    "ALTER TABLE api_tokens ADD COLUMN token_prefix VARCHAR(20) NULL AFTER user_id",
    'SELECT 1'
);
PREPARE api_prefix_stmt FROM @api_prefix_sql;
EXECUTE api_prefix_stmt;
DEALLOCATE PREPARE api_prefix_stmt;

SET @api_scopes_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'api_tokens' AND COLUMN_NAME = 'scopes_json'
);
SET @api_scopes_sql = IF(
    @api_scopes_exists = 0,
    "ALTER TABLE api_tokens ADD COLUMN scopes_json TEXT NULL AFTER device_label",
    'SELECT 1'
);
PREPARE api_scopes_stmt FROM @api_scopes_sql;
EXECUTE api_scopes_stmt;
DEALLOCATE PREPARE api_scopes_stmt;

INSERT IGNORE INTO settings (`key`, value, type, is_secret, created_at, updated_at)
VALUES
    ('community.comments_enabled', '1', 'boolean', 0, NOW(), NOW()),
    ('community.follows_enabled', '1', 'boolean', 0, NOW(), NOW()),
    ('community.duplicate_suggestions_enabled', '1', 'boolean', 0, NOW(), NOW()),
    ('notifications.new_answer', '1', 'boolean', 0, NOW(), NOW()),
    ('notifications.new_comment', '1', 'boolean', 0, NOW(), NOW());
