CREATE TABLE IF NOT EXISTS suggestions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 title VARCHAR(160) NOT NULL,
 message TEXT NOT NULL,
 category VARCHAR(24) NOT NULL DEFAULT 'other',
 likes_count INT UNSIGNED NOT NULL DEFAULT 0,
 comments_count INT UNSIGNED NOT NULL DEFAULT 0,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 deleted_at DATETIME NULL,
 KEY idx_suggestion_feed(deleted_at,id),
 KEY idx_suggestion_author(user_id,deleted_at,id),
 FOREIGN KEY(user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS suggestion_likes (
 suggestion_id BIGINT UNSIGNED NOT NULL,
 user_id BIGINT UNSIGNED NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(suggestion_id,user_id),
 FOREIGN KEY(suggestion_id) REFERENCES suggestions(id) ON DELETE CASCADE,
 FOREIGN KEY(user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS suggestion_comments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 suggestion_id BIGINT UNSIGNED NOT NULL,
 user_id BIGINT UNSIGNED NOT NULL,
 message TEXT NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 deleted_at DATETIME NULL,
 KEY idx_suggestion_comments(suggestion_id,deleted_at,id),
 FOREIGN KEY(suggestion_id) REFERENCES suggestions(id) ON DELETE CASCADE,
 FOREIGN KEY(user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
